锐客网

  1. 首页 > it技术 > >

使用spring的JdbcTemplate操作批量插入数据

java

场景:从数据库中一张表批量查出数,每条记录封装到一个实例对象中,再将批量查出的数据插入到另一张数据库表
1.实现类与测试main方法

public class BatchOperationDao { private static String querySql = "SELECT CITY_NAME,LAND_AREA,POPULATION,GROSS,AREA_NUMBER,POSTAL_CODE,TELEPHONE_CODE,CAR_CODE,CITY_DESCRIBE FROM T_CITY WHEREAREA_NUMBER= ? "; private static String insertBatchSql = "INSERT INTO T_CITY_INFO(CITY_NAME,LAND_AREA,POPULATION,GROSS,AREA_NUMBER,POSTAL_CODE,TELEPHONE_CODE,CAR_CODE,CITY_DESCRIBE) VALUES (?,?,?,?,?,?,?,?,?) "; private static Logger logger = LoggerFactory.getLogger(BatchOperationDao.class); @SuppressWarnings("unchecked") public List queryForInsert() { List list = new ArrayList(); Object[] Params = {"350200"}; JdbcTemplate jdbc= OperateJdbcUtils.getJdbcTemplate(); list = jdbc.query(querySql, Params,new CityInfoMapper()); return list; } private static final class CityInfoMapper implements RowMapper{@Override public Object mapRow(ResultSet rs, int rowNum) throws SQLException { CityModel cityModel = new CityModel(); cityModel.setCityName(rs.getString("CITY_NAME")); cityModel.setLandArea(rs.getDouble("LAND_AREA")); cityModel.setPopulation(rs.getLong("POPULATION")); cityModel.setGross(rs.getDouble("GROSS")); cityModel.setAreaNumber(rs.getString("AREA_NUMBER")); cityModel.setPostalCode(rs.getString("POSTAL_CODE")); cityModel.setTelephoneCode(rs.getString("TELEPHONE_CODE")); cityModel.setCarCode(rs.getString("CAR_CODE")); cityModel.setCityDescribe(rs.getString("CITY_DESCRIBE")); return cityModel; } } public int batchInsert(final List list) throws Exception { if (list.size() == 0) { return 0; } JdbcTemplate jdbc= OperateJdbcUtils.getJdbcTemplate(); String batchsql=getBatchInsertSql(insertBatchSql, list); int k= jdbc.update( batchsql , new PreparedStatementSetter(){ @Override public void setValues(PreparedStatement ps) throws SQLException { ObjectToPs(ps, list); } }); return k; } public void ObjectToPs(PreparedStatement ps, List list) throws SQLException { for (int i = 0; i < list.size(); ++i) { CityModel cityModel = (CityModel) list.get(i); ps.setString(9*i+1, cityModel.getCityName()); ps.setDouble(9*i+2, cityModel.getLandArea() ); ps.setLong(9*i+3, cityModel.getPopulation() ); ps.setDouble(9*i+4, cityModel.getGross() ); ps.setString(9*i+5, cityModel.getAreaNumber() ); ps.setString(9*i+6, cityModel.getPostalCode() ); ps.setString(9*i+7, cityModel.getTelephoneCode() ); ps.setString(9*i+8, cityModel.getCarCode() ); ps.setString(9*i+9, cityModel.getCityDescribe() ); } } public static String getBatchInsertSql(String keyStr,List list) throws Exception{int index = keyStr.indexOf("VALUES"); String loopStr = keyStr.substring(index+6); StringBuilder builder = new StringBuilder(keyStr.substring(0, index+6)); for (int j = 0; j list = dao.queryForInsert(); dao.batchInsert(list); logger.info("测试结束......"); } }
2.实体类
public class CityModel implements Serializable { private static final long serialVersionUID = 4443714978780088961L; String cityName; double landArea; long population; double gross ; String areaNumber; String postalCode; String telephoneCode; String carCode; String cityDescribe; public String getCityName() { return cityName; } public void setCityName(String cityName) { this.cityName = cityName; } public double getLandArea() { return landArea; } public void setLandArea(double landArea) { this.landArea = landArea; } public long getPopulation() { return population; } public void setPopulation(long population) { this.population = population; } public double getGross() { return gross; } public void setGross(double gross) { this.gross = gross; } public String getAreaNumber() { return areaNumber; } public void setAreaNumber(String areaNumber) { this.areaNumber = areaNumber; } public String getPostalCode() { return postalCode; } public void setPostalCode(String postalCode) { this.postalCode = postalCode; } public String getTelephoneCode() { return telephoneCode; } public void setTelephoneCode(String telephoneCode) { this.telephoneCode = telephoneCode; } public String getCarCode() { return carCode; } public void setCarCode(String carCode) { this.carCode = carCode; } public String getCityDescribe() { return cityDescribe; } public void setCityDescribe(String cityDescribe) { this.cityDescribe = cityDescribe; } @Override public String toString() {return "CityModel [cityName=" + cityName +",landArea=" + landArea +",population=" + population +",gross=" + gross +",areaNumber=" + areaNumber +",postalCode=" + postalCode +",telephoneCode=" + telephoneCode +",carCode=" + carCode +",cityDescribe=" + cityDescribe +"]"; } }

3.JdbcTemplate实例类
public class OperateJdbcUtils { /**数据库连接需要字符串*/ public static final String username = "root"; public static final String password = "123456"; public static final String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/zbzdb"; public static final String driverName = "com.mysql.jdbc.Driver"; public static JdbcTemplate jdbcTemplate = getJdbcTemplate(); /**获取一个JdbcTemplate实例对象*/ public static JdbcTemplate getJdbcTemplate() { DruidDataSource dataSource = new DruidDataSource(); dataSource.setPassword(password); dataSource.setUrl(jdbcUrl); dataSource.setUsername(username); dataSource.setDriverClassName(driverName); JdbcTemplate jdbcTemplate = new JdbcTemplate(); jdbcTemplate.setDataSource(dataSource); return jdbcTemplate; } }

4.数据库两张表
CREATE TABLE `t_city` ( `CITY_NAME` VARCHAR(64) COLLATE utf8_bin NOT NULL COMMENT '城市名', `LAND_AREA` DOUBLE DEFAULT NULL COMMENT '城市面积', `POPULATION` BIGINT(16) DEFAULT NULL COMMENT '城市人口', `GROSS` DOUBLE DEFAULT NULL COMMENT '生产总值', `AREA_NUMBER` VARCHAR(64) COLLATE utf8_bin DEFAULT NULL COMMENT '行政区划代码', `POSTAL_CODE` VARCHAR(64) COLLATE utf8_bin DEFAULT NULL COMMENT '邮政编码', `TELEPHONE_CODE` VARCHAR(64) COLLATE utf8_bin DEFAULT NULL COMMENT '电话区号', `CAR_CODE` VARCHAR(64) COLLATE utf8_bin DEFAULT NULL COMMENT '车牌代码', `CITY_DESCRIBE` VARCHAR(512) COLLATE utf8_bin DEFAULT NULL COMMENT '城市描述' ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='城市信息表'CREATE TABLE `t_city_info` ( `CITY_NAME` VARCHAR(64) COLLATE utf8_bin NOT NULL COMMENT '城市名', `LAND_AREA` DOUBLE DEFAULT NULL COMMENT '城市面积', `POPULATION` BIGINT(16) DEFAULT NULL COMMENT '城市人口', `GROSS` DOUBLE DEFAULT NULL COMMENT '生产总值', `AREA_NUMBER` VARCHAR(64) COLLATE utf8_bin DEFAULT NULL COMMENT '行政区划代码', `POSTAL_CODE` VARCHAR(64) COLLATE utf8_bin DEFAULT NULL COMMENT '邮政编码', `TELEPHONE_CODE` VARCHAR(64) COLLATE utf8_bin DEFAULT NULL COMMENT '电话区号', `CAR_CODE` VARCHAR(64) COLLATE utf8_bin DEFAULT NULL COMMENT '车牌代码', `CITY_DESCRIBE` VARCHAR(512) COLLATE utf8_bin DEFAULT NULL COMMENT '城市描述' ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='城市信息表'

【使用spring的JdbcTemplate操作批量插入数据】以上,TKS.

    推荐阅读


    上一篇：算法导论程序16--基数排序（Python）

    下一篇：禹音（当下社会之我想）