Java 使用 Dbutils 工具类库 操作mysql

Dbutils 官网http://commons.apache.org/proper/commons-dbutils/

结合我个人写的JDBCUtils 写的 Dbutils 的使用demo . JDBCUtils 传送门:http://www.cnblogs.com/wwcherish/p/8037395.html

package JDBCDemo;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.xml.bind.annotation.W3CDomHandler;

import org.apache.commons.dbutils.DbUtils;
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;

import com.mysql.jdbc.Connection;

public class dbutilsdemo1 {
    private static Connection connection  = JDBCUtils.getConnection();
    private static QueryRunner qRunner = new QueryRunner();
    public static void main(String[] args) {
        try {
            //insert();
            //delete();
            //ArraryHandlerdemo();
//            beanListHandlerDemo();
            //ColumnListHandlerDemo();
            //mapHandlerDemo();
            mapListHandlerDemo();
        } catch (Exception e) {
            // TODO: handle exception
            System.out.println(e);
        }
        
    }
    public static void insert() throws SQLException {
        //insert  delete update 
        String sql = "insert into zhangwu (name,money) values (?,?);";
        Object[] object = {"打麻将支出",100};
        int row = qRunner.update(connection,sql,object);
        DbUtils.closeQuietly(connection);
        if (row>0) {
            System.out.println("插入数据成功");
        }else {
            System.out.println("插入数据失败");
        }
        
    }
    
    public static void update() throws SQLException {
        String sql = "update zhangwu set money=? where id=?;";
        Object[] object = {500,4};
        int row = qRunner.update(connection,sql,object);
        DbUtils.closeQuietly(connection);
        if (row>0) {
            System.out.println("修改数据成功");
        }else {
            System.out.println("修改数据失败");
        }
    }
    
    public static void delete() throws SQLException {
        String sql = "delete from  zhangwu  where id=?;";
        Object[] object = {100};
        int row = qRunner.update(connection,sql,object);
        DbUtils.closeQuietly(connection);
        if (row>0) {
            System.out.println("删除数据成功");
        }else {
            System.out.println("删除数据失败");
        }
    }
    
    public static void ArraryHandlerdemo () throws SQLException {
        //返回1条数据集
        //ArraryHandler
        String sql = "select * from  zhangwu;";
        Object[] res = qRunner.query(connection, sql, new ArrayHandler());
        for(Object object : res) {
            System.out.print(object+"\t");
        }
        
    }
    public static void ArraryListHandlerDemo() throws SQLException {
        //返回整个数据集
        String sql = "select * from  zhangwu;";
        List<Object[]>  res = qRunner.query(connection, sql, new ArrayListHandler());
        for(Object[] objs : res) {
            for(Object oo : objs) {
                System.out .print(oo  + "\t");
            }
            System.out.println("");
        }
    }
    public static void beanHandler() throws SQLException {
        //返回1条数据集
        String sql = "select * from  zhangwu;";
        Zhangwu zw = qRunner.query(connection,sql,new BeanHandler<Zhangwu>(Zhangwu.class));
        System.out.println(zw);
    }
    public static void beanListHandlerDemo() throws SQLException{
        //返回整个数据集
        String sql = "select * from  zhangwu;";
        List<Zhangwu> zwlist= qRunner.query(connection, sql, new BeanListHandler<Zhangwu>(Zhangwu.class));
        for(Zhangwu zwchild : zwlist) {
            System.out.println(zwchild.toString());
        }
    }
    public static void ColumnListHandlerDemo()  throws SQLException {
        //单列结果查询
        String sql = "select distinct name from  zhangwu";
        List<Object> zwlist= qRunner.query(connection, sql, new ColumnListHandler<Object>("name"));
        for(Object zwchild : zwlist) {
            System.out.println(zwchild.toString());
        }
    }
    public static void scalarHandlerDemo() throws SQLException {
        //单结果集查询
        String sql = "select count(*) from  zhangwu;";
        Long num =  qRunner.query(connection, sql, new ScalarHandler<Long>());
        System.out.println(num);
    }
    public static void mapHandlerDemo() throws SQLException {
        //结果集第一行数据 封装到map集合中
        String sql = "select * from  zhangwu;";
        Map< String,Object >  map= qRunner.query(connection,sql,new MapHandler());
        for(String key : map.keySet()) {
            System.out.println(key + "..." + map.get(key));
        }
    }
    public static void mapListHandlerDemo() throws SQLException {
        // 封装到map集合中 适合少量数据
        String sql = "select * from  zhangwu;";
        List<Map<String, Object>>  listmap = qRunner.query(connection, sql, new MapListHandler());
        for(Map<String, Object> mapchild : listmap) {
            for(String key : mapchild.keySet()) {
                System.out.print(key + " = " + mapchild.get(key) + "  ");
            }
            System.out.println("");
        }
    }
}