DruidJDBCUtil工具类(Driuid和JDBC)

一.引言

读到了一片关于excel数据批量导入导出的文章，提到了使用原声jdbc的优点：批量插入速度快、没有sql语句大小限制。所以写了一个DruidJDBCUtil学习记录，使用原生jdbc连接，并使用Druid进行连接池的控制。

二.实现

a.maven依赖

// mysql连接驱动
<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
  
 //连接池
 <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.18</version>
        </dependency>

b.配置文件druid.properties

# druid.properties配置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/blog-data
username=root
password=******
initialSize=10
maxActive=50
maxWait=60000

c.JDBCDruidUtil工具类

public class JDBCDruidUtil {
    private static DataSource dataSource;

    static {
        Properties pro = new Properties();
        //加载数据库连接池对象
        try {
            //获取数据库连接池对象
            pro.load(JDBCDruidUtil.class.getClassLoader().getResourceAsStream("druid.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(pro);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /*
    获取连接
     */
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }


    /**
     * 关闭conn,和 statement独对象资源
     *
     * @param connection
     * @param statement
     * @MethodName: close
     * @return: void
     */
    public static void close(Connection connection, Statement statement) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭 conn , statement 和resultset三个对象资源
     *
     * @param connection
     * @param statement
     * @param resultSet
     * @MethodName: close
     * @return: void
     */
    public static void close(Connection connection, Statement statement, ResultSet resultSet) {
        close(connection, statement);
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /*
    获取连接池对象
     */
    public static DataSource getDataSource() {
        return dataSource;
    }
}

d.测试用例

public class ExcelTest {
    @Autowired
    private DemoServiceImpl demoService;

    public void import2DBFromExcel10wWithMybatisplus(List<Map<Integer,String>> datalist){
        long start = System.currentTimeMillis();
        List<Demo> demos = datalist.stream().map(item -> {
            Demo demo = new Demo();
            demo.setId(item.get(0));
            demo.setTitle(item.get(1));
            demo.setId(item.get(2));
            return demo;
        }).collect(Collectors.toList());
        demoService.saveBatch(demos);
        System.out.println("mybatis-plus耗时" + (System.currentTimeMillis()-start) + "毫秒");
    }




    public Map<String, Object> import2DBFromExcel10w(List<Map<Integer, String>> dataList) {
        HashMap<String, Object> result = new HashMap<>();
        //结果集中数据为0时,结束方法.进行下一次调用
        if (dataList.size() == 0) {
            result.put("empty", "0000");
            return result;
        }
        //JDBC分批插入+事务操作完成对10w数据的插入
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            long startTime = System.currentTimeMillis();
            System.out.println(dataList.size() + "条,开始导入到数据库时间:" + startTime + "ms");
            conn = JDBCDruidUtil.getConnection();
            //控制事务:默认不提交
            conn.setAutoCommit(false);
            String sql = "insert into demo(id, title, tag) VALUES ";
            sql += "(?,?,?)";
            ps = conn.prepareStatement(sql);
            //循环结果集:这里循环不支持"烂布袋"表达式
            for (int i = 0; i < dataList.size(); i++) {
                Map<Integer, String> item = dataList.get(i);
                ps.setString(1, item.get(0));
                ps.setString(2, item.get(1));
                ps.setString(3, item.get(2));
                //将一组参数添加到此 PreparedStatement 对象的批处理命令中。
                ps.addBatch();
            }
            //执行批处理
            ps.executeBatch();
            //手动提交事务
            conn.commit();
            long endTime = System.currentTimeMillis();
            System.out.println(dataList.size() + "条,结束导入到数据库时间:" + endTime + "ms");
            System.out.println(dataList.size() + "条,导入用时:" + (endTime - startTime) + "ms");
            result.put("success", "1111");
        } catch (Exception e) {
            result.put("exception", "0000");
            e.printStackTrace();
        } finally {
            //关连接
            JDBCDruidUtil.close(conn, ps);
        }
        return result;
    }


    @Test
    public void createData(){
        List<Map<Integer,String>> list = new ArrayList<>();
        for(int i=0; i<300000; i++){
            Map<Integer,String> map = new HashMap<>();
            map.put(0, String.valueOf(i));
            map.put(1, String.valueOf(i));
            map.put(2, String.valueOf(i));
            list.add(map);
        }
        import2DBFromExcel10w(list);
    }

    @Test
    public void createData2(){
        List<Map<Integer,String>> list = new ArrayList<>();
        for(int i=0; i<300000; i++){
            Map<Integer,String> map = new HashMap<>();
            map.put(0, String.valueOf(i));
            map.put(1, String.valueOf(i));
            map.put(2, String.valueOf(i));
            list.add(map);
        }
        import2DBFromExcel10wWithMybatisplus(list);
    }
}

e.测试结果对比

jdbc插入耗时:11288msms
mybatis-plus插入耗时：11921ms

后续准备学习整理excel如何实现大批量数据快入导入导出及优化。