java JDBC (七) org.apache.commons.dbutils 查询

时间:2022-12-07 08:13:10
package cn.sasa.demo1;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map; import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler; import cn.sasa.demo5.DBProperties; public class ResultSetHandlerDemo {
static Connection conn = null;
static {
try {
conn = DBProperties.getConnection();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} public static void main(String[] args) throws SQLException {
//arrayHandler();
//arrayListHandler();
//beanHandler();
//beanListHandler();
//columnListHandler();
//scalarHandler();
//mapHandler();
mapListHandler();
}
/**
* ArrayHandler 获取一行
*/
static void arrayHandler() throws SQLException {
QueryRunner query = new QueryRunner();
String sql = "SELECT * FROM product WHERE pid=?;";
Object[] objArr = query.query(conn, sql, new ArrayHandler(),4);
for(Object obj : objArr) {
System.out.println(obj);
}
} /**
* ArrayListHandler
*/
static void arrayListHandler() throws SQLException {
QueryRunner query = new QueryRunner();
String sql = "SELECT * FROM product WHERE pid<?;";
List<Object[]> objList = query.query(conn, sql, new ArrayListHandler(),4);
for(Object[] objArr : objList) {
for(Object obj : objArr) {
System.out.print(obj);
System.out.print("\t");
}
System.out.println("");
}
} /**
* BeanHandler
* 封装成JavaBean对象,JavaBean对象必须有空参构造函数
*/
static void beanHandler() throws SQLException {
QueryRunner query = new QueryRunner();
String sql = "SELECT * FROM product;";
Product product = query.query(conn, sql, new BeanHandler<Product>(Product.class));
System.out.println(product.getPname());
} /**
* 转成一个对象集合
*/
static void beanListHandler() throws SQLException {
QueryRunner query = new QueryRunner();
String sql = "SELECT * FROM product WHERE pid<?;";
List<Product> objList = query.query(conn, sql, new BeanListHandler<Product>(Product.class),4);
for(Product obj : objList) {
System.out.println(obj.getPname());
}
} /**
* columnListHandler
* 指定列的数据存到集合
*/
static void columnListHandler() throws SQLException {
QueryRunner query = new QueryRunner();
String sql = "SELECT * FROM product WHERE pid<?;";
List<Object> objList = query.query(conn, sql, new ColumnListHandler<Object>("pname"),4);
for(Object obj : objList) {
System.out.println(obj);
}
} /**
* ScalarHandler 获取单行单列的结果集
*/
static void scalarHandler() throws SQLException {
QueryRunner query = new QueryRunner();
String sql = "SELECT COUNT(*) AS row_count FROM product WHERE pid<?;";
Object obj = query.query(conn, sql, new ScalarHandler<Object>(),4);
System.out.println(obj);
} /**
* MapHandler 将第一行结果集封装到Map集合中
* 键:列名
* 值:列中的数据
* @throws SQLException
*/
static void mapHandler() throws SQLException {
QueryRunner query = new QueryRunner();
String sql = "SELECT * FROM product";
Map<String, Object> map = query.query(conn, sql, new MapHandler());
for(String key : map.keySet()) {
System.out.println(map.get(key).toString());
}
} /**
* MapListHandler
* 结果集的每一行 列:值 存到 Map集合的 键:值
* Map集合再存到List集合中
* @throws SQLException
*/
static void mapListHandler() throws SQLException {
QueryRunner query = new QueryRunner();
String sql = "SELECT * FROM product";
List<Map<String,Object>> mapList = query.query(conn, sql, new MapListHandler());
for(int i=0; i<mapList.size(); i++) { for(String key : mapList.get(i).keySet()) {
System.out.print("key:" + key + " val:" + mapList.get(i).get(key) + "\t");
}
System.out.println("");
}
}
}
package cn.sasa.demo1;

public class Product {
private int pid;
private String pname;
private double price;
private String ptype;
private String create_tm; public Product() {}
public Product(int pid, String pname,double price,
String ptype, String create_tm) {
this.pid = pid;
this.pname = pname;
this.price = price;
this.ptype = ptype;
this.create_tm = create_tm;
}
public int getPid() {
return pid;
}
public void setPid(int pid) {
this.pid = pid;
}
public String getPname() {
return pname;
}
public void setPname(String pname) {
this.pname = pname;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public String getPtype() {
return ptype;
}
public void setPtype(String ptype) {
this.ptype = ptype;
}
public String getCreate_tm() {
return create_tm;
}
public void setCreate_tm(String create_tm) {
this.create_tm = create_tm;
}
}