Top 10 skills for operating Oracle databases through JDBC connection

Java database connection (JDBC) is composed of a group of classes and interfaces written in Java programming language. JDBC provides a standard API for tools/database developers to write database applications using pure Java APIs. However, the interfaces of various developers are not exactly the same, so changes in the development environment will bring about certain configuration changes. This document describes how to connect different databases.

I. Fast query of tables connected to various database Methods

The following lists the JDBC Connection Methods for various databases, which can be used as a manual.

1. oracle8/8i/9i Database (thin Mode)

Class. forname ("oracle. JDBC. Driver. oracledriver"). newinstance ();

String url = "JDBC: oracle: thin: @ localhost: 1521: orcl"; // orcl indicates the SID of the database.

String user = "test ";

String Password = "test ";

Connection conn = drivermanager. getconnection (URL, user, password );

2. DB2 database

Class. forname ("com. IBM. db2.jdbc. App. db2driver"). newinstance ();

String url = "JDBC: DB2: // localhost: 5000/sample"; // sample is your database name

String user = "admin ";

String Password = "";

Connection conn = drivermanager. getconnection (URL, user, password );

3. SQL Server7.0/2000 database

Class. forname ("com. Microsoft. JDBC. sqlserver. sqlserverdriver"). newinstance ();

String url = "JDBC: Microsoft: sqlserver: // localhost: 1433; databasename = mydb ";

// Mydb is a database

String user = "sa ";

String Password = "";

Connection conn = drivermanager. getconnection (URL, user, password );

4. Sybase Database

Class. forname ("com. Sybase. JDBC. sybdriver"). newinstance ();

String url = "JDBC: Sybase: TDS: localhost: 5007/mydb"; // mydb is your database name

Properties sysprops = system. getproperties ();

Sysprops. Put ("user", "userid ");

Sysprops. Put ("password", "user_password ");

Connection conn = drivermanager. getconnection (URL, sysprops );

5. Informix Database

Class. forname ("com. Informix. JDBC. ifxdriver"). newinstance ();

String url =

"JDBC: Informix-sqli: // 123.45.67.89: 1533/mydb: informixserver = myserver;

User = testuser; Password = testpassword "; // mydb indicates the database name.

Connection conn = drivermanager. getconnection (URL );

6. MySQL database

Class. forname ("org. gjt. Mm. MySQL. Driver"). newinstance ();

String url = "JDBC: mysql: // localhost/mydb? User = soft & Password = soft1234 & useunicode = true & characterencoding = 8859_1"

// Mydb indicates the Database Name

Connection conn = drivermanager. getconnection (URL );

7. PostgreSQL database

Class. forname ("org. PostgreSQL. Driver"). newinstance ();

String url = "JDBC: PostgreSQL: // localhost/mydb" // mydb indicates the database name.

String user = "myuser ";

String Password = "mypassword ";

Connection conn = drivermanager. getconnection (URL, user, password );

8. Access database directly connected to ODBC

Class. forname ("Sun. JDBC. ODBC. jdbcodbcdriver ");

String url = "JDBC: ODBC: Driver = {Microsoft Access Driver (*. mdb)}; DBQ =" + application. getrealpath ("/data/reportdemo. mdb ");

Connection conn = drivermanager. getconnection (URL ,"","");

Statement stmtnew = conn. createstatement ();

Ii. JDBC connection to MySQL

The following is a small tutorial on connecting to MySQL using JDBC.

1. Find the driver

MySQL currently provides the Java driver connection/J, can be downloaded from the MySQL official website, and find the mysql-connector-java-3.0.15-ga-bin.jar file, this driver is pure Java driver, do not need to do other configuration.

2. dynamically specify classpath

If you need to dynamically specify the classpath for execution, use the-CP method during execution. Otherwise, add the above. jar file to the classpath environment variable.

3. Load the driver

Try {

Class. forname (COM. MySQL. JDBC. Driver );

System. Out. println (success loading MySQL driver !);

} Catch (exception E)

{

System. Out. println (error loading MySQL driver !);

E. printstacktrace ();

}

4. Set the connection URL

JDBC: mysql: // localhost/databasename [? Pa = VA] [& pa = VA]

III. The following lists some tips you can use to connect to an Oracle database using JDBC, these skills enable us to better utilize the system performance and implement more functions (reproduced in series ).

1. Use the thin driver in client software development

In terms of Java software development, Oracle database provides four types of drivers, two types of client software for application software, applets, Servlets, and so on, the other two types are used for server software such as Java stored procedures in databases. In the development of client software, we can choose the 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. To achieve the highest performance, Oracle recommends using the OCI driver in client software development, which seems to be correct. However, I recommend using the thin driver because multiple tests show that the performance of the thin driver is usually higher than that of the OCI driver.

2. Disable the automatic submission function to improve system performance.

The connection to the database is established for the first time. By default, the connection is in the automatic submission mode. To achieve better performance, you can disable the automatic submission function by calling the setautocommit () method of the connection class with the Boolean value false parameter, as shown below:

Conn. setautocommit (false );

It is worth noting that once the automatic commit function is disabled, we need to manually manage transactions by calling the Commit () and rollback () Methods of the connection class.

3. Use the statement object in dynamic SQL statements or commands with time restrictions

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.

4. Use the Helper function to format dynamic SQL commands

When creating a dynamic SQL command executed using the statement object, we need to solve some formatting problems. 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.

5. Use the preparedstatement object to improve the overall efficiency of the database

When you use the preparedstatement object to execute an SQL command, the command is 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 the database. 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.

6. Use the preparedstatement object in batch processing repeated insert or update operations

If insert and update operations are processed in batches, the required time can be significantly reduced. 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. To use the dedicated Batch Processing Mechanism of Oracle, you can call setexecutebatch () as follows ():

Preparedstatement pstmt3d NULL;

Try {

(Oraclepreparedstatement)

Pstmt). setexecutebatch (30 );

...

Pstmt.exe cuteupdate ();

}

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.

7. Use Oracle locator to insert and update a large object (LOB)

The preparedstatement class of Oracle does not fully support processing large objects such as blob and clob. In particular, the thin driver does not support setting blob values using the setobject () and setbinarystream () Methods of the preparedstatement object, you cannot use setcharacterstream () to set the clob value. Only the locator method can obtain the value of the lob type from the database. You can use the preparedstatement object to insert or update lob, but you need to use locator to obtain the value of lob. Because of these two problems, we recommend that you use the locator method to insert, update, or obtain the value of lob.

8. Use sql92 syntax to call the Stored Procedure

When calling a stored procedure, we can use sql92 or Oracle PL/SQL. Since Oracle PL/SQL does not have any practical advantages, in addition, it will cause trouble for developers who will maintain your application in the future. Therefore, I suggest using sql92 when calling the stored procedure.

9. Use object SQL to transfer the object mode to the database

Since the Oracle database can be used as an object-oriented database, you can consider converting the object-oriented mode in the application to the database. The current method is to create Java Beans as disguised database objects, map their attributes to the relational table, and then add methods to these beans. Although this is not a problem in Java, operations are performed outside the database, so other database access applications cannot use the object mode. If you use Oracle's object-oriented technology, you can create a new database object type to simulate its data and operations in the database, and then use tools such as jpublisher to generate your own Java Bean class. If you use this method, not only can Java applications use the object mode of application software, but other applications that need to share data and operations in your application can also use the object mode of application software.

10. use SQL to perform database operations

The most important experience I would like to introduce to you is to make full use of SQL's collection-oriented method to address database processing needs, rather than using Java and other procedural programming languages.

If a programmer needs to search for many rows in a table, each row in the result searches for data in other tables. Finally, the programmer creates an independent update command to update the data in the first table in batches. Similar tasks can be completed in an update command by using multi-column subqueries in the set clause. When a task can be completed in a single SQL command, why stream data online? I suggest that you carefully learn how to maximize the SQL function.