JDBC通过反射机制批量的把List里面的对象添加到数据库中

1. 通过传递BOList,表名，sequence,还有一个表字段的数组为参数，来完成数据的批量删除

 

      String[] columnNames = {"BIZ_CODE_ID","CODE_TYPE","CODE_NUM","CODE_VALUE",
                                               "CODE_DESC","CODE_ORDER","IS_DESC_DISPLAY_REQUIRED"};
   
      commonJdbcDAO.insertBatchBOList(bizCodeDefList, "biz_code","seq_biz_code",columnNames);

 

2.JDBC中的insertBatchBOList方法内容

 

/** * Seq_financial_rep_acct_data.Nextval * @param boList * @param tableName * @param columnNames * @return */ public int[] insertBatchBOList(List boList,String tableName,String idSequence,String[] columnNames){ StringBuffer sqlStr = new StringBuffer( "insert into ").append(tableName); sqlStr.append(" ("); for(int i=0;i<columnNames.length;i++){ if(i==0) { sqlStr.append(columnNames[i]); } else { sqlStr.append(","); sqlStr.append(columnNames[i]); } } sqlStr.append(") values ("); for(int j=0;j<columnNames.length;j++) { if(j==0) { sqlStr.append("?"); } else { sqlStr.append(",?"); } } sqlStr.append(")"); //for test sqlStr = new StringBuffer(); // sqlStr.append("insert into biz_code (BIZ_CODE_ID,CODE_TYPE,CODE_NUM,CODE_VALUE,CODE_DESC,CODE_ORDER,IS_DESC_DISPLAY_REQUIRED) values (seq_biz_code.Nextval,?,?,?,?,?,?)"); sqlStr.append("insert into biz_code (BIZ_CODE_ID) values (seq_biz_code.Nextval)"); //for test //String sql = "insert into monitor_snap(Target,UCIName,ServerName,EnterTime,OrgCd,Username) values(?,?,?,?,?,?)"; JdbcControl.update(sqlStr.toString(),"BIZ_CODE_ID"); // return JdbcControl.batchInsert(sqlStr.toString(),new MyBatchPreparedStatementSetter(boList,columnNames)); return null; }

 

 

3。相应的 MyBatchPreparedStatementSetter 类代码

 

package cn.ccb.faengine.common.dao.jdbc.util; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.Date; import java.util.List; import org.apache.commons.beanutils.MethodUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.springframework.jdbc.core.BatchPreparedStatementSetter; public class MyBatchPreparedStatementSetter implements BatchPreparedStatementSetter { private static final Log logger = LogFactory .getLog(MyBatchPreparedStatementSetter.class); private List boList; private String[] columnNames; public MyBatchPreparedStatementSetter(List boList,String[] columnNames){ this.boList = boList; this.columnNames = columnNames; } public void setValues(PreparedStatement arg0, int arg1) throws SQLException { // TODO 自动生成方法存根 Object boObject = boList.get(arg1); for(int i= 1;i<columnNames.length;i++){ //String methodName = "get" + String.valueOf(columnNames[i].charAt(0)).toUpperCase()+ columnNames[i].substring(1); String methodName = this.getEntityMethodName(columnNames[i]); try{ Object valueObj = MethodUtils.invokeMethod(boObject, methodName,null); arg0.setObject(i, valueObj); }catch(Exception e){ throw new SQLException(e.getMessage()); } } } public int getBatchSize() { // TODO 自动生成方法存根 return boList.size(); } /** * 把传递进来的数据库真实字段转化为对应的BO的get方法 * @param columnName * @return */ private String getEntityMethodName(String columnName) { String[] str = columnName.split("_"); StringBuffer sb = new StringBuffer(); sb.append("get"); for(int i=0;i<str.length;i++) { String firstChar = String.valueOf(str[i].charAt(0)).toUpperCase(); String otherStr = str[i].substring(1).toLowerCase(); sb.append(firstChar + otherStr); } return sb.toString(); } }

 

 

4。相应的JdbcControl中的对应方法

 

package cn.ccb.faengine.common.dao.jdbc.util; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.PreparedStatementCreator; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.jdbc.support.KeyHolder; import org.springframework.transaction.TransactionDefinition; import org.springframework.transaction.TransactionStatus; import org.springframework.transaction.support.DefaultTransactionDefinition; public class JdbcControl { private static final Log logger = LogFactory .getLog(JdbcControl.class); private static JdbcTemplate jdbcTemplate; private static DataSourceTransactionManager dsTxMgr; /** * 通过此方法操作数据库，并返回插入数据的主键 * @param sql * @param idName * @return */ public static Long update(String sql,String idName) { TransactionStatus txStatus = getTxStatus(TransactionDefinition.PROPAGATION_REQUIRES_NEW); Long returnLong = null; int seqNum = 0; try { final String INSERT_SQL = "insert into biz_code(BIZ_CODE_ID,CODE_TYPE) values(SEQ_BIZ_CODE.nextval,?) returning id into ?";//sql; //"insert into my_test (name) values(?)"; final String id = "111";//idName; KeyHolder keyHolder = new GeneratedKeyHolder(); int rowsAffected = jdbcTemplate.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection conn) throws SQLException { System.out.println(INSERT_SQL); PreparedStatement ps = conn.prepareStatement(INSERT_SQL,new String[] {"BIZ_CODE_ID"}); //PreparedStatement ps = null; //PreparedStatement sta = conn.prepareStatement("INSERT INTO LOGGING VALUES (TESTSEQ.NEXTVAL,SYSDATE)returning id into ? "); return ps; } },keyHolder); returnLong = Long.valueOf(keyHolder.getKey().longValue()); dsTxMgr.commit(txStatus); } catch (DataAccessException e) { // TODO 自动生成 catch 块 dsTxMgr.rollback(txStatus); e.printStackTrace(); } System.out.println("returnLong is " + returnLong); return returnLong; } public static int[] batchInsert(String sql , MyBatchPreparedStatementSetter myBatchPreparedStatementSetter){ TransactionStatus txStatus = getTxStatus(TransactionDefinition.PROPAGATION_REQUIRES_NEW); int[] a = null; try { a = jdbcTemplate.batchUpdate(sql,myBatchPreparedStatementSetter); dsTxMgr.commit(txStatus); } catch (DataAccessException e) { // TODO 自动生成 catch 块 dsTxMgr.rollback(txStatus); e.printStackTrace(); } return a; } public static void commitDB(String sql) { TransactionStatus txStatus = getTxStatus(TransactionDefinition.PROPAGATION_REQUIRES_NEW); try { jdbcTemplate.update(sql); dsTxMgr.commit(txStatus); } catch (DataAccessException e) { // TODO 自动生成 catch 块 dsTxMgr.rollback(txStatus); e.printStackTrace(); } } public static JdbcTemplate getJdbcTemplate() { return jdbcTemplate; } public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { JdbcControl.jdbcTemplate = jdbcTemplate; } public DataSourceTransactionManager getDsTxMgr() { return dsTxMgr; } public void setDsTxMgr(DataSourceTransactionManager dsTxMgr) { JdbcControl.dsTxMgr = dsTxMgr; } private static TransactionStatus getTxStatus(int txdefine) { DefaultTransactionDefinition txDef = getTxDef(txdefine); TransactionStatus txStatus = dsTxMgr.getTransaction(txDef); return txStatus; } private static DefaultTransactionDefinition getTxDef(int txdefine) { DefaultTransactionDefinition txDef = new DefaultTransactionDefinition(); txDef.setPropagationBehavior(txdefine); return txDef; } }

 

5。配置文件中添加相对应的配置记录

 

<bean id="dsTxMgr" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource"> <ref local="jndiDataSource" /> </property> </bean> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource"> <ref bean="jndiDataSource" /> </property> </bean>

 

 

这样，就是一个大概的JDBC批量添加数据的方法代码，其中有些代码动需要自己改动下！