Increase processing speed by processing JDBC statements in batches

Sometimes JDBC is not ideal enough, which prompts us to write some stored procedures related to specific databases. As a replacement solution, try the batch processing feature of statement to see if any SQL statement can increase the speed at a time.

The simplest form of a stored procedure is that the entire process contains only one set of SQL statements. Putting these statements together can be easy to manage and improve the running speed. The statement class can contain a series of SQL statements. Therefore, it allows all SQL statements to be executed in a database session, thus avoiding a series of execution calls to the database.

The batch processing function involves two methods:

Addbatch (string) Method
Executebatch Method
The addbatch method can accept a standard SQL statement (if you use a statement) as a parameter, or it can be left blank (if you use a preparedstatement ).

The executebatch method then executes the SQL statement and returns an int array. This array includes the number of rows affected by each statement. If you use a SELECT statement or other statements that only return results in a batch, an sqlexception exception will occur.

The following is a simple example of Java. SQL. Statement:

Statement stmt = conn. createstatement ();
Stmt. insert ("delete from users ");
Stmt. insert ("insert into users values ('rod ', 37, 'circle ')");
Stmt. insert ("insert into users values ('Jane ', 33, 'trigger ')");
Stmt. insert ("insert into users values ('freddy ', 29, 'square ')");

Int [] counts = stmt.exe cutebatch ();

Preparedstatement is slightly different. It can process only one SQL statement, but can contain many parameters. The example above is rewritten using preparedstatement:

// Note that we have not performed any Delete action

Preparedstatement stmt = conn. preparestatement (_
"Insert into users values (?,?,?) ");
User [] users = ...;
For (INT I = 0; I <users. length; I ++)

{
Stmt. setint (1, users [I]. getname ());
Stmt. setint (2, users [I]. getage ());
Stmt. setint (3, users [I]. getshape ());
Stmt. addbatch ();
}
Int [] counts = stmt.exe cutebatch ();

This is a good way to process SQL code that does not know the number of executions. No batch processing. If you want to add 50 users, the performance may be affected. If someone writes a script to add 10000 users, the running speed will be unbearable. Adding batch processing can improve the performance. In the latter case, it can even improve the readability of the Code.

Use preparedstatement to reduce development time

Java. SQL. statement, one of the four main classes in JDBC APIs, requires developers to spend a lot of time and effort. One common problem when using statement to obtain JDBC access is to enter a date and timestamp in the appropriate format: Or 02/05/02 PM. By using Java. SQL. preparedstatement, this problem can be automatically solved. A preparedstatement is obtained from the java. SQL. connection object and the provided SQL string. The SQL string contains the question mark (?), These question marks indicate the location of the variable, then provide the value of the variable, and finally execute the statement, for example:

Stringsql = "SELECT * FROM People p WHERE p.id = ? AND p.name = ?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setInt(1,id);
ps.setString(2,name);
ResultSet rs = ps.execute(); 

Another advantage of using preparedstatement is that strings are not dynamically created. The following is an example of dynamically creating strings:

Stringsql = "SELECT * FROM People p WHERE p.i = "+id; 

& Lt; a href = "http://ad.cn.doubleclick.net/click%3Bh=v2 | 3065 | 3 | 0 | % 2a | D % 3b6674094% 3b0-0% 3b0% 3b8394480% 3b%2-1 | 1% 3b3892012 | 3909908 3B % 3B % 7 efdr % 3d6407221% 3b0-0% 3b0% 3b6694709% 3b31-1 | 1% 3b3640229 | 3658125 | 1% 3B % 3B % 3 fhttp: // www.oracle.com/go /? & Amp; src = 2039880 & amp; Act = 388 "target = _ top & gt; & lt; IMG src =" http://m2.cn.doubleclick.net/714347/10gdata_336x280.gif "border = 0 & gt; & lt;/A & gt;

This allows JVM (javavirtual machine, Java Virtual Machine) and driver/database cache statements and strings and improves performance.

Preparedstatement also provides database independence. When less declared SQL statements are displayed, the database dependency of potential SQL statements is smaller.

Because preparedstatement has many advantages, developers may usually use it, and use the normal statement only when it is completely because of performance or when there is no variable in a row of SQL statements.

Issue query and processing results

When you want to run an SQL statement to the database, you needStatementOrPreparedstatementInstance. Once you haveStatementOrPreparedstatementYou can issue a query. This will returnResultsetInstance that contains the entire result. Example 31-1 demonstrates this process.

Example 31-1. process a simple query in JDBC

In this example, a simple query is sent andStatementPrint the first field in each line.

Statement st = db.createStatement();ResultSet rs = st.executeQuery("SELECT * FROM mytable WHERE columnfoo = 500");while (rs.next()) {    System.out.print("Column 1 returned ");    System.out.println(rs.getString(1));}rs.close();st.close();

This example usesPreparedstatementIssue the same query as the previous one and create a value in the query.

int foovalue = 500;PreparedStatement st = db.prepareStatement("SELECT * FROM mytable WHERE columnfoo = ?");st.setInt(1, foovalue);ResultSet rs = st.executeQuery();while (rs.next()) {    System.out.print("Column 1 returned ");    System.out.println(rs.getString(1));}rs.close();st.close();

31.3.1. Use StatementOr PreparedstatementInterface

In useStatementOrPreparedstatementThe following issues must be taken into account during the interface:

• You canStatementOrPreparedstatementThe instance is used for any time. You can createStatementInstance and used in the lifetime of the connection. You must remember everyStatementOrPreparedstatementOnly oneResultset.

• If you need to processResultsetTo execute a query, you only need to create and use anotherStatement.

• If you use threads (thread) and several databases, you must use an independentStatement. If you want to use threads, refer to section 31.8 later in this document because the content contains important information.

• When you run outStatementOrPreparedstatementThen, you should close it.

31.3.2. Use Resultset(Result set) Interface

UseResultsetThe following issues must be taken into account during the interface:

• You must callNext (). If there are still results, true is returned, but more importantly, it prepares data rows for processing.

• In the JDBC specification, you should only access one field once. Following this rule is the safest, but currently the PostgreSQL driver will allow you to access a field for any time.

• Once you finish processing a resultset, you must callClose ()To close it.

• Once you use the createdResultsetOfStatementFor another query request, the currently openedResultsetThe instance is automatically disabled.

• CurrentResultsetIs read-only. You cannot passResultsetTo update data. If you want to update the data, you need to use the common method: by issuing an SQL update statement. This is compatible with the JDBC specification and does not require the driver to provide support for updatable result sets.