Article source: Database Forum Author: jackma
The Java Database Connectivity (JDBC) API is a series of interfaces that allow Java programmers to access databases. The interfaces of various developers are not exactly the same. After using Oracle's JDBC for many years, I have accumulated many skills that can enable us to better utilize the performance of the system and achieve more functions.
1. Use Thin driver in client software development
In terms of developing Java software, Oracle's database provides four types of drivers, two for client software such as application software, applets, and servlets, and the other two for server-side software such as Java stored procedures in the database. In the development of client-side software, we can choose OCI driver or Thin driver. The OCI driver utilizes the Java Localization Interface (JNI) to communicate with the database through Oracle client software. The Thin driver is a pure Java driver that communicates directly with the database. For maximum performance, Oracle recommends using OCI drivers in the development of client software, and this seems to be correct. But I recommend using the Thin driver, because through many tests it has been found that the performance of the Thin driver exceeds that of the OCI driver under normal circumstances.
2. Turn off the automatic submission function to improve system performance
When you first establish a connection to the database, the connection is in autocommit mode by default. For better performance, you can turn off the autocommit feature by calling the setAutoCommit() method of the Connection class with a Boolean false parameter, as shown below:
conn.setAutoCommit(false);
It is worth noting that once the auto-commit function is turned off, we need to manually manage the transaction by calling the commit() and rollback() methods of the Connection class.
3. Use Statement objects in dynamic SQL or time-limited commands
When executing SQL commands, we have two options: you can use PreparedStatement objects or Statement objects. No matter how many times you use the same SQL command, PreparedStatement only parses and compiles it once. When using the Statement object, each time a SQL command is executed, it is parsed and compiled. This might make you think that using PreparedStatement objects is faster than using Statement objects. However, testing I've done shows that in client software this is not the case. Therefore, in time-limited SQL operations, unless SQL commands are processed in batches, we should consider using Statement objects.
In addition, using Statement objects also makes writing dynamic SQL commands easier because we can concatenate strings together to build a valid SQL command. Therefore, I believe that the Statement object can make the creation and execution of dynamic SQL commands easier.
4. Use helper functions to format dynamic SQL commands
When creating dynamic SQL commands executed using Statement objects, we need to deal with some formatting issues. For example, if we want to create an SQL command that inserts the name O'Reilly into a table, we must replace the "'" sign in O'Reilly with two consecutive "''" signs. The best way to do this is to create a helper method that performs the replacement operation, and then use the created helper method when the connection string is expressed as a SQL command using a formula. Similarly, we can have the helper method accept a Date value and then have it output a string expression based on Oracle's to_date() function.
5. Use PreparedStatement objects to improve the overall efficiency of the database
When using a PreparedStatement object to execute a SQL command, the command is parsed and compiled by the database, and then placed in the command buffer. Then, whenever the same PreparedStatement object is executed, it will be parsed again, but not compiled again. Precompiled commands are found in the buffer and can be reused. In enterprise-level applications with a large number of users, the same SQL commands are often executed repeatedly. The reduction in the number of compilations brought about by using PreparedStatement objects can improve the overall performance of the database. Unless creating, preparing, and executing PreparedStatement tasks on the client side takes longer than Statement tasks, I would recommend using PreparedStatement objects in all cases except for dynamic SQL commands.
6. Use PreparedStatement objects in batch processing of repeated insert or update operations <br /> <br /> If insert and update operations are processed in batches, the time required for them can be significantly reduced. The Statement and CallableStatement provided by Oracle do not really support batch processing. Only the PreparedStatement object really supports batch processing. We can choose standard JDBC batch processing using the addBatch() and executeBatch() methods, or choose the faster Oracle proprietary method by utilizing the PreparedStatement object's setExecuteBatch() method and the standard executeUpdate() method. To use Oracle's proprietary batching mechanism, you can call setExecuteBatch() as shown below:
PreparedStatement pstmt3D null;
try {
((OraclePreparedStatement)
pstmt).setExecuteBatch(30);
...
pstmt.executeUpdate();
}