JDBC批量操作

1、jdbc工具类抽取

上一篇做了jdbc的基本操作，但是获取连接及释放资源是比较重复的操作，可以抽取工具类而达到代码重用的目的
工程结构如图

jdbc工具类代码
db.properties

driver=com.mysql.jdbc.driver
url=jdbc:mysql://192.168.47.151:3306/web?useunicode=true&characterencoding=utf8
username=root
password=root

jdbcutils.java

package com.rookie.bigdata.util;

import java.io.inputstream;
import java.sql.*;
import java.util.properties;
import java.util.resourcebundle;

/**
 * created by dell on 2019/5/22.
 */
package com.rookie.bigdata.util;

import java.io.inputstream;
import java.sql.*;
import java.util.properties;
import java.util.resourcebundle;

/**
 * created by dell on 2019/5/22.
 */
public class jdbcutils {

    private static string driver;
    private static string url;
    private static string username;
    private static string password;

    //    //静态代码块加载配置文件信息
//    static {
//        resourcebundle db = resourcebundle.getbundle("db");
//        driver = db.getstring("driver");
//        url = db.getstring("url");
//        username = db.getstring("username");
//        password = db.getstring("password");
//    }
    //静态代码块加载配置文件信息
    static {
        try {
            //获取类加载器
            classloader classloader = jdbcutils.class.getclassloader();
            //通过类加载器的方法获取一个输入流
            inputstream resourceasstream = classloader.getresourceasstream("db.properties");
            properties properties = new properties();
            properties.load(resourceasstream);
            //获取相关参数的值
            driver = properties.getproperty("driver");
            url = properties.getproperty("url");
            username = properties.getproperty("username");
            password = properties.getproperty("password");
        } catch (exception e) {
            e.printstacktrace();
        }

    }


    /**
     * 获取连接
     *
     * @return
     */
    public static connection getconnection() {

        connection conn = null;
        try {
            class.forname(driver);
            conn = drivermanager.getconnection(url, username, password);
        } catch (exception e) {
            e.printstacktrace();
        }
        return conn;
    }

    /**
     * 释放资源
     * @param conn
     * @param pstmt
     * @param rs
     */
    public static void relase(connection conn, preparedstatement pstmt, resultset rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (sqlexception e) {
                e.printstacktrace();
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (sqlexception e) {
                e.printstacktrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (sqlexception e) {
                e.printstacktrace();
            }
        }

    }

}

2、批量插入数据

package com.rookie.bigdata;

import com.rookie.bigdata.util.jdbcutils;

import java.sql.connection;
import java.sql.preparedstatement;

/**
 * create table `user` (
 * `username` varchar(30) default null comment '用户名',
 * `password` varchar(10) default null comment '密码'
 * ) engine=innodb default charset=utf8;
 */
public class jdbcbatch {
    public static void main(string[] args) throws exception {
        connection connection = jdbcutils.getconnection();
        //设置自动提交关闭
        connection.setautocommit(false);
        preparedstatement preparedstatement = connection.preparestatement("insert into user values (?,?)");

        for (int i = 1; i <= 5000; i++) {
            preparedstatement.setstring(1, "张三" + i);
            preparedstatement.setstring(2, "123" + i);
            preparedstatement.addbatch();
            if (i % 1000 == 0) {
                preparedstatement.executeupdate();
                connection.commit();
                preparedstatement.clearbatch();
            }
        }
        preparedstatement.executeupdate();
        connection.commit();
        preparedstatement.clearbatch();

    }
}