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.exe cute ();
 
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;
 
 
Click here to find out more!
 
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 need a Statement or PreparedStatement instance. Once you have a Statement or PreparedStatement, you can issue a query. In this way, a ResultSet instance is returned, containing 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 and a Statement is used to print the first field in each row.
 
Statement st = db. createStatement ();
 
ResultSet rs = st.exe cuteQuery ("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 ();
 
In this example, PreparedStatement is used to issue 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.exe cuteQuery ();
 
While (rs. next ()){
 
System. out. print ("Column 1 returned ");
 
System. out. println (rs. getString (1 ));
 
}
 
Rs. close ();
 
St. close ();
 
31.3.1. Use the Statement or PreparedStatement Interface
 
When using the Statement or PreparedStatement interface, you must consider the following issues:
 
You can use a Statement or PreparedStatement instance for any time. You can create a Statement instance immediately after opening a connection and use it during the connection life. You must remember that each Statement or PreparedStatement can have only one ResultSet.
 
If you need to execute a query when processing a ResultSet, you only need to create and use another Statement.
 
If you use threads and several databases, you must use an independent Statement for each thread. If you want to use threads, refer to Section 31.8 later in this document because the content contains important information.
 
After you have used up Statement or PreparedStatement, you should disable it.
 
31.3.2. Use the ResultSet Interface
 
When using the ResultSet interface, you must consider the following issues:
 
When reading any value, you must call next (). 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 call close () to close it.
 
Once you use the Statement for creating the ResultSet for another query request, the currently opened ResultSet instance is automatically disabled.
 
The current ResultSet is read-only. You cannot update data through ResultSet. 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.

Author: ERDP Technical Architecture"