Oracle Database linked instance resolution

This article mainly describes some examples of Oracle Database Connection, including detailed analysis of the actual application of the Statement object, if you are a beginner in the practical application of Oracle database links, I believe this article will help you a lot.

Use of Statement object

The Statement object is indispensable for database connection. The Statement object is a very important object in JDBC programming. It contains SQL Statement query and update operations, the Statement object can be used to query and update databases. It can be understood that the Statement object is the carrier for operating database data.

When executing SQL commands, we have two options: You can use the PreparedStatement object or the Statement object. No matter how many times you use the same SQL command, PreparedStatement only parses and compiles it once. When a Statement object is used, it is parsed and compiled every time an SQL command is executed.

This may make you think that using a PreparedStatement object is faster than using a Statement object. However, my tests show that this is not the case in client software. Therefore, in SQL operations with time restrictions, unless SQL commands are processed in batches, we should consider using the Statement object.

In addition, using the Statement object makes it easier to write dynamic SQL commands, because we can connect strings together to create a valid SQL command. Therefore, I think the Statement object can simplify the creation and execution of dynamic SQL commands.

Format in dynamic SQL commands

When using the dynamic SQL command executed by the Statement object during the link process, we need to deal with some formatting issues. For example, if we want to create an SQL command to insert the name 'Reilly into the table, we must replace the "'" In 'Reilly with the two connected.

The best way to do this is to create a helper method to complete the replacement operation, and then use the created helper method when the connection string heart uses a public table to execute an SQL command. Like this, we can let the helper method accept a value of the Date type, and then let it output a string expression based on the Oracle to_date () function.

How to Improve database efficiency

When we use the PreparedStatement object to execute the SQL command, the preparedstatement is the jdbc api, which also creates other resources related to the specific database. Commands are parsed and compiled by the database and then placed in the command buffer zone. Then, every time you execute the same PreparedStatement object, it will be parsed again, but will not be compiled again.

Pre-compiled commands can be found in the buffer and can be reused. In enterprise-level applications of a large number of users, the same SQL command is often executed repeatedly. Reducing the number of compilations caused by PreparedStatement objects can improve the overall performance of Oracle databases. If the time required for creating, preparing, and executing a PreparedStatement task on the client is longer than that required by the Statement task, we recommend that you use the PreparedStatement object in all situations except the dynamic SQL command.

Batch insert and update operations

Do not add too many indexes to a table because the index affects the speed of batch insertion and update. The Statement and CallableStatement provided by Oracle do not really support batch processing. Only the PreparedStatement object actually supports batch processing. We can use the addBatch () and executeBatch () Methods to select a standard JDBC batch, or use the setExecuteBatch () method of the PreparedStatement object and the standard executeUpdate () method () method: select an Oracle proprietary method that is faster.

When setExecuteBatch () is called, the specified value is an upper limit. When this value is reached, SQL command execution is automatically triggered. Standard executeUpdate () the method will be sent to the database as a batch. You can call the sendBatch () method of the PreparedStatement class to transfer a batch task at any time.

The above is an example of how to link Oracle databases.