Tutorials to .com

Tutorials to .com » Database » Oracle » Oracle database connectivity through JDBC

Oracle database connectivity through JDBC

Print View , by: iSee ,Total views: 21 ,Word Count: 1743 ,Date: Thu, 20 Aug 2009 Time: 9:59 PM

java database Connectivity (JDBC) API is a series that allows java programmers to access the database interface, each interface is not exactly the same developer. For many years in the use of Oracle's JDBC, I have accumulated a lot of skills, these skills will enable us to better system performance and to achieve more.

1, the client software development in the use of Thin driver

In the development of Java software, oracle database to provide four types of drivers for the two kinds of application software, applets, servlets, such as client software, while for the two kinds of database stored procedures, such as Java server-side software. In the client-side software development, we can choose to OCI driver or the Thin driver. OCI driver interface using Java localization (JNI), through the Oracle client software to communicate with the database. Thin driver is a pure Java driver that communicates directly with the database. In order to obtain the highest performance, Oracle recommends that the client software to use OCI driver, it appears to be correct. However, I recommend the use of Thin driver, as found through a series of trial runs in the ordinary case, Thin driver performance over the OCI driver.

2, turn off the automatic submission feature, to improve system performance

The establishment of the first connection with the database, by default, the connection is in auto-commit mode. In order to obtain better performance, you can call with boolean false class Connection parameters setAutoCommit () method to turn off the automatic submission feature, as follows:

conn.setAutoCommit (false);

It is worth noting that, once submitted to the closure of the automatic function, we need to kind of Connection by calling commit () and rollback () methods to manually carry out the management of affairs.

3, in a dynamic SQL or a time limit for the use of the command object Statement

SQL command in the implementation, we have two kinds of options: you can use PreparedStatement object, you can use Statement object. No matter how many times to use the same SQL command, PreparedStatement parsing and compiling it only once. When using the Statement object, each time a SQL command, it will parse and compile it. This may make you think, the use of PreparedStatement object using the Statement object than faster. However, my test showed that the client software is not the case. As a result, time-bound operation in SQL, unless the bulk handling of SQL command, we should consider using the Statement object.

In addition, the Statement object has made the use of dynamic SQL command to prepare a more simple, because we can connect string, the establishment of an effective SQL command. Therefore, I think, Statement objects can make dynamic SQL commands to create and easier to implement.

4, the use of helper functions for dynamic SQL command format

Statement object used in the creation of the implementation of dynamic SQL command, we need to deal with some formatting issues. For example, if we want to create a table to insert the name of O'Reilly's SQL command, you must use two connected "''" O'Reilly in the replacement of its " '" No.. Completion of these work the best way is to create a complete replacement of the helper methods of operation, and then in the connection string using the formula to express the heart of a SQL command, use the helper method to create. Similarly, helper methods so that we can accept a value of type Date, and then allow it to output based on Oracle's to_date () function of the string expression.

5, the use of the PreparedStatement object to increase the overall efficiency of the database

PreparedStatement object in the implementation of the use of SQL command, the command was parsing and compiling the database, and then the buffer zone was placed orders. Then, whenever the implementation of the same PreparedStatement object, it will be a re-analysis, but it will not be re-compiled. Can be found in the buffer zone in order pre-compiled and can be re-used. There are a large number of users in the Enterprise application software, often repeat the same SQL command, use the PreparedStatement object brought about by the compiler to reduce the number of databases to improve overall performance. If it is not created on the client, preparation, implementation of PreparedStatement tasks Statement longer than the time required for the task, I shall propose a dynamic SQL command in addition to all cases other than the use of the PreparedStatement object.

6, in the repeated batch operation to insert or update using the PreparedStatement object

If the bulk to handle insert and update operations, we can significantly reduce the time required for them. Oracle provides the Statement and CallableStatement does not really support the batch, and only really PreparedStatement object to support batch processing. We can use addBatch () and executeBatch () method to choose the standard JDBC batch, or through the use of the PreparedStatement object setExecuteBatch () methods and standards of executeUpdate () method of selection of the Oracle proprietary faster method. To use the proprietary Oracle batch mechanism to call the following manner setExecuteBatch ():

PreparedStatement pstmt3D null;

try (


pstmt). setExecuteBatch (30);


pstmt.executeUpdate ();


Call setExecuteBatch () when the specified value is a ceiling, when reached this value, it will automatically lead to the implementation of SQL commands, the standard of executeUpdate () method will be sent to the database as a batch. We can PreparedStatement class by calling sendBatch () method at any time transfer batch tasks.

7, the use of Oracle locator to insert, update large object (LOB)

Oracle's type PreparedStatement is not fully supported, such as BLOB and CLOB handling of large objects, especially the Thin driver does not support the use of the PreparedStatement object setObject () and setBinaryStream () method set the BLOB value, do not support the use of setCharacterStream () method set the CLOB value. Locator itself only way to be able to obtain the LOB from the database values. PreparedStatement objects can be used to insert or update the LOB, but need to use the locator to obtain the LOB value. Because of these two issues, I recommend the use of locator means to insert, update, or access to LOB values.

8, using the syntax to call stored procedure SQL92

In the stored procedure call, we can use SQL92 or Oracle PL / SQL, as a result of the use of Oracle PL / SQL, and no real benefits, but also to safeguard the future of your application developers to cause trouble, so I suggested that used to call stored procedure SQL92.

9, the use of Object SQL object model will be transferred to the database

Since Oracle database can be as an object-oriented database to use, we can consider the application of object-oriented model to the database. The current method is disguised as a Java bean to create the database objects, their properties will be mapped to the relationship table, and then add in these methods of bean. Although in doing so there is nothing in Java, but are in the database as a result of the operation outside, so other applications access the database object model can not be used. If you use Oracle's object-oriented technology, you can create a new database object type in the database and operating their data copied, and then use tools such as JPublisher to generate their own type of Java bean. If you use this way, not only Java applications can use the object model of application software, other applications need to share your data and operation of application software application can also use the object model.

10, the use of SQL database operations completed

I would like to introduce the most important experience is full use of SQL set oriented approach to the resolution of the database processing requirements, rather than the process of using Java and other programming languages.

If the programmers have to find a table in a number of firms, the results of each line will find the data in other tables, and finally, the programming staff to create an independent bulk UPDATE command to update the first data in the table below. Similar tasks can be set through the use of multi-clause Liezi UPDATE query in a completed order. When can a single SQL command to complete the task, why let the data stream to stream-line go? I seriously recommend that users learn how to maximize the functions of SQL.

(Editor: Heiner rivers qlmzl11268@hotmail.com TEL: (010) 68476606-8007)

Oracle Tutorial Articles

Can't Find What You're Looking For?

Rating: Not yet rated


No comments posted.