Database Operations in JDBC

Executing Basic SQL Statements

You can use the createStatement() method of a Connection instance, and the executeUpdate() or executeQuery() method of a Statement instance, to execute a SQL statement within a Java application.

See Working with Data in a TimesTen Database in Oracle TimesTen In-Memory Database Operations Guide.

Unless statements are prepared in advance, use the execution methods of a Statement object, such as execute(), executeUpdate() or executeQuery(), depending on the nature of the SQL statement and any returned result set.

For SQL statements that are prepared in advance, use the same execution methods of a PreparedStatement object.

The execute() method returns true if there is a result set (for example, on a SELECT) or false if there is no result set (for example, on an INSERT, UPDATE, or DELETE). The executeUpdate() method returns the number of rows affected. For example, when executing an INSERT statement, the executeUpdate() method returns the number of rows inserted. The executeQuery() method returns a result set, so it should only be called when a result set is expected (for example, when executing a SELECT statement).

Note:

Typically, use the Statement, PreparedStatement, or CallableStatement method close() to close a statement you have finished using. TimesTen statements also support standard try-with-resource functionality using java.lang.AutoCloseable.
See Working with TimesTen Result Sets: Hints and Restrictions.

This example uses the executeUpdate() method on the Statement object to execute an INSERT statement to insert data into the customer table in the current schema. The connection must have been opened, which is not shown.

Connection conn = null;
Statement stmt = null;
...
// [Code to open connection. See Connecting to the Database...] 
...
try {
    stmt = conn.createStatement();
    int numRows = stmt.executeUpdate("insert into customer values" 
                  + "(40, 'West', 'Big Dish', '123 Signal St.')");
}
catch (SQLException ex) {
    ...
}

The following example uses an executeQuery() call on the Statement object to execute a SELECT statement on the customer table in the current schema and display the returned java.sql.ResultSet instance:

Statement stmt = null;
. . . . . .
try {
  ResultSet rs = stmt.executeQuery("select cust_num, region, " +
                      "name, address from customer");
  System.out.println("Fetching result set...");
  while (rs.next()) {
    System.out.println("\n Customer number: " + rs.getInt(1));
    System.out.println(" Region: " + rs.getString(2));
    System.out.println(" Name: " + rs.getString(3));
    System.out.println(" Address: " + rs.getString(4));
    }
  } 
catch (SQLException ex) {
  ex.printStackTrace();
}

Committing or Rolling Back Changes to the Database

This section discusses autocommit and manual commits or rollbacks, assuming a JDBC Connection object myconn and Statement object mystmt. The following topics are covered:

Note:

All open cursors on the connection are closed upon transaction commit or rollback in TimesTen.

Refer to Transaction Overview in Oracle TimesTen In-Memory Database Operations Guide.

Setting Autocommit

A TimesTen connection has autocommit enabled by default, but for performance reasons it is recommended that you disable it. You can use the Connection method setAutoCommit() to enable or disable autocommit.

Disable autocommit as follows:

myconn.setAutoCommit(false);
// Report any SQLWarnings on the connection.
// See Reporting Errors and Warnings.

Note:

A setAutoCommit() call results in a commit only when the call actually changes the autocommit setting.

Manually Committing or Rolling Back Changes

If autocommit is disabled, you must use the Connection method commit() to manually commit transactions, or the rollback() method to roll back changes. Consider the following example.

myconn.commit();

Or:

myconn.rollback();

Using COMMIT and ROLLBACK SQL Statements

You can prepare and execute COMMIT and ROLLBACK SQL statements the same way as other SQL statements. Using COMMIT and ROLLBACK statements has the same effect as using the Connection methods commit() and rollback().

For example:

mystmt.execute("COMMIT");

Managing Multiple Threads

When your application has a direct connection to the database, TimesTen functions share stack space with your application. In multithreaded environments it is important to avoid overrunning the stack allocated to each thread, as this can cause a program to fail in unpredictable ways that are difficult to debug. The amount of stack space consumed by TimesTen calls varies depending on the SQL functionality used. Most applications should set thread stack space between 34 KB and 72 KB.

The amount of stack space allocated for each thread is specified by the operating system when threads are created. On Windows, you can use the TimesTen debug driver and link your application against the Visual C++ debug C library to enable stack probes that raise an identifiable exception if a thread attempts to grow its stack beyond the amount allocated.

Note:

In multithreaded applications, a thread that issues requests on different connection handles to the same database may encounter lock conflicts with itself. TimesTen returns lock timeout and deadlock errors in this situation.

The level4 sample application demonstrates the use of multiple threads. Refer to About TimesTen Quick Start and Sample Applications.

Java Escape Syntax and SQL Functions

When using SQL in JDBC, pay special attention to Java escape syntax. SQL functions such as UNISTR use the backslash (\) character. You should escape the backslash character.

For example, using the following SQL syntax in a Java application may not produce the intended results:

INSERT INTO table1 SELECT UNISTR('\00E4') FROM dual;

Escape the backslash character as follows:

INSERT INTO table1 SELECT UNISTR('\\00E4') FROM dual;