There are two types of data sources:
- Ordinary Data Source: Data Source Driven by Database
- Connection pools include connection pools with database drivers and common third-party connection pools such as DBCP and C3P0.
Data Source with Database Driver
1 //from properties File Loading Data Source Configuration 2 Properties properties = new Properties(); 3 InputStream is = Class.forName("test.Test").getResourceAsStream("/mysqlDataSource.properties"); 4 properties.load(is); 5 String url=properties.getProperty("url"); 6 String user = properties.getProperty("user"); 7 String password = properties.getProperty("password"); 8 9 //Set up a common data source because it uses MysqlDataSource Class, driving nature can only be mysql Driver, this class has set up the database driver, we do not need to set. 10 MysqlDataSource mysqlDataSource = new MysqlDataSource(); 11 mysqlDataSource.setUrl(url); //There is another method, setURL (), which is exactly the same, because the function body of setURL () is to call setUrl (). 12 mysqlDataSource.setUser(user); 13 mysqlDataSource.setPassword(password); 14 15 //Get the connection and operate the database 16 Connection connection = mysqlDataSource.getConnection(); 17 String sql = "insert into student_tb (name,age,score) values (?,?,?)"; 18 PreparedStatement preparedStatement = connection.prepareStatement(sql); 19 preparedStatement.setString(1,"chy"); 20 preparedStatement.setInt(2,20); 21 preparedStatement.setInt(3,100); 22 preparedStatement.executeUpdate(); 23 preparedStatement.close(); 24 connection.close();
I imported Mysql database driver. Mysql driver naturally provides MySQL data source. We have registered MySQL database driver by default. We don't need to register manually. If the driver of imported other databases is provided, the data source of such databases is naturally provided.
Generally speaking, database drivers provide two kinds of data sources: common data source and connection pool.
The common data source provided by the mysql driver is mysql Data Source, and the connection pool is mysql Connection Pool Data Source, which is known by name as the connection pool data source.
Connection pool with database driver
1 //from properties File Loading Data Source Configuration 2 Properties properties = new Properties(); 3 InputStream is = Class.forName("test.Test").getResourceAsStream("/mysqlDataSource.properties"); 4 properties.load(is); 5 String url=properties.getProperty("url"); 6 String user = properties.getProperty("user"); 7 String password = properties.getProperty("password"); 8 9 //Setting up connection pool 10 MysqlConnectionPoolDataSource poolDataSource = new MysqlConnectionPoolDataSource(); 11 poolDataSource.setURL(url); 12 poolDataSource.setUser(user); 13 poolDataSource.setPassword(password); 14 15 //Get the connection and operate the database 16 Connection connection = poolDataSource.getConnection(); 17 String sql = "insert into student_tb (name,age,score) values (?,?,?)"; 18 PreparedStatement ps = connection.prepareStatement(sql); 19 ps.setString(1,"chy"); 20 ps.setInt(2,20); 21 ps.setInt(3,99); 22 ps.executeUpdate(); 23 connection.close();
Ordinary data sources use direct connection to the database. When calling getConnection() to get a connection, in fact, the bottom layer is still calling DriverManager.getConnection(url, user, password); to get a connection; when closing a connection, it is to actually close the connection and release resources.
Connection pool is to create a connection pool on the server, set up some database connections in advance, put them in the connection pool, call getConnection() to get the connection, just take out a connection from the connection pool, call close() to close the connection, not really close the connection, is recycled by the connection pool, the next can also be. Then use it. Creating database connections takes time, and using connection pools reduces time overhead.
Data sources are suitable for applications that need to create database connections many times. If you do not use data sources, one by one DriverManager.getConnection(url, user, password); it is troublesome to create connections by passing in url, user, password.
Connection pool data sources are suitable for applications that need to create multiple connections.
DBCP Data Source
DBCP is an open source project of Apache, and Tomcat's connection pool is implemented using DBCP.
Using DBCP requires two jar packages:
- The core package of commons-dbcp.jar dbcp, which contains the compressed package after downloading
- commons-pool.jar dbcp dependencies need to be downloaded separately
These two are all downloaded in apache. When downloading, we should pay attention to corresponding JDK version (actually corresponding to JDBC version in JDK).
In addition, two additional jar packages are required:
- commons-logging.jar log package
- Database Driver
The data source class commonly used by DBCP is BasicDataSource.
1 //from properties File Loading Data Source Configuration 2 Properties properties = new Properties(); 3 InputStream is = Class.forName("test.Test").getResourceAsStream("/dataSource.properties"); 4 properties.load(is); 5 String driverStr = properties.getProperty("driver"); 6 String url=properties.getProperty("url"); 7 String username = properties.getProperty("username"); 8 String password = properties.getProperty("password"); 9 10 //Setting up connection pool 11 BasicDataSource dataSource = new BasicDataSource(); 12 //These third-party data sources do not know which database we are using, so we need to set up the database driver manually. 13 dataSource.setDriverClassName(driverStr); //Note that setDriverClassName(),The parameter is String 14 /* 15 It can also be written as follows: 16 java.sql.Driver driver = DriverManager.getDriver(driverStr); 17 dataSource.setDriver(driver); //The parameter is java.sql.Driver 18 Driver Classes are available under multiple packages. Be careful not to write them wrong. 19 It is suggested to use the former one, which is simpler and not easy to make mistakes. 20 */ 21 dataSource.setUrl(url); 22 dataSource.setUsername(username); //Note that setUsername()，No setUser() 23 dataSource.setPassword(password); 24 25 //Get connections from data sources, get connections, and manipulate databases 26 Connection connection = dataSource.getConnection(); 27 String sql = "insert into student_tb (name,age,score) values (?,?,?)"; 28 PreparedStatement ps = connection.prepareStatement(sql); 29 ps.setString(1,"chy"); 30 ps.setInt(2,20); 31 ps.setInt(3,99); 32 ps.executeUpdate(); 33 connection.close();
Data sources can be created through factories:
BasicDataSource dataSource= BasicDataSourceFactory.createDataSource(properties); //The parameter is Properties type
Very simple, but there are strict requirements for key s in properties, such as:
setUsername() => Remove set, and the latter part becomes camel, username.
If the key is written incorrectly, for example, as user, it will report an error: Access denied for user''@'localhost' (using password: YES), sometimes NO.
Some other parameters can be set to the data source, such as:
dataSource.setInitialSize(5); //Set the initial number of connections. When you initialize the connection pool, you create five connections and put them in the connection pool.
dataSource.setMinIdle(2); //Set the minimum number of idle connections, and at least two connections in the connection pool are idle. Idle, idle, idle.
dataSource.setMaxTotal(20); //Set the maximum number of connections, up to 20 connections in the connection pool
//......
C3P0 Data Source
C3P0 is an open source project of mchange. Compared with DBCP, C3P0 has the function of automatically reclaiming idle connections and has better performance.
Download, decompressed lib folder, there are three jar packages, c3p0.jar, mchange-commons-java.jar these two jar packages added to the project, with oracle that Oracel only used.
Add the database driver to the project.
DBCP is Apache's. To add its own log package commons-logging.jar, C3P0 does not need to add a log package.
The data source class commonly used in C3P0 is ComboPooled DataSource.
1 //from properties File Loading Data Source Configuration 2 Properties properties = new Properties(); 3 InputStream is = Class.forName("test.Test2").getResourceAsStream("/dataSource.properties"); 4 properties.load(is); 5 String driver = properties.getProperty("driver"); 6 String jdbcUrl=properties.getProperty("jdbcUrl"); 7 String user = properties.getProperty("user"); 8 String password = properties.getProperty("password"); 9 10 //Configuring data sources 11 ComboPooledDataSource dataSource = new ComboPooledDataSource(); 12 dataSource.setDriverClass(driver); //That's the only way. No. setDriver() 13 dataSource.setJdbcUrl(jdbcUrl); //Note that setJdbcUrl()，Unlike other data sources 14 dataSource.setUser(user); 15 dataSource.setPassword(password); 16 17 //Get connections from data sources and manipulate databases 18 Connection connection = dataSource.getConnection(); 19 String sql = "insert into student_tb (name,age,score) values (?,?,?)"; 20 PreparedStatement ps = connection.prepareStatement(sql); 21 ps.setString(1,"chy"); 22 ps.setInt(2,20); 23 ps.setInt(3,99); 24 ps.executeUpdate(); 25 connection.close();
Compared with DBCP, C3P0 can set more configuration parameters, such as:
dataSource.setMaxStatements(10);
dataSource.setMaxStatementsPerConnection(10);
DBCP does not have the function of automatically reclaiming idle connections, but C3P0 does.