Java learning notes 50 (DBCP connection pool)

At this time, in order to improve efficiency, the connection pool technology will be used here:

Common understanding of connection pool:

There are many connections in a pool. Which one should be taken out and used? Put it back after use instead of closing the connection

Four jar packages are used here

commons-dbcp-1.4.jar

commons-pool-1.5.6.jar

mysql-connector-java-5.1.37-bin.jar

commons-dbutils-1.6.jar

DataSource interface: canonical interface for connection pool

BasicDataSource class: implementation class in imported jar package

Method and configuration of connecting pool to database:

package demo; import java.sql.Connection; import java.sql.SQLException; import org.apache.commons.dbcp.BasicDataSource; public class DataSourceDemo { public static void main(String[] args) { BasicDataSource dataSource = new BasicDataSource(); // General basic configuration dataSource.setDriverClassName("com.mysql.jdbc.Driver"); dataSource.setUrl("jdbc:mysql://localhost:3306/mybase"); dataSource.setUsername("root"); dataSource.setPassword("xuyiqing"); // Connection pool configuration dataSource.setMaxActive(10);// maximum connection dataSource.setMaxIdle(5);// Maximum free connection dataSource.setMinIdle(2);// Minimum free connection dataSource.setInitialSize(5);// Initialize connection try { Connection con = dataSource.getConnection(); } catch (SQLException ex) { ex.printStackTrace(); throw new RuntimeException("Database connection failed"); } } }

Next, create a tool class of connection pool to facilitate development:

Data preparation: create table:

CREATE TABLE sort( sid INT PRIMARY KEY AUTO_INCREMENT, sname VARCHAR(100), sprice DOUBLE, sdesc VARCHAR(5000) );

Tools:

package demo; import javax.sql.DataSource; import org.apache.commons.dbcp.BasicDataSource; public class JDBCUtils { private static BasicDataSource dataSource = new BasicDataSource(); static { dataSource.setDriverClassName("com.mysql.jdbc.Driver"); dataSource.setUrl("jdbc:mysql://localhost:3306/mybase"); dataSource.setUsername("root"); dataSource.setPassword("xuyiqing"); dataSource.setInitialSize(10); dataSource.setMaxActive(8); dataSource.setMaxIdle(5); dataSource.setMinIdle(1); } public static DataSource getDataSource() { return dataSource; } }

Test:

package demo; import java.sql.SQLException; import java.util.List; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.ArrayListHandler; public class QueryRunnerDemo { private static QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource()); public static void main(String[] args) { insert(); } public static void insert() { String sql = "INSERT INTO sort (sname,sprice,sdesc)VALUES(?,?,?)"; Object[] params = { "Fruits", 20, "Promotion" }; try { qr.update(sql, params); } catch (SQLException ex) { throw new RuntimeException("Data add failed"); } } public static void select() { String sql = "SELECT * FROM sort"; try { List<Object[]> list = qr.query(sql, new ArrayListHandler()); for (Object[] objs : list) { for (Object obj : objs) { System.out.println(obj + "\t"); } } } catch (SQLException ex) { throw new RuntimeException("Data add failed"); } } }

4 May 2020, 10:37 | Views: 4000