Java工具类07dbUtils基础使用

1.依赖包

 1 <dependency>
 2     <groupId>commons-dbutils</groupId>
 3     <artifactId>commons-dbutils</artifactId>
 4     <version>1.7</version>
 5 </dependency>
 6 
 7 <!-- 连接池 -->
 8 <dependency>
 9    <groupId>com.mchange</groupId>
10    <artifactId>c3p0</artifactId>
11    <version>0.9.5.4</version>
12 </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);  
    }

结果：

 新增、删除

 1 public class JDBCUtils {
 2 
 3     // 获得c3p0连接池对象
 4     private static ComboPooledDataSource ds = new ComboPooledDataSource();
 5 /**
 6      * 获得数据库连接对象
 7      *
 8      * @return
 9      * @throws SQLException
10      */
11     public static Connection getConnection() throws SQLException {
12         return ds.getConnection();
13     }
14 
15     /**
16      * 获得c3p0连接池对象
17      * 
18      * @return
19      */
20     public static DataSource getDataSource() {
21         return ds;
22     }    
23 }
24 
25     /**
26      * 删除和添加测试
27      * @throws SQLException
28      */
29 private static void add() throws SQLException {
30     QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
31     //删除
32     runner.update("delete from t_user_test");
33     //添加
34     for (int i = 0; i < 1000; i++) {
35             Object[] objects= new Object[] {UUID.randomUUID().toString(),"test"+i, "pass"+i};
36             runner.update("insert INTO t_user_test(uid,loginname,loginpass) values(?,?,?)", objects);
37         }
38     }

修改

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

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

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

 1 import com.github.crab2died.annotation.ExcelField;
 2 
 3 public class DB_idCard {
 4     @ExcelField(title = "phone",order = 2)
 5     private String phone;
 6     @ExcelField(title = "idcard",order = 3)
 7     private String idcard;
 8     @ExcelField(title = "color",order = 4)
 9     private String color;
10     @ExcelField(title = "id",order = 1)
11     private int id;
12     
13     
14     public int getId() {
15         return id;
16     }
17     public void setId(int id) {
18         this.id = id;
19     }
20     public String getPhone() {
21         return phone;
22     }
23     public void setPhone(String phone) {
24         this.phone = phone;
25     }
26     public String getIdcard() {
27         return idcard;
28     }
29     public void setIdcard(String idcard) {
30         this.idcard = idcard;
31     }
32     public String getColor() {
33         return color;
34     }
35     public void setColor(String color) {
36         this.color = color;
37     }
38     @Override
39     public String toString() {
40         return "DB_idCard [phone=" + phone + ", idcard=" + idcard + ", color=" + color + ", id=" + id + "]";
41     }
42     
43     
44 }

　方法1:使用MapListHandler

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

　方法2：BeanListHandler

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

从txt读取，写入DB

 1 ComboPooledDataSource ds = new ComboPooledDataSource();
 2 QueryRunner runner = new QueryRunner(ds);
 3 String basePath = System.getProperty("user.dir");
 4 String filePath = basePath + File.separator+"data"+ File.separator +"test.txt";
 5 List<String> list = new ArrayList<String>();
 6     try {
 7     //写入数据库
 8     list = FileUtils.readLines(new File(filePath),"utf-8");
 9     //清除数据再写
10     runner.update("delete from fan_test_tjc");
11     int num = 1;
12     for (String string : list) {
13         String phone = String.valueOf(JSONPath.read(string, "phone"));
14         String idcard = String.valueOf(JSONPath.read(string, "idcard"));
15         String color = String.valueOf(JSONPath.read(string, "color"));
16         Object[] objects = new Object[] {phone,idcard,color,num};
17     try {
18         runner.update("insert INTO fan_test_tjc (phone,idcard,color,id) values(?,?,?,?)",objects);
19         num++;
20                 
21     } catch (Exception e) {
22         // TODO Auto-generated catch block
23         e.printStackTrace();
24         }
25      }

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/tongjc-0901/p/12660421.html