Apache的DbUtils使用

Apache的DbUtils使用

Connection DbUtils是Apache组织提供的一个对JDBC进行简单封装的开源工具类库，使用它能够简化JDBC应用程序的开发！同时，不会影响程序的性能

19.1DbUtils简介

• DbUtils是java编程中数据库操作实用小工具，小巧、简单、实用
  • 对于数据库表的操作，可以把结果转换为List、Array、Set等集合。便于操作
  • 对于数据库表的DML操作，也变得很简单(只需要写SQl语句);

19.1.1DbUtils主要包含

• ResultSetHandler接口：转换类型接口
  • BeanHandler类：实现类，把一条记录转换成对象
  • BeanListHandler类：实现类，把多条记录转换成List集合。
  • ScalarHandler类：实现类，适合获取一行一列的数据
• QueryRunner：执行sql语句的类
  • 增、删、改：update();
  • 查询：query();

19.2DbUtils的使用步骤

• 导入jar包
  • mysql连接驱动jar包
  • druid-1.1.5.jar包
  • database.properties配置文件
  • commons-dbutils-1.7.jar

Dbutils代码实现

public class DBUtils {
    private static DruidDataSource ds;

    static{
        Properties properties = new Properties();
        InputStream is = DBUtils.class.getResourceAsStream("/database.properties");
        try {
            properties.load(is);
            //创建连接池
            ds = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        try {
            return ds.getConnection(); //通过连接池获得连接对象
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
    //返回一个数据源在Dao下面的impl类用于接收数据源
    public static DataSource getDataSource(){
        return ds;
    }
    }
--------------------------------------------------------------------------------------------------
		测试
    public class TestPool {
    public static void main(String[] args) throws Exception{
        for (int i = 1; i <= 4; i++) {
            Connection connection = DBUtils.getConnection();
            System.out.println(connection);
            //关闭---放回池中
            connection.close();//调用的是DruidPooledConnection实现类里的close()
        }
    }
}

DaoImpl数据访问对象代码实现

public class StudentImpl implements StudentDao {
    //创建QueryRunner对象，并传递一个数据源对象
    private QueryRunner queryRunner = new QueryRunner(DBUtils.getDataSource());

    @Override
    public int insert(Student student) {
        Object[] params = {student.getStuname(),student.getAge(),student.getSex()};
        try {
            int result = queryRunner.update("insert into student(stuname,age,sex) values(?,?,?);",params);
            return result;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int update(Student student) {
        Object[] params = {student.getStuname(),student.getAge(),student.getSex(),student.getSid()};
        try {
            int result = queryRunner.update("update student set stuname =?,age=?,sex=? where sid=?;",params);
            return result;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int delete(int id) {
        try {
            int result = queryRunner.update("delete from student where sid=?;",id);
            return result;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return 0;
    }

    @Override
    public Student select(int id) {
        try {
            //查询记录封装成指定对象返回
            Student query = queryRunner.query("select * from student where sid=?", new BeanHandler<Student>(Student.class) ,id);
            return query;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Student> selectAll() {
        try {
             List<Student> studentList = queryRunner.query("select * from student", new BeanListHandler<Student>(Student.class));
             return studentList;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public long selectStudentNums() {
        try {
            long count = queryRunner.query("select count(*) from student", new ScalarHandler<>());
            return count;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }