DBUtils的使用

1.简介

    只使用JDBC进行开发过于繁杂，DBUtils是JDBC的简化开发包。需要导入相关jar包。

    commons-dbutils-1.6.jar     jar名字 

 2.常用类和方法

    QueryRunner：执行SQL语句

            update(Connection conn, String sql, Object… params) 插入表记录、更新表记录、删除表记录

            query(Connection conn, String sql, ResultSetHandler handler, Object… params) 查询表记录

    ResultSetHandler 处理结果集

    2.1QueryRunner类的使用

import java.sql.Connection;
import java.sql.SQLException;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
/*
 * 操作数据表
 */

public class QueryRunnerDemo {
	
	private static Connection con=JDBCUtilsConfig.getConnection();//创建连接
	
	public static void main(String[] args) throws Exception {
		insert();
		update();
		delete(); 
	}
	
	//添加插入
	public static void insert()throws SQLException{
		QueryRunner qr=new QueryRunner();
		String sql="insert into sort (sname,sprice,sdesc)values(?,?,?)";
		Object[] params= {"d",3,"dd"};
		int row=qr.update(con, sql, params);
		System.out.println(row);
		DbUtils.closeQuietly(con);
	}
	
	//修改
	public static void update()throws Exception{
		QueryRunner qr=new QueryRunner();
		String sql="update sort set sname=?,sprice=?,sdesc=? where sid=?";
		Object[] params= {"f",100.88,"oiqwei",4};
		qr.update(con, sql, params);
		DbUtils.closeQuietly(con);
	}
	
	//删除
	public static void delete()throws SQLException{
		QueryRunner qr=new QueryRunner();
		String sql="delete from Sort where sid=?";
		qr.update(con,sql,3);
		DbUtils.close(con);
	}
}

2.2ResultSetHandler 处理结果集的使用

    我所使用数据库描述物品，所以先创建一个相关类

public class Sort {

	private int sid;
	private String sname;
	private double sprice;
	private String sdesc;
	public Sort(int sid, String sname, double sprice, String sdesc) {
		this.sid = sid;
		this.sname = sname;
		this.sprice = sprice;
		this.sdesc = sdesc;
	}
	public Sort() {}
	public int getSid() {
		return sid;
	}
	public void setSid(int sid) {
		this.sid = sid;
	}
	public String getSname() {
		return sname;
	}
	public void setSname(String sname) {
		this.sname = sname;
	}
	public double getSprice() {
		return sprice;
	}
	public void setSprice(double sprice) {
		this.sprice = sprice;
	}
	public String getSdesc() {
		return sdesc;
	}
	public void setSdesc(String sdesc) {
		this.sdesc = sdesc;
	}
	@Override
	public String toString() {
		return "Sort [sid=" + sid + ", sname=" + sname + ", sprice=" + sprice + ", sdesc=" + sdesc + "]";
	}
	
}

这个类会在处理结果集时使用到

处理结果集共有8中方法

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;

/*
 * 数据查询
 */
public class QueryRunnerDemo1 {
	private static Connection con=JDBCUtilsConfig.getConnection();

	public static void main(String[] args) throws Exception {
//		arrayHandler();
//		arrayListHandler();
//		beanHandler();
//		beanListHander();
//		columListHandler();
//		scalarHandler();
//		mapHandler();
		mapListHandler();
	}
	
	/*
	 * 1.ArrayListHandler
	 * 将结果集的每一行，封装到对象数组中
	 */
	public static void arrayListHandler() throws Exception{
		QueryRunner qr=new QueryRunner();
		String sql="select * from sort";
		List<Object[]> result=qr.query(con, sql, new ArrayListHandler());//返回值   返回一个List集合，集合里装的是对象数组
		
		for(Object[] objs:result) {
			for(Object obj:objs) {
				System.out.print(obj+" ");
			}
			System.out.println();
		}
	}
	
	
	/*
	 * 2.ArrayHandler
	 * 将结果集第一行存到数组对象中 
	 */
	public static void arrayHandler()throws SQLException{
		QueryRunner qr=new QueryRunner();
		String sql="select * from sort";
		Object[] obj=qr.query(con, sql, new ArrayHandler());
		for(Object o:obj) {
			System.out.println(o);
		}
	}
	
	/*
	 * 3.BeanHandle
	 * 将结果集的第一行数据封装成JavaBean对象
	 */
	
	public static void beanHandler() throws SQLException {
		QueryRunner qr=new QueryRunner();
		String sql="select * from sort where sid=2";
		Sort s=qr.query(con, sql,new BeanHandler<Sort>(Sort.class));
		System.out.println(s);
	}
	
	/*
	 * 4.BeanListHandler
	 * 将结果集的每一行封装成javaBean对象
	 * 将多个javaBean对象封装到List集合中
	 */
	public static void beanListHander() throws SQLException {
		QueryRunner qr=new QueryRunner();
		String sql="select * from sort";
		List<Sort> list=qr.query(con, sql, new BeanListHandler<Sort>(Sort.class));
		for(Sort s:list) {
			System.out.println(s);
		}
	}
	
	/*
	 * 5.ColumListHandler
	 * 结果集指定列的数据存储的List集合
	 * 
	 */
	public static void columListHandler() throws SQLException {
		QueryRunner qr=new QueryRunner();
		String sql="select * from sort";
		List<Object> list=qr.query(con, sql,new ColumnListHandler<Object>("sname"));//指定列！
		for(Object obj:list) {
			System.out.println(obj);
		}
	}
	
	/*
	 * 6.ScalarHandler
	 * 一个结果集的 列 装到一个类中
	 */
	public static void scalarHandler()throws Exception{
		QueryRunner qr=new QueryRunner();
		String sql="select count(*) from sort";  //count(*)
		long count=qr.query(con, sql, new ScalarHandler<Long>());
		System.out.println(count);
	}
	
	/*
	 * 7.MapHandler
	 * 将结果集的 第一行 封装的map集合中
	 * Map<键，值>  键=列名  值=这列的数据
	 */
	public static void mapHandler() throws Exception{
		QueryRunner qr=new QueryRunner();
		String sql="select * from sort";
		Map<String,Object> map=qr.query(con, sql, new MapHandler());
		for(String key:map.keySet()) {
			System.out.println(key+" "+map.get(key));
		}
	}
	
	/*
	 * 8.MapListHandler
	 * 将结果集的每一行封装到map集合中
	 */
	public static void mapListHandler() throws Exception{
		QueryRunner qr=new QueryRunner();
		String sql="select * from sort";
		List<Map<String,Object>> list=qr.query(con, sql, new MapListHandler());//返回值是list 而list中存储的map集合 形成集合的嵌套
		for(Map<String,Object> map:list) {
			for(String key:map.keySet()) {
				System.out.println(key+" "+map.get(key));
			}
		}
	}
}

这就是利用工具类简化java中JDBC的操作

基本操作：增删改查