使用dbutils进行批处理

使用dbutils可以进行批处理操作,使用的方法是batch,但是只能执行相同的SQL语句,参数可以不同。

有两个batch方法,源码如下:

 /**
     * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
     * 
     * @param conn The Connection to use to run the query.  The caller is
     * responsible for closing this Connection.
     * @param sql The SQL to execute.
     * @param params An array of query replacement parameters.  Each row in
     * this array is one set of batch replacement values. 
     * @return The number of rows updated per statement.
     * @throws SQLException if a database access error occurs
     * @since DbUtils 1.1
     */
    public int[] batch(Connection conn, String sql, Object[][] params)
        throws SQLException {

        PreparedStatement stmt = null;
        int[] rows = null;
        try {
            stmt = this.prepareStatement(conn, sql);

            for (int i = 0; i < params.length; i++) {
                this.fillStatement(stmt, params[i]);
                stmt.addBatch();
            }
            rows = stmt.executeBatch();

        } catch (SQLException e) {
            this.rethrow(e, sql, params);
        } finally {
            close(stmt);
        }

        return rows;
    }

    /**
     * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.  The 
     * <code>Connection</code> is retrieved from the <code>DataSource</code> 
     * set in the constructor.  This <code>Connection</code> must be in 
     * auto-commit mode or the update will not be saved. 
     * 
     * @param sql The SQL to execute.
     * @param params An array of query replacement parameters.  Each row in
     * this array is one set of batch replacement values. 
     * @return The number of rows updated per statement.
     * @throws SQLException if a database access error occurs
     * @since DbUtils 1.1
     */
    public int[] batch(String sql, Object[][] params) throws SQLException {
        Connection conn = this.prepareConnection();

        try {
            return this.batch(conn, sql, params);
        } finally {
            close(conn);
        }
    }
 public void fillStatement(PreparedStatement stmt, Object[] params)
        throws SQLException {

        if (params == null) {
            return;
        }
        
        ParameterMetaData pmd = stmt.getParameterMetaData();
        if (pmd.getParameterCount() < params.length) {
            throw new SQLException("Too many parameters: expected "
                    + pmd.getParameterCount() + ", was given " + params.length);
        }
        for (int i = 0; i < params.length; i++) {
            if (params[i] != null) {
                stmt.setObject(i + 1, params[i]);
            } else {
                // VARCHAR works with many drivers regardless
                // of the actual column type.  Oddly, NULL and 
                // OTHER don't work with Oracle's drivers.
                int sqlType = Types.VARCHAR;
                if (!pmdKnownBroken) {
                    try {
                        sqlType = pmd.getParameterType(i + 1);
                    } catch (SQLException e) {
                        pmdKnownBroken = true;
                    }
                }
                stmt.setNull(i + 1, sqlType);
            }
        }
    }

示例:向account表中增加10条记录

account表:
create table account(
      id int primary key auto_increment,
      name varchar(20),
      money float
);
   //code

@Test
	public void batchTest() throws SQLException{
		QueryRunner qr = new QueryRunner(JdbcC3p0Utils.getDataSource());
		String sql = "insert into account(name, money) values(?,?)";
		Object[][] param= new Object[10][];
		for(int i=0; i<10; i++){
			param[i]= new Object[2];
			param[i][0] = "name"+i*10+0;
			param[i][1] = i*10+1;
		}
		qr.batch(sql, param);
		
	}

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值