Java工具类07dbUtils基础使用

1.依赖包

<dependency>
    <groupId>commons-dbutils</groupId>
    <artifactId>commons-dbutils</artifactId>
    <version>1.7</version>
</dependency>

<!-- 连接池 -->
<dependency>
   <groupId>com.mchange</groupId>
   <artifactId>c3p0</artifactId>
   <version>0.9.5.4</version>
</dependency>

2.数据库与连接池配置

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!-- 默认配置，只可以出现一次 -->
    <default-config>
        <!-- 配置JDBC 四个基本属性 -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&amp;useSSL=false
        </property>
        <property name="user">root</property>
        <property name="password">123456</property>
        <!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 -->
        <property name="acquireIncrement">3</property>
        <!-- 初始化数据库连接池时连接的数量 -->
        <property name="initialPoolSize">5</property>
        <!-- 数据库连接池中的最小的数据库连接数 -->
        <property name="minPoolSize">2</property>
        <!-- 数据库连接池中的最大的数据库连接数 -->
        <property name="maxPoolSize">10</property>
    </default-config>
</c3p0-config>

3.代码示例

表：

查询

获取整个表

//获取c3p0连接池对象
ComboPooledDataSource ds = new ComboPooledDataSource();
QueryRunner runner = new QueryRunner(ds);
String sql2 = "select * from fan_test";
try {
　　　//获取list<Map>
    List<Map<String, Object>> list = (List) runner.query(sql2, new MapListHandler());
    //System.out.println(list);
    for (Object object : list) {
        System.out.println(object);
    }
} catch (SQLException e) {
    e.printStackTrace();
    }

结果：

 

 获取指定数据：

//获取指定数据
private static void findOneMap(Integer id) {
           ComboPooledDataSource ds = new ComboPooledDataSource();
           QueryRunner runner = new QueryRunner(ds);
            String sql = "select * from fan_test where id=?";
             
            try {
                Map map = runner.query(sql, new MapHandler(), uid);
                System.out.println(map);
                
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }
public static void main(String[] args) {
          findOneMap(1);  
    }

结果：

 新增、删除

public class JDBCUtils {

    // 获得c3p0连接池对象
    private static ComboPooledDataSource ds = new ComboPooledDataSource();
/**
     * 获得数据库连接对象
     *
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    /**
     * 获得c3p0连接池对象
     * 
     * @return
     */
    public static DataSource getDataSource() {
        return ds;
    }    
}

    /**
     * 删除和添加测试
     * @throws SQLException
     */
private static void add() throws SQLException {
    QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
    //删除
    runner.update("delete from t_user_test");
    //添加
    for (int i = 0; i < 1000; i++) {
            Object[] objects= new Object[] {UUID.randomUUID().toString(),"test"+i, "pass"+i};
            runner.update("insert INTO t_user_test(uid,loginname,loginpass) values(?,?,?)", objects);
        }
    }

修改

/**
* 修改测试
* @throws SQLException
*/
private static void update() throws SQLException {
    //ComboPooledDataSource ds = new ComboPooledDataSource();
    // dbutis使用数据源
    QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
    // 可变变量  无限 也可以没有 也可以数组
    Object[] objects= new Object[] {"123_test","333_test", "14ba4bd0-a0da-4a2c-b136-de036b54e98a"};
    //runner.update("update t_user_test set loginname=?,loginpass=? where uid=?", "123_dbutils","123_dbutils","14ba4bd0-a0da-4a2c-b136-de036b54e98a");
    runner.update("update t_user_test set loginname=?,loginpass=? where uid=?", objects);
    }

 从数据库读取-写入excel,csv

首先需要自己构造一个idCard类

import com.github.crab2died.annotation.ExcelField;

public class DB_idCard {
    @ExcelField(title = "phone",order = 2)
    private String phone;
    @ExcelField(title = "idcard",order = 3)
    private String idcard;
    @ExcelField(title = "color",order = 4)
    private String color;
    @ExcelField(title = "id",order = 1)
    private int id;
    
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getPhone() {
        return phone;
    }
    public void setPhone(String phone) {
        this.phone = phone;
    }
    public String getIdcard() {
        return idcard;
    }
    public void setIdcard(String idcard) {
        this.idcard = idcard;
    }
    public String getColor() {
        return color;
    }
    public void setColor(String color) {
        this.color = color;
    }
    @Override
    public String toString() {
        return "DB_idCard [phone=" + phone + ", idcard=" + idcard + ", color=" + color + ", id=" + id + "]";
    }
    
    
}

 

　方法1:使用MapListHandler

String filePath3 = basePath + File.separator+"data"+ File.separator +"test4.xlsx";
String filePath4 = basePath + File.separator+"data"+ File.separator +"test4.csv";
ComboPooledDataSource ds = new ComboPooledDataSource();
QueryRunner runner = new QueryRunner(ds);
String sql = "select * from fan_test_tjc";
//获取到List<Map>
List<Map<String, Object>> list2 = runner.query(sql, new MapListHandler());
List<DB_idCard> list3 = new ArrayList<DB_idCard>();
for (Map<String, Object> map : list2) {
    //写入txt
    //FileUtils.write(new File(filePath2), map.toString()+"\n","utf-8",true);
DB_idCard idCard = new DB_idCard();
idCard.setId((int) map.get("id"));
idCard.setPhone(String.valueOf(map.get("phone")));                
　　idCard.setIdcard(String.valueOf(map.get("idcard")));
idCard.setColor(String.valueOf(map.get("color")));
list3.add(idCard);
    }
ExcelUtils.getInstance().exportObjects2Excel(list3, DB_idCard.class, filePath3);
　　ExcelUtils.getInstance().exportObjects2CSV(list3, DB_idCard.class, filePath4);

　方法2：BeanListHandler

String filePath3 = basePath + File.separator+"data"+ File.separator +"test4.xlsx";
String filePath4 = basePath + File.separator+"data"+ File.separator +"test4.csv";
//构造对象集合
List<DB_idCard> list4 = (List)runner.query(sql,new BeanListHandler(DB_idCard.class));
ExcelUtils.getInstance().exportObjects2Excel(list4, DB_idCard.class, filePath3);
ExcelUtils.getInstance().exportObjects2CSV(list4, DB_idCard.class, filePath4);

 

 

从txt读取，写入DB

ComboPooledDataSource ds = new ComboPooledDataSource();
QueryRunner runner = new QueryRunner(ds);
String basePath = System.getProperty("user.dir");
String filePath = basePath + File.separator+"data"+ File.separator +"test.txt";
List<String> list = new ArrayList<String>();
    try {
    //写入数据库
    list = FileUtils.readLines(new File(filePath),"utf-8");
    //清除数据再写
    runner.update("delete from fan_test_tjc");
    int num = 1;
    for (String string : list) {
        String phone = String.valueOf(JSONPath.read(string, "phone"));
        String idcard = String.valueOf(JSONPath.read(string, "idcard"));
        String color = String.valueOf(JSONPath.read(string, "color"));
        Object[] objects = new Object[] {phone,idcard,color,num};
    try {
        runner.update("insert INTO fan_test_tjc (phone,idcard,color,id) values(?,?,?,?)",objects);
        num++;
                
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
        }
     }