commons-dbutils 是Apache組織提供的一個開源JDBC工具類庫，封裝了針對于針對于數(shù)據(jù)庫的增刪改查操作

package dbutils.util;

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

import org.apache.commons.dbutils.QueryRunner;
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.junit.Test;

import bean.Thesis;
import util.JDBCUtils;

/*
 * commons-dbutils 是Apache組織提供的一個開源JDBC工具類庫，封裝了針對于針對于數(shù)據(jù)庫的增刪改查操作
 */
public class QueryRunnerTest {

    //插入 
    @Test
    public void testInsert()  {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection();
            String sql = "insert into thesis(t_name,t_author,t_create_time,t_magazine,t_number)values(?,?,?,?,?)";//？表示占位符
            int update = runner.update(conn, sql, "哈哈哈","書籍",new Date(),"嘿嘿",2);
            if(update>0) {
                System.out.println("添加成功");
            }else {
                System.out.println("添加失敗");
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn, null);
        }
    }
    
//  查詢操作
    /*/
     * BeanHandler:是ResultSetHandler接口的實現(xiàn)類，用于封裝表中的一條記錄。
     */
    @Test
    public void testQuery1() {
        Connection conn=null;
        try {
            conn = c3p0.util.JDBCUtils.getConnection3();
            QueryRunner runner = new QueryRunner();
            String sql = "select id,t_name,t_author,t_magazine,t_number from thesis where id = ?";
            BeanHandler<Thesis> handler = new BeanHandler<>(Thesis.class);
            Thesis query = runner.query(conn, sql, handler, 5);
            System.out.println(query);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            c3p0.util.JDBCUtils.closeResource(conn, null);          
        }
    }
    /*
     * BeanListHandler:是ResultSetHandler接口的實現(xiàn)類，用于封裝表中的多條記錄的集合。
     */
    @Test
    public void testQuery2() {
        Connection conn=null;
        try {
            conn = c3p0.util.JDBCUtils.getConnection3();
            QueryRunner runner = new QueryRunner();
            String sql = "select id,t_name,t_author,t_magazine,t_number from thesis where id < ?";
            BeanListHandler<Thesis> handler = new BeanListHandler<>(Thesis.class);
             List<Thesis> query = runner.query(conn, sql, handler, 5);
            query.forEach(System.out::println);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            c3p0.util.JDBCUtils.closeResource(conn, null);          
        }
    }
    /*/
     * MapHandler:是ResultSetHandler接口的實現(xiàn)類，用于封裝表中的一條記錄。
     * 將字段及相應(yīng)字段的值作為map中額key和value
     */
    @Test
    public void testQuery3() {
        Connection conn=null;
        try {
            conn = c3p0.util.JDBCUtils.getConnection3();
            QueryRunner runner = new QueryRunner();
            String sql = "select id,t_name,t_author,t_magazine,t_number from thesis where id = ?";
            MapHandler handler = new MapHandler();
             Map<String, Object> query = runner.query(conn, sql, handler, 5);
            System.out.println(query);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            c3p0.util.JDBCUtils.closeResource(conn, null);          
        }
    }
    /*/
     * MapListHandler:是ResultSetHandler接口的實現(xiàn)類，用于封裝表中的多條記錄。
     * 將字段及相應(yīng)字段的值作為map中額key和value
     */
    @Test
    public void testQuery4() {
        Connection conn=null;
        try {
            conn = c3p0.util.JDBCUtils.getConnection3();
            QueryRunner runner = new QueryRunner();
            String sql = "select id,t_name,t_author,t_magazine,t_number from thesis where id < ?";
            MapListHandler handler = new MapListHandler();
              List<Map<String,Object>> query = runner.query(conn, sql, handler, 5);
              query.forEach(System.out::println);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            c3p0.util.JDBCUtils.closeResource(conn, null);          
        }
    }

@Test
    public void testQuery5() {
        Connection conn=null;
        try {
            conn = c3p0.util.JDBCUtils.getConnection3();
            QueryRunner runner = new QueryRunner();
            String sql ="select count(*) from thesis";
            ScalarHandler handler = new ScalarHandler();
             Long query = (Long) runner.query(conn, sql, handler);
              System.out.println(query);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            c3p0.util.JDBCUtils.closeResource(conn, null);          
        }
    }
    
    @Test
    public void testQuery6() {
        Connection conn=null;
        try {
            conn = c3p0.util.JDBCUtils.getConnection3();
            QueryRunner runner = new QueryRunner();
            String sql ="select max(t_create_time) from thesis";
            ScalarHandler handler = new ScalarHandler();
            Date query =  (Date) runner.query(conn, sql, handler);
              System.out.println(query);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            c3p0.util.JDBCUtils.closeResource(conn, null);          
        }
    }
    
    //自定義ResultSetHandler的實現(xiàn)類
    @Test
    public void testQuery7() {
        Connection conn=null;
        try {
            QueryRunner runner =new QueryRunner();
            conn = c3p0.util.JDBCUtils.getConnection3();
            String sql = "select id,t_name,t_author,t_create_time,t_magazine,t_number from thesis where id = ?";
            ResultSetHandler<Thesis> handler=new ResultSetHandler<Thesis>() {
                @Override
                public Thesis handle(ResultSet rs) throws SQLException {
                if(rs.next()) {
                    int id = rs.getInt("id");
                    String t_name = rs.getString("t_name");
                    String t_author = rs.getString("t_author");
                    java.sql.Date t_create_time = rs.getDate("t_create_time");
                    String t_magazine = rs.getString("t_magazine");
                    int t_number = rs.getInt("t_number");
                    Thesis thesis =new Thesis(id, t_name, t_author, t_create_time, t_magazine, t_number);
                    return thesis;
                }
                    return null;
                }
            };
            Thesis thesis = runner.query(conn, sql, handler,23);
            System.out.println(thesis);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            c3p0.util.JDBCUtils.closeResource(conn, null);          
        }
    }

    //關(guān)閉資源查詢的
    /*
     * 使用dbutils.jar包中提供的DbUtils 工具類，實現(xiàn)資源的關(guān)閉
     */
    public static void closeResource1(Connection conn,Statement ps,ResultSet rs) {
//      try {
//          DbUtils.close(conn);
//      } catch (SQLException e) {
//          e.printStackTrace();
//      }
//      try {
//          DbUtils.close(ps);
//      } catch (SQLException e) {
//          e.printStackTrace();
//      }
//      try {
//          DbUtils.close(rs);
//      } catch (SQLException e) {
//          e.printStackTrace();
//      }
//  }
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
        }
}