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);
}
}