Mysql插入大量数据（Mybatis和PreparedStatement）

mysql插入大数据量时（几万甚至几十万数据），使用普通的for循环插入，非常慢，有开发经验的应该知道，此处就不做耗时对比，只给出工作中使用的两种方案

第一种：PreparedStatement

要使用批处理方法，要开启批处理，在数据库配置上添加rewriteBatchedStatements=true

jdbc.url=jdbc\:mysql\://ip\:3306/test?serverTimezone\=Hongkong&useUnicode\=true&characterEncoding\=utf8&useOldAliasMetadataBehavior\=true&rewriteBatchedStatements\=true&tcpRcvBuf\=1024000&allowMultiQueries\=true&useSSL\=false

public class Test {
    @Autowired
    DataSource dataSource;

    String sql = "insert into table_name (column1,column2,column3,column4,column5) values (?,?,?,?,?)";

    /**
     * 批量执行sql
     *
     * @param sql sql语句
     * @param values 值
     * @param dataSource 数据源
     */
    public void executeBatchByPrepareStatement(String sql, List<Object[]> values, DataSource dataSource){
        Connection conn = null;
        PreparedStatement st = null;
        final int batchSize = 20000;
        try
        {
            if(dataSource == null) {
                conn = getConnection();
            }else {
                conn = dataSource.getConnection();
            }
            st = conn.prepareStatement(sql);

            if(values != null){
                int length = values.size();
                if(length == 0){
                    LoggerUtil.error(this.getClass(), "executeBatchByPrepareStatement	values is empty");
                    return;
                }
                int tempLength = values.get(0).length;
                for(int i = 0; i < length; i++){
                    Object[] o = values.get(i);
                    if(o == null){
                        continue;
                    }

                    for (int j = 0; j < tempLength; j++)
                    {
                        st.setObject(j+1, o[j]);
                    }
                    st.addBatch();
                    if((i+1) % batchSize == 0){
                        st.executeBatch();
                        st.clearBatch();
                    }
                }
                st.executeBatch();
            }

        } catch (SQLException e)
        {
            rollBackTransaction(conn);
            LoggerUtil.error(this.getClass(), "executeBatch error", e);
        }finally {
            closeConnection(conn);
        }
    }

    /**
     * 获取数据库连接
     *
     * @return
     */
    public Connection getConnection(){
        Connection con = null;
        try {
            con =dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接不存在！");
        }
        return con;
    }

    /**
     * 回滚事务
     * @param cnn
     */
    public void rollBackTransaction(Connection cnn){
        if(cnn!=null){
            try {
                if(!cnn.getAutoCommit()){
                    cnn.rollback();
                }
            } catch (SQLException e) {
                LoggerUtil.error(this.getClass(), "回滚异常", e);
            }
        }
    }

    /**
     * 关闭连接
     *
     * @param con
     */
    public void closeConnection(Connection con){
        if(con != null){
            try {
                con.close();
            } catch (SQLException e) {
                LoggerUtil.error(this.getClass(), "数据库关闭连接错误",e);
            }
        }
    }
}

方法中sql形式如下，当然，批量修改亦或删除也可使用此方法：

String sql = "insert into table_name (column1,column2,column3,column4,column5) values (?,?,?,?,?)";

附常用工具方法

public class BaseDao {

    @Autowired DataSource dataSource;

    @Resource DataSource dataSourceRemote;

    String url="jdbc:mysql://ip:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
    //String driverClass = "com.mysql.jdbc.Driver";
    String driverClass = "com.mysql.cj.jdbc.Driver";
    String userName="root";
    String pwd = "root";
    public Connection getConnection(){
        Connection con = null;
        try {
            con =dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接不存在！");
        }
        return con;
    }

    public DataSource getDataSource() {
        return dataSource;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public Connection getParamConnection(){
        Connection con = null;
        try {
            Class.forName(driverClass);
            con = DriverManager.getConnection(url, userName, pwd);
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接不存在！");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return con;
    }
    public Connection getRemoteConnection(){
        Connection con = null;
        try {
            con =dataSourceRemote.getConnection();
        } catch (SQLException e) {
            LoggerUtil.error(this.getClass(),"数据库连接不存在！",e);
        }
        return con;
    }


    public synchronized void executeProcedure(String procedure, Object[] values){
        Connection con = null;
        PreparedStatement st = null;
        try {
            con = getConnection();
            st = setParams(con, "{call "+ procedure +"}", values);
            st.execute();
        } catch (Exception e) {
            e.printStackTrace();
            LoggerUtil.error(this.getClass(), "调用存储过程失败 \n调用语句：+" + procedure+ "\n参数："+Arrays.toString(values), e);
        }finally{
            closeConnection(con);
        }
    }

    public int execute(String sql, Object[] values){
        int num = 0;
        Connection con = null;
        try {
            con = getConnection();
            num = execute(sql, values, con);
        } catch (Exception e) {
            LoggerUtil.error(this.getClass(), "数据库执行错误：",e);
        }finally{
            closeConnection(con);
        }
        return num;
    }

    public int execute(String sql,Object[] values, DataSource dataSource){
        int num = 0;
        Connection con = null;
        try {
            if(dataSource != null) {
                con = dataSource.getConnection();
            }
            num = execute(sql, values, con);
        } catch (Exception e) {
            LoggerUtil.error(this.getClass(), "数据库执行错误：",e);
        }finally{
            closeConnection(con);
        }
        return num;
    }

    public int execute(String sql,Object[] values, Connection conn) throws Exception {
        int num = 0;
        PreparedStatement ps = setParams(conn, sql, values);
        num =ps.executeUpdate();
        return num;
    }

    public void executeBatchByPrepareStatement(String sql, List<Object[]> values){
        executeBatchByPrepareStatement(sql, values,null);
    }

    public void executeBatchByPrepareStatement(String sql, List<Object[]> values, DataSource dataSource){
        Connection conn = null;
        PreparedStatement st = null;
        final int batchSize = 20000;
        try
        {
            if(dataSource == null) {
                conn = getConnection();
            }else {
                conn = dataSource.getConnection();
            }
            st = conn.prepareStatement(sql);

            if(values != null){
                int length = values.size();
                if(length == 0){
                    LoggerUtil.error(this.getClass(), "executeBatchByPrepareStatement	values is empty");
                    return;
                }
                int tempLength = values.get(0).length;
                for(int i = 0; i < length; i++){
                    Object[] o = values.get(i);
                    if(o == null){
                        continue;
                    }

                    for (int j = 0; j < tempLength; j++)
                    {
                        st.setObject(j+1, o[j]);
                    }
                    st.addBatch();
                    if((i+1) % batchSize == 0){
                        st.executeBatch();
                        st.clearBatch();
                    }
                }
                st.executeBatch();
            }

        } catch (SQLException e)
        {
            rollBackTransaction(conn);
            LoggerUtil.error(this.getClass(), "executeBatch error", e);
        }finally {
            closeConnection(conn);
        }
    }
    public void executeBatchByPrepareStatementJa(String sql, JsonArray values) {
        executeBatchByPrepareStatementJa( sql,  values,null);
    }
    public void executeBatchByPrepareStatementJa(String sql, JsonArray values, DataSource dataSource){
        Connection conn = null;
        PreparedStatement st = null;
        final int batchSize = 20000;
        try
        {
            if(dataSource == null) {
                conn = getConnection();
            }else {
                conn = dataSource.getConnection();
            }
            st = conn.prepareStatement(sql);

            if(values != null){
                int length = values.size();
                if(length == 0){
                    LoggerUtil.error(this.getClass(), "executeBatchByPrepareStatement	values is empty");
                    return;
                }
                int tempLength = values.get(0).getAsJsonObject().size();
                for(int i = 0; i < length; i++){
                    Object[] o = GsonUtil.getJsonValueArray(values.get(i).getAsJsonObject(), tempLength, Object.class) ;
                    if(o == null){
                        continue;
                    }

                    for (int j = 0; j < tempLength; j++)
                    {
                        st.setObject(j+1, o[j]);
                    }
                    st.addBatch();
                    if((i+1) % batchSize == 0){
                        st.executeBatch();
                        st.clearBatch();
                    }
                }
                st.executeBatch();
            }

        } catch (SQLException e)
        {
            rollBackTransaction(conn);
            LoggerUtil.error(this.getClass(), "executeBatch error", e);
        }finally {
            closeConnection(conn);
        }
    }

    public <V> V executeUniqueFiled(String sql ,Object[] values){

        return executeUniqueFiled(sql,values,null);
    }

    public <V> V executeUniqueFiled(String sql ,Object[] values, Connection conn){
        Connection con = null;
        Object o = null;
        try {
            if(conn != null) {
                con = conn;
            }else {
                con = getConnection();
            }

            PreparedStatement ps = setParams(con, sql, values);
            ResultSet rs = ps.executeQuery();

            if(rs.next()){
                o = rs.getObject(1);
            }
        } catch (SQLException e) {
            LoggerUtil.error(this.getClass(), "数据库查询错误："+con,e);
        }finally{
            closeConnection(con);
        }
        return extracted(o);
    }

    public <V> V executeUniqueFiled(String sql){
        return executeUniqueFiled(sql, null);
    }

    public JsonObject executeFirstResult(String sql){
        return executeFirstResult(sql, null);
    }

    public JsonObject executeFirstResult(String sql,Object[] values){
        return executeFirstResult(sql, values, null);
    }

    public JsonObject executeFirstResult(String sql,Object[] values, Connection conn){
        Connection con = null;
        JsonObject jo = new JsonObject();
        try {
            if(conn == null) {
                con = getConnection();
            }else {
                con = conn;
            }

            PreparedStatement ps = setParams(con, sql, values);
            ResultSet rs = ps.executeQuery();
            JsonArray ja = handleResult(rs);
            if(ja.size() >=1){
                jo = ja.get(0).getAsJsonObject();
            }
            return jo;
        } catch (SQLException e) {
            LoggerUtil.error(this.getClass(), "数据库查询错误："+con,e);
        }finally{
            closeConnection(con);
        }
        return jo;
    }

    public JsonArray executQuery(String sql){
        return executeQuery(sql, null);
    }

    public Vector<Object[]> executQueryToArrayList(String sql){
        return executeQueryToArrayList(sql, null);
    }

    public JsonArray executQuery(String sql, Connection conn){
        return executeQuery(sql, null,conn);
    }

    public JsonObject executQuery(String sql,int page, int pageSize){

        return executeQuery(sql, null, page, pageSize);
    }

    public JsonArray executeQuery(String sql, Object[] values){

        return executeQuery(sql, values,null);
    }

    public List<JsonObject> executeQueryReturnList(String sql, Object[] values){

        return executeQueryReturnList(sql, values,null);
    }

    public Vector<Object[]> executeQueryToArrayList(String sql, Object[] values){

        return executeQueryToArrayList(sql, values,null);
    }

    public JsonArray executeQuery(String sql, Object[] values, Connection conn){

        Connection con = null;
        JsonArray ja = null;
        try {
            if(conn == null) {
                con = getConnection();
            }else {
                con = conn;
            }

            PreparedStatement ps = setParams(con, sql, values);
            ResultSet rs = ps.executeQuery();
            ja = handleResult(rs);
        } catch (SQLException e) {
            LoggerUtil.error(this.getClass(), "数据库查询错误："+con,e);
        }finally{
            closeConnection(con);
        }
        return ja;
    }

    public List executeQueryReturnList(String sql, Object[] values, Connection conn){

        Connection con = null;
        List<JsonObject> ja = null;
        try {
            if(conn == null) {
                con = getConnection();
            }else {
                con = conn;
            }

            PreparedStatement ps = setParams(con, sql, values);
            ResultSet rs = ps.executeQuery();
            ja = handleResultReturnList(rs);
        } catch (SQLException e) {
            LoggerUtil.error(this.getClass(), "数据库查询错误："+con,e);
        }finally{
            closeConnection(con);
        }
        return ja;
    }

    public Vector<Object[]> executeQueryToArrayList(String sql, Object[] values, Connection conn){

        Connection con = null;
        Vector<Object[]> ja = null;
        try {
            if(conn == null) {
                con = getConnection();
            }else {
                con = conn;
            }

            PreparedStatement ps = setParams(con, sql, values);
            ResultSet rs = ps.executeQuery();
            ja = handleResultToArrayList(rs);
        } catch (SQLException e) {
            LoggerUtil.error(this.getClass(), "数据库查询错误："+con,e);
        }finally{
            closeConnection(con);
        }
        return ja;
    }

    public JsonObject executeQuery(String sql, Object[] values,int page, int pageSize){

        JsonObject jo = null;
        Connection con = null;
        try {
            con = getConnection();
            if(page >= 0){
                sql = "select SQL_CALC_FOUND_ROWS u.* from ("+sql+") u limit "+((page-1)*pageSize) + ","+ pageSize;
            }
            PreparedStatement ps = setParamsNoIndex(con, sql, values);
            ResultSet rs = ps.executeQuery();
            jo = handlePaging(rs, con,pageSize);

        } catch (SQLException e) {
            LoggerUtil.error(this.getClass(), "数据库查询错误："+con,e);
        }finally{
            closeConnection(con);
        }
        return jo;
    }

    public String[] getMenu(ResultSet rs) throws SQLException{
        ResultSetMetaData rsd = rs.getMetaData();
        int meta = rsd.getColumnCount();
        String[] menu = new String[meta];
        for (int i = 0; i < meta; i++) {
            menu[i] = rsd.getColumnName(i+1);
        }
        return menu;
    }

    public int getUniqueCount(String sql, Connection con) {
        return getUniqueCount(sql, null, con);
    }
    public int getUniqueCount(String sql,Object[] values, Connection con){

        Connection conne = null;
        if(con == null) {
            conne = getConnection();
        }else {
            conne = con;
        }

        try {
            PreparedStatement ps = conne.prepareStatement(sql);
            if(values != null){
                for (int i = 0; i < values.length; i++) {
                    ps.setObject(i+1, values[i]);
                }
            }
            ResultSet rs = ps.executeQuery();
            rs.next();
            int s = rs.getInt(1);
            if(s < 0){
                return 0;
            }
            return s;
        } catch (SQLException e) {
            LoggerUtil.error(this.getClass(), "数据库查询错误："+con,e);
        }finally{
            try {
                conne.close();
            } catch (SQLException e) {
                LoggerUtil.error(this.getClass(), "getUniqueCount error: sql="+sql, e);
            }
        }
        return 0;
    }

    public int getUniqueCount(String sql,Object[] values){
        return getUniqueCount(sql, values, null);
    }

    public <V> V executeQueryOneFiled(String sql, Class<?> c){
        return executeQueryOneFiled(sql, null, c);
    }

    public <V> V executeQueryOneFiled(String sql, Class<?> c,Connection conn){
        return executeQueryOneFiled(sql, null, c,conn);
    }

    public <V> V executeQueryOneFiled(String sql, Object[] values, Class<?> c){

        return executeQueryOneFiled(sql,values,c,null);
    }

    public <V> V executeQueryOneFiled(String sql, Object[] values, Class<?> c, Connection conn){

        Connection con = null;
        try {
            if(conn == null) {
                con = getConnection();
            }else {
                con = conn;
            }

            PreparedStatement ps = setParams(con, sql, values);
            ResultSet rs = ps.executeQuery();
            return handleResult(rs, c);
        } catch (SQLException e) {
            LoggerUtil.error(this.getClass(), "数据库查询错误："+con,e);
        }finally{
            closeConnection(con);
        }
        return null;
    }

    private  <V> V handleResult(ResultSet rs, Class<?> c){
        try
        {
            rs.last();
            int rows = rs.getRow();
            rs.beforeFirst();
            Object array = Array.newInstance(c, rows);

            for (int i = 0; i < rows; i++)
            {
                if(rs.next()){
                    Array.set(array, i, rs.getObject(1));
                }
            }
            return extracted(array);
        } catch (Exception e)
        {
            LoggerUtil.error(this.getClass(), "数据库查询错误",e);
        }
        return null;

    }

    @SuppressWarnings("unchecked")
    private <V> V extracted(Object array)
    {
        return (V) array;
    }


    private JsonArray handleResult(ResultSet rs) throws SQLException{
        JsonArray ja = new JsonArray();
        String[] menu = getMenu(rs);
        int len = menu.length;
        while(rs.next()){
            JsonObject jo = new JsonObject();
            for (int i = 0; i < len; i++) {
                jo.addProperty(menu[i], rs.getString(menu[i]));
            }
            ja.add(jo);
        }
        return ja;
    }

    private List<JsonObject> handleResultReturnList(ResultSet rs) throws SQLException{

        rs.last();
        int rows = rs.getRow();
        rs.beforeFirst();

        List<JsonObject> ja = new ArrayList<>(rows);

        String[] menu = getMenu(rs);
        int len = menu.length;

        while(rs.next()){
            JsonObject jo = new JsonObject();
            for (int i = 0; i < len; i++) {
                jo.addProperty(menu[i], rs.getString(menu[i]));
            }
            ja.add(jo);
        }
        return ja;
    }

    private Vector<Object[]> handleResultToArrayList(ResultSet rs) throws SQLException{
        String[] menu = getMenu(rs);
        int len = menu.length;
        Vector<Object[]> v= new Vector<Object[]>();

        while(rs.next()){
            Object[] o = new Object[len];
            for (int i = 0; i < len; i++) {
                o[i]= rs.getString(menu[i]);
            }
            v.add(o);
        }
        return v;
    }

    private JsonObject handlePaging(ResultSet rs,Connection con,int pageSize) throws SQLException{
        JsonObject jo = new JsonObject();
        JsonArray ja = handleResult(rs);
        jo.add("rows", ja);

        String count = "select FOUND_ROWS()";
        PreparedStatement ps1 = con.prepareStatement(count);
        ResultSet rs1 = ps1.executeQuery();
        int num = 0;
        if(rs1.next()){
            num = rs1.getInt(1);
        }

        jo.addProperty("total", num%pageSize==0?num/pageSize:num/pageSize+1);
        jo.addProperty("records", num);
        return jo;
    }

    private PreparedStatement setParams(Connection con,String sql, Object[] values ) throws SQLException{
        PreparedStatement ps = con.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
        if(values != null){
            int length = values.length;
            for (int i = 0; i < length; i++) {
                ps.setObject(i+1, values[i]);
            }
        }
        return ps;
    }

    private PreparedStatement setParamsNoIndex(Connection con,String sql, Object[] values ) throws SQLException{
        PreparedStatement ps = con.prepareStatement(sql);
        if(values != null){
            int length = values.length;
            for (int i = 0; i < length; i++) {
                ps.setObject(i+1, values[i]);
            }
        }
        return ps;
    }

    public void closeConnection(Connection con){
        if(con != null){
            try {
                con.close();
            } catch (SQLException e) {
                LoggerUtil.error(this.getClass(), "数据库关闭连接错误",e);
            }
        }
    }


    /**
     * 开始事务 
     * @param cnn
     */
    public void beginTransaction(Connection cnn){
        if(cnn!=null){
            try {
                if(cnn.getAutoCommit()){
                    cnn.setAutoCommit(false);
                }
            } catch (SQLException e) {
                LoggerUtil.error(this.getClass(), "开启事务Error", e);
            }
        }
    }

    /**
     * 提交事务 
     * @param cnn
     */
    public void commitTransaction(Connection cnn){
        if(cnn!=null){
            try {
                if(!cnn.getAutoCommit()){
                    cnn.commit();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block  
                LoggerUtil.error(this.getClass(), "数据库事务提交错误",e);
            }
        }
    }

    /**
     * 回滚事务 
     * @param cnn
     */
    public void rollBackTransaction(Connection cnn){
        if(cnn!=null){
            try {
                if(!cnn.getAutoCommit()){
                    cnn.rollback();
                }
            } catch (SQLException e) {
                LoggerUtil.error(this.getClass(), "回滚异常", e);
            }
        }
    }


}

 

第二种：Mybatis

1、同样要开启批处理，在数据库配置上添加rewriteBatchedStatements=true

2、准备sql

3、service方法，开启批处理，关闭自动提交，最后统一提交

 可参考

Mybatis批量插入数据的两种方式_mybatis 批量插数据_Neo Yang的博客-CSDN博客