代码使用案例:(commons-dbutils-1.4)
@Test//原来不使用dbUtils工具的数据库查询代码实现
public void jdbcQuery() throws Exception{
List<Stud> studs = new ArrayList<Stud>();
Connection con = C3p0Pool.getConnection();
String sql = "select * from stud ";
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
Stud s = new Stud();
s.setId(rs.getString("id"));
s.setName(rs.getString("name"));
studs.add(s);
}
System.out.println(studs);
}
@Test//使用dbUtils工具的数据库查询代码实现
public void dbUtilsQuery() throws Exception{
QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
String sql = "select * from stud ";
List<Stud> studs = run.query(sql,new BeanListHandler<Stud>(Stud.class) );
System.out.println(studs);
}
@Test
public void dbUtilsQuery2() throws Exception{
QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
String sql = "select * from stud ";
List<Map<String, Object>> studs = run.query(sql, new MapListHandler() );
System.out.println(studs);
}
////DbUtils工具的使用演示: 增删改--用update(), 查--用query()方法
/*
CREATE TABLE person(
id VARCHAR(30) PRIMARY KEY,
NAME VARCHAR(30),
address VARCHAR(30),
age INT
);
*/
@Test
public void save() throws Exception{
QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
//statement方式
//run.update("insert into person(id,name,address,age) values('A001','Jack','湖南长沙',22) ");
//prepareStatement方式
run.update("insert into person(id,name,address,age) values(?,?,?,?)","A003","Tom","中国西安",24);//如果参数个数或类型 与 “?”号不匹配,会出异常
}
@Test
public void saveTx() throws Exception{
QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
Connection con = C3p0Pool.getConnection();
try{
con.setAutoCommit(false);
//※注意,实现事务功能时,要传入con对象,且多条语句共处一个事务时,要传入同一个con对象。但如果不实现事务功能,可以有传入con对象
run.update(con,"insert into person(id,name,address,age) values(?,?,?,?)","A003","Tom","浙江杭州",24);
run.update(con,"insert into person(id,name,address,age) values(?,?,?,?)","A003","Rose","江苏苏州",22);
con.commit();
}catch (Exception e) {
con.rollback();
System.out.println("事务回滚了....");
}finally{
con.setAutoCommit(true);
con.close();
}
}
//下面演示一下查询的结果集封装功能
@Test//封装成BeanList
public void query1() throws Exception{
QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
//封装成BeanList: 如果值对象中的属性名和表中的字段名不一致,那么该属性的值返回的是null----解决:采用别名
//List<Person> persons = run.query("select * from person ",new BeanListHandler<Person>(Person.class) );//没有采用别名,addr属性为null
List<Person> persons = run.query("select id,name,address addr,age from person ",new BeanListHandler<Person>(Person.class) );//用属性名 当 字段别名
System.out.println(persons);
}
@Test//封装成MapList
public void query2() throws Exception{
QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
List<Map<String, Object>> persons = run.query("select * from person ",new MapListHandler() );
System.out.println(persons);
}
@Test//封装成BeanList---查询带参数
public void query3() throws Exception{
QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
String sql = "select id,name,address addr,age from person where name like ? and age>? ";
List<Person> persons = run.query(sql,new BeanListHandler<Person>(Person.class),"%a%",25 );
System.out.println(persons);
}
@Test//演示批处理功能
public void batch() throws Exception{
QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
for(int i=1;i<=100;i++){
String sql = "insert into stud(id,name) values(?,?) ";
String str= "000"+i;
str = str.substring(str.length()-3, str.length());
String id1 = "A"+ str;
String id2 = "B"+ str;
//System.out.println(str);
String params[][] = {{id1,"Alice"+i},{id2,"Bob"+i} };
run.batch(sql, params);
}
}
ext增强操作,去除sql语句操作数据库:
////////以下演示扩展包commons-dbutilss-ext.jar的功能//////////////
//注意,下面的用法要生效,必须给值对象添加注解
@Test//封装成BeanList---直接通过JavaBean的字节码查询
public void query4() throws Exception{
ExtQueryRunner run = new ExtQueryRunner(C3p0Pool.getDataSource());
List<Person> persons = run.query(Person.class);//不用sql语句,,直接查询Bean-List
System.out.println(persons);
}
@Test//封装成BeanList---直接通过JavaBean的字节码查询
public void save3() throws Exception{
ExtQueryRunner run = new ExtQueryRunner(C3p0Pool.getDataSource());
Stud stud = new Stud();
stud.setId("A006");
stud.setName("Alice");
run.save(stud);//不用sql语句,,直接存对象
System.out.println(stud);
}
注意:使用ext,值对象要加注解
@Table(value="person")
public class Person {
private String id;
private String name;
@Column(value="address")//该注解无效,反正以后开发时属性名都取成和表字段名一样
private String addr;
private Integer age;
public String getId() {
return id;
}
public void setId(String id) {//可以使得函数名与子段名一致获取
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAddr() {
return addr;
}
public void setAddr(String addr) {
this.addr = addr;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
updata delete 函数要加入id注解:
@Id
private double salay;
@Column
private int age;
@Column
private String name;
@Column
private String id;
@Column
private String sex;
sql语句为:update column注解 from table注解 where id注解