Java database connection pool

Keywords: Java Database

1. C3P0 database connection pool

  • Information: link: https://pan.baidu.com/s/19jWfIQ_OlZ29pPPnxNm5og
    Extraction code: hgha

1.1 concept

  • In fact, it is a container (Collection) for storing database connections.
  • After the system is initialized, the container is created, and some connection objects will be applied in the container. When the user accesses the database, the connection objects will be obtained from the container. After the user accesses, the connection objects will be returned to the container.

1.2 benefits

  • save resources
  • Efficient user access

1.3 realization:

  1. Standard interface: under DataSource javax.sql package
    method:
    -Get connection: getConnection()
    -Return Connection: Connection.close(). If the Connection object Connection is obtained from the Connection pool, the Connection will not be closed by calling the Connection.close() method. Instead, return the Connection

  2. Generally, we do not implement it, but database manufacturers do
    C3P0: database connection pool technology
    Druid: database connection pool implementation technology, provided by Alibaba

1.4 C3P0 database connection pool technology

  • Steps:
    1. Import the jar package (two) c3p0-0.9.5.2.jar mchange-commons-java-0.2.12.jar,
    *Don't forget to import the database driver jar package
    2. Define profile:
    *Name: c3p0.properties or c3p0-config.xml
    *Path: directly put the file in the src directory.

     	3. Create core database connection pool object ComboPooledDataSource
     	4. Get connection: getConnection
     * code:
     	 //1. Create database connection pool object
         DataSource ds  = new ComboPooledDataSource();
         //2. Get the connection object
         Connection conn = ds.getConnection();
    

2. Druid database connection pool

  • Information: link: https://pan.baidu.com/s/1ehW_hnrbMRRITdrmDUZ3oA
    Extraction code: 7i1v

2.1 steps

  1. Import the jar package druid-1.0.9.jar

  2. Define profile:
    *It is in the form of properties
    *You can call any name and put it in any directory

  3. Load the configuration file. Properties

  4. Get database connection pool object: get DruidDataSourceFactory through the factory

  5. Get connection: getConnection

    //1. Import the jar package druid-1.0.9.jar
    //2. Define profile
    //3. Load configuration file
    Properties pro = new Properties();
    InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");
    pro.load(is);
    //4. Get connection pool object
    DataSource ds = DruidDataSourceFactory.createDataSource(pro);
    //5. Get connection
    Connection conn = ds.getConnection();
    

2.2 defining tool classes

  • Define a class JDBC utils

  • Provide static code blocks, load configuration files, and initialize connection pool objects

  • Provide method

    1. Get connection method: get connection through database connection pool
    2. Release resources
    3. Method to get connection pool
    	public class JDBCUtils {
    
    		    //1. Define member variable DataSource
    		    private static DataSource ds ;
    		
    		    static{
    		        try {
    		            //1. Load configuration file
    		            Properties pro = new Properties();
    		            pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
    		            //2. Get DataSource
    		            ds = DruidDataSourceFactory.createDataSource(pro);
    		        } catch (IOException e) {
    		            e.printStackTrace();
    		        } catch (Exception e) {
    		            e.printStackTrace();
    		        }
    		    }
    		
    		    /**
    		     * Get connection
    		     */
    		    public static Connection getConnection() throws SQLException {
    		        return ds.getConnection();
    		    }
    		
    		    /**
    		     * Release resources
    		     */
    		    public static void close(Statement stmt,Connection conn){
    		       /* if(stmt != null){
    		            try {
    		                stmt.close();
    		            } catch (SQLException e) {
    		                e.printStackTrace();
    		            }
    		        }
    		
    		        if(conn != null){
    		            try {
    		                conn.close();//Return connection
    		            } catch (SQLException e) {
    		                e.printStackTrace();
    		            }
    		        }*/
    		
    		       close(null,stmt,conn);
    		    }
    		
    		
    		    public static void close(ResultSet rs , Statement stmt, Connection conn){
    		
    		
    		        if(rs != null){
    		            try {
    		                rs.close();
    		            } catch (SQLException e) {
    		                e.printStackTrace();
    		            }
    		        }
    		
    		
    		        if(stmt != null){
    		            try {
    		                stmt.close();
    		            } catch (SQLException e) {
    		                e.printStackTrace();
    		            }
    		        }
    		
    		        if(conn != null){
    		            try {
    		                conn.close();//Return connection
    		            } catch (SQLException e) {
    		                e.printStackTrace();
    		            }
    		        }
    		    }
    		
    		    /**
    		     * Get connection pool method
    		     */
    		
    		    public static DataSource getDataSource(){
    		        return  ds;
    		    }
    		
    		}
    

Use of tool classes:

 public static void main(String[] args) {
        /*
         * Complete the add operation: add a record to the account table
         */
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            //1. Get connection
            conn = JdbcUtils.getConnection();
            //2. Define sql
            String sql = "insert into dept values(null,?)";
            //3. Get pstmt object
            pstmt = conn.prepareStatement(sql);
            //4. Here? assignment
            pstmt.setString(1,"Zhao 412 Liu");

            //5. Execute sql
            int count = pstmt.executeUpdate();
            System.out.println(count);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //6. Release resources
            JdbcUtils.close(pstmt,conn);
        }
    }

3. Spring JDBC

  • Spring framework's simple encapsulation of JDBC. Provides a JDBC template object to simplify JDBC development
    Steps:
  1. Import jar package

  2. Create a JdbcTemplate object. Dependent on DataSource datasource
    * JdbcTemplate template = new JdbcTemplate(ds);

  3. Call the method of JdbcTemplate to complete CRUD operation

  • update(): execute DML statement. Add, delete and modify statements

  • queryForMap(): the query result encapsulates the result set as a map set, the column name as the key, the value as the value, and the record as a map set

    *Note: the length of the result set of this method query can only be 1

  • queryForList(): the query result encapsulates the result set into a list set
    *Note: encapsulate each record into a Map set, and then load the Map set into the List set

  • query(): query results, which are encapsulated as JavaBean objects
    *Parameter of query: RowMapper
    *Generally, we use the BeanPropertyRowMapper implementation class. It can complete the automatic encapsulation of data to JavaBean s
    *New beanpropertyrowmapper < type > (type. class)

  • queryForObject: the query result is encapsulated as an object
    *It is generally used for queries of aggregate functions

				import cn.itcast.domain.Emp;
				import cn.itcast.utils.JDBCUtils;
				import org.junit.Test;
				import org.springframework.jdbc.core.BeanPropertyRowMapper;
				import org.springframework.jdbc.core.JdbcTemplate;
				import org.springframework.jdbc.core.RowMapper;			
				import java.sql.Date;
				import java.sql.ResultSet;
				import java.sql.SQLException;
				import java.util.List;
				import java.util.Map;
				
				public class JdbcTemplateDemo {
				
				    //Junit unit tests allow methods to execute independently
				
				
				    //1. Get JDBC template object
				    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
				    /**
				     * 1. Modify the salary of No. 1 data to 10000
				     */
				    @Test
				    public void test1(){
				
				        //2. Define sql
				        String sql = "update emp set salary = 10000 where id = 1001";
				        //3. Execute sql
				        int count = template.update(sql);
				        System.out.println(count);
				    }
				
				    /**
				     * 2. Add a record
				     */
				    @Test
				    public void test2(){
				        String sql = "insert into emp(id,ename,dept_id) values(?,?,?)";
				        int count = template.update(sql, 1015, "Guo Jing", 10);
				        System.out.println(count);
				
				    }
				
				    /**
				     * 3.Delete the record just added
				     */
				    @Test
				    public void test3(){
				        String sql = "delete from emp where id = ?";
				        int count = template.update(sql, 1015);
				        System.out.println(count);
				    }
				
				    /**
				     * 4.Query the record with id 1001 and encapsulate it as a Map set
				     * Note: the length of the result set of this method query can only be 1
				     */
				    @Test
				    public void test4(){
				        String sql = "select * from emp where id = ? or id = ?";
				        Map<String, Object> map = template.queryForMap(sql, 1001,1002);
				        System.out.println(map);
				        //{id=1001, ename = Monkey King, job_id=4, mgr=1004, joindate=2000-12-17, salary=10000.00, bonus=null, dept_id=20}
				
				    }
				
				    /**
				     * 5. Query all records and encapsulate them as a List
				     */
				    @Test
				    public void test5(){
				        String sql = "select * from emp";
				        List<Map<String, Object>> list = template.queryForList(sql);
				
				        for (Map<String, Object> stringObjectMap : list) {
				            System.out.println(stringObjectMap);
				        }
				    }
				
				    /**
				     * 6. Query all records and encapsulate them as a List collection of Emp objects
				     */
				
				    @Test
				    public void test6(){
				        String sql = "select * from emp";
				        List<Emp> list = template.query(sql, new RowMapper<Emp>() {
				
				            @Override
				            public Emp mapRow(ResultSet rs, int i) throws SQLException {
				                Emp emp = new Emp();
				                int id = rs.getInt("id");
				                String ename = rs.getString("ename");
				                int job_id = rs.getInt("job_id");
				                int mgr = rs.getInt("mgr");
				                Date joindate = rs.getDate("joindate");
				                double salary = rs.getDouble("salary");
				                double bonus = rs.getDouble("bonus");
				                int dept_id = rs.getInt("dept_id");
				
				                emp.setId(id);
				                emp.setEname(ename);
				                emp.setJob_id(job_id);
				                emp.setMgr(mgr);
				                emp.setJoindate(joindate);
				                emp.setSalary(salary);
				                emp.setBonus(bonus);
				                emp.setDept_id(dept_id);
				
				                return emp;
				            }
				        });
				
				
				        for (Emp emp : list) {
				            System.out.println(emp);
				        }
				    }
				
				    /**
				     * 6. Query all records and encapsulate them as a List collection of Emp objects
				     */
				
				    @Test
				    public void test6_2(){
				        String sql = "select * from emp";
				        List<Emp> list = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
				        for (Emp emp : list) {
				            System.out.println(emp);
				        }
				    }
				
				    /**
				     * 7. Total records queried
				     */
				
				    @Test
				    public void test7(){
				        String sql = "select count(id) from emp";
				        Long total = template.queryForObject(sql, Long.class);
				        System.out.println(total);
				    }
				
				}

Posted by cent on Sun, 05 Dec 2021 08:55:22 -0800