Spring JdbcTemplate 批量执行SQL语句 ![]() ![]() ![]() ![]() ![]() ![]() ![]() | |
Anonymous | view plaincopy to clipboardprint? package nut.jade.db; imp imp imp imp imp imp imp imp imp imp imp imp imp imp imp imp imp imp /** * 数据库操作类 * * @author nut.jade * */ @SuppressWarnings("unused") public class DBUtil { /** * jdbc 操作类 */ private JdbcTemplate jdbcTemplate; /** * 事务管理模版 */ private TransactionTemplate transactionTemplate; /** * 数据源 */ private DataSource ds; public DBUtil() { ds = getDataSource(); jdbcTemplate = new JdbcTemplate(ds); // // 事务处理 PlatformTransactionManager platformTransactionManager = new DataSourceTransactionManager( ds); transactionTemplate = new TransactionTemplate( platformTransactionManager); transactionTemplate.afterPropertiesSet(); transactionTemplate .setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED); } /** * 不带事务处理,批量更新 * * @param sql * @param obj */ public boolean batchUpdate(String sql, final List<Object[]> objs) { try { jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { Object[] obj = objs.get(i); if (obj != null) { for (int j = 0; j < obj.length; j++) { ps.setObject(j + 1, obj[j]); } } } public int getBatchSize() { return objs.size(); } }); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); return false; } return true; } /** * 事务处理,批量更新List<String> String是SQL语句 * * @return */ public boolean batchUpdate(final List<String> sqlList) { try { transactionTemplate.execute(new TransactionCallback() { public Object doInTransaction(TransactionStatus status) { for (String sql : sqlList) { jdbcTemplate.update(sql); } return null; } }); } catch (Exception e) { e.printStackTrace(); return false; } return true; } /** * 事务处理,批量更新 * Map<String,Object[]> String是SQL语句带占位符,Object[]是占位符值 * * @param map * Key值 是不允许重复为SQL * @return */ public boolean batchUpdate(final Map<String, Object[]> sqlMap) { try { transactionTemplate.execute(new TransactionCallback() { public Object doInTransaction(TransactionStatus status) { Set<String> keys = sqlMap.keySet(); for (String sql : keys) { jdbcTemplate.update(sql, sqlMap.get(sql)); } return null; } }); } catch (Exception e) { e.printStackTrace(); return false; } return true; } /** * 事务处理,批量更新 * Object[][] 允許重復 二维数组,第一列为SQL语句,第二列为Object[]占位符值 * * @param map * @return */ public boolean batchUpdate(final Object[][] sqlArr) { try { transactionTemplate.execute(new TransactionCallback() { public Object doInTransaction(TransactionStatus status) { for (int i = 0; i < sqlArr.length; i++) { String sql = sqlArr[i][0].toString(); Object[] obj = (Object[]) sqlArr[i][1]; jdbcTemplate.update(sql, obj); } return null; } }); } catch (Exception e) { e.printStackTrace(); return false; } return true; } /** * 批量更新 * * @param sqls * @return */ public int[] batchUpdate(String[] sqls) { int[] ints = jdbcTemplate.batchUpdate(sqls); return ints; } /** * 获取数据源 * * @return */ private DataSource getDataSource() { String dsType = ConfigBean.getProperty(ConfigBean.JDBC_POOL_TYPE); if (dsType.equals(ConfigBean.DBCPPool)) { return DBCPPool.getDataSource(); } String jndi = ConfigBean.getProperty(ConfigBean.JDBC_JNDI); InitialContext ctx; DataSource ds = null; try { ctx = new InitialContext(); ds = (DataSource) ctx.lookup(jndi); } catch (NamingException e) { e.printStackTrace(); } return ds; } } |
Spring JdbcTemplate 批量执行SQL语句 ![]() ![]() ![]() ![]() ![]() ![]() ![]() | |
Anonymous | view plaincopy to clipboardprint? package nut.jade.db; imp imp imp imp imp imp imp imp imp imp imp imp imp imp imp imp imp imp /** * 数据库操作类 * * @author nut.jade * */ @SuppressWarnings("unused") public class DBUtil { /** * jdbc 操作类 */ private JdbcTemplate jdbcTemplate; /** * 事务管理模版 */ private TransactionTemplate transactionTemplate; /** * 数据源 */ private DataSource ds; public DBUtil() { ds = getDataSource(); jdbcTemplate = new JdbcTemplate(ds); // // 事务处理 PlatformTransactionManager platformTransactionManager = new DataSourceTransactionManager( ds); transactionTemplate = new TransactionTemplate( platformTransactionManager); transactionTemplate.afterPropertiesSet(); transactionTemplate .setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED); } /** * 不带事务处理,批量更新 * * @param sql * @param obj */ public boolean batchUpdate(String sql, final List<Object[]> objs) { try { jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { Object[] obj = objs.get(i); if (obj != null) { for (int j = 0; j < obj.length; j++) { ps.setObject(j + 1, obj[j]); } } } public int getBatchSize() { return objs.size(); } }); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); return false; } return true; } /** * 事务处理,批量更新List<String> String是SQL语句 * * @return */ public boolean batchUpdate(final List<String> sqlList) { try { transactionTemplate.execute(new TransactionCallback() { public Object doInTransaction(TransactionStatus status) { for (String sql : sqlList) { jdbcTemplate.update(sql); } return null; } }); } catch (Exception e) { e.printStackTrace(); return false; } return true; } /** * 事务处理,批量更新 * Map<String,Object[]> String是SQL语句带占位符,Object[]是占位符值 * * @param map * Key值 是不允许重复为SQL * @return */ public boolean batchUpdate(final Map<String, Object[]> sqlMap) { try { transactionTemplate.execute(new TransactionCallback() { public Object doInTransaction(TransactionStatus status) { Set<String> keys = sqlMap.keySet(); for (String sql : keys) { jdbcTemplate.update(sql, sqlMap.get(sql)); } return null; } }); } catch (Exception e) { e.printStackTrace(); return false; } return true; } /** * 事务处理,批量更新 * Object[][] 允許重復 二维数组,第一列为SQL语句,第二列为Object[]占位符值 * * @param map * @return */ public boolean batchUpdate(final Object[][] sqlArr) { try { transactionTemplate.execute(new TransactionCallback() { public Object doInTransaction(TransactionStatus status) { for (int i = 0; i < sqlArr.length; i++) { String sql = sqlArr[i][0].toString(); Object[] obj = (Object[]) sqlArr[i][1]; jdbcTemplate.update(sql, obj); } return null; } }); } catch (Exception e) { e.printStackTrace(); return false; } return true; } /** * 批量更新 * * @param sqls * @return */ public int[] batchUpdate(String[] sqls) { int[] ints = jdbcTemplate.batchUpdate(sqls); return ints; } /** * 获取数据源 * * @return */ private DataSource getDataSource() { String dsType = ConfigBean.getProperty(ConfigBean.JDBC_POOL_TYPE); if (dsType.equals(ConfigBean.DBCPPool)) { return DBCPPool.getDataSource(); } String jndi = ConfigBean.getProperty(ConfigBean.JDBC_JNDI); InitialContext ctx; DataSource ds = null; try { ctx = new InitialContext(); ds = (DataSource) ctx.lookup(jndi); } catch (NamingException e) { e.printStackTrace(); } return ds; } } |
package nut.jade.db;
imp
imp
imp
imp
imp
imp
imp
imp
imp
imp
imp
imp
imp
imp
imp
imp
imp
imp
/**
* 数据库操作类
*
* @author nut.jade
*
*/
@SuppressWarnings("unused")
public class DBUtil {
/**
* jdbc 操作类
*/
private JdbcTemplate jdbcTemplate;
/**
* 事务管理模版
*/
private TransactionTemplate transactionTemplate;
/**
* 数据源
*/
private DataSource ds;
public DBUtil() {
ds = getDataSource();
jdbcTemplate = new JdbcTemplate(ds);
// // 事务处理
PlatformTransactionManager platformTransactionManager = new DataSourceTransactionManager(
ds);
transactionTemplate = new TransactionTemplate(
platformTransactionManager);
transactionTemplate.afterPropertiesSet();
transactionTemplate
.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
}
/**
* 不带事务处理,批量更新
*
* @param sql
* @param obj
*/
public boolean batchUpdate(String sql, final List<Object[]> objs) {
try {
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement ps, int i)
throws SQLException {
Object[] obj = objs.get(i);
if (obj != null) {
for (int j = 0; j < obj.length; j++) {
ps.setObject(j + 1, obj[j]);
}
}
}
public int getBatchSize() {
return objs.size();
}
});
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
return true;
}
/**
* 事务处理,批量更新List<String> String是SQL语句
*
* @return
*/
public boolean batchUpdate(final List<String> sqlList) {
try {
transactionTemplate.execute(new TransactionCallback() {
public Object doInTransaction(TransactionStatus status) {
for (String sql : sqlList) {
jdbcTemplate.update(sql);
}
return null;
}
});
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
/**
* 事务处理,批量更新
* Map<String,Object[]> String是SQL语句带占位符,Object[]是占位符值
*
* @param map
* Key值 是不允许重复为SQL
* @return
*/
public boolean batchUpdate(final Map<String, Object[]> sqlMap) {
try {
transactionTemplate.execute(new TransactionCallback() {
public Object doInTransaction(TransactionStatus status) {
Set<String> keys = sqlMap.keySet();
for (String sql : keys) {
jdbcTemplate.update(sql, sqlMap.get(sql));
}
return null;
}
});
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
/**
* 事务处理,批量更新
* Object[][] 允許重復 二维数组,第一列为SQL语句,第二列为Object[]占位符值
*
* @param map
* @return
*/
public boolean batchUpdate(final Object[][] sqlArr) {
try {
transactionTemplate.execute(new TransactionCallback() {
public Object doInTransaction(TransactionStatus status) {
for (int i = 0; i < sqlArr.length; i++) {
String sql = sqlArr[i][0].toString();
Object[] obj = (Object[]) sqlArr[i][1];
jdbcTemplate.update(sql, obj);
}
return null;
}
});
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
/**
* 批量更新
*
* @param sqls
* @return
*/
public int[] batchUpdate(String[] sqls) {
int[] ints = jdbcTemplate.batchUpdate(sqls);
return ints;
}
/**
* 获取数据源
*
* @return
*/
private DataSource getDataSource() {
String dsType = ConfigBean.getProperty(ConfigBean.JDBC_POOL_TYPE);
if (dsType.equals(ConfigBean.DBCPPool)) {
return DBCPPool.getDataSource();
}
String jndi = ConfigBean.getProperty(ConfigBean.JDBC_JNDI);
InitialContext ctx;
DataSource ds = null;
try {
ctx = new InitialContext();
ds = (DataSource) ctx.lookup(jndi);
} catch (NamingException e) {
e.printStackTrace();
}
return ds;
}
}
![spring事务控制 - cyxinda - 曹勇的博客 spring事务控制 - cyxinda - 曹勇的博客](http://img.ph.126.net/neHgueSwjjuORDGKiY-EPg==/4883027895976475619.jpg)