Druid连接池连接数据库

1. Mysql数据库创建

新建一个info数据库

create database info;

新建student表

Create table student(
      id  int  primary key auto_increment,
      name varchar(32) nut null,
      age  int nut null,
      course varchar(32) nut null
);

插入数据

insert into student(name, age, course) values
(“张三”, 18 ， “数学”),
(“李四”, 19,  “英语”);

2. Java代码

2.1 导jar 包（见上图lib包下）

2.2 新建druid.properties, 配置sql连接

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/info?useSSL=false
username=root
password=123456

initialSize=10
maxActive=20
maxWait=1000
filters=wall

2.3 封装 DruidUtil类，使用druid连接池连接mysql

 public class DruidUtil {

     static DataSource dataSource = null;
     static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("./src/druid.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }


     public static Connection getConnection(){
        Connection connection =null;
        try {
            connection = dataSource.getConnection();
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }

        return connection;
    }

    public static void close(Connection connection, Statement statement, ResultSet resultSet){
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if(resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }


public static void close(Connection connection, Statement statement){
        close(connection,statement,null);
}
public static void close(Connection connection){
        close(connection,null,null);
}

}

2.4 封装BaseDao类，实现增删改查方法

public class BaseDao {

    /**
     * 增删改方法
     */

    public int update(String sql, Object[] parameters) throws Exception {

        if(sql == null){
            throw new SQLException("SQL is null");
        }

        //1.获取数据库的连接
        Connection connection = DruidUtil.getConnection();

        //2.使用预处理的搬运工
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //3.获取参数元数据对象,获取参数的个数
        int parameterCount = preparedStatement.getParameterMetaData().getParameterCount();

        if(parameters != null && parameters.length == parameterCount){
            for (int i = 1; i <= parameterCount ; i++) {
                //4.使用for循环进行赋值，对 ？ 进行赋值
                preparedStatement.setObject(i, parameters[i-1]);
            }
        }
        //5.执行sql语句
        int i = preparedStatement.executeUpdate();
        //6.关闭资源
        DruidUtil.close(connection,preparedStatement);
        //7.返回受影响的行数
        return i;
    }


    /**
     * 查询方法
     */


    public <T> List<T> query(String sql, Object[] parameters, Class<T> cls) throws Exception {
        if(sql == null || cls == null){
            throw new NullPointerException();
        }

        //1.获取数据库的连接
        Connection connection = DruidUtil.getConnection();
        //2. 使用预处理搬运工对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //3.获取参数元数据对象，获取参数的个数
        int parameterCount = preparedStatement.getParameterMetaData().getParameterCount();

        if(parameters != null && parameters.length == parameterCount){
            for (int i = 1; i <= parameterCount ; i++) {
                //4.使用for循环进行赋值， 对 ？ 进行赋值
                preparedStatement.setObject(i, parameters[i - 1]);
            }
        }
        //5. 执行sql语句返回值是一个ResultSet对象
        ResultSet resultSet = preparedStatement.executeQuery();

        //6.准备一个List
        List<T> list = new ArrayList<>();
        //7.获取结果集元数据对象
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();

        while(resultSet.next()){
            T t = cls.getConstructor(null).newInstance(null);
            for (int i = 1; i <=columnCount ; i++) {
                String columnName = metaData.getColumnName(i);
                Object value = resultSet.getObject(columnName);
                BeanUtils.setProperty(t, columnName,value);
            }
            list.add(t);
        }
        DruidUtil.close(connection,preparedStatement,resultSet);
        return list.size() != 0 ? list:null;
    }

2.5 新建student实体类（对应数据库中的student表）

public class Student {
    private int id;
    private String name;
    private int age;
    private String course;

    public Student() {
    }

    public Student(int id, String name, int age, String course) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.course = course;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getCourse() {
        return course;
    }

    public void setCourse(String course) {
        this.course = course;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", course='" + course + '\'' +
                '}';
    }
}

2.6 测试增删改查方法

public class Demo1 extends BaseDao {
    /**
     * 增加数据
     * @throws Exception
     */
    @Test
    public void testAdd() throws Exception {
        String sql = "insert into student(name,age,course) values(?,?,?)";
        Object[] objs = {"王五", 19,"计算机"};
        super.update(sql,objs);
    }


    /**
     * 删除数据
     */

    @Test
    public void testDelete() throws Exception {
        String sql = "delete from student where id = 2";
        super.update(sql,null);
    }


    /**
     * 修改数据
     */
    @Test
    public void testUpdate() throws Exception {
        String sql = "update student set name=?, age=?, course=? where id = ?";
        Object[] objs = {"盖伦",19,"游泳",2};
        int update = super.update(sql, objs);
        System.out.println(update);
    }


    @Test
    public void testSelectAll() throws Exception {
        String sql = "select * from student";
        List<Work> query = super.query(sql, null, Work.class);
        for (Work work : query) {
            System.out.println(work);
        }

    }




    @Test
    public void testSelect() throws Exception {
        String sql = "select * from student where id = ?";
        Object[] objs = {2};
        
        List<Work> query = super.query(sql, objs, Work.class);
        for (Work work : query) {
            System.out.println(work);
        }
    }


}