spring mysql jdbc insert 占位符_【JDBC】使用Spring提供的JDBCTemplate通过Statement向MySql数据库插入千万条数据，耗时4m49s，使用inse...

packagecom.hy.action.jdbc;importjava.sql.Connection;importjava.sql.SQLException;importjava.sql.Statement;importjava.text.MessageFormat;importjava.text.SimpleDateFormat;importjava.util.ArrayList;importjava.util.Calendar;importjava.util.Date;importjava.util.List;importorg.apache.log4j.Logger;importorg.springframework.context.ApplicationContext;importorg.springframework.context.support.ClassPathXmlApplicationContext;importorg.springframework.jdbc.core.JdbcTemplate;public classBatchJDBCStmtInsert2 {private static Logger logger = Logger.getLogger(BatchJDBCStmtInsert2.class);public static voidmain(String[] args) {long startTime =System.currentTimeMillis();//把beans.xml的类加载到容器

ApplicationContext applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");

JdbcTemplate jt=(JdbcTemplate)applicationContext.getBean("jdbcTemplate");//Initialize conn&stmt

Connection conn=null;

Statement stmt=null;try{

conn=jt.getDataSource().getConnection();

conn.setAutoCommit(false);

stmt=conn.createStatement();

String ctime="2017-11-01 00:00:01";int index=0;for(int i=0;i<10000;i++) {

String insertSql="insert into emp(name,age,cdate) values ";

List list=new ArrayList();for(int j=0;j<1000;j++) {

index++;

Object arr[]={"'E:"+index+"'",index % 100,"'"+ctime+"'"};

String valueSql=MessageFormat.format("({0},{1},{2})", arr);

list.add(valueSql);

ctime=timePastOneSecond(ctime);

}

String sql=insertSql+String.join(",", list);

stmt.addBatch(sql);

stmt.executeBatch();//stmt.clearBatch();

conn.commit();

logger.info("#"+i+" 1000 records have been inserted to table:'emp'.");

}

}catch(SQLException e) {

logger.error("Error happened:"+e);try{

conn.rollback();

}catch(SQLException e1) {

logger.error("Can not rollback because of the error:'"+e+"'.");

}

}finally{try{

stmt.close();

conn.close();long endTime =System.currentTimeMillis();

logger.info("Time elapsed:" + toDhmsStyle((endTime - startTime)/1000) + ".");

}catch(SQLException e1) {

logger.error("Can not close connection because of the error:'"+e1+"'.");

}

}

}public staticString timePastOneSecond(String otime) {try{

SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

Date dt=sdf.parse(otime);

Calendar newTime=Calendar.getInstance();

newTime.setTime(dt);

newTime.add(Calendar.SECOND,1);

Date dt1=newTime.getTime();

String retval=sdf.format(dt1);returnretval;

}catch(Exception ex) {

ex.printStackTrace();return null;

}

}//format seconds to day hour minute seconds style//Example 5000s will be formatted to 1h23m20s

public static String toDhmsStyle(longallSeconds) {

String DateTimes= null;long days = allSeconds / (60 * 60 * 24);long hours = (allSeconds % (60 * 60 * 24)) / (60 * 60);long minutes = (allSeconds % (60 * 60)) / 60;long seconds = allSeconds % 60;if (days > 0) {

DateTimes= days + "d" + hours + "h" + minutes + "m" + seconds + "s";

}else if (hours > 0) {

DateTimes= hours + "h" + minutes + "m" + seconds + "s";

}else if (minutes > 0) {

DateTimes= minutes + "m" + seconds + "s";

}else{

DateTimes= seconds + "s";

}returnDateTimes;

}

}