Ten tips for connecting Oracle databases via JDBC

oracle| Skills | data | database

The Java Database Connectivity (JDBC) API is a series of interfaces that enable Java programmers to access the database, and the interfaces of each developer are not exactly the same. After years of JDBC with Oracle, I've accumulated a lot of skills that enable us to better perform system performance and achieve more functionality.

1, use the thin driver in the client software development

In the development of Java software, Oracle's database provides four types of drivers, two for application software, applets, Servlets and other client software, and two for the Java stored procedures in the database and other server-side software. In the development of client side software, we can choose OCI driver or thin driver. The OCI driver uses 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 the OCI driver in the development of the client software, which seems to be correct. However, I recommend using the thin driver because multiple tests have found that, in general, the performance of the thin driver exceeds the OCI driver.

2, turn off the automatic submission function, improve system performance

When you first establish a connection to a database, by default, the connection is in autocommit mode. For better performance, you can turn off the autocommit feature by calling the Setautocommit () method of the connection class with the Boolean false parameter, as follows:

Conn.setautocommit (FALSE);

It is noteworthy that once the autocommit feature is turned off, we need to manage the transaction manually by invoking the commit () and rollback () Methods of the connection class.

3. Use the Statement object in dynamic SQL or time constrained commands

When executing SQL commands, we have two choices: you can use the PreparedStatement object, or you can use the statement object. No matter how many times the same SQL command is used, PreparedStatement only parses and compiles it once. When the statement object is used, it is parsed and compiled each time an SQL command is executed. This may make you think that using a PreparedStatement object is faster than using a statement object. However, the tests I conducted showed that this was not the case in the client software. Therefore, in a time-constrained SQL operation, we should consider using the statement object, unless the SQL commands are processed in batches.

Also, using the statement object makes it easier to write dynamic SQL commands because we can concatenate strings together to create a valid SQL command. Therefore, I think the statement object makes it easier to create and execute dynamic SQL commands.

4. Use helper function to format dynamic SQL commands

When creating dynamic SQL commands that are 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, you must replace the "'" Number in O ' Reilly with a two-connected "" number. The best way to do this is to create a helper method that completes the substitution operation, and then use the helper method that is created when the connection string expresses an SQL command in the heart-taking formula. Similarly, we can have the helper method accept a date-type value and then let it output a string expression based on the Oracle To_date () function.

5, using PreparedStatement object to improve the overall efficiency of the database

When the SQL command is executed 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 is parsed again, but it is not compiled again. Precompiled commands can be found in the buffer and can be reused. In enterprise-class applications with a large number of users, the same SQL commands are often executed repeatedly, and the reduction in compilation times with PreparedStatement objects can improve the overall performance of the database. If it takes longer than the statement task to create, prepare, and perform preparedstatement tasks on the client, I recommend that you use the PreparedStatement object in all cases except the dynamic SQL command.

6. Use the PreparedStatement object in a batch process duplicate insert or update operation

If you process inserts and updates in batches, you can significantly reduce the time they need. The statement and CallableStatement provided by Oracle do not really support batching, and only PreparedStatement objects truly support batch processing. We can use the Addbatch () and ExecuteBatch () methods to select standard JDBC batches, or by using the Setexecutebatch () method of the PreparedStatement object and the standard executeupdate () method to choose a faster Oracle proprietary approach. To use the Oracle proprietary batch mechanism, you can invoke Setexecutebatch () in the manner shown below:
PreparedStatement Pstmt3d null;
try {
((oraclepreparedstatement)
pstmt). Setexecutebatch (30);
...
Pstmt.executeupdate ();
}

The value specified when calling Setexecutebatch () is an upper bound, and when that value is reached, SQL command execution is automatically raised, and the standard Executeupdate () method is sent as a batch to the database. We can transfer batch tasks at any time by invoking the Sendbatch () method of the PreparedStatement class.

7. Insert and update large objects (LOB) using Oracle Locator method

Oracle's PreparedStatement classes do not fully support the processing of large objects such as blobs and clob, especially thin drivers do not support SetObject () and Setbinarystream using PreparedStatement objects () method to set the value of a blob, nor does it support the use of the Setcharacterstream () method to set CLOB values. 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 the LOB, but you need to use locator to get the value of the LOB. Because of these two problems, I recommend using the Locator method to insert, update, or get LOB values.

8. Using SQL92 syntax to invoke stored procedures

When invoking a stored procedure, we can use SQL92 or Oracle Pl/sql, because there is no real benefit from using Oracle Pl/sql, and it can cause problems for future developers who maintain your application, so I recommend using SQL92 when calling stored procedures.

9, using object SQL to transfer the objects mode to the database

Since Oracle's databases can be used as an object-oriented database, you can consider going to the database for object-oriented patterns in your application. The current approach is to create Java beans as camouflaged database objects, map their properties to relational tables, and then add methods to those beans. Although this is not a problem in Java, because operations are performed outside the database, other applications that access the database cannot take advantage of object patterns. If you take advantage of Oracle's object-oriented technology, you can emulate its data and operations in a database by creating a new database object type, and then generate your own Java bean classes using tools such as Jpublisher. In this way, not only can Java applications use the object schema of the application software, but other applications that need to share data and operations in your application can also use object patterns in the application software.

10, the use of SQL to complete the operation of the database

The most important experience I want to introduce to you is to take full advantage of SQL's collection-oriented approach to database processing requirements, rather than using procedural programming languages such as Java.

If a programmer wants to find many rows in a table, each row in the result looks for data from another table, and finally, the programmer creates a separate update command to batch update the data in the first table. Tasks similar to this can be done in an update command by using a multiple-column subquery in the SET clause. When you can complete a task in a single SQL command, why should you let the data flow over the Internet? I recommend that users carefully learn how to maximize the functionality of SQL.