[JAVA]使用德鲁伊连接池和DAO方法完成对goods表的crud

目录

1.配置德鲁伊连接池的properties文件。

2.创建goods表，并插入数据[MySQL]。

3.创建德鲁伊数据库连接池的工具类。

4.创建Goods的domain层 

5.创建BasicDAO（BasicDAO 是其他DAO的父类）。

6.创建GoodsDAO，继承BasicDAO。

7.通过crud测试goods表

8.测试结果 

---

示意图

 开发GoodsDAO和Goods，完成对goods表的crud。

1.配置德鲁伊连接池的properties文件。

#key=value
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc_db?rewriteBatchedStatements=true&serverTimezone=UTC&&useUnicode=true&characterEncoding=UTF-8
username=root
password=030522
#initial connection Size
initialSize=10
#min idle connecton size
minIdle=5
#max active connection size
maxActive=20
#max wait time (5000 mil seconds)
maxWait=5000

2.创建goods表，并插入数据[MySQL]。

DROP TABLE IF EXISTS goods;
CREATE TABLE goods(
	id INT PRIMARY KEY,
	goods_name VARCHAR(10),
	price DOUBLE);
INSERT INTO goods VALUES
(1,'华为',6000),
(2,'苹果',7000),
(3,'三星',5000),
(4,'vivo',NULL);
SELECT * FROM goods;

3.创建德鲁伊数据库连接池的工具类。

package com.hhb.dao_.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

//基于德鲁伊数据库连接池的工具类
public class JDBCUtilsByDruid {
    private static DataSource ds;

    //在静态代码块完成ds初始化
    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\\druid.properties"));
            ds = DruidDataSourceFactory.createDataSource((properties));
        } catch (IOException e) {
            throw new RuntimeException(e);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    //编写getConnection方法
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    //关闭连接，在数据库连接池技术中，close不是真的断掉连接，而是把使用的Connection对象放回连接池
    public static void close(ResultSet resultSet, Statement statement, Connection connection) throws Exception {
        if (resultSet != null) {
            resultSet.close();
        }
        if (statement != null) {
            statement.close();
        }
        if (connection != null) {
            connection.close();
        }
    }
}

4.创建Goods的domain层 

package com.hhb.dao_.domain;

public class Goods {
    private Integer id;
    private String goods_name;
    private double price;


    public Goods(){//一定要给一个无参构造器[反射]

    }

    public Goods(Integer id, String goods_name, double price) {
        this.id = id;
        this.goods_name = goods_name;
        this.price = price;
    }

    public Integer getId() {
        return id;
    }

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

    public String getGoods_name() {
        return goods_name;
    }

    public void setGoods_name(String goods_name) {
        this.goods_name = goods_name;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }
    @Override
    public String toString() {
        return "Goods{" +
                "id=" + id +
                ", goods_name='" + goods_name + '\'' +
                ", price=" + price +
                '}';
    }
}

5.创建BasicDAO（BasicDAO 是其他DAO的父类）。

package com.hhb.dao_.dao;

import com.hhb.jdbc.dataSource.JDBCUtilsByDruid;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

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

//BasicDAO 是其他DAO的父类
public class BasicDAO<T> {//泛型指定具体的类型

    private QueryRunner qr = new QueryRunner();

    //开发通用的dml方法，针对任意的表
    public int update(String sql, Object... parameters) throws Exception {

        Connection connection = null;

        try {
            connection = JDBCUtilsByDruid.getConnection();

            int update = qr.update(connection, sql, parameters);
            return update;

        } catch (SQLException e) {
            throw new RuntimeException(e);//将编译异常转化为运行异常
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }
    }
    //返回多个对象(即查询的结果是多行)，针对任意表
    /*
    * sql :sql 语句，可以有?
    * clazz： 传入一个类的Class 对象 比如 Actor.class
    * parameters 传入? 的具体值，可以是多个
    * return 根据Actor.class 返回对应的ArrayList集合*/
    public List<T> queryMulti(String sql,Class<T> clazz,Object... parameters) throws Exception {

        Connection connection=null;
        try {
            connection = JDBCUtilsByDruid.getConnection();

            return qr.query(connection,sql,new BeanListHandler<T>(clazz),parameters);

        } catch (SQLException e) {
            throw new RuntimeException(e);//将编译异常转化为运行异常
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }

    }
    //查询单行结果的通用方法
    public T querySingle(String sql,Class<T> clazz,Object... parameters) throws Exception {
        Connection connection=null;
        try {
            connection = JDBCUtilsByDruid.getConnection();

            return qr.query(connection,sql,new BeanHandler<T>(clazz),parameters);

        } catch (SQLException e) {
            throw new RuntimeException(e);//将编译异常转化为运行异常
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }
    }
    //返回单值的方法
    public  Object queryscalar(String sql,Object... parameters) throws Exception {
        Connection connection=null;
        try {
            connection = JDBCUtilsByDruid.getConnection();

            return qr.query(connection,sql,new ScalarHandler(),parameters);

        } catch (SQLException e) {
            throw new RuntimeException(e);//将编译异常转化为运行异常
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }
    }
}

6.创建GoodsDAO，继承BasicDAO。

package com.hhb.dao_.dao;

import com.hhb.dao_.domain.Goods;


public class GoodsDAO extends BasicDAO<Goods>{
        //1.拥有BasicDAO的方法
        //2.根据业务需求，可以编写特有的方法，

}

7.通过crud测试goods表

package com.hhb.dao_.test;

import com.hhb.dao_.dao.GoodsDAO;
import com.hhb.dao_.domain.Goods;

import org.junit.Test;

import java.util.List;

public class GoodsTestDAO {
    //测试GoodsDAO 对goods表的crud操作
    @Test
    public void TestGoodsDAO() throws Exception {
        GoodsDAO goodsDAO = new GoodsDAO();
        //1.查询
        List<Goods> goods1 = goodsDAO.queryMulti("select * from goods where id >= ?", Goods.class, 1);
        System.out.println("查询结果");
        for (Goods goods : goods1) {
            System.out.println(goods);
        }
        //查询单行记录
        Goods goods = goodsDAO.querySingle("select * from goods where id=?", Goods.class, 3);
        System.out.println("查询单行记录");
        System.out.println(goods);
        //查询单行单值
        Object o = goodsDAO.queryscalar("select goods_name from  goods where id=?", 2);
        System.out.println("查询单行单值");
        System.out.println(o);
        //4.dml操作 insert,update,delete
        int update = goodsDAO.update("insert into goods values(?,?,?)", 5, "小米", 4000);
        System.out.println(update>0?"执行成功":"执行未影响到表");

    }
}

8.测试结果