[大数据]java(十四) —— JDBC(四)数据库连接池技术-Druid、DBUtils类相应操作

1.Druid连接池

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;

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

/**
 * @author ym
 * @create 2022-02-01 20:22
 * @description
 */
public class DruidTest {
    @Test
    public void getConnection() throws Exception {
        Properties pros = new Properties();
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
        pros.load(is);
        DataSource source = DruidDataSourceFactory.createDataSource(pros);
        Connection conn = source.getConnection();
        System.out.println(conn);
    }
}

properties:在src目录下

url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
username=root
password=abc123
driverClassName=com.mysql.jdbc.Driver

initialSize=10
maxActive=10

2.DBUtils类相应操作

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * @author ym
 * @create 2022-02-01 20:33
 * @description
 */
public class JDBCUtil {
    //测试添加
    @Test
    public void testInsert() {
        Connection connection = null;
        try {
            //1.获取连接
            connection = DBUtil.getConnection();
            //2.操作对象
            QueryRunner queryRunner = new QueryRunner();
            String sql = "insert into customers(name,email,birth) values(?,?,?)";
            queryRunner.update(connection, sql, "汤姆", "tom@163.com", "1994-08-07");
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(connection, null);
        }

    }

    //测试查询
    @Test
    public void testQueryOne() {
        Connection connection = null;
        try {
            //1.获取连接
            connection = DBUtil.getConnection();
            //2.操作对象
            QueryRunner queryRunner = new QueryRunner();
            String sql = "select id,name,email,birth from customers where id =?";
            BeanHandler<Customer> customerBeanHandler = new BeanHandler<Customer>(Customer.class);
            Customer query = queryRunner.query(connection, sql, customerBeanHandler, 3);
            System.out.println(query.toString());
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(connection, null);
        }

    }

    //测试多条数据查询
    @Test
    public void testQueryMany() {
        Connection connection = null;
        try {
            //1.获取连接
            connection = DBUtil.getConnection();
            //2.操作对象
            QueryRunner queryRunner = new QueryRunner();
            String sql = "select id,name,email,birth from customers where id >?";
            BeanListHandler<Customer> customerBeanListHandler = new BeanListHandler<Customer>(Customer.class);
            List<Customer> query = queryRunner.query(connection, sql, customerBeanListHandler, 7);
            query.forEach(System.out::println);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(connection, null);
        }

    }
}