Ten skills of connecting Oracle database through JDBC

The Java database connection (JDBC) API is a series of interfaces that allow Java programmers to access the database, and the interfaces of various developers are not exactly the same. After using Oracle's JDBC for many years, I have accumulated many skills, which can enable us to give better play to the performance of the system and realize more functions.

1. Use thin driver in the development of client software. In the development of Java software, Oracle database provides four types of drivers, two for application software, applets, servlets and other client software, and the other two for Java storage process and other server software in the database. In the development of client-side software, we can choose OCI driver or thin driver. OCI drivers utilize Java localized interfaces (JNI) communicates with the database through the Oracle client software. The thin driver is a pure Java driver, which communicates directly with the database. In order to obtain the highest performance, Oracle recommends using the OCI driver in the development of the client software, which seems to be correct. However, I recommend using the thin driver, because it is found through multiple tests that under normal circumstances The performance of thin driver exceeds that of OCI driver. 2. Turn off the automatic submission function to improve system performance. When establishing a connection with the database for the first time, the connection is in the automatic submission mode by default. For better performance, you can turn off the auto commit function by calling the setautocommit() method of the connection class with the 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 the statement object in dynamic SQL or time limited commands. When executing SQL commands, we have two options: Preparedstatement object or statement object. No matter how many times you use the same SQL command, Preparedstatement parses and compiles it only once. When using the statement object, it will be parsed and compiled every time an SQL command is executed. This may make you think that using Preparedstatement object is faster than using statement object. However, my tests show that this is not the case in client software. Therefore, in time limited SQL operations, unless SQL commands are processed in batches, we should consider using the statement object. In addition, using the statement object also makes it easier to write dynamic SQL commands, because we can connect strings together to establish an effective SQL command. Therefore, I think that the statement object can make the creation and execution of dynamic SQL commands easier. 4. Use the helper function to format dynamic SQL commands. When creating dynamic SQL commands executed using the statement object, we need to deal with some formatting problems. For example, if we want to create an SQL command that inserts the name O'Reilly into the table, we must replace the "'' number in O'Reilly with two connected" '' numbers. The best way to complete these tasks is to create a helper method to complete the replacement operation, and then use the created helper method when the connection string formula expresses an SQL command. Similarly, we can let the helper method accept a date value, and then let it output Oracle based to_ String expression for the date() function. 5. Use the Preparedstatement object to improve the overall efficiency of the database. When using the Preparedstatement object to execute SQL commands, the commands are 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 it will not be compiled again. Precompiled commands can be found in the buffer and can be reused. In enterprise application software with a large number of users, the same SQL commands are often executed repeatedly. The reduction of compilation times caused by using Preparedstatement object can improve the overall performance of the database. If it takes longer to create, prepare and execute the Preparedstatement task on the client than the statement task, I would recommend using the Preparedstatement object in all cases except dynamic SQL commands.

6. Use the Preparedstatement object in batch processing of repeated insert or update operations. If batch processing of insert and update operations can significantly reduce their time. The statement and callablestatement provided by Oracle do not really support batch processing. Only Preparedstatement objects really support batch processing. We can use the addbatch () and executebatch () methods to select a standard JDBC batch, or we can use the setexecutebatch () method and the standard executeupdate () method of the Preparedstatement object to select a faster Oracle specific method. To use Oracle's proprietary batch processing mechanism, setexecutebatch() can be called as follows:

PreparedStatement pstmt3D null; try { ((OraclePreparedStatement) pstmt).setExecuteBatch(30); ... pstmt.executeUpdate(); }