DBUtils中的JavaBean的编写
import java.io.Serializable;
public class Bean implements Serializable{
private String loc;
private String dname;
private int deptno;
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
public Bean(String loc, String name, int id) {
super();
this.loc = loc;
this.dname = name;
this.deptno = id;
}
public Bean() {}
}
javabean的编写根据自己的需要修改,最好是里面的成员变量和你表中的成员变量名称一一对应,再用source的自动生成方法,把get,set和构造器写好,不然如果方法名,javabean中成员变量和你表中的成员变量名称不统一会出现问题,因为QueryRunner类会使用JavaBean的方法,名字不统一会出现无法正确赋值的问题。
DBUtils的各种用法
import java.sql.SQLException;
import java.util.List;
import java.util.ListResourceBundle;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;
/**
* @author 浪子
* @function DBUtils的各种用法
*/
public class DBUtils {
//删除
public static void Delete() throws Exception {
QueryRunner qr = new QueryRunner(C3P0Utiles.getdatasource());
String sql = "delete from dept where loc=?";
Bean bean = new Bean("水星","宇宙部",205);
int row = qr.update(sql,bean.getLoc());
System.out.println("执行行数:"+row);
}
//修改数据
public static void Updata() throws Exception {
QueryRunner qr = new QueryRunner(C3P0Utiles.getdatasource());
String sql = "update dept set deptno=?,dname=?,loc=? where loc=?";
Bean bean = new Bean("水星","宇宙部",204);
int row = qr.update(sql,bean.getDeptno(),bean.getDname(),bean.getLoc(),"北京");
System.out.println("执行行数:"+row);
}
//插入数据
public static void Insert() throws Exception {
QueryRunner qr = new QueryRunner(C3P0Utiles.getdatasource());
String sql = "insert into dept values(?,?,?)";
Bean bean = new Bean("水星","宇宙部",205);
int row = qr.update(sql,bean.getDeptno(),bean.getDname(),bean.getLoc());
System.out.println("执行行数:"+row);
}
//BeanListHandler来查询多行行记录并返回指定类型
public static void BeanListHandler() throws Exception {
QueryRunner qr = new QueryRunner(C3P0Utiles.getdatasource());
String sql = "select * from dept";
List<Bean> list = qr.query(sql, new BeanListHandler<Bean>(Bean.class));
for(Bean bean:list) {
System.out.println("地址:"+bean.getLoc()+" 名字:"+bean.getDname()+" 编号:"+bean.getDeptno());
}
}
//BeanHandler来查询单行记录并返回指定类型
public static void BeanHandler() throws Exception {
QueryRunner qr = new QueryRunner(C3P0Utiles.getdatasource());
String sql = "select * from dept";
Bean bean = qr.query(sql, new BeanHandler<Bean>(Bean.class));
System.out.println("地址:"+bean.getLoc()+" 名字:"+bean.getDname()+" 编号:"+bean.getDeptno());
}
//MapHandler来查询单行数据记录
public static void MapHandler() throws Exception {
QueryRunner qr = new QueryRunner(C3P0Utiles.getdatasource());
String sql = "select * from dept";
Map<String, Object> map = qr.query(sql, new MapHandler());
System.out.println(map);
}
//MapListHandler来读取多行数据记录(一般查询所有)
public static void MapListHandler() throws Exception {
QueryRunner qr = new QueryRunner(C3P0Utiles.getdatasource());
String sql = "select * from dept";
List<Map<String, Object>> list = qr.query(sql, new MapListHandler());
for(Map<String, Object> map:list) {
System.out.println(map);
}
}
//ScalarHandler一般用来获取聚合函数的结果,此处我用了Number类,以
//避免出现数据过大问题,使用long或者int类型可以根据实际情况而定
public static void ScalarHandler() throws Exception {
QueryRunner qr = new QueryRunner(C3P0Utiles.getdatasource());
String sql = "select count(1) from dept";
Number nub = (Number)qr.query(sql, new ScalarHandler());
System.out.println(nub.intValue());
}
}
此处用到了我之前编写的C3P0工具类,需要的可以点击连接获取
C3P0工具类的编写
使用DBUtils的基本模板:
- 创建QueryRunner类对象 QueryRunner qr = new QueryRunner(连接池)
- 写出SQL语句
- 使用QueryRunner的方法,获取返回值
- 对返回值进行处理