文档章节

SQL数据批量提交的优化和心得

后海
 后海
发布于 2016/02/19 10:56
字数 1931
阅读 579
收藏 7
    //通用的批处理sql(预编译)
    public int[] addBatchs(String sql,List<Object[]> ds) { 
    	int[] ls = null;
    	if(ds == null || ds.size() <= 0){ return null; }
    	Connection conn = null;
    	PreparedStatement st = null; //PreparedStatementLog 可输出动态解析的预执行sql
        try { 
    		conn = jdbc.getDataSource().getConnection();
    		//默认每执行一条sql提交一次,消耗太大,所以关闭自动提交。
        	conn.setAutoCommit(false); 
        	//采用预编译SQL,会大幅提升性能。
        	st = conn.prepareStatement(sql);  //new PreparedStatementLog(conn,sql);
        	for(int i=0;i<ds.size();i++){
    		    //动态赋值
                for(int j=0;j<ds.get(i).length;j++) { 
                        //对于NULL数据进行特殊处理
                        if(ds.get(i)[j] == null || "NULL".equals(ds.get(i)[j])){
                    	    st.setObject(j+1, null);
                         }else{
                    	    st.setObject(j+1, ds.get(i)[j]);
                         }
                 }  
                 //System.out.println(i+"-SQL: "+st.getQueryString()); //输出动态解析的预执行sql
                 st.addBatch();
                 //批次路由控制,每次批量提交一个性能和效率的最优值。Oracle是500,Mysql是200
			     if(0 == i%500 || i == ds.size()-1){
			        st.executeBatch();
			        conn.commit();
                    st.clearBatch();
			     }
			}
        } catch (Exception e) { 
            try {
		conn.rollback();
	    } catch (Exception e1) {}
            System.out.println("DB {addBatchs}{Error} "+e.getMessage());
        }finally{
        	try {
        	    st.close();
		    conn.close();
		} catch (Exception e) {
			System.out.println("DB {close}{Error} "+e.getMessage());
		}
        }  
        return ls; 
    }
      
    使用方式:
    List<Object[]> os = new ArrayList<Object[]>();
    LinkedHashMap<String,Object> ms = null;
    for(int i=0;i<ds.size();i++){
	ms = ds.get(i);
	//基本信息:ID、证件类型、证件号码、姓名、实际出生日期、创建时间
	os.add(new Object[]{ms.get("0"),ms.get("1"),ms.get("2"),ms.get("3")});
    }
    addBatchs("INSERT INTO User(id,zjlx,zjhm,xm) VALUES(?,?,?,?) ",os);
    
    
    测评效果:
    Oracle可以达到每秒1w条数据入库;
    
    
    
    
    //批处理sql(常规)
    public int[] addBatch(List<String> sql) { 
    	int[] ls = null;
    	if(sql == null || sql.size() <= 0){ return null; }
    	Connection conn = null;
    	Statement st = null;
        try { 
    		conn = jdbc.getDataSource().getConnection();
        	//关闭自动提交,默认情况下每执行一条sql提交一次  
        	conn.setAutoCommit(false); 
        	st = conn.createStatement(); 
        	
        	for(int i=0;i<sql.size();i++){
    			st.addBatch(sql.get(i));
				if(0 == i%500 || i == ds.size()-1){
					System.out.println(i+" [批次] ");
					st.executeBatch();
					conn.commit();
				}
        	}
            //ls = st.executeBatch();
            //conn.commit();
        } catch (Exception e) { 
        	try {
				conn.rollback();
			} catch (Exception e1) {}
        	System.out.println("DB {addBatchs}{Error} "+e.getMessage());
        }finally{
        	try {
        		st.close();
				conn.close();
			} catch (Exception e) {
				System.out.println("DB {close}{Error} "+e.getMessage());
			}
        }  
        return ls; 
    }
package com.matols.tools;

import java.io.InputStream;
import java.io.Reader;
import java.math.BigDecimal;
import java.net.URL;
import java.sql.Array;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.NClob;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.Ref;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.RowId;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;

/**
 * 扩展PreparedStatement,以便输出执行的sql语句
 */
public class PreparedStatementLog implements PreparedStatement {
	/** used for storing parameter values needed for producing log */
	private ArrayList parameterValues;
	/** the query string with question marks as parameter placeholders */
	private String sqlTemplate;
	/** a statement created from a real database connection */
	private PreparedStatement wrappedStatement;
	public PreparedStatementLog(Connection connection, String sql) throws SQLException {
		// use connection to make a prepared statement
		wrappedStatement = connection.prepareStatement(sql);
		sqlTemplate = sql;
		parameterValues = new ArrayList();
	}
	private void saveQueryParamValue(int position, Object obj) {
		String strValue;
		if (obj instanceof String || obj instanceof Date) {
			// if we have a String, include '' in the saved value
			strValue = "'" + obj + "'";
		} else {
			if (obj == null) {
				// convert null to the string null
				strValue = "null";
			} else {
				// unknown object (includes all Numbers), just call toString
				strValue = obj.toString();
			}
		}
		// if we are setting a position larger than current size of
		// parameterValues, first make it larger
		while (position >= parameterValues.size()) {
			parameterValues.add(null);
		}
		// save the parameter
		parameterValues.set(position, strValue);
	}
	// 这一步是对ArrayList与sql进行处理,输出完整的sql语句
	public String getQueryString() {
		int len = sqlTemplate.length();
		StringBuffer t = new StringBuffer(len * 2);
		if (parameterValues != null) {
			int i = 1, limit = 0, base = 0;
			while ((limit = sqlTemplate.indexOf('?', limit)) != -1) {
				t.append(sqlTemplate.substring(base, limit));
				t.append(parameterValues.get(i));
				i++;
				limit++;
				base = limit;
			}
			if (base < len) {
				t.append(sqlTemplate.substring(base));
			}
		}
		return t.toString();
	}
	public void addBatch() throws SQLException {
		wrappedStatement.addBatch();
	}

	public void clearParameters() throws SQLException {
		wrappedStatement.clearParameters();
	}
	public boolean execute() throws SQLException {
		return wrappedStatement.execute();
	}
	public ResultSet executeQuery() throws SQLException {
		return wrappedStatement.executeQuery();
	}
	public int executeUpdate() throws SQLException {
		return wrappedStatement.executeUpdate();
	}
	public ResultSetMetaData getMetaData() throws SQLException {
		return wrappedStatement.getMetaData();
	}
	public ParameterMetaData getParameterMetaData() throws SQLException {
		return wrappedStatement.getParameterMetaData();
	}
	public void setArray(int i, Array x) throws SQLException {
		wrappedStatement.setArray(i, x);
		saveQueryParamValue(i, x);
	}
	public void setAsciiStream(int parameterIndex, InputStream x, int length) throws SQLException {
		wrappedStatement.setAsciiStream(parameterIndex, x, length);
		saveQueryParamValue(parameterIndex, x);
	}

	public void setBigDecimal(int parameterIndex, BigDecimal x) throws SQLException {
		wrappedStatement.setBigDecimal(parameterIndex, x);
		saveQueryParamValue(parameterIndex, x);
	}

	public void setBinaryStream(int parameterIndex, InputStream x, int length) throws SQLException {
		wrappedStatement.setBinaryStream(parameterIndex, x, length);
		saveQueryParamValue(parameterIndex, x);
	}

	public void setBlob(int i, Blob x) throws SQLException {
		wrappedStatement.setBlob(i, x);
		saveQueryParamValue(i, x);
	}

	public void setBoolean(int parameterIndex, boolean x) throws SQLException {
		wrappedStatement.setBoolean(parameterIndex, x);
		saveQueryParamValue(parameterIndex, new Boolean(x));
	}

	public void setByte(int parameterIndex, byte x) throws SQLException {
		wrappedStatement.setByte(parameterIndex, x);
		saveQueryParamValue(parameterIndex, new Byte(x));
	}

	public void setBytes(int parameterIndex, byte[] x) throws SQLException {
		wrappedStatement.setBytes(parameterIndex, x);
		saveQueryParamValue(parameterIndex, x);
	}

	public void setCharacterStream(int parameterIndex, Reader reader, int length) throws SQLException {
		wrappedStatement.setCharacterStream(parameterIndex, reader, length);
		saveQueryParamValue(parameterIndex, reader);
	}

	public void setClob(int i, Clob x) throws SQLException {
		wrappedStatement.setClob(i, x);
		saveQueryParamValue(i, x);
	}

	public void setDate(int parameterIndex, Date x) throws SQLException {
		wrappedStatement.setDate(parameterIndex, x);
		saveQueryParamValue(parameterIndex, x);
	}

	public void setDate(int parameterIndex, Date x, Calendar cal) throws SQLException {
		wrappedStatement.setDate(parameterIndex, x, cal);
		saveQueryParamValue(parameterIndex, x);
	}

	public void setDouble(int parameterIndex, double x) throws SQLException {
		wrappedStatement.setDouble(parameterIndex, x);
		saveQueryParamValue(parameterIndex, new Double(x));
	}

	public void setFloat(int parameterIndex, float x) throws SQLException {
		wrappedStatement.setFloat(parameterIndex, x);
		saveQueryParamValue(parameterIndex, new Float(x));
	}

	public void setInt(int parameterIndex, int x) throws SQLException {
		wrappedStatement.setInt(parameterIndex, x);
		saveQueryParamValue(parameterIndex, new Integer(x));
	}

	public void setLong(int parameterIndex, long x) throws SQLException {
		wrappedStatement.setLong(parameterIndex, x);
		saveQueryParamValue(parameterIndex, new Long(x));
	}

	public void setNull(int parameterIndex, int sqlType) throws SQLException {
		wrappedStatement.setNull(parameterIndex, sqlType);
		saveQueryParamValue(parameterIndex, new Integer(sqlType));
	}

	public void setNull(int paramIndex, int sqlType, String typeName) throws SQLException {
		wrappedStatement.setNull(paramIndex, sqlType, typeName);
		saveQueryParamValue(paramIndex, new Integer(sqlType));
	}

	public void setObject(int parameterIndex, Object x) throws SQLException {
		wrappedStatement.setObject(parameterIndex, x);
		saveQueryParamValue(parameterIndex, x);
	}

	public void setObject(int parameterIndex, Object x, int targetSqlType) throws SQLException {
		wrappedStatement.setObject(parameterIndex, x, targetSqlType);
		saveQueryParamValue(parameterIndex, x);
	}

	public void setObject(int parameterIndex, Object x, int targetSqlType, int scale) throws SQLException {
		wrappedStatement.setObject(parameterIndex, x, targetSqlType, scale);
		saveQueryParamValue(parameterIndex, x);
	}

	public void setRef(int i, Ref x) throws SQLException {
		wrappedStatement.setRef(i, x);
		saveQueryParamValue(i, x);
	}

	public void setShort(int parameterIndex, short x) throws SQLException {
		wrappedStatement.setShort(parameterIndex, x);
		saveQueryParamValue(parameterIndex, new Short(x));
	}

	public void setString(int parameterIndex, String x) throws SQLException {
		wrappedStatement.setString(parameterIndex, x);
		saveQueryParamValue(parameterIndex, x);
	}

	public void setTime(int parameterIndex, Time x) throws SQLException {
		wrappedStatement.setTime(parameterIndex, x);
		saveQueryParamValue(parameterIndex, x);
	}

	public void setTime(int parameterIndex, Time x, Calendar cal) throws SQLException {
		wrappedStatement.setTime(parameterIndex, x, cal);
		saveQueryParamValue(parameterIndex, x);
	}
	public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException {
		wrappedStatement.setTimestamp(parameterIndex, x);
		saveQueryParamValue(parameterIndex, x);
	}
	public void setTimestamp(int parameterIndex, Timestamp x, Calendar cal) throws SQLException {
		wrappedStatement.setTimestamp(parameterIndex, x, cal);
		saveQueryParamValue(parameterIndex, x);
	}
	public void setURL(int parameterIndex, URL x) throws SQLException {
		wrappedStatement.setURL(parameterIndex, x);
		saveQueryParamValue(parameterIndex, x);
	}
	public void setUnicodeStream(int parameterIndex, InputStream x, int length) throws SQLException {
		wrappedStatement.setUnicodeStream(parameterIndex, x, length);
		saveQueryParamValue(parameterIndex, x);
	}

	public void addBatch(String sql) throws SQLException {
		wrappedStatement.addBatch(sql);
	}
	public void cancel() throws SQLException {
		wrappedStatement.cancel();
	}
	public void clearBatch() throws SQLException {
		wrappedStatement.clearBatch();
	}
	public void clearWarnings() throws SQLException {
		wrappedStatement.clearWarnings();
	}
	public void close() throws SQLException {
		wrappedStatement.close();
	}
	public boolean execute(String sql) throws SQLException {
		return wrappedStatement.execute(sql);
	}

	public boolean execute(String sql, int autoGeneratedKeys) throws SQLException {
		return wrappedStatement.execute(sql, autoGeneratedKeys);
	}
	public boolean execute(String sql, int[] columnIndexes) throws SQLException {
		return wrappedStatement.execute(sql, columnIndexes);
	}
	public boolean execute(String sql, String[] columnNames) throws SQLException {
		return wrappedStatement.execute(sql, columnNames);
	}

	public int[] executeBatch() throws SQLException {
		return wrappedStatement.executeBatch();
	}
	public ResultSet executeQuery(String sql) throws SQLException {
		return wrappedStatement.executeQuery(sql);
	}
	public int executeUpdate(String sql) throws SQLException {
		return wrappedStatement.executeUpdate(sql);
	}

	public int executeUpdate(String sql, int autoGeneratedKeys) throws SQLException {
		return wrappedStatement.executeUpdate(sql, autoGeneratedKeys);
	}
	public int executeUpdate(String sql, int[] columnIndexes) throws SQLException {
		return wrappedStatement.executeUpdate(sql, columnIndexes);
	}
	public int executeUpdate(String sql, String[] columnNames) throws SQLException {
		return wrappedStatement.executeUpdate(sql, columnNames);
	}
	public Connection getConnection() throws SQLException {
		return wrappedStatement.getConnection();
	}
	public int getFetchDirection() throws SQLException {
		return wrappedStatement.getFetchDirection();
	}
	public int getFetchSize() throws SQLException {
		return wrappedStatement.getFetchSize();
	}
	public ResultSet getGeneratedKeys() throws SQLException {
		return wrappedStatement.getGeneratedKeys();
	}
	public int getMaxFieldSize() throws SQLException {
		return wrappedStatement.getMaxFieldSize();
	}
	public int getMaxRows() throws SQLException {
		return wrappedStatement.getMaxRows();
	}
	public boolean getMoreResults() throws SQLException {
		return wrappedStatement.getMoreResults();
	}

	public boolean getMoreResults(int current) throws SQLException {
		return wrappedStatement.getMoreResults(current);
	}
	public int getQueryTimeout() throws SQLException {
		return wrappedStatement.getQueryTimeout();
	}
	public ResultSet getResultSet() throws SQLException {
		return wrappedStatement.getResultSet();
	}
	public int getResultSetConcurrency() throws SQLException {
		return wrappedStatement.getResultSetConcurrency();
	}
	public int getResultSetHoldability() throws SQLException {
		return wrappedStatement.getResultSetHoldability();
	}

	public int getResultSetType() throws SQLException {
		return wrappedStatement.getResultSetType();
	}
	public int getUpdateCount() throws SQLException {
		return wrappedStatement.getUpdateCount();
	}
	public SQLWarning getWarnings() throws SQLException {
		return wrappedStatement.getWarnings();
	}
	public void setCursorName(String name) throws SQLException {
		wrappedStatement.setCursorName(name);
	}

	public void setEscapeProcessing(boolean enable) throws SQLException {
		wrappedStatement.setEscapeProcessing(enable);
	}
	public void setFetchDirection(int direction) throws SQLException {
		wrappedStatement.setFetchDirection(direction);
	}
	public void setFetchSize(int rows) throws SQLException {
		wrappedStatement.setFetchSize(rows);
	}
	public void setMaxFieldSize(int max) throws SQLException {
		wrappedStatement.setMaxFieldSize(max);
	}

	public void setMaxRows(int max) throws SQLException {
		wrappedStatement.setMaxFieldSize(max);
	}
	public void setQueryTimeout(int seconds) throws SQLException {
		wrappedStatement.setQueryTimeout(seconds);
	}
	public void setAsciiStream(int parameterIndex, InputStream x) throws SQLException {
		// TODO Auto-generated method stub

	}
	public void setAsciiStream(int parameterIndex, InputStream x, long length) throws SQLException {
		// TODO Auto-generated method stub

	}

	public void setBinaryStream(int parameterIndex, InputStream x) throws SQLException {
		// TODO Auto-generated method stub

	}
	public void setBinaryStream(int parameterIndex, InputStream x, long length) throws SQLException {
		// TODO Auto-generated method stub

	}
	public void setBlob(int parameterIndex, InputStream inputStream) throws SQLException {
		// TODO Auto-generated method stub

	}
	public void setBlob(int parameterIndex, InputStream inputStream, long length) throws SQLException {
		// TODO Auto-generated method stub

	}
	public void setCharacterStream(int parameterIndex, Reader reader) throws SQLException {
		// TODO Auto-generated method stub

	}
	public void setCharacterStream(int parameterIndex, Reader reader, long length) throws SQLException {
		// TODO Auto-generated method stub

	}

	public void setClob(int parameterIndex, Reader reader) throws SQLException {
		// TODO Auto-generated method stub

	}
	public void setClob(int parameterIndex, Reader reader, long length) throws SQLException {
		// TODO Auto-generated method stub

	}
	public void setNCharacterStream(int parameterIndex, Reader value) throws SQLException {
		// TODO Auto-generated method stub

	}
	public void setNCharacterStream(int parameterIndex, Reader value, long length) throws SQLException {
		// TODO Auto-generated method stub

	}
	public void setNClob(int parameterIndex, NClob value) throws SQLException {
		// TODO Auto-generated method stub

	}
	public void setNClob(int parameterIndex, Reader reader) throws SQLException {
		// TODO Auto-generated method stub

	}

	public void setNClob(int parameterIndex, Reader reader, long length) throws SQLException {
		// TODO Auto-generated method stub

	}

	public void setNString(int parameterIndex, String value) throws SQLException {
		// TODO Auto-generated method stub

	}
	public void setRowId(int parameterIndex, RowId x) throws SQLException {
		// TODO Auto-generated method stub

	}
	public void setSQLXML(int parameterIndex, SQLXML xmlObject) throws SQLException {
		// TODO Auto-generated method stub

	}

	public boolean isClosed() throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	public boolean isPoolable() throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	public void setPoolable(boolean poolable) throws SQLException {
		// TODO Auto-generated method stub

	}
	public boolean isWrapperFor(Class<?> iface) throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}
	public <T> T unwrap(Class<T> iface) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}
	@Override
	public void closeOnCompletion() throws SQLException {
		// TODO Auto-generated method stub

	}
	@Override
	public boolean isCloseOnCompletion() throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}
}

 

© 著作权归作者所有

后海
粉丝 30
博文 44
码字总数 25623
作品 2
闵行
后端工程师
私信 提问
加载中

评论(1)

jasonkavay
jasonkavay
非常不错!博主,我们做了一个帮助博主推广博客的app叫同行说,只需复制文章链接即可发布给更多程序员们看到哦,欢迎体验哈,一起发扬分享精神~
天啦噜,体系结构原来还能让SQL这样飞!

作者介绍 本章会较为简要地给大家介绍一下体系结构知识,然后描述体系结构和SQL优化的关系。最后通过系列扩展的相关优化案例来拓宽我们的视野,从而使我们更深入地了解体系结构的原理。 Orac...

梁敬彬
2017/08/30
0
0
使用BULK COLLECT+FORALL加速批量提交

一、 批量提交 批量提交特点: 占用较少undo,资源(独占锁,undo)快速释放,执行时间长 批量提交适合场景: 在线大批量插入,更新,删除数据 二、 BULK COLLECT+FORALL性能提升 1.通过BULK COLLECT加...

楚云泽
2016/03/21
264
0
优化solr全量建索引,减少索引时间

优化solr全量建索引 主要优化从数据库取数据这一块。 先简单为读取某个单表数据:该表数据很多娄,数据库为MySQL。 旧的建索引设计: 之前的设计是分段读取数据,可以按自增主键分段或者按记...

小样
2013/02/21
0
0
Oracle + Mybatis实现批量插入、更新和删除示例代码

前言 Mybatis是web工程开发中非常常用的数据持久化的框架,通过该框架,我们非常容易的进行数据库的增删改查。数据库连接进行事务提交的时候,需要耗费的资源比较多,如果需要插入更新的数据...

一看就喷亏的小猿
2018/11/22
0
0
PHP+MySQL百万数据插入

第一种方法:使用insert into 插入,代码如下: 最后显示为:23:25:05 01:32:05 也就是花了2个小时多! 这是时间撸几把 都行。。。 第二种方法:使用事务提交,批量插入数据库(每隔10W条提交下...

笨尛孩你谁啊
2018/05/24
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Tedis:基于 TiKV 构建的 NoSQL 数据库

作者介绍: 陈东明,饿了么北京技术中心架构组负责人,负责饿了么的产品线架构设计以及饿了么基础架构研发工作。曾任百度架构师,负责百度即时通讯产品的架构设计。具有丰富的大规模系统构 ...

TiDB
20分钟前
0
0
linux命令

ls命令是linux下最常用的命令。ls命令就是list的缩写,缺省下ls用来打印出当前目录的清单。如果ls指定其他目录,那么就会显示指定目录里的文件及文件夹清单。 通过ls 命令不仅可以查看linux文件...

WinkJie
28分钟前
0
0
你需要的物流运输类报表,这里都有

你需要的物流运输类报表,都在这里 葡萄城报表模板库是一款免费的报表制作、学习和参考工具,包含了超过 200 张高质量报表模板,涵盖了 16 大行业和 50 多种报表类型,为 30 余万报表开发者提...

葡萄城技术团队
35分钟前
1
0
像Java SE一样编写Java EE(ddd探索)

今天主要改写昨天的组合模式成Web系统。 容器接口为 public interface TreeProduct { /** * 展示所有产品 * @return */ List<TreeProduct> allProducts();...

算法之名
36分钟前
0
0
Django Model 模型建立

Django Model 模型 Django Model层是Django的数据模型层,每一个Model类就是数据库中的一张表; 我们需要注意下面几点: model一般都是定义在不同的APP的models.py模块文件中,可以是一个,也...

彩色泡泡糖
44分钟前
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部