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.
9. Use Object SQL to transfer object mode to the database. Since Oracle's database can be used as an object-oriented database, you can consider transferring object-oriented mode in the application to the database. The current method is to create a Java bean as a camouflage database object, map their properties into the relational table, and then add a method in these beans. Although there is no problem in Java, since operation is performed outside of the database, other access database applications cannot take advantage of object mode. If you use Oracle's object-oriented technology, you can use a new database object type to simulate your data and operations in the database, then use JPublisher and other tools to generate your own Java Bean class. If this is used, not only Java applications can use the application software object mode, other applications that need to share data and operations in your application can also use object patterns in the application. 10. Using SQL to complete the operation within the database I want to introduce the most important experience introduced by using SQL-oriented ways to solve the database processing needs, rather than using Java. Processing programming language. If the programmer wants to find a lot of rows in a table, each line in the result looks up the data in other tables, and finally, the programmer creates a separate UPDATE command to update the data in the first table. Tasks similar to this can be done in a UPDATE command by using a multi-column query in the Set clause. When you can complete the task in a single SQL command, how must you let the data flow from the Internet? I recommend that users carefully learn how to maximize the functionality of SQL. I don't agree with the tricks: 1. Use the Thin driver without using an OCI driver, the Thin driver is to transfer complex data conversion operations to the database server side, I don't know how the author is tested, maybe his database The server is faster than the client. In the case of a large number of clients, using the Thin driver will increase the burden on the database server, consume valuable server CPU resources, this approach is not available. One client is slower, and the server is slow, all customers will be affected. Also, when I test, I found that the Thin driver is locked in multiple threaded access to the database, and the processing performance cannot be effectively improved on the multi-CPU machine. So my suggestion is to use OCI drivers as much as possible using OCI drive. 2, use Statement instead of PreparedStatement, only occurs on a dynamic SQL statement that occurs temporarily generated, in fact, for the user queries, the dynamic SQL statement that is spelled out in most cases (except for the value), for example according to the user Setting the condition flexible constructing the SQL statement, the customer is the same in the query condition of most of the time settings, but the specific value is different. At this time, at this time, it is still better than Statement. In addition, the latest Oracle driver supports Statement Caching, using this feature to make Prepared database, directly get preparedStatement directly, in which case PREPAREDStatement is also an access database. So my suggestion is that unless your SQL statement is different for each setting (this situation rarely occurs), otherwise it should try to use PreparedStatement.
