Ten flexible technologies for connecting to Oracle through JDBC

Java database connection (JDBC) APIs are a series of interfaces that allow Java programmers to access databases. Different developers have different interfaces. After using JDBC from Oracle (a large website database platform) for many years, I have accumulated many skills that allow us to better utilize the system performance and implement more functions.

1. Use the Thin driver in client software development

In terms of Java software development, Oracle (large website database platform) databases provide four types of drivers and two types of client software for application software, applets, and servlets, the other two types are used for server software such as Java stored procedures in databases. In the development of client software, we can choose the OCI driver or Thin driver. The OCI driver uses the Java localization interface (JNI) to communicate with the database through the Oracle (large website database platform) client software. The Thin driver is a pure Java driver that communicates directly with the database. To achieve the highest performance, Oracle (a large website database platform) recommends using the OCI driver in client software development, which seems to be correct. However, I recommend using the Thin driver because multiple tests show that the performance of the Thin driver is usually higher than that of the OCI driver.

2. Disable the automatic submission function to improve system performance.

The connection to the database is established for the first time. By default, the connection is in the automatic submission mode. To achieve better performance, you can disable the automatic submission function by calling the setAutoCommit () method of the Connection class with the Boolean value false parameter, as shown below:

Conn. setAutoCommit (false );

It is worth noting that once the automatic commit function is disabled, we need to manually manage transactions by calling the commit () and rollback () Methods of the Connection class.

3. Use the Statement object in dynamic SQL statements or commands with time restrictions

When executing SQL commands, we have two options: You can use the PreparedStatement object or the Statement object. No matter how many times you use the same SQL command, PreparedStatement only parses and compiles it once. When a Statement object is used, it is parsed and compiled every time an SQL command is executed. This may make you think that using a PreparedStatement object is faster than using a Statement object. However, my tests show that this is not the case in client software. Therefore, in SQL operations with time restrictions, unless SQL commands are processed in batches, we should consider using the Statement object.

In addition, using the Statement object makes it easier to write dynamic SQL commands, because we can connect strings together to create a valid SQL command. Therefore, I think the Statement object can simplify the creation and execution of dynamic SQL commands.

4. Use the helper function to format dynamic SQL commands

When creating a dynamic SQL command executed using the Statement object, we need to solve some formatting problems. For example, if we want to create an SQL command to insert the name OReilly into the table, we must replace the "" In OReilly with two connected. The best way to do this is to create a helper method to complete the replacement operation, and then use the created helper method when the connection string heart uses a public table to execute an SQL command. Similarly, we can let the helper method accept a value of the Date type, and then let it output a string expression based on the to_date () function of Oracle (large website database platform.

5. Use the PreparedStatement object to improve the overall efficiency of the database

When you use the PreparedStatement object to execute an SQL command, the command is parsed and compiled by the database, and then placed in the command buffer zone. Then, every time you execute the same PreparedStatement object, it will be parsed again, but will not be compiled again. Pre-compiled commands can be found in the buffer and can be reused. In enterprise-level applications of a large number of users, the same SQL command is often executed repeatedly. Reducing the number of compilations caused by PreparedStatement objects can improve the overall performance of the database. If the time required for creating, preparing, and executing a PreparedStatement task on the client is longer than that required by the Statement task, we recommend that you use the PreparedStatement object in all situations except the dynamic SQL command.

6. Use the PreparedStatement object in batch processing repeated insert or update operations

If insert and update operations are processed in batches, the required time can be significantly reduced. The Statement and CallableStatement provided by Oracle (large website database platform) do not really support batch processing. Only the PreparedStatement object truly supports batch processing. We can use the addBatch () and executeBatch () Methods to select a standard JDBC batch, or use the setExecuteBatch () method of the PreparedStatement object and the standard executeUpdate () method () methods select a proprietary method for faster Oracle (large website database platform. To use the dedicated Batch Processing Mechanism of Oracle (large website database platform), you can call setExecuteBatch () in the following way ():