Apache DbUtils + Druid 联合使用演示

package com.xmaven;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.xmaven.entity.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;

/**
 * @ClassName DbUtilsSample
 * @Description TODO Apache DbUtils + Druid 联合使用演示
 * @Author Ambition
 * @Date 2021/2/5 19:12
 * @Version 1.0.0
 **/
public class DbUtilsSample {
    private static void query(){
        Properties properties = new Properties();
        String propertyFile = DbUtilsSample.class.getResource("/druid-config.properties").getPath();
        try {
            propertyFile = URLDecoder.decode(propertyFile,"UTF-8");
            properties.load(new FileInputStream(propertyFile));
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            // 利用Apache DbUtils大幅简化了数据的提取过程
            QueryRunner qr = new QueryRunner(dataSource);
            /**
             * 参数1 第一个是SQL语句
             * 参数2 第二个是转换的实体类，必须和数据库中的字段一一对应
             * 参数3 传入的参数
             */
            List<User> userList = qr.query("select * from user limit ?,10",
                    new BeanListHandler<>(User.class),
                    new Object[]{10});
            for (User user : userList) {
                System.out.println(user);
            }
        } catch (UnsupportedEncodingException | FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public static void update(){
        Properties properties = new Properties();
        String propertyFile = DbUtilsSample.class.getResource("/druid-config.properties").getPath();
        Connection conn = null;
        try {
            propertyFile = URLDecoder.decode(propertyFile,"UTF-8");
            properties.load(new FileInputStream(propertyFile));
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            String sql1 = "update user set salary=salary+1000 where id=?";
            String sql2 = "update user set salary=salary-500 where id=?";
            QueryRunner qr = new QueryRunner();
            qr.update(conn,sql1,new Object[]{1});
            qr.update(conn,sql2,new Object[]{2});
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                if (conn!=null&&!conn.isClosed()){
                    conn.rollback();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }finally {
            try {
                if (conn!=null&&!conn.isClosed()){
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {
        query();
        update();
    }
}