使用DBUtils第三方工具类，实现MySQL数据库的增删改查

DBUtuls第三方工具类

使用到的有：
1.ResultSetHandler接口
    结果集处理接口
2.QueryRunner类
    完成数据库的增/删/改/查
3.DbUtils类
    定义关闭资源与事物处理方法

1.自定义JDBC工具类与配置文件，实现数据库的连接

pro.properties配置文件

DriverPath=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mydatabase
username=root
password=123

JDBCUtils 自定义工具类

package DBUitlsDemo;

import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

/**
 * JDBC工具类
 * Created by Aongi on 2017/5/1.
 * Version 1.0
 */
public class JDBCUtils {
    public JDBCUtils(){}
    private static Connection CON;
    static{
        FileReader reader;
        try {
            String a=JDBCUtils.class.getResource("pro.properties").toString().substring(6);
            reader = new FileReader(a);
            //下面也是一种拿到文件路径的方式
            //InputStream reader=JDBCUtils.class.getClassLoader().getResourceAsStream("pro.properties");
            Properties pro = new Properties();
            pro.load(reader);
            reader.close();
            Class.forName(pro.getProperty("DriverPath"));
            CON= DriverManager.getConnection(pro.getProperty("url"),pro.getProperty("username"),pro.getProperty("password"));
        } catch (Exception e) {
            throw new RuntimeException("连接数据库失败！");
        }
    }
    /*
     *链接数据库操作，返回Connection对象
     */
    public static Connection getConnection(){
        return CON;
    }
}

2.定义与数据库匹配的实体类：Students

1.私有变量对应数据库的列
2.必须有空参构造方法
3.get/set方法
4.重写toString方法

package DBUitlsDemo;
/**
 * Created by Aongi on 2017/5/2.
 * Version 1.0
 */
public class Students {
    private Integer uid;
    private String uname;
    private String uaddress;
    private Integer age;

    public Students(Integer uid, String uname, String uaddress, Integer age) {
        this.uid = uid;
        this.uname = uname;
        this.uaddress = uaddress;
        this.age = age;
    }
    public Students(){}

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public String getUname() {
        return uname;
    }

    public void setUname(String uname) {
        this.uname = uname;
    }

    public String getUaddress() {
        return uaddress;
    }

    public void setUaddress(String uaddress) {
        this.uaddress = uaddress;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return uid + "\t" + uname + "\t" + uaddress + "\t" + age;
    }
}

3.实现增删改查类：QueryRunnerDemo

1.增加/修改/删除，写到了一个方法里，传入两个参数
    String sql   SQL语句
    Object[] params SQL语句中的？占位符数组集
2.查询数据-----8中结果集处理方式
    1.ArrayHandler        查询------取单行遍历
    2.ArrayListHandler    查询------取多行遍历
    3.BeanHandler         查询------取单行遍历
    4.BeanListHandler     查询------取多行遍历
    5.MapHandler          查询------取单行遍历
    6.MapListHandler      查询------取多行遍历
    7.ColumnListHandler   查询列数据
    8.ScalarHandler       查询单个数据

package DBUitlsDemo;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * Created by Aongi on 2017/5/2.
 * Version 1.0
 */
public class QueryRunnerDemo {
    private static Connection CON = JDBCUtils.getConnection();

    //数据的增加，修改，删除
    public static void addUpdataDelete(String sql, Object[] params) throws Exception {
        QueryRunner qr = new QueryRunner();
        int i = qr.update(CON, sql, params);
        DbUtils.close(CON);
        System.out.println(i + "条语句执行成功");
    }

    //main方法
    public static void main(String[] args) throws Exception {
        /**
         * 增加数据
         */
//        String sql="INSERT INTO users (uname,uaddress,age) VALUES (?,?,?);";
//        Object[] params={"小李","guangzhou","30"};
//        addUpdataDelete(sql,params);
        /**
         * 修改数据
         */
//        String sql="UPDATE users SET uname=?,uaddress=?,age=? WHERE uid=?;";
//        Object[] params={"小赵","chengdu","28","10"};
//        addUpdataDelete(sql,params);
        /**
         * 删除数据
         */
//        String sql="DELETE FROM users WHERE uid=?;";
//        Object[] params={"10"};
//        addUpdataDelete(sql,params);

        /**
         * 查询数据---8种结果集处理方式
         */
        select();
    }

    /**
     * 多种方式---数据的查询
     */
    public static void select() throws Exception {
        arrayHandler();//ArrayHandler查询
        System.out.println("--------------------------------------------------");
        arrayListHandler();//ArrayListHandler查询
        System.out.println("--------------------------------------------------");
        beanHandler();//BeanHandler查询
        System.out.println("--------------------------------------------------");
        beanListHandler();//BeanListHandler查询
        System.out.println("--------------------------------------------------");
        columnListHandler();//ColumnListHandler查询
        System.out.println("--------------------------------------------------");
        mapHandler();//MapHandler查询
        System.out.println("--------------------------------------------------");
        mapListHandler();//MapListHandler查询
        System.out.println("--------------------------------------------------");
        scalarHandler();//ScalarHandler查询
    }

    /**
     * ArrayHandler查询-----取一行遍历
     */
    public static void arrayHandler() throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT * FROM users WHERE uid=4;";
        Object[] data = qr.query(CON, sql, new ArrayHandler());
        for (Object obj : data) {
            System.out.print(obj + "\t");
        }
        System.out.println();
    }

    /**
     * ArrayListHandler查询------取多行遍历
     */
    public static void arrayListHandler() throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT * FROM users WHERE uid>4;";
        List<Object[]> data = qr.query(CON, sql, new ArrayListHandler());
        for (Object[] obj : data) {
            for (Object o : obj) {
                System.out.print(o + "\t");
            }
            System.out.println();
        }
    }

    /**
     * BeanHandler查询------取一行遍历
     * 实体类必须要有空参构造方法
     */
    public static void beanHandler() throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT * FROM users WHERE uid=4;";
        Students stu = qr.query(CON, sql, new BeanHandler<Students>(Students.class));
        System.out.println(stu);
    }

    /**
     * BeanListHandler查询------取多行遍历
     * 实体类必须要有空参构造方法
     */
    public static void beanListHandler() throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT * FROM users WHERE uid>4;";
        List<Students> stulist = qr.query(CON, sql, new BeanListHandler<Students>(Students.class));
        for (Students stu : stulist) {
            System.out.println(stu);
        }
    }
    /**
     * ColumnListHandler查询列数据
     * 注意：sql查询语句里查询的数据必须包含你想要查询的列数据
     */
    public static void columnListHandler() throws SQLException{
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT * FROM users;";
        List<Object> uname = qr.query(CON,sql,new ColumnListHandler<Object>("uname"));
        for (Object obj : uname){
            System.out.print(obj + "\t");
        }
        System.out.println();
    }
    /**
     * MapHandler查询 -----取单行遍历
     * 这里用到了Map集合的遍历
     */
    public static void mapHandler() throws SQLException{
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT * FROM users WHERE uid=4;";
        Map<String,Object> map= qr.query(CON,sql,new MapHandler());
        for(String key: map.keySet()){
            System.out.print(key+"="+map.get(key)+"\t");
        }
        System.out.println();
    }
    /**
     * MapListHandler查询-----取多行遍历
     * 取出的结果是有序的，继承自LinkedHashMap<String,Object>类
     */
    public static void mapListHandler() throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT * FROM users WHERE uid>4;";
        List<Map<String,Object>> list= qr.query(CON,sql,new MapListHandler());
        for (Map<String,Object> map:list){
            for(String key: map.keySet()){
                System.out.print(key+"="+map.get(key)+"\t");
            }
            System.out.println();
        }
    }
    /**
     * ScalarHandler查询 ----- 单个数据
     * ScalarHandler<Object>泛型最好是Object类型
     */
    public static void scalarHandler() throws SQLException{
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT COUNT(*) FROM users";
        Object uname=qr.query(CON,sql,new ScalarHandler<Object>());
        System.out.println(uname);
    }

}