Java Database 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 are some tips you can use to connect to an Oracle database using JDBC:

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.

Basic JDBC tutorial-driver settings
1. Overview
The drivermanager class is the management layer of JDBC and acts between users and drivers. It tracks available drivers and establishes connections between the database and the corresponding drivers. In addition, the drivermanager class also processes transactions such as the driver logon time limit and the display of logon and tracing messages.

For simple applications, the only method that programmers need to directly use in this class is drivermanager. getconnection. As shown in the name, this method establishes a connection with the database. JDBC allows you to call the drivermanager method getdriver, getdrivers, registerdriver, and driver method connect. However, in most cases, it is best to set up the connection details for drivermanager class management.
1. Track available drivers
The drivermanager class contains a column of driver classes that have been registered by calling the drivermanager. registerdriver method. All driver classes must contain a static part. It creates an instance of this class and registers the drivermanager class when the instance is loaded. In this way, the user will not directly call drivermanager. registerdriver, but will be automatically called by the driver when loading the driver. There are two ways to load the driver class and then automatically register in drivermanager:
Call class. forname. This will explicitly load the driver class. Because this is irrelevant to external settings, we recommend that you use this method to load the driver. Run the following code to load acme. DB. DRIVER:
Class. forname ("Acme. DB. Driver ");

If you set Acme. DB. the driver program creates an instance when it is loaded and calls the drivermanager with the instance as the parameter. registerdriver (this should have been the case) is included in the driver list of drivermanager and can be used to create a connection.
Add the driver to the JDBC. Drivers attribute of Java. Lang. system. This is a list of Driver Class names loaded by the drivermanager class, separated by a colon: it searches for System Properties JDBC when initializing the drivermanager class. drivers. If you have entered one or more drivers, the drivermanager class will try to load them. The following code describes how programmers can ~ /. Enter three Driver classes in hotjava/properties (at startup, hotjava loads them to the system property list ):

JDBC. Drivers = Foo. Bah. DRIVER: Wombat. SQL. DRIVER: Bad. Test. ourdriver;

The first call to the drivermanager method will automatically load these driver classes.
Note: The second method for loading the driver requires a persistent preset environment. If this is not guaranteed, it is safer to call the class. forname method to explicitly load each driver. This is also the method for introducing specific drivers, because once the drivermanager class is initialized, it will no longer check the JDBC. Drivers attribute list.
In the preceding two cases, the newly loaded driver class must be self-registered by calling the drivermanager. registerdriver class. As described above, this process is automatically executed when a class is loaded.
For security reasons, the JDBC management layer will track which class loader provides which driver. In this way, when the drivermanager class opens the connection, it only uses the driver provided by the local file system or the class loader that identical to the code that sends the connection request.
2. Establish a connection
After loading the driver class and registering it in the drivermanager class, they can be used to establish a connection with the database. When the drivermanager. getconnection method is called to send a connection request, drivermanager checks Each driver to see if it can establish a connection.
Sometimes there may be multiple JDBC drivers that can be connected to a given URL. For example, when connecting to a given remote database, you can use the JDBC-ODBC bridge driver, JDBC to the generic network protocol driver, or the driver provided by the database vendor. In this case, the order to test the driver is crucial because drivermanager will use the first driver it finds to successfully connect to the given URL.
First, drivermanager tries to use each driver in the registration order (drivers listed in JDBC. Drivers are always registered first ). It skips untrusted drivers of the Code unless the source that loads them is the same as the source of the Code that tries to open the connection.
It calls the method driver on each driver in turn. connect, and pass them to the user to start passing to the method drivermanager. getconnection URL to test the driver and connect the first driver that recognizes the URL.
This method seems inefficient at the beginning, but since it is impossible to load dozens of drivers at the same time, each connection actually requires only a few process calls and string comparison.
The following code is an example of all the steps required to establish a connection with a driver, such as a JDBC-ODBC bridge DRIVER:

Class. forname ("Sun. JDBC. ODBC. jdbcodbcdriver"); // load the driver

String url = "JDBC: DBC: Fred ";
Drivermanager. getconnection (URL, "userid", "passwd ");