Shulou(Shulou.com)06/02 Report--

This article mainly explains "how to connect Oracl through JDBC". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn how to connect Oracl through JDBC.

1. Use Thin drivers in client software development

In the development of Java software, the Oracle database provides four types of drivers, two for application software, applets, servlets and other client software, and the other two for server-side software such as Java stored procedures in the database. 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 the Oracle client software. The Thin driver is a pure Java driver that communicates directly with the database. For maximum performance, it seems correct that Oracle recommends the use of OCI drivers in the development of client software. However, I recommend using the Thin driver, because several tests have found that the performance of the Thin driver outperforms the OCI driver in general.

2. Turn off the auto-commit function to improve the system performance.

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

Conn.setAutoCommit (false)

It is worth noting that once autocommit 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 the SQL command, we have two choices: we can use either the PreparedStatement object or the Statement object. No matter how many times you use the same SQL command, PreparedStatement parses and compiles it only once. When using the Statement object, each time a SQL command is executed, it is parsed and compiled. This may make you think that using PreparedStatement objects is faster than using Statement objects. However, my tests show that this is not the case in client software. Therefore, in time-bound SQL operations, unless SQL commands are processed in batches, we should consider using Statement objects.

In addition, 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 that the Statement object can make it easier to create and execute dynamic SQL commands.

4. Using helper function to format dynamic SQL commands.

When creating dynamic SQL commands that are executed using Statement objects, we need to deal with some formatting issues. For example, if we want to create a SQL command that inserts the name O'Reilly into the table, we must replace the "'" sign in O'Reilly with two contiguous "'" signs. The best way to do this is to create a helper method that completes the replacement operation, and then use the created helper method when concatenating a string heart formula to express a SQL command. 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. Improve the overall efficiency of the database by using PreparedStatement objects.

When a 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 not compiled again. Precompiled commands can be found in the buffer and can be reused. In enterprise applications with a large number of users, the same SQL commands are often executed repeatedly, and the reduction of compilation times caused by the use of PreparedStatement objects can improve the overall performance of the database. If it didn't take longer to create, prepare, and execute PreparedStatement tasks on the client side than Statement tasks, I would recommend using PreparedStatement objects in all cases except dynamic SQL commands.

How to connect to Oracl through JDBC

6. Use PreparedStatement objects in batch repetitive insert or update operations

If insert and update operations are processed in batches, the time required for them can be significantly reduced. The Statement and CallableStatement provided by Oracle do not really support batch processing, only the PreparedStatement object does. We can use the addBatch () and executeBatch () methods to select standard JDBC batches, or we can select faster Oracle proprietary methods by using the setExecuteBatch () method of the PreparedStatement object and the standard executeUpdate () method. To use Oracle's proprietary batch mechanism, you can call setExecuteBatch () as follows:

PreparedStatementpstmt3Dnull

Try {

((OraclePreparedStatement))

Pstmt) .setExecuteBatch (30)

...

Pstmt.executeUpdate ()

}

When setExecuteBatch () is called, the specified value is an upper limit, and when this value is reached, the SQL command execution is automatically triggered, and the standard executeUpdate () method is sent to the database as a batch. We can transfer batch tasks at any time by calling the sendBatch () method of the PreparedStatement class.

7. Use the Oraclelocator method to insert and update large objects (LOB)

Oracle's PreparedStatement class does not fully support the handling of large objects such as BLOB and CLOB, especially the Thin driver does not support the use of the setObject () and setBinaryStream () methods of PreparedStatement objects to set the value of BLOB, nor does it support the use of the setCharacterStream () method to set the value of CLOB. Only methods in locator itself can get values of type LOB 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 LOB. Because of these two problems, I recommend using the method of locator to insert, update, or get the value of LOB.

8. Use SQL92 syntax to call stored procedures

We can use SQL92 or OraclePL/SQL when calling stored procedures, and since using OraclePL/SQL has no practical benefits and can cause trouble for developers who maintain your application in the future, I recommend using SQL92 when calling stored procedures.

9. Use ObjectSQL to transfer the object schema to the database

Now that you can use Oracle's database as an object-oriented database, you can consider transferring the object-oriented schema in your application to the database. The current approach is to create Javabean as disguised database objects, map their properties to relational tables, and then add methods to these bean. Although there is no problem with this in Java, because the operations are done outside the database, other applications that access the database cannot take advantage of the object schema. If you use Oracle's object-oriented technology, you can create a new database object type to mimic its data and operations in the database, and then use tools such as JPublisher to generate your own Javabean classes. If you use this approach, not only Java applications can use the object schema of the application, but other applications that need to share the data and operations in your application can also use the object schema of the application.

10. Use SQL to complete the operations in the database

The most important lesson 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 looks for many rows in a table, each row in the result looks for data in another table, and finally, the programmer creates a separate UPDATE command to update the data in the first table in batches. A similar task can be accomplished in a single UPDATE command by using multiple-column subqueries in the set clause. Why let data flow around the web when you can accomplish a task in a single SQL command? I suggest that users seriously learn how to maximize the functionality of SQL.

Thank you for your reading, the above is the content of "how to connect Oracl through JDBC". After the study of this article, I believe you have a deeper understanding of how to connect Oracl through JDBC, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.