使用Druid完成MySql数据的增删改查——DAO层的封装

这里使用的是阿里巴巴的工具类

1.Dao实现层

这里提供了两种，一种是手写DaoUtils完成Dao层的封装，一种是使用阿里巴巴的工具完成Dao层的封装

 private  QueryRunner runner=new QueryRunner(DruidDBUtils.getDataSource());
    public int add(Student st) throws SQLException {
        String sql="insert into student(name,age,email,bornDate,address) values(?,?,?,?,?)";
        //1.手写DaoUtils完成数据的增删改查
        //return DaoUtils.commonsUpdate(sql,st.getName(),st.getAge(),st.getEmail(),st.getBornDate(),st.getAddress());
        //2.使用Druid工具完成数据的增删改查
        return runner.update(sql,st.getName(),st.getAge(),st.getEmail(),st.getBornDate(),st.getAddress());
    }

    public int delById(int id) throws SQLException {
        String sql="delete from student where id=?";
        //return DaoUtils.commonsUpdate(sql,id);
        return runner.update(sql,id);
    }

    public int updateById(int id, String name) throws SQLException {
        String sql="update student set name=? where id=?";
        //return DaoUtils.commonsUpdate(sql,name,id);
        return runner.update(sql,id,name);
    }

    public List<Student> selelctAll() throws SQLException {
        String sql="select * from student";
        //List<Student> list=DaoUtils.commonsQuery(sql,Student.class);
        //return list;
        return runner.query(sql,new BeanListHandler<>(Student.class));
    }

    public Student selectById(int id) throws SQLException {
        String sql="select * from student where id=?";
       /* List<Student> list=DaoUtils.commonsQuery(sql,Student.class,id);
        if (list.isEmpty()){//判断查询的id号所代表的信息是否存在
             return null;
        }
        return list.get(0);//存在则取出*/
       return runner.query(sql,new BeanHandler<>(Student.class),id);
    }

2.Duid工具类

private static DataSource dataSource;
    static {//静态代码块只加载一次
        try {
            Properties p=new Properties();//实例化map集合对象
            p.load(new FileInputStream("user.properties"));
            dataSource= DruidDataSourceFactory.createDataSource(p);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {
        //2.获取连接对象，并返回
        Connection conn=null;
       return conn=dataSource.getConnection();
    }
    public static DataSource getDataSource(){
        return dataSource;
    }
    public static void closeAll(AutoCloseable...ac){
        for (AutoCloseable c:ac){
            if (c!=null){
                try {
                    c.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

3.测试层

 StudentDaoImpl studentDao=new StudentDaoImpl();
        Scanner sc=new Scanner(System.in);
        do {
            System.out.println("请输入数字：1.添加 2.删除 3.修改 4.查询 5.查询单个 6.退出");
            int n=sc.nextInt();
            int res=0;
            switch (n){
                case 1:
                    int r=new Random().nextInt(100);//年龄设置取随机数
                    System.out.println("请输入要添加的姓名");
                    String name=sc.next();
                    System.out.println("请输入要添加的邮箱");
                    String email=sc.next();
                    System.out.println("请输入要添加的地址");
                    String address=sc.next();
                    Student st=new Student(r,name,r,email,new Date(),null);
                    res=studentDao.add(st);
                    System.out.println("添加："+res);
                    break;
                case 2:
                    System.out.println("请输入要删除的id");
                    int id=sc.nextInt();
                    res=studentDao.delById(id);
                    System.out.println("删除："+res);
                    break;
                case 3:
                    System.out.println("请输入要修改的id");
                    id=sc.nextInt();
                    res=studentDao.updateById(id,"HTML");
                    System.out.println("修改"+res);
                    break;
                case 4:
                    List<Student> list=studentDao.selelctAll();
                    System.out.println(list);
                    break;
                case 5:
                    System.out.println("请输入要查询的id");
                    id=sc.nextInt();
                    st=studentDao.selectById(id);
                    System.out.println(st);
                    break;
                case 6:
                    System.out.println("结束");
                    return;
                default:
                    System.out.println("输入错误！请重新输入");
                    break;
            }
        }while (true);