Database connection pool

• 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 riani object will be returned to the container

• benefit

1. save resources
2. Efficient user access

• 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, call the Connection.close() method to return the Connection instead of closing the Connection

1. Generally, we do not implement it, but have database manufacturers to implement it

C3P0: database connection pool

Bruid: database connection pool implementation technology, provided by Alibaba

C3P0: database connection pool technology

• step

1. Import the jar package (two) c3po-0.9.5.2.jar mchange-commons-java-0,2,12,jar,
2. Define profile

Name: c3po.properties or c3po-config.xml

Path: bytes. Just put the file in the src directory

1. Create the core object database connection pool object CombopooledDataSource
2. Get connection: getConnection

Druid: implementation technology of database connection pool

• step

1. Import the jar package druid-1.0.9.jar
2. Definition configuration file: it is in the form of properties. It can be called any name and placed under any name
3. Load the configuration file. Properties
4. Get database connection pool object: get DruidDataSourceFactory through factory
5. Get connection getConnection

package C3P0Demo1;

import  com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
import java.util.concurrent.Callable;

public class DruidDemo {
    public static void main(String[] args) throws Exception {
//1. Import jar package
        //2. Define profile
        //3. Load configuration file
        Properties pro = new Properties();
        InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");
        pro.load(is);
         DataSource ds = DruidDataSourceFactory.createDataSource(pro);
        //5.  Get connection
        Connection conn = ds.getConnection();
        System.out.println(conn);
        }
}

spring JDBC

• The Spring framework will simply encapsulate JDBC and provide a JDBC template object to simplify JDBC development
• step

1. Import jar package
2. Creating a JDBC template object depends on the data source

jdbctemplate template = new JdbcTemplate(ds)

1. Call the method of Jdbctemplate to complete the crud operation

update(): execute DML statements, add, delete and change statements

queryForMap(): the query result encapsulates the result into a map collection

queryForList(): query round results are encapsulated into a list collection

query(): query results, which are encapsulated as javabean objects

Query parameter RowMapper:

Generally, we use the BeanPropertyRowMapper implementation class to automatically encapsulate data into Java beans

new BeanPropertyRowMapper<type>(type.class)

queryforObject: the query result is encapsulated as an object

practice:

Requirements:

1. Modify 1 the data salary to 10000
2. Add a day record
3. Delete the record just added
4. The giant deer with jd of 1 is described and encapsulated into a map set// The result set of this method query can only be 1. Take the column name as the key and the value as the calue and encapsulate it as a map set
5. Query all giant deer and encapsulate them into a list: Note: encapsulate each record into a map set, and load the map set into the list set
6. Query all records and encapsulate them as a list collection of emp objects
7. Total records queried

package Domain;

import JDBCTemplate.JDBCUtils;
import com.alibaba.druid.sql.ast.statement.SQLCreateTriggerStatement;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;
import java.util.Map;

public class jdbctemplatedemo {
    //junit unit tests allow methods to execute independently
   private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
    @Test
    public void test1(){
        //Or defraud the JDBC tempalte object

        //2. Define sql
        String sql = "update emp set salary = 1000 where id =1001";
        //Execute sql
        int count = template.update(sql);
        System.out.println(count );


    }
    @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);
    }
    @Test
    public void test3(){
        String sql = "delete from emp where id = ?";
        int count = template.update(sql,1015);
        System.out.println(count);
    }
    @Test
    public void test4(){//The result set of this method query can only be 1. Take the column name as the key and the value as the calue and encapsulate it as a map set
        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=1000.00, bounds=null, dept_id=20}
    }
    @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);
        }


    }
    @Test
    public void test6(){//Data type to encapsulate
        String sql = "select * from emp";
        List<emp> list = template.query(sql,new BeanPropertyRowMapper<emp>(emp.class));
        for(emp emp :list){
            System.out.println(emp);
        }
    }
    //Check the general record
    @Test
    public void test7(){
        String sql = "select count(id) from emp";
        Long total = template.queryForObject(sql,Long.class);
        System.out.println(total);
    }