JAVA Database Connection Methods

 

I. Problem Introduction
In java programs, you need to access the database and perform operations such as adding, deleting, modifying, and querying. How to access the database and perform database operations?

 

Ii. Java database connection method Overview
Java. SQL provides some interfaces and classes to support addition, deletion, modification, and query of databases. This jar package defines java access to various databases (mysql, oracle, sqlserver .....) Unified interfaces and standards. At the same time, each database vendor provides implementation classes for each interface defined in the jar package, which is used to realize the addition, deletion, modification, and query operations of the database in our factory, that is, "database-driven jdbc driver ". For example, the mysql database driver is com. mysql. jdbc. driver. The oracle Database driver is oracle. jdbc. driver. oracledriver.

When accessing the database in a java program, you can use either of the following methods to connect to the database:

1. Use java. SQL API

Use the various interfaces and classes provided by this package to directly access the database.

 

 

2. Use the database connection pool

There are currently multiple open-source java database connection pools, which are compiled on the basis of java. SQL.

The connection pool solves the following problems:
When using APIs provided in java. SQL to create a database connection, it takes a lot of resources, such as user name and password database connection verification, which consumes both resources and time. If you connect to the database every time you access the database in the program, the performance will inevitably be low. If you forget to release the database connection due to user errors, the resources will be wasted. The database connection pool solves this problem by managing multiple connection objects in the connection pool to reuse the connection. This greatly improves the database connection performance.
The functions of the connection pool are as follows:
Creates, manages, releases, and allocates database connections ). First, create a number of database connection objects and place them in the database connection pool. When a user requests a database connection, the connection pool is responsible for allocating a database connection object in idle state. When the user releases the database connection, the connection pool is responsible for resetting the connection object to idle state, so that it can be reused by other requests. At the same time, the database connection pool is responsible for checking (idle time> maximum idle time) database connections and releasing them.
Listener connection pool Parameters
Minimum connections: during initialization, the system will be responsible for creating this number of connections and placing them in the connection pool.
Max connections: the maximum number of connections allowed by the system. When the system requests a connection, and there is no idle connection in the connection pool: if the total number of connections does not exceed the maximum number of connections, the connection pool is responsible for creating a new connection object and returning this object; if the total number of connections reaches the maximum number of connections, the connection pool transfers user requests to the waiting queue.
 
 
Iii. Common Database Connection pools
1. JNDI
2. C3p0
3. Apache Jakarta DBCP
4. BoneCP
Among them, the sping Framework relies on third parties using c3p0 and dbcp, while bonecp claims to be the fastest database connection pool. The datasource created and implemented by the JNDI method is actually implemented by javax. SQL. datasource. None of the other three methods is implemented. The following list lists the differences and differences between the methods:
Serial number

Connection Pool name

Dependent jar package

Datasource class

Remarks

1

JNDI

The data source is initialized, created, and managed by the corresponding web server (such as tomcat, weblogic, and websphere. The program does not need to introduce special jar packages.

Javax. SQL. datasource

 

2

C3P0

C3p0-0.9.xxx.jar

Com. mchange. v2.c3p0. ComboPooledDataSource

 

3

DBCP

Commons-dbcp.jar, commons-pool.jar

Org. apache. commons. dbcp. BasicDataSource

 

4

BoneCP

 

Bonecp-0.6.5.jar

· Google-collections-1.0.jar

· Slf4j-api-1.5.11.jar

· Slf4j-log4j12-1.5.11.jar

· Log4j-1.2.15.jar

 

BoneCPDataSource

 

Note: The configuration parameters of the database connection pool in the above methods are similar and slightly different. The parameter configuration can be configured either through the configuration file or hard-coded.
 
4. List the encoding examples of several connection pools.
(For all examples, see the D: \ work \ qsyworkspace2 \ jdbctest project)

1. Access the database directly using java. SQL API
For more information, see the javasql. java file.

Class. forName ("com. mysql. jdbc. Driver ");

String url = "jdbc: mysql: // localhost: 3306/editortest ";

String user = "root ";

String password = "123456 ";

Connection cn = DriverManager. getConnection (url, user, password );

Statement st = cn. createStatement ();

String SQL = "select * from artical where id = 1 ";

ResultSet rs1_st.exe cuteQuery (SQL );

While (rs. next ()){

System. out. println ("1:" + rs. getString (1 ));

System. out. println ("2:" + rs. getString (2 ));

System. out. println ("3:" + rs. getString (3 ));

System. out. println ("4:" + rs. getString (4 ));

}
2. Use the JNDI Method
In this way, java. SQL. datasource is implemented on the web server. The web server initializes the data source, creates a connection, allocates, and manages the connection. Because it is implemented by the web server, you do not need to introduce a special jar package in the project, but you need to add related configurations to some configuration files on the server. The following uses the tomcat server as an example to describe how to use this method.

(1) modify the context. xml file in the conf file of tomcat to support Resource configuration.

(2) because the data source is created by tomcat, the required jdbc driver should be placed in the lib path of tomcat.

(3) Compile and use java code and use it in the tomcat environment as follows:

Public void jnditest (){

// TODO Auto-generated method stub

Try {

Context initcontext = new InitialContext ();

Context context = (Context) initcontext. lookup ("java: comp/env ");

 

DataSource datasource = (DataSource) context. lookup ("jdbc/editortest ");

Connection cn = datasource. getConnection ();

Statement st = cn. createStatement ();

String SQL = "select * from artical where id = 1 ";

ResultSet rs1_st.exe cuteQuery (SQL );

While (rs. next ()){

System. out. println ("1:" + rs. getString (1 ));

System. out. println ("2:" + rs. getString (2 ));

System. out. println ("3:" + rs. getString (3 ));

System. out. println ("4:" + rs. getString (4 ));

}

} Catch (NamingException e ){

// TODO Auto-generated catch block

E. printStackTrace ();

} Catch (SQLException e ){

// TODO Auto-generated catch block

E. printStackTrace ();

}

}

 

(4) For details, see jndisql. Java file and index. jsp.

 

Note: This test cannot be tested in the main method. You can write a jsp file for testing in the tomcat environment. Because: the java unit environment is jdk, while the jsp environment is tomcat; the data connection pool is configured in tomcat, so it can run normally, but the java test environment is only jdk, therefore, when the data connection pool is referenced, the environment cannot be found.

Use environment: when using weblogic, websphere, and other advanced web servers, you can consider using this method to improve performance.

3. C3p0
C3P0 is an open-source database connection component that supports creating database connection pools and managing connections. When using this method for database connection, you need to import c3p0-0.9.1.2.jar.

In addition, specific database connection parameters, such as url, username, password, minimum connections, and maximum connections ..... And other information can be configured in the xml configuration file or created through program encoding. Spring supports the database connection pool of c3p0. Therefore, it supports configuration in the applicationcontext. xml file when used in spring environments. In addition, because the database connection pool is single-instance for a database in the entire project, even if you create a database by encoding, you must ensure its single-instance features. If there are multiple, it will inevitably lead to poor performance.

The following describes how to use the c3p0 database connection pool through program encoding.

ComboPooledDataSource ds = new ComboPooledDataSource ();
Try {

Ds. setDriverClass ("com. mysql. jdbc. Driver ");

Ds. setJdbcUrl ("jdbc: mysql: // localhost: 3306/editortest ");

Ds. setUser ("root ");

Ds. setPassword ("123456 ");

Ds. setMaxPoolSize (20 );

Ds. setInitialPoolSize (10 );

Ds. setMaxIdleTime (2000 );

Connection cn = ds. getConnection ();

Statement st = cn. createStatement ();

String SQL = "select * from artical where id = 1 ";

ResultSet rs1_st.exe cuteQuery (SQL );

While (rs. next ()){

System. out. println ("1:" + rs. getString (1 ));

System. out. println ("2:" + rs. getString (2 ));

System. out. println ("3:" + rs. getString (3 ));

System. out. println ("4:" + rs. getString (4 ));

}

} Catch (PropertyVetoException e ){

// TODO Auto-generated catch block

E. printStackTrace ();

} Catch (SQLException e ){

// TODO Auto-generated catch block

E. printStackTrace ();

}

Note: This hard encoding method is usually used through configuration files. Here, we will briefly introduce how to use C3P0. For more information, see c3p0test. java.

 

4. Use dbcp
DBCP is a data source connection pool provided by apache. It supports database connection pool creation and connection management. To use the environment, you need to import both the commons-dbcp.jar and the commons-pool.jar. The JNDI method mentioned above is actually a dbcp data source, but it is configured on the web server, and the web server is responsible for creating the data source.

Similarly, the dbcp data source supports both the xml configuration file and hard encoding methods. Generally, configuration files are used, and hard encoding is rarely used. The following describes dbcp encoding:

BasicDataSource ds = new BasicDataSource ();
Ds. setDriverClassName ("com. mysql. jdbc. Driver ");

Ds. setUrl ("jdbc: mysql: // localhost: 3306/editortest ");

Ds. setUsername ("root ");

Ds. setPassword ("123456 ");

Ds. setMaxIdle (20 );

Ds. setInitialSize (10 );

Ds.setmaxactive (2000 );

Try {

Connection cn = ds. getConnection ();

Statement st = cn. createStatement ();

String SQL = "select * from artical where id = 1 ";

ResultSet rs1_st.exe cuteQuery (SQL );

While (rs. next ()){

System. out. println ("1:" + rs. getString (1 ));

System. out. println ("2:" + rs. getString (2 ));

System. out. println ("3:" + rs. getString (3 ));

System. out. println ("4:" + rs. getString (4 ));

}

} Catch (SQLException e ){

// TODO Auto-generated catch block

E. printStackTrace ();

}

5. Use BoneCP.
BoneCP is a fast and efficient database connection pool component. It is said that it is currently the best in terms of performance, 25 times faster than C3P0 and DBCP. Using this component, You need to import bonecp-0.6.5.jar, google-collections-1.0.jar, slf4j-api-1.5.11.jar, slf4j-log4j12-1.5.11.jar, log4j-1.2.15.jar.

The following describes how to use the encoding method for a brief understanding.

BoneCPDataSource ds = new BoneCPDataSource ();

Ds. setDriverClass ("com. mysql. jdbc. Driver ");

Ds. setJdbcUrl ("jdbc: mysql: // localhost: 3306/editortest ");

Ds. setUsername ("root ");

Ds. setPassword ("123456 ");

Try {

Connection cn = ds. getConnection ();

Statement st = cn. createStatement ();

String SQL = "select * from artical where id = 1 ";

ResultSet rs = st.exe cuteQuery (SQL );

While (rs. next ()){

System. out. println ("1:" + rs. getString (1 ));

System. out. println ("2:" + rs. getString (2 ));

System. out. println ("3:" + rs. getString (3 ));

System. out. println ("4:" + rs. getString (4 ));

}

} Catch (SQLException e ){

// TODO Auto-generated catch block

E. printStackTrace ();

}

 

Summary: The above section describes several common data source connection pools. These connection pools support hard encoding and configuration file configuration, configuration methods should be used whenever possible to facilitate maintenance and management. The hard-coding method can be used for testing. At the same time, the spring framework integrates the above several data sources in his own way, which is supported theoretically. Each data source connection pool has some public attributes, because they are inherited from javax. SQL. datasource, and all have the concepts of maximum connections and initial connections. At the same time, they have different attributes and have made extensions. Here is a brief introduction. In actual use, to achieve high-performance database connection pool management, you also need to thoroughly study the connection attribute configuration of each method. For example: based on actual needs, set the appropriate minimum number of connections, maximum number of connections, and wait time.

 

V. Java (x). SQL directly operates the relationship between the database and various open source data sources (datasource)
When you use classes in the java (x). SQL package provided by JDK to access the database, drivermanager, connection, statement, and resultset are basically used. Drivermanger is a class, which calls the method in the corresponding driver (that is, the driver provided by each database vendor) to generate the connection object. Connection is an interface that is implemented in the database drivers provided by various database vendors. For example, when com. mysql. jdbc. driver is used, the generated connection is the com. mysql. jdbc. Connection object.

The Javax. SQL package defines the interface datasource and specifies the methods and attributes that must be provided as the data source connection pool. Datasource provided by each data source component implements this interface. When the connnection is obtained through the data source connection pool, similarly, each data source component also provides a class that implements the java. SQL. connection interface.

For more details, see java (x) In the jdk documentation ). description of related classes and interfaces in the SQL package. For more information, see the source code of the open source data source connection pool component (such as C3P0). For more information, see related database drivers.

 

Vi. Appendix: Java open-source database connection pool
Open-source database connection pools in Java include the following:

1. C3P0 C3P0 is an open-source JDBC Connection pool. It is released together with Hibernate in the lib directory, including the DataSources object of the Connection and Statement pool that implements the jdbc3 and jdbc2 extension specifications.

2. Proxool this is a Java SQL Driver and provides connection pool encapsulation for other types of drivers you choose. It can be easily transplanted to the existing code. Completely configurable. Fast, mature, and robust. You can transparently Add the connection pool function to your existing JDBC driver.

3. Jakarta DBCP is a database connection pool dependent on the Jakarta commons-pool Object pool mechanism. DBCP can be used directly in applications.

4. DDConnectionBroker is a simple and lightweight database connection pool.

5. DBPool is an efficient and easy-to-configure database connection pool. In addition to supporting the functions of the connection pool, it also includes an object pool that allows you to develop a database connection pool that meets your own needs.

6. XAPool is an XA database connection pool. It implements javax. SQL. XADataSource and provides a connection pool tool.

7. Primrose is a Java-developed database connection pool. Currently, the supported containers include Tomcat 4 & 5, Resin3 and JBoss3. it also has an independent version that can be used in the application without running in the container. Primrose uses a web interface to control SQL processing tracking, configuration, and dynamic pool management. In case of heavy load, You can process the Connection Request queue.

8. SmartPool is a connection pool component that imitates the features of the Application Server Object pool. SmartPool can solve some critical problems such as connection leaks, connection blocking, and opened JDBC objects such as Statements and PreparedStatements. smartPool supports multiple pools, automatically closes associated JDBC objects, detects connection leaks after the set time-outs, and tracks connection usage, forcibly enable connections that are least recently used, and pack SmartPool into an existing pool.

9. MiniConnectionPoolManager is a lightweight JDBC database connection pool. It only requires Java1.5 (or higher) and does not depend on third-party packages.

10. BoneCP is a fast and open-source database connection pool. Help you manage data connections so that your applications can access the database more quickly. It is 25 times faster than C3P0/DBCP connection pool.