杂记jdbc保存

/**
 *  批量插入
 * @Version                  : 1.0
 * @Author                   :
 * @CreateDate               : Jan 21, 2013 9:45:31 AM
 * @param list
 * @throws SQLException 
 * @Return
 */
public void batchInsert(List<ProjectArDetail> list) throws SQLException{
PreparedStatement pstmt = null;
Connection con = null;
try {
Properties properties = ConfigUtil.getProperties(Global.JdbcConnectionFileNme);
String username = properties.getProperty(Global.JdbConnectionUserName);
String password = properties.getProperty(Global.JdbcConnectionPassword);
String className = properties.getProperty(Global.JdbcConnectionClassName);
String url =  properties.getProperty(Global.JdbcConnectionUrl);


JdbcHelperUtil jdbc = new JdbcHelperUtil(username,password,className,url);
con = jdbc.getConnection();
con.setAutoCommit(false);

String sql = "insert into b_project_ar_detail(" +
"year,month,sale_id,sale_name,project_sn,bill_num,project_name,company_name," +
"billing_date,ar_date,project_gp,project_type,ar_money,money_award,contract_date," +
"contract_start,contract_finish,terms_of_pay,ec_or_nc,budget_revenue," +
"status,modify_person,modify_time,remark)"+
" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

pstmt = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);

for (int i = 0; i < list.size(); i++) {
ProjectArDetail detail = list.get(i);
if(detail==null){continue;}


pstmt.setString(1, detail.getYear());
pstmt.setString(2, detail.getMonth());
if(detail.getSaleId()!=null && detail.getSaleId()>0)
pstmt.setInt(3, detail.getSaleId());
else 
pstmt.setNull(3, Types.INTEGER);


pstmt.setString(4, detail.getSaleName());
pstmt.setString(5, detail.getProjectSn());
pstmt.setString(6, detail.getBillNum());
pstmt.setString(7, detail.getProjectName());
pstmt.setString(8, detail.getCompanyName());
if(detail.getBillingDate()!=null)
pstmt.setDate(9, Date.valueOf(DateUtil.formatDateToYMD(detail.getBillingDate())));
if(detail.getArDate()!=null)
pstmt.setDate(10, Date.valueOf(DateUtil.formatDateToYMD(detail.getArDate())));


if(detail.getProjectGp()!=null && !detail.getProjectGp().equals(""))
pstmt.setDouble(11, detail.getProjectGp());
else
pstmt.setNull(11, Types.DOUBLE);


if(detail.getProjectType()!=null && detail.getProjectType()>0)
pstmt.setInt(12, detail.getProjectType());
else
pstmt.setNull(12, Types.INTEGER);


if(detail.getArMoney()!=null && detail.getArMoney()>0)
pstmt.setDouble(13, detail.getArMoney());
else
pstmt.setNull(13, Types.DOUBLE);


if(detail.getMoneyAward()!=null && detail.getMoneyAward()>0)
pstmt.setDouble(14, detail.getMoneyAward());
else
pstmt.setNull(14, Types.DOUBLE);


if(detail.getContractDate()!=null)
pstmt.setDate(15, Date.valueOf(DateUtil.formatDateToYMD(detail.getContractDate())));
else
pstmt.setNull(15, Types.DATE);

if(detail.getContractStart()!=null)
pstmt.setDate(16, Date.valueOf(DateUtil.formatDateToYMD(detail.getContractStart())));
else
pstmt.setNull(16, Types.DATE);

if(detail.getContractFinish()!=null)
pstmt.setDate(17, Date.valueOf(DateUtil.formatDateToYMD(detail.getContractFinish())));
else
pstmt.setNull(17, Types.DATE);



if(detail.getTermsOfPay()!=null && !detail.getTermsOfPay().equals(""))
pstmt.setString(18, detail.getTermsOfPay());
else
pstmt.setNull(18, Types.LONGVARCHAR);


if(detail.getEcOrNc()!=null && !detail.getEcOrNc().equals("")) 
pstmt.setString(19, detail.getEcOrNc());
else
pstmt.setNull(19, Types.VARCHAR);


if(detail.getBudgetRevenue()!=null && detail.getBudgetRevenue()>0)
pstmt.setDouble(20, detail.getBudgetRevenue());
else
pstmt.setNull(20, Types.DOUBLE);


if(detail.getStatus()!=null && !detail.getStatus().equals(""))
pstmt.setString(21, detail.getStatus());
else
pstmt.setNull(21, Types.VARCHAR);


if(detail.getModifyPerson()!=null && detail.getModifyPerson()>0)
pstmt.setInt(22, detail.getModifyPerson());
else
pstmt.setNull(22, Types.INTEGER);
if(detail.getModifyTime()!=null)
pstmt.setDate(23, Date.valueOf(DateUtil.formatDateToYMD(detail.getModifyTime())));
else
pstmt.setNull(23, Types.DATE);


if(detail.getRemark()!=null && !detail.getRemark().equals(""))
pstmt.setString(24, detail.getRemark());
else
pstmt.setNull(24, Types.VARCHAR);


pstmt.addBatch();
}


pstmt.executeBatch();
con.commit();


}catch (Exception e) {
e.printStackTrace();
}finally{
if(con!=null){
con.close();
}
}


}