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

The purpose of this article is to share with you what JDBC has to do with connecting to Oracle databases. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. Use Thin drivers in client software development

Use JDBC to connect to the Oracle database 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. In order to achieve the performance of * *, Oracle recommends the use of OCI drivers in client software development, which seems to be correct. 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 establishing a JDBC connection to the Oracle database for the * times, the connection is in autocommit mode by default. 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 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.

6. Use the Oracle locator method to insert and update large objects (LOB)

When JDBC connects to Oracle, the PreparedStatement class of Oracle 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 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.

7. Use SQL92 syntax to call stored procedures

We can use SQL92 or Oracle PL/SQL when calling stored procedures, and since using Oracle PL/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.

8. Transfer the object schema to the database using Object SQL

In JDBC connection Oracle, since 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 Java bean 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 Java bean 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.

Thank you for reading! This is the end of this article on "what are the skills of JDBC connecting to Oracle database?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

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.