JDBC的框架apache的dbutils的使用

apache的dbutils用于数据库的连接和数据库的CRUD操作，现在通过几个简单的测试方法演示最基本的使用
1 下载相应的JAR包

2 各个层次代码

3 代码如下
druid.properties属性配置文件

url=jdbc:mysql://localhost:3306/test1?useSSL=false&rewriteBatchedStatements=true
username=root
password=2222
driverClassName=com.mysql.jdbc.Driver

initialSize=10
maxActive=10

数据库操作类

package com.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import javax.xml.crypto.Data;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtil {
    public static Connection getConnectionDRUID()throws  Exception{
        Properties pros=new Properties();
        InputStream is=ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
        pros.load(is);
        DataSource source= DruidDataSourceFactory.createDataSource(pros);
        Connection conn=source.getConnection();
        return conn;
    }
    public static void closeAll(Connection con, Statement stmt, ResultSet rs){
        try{
            if(null!=con){
                con.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        try{
            if(null!=stmt){
                stmt.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        try{
            if(null!=rs){
                rs.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

bean实体层
package com.bean;

import java.sql.Date;

public class Customer {
private int cus_id;
private String cus_name;
private String cus_email;
private Date cus_birth;

public int getCus_id() {
    return cus_id;
}

public void setCus_id(int cus_id) {
    this.cus_id = cus_id;
}

public String getCus_name() {
    return cus_name;
}

public void setCus_name(String cus_name) {
    this.cus_name = cus_name;
}

public String getCus_email() {
    return cus_email;
}

public void setCus_email(String cus_email) {
    this.cus_email = cus_email;
}

public Date getCus_birth() {
    return cus_birth;
}

public void setCus_birth(Date cus_birth) {
    this.cus_birth = cus_birth;
}

@Override
public String toString() {
    return "Customer{" +
            "cus_id=" + cus_id +
            ", cus_name='" + cus_name + '\'' +
            ", cus_email='" + cus_email + '\'' +
            ", cus_birth=" + cus_birth +
            '}';
}

}
mysql数据库表结构

表记录内容

测试代码

package com.test;

import com.bean.Customer;
import com.mysql.cj.QueryResult;
import com.util.JDBCUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

public class Main1 {
    //测试一下数据库连接用的是druid
    @Test
    public void test1Conn()throws Exception{
        Connection conn= JDBCUtil.getConnectionDRUID();
        String sql="select count(*) from goods";
        PreparedStatement pstm=conn.prepareStatement(sql);
        ResultSet rs=pstm.executeQuery();
        if(rs.next()){
            System.out.println(rs.getInt(1));
        }
    }
    //测试插入数据
    @Test
    public void test2Insert(){
         Connection conn=null;
         PreparedStatement pstm=null;
         try{
             QueryRunner runner=new QueryRunner();
             conn=JDBCUtil.getConnectionDRUID();
             String sql="insert into customer (cus_name,cus_email,cus_birth)values(?,?,?)";
             int insertCount=runner.update(conn,sql,"志刚谢谢","type@aa.com","1984-2-12");
             System.out.println("写入了。"+insertCount);
         }catch (Exception e){
             e.printStackTrace();
         }finally {
             JDBCUtil.closeAll(conn,pstm,null);
         }
    }
    //测试查询
    /*
          BeanHander:是ResultSetHandler接口实现类，用于封装表中的一条记录。
     */
    @Test
    public void testQuery1(){
        Connection conn=null;
        try{
            conn=JDBCUtil.getConnectionDRUID();
            QueryRunner runner=new QueryRunner();
            String sql="select cus_name ,cus_email, cus_birth  from customer where cus_id=?";
            BeanHandler<Customer> handler=new BeanHandler<>(Customer.class);
            Customer customer=runner.query(conn,sql,handler,1);
            System.out.println(customer);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(conn,null,null);
        }
    }
    /*
        BeanListHandler:是ResultSetHandler接口实现类，可以查询多条记录。
     */
    @Test
    public void testQuery2(){
        Connection conn=null;
        try{
            conn=JDBCUtil.getConnectionDRUID();
            QueryRunner runner=new QueryRunner();
            String sql="select cus_name ,cus_email, cus_birth  from customer";
            BeanListHandler<Customer> handler=new BeanListHandler<>(Customer.class);

            List<Customer> list=runner.query(conn,sql,handler);
            Iterator<Customer> iter=list.iterator();
            while (iter.hasNext()){
                System.out.println(iter.next());
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(conn,null,null);
        }
    }
     /*
         MapHander:是ResultSetHandler接口的实现类，对应表中的1条记录。
         将字段对应的值作为key和value
     */
     @Test
    public void testQuery3(){
         Connection conn=null;
         try{
             QueryRunner runner=new QueryRunner();
             conn=JDBCUtil.getConnectionDRUID();
             String sql="select cus_name ,cus_email, cus_birth  from customer";
             MapHandler handler=new MapHandler();
             Map<String,Object>map=runner.query(conn,sql,handler);

                 System.out.println(map);


         }catch (Exception e){
             e.printStackTrace();
         }
     }
     /*

      */
     @Test
    public void testQuery4(){
          Connection conn=null;
          try{
              QueryRunner runner=new QueryRunner();
              conn=JDBCUtil.getConnectionDRUID();
              String sql="select cus_name ,cus_email, cus_birth  from customer";
              MapListHandler handler=new MapListHandler();
              List<Map<String,Object>> list=runner.query(conn,sql,handler);
               Iterator it=list.iterator();
               while (it.hasNext()){
                   System.out.println(it.next());
               }
          }catch (Exception e){
              e.printStackTrace();
          }finally {
              JDBCUtil.closeAll(conn,null,null);
          }
     }
     @Test
    public void testQuery5(){
         Connection conn=null;
         try{
             QueryRunner qr=new QueryRunner();
             conn=JDBCUtil.getConnectionDRUID();
             String sql="select count(*) from goods";
             ScalarHandler handler=new ScalarHandler();
             Long count=(Long)qr.query(conn,sql,handler);
             System.out.println(count);
         }catch (Exception e){
             e.printStackTrace();
         }finally {
             JDBCUtil.closeAll(conn,null,null);
         }
     }
     @Test
    public void testQuery6(){
         Connection conn=null;
         try{
             QueryRunner qr=new QueryRunner();
             conn=JDBCUtil.getConnectionDRUID();
             String sql="select max(cus_birth) from customer";
             ScalarHandler handler=new ScalarHandler();
             Date maxBirth=(Date)qr.query(conn,sql,handler);
             System.out.println(maxBirth);
         }catch (Exception e){
             e.printStackTrace();
         }finally {
             JDBCUtil.closeAll(conn,null,null);
         }
     }

}