Java Database Connection (JDBC) API is a series of interfaces that enable Java programmers to access databases, and the interfaces of each developer are not exactly the same. After using years of Oracle's JDBC, I have accumulated many techniques that allow us to better play the system's performance and achieve more features. 1. Use the Thin driver in the development of Java software in the client software development, Oracle's database provides four types of drivers, two client software such as application software, applets, servlets, and two other kinds Server-side software such as Java stored procedures in the database. In the development of client-end software, we can select an OCI driver or Thin driver. The OCI driver uses 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. In order to achieve the highest performance, Oracle recommends using an OCI driver in the development of client software, which seems correct. But I recommend using Thin drivers because it is discovered by multiple tests, in general, the performance of the Thin driver exceeds the OCI driver. 2. Turn off the automatic submission function, improve system performance When the connection with the database is established for the first time, the connection is in automatic submission mode by default. In order to get better performance, you can turn off the automatic submission function by calling the setOCommit () method of the Connection class with Pooler FALSE parameters, as shown below: Conn.setAutocommit (false); It is worth noting that automatic submission function is closed We need to manage transactions by calling the COMMIT () and rollback () methods to call the CONNECTION class. 3. When using the Statement object in dynamic SQL or time-limited commands, we have two options: You can use the PreparedStateMent object, or you can use the Statement object. No matter how many times use the same SQL command, PreparedStatement is only parsed and compiled once. When using the Statement object, it is parsed and compiled each time a SQL command is executed. This may make you think that using the PreparedStateMent object is faster than using the Statement object. However, the test I conducted indicated that in the client software, the situation is not the case. Therefore, in the SQL operation with time-limited, we should consider using the Statement object unless divided by the SQL command. In addition, using the Statement object also makes the writing dynamic SQL commands easier because we can connect strings together to create a valid SQL command. So I think the Statement object can make the creation and execution of the dynamic SQL command easier. 4. When using the Helper function to format the dynamic SQL command to create a dynamic SQL command executed using the Statement object, we need to handle some formatted issues. For example, if we want to create a SQL command to insert the name O'Reilly into the table, you must use the "''" number of O'Reilly in two. Completing the best way to do this is to create a Helper method that completes the replacement operation, and then uses a SQL command when connecting a string to express a SQL command.
Similarly, we can let the Helper method accept a DATE-type value and then allow it to output a string expression based on Oracle to_date () function. 5. When using the PreparedStatement object to improve the overall efficiency of the database When using the PreparedStateMent object, 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 resolved once, but it will not be compiled again. A pre-compiled command can be found in the buffer and can be reused. In an enterprise application with a large number of users, the same SQL commands are often repeated, and the reduction in the number of compilations that use the PreparedStatement object can improve the overall performance of the database. If you are not created, prepared, executing the pre previoustement task is longer than the Statement task, and I will recommend using the PreparedStateMent object in all situations other than the dynamic SQL command. 6. Using the PreparedStatement object in a batch repetition insertion or update operation, if the insertion and update operation is processed, it is possible to significantly reduce the time they need. Statement and CallableStatement provided by Oracle do not really support batch, only PreparedStatement objects really support batch. We can use Addbatch () and Executebatch () methods to select a standard JDBC batch, or select the faster Oracle proprietary approach by using the setEcuteBatch () method of the PreparedStatement object and the standard executeUpdate () method. To use the Oracle's proprietary batch mechanism, setExecutebatch () can be called in the following manner; try {(OraclePreParedStatement) PSTMT) .SETEXECUTEBATCH (30); ... pstmt.executeUpdate ();} When you call setExecutebatch (), the value specified is a upper limit. When this value is reached, the SQL command is automatically executed, and the standard executeUpdate () method will be sent as a batch to the database. We can transfer batch tasks at any time by calling the SendBatch () method of the PreparedStatement class. 7. Using Oracle Locator methods, update large objects (LOB) Oracle's PreparedStateMent class does not fully support large objects such as BLOB and CLOB, especially the Thin driver does not support setObject () and setBinaryStream () methods using preparedStatement objects. The value of the blob also does not support the value of using the setcharacterstream () method. Only the method in the Locator itself can get the value of the LOB type from the database. You can use the PreparedStateMent object to insert or update LOB, but you need to use Locator to get the value of the LOB. Due to these two problems, I recommend using Locator's way to insert, update, or get the value of the LOB. 8. Use the SQL92 syntax to call the stored procedure when calling the stored procedure, we can use SQL92 or Oracle PL / SQL, because there is no practical benefit using Oracle PL / SQL, and will give developers who have maintained your application later Come trouble, therefore, I recommend using SQL92 when calling the stored procedure.
