Apache DBUtils常用方法总结

首先我们的明白Apache DBUtils是干什么用的：它是dao层的一个帮助类，简化了dao层jdbc的开发，下面直接开始讲述实现步骤：

1.下载commons-dbutils-1.7.jar
其中包含了三个基本类：
DbUtils辅助,QueryRunner：增删改查. ResultSetHandler查询返回集合
还需要引入一种数据源的jar，和jdbc的jar。
下面是各自的常用方法
1.DbUtils常用方法：

2.QueryRunner类常用方法：

3.ResultSetHandler：

测试：

数据库的表：

Student类：

package Clss.apacheddbutil;

public class Student {
private int stuno;
private String stuname;
private int stuage;
private String gname;

public Student(int stuno, String stuname, int stuage, String gname) {
        this.stuno = stuno;
        this.stuname = stuname;
        this.stuage = stuage;
        this.gname = gname;
    }
    public Student() {

    }
    public int getStuno() {
        return stuno;
    }

    public void setStuno(int stuno) {
        this.stuno = stuno;
    }

    public String getStuname() {
        return stuname;
    }

    public void setStuname(String stuname) {
        this.stuname = stuname;
    }

    public int getStuage() {
        return stuage;
    }

    public void setStuage(int stuage) {
        this.stuage = stuage;
    }

    public String getGname() {
        return gname;
    }

    public void setGname(String gname) {
        this.gname = gname;
    }
}

查询：

package Clss.apacheddbutil;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import util.DataSourceUtil;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.sql.SQLOutput;
import java.util.List;
import java.util.Map;

public class query {
    //查询,自动提交事务
    //返回单行Object
    public static void testArrayHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXml());
        Object[] student = runner.query("select *from student where stuno >? and stuname like ?",new ArrayHandler(),new Object[]{10,"%z%"});//ArrayHandaler只取一行
        System.out.println(student.length);
        System.out.println(student[0]+","+student[1]+","+student[2]+","+student[3]);
    }//顺序默认是oracle 表的顺序
    //返回多行Object
    public static void testArrayListHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXml());
        List<Object[]> students = runner.query("select *from student where stuno >?",new ArrayListHandler(),5);//ArrayHandaler只取一行
        for(Object[] student:students){
            System.out.println(student[0]+","+student[1]+","+student[2]+","+student[3]);
        }//顺序默认是oracle表的顺序

    }


    //Student
    //查询单行数据（放入对象中）
    public static void testBeanHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXml());
        Student student = runner.query("select *from student where stuno >?",new BeanHandler<Student>(Student.class),10);//ArrayHandaler只取一行
        System.out.println(student.getStuname()+","+student.getStuno()+","+student.getStuage()+","+student.getGname());
    }//注意创建的Student类里面的参数必须和该表的属性一模一样  比如说必须是Student类里面必须定义stuno ，不能写成stu
    //输出可以根据自己的喜好  调整输出参数的位置


    //查询多行数据
    public static void testBeanListHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXml());
        List<Student> students = runner.query("select *from student where stuno >?",new BeanListHandler<Student>(Student.class),10);//ArrayHandaler只取一行
        for(Student student:students) {
            System.out.println(student.getStuname() + "," + student.getStuno() + "," + student.getStuage() + "," + student.getGname());
        }
    }

    //查询多行数据（放入map中）
    public static void testBeanMapHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXml());                        //注意Oracle默认数据类型是BigDecimal,不能用Integer
        Map<BigDecimal,Student> students = runner.query("select *from student where stuno >?",new BeanMapHandler<BigDecimal,Student>(Student.class,"stuno"),10);//ArrayHandaler只取一行
        Student stu = students.get(new BigDecimal(13));//坑！！！！！不能写13 必须转成BigDecimal类型
        System.out.println(stu.getStuname() + "," + stu.getStuno() + "," + stu.getStuage() + "," + stu.getGname());
    }


.......................................Map   ->返回的结果为前面有属性名
    //返回第一行数据
public static void testMapHandler() throws SQLException {
    QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXml());
    Map<String,Object> student = runner.query("select *from student where stuno >?",new MapHandler(),656);
    System.out.println(student);
}
    //返回第多行数据
    public static void testMapListHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXml());
        List<Map<String,Object>> students = runner.query("select *from student where stuno >?",new MapListHandler(),10);
        for(Map<String,Object> student:students)
        System.out.println(student);
    }

//多行加字段   给{}一个key值
public static void testKeyedHandler() throws SQLException {
    QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXml());
    Map<String,Map<String,Object>> students = runner.query("select *from student where stuno >?",new KeyedHandler<String>("stuname"),10);
    System.out.println(students);
}

///................................................把结果集中的某一列  保存到List中
public static void testColumnListHandler() throws SQLException {
    QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXml());
    List<String> students = runner.query("select *from student where stuno >?",new ColumnListHandler<String>("stuname"),10);
    System.out.println(students);
}
//ScalarHandler ：单值结果
public static void testScalarHandler() throws SQLException {
    QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXml());
    BigDecimal result = runner.query("select count(1) from student where stuno >?",new ScalarHandler<BigDecimal>(),10);
    System.out.println(result);//sql: select stuno from student where stu0= ?;  param:13
}
public static void main(String[] args) throws SQLException {
//        testArrayHandler();
           testArrayListHandler();
        //testBeanHandler();
//        testBeanListHandler();
        //testBeanMapHandler();
//        testMapHandler();
//        testMapListHandler();
//        testKeyedHandler();
//        testColumnListHandler();
        //testScalarHandler();
    }
}

增删改：

package Clss.apacheddbutil;

import org.apache.commons.dbutils.QueryRunner;
import util.DataSourceUtil;

import java.sql.SQLException;

public class UpdateDemo {
    //自动提交
    //增加
    public static void add() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXml());
        int count = runner.update("insert into student(stuno,stuname,stuage,gname) values(?,?,?,?)",new Object[]{50,"huhuilin",18,"sss"});
        System.out.println(count);//返回增删改的 条数
    }

    //删除
    public static void delete() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXml());
        int count = runner.update("delete from student where stuno = ?",999);
        System.out.println(count);//返回增删改的 条数
    }

    //修改
    public static void update() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXml());
        int count = runner.update("update student set stuname=? where stuno=?",new Object[]{"wangyin",4});
        System.out.println(count);//返回增删改的 条数
    }

  public static void main(String[] args) throws SQLException {
//        add();
//        delete();
          update();
    }
}

注意，有些ojdbc.jar包和本项目不兼容！