DBUtils增删改查操作
java编程中的数据库操作实用工具,封装了jdbc的操作,简化了jdbc的书写,小巧简单实用。
1.对于数据表的读取操作,它可以把结果集转成 List Array Set等集合;
2.对于数据表的写操作,变的更简单(只需要sql 语句就可以了);
3.可以使用数据库连接池技术,来优化性能,重用已经构建好的数据库连接对象。
DBUtils中主要用QueryRunner类,提供了对sql语句操作的API,使用时先创建一个QueryRunner对象:
QueryRunner qr = QueryRunner(DataSource ds);DataSource ds可以是DBCP或者C3P0的任意一个,只要提供使用方法就可以,数据库连接池文中C3P0Utils.java中有使用。
将连接池给DBUtils框架使用,之后的每一个操作都会从连接池中获取一个新的连接,每条sql语句都是一个单独的事务。这个类中主要有三个方法:
1、update() 用于执行 insert update delete语句
package com.qf.dbutils;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;
import com.qf.bean.Book;
import com.qf.utils.C3P0Utils;
//update()
public class Demo_Update {
//insert增加方法
@Test
public void testInsert01() throws SQLException{
//1.获取QueryRunner 对象
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql = "insert into book(name,price,num) values('html',10,12)";
qr.update(sql);
}
@Test
public void testInsert02() throws SQLException{
//1.获取QueryRunner 对象
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql = "insert into book(name,price,num) values(?,?,?)";
//update的多参数构造方法,每个参数代表一个sql语句中的?
qr.update(sql, "photoshop",23,6);
}
//update改方法
@Test
public void testUpdate(){
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql = "update book set price=? where id=1";
try {
qr.update(sql,80);//
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//delete删方法
@Test
public void testDelete() throws SQLException{
//1.获取QueryRunner 对象
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql = "delete from book where id=13";
qr.update(sql);
}
@Test
public void testDelete2() throws SQLException{
//1.获取QueryRunner 对象
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql = "delete from book where name like ?";
qr.update(sql,"%java%");
}
}
2、query() 用于执行 select语句
@Test
public void testQuery1(){
//1.创建queryRunner对象
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
//查询 图书信息 返回List<Book> 集合
//query()方法 执行 select查询
try {
List<Book> list = qr.query("select * from book", new ResultSetHandler<List<Book>>(){
@Override
public List<Book> handle(ResultSet arg0) throws SQLException {
List<Book> list = new ArrayList<>();
while(arg0.next()){
Book book = new Book();
book.setId(arg0.getInt("id"));
book.setName(arg0.getString("name"));
book.setPrice(arg0.getDouble("price"));
book.setNum(arg0.getInt("num"));
book.setCategory(arg0.getString("category"));
book.setDescription(arg0.getString("description"));
list.add(book);
}
return list;
}
});
System.out.println(list);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//1.BeanHandler 把每条记录封装成对象 适合取一条记录
@Test
public void testQuery2(){
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
try {
Book b = qr.query("select * from book where id=?", new BeanHandler<Book>(Book.class),1);
System.out.println(b);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//2.BeanListHandler 把每条记录封装成对象 将对象存储到List集合中
@Test
public void testQuery3(){
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
try {
List<Book> list = qr.query("select * from book where id=? or id=?",
new BeanListHandler<Book>(Book.class), 2,9);
System.out.println(list);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//3.ArrayHandler用于取一条数据,把该数据的每一列的值存储到数组Object[ ]中
@Test
public void testQuery3() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
Object[] arr = qr.query("select * from book where id=1", new ArrayHandler());
for(Object obj : arr){
System.out.println(obj);
}
}
//4.ArrayListHandler取多条记录,把这些记录的每一列的值存储到一个数组Object[ ]中,然后把数组封装到集合List中
@Test
public void testQuery4() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
List<Object[]> list = qr.query("select * from book", new ArrayListHandler());
for(Object[] obj:list){
for(Object o : obj){
System.out.print(o+",");
}
System.out.println();
}
}
//5.ColumnListHandle(参数),获取某一列的数据,封装到List集合中,参数为查询表的字段名(列名)或者字段号(列号从1开始)
@Test
public void testQuery5() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
List<Object> list = qr.query("select * from book", new ColumnListHandler(2));
for(Object obj:list){
System.out.println(obj);
}
}
//6.MapHandler取一条记录,把当前记录的列名 和 列值 放到一个Map中
@Test
public void testQuery6() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
Map<String, Object> map = qr.query("select * from book where id=1", new MapHandler());
for(Map.Entry<String, Object> entry : map.entrySet()){
System.out.println(entry.getKey()+"::"+entry.getValue());
}
}
//7.KeyedHandler 取多条记录
//每条记录封装到Map中 再把Map封装到另一个Map中
// Map<key,Map<key,value>>
// 内Map :key字段名 value:字段值
// 外Map :key指定字段的值(自己设置的,如下面例子中的new KeyedHandler("id")里的id),value:内Map
//key 为指定的字段值
@Test
public void testQuery7() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
Map<Object, Map<String,Object>> map = qr.query("select * from book", new KeyedHandler("id"));
for(Map.Entry<Object, Map<String,Object>> entry : map.entrySet()){
System.out.println(entry.getKey());
for(Map.Entry<String, Object> e : entry.getValue().entrySet()){
System.out.println(e.getKey()+";;;;"+e.getValue());
}
System.out.println("-------------");
}
}
//8.MapListHandler 取多条记录 把当前记录封装到Map中 再把Map封装到List中
@Test
public void testQuery8() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
List<Map<String, Object>>list = qr.query("select * from book", new MapListHandler());
for(Map<String, Object> map : list){
for(String key : map.keySet()){
System.out.println(key+";;;;"+map.get(key));
}
System.out.println("--------------");
}
}
//9.ScalarHandler 获取单行单列数据,主要用于分页查询,如下例获取数据总条数
@Test
public void testQuery9() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
Object obj = qr.query("select count(*) from book", new ScalarHandler());
System.out.println(obj);
}
3、 batch() 批处理,执行多条语
* 所需要的参数包括一个二维数组
* Object[][] params = new Object[5][]; 高维 的数 5 决定是执行sql语句的次数
* 低维数组的元素 就是给 sql语句 ? 赋值的
package com.qf.dbutils;
import java.sql.SQLException;
import java.util.Arrays;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;
import com.qf.utils.C3P0Utils;
public class Demo_Batch {
@Test
public void testBatchInsert() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
//batch方法 第二个参数 是一个二维数组 所以 我们先创建一个二维数组
Object[][] params = new Object[5][];
//给二维数组中 每一个一维数组的元素 进行赋值
for(int i=0;i<params.length;i++){
params[i] = new Object[]{"html",22,39};
}
int[] arr = qr.batch("insert into book(name,price,num) values(?,?,?)", params);
System.out.println(Arrays.toString(arr));
}
@Test
public void testBatchDelete() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
Object[][] params = new Object[3][];
for(int i=0;i<params.length;i++){
params[i] = new Object[]{i};
}
qr.batch("delete from book where id=?", params);
}
}