使用Druid数据库连接池与DBUtils工具实现增删改查

使用Druid数据库连接池与DBUtils工具实现增删改查

User.java

package Bean;

public class User {
    private int id;
    private String name;
    private int age;
    private String sex;

    public User() {
    }
    public User(int id, String name, int age, String sex) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.sex = sex;
    }

    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 getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

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

druid.properties

url=jdbc:mysql://localhost:3306/demo?useUnicode=true&&characterEncodeing=UTF-8&&useSSL=false&&serverTimezone=GMT&&rwriteBatchedStatments=true
username=root
password=123456
driverClassName=com.mysql.cj.jdbc.Driver
#<!-- 初始化连接 -->
initialSize=10
#最大连接数量
maxActive=50
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=6000

DruidUtils.java

package DB;
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;

public class DruidUtils {
    private static DataSource ds=null;
    static {
        //新建配置文件对象
        Properties pros = new Properties();
        try {
            //通过类加载器找到文件路径，读取配置文件
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
            //把文件以数据流的形式加载到配置文件中
            pros.load(is);
            //创建数据源对象
            ds = DruidDataSourceFactory.createDataSource(pros);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    public static DataSource getDataSource(){
        return ds;
    }


}

DBUtilsDao.java

package DAO;

import Bean.User;
import DB.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

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

public class DBUtilsDao {
    //查询所有，返回List集合
    public List findAll() throws SQLException {
        //创建QueryRunner对象
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        //SQL语句
        String sql = "select * from stu";
        //调用方法
        List list =(List) runner.query(sql, new BeanListHandler(User.class));
        return list;
    }

    //查询单个，返回对象
    public User find(int id) throws SQLException {
        //创建QueryRunner对象
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        //sql语句
        String sql = "select * from stu where id =?";
        //调用方法
        User user = (User) runner.query(sql, new BeanHandler(User.class),id);
        return user;
    }

    //添加用户
    public Boolean insert(User user) throws SQLException {
        //创建QueryRunner对象
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        //sql语句
        String sql = "insert into stu (id,name,age,sex) values(?,?,?,?)";
        //调用方法
        int num = runner.update(sql, new Object[]{user.getId(), user.getName(), user.getAge(), user.getSex()});
        if (num>0)
            return true;
        return false;
    }

    //修改用户
    public Boolean update(User user) throws SQLException {
        //创建QueryRunner对象
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        //sql语句
        String sql = "update stu set name=?,age=?,sex=? where id =?";
        //调用方法
        int num = runner.update(sql, new Object[]{ user.getName(), user.getAge(), user.getSex(),user.getId()});
        if (num>0)
            return true;
        return false;
    }

    //删除用户
    public Boolean delete(int id) throws SQLException {
        //创建QueryRunner对象
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        //sql语句
        String sql = "delete from stu where id =?";
        //调用方法
        int num = runner.update(sql, id);
        if (num>0)
            return true;
        return false;
    }
}

DBUtilsDaoTest.java

import Bean.User;
import DAO.DBUtilsDao;
import org.junit.Test;

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

public class DBUtilsDaoTest {
    private static DBUtilsDao dao = new DBUtilsDao();
    @Test
    public void update() throws SQLException {
        User user = new User(6,"老六",14,"男");
        User user1 = new User(2,"老二",24,"男");
        if (dao.insert(user)){
            System.out.println("添加成功");
        }else {
            System.out.println("添加失败");
        }
        if (dao.update(user1)){
            System.out.println("更新成功");
        }else {
            System.out.println("更新失败");
        }
        if (dao.delete(1)){
            System.out.println("删除成功");
        }else {
            System.out.println("删除失败");
        }
    }
    @Test
    public void find() throws SQLException {
        User user = dao.find(2);
        System.out.println("查id为2");
        System.out.println(user);
        System.out.println("查所有");
        List all = dao.findAll();
        all.forEach(System.out::println);
    }
}

---

测试结果：

初始数据库：

update运行结果：

find运行结果：