package com.xin.Test;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import javax.sql.DataSource;
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.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import com.xin.Domain.Account;
import com.xin.Util.MysqlUtil;
/**
* DbUtils框架的使用
* 目的:减轻CURD操作
* DbUtils框架最核心的类,就是QueryRunner类,构造其有空参构造和带连接池(DataSource)的构造
*
*
*
*
*
*/
public class DbUtils {
public static void main(String[] args) {
/**
* 数据库为test,在c3p0-config.xml中配置
*/
// queryRunner();
//下面是ResultSetHandler的接口的各种实现类的用法(7个实现类)
// beanHandler(); //针对JavaBean
// beanListHandler(); //针对JavaBean
// arrayHandler(); //针对数组
// arrayListHandler(); //针对数组
// mapHandler(); //针对Map
// mapListHandler(); //针对Map
scalarHandler(); //针对Long
}
//QueryRunner
public static void queryRunner(){
//获取连接池
DataSource ds = MysqlUtil.getPool();
//用连接池构造一个QueryRunner
QueryRunner qr = new QueryRunner(ds);
// String sql = "update account set name=? where id=?";
String sql = "insert into account(name, money) values(?, ?)";
try {
// qr.update(sql, new Object[]{"O(∩_∩)O哈哈~", 2});
qr.update(sql, new Object[]{"一生有你", 25000});
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//BeanHandler
public static void beanHandler(){
QueryRunner qr = new QueryRunner(MysqlUtil.getPool());
String sql = "select * from account where id = ?";
try {
Account acc = (Account) qr.query(sql, new Object[]{3}, new BeanHandler(Account.class));
System.out.println(acc.getId()+" "+acc.getName()+" "+acc.getMoney());
} catch (SQLException e) {
e.printStackTrace();
}
}
//BeanListHandler
public static void beanListHandler(){
QueryRunner qr = new QueryRunner(MysqlUtil.getPool());
String sql = "select * from account";
try {
List<Account> accounts = (List<Account>) qr.query(sql, new BeanListHandler(Account.class));
for(Account a : accounts){
System.out.println(a.getId()+" "+a.getName()+" "+a.getMoney());
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//ArrayHandler
public static void arrayHandler(){
QueryRunner qr = new QueryRunner(MysqlUtil.getPool());
String sql = "select * from account where money=?";
try {
Object[] a = (Object[]) qr.query(sql, 10000, new ArrayHandler());
for(int i = 0;i < a.length;i++){
System.out.print(a[i]+" ");
}
System.out.println("\n------------------------------");
} catch (SQLException e) {
e.printStackTrace();
}
}
//ArrayListHandler
public static void arrayListHandler(){
QueryRunner qr = new QueryRunner(MysqlUtil.getPool());
String sql = "select * from account";
try {
List<Object[]> accounts = (List<Object[]>) qr.query(sql, new ArrayListHandler());
for(Object[] obj : accounts){
for(int i = 0;i < obj.length;i++){
System.out.print(obj[i]+" ");
}
System.out.println("\n------------------------------");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//MapHandler
public static void mapHandler(){
QueryRunner qr = new QueryRunner(MysqlUtil.getPool());
String sql = "select * from account";
try {
Map<Object, Object> map = (Map<Object, Object>) qr.query(sql, new MapHandler());
Set<Entry<Object, Object>> entry = map.entrySet();
for(Entry e : entry){
System.out.print(e.getKey()+" ");
System.out.println(e.getValue());
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//MapListHandler
public static void mapListHandler(){
QueryRunner qr = new QueryRunner(MysqlUtil.getPool());
String sql = "select * from account";
try {
List< Map<Object, Object> > maps = (List<Map<Object, Object>>) qr.query(sql, new MapListHandler());
for(Map<Object, Object> map : maps){
Set<Entry<Object, Object>> entry = map.entrySet();
for(Entry e : entry){
System.out.print(e.getKey()+" ");
System.out.println(e.getValue());
}
System.out.println("-------------------------");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//ScalarHandler 只返回一行一列数据
public static void scalarHandler(){
QueryRunner qr = new QueryRunner(MysqlUtil.getPool());
String sql = "select count(*) from account";
try {
Object obj = qr.query(sql, new ScalarHandler());
System.out.println(obj);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
DBUtils工具
最新推荐文章于 2021-03-16 20:15:08 发布