The Java Database Connectivity (JDBC) API is a series of interfaces that allow Java programmers to access databases. The interfaces of various developers are not exactly the same. After using Oracle's JDBC for many years, I have accumulated many skills that can enable us to better utilize the performance of the system and achieve more functions.
1. Use Thin drivers in client software development
. In terms of developing Java software, Oracle's database provides four types of drivers, two of which are used for client software such as application software, applets, and servlets, and the other two are used for client software such as application software, applets, and servlets. Server-side software such as Java stored procedures in databases. In the development of client-side software, we can choose OCI driver or Thin driver. The OCI driver utilizes 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 OCI drivers in the development of client software, and this seems to be correct. But I recommend using the Thin driver, because through many tests it has been found that the performance of the Thin driver exceeds that of the OCI driver under normal circumstances.
2. Turn off the automatic submission function to improve system performance.
When establishing a connection with the database for the first time, by default, the connection is in automatic submission mode. For better performance, you can turn off the autocommit function by calling the setAutoCommit() method of the Connection class with a Boolean false parameter, as follows:
conn.setAutoCommit(false);
It is worth noting that once the autocommit function is turned off , we need to manually manage the transaction by calling the commit() and rollback() methods of the Connection class.
3. Use Statement objects in dynamic SQL or time-limited commands
. When executing SQL commands, we have two options: you can use PreparedStatement objects or Statement objects. No matter how many times you use the same SQL command, PreparedStatement only parses and compiles it once. When using the Statement object, each time a SQL command is executed, it is parsed and compiled. This might make you think that using PreparedStatement objects is faster than using Statement objects. However, testing I've done shows that in client software this is not the case. Therefore, in time-limited SQL operations, unless SQL commands are processed in batches, we should consider using Statement objects.
In addition, using Statement objects also makes writing dynamic SQL commands easier because we can concatenate strings together to build a valid SQL command. Therefore, I believe 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 Statement objects, we need to deal with some formatting issues. For example, if we want to create an SQL command that inserts the name O'Reilly into a table, we must replace the "'" sign in O'Reilly with two consecutive "''" signs. The best way to accomplish these tasks is to create a helper method that performs the replacement operation, and then use the created helper method when the connection string is expressed as a SQL command using a formula. Similarly, we can have the helper method accept a Date value and then have it output a string expression based on Oracle's to_date() function.
5. Use PreparedStatement objects to improve the overall efficiency of the database.
When using PreparedStatement objects 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 not compiled again. Precompiled commands are found in the buffer and can be reused. In enterprise-level applications with a large number of users, the same SQL commands are often executed repeatedly. The reduction in the number of compilations brought about by using PreparedStatement objects can improve the overall performance of the database. Unless creating, preparing, and executing PreparedStatement tasks on the client side takes longer than Statement tasks, I would recommend using PreparedStatement objects in all cases except for dynamic SQL commands.
6. Use PreparedStatement objects to process repeated insert or update operations in batches.
If you process insert and update operations in batches, you can significantly reduce the time they require. The Statement and CallableStatement provided by Oracle do not really support batch processing. Only the PreparedStatement object really supports batch processing. We can choose standard JDBC batch processing using the addBatch() and executeBatch() methods, or choose the faster Oracle proprietary method by utilizing the PreparedStatement object's setExecuteBatch() method and the standard executeUpdate() method. To use Oracle's proprietary batching mechanism, you can call setExecuteBatch() as follows:
PreparedStatement pstmt3D null;
try {
((OraclePreparedStatement)
pstmt).setExecuteBatch(30);
...
pstmt.executeUpdate();
}
The value specified when calling setExecuteBatch() is an upper limit. When this value is reached, SQL command execution will be automatically triggered, and the standard executeUpdate() method will be sent to the database as a batch process. We can transfer batch tasks at any time by calling the sendBatch() method of the PreparedStatement class.
7. Use the Oracle locator method to insert and update large objects (LOB).
Oracle's PreparedStatement class does not fully support the processing of large objects such as BLOB and CLOB. In particular, the Thin driver does not support setting using the setObject() and setBinaryStream() methods of the PreparedStatement object. The value of BLOB does not support setting the value of CLOB using the setCharacterStream() method. Only methods in the locator itself can obtain LOB type values from the database. You can use PreparedStatement objects to insert or update LOBs, but you need to use a locator to get the value of the LOB. Due to these two problems, I recommend using the locator method to insert, update or obtain LOB values.
8. Use SQL92 syntax to call stored procedures
. When calling stored procedures, we can use SQL92 or Oracle PL/SQL. There are no practical benefits to using Oracle PL/SQL, and it will bring trouble to developers who will maintain your application in the future. Comes to trouble, therefore, I recommend using SQL92 when calling stored procedures.
9. Use Object SQL to transfer the object schema to the database.
Since Oracle's database can be used as an object-oriented database, you can consider transferring the object-oriented schema in the application to the database. The current approach is to create Java beans as disguised database objects, map their properties into relational tables, and then add methods to these beans. Although there is no problem in doing this in Java, because the operations are performed outside the database, other application software that accesses the database cannot take advantage of the object model. If you take advantage of Oracle's object-oriented technology, you can simulate its data and operations in the database by creating a new database object type, and then use tools such as JPublisher to generate your own Java bean classes. If you use this method, not only Java applications can use the object model of the application software, but other application software that needs to share the data and operations in your application can also use the object model of the application software.
10. Use SQL to complete operations in the database.
The most important experience I want to introduce to you is to make full use of SQL's set-oriented approach to solve database processing needs, rather than using procedural programming languages such as Java.
If the programmer were to look up many rows in one table, each row in the result would look up data in other tables. Finally, the programmer created separate UPDATE commands to update the data in the first table in batches. A task similar to this can be accomplished in an UPDATE command by using a multi-column subquery in the set clause. Why let data flow around the Internet when you can accomplish the task in a single SQL command? I recommend that users carefully learn how to maximize the power of SQL.
Author: picva Source: CCID Technology Community