随笔 - 37  文章 - 14  trackbacks - 0
<2009年6月>
31123456
78910111213
14151617181920
21222324252627
2829301234
567891011

常用链接

留言簿

随笔分类

随笔档案

文章分类

相关链接

搜索

  •  

最新评论

阅读排行榜

评论排行榜

JDBC2.0 sql批量提交效率测试

测试环境:

   springframework 1.0

       数据库:Informix 9.21.FC1

       表结构:

       Column name          Type                                    Nulls

f1                   varchar(10,0)                           yes

f2                   varchar(10,0)                           yes

f3                   varchar(10,0)                           yes

f4                   varchar(10,0)                           yes

f5                   varchar(10,0)                           yes

f6                   varchar(10,0)                           yes

f7                   varchar(10,0)                           yes

f8                   varchar(10,0)                           yes

f9                   varchar(10,0)                           yes

f10                  varchar(10,0)                           yes

f1上建立索引

测试情况:

case 1SQL单个提交,不prepare,使用BaseDAOJdbcexecuteSql方法

case 2SQL单个提交,prepare,方法如下:

public void saveDataSinglePrepare(final List data) {

        getJdbcTemplate().execute(insertData,

            new PreparedStatementCallback() {

                          public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {

                              Iterator iter = data.iterator();

                              while (iter.hasNext()){

                                  Object[] objs = (Object[]) iter.next();

                                  for(int i=0; i

                                            ps.setObject(i+1, objs[i]);

                                           }

                                           ps.execute();

                              }

                              return null;

                          }

                }

    );

    }

case 3SQL批量提交,使用BaseDAOJdbcexecuteBatch方法

/**

 * Jdbc sql的批量操作

 * @param sql       sql语句

 * @param values 包含参数的ListList的每个元素是一个Object[]Object[]是一个单独的sql操作所需的参数

 */

public void executeBatch(final String sql, final List values){

    if (values.size() == 0) return ;

    getJdbcTemplate().batchUpdate(sql,

        new BatchPreparedStatementSetter() {

               public void setValues(PreparedStatement ps, int i) throws SQLException {

                   Object[] args = (Object[]) values.get(i);

                   for(int j=0; j

                       ///log.debug(String.valueOf(j+1)+":"+args[j].toString());

                              ps.setObject(j+1, args[j]);

                             }

            }

            public int getBatchSize() {

                return values.size();

            }

               }

    );  

}

case 4SQL批量提交,使用BaseDAOJdbcexecuteBatch2方法

private static final int batch_size = 100;

public void executeBatch2(final String sql, final List values){

    getJdbcTemplate().execute(sql,

        new PreparedStatementCallback() {

                      public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {

                          Iterator iter = values.iterator();

                          int sqlCount = 0;

                          while (iter.hasNext()){

                              if (sqlCount >= batch_size){

                                  ps.executeBatch();

                                  sqlCount = 0;

                                  //log.info("executeBatch:" + sql);

                              }

                              Object[] args = (Object[]) iter.next();

                              for(int i=0; i

                                     ps.setObject(i+1, args[i]);

                                    }

                              ps.addBatch();

                           sqlCount ++;

                           log.debug("sqlCount:" + String.valueOf(sqlCount));

                          }

                         

                          if (sqlCount > 0){

                           ps.executeBatch();

                           sqlCount = 0;

                           //log.info("executeBatch:" + sql);

                       }

                             return null;

                      }

        }

    );  

}    

posted on 2009-06-19 13:44 扭曲的铅笔 阅读(934) 评论(0)  编辑  收藏 所属分类: J2EE

只有注册用户登录后才能发表评论。


网站导航: