QueryRunner操作

QueryRunner操作

前面通过数据库连接池获取到了连接，传统方法就是在获取到连接后，通过连接得到prepareStatement来执行sql语句，进而来改变数据库。

String sql = "insert into table set(,,,,)values(?,?,?,?)";
PrepareStatement ps = conn.prepareStatement(sql);
ps.setObject(1,?);
ps.setObject(2,?);
ps.setObject(3,?);
ps.setObject(4,?);
ps.execute();
ps.close();
in.closer();
conn.close();

现在将通过prepareStatement对数据库进行CRUD进行封装，通过dbutils工具类使用queryRunner来操作。

1、导入commons-dbutils-jar包

2、调用QueryRunner的增删改查方法

2.1增删改是共用QueryRunner.uptate方法

只需要传入操作数据库的一条连接，增删改的sql语句以及sql语句中相应的参数即可。

public void testQueryRunner(){
Connection conn = null;
try{
	QueryRunner qr = new QueryRunner();
	conn = JDBCUtils.getConnection();
	String sql = "update table set ...";
	qr.update(conn,sql,...);
}catch(Exception e){
	e.printStackTrace:
}finally{
	JDBCUtils.closeResource(conn);
}
}

1.2查询操作对应QueryRunner.query()方法

在该方法中处了要传入连接操作数据库的连接，sql语句、返回的类型以及sql中的参数。其中，返回值类型多种多样，可以是查询返回单个对象，也可能是单个对象构成的集合、可能是数据库的数据量等等。

1.2.1 BeanHandler<>

public Customer testQueryRunnerForOneQuery(){
    Connection conn = null;
    try{
        conn = JDBCUtils.getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "select * from where id =?";
        BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
        Customer customer = qr.query(conn,sql,handler,sql,?);
        return customer;
        JDBCUtils.closeResource(conn);
        
    }
}

1.2.2 BeanListHandler<>

public List<Customer> testQueryRunnerForListQuery(){
    Connection conn = null;
    try{
        conn = JDBCUtils.getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "select * from table ";
        BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
        List<Customer> customer = qr.query(conn,sql,handler);
        return customer;
        JDBCUtils.closeResource(conn);
    }
}

1.2.3 MapHandler()方法

将查询到的结果中的字段和值通过键值对的形式存储到Map中

public void testQueryRunnerForMapQuery(){
    Connection conn = null;
    try{
        conn = JDBCUtils.getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "select * from table where id = ?";
        MapHandler handler = new MapHandler();
       	Map map = qr.query(conn,sql,handler,?);
        system.out.println(map);
        JDBCUtils.closeResource(conn);
    }
}

1.2.4 MapListHandler()方法

将查询到的结果中的多个对象存储到Map中

public void testQueryRunnerForMapListQuery(){
    Connection conn = null;
    try{
        conn = JDBCUtils.getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "select * from table ";
        MapListHandlder handler = new MapListHandler();
        List<Map<String,Object> customer = qr.query(conn,sql,handler);
       system.out.println (customer);
        JDBCUtils.closeResource(conn);
    }
}

1.2.ScalarHandler()方法

public void testQueryRunnerForScalarQuery(){
    Connection conn = null;
    try{
        conn = JDBCUtils.getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "select account(*) from table";
        ScalarHandler handler = new ScalarHandler();
        Long account = (Long)qr.query(conn,sql,handler);
       	system.out.println (account);
        JDBCUtils.closeResource(conn);
    }
}

ount = (Long)qr.query(conn,sql,handler);
system.out.println (account);
JDBCUtils.closeResource(conn);
}
}