Java的DBUtils工具演示
掌握 DbUtils工具的关键是:学好2个(类+接口)
1、QueryRunner类 用于执行SQL语句
2、ResultSetHandler接口 用于封装返回结果 具体使用时 是用他的实现类
在演示中我使用的是c3p0连接池。
/* SQL脚本
* CREATE TABLE person(
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(32),
address VARCHAR(30),
age INT
);
还要建立person的值对象
*/
@Test //不带事务的增,不带事务会自动帮我们关流 Statement
public void save1() throws Exception {
QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
String sql="INSERT INTO person VALUES('A002','Jack','长沙',25);";
run.update(sql);
}
@Test //不带事务的增,PreparedStatement
public void save2() throws Exception {
QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
//可变参数,内部会自动帮我们把参数赋值给对应序号的占位符'?'
run.update("INSERT INTO person VALUES('A003','Rose',?,?)","湖南益阳",20);
}
@Test //带事务的增,PreparedStatement
public void save3() throws Exception {
QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
//事务
Connection con=C3p0Pool.getConnection();
try {
con.setAutoCommit(false);
//结果 错误的回滚了,正确的进库了
run.update("INSERT INTO person VALUES('A004','张三',?,?)","益阳",28);
run.update("INSERT INTO person VALUES('A003','李四',?,?)","杭州",24);
con.commit();
} catch (Exception e) {
con.rollback();
System.out.println("事务回滚...");
}finally{
con.setAutoCommit(true);
con.close();
}
}
@Test //带事务的增,PreparedStatement
public void save4() throws Exception {
QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
//事务
Connection con=C3p0Pool.getConnection();
try {
con.setAutoCommit(false);
//结果 使用同一个con,都回滚了
run.update(con,"INSERT INTO person VALUES('A004','张三',?,?)","益阳",28);
run.update(con,"INSERT INTO person VALUES('A003','李四',?,?)","杭州",24);
con.commit();
} catch (Exception e) {
con.rollback();
System.out.println("事务回滚...");
}finally{
con.setAutoCommit(true);
con.close();
}
}
@Test //使用DBuUtils 进行删除和修改,原理同新增
public void update() throws Exception {
QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
run.update("update person set age=age-2");
}
在查询中,重新过in写了一个值对象,其中的arrdess改成addr。
//查询
@Test //封装成MapList
public void query() throws Exception {
QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
String sql = "select * from person ";
List<Map<String, Object>> person = run.query(sql, new MapListHandler());
System.out.println(person);
}
@Test //封装成BeanList
public void query2() throws Exception {
QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
String sql = "select * from person ";
List<Person> person = run.query(sql, new BeanListHandler<Person>(Person.class));
System.out.println(person);
}
@Test //如果出现值对象中属性名和表字段名不相同,则需要别名
public void query3() throws Exception {
QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
//用别名解决不一致的情况
String sql = "select id,name,address,age from person ";
List<Person2> person2 = run.query(sql, new BeanListHandler<Person2>(Person2.class));
System.out.println(person2);
}
@Test //带参数
public void query4() throws Exception {
QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
//使用占位符设置参数
String sql = "select id,name,address,age from person where name like ? and age > ? ";
List<Person2> person2 = run.query(sql, new BeanListHandler<Person2>(Person2.class),"%j%",18);
System.out.println(person2);
}
演示扩展包内容
/以下演示---扩展包和批处理
//自动生成sql语句,我们必须在JavaBean中添加相应的注解,让我们省去写sql语句
@Test //直接让给扩展包内部,帮我们利用Person值对象
public void query5() throws Exception{
//该方式必须给Person类添加注解 @Table(value="person")//对应的数据库表
ExtQueryRunner run = new ExtQueryRunner(C3p0Pool.getDataSource());
List<Person> person = run .query(Person.class);
System.out.println(person);
}
@Test // O/R Object/Relation 对象/关系
public void save5() throws Exception{
//该方式下 需要在 值对象的属性中加@Column
Person p = new Person();
p.setId("A005");
p.setAge(30);
p.setName("MMMM");
ExtQueryRunner run = new ExtQueryRunner(C3p0Pool.getDataSource());
run.save(p);
}
@Test // 不一致的 添加
public void save6() throws Exception{
//该方式下 需要在 值对象的属性中加@Column
Person2 p = new Person2();
p.setId("A006");
p.setAge(30);
p.setName("ddddd");
p.setAddr("XXX");
ExtQueryRunner run = new ExtQueryRunner(C3p0Pool.getDataSource());
run.save(p);
}
//批处理
@Test
public void dom() throws Exception{
QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
for(int i = 0 ; i <100;i++){
String sql="insert into stud values(?,?)";
String [][] values={
{"Q001"+i,"Jack"+i},
{"Q002"+i,"Tomm"+i}
};
run.batch(sql, values);
}
}
附录
将获取连接做成工具
public class C3p0Pool {
private static DataSource ds;// 单例的池
private static ThreadLocal<Connection> t = new ThreadLocal<Connection>();
static {
try {
ds =new ComboPooledDataSource();
} catch (Exception e) {
throw new RuntimeException("数据库连接池创建失败!");
}
}
// 以后会用到这个功能 :
public static DataSource getDataSource() {
return ds;
}
public static Connection getConnection() {
Connection con = t.get();
if (con == null) {
try {
con = ds.getConnection();
t.set(con);
} catch (SQLException e) {
e.printStackTrace();
}
}
return con;
}
public static void cleanConFromThradeLocal() {
t.set(null);
}
}
连接池的工具包
http://pan.baidu.com/s/1hrQBXyO