【JDBC】DBUtils操作数据库，增删改查

第一步：导入jar包

1. 导入mysql的驱动包
   mysql-connector-java-8.0.11.jar
2. 导入C3P0的jar包
   c3p0-0.9.5.2.jar
   mchange-commons-java-0.2.11.jar
   在src目录下添加c3p0-config.xml配置文件
3. 导入commons-dbutils-1.6.jar
4. 导入JUnit测试包
   junit-4.12.jar
   hamcrest-core-1.3.jar

DBUtils
将jdbc进行封装,业务实现上,开发，执行 mybatis dbutils组件提供了一组API

增加dbutils组件jar API

query.update(sql,params); 执行ddl dml中insert update delete

query.query(sql,ResultSetHandler,params)

ResultSetHandler:
处理查询出来的一条记录 User类型对象
​ 查询出多条记录 List
​ select count(*) from tbl_user
编写测试类

public class TeacherTest {
    private QueryRunner query=null;
    private ComboPooledDataSource dataSource=new ComboPooledDataSource();
    @Before
    /**
    @Before标注过的方法是针对这个类文件中所有的@Test标注过的测试方法，
    每一个@Test方法执行前都需要去执行一遍@Before标注的setup()方法。*/
    public void before(){
        query=new QueryRunner(dataSource);
    }

    //测试连接有没有成功
    @Test
    public void testC3p0() {
        try {
            Connection connection = dataSource.getConnection();
            //输出连接的数据库名字
            System.out.println(connection.getCatalog());
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
        @Test
        /*插入数据*/
    public  void testInsert(){
    String sql="insert into teacher values(?,?)";
    Object[] params={05,"bigdata"};
        try {
            int rs=query.update(sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    @Test
    /*根据ID查找*/
    public  void testFindById(){
    //要变成实体对象返回，所以要添加实体类
        String sql="select * from teacher where t_id=?";
        //ResultSetHandler<T>  对结果集处理的类
        //处理查询出来的一条记录   Teacher类型对象
        //查询出多条记录  List<Teacher>
        //select count(*) from teacher
        Teacher teacher=null;
        try {
            //完成反射，将查询记录实例化为teacher对象
            //类名和字段名要相同
            teacher=query.query(sql, new BeanHandler<Teacher>(Teacher.class), 5);
        }catch (Exception e){
            e.printStackTrace();
        }
        System.out.println(teacher);
    }
    @Test
    /**
     * 验证密码和用户名是否一致
     * 根据用户名，和密码查询,如果有记录，则表示用户名密码一致
     */
    public void UserNamePassword(){
        String sql="select * from teacher where t_id=? and t_name=?";
        Teacher teacher=null;
        try{
            teacher=query.query(sql,new BeanHandler<Teacher>(Teacher.class),01,"叶平");
        }catch (Exception e){
            e.printStackTrace();
        }
        System.out.println(teacher);
    }

    @Test
    /**
     * 查询所有数据
     */
    public void testFindAll(){
        String sql="select * from teacher";
        List<Teacher> list=null;
        try{
            //返回一个集合
            list=query.query(sql,new BeanListHandler<Teacher>(Teacher.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        for(Teacher t:list){
            System.out.println(t);
        }
    }

    @Test
    /**
     * 分页查询
     */
    public void testFindPage(){
        String sql="select * from teacher limit ?,?";
        List<Teacher> list=null;
        try{
            list=query.query(sql,new BeanListHandler<Teacher>(Teacher.class),4,2);
        }catch (Exception e){
            e.printStackTrace();
        }
        for(Teacher t:list){
            System.out.println(t);
        }
    }

    @Test
    /**
     *
     * 返回的结果是一个数字，使用 ScalarHandler<Long>
     */
    public void  testCount(){
        String sql="select count(*) from teacher";
        long count=-1;
        try {
            count=query.query(sql, new ScalarHandler < Long > ());
        }catch (Exception e){
            e.printStackTrace();
        }
        System.out.println(count);
    }
    }

创建实体类

package com.kgc.one.entity;

public class Teacher {
    private int t_id;
    private String t_name;

    public Teacher(){}
    public Teacher(int t_id, String t_name) {
        this.t_id = t_id;
        this.t_name = t_name;
    }

    public int getT_id() {
        return t_id;
    }

    public void setT_id(int t_id) {
        this.t_id = t_id;
    }

    public String getT_name() {
        return t_name;
    }

    public void setT_name(String t_name) {
        this.t_name = t_name;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "t_id=" + t_id +
                ", t_name='" + t_name + '\'' +
                '}';
    }
}