一、queryForList重写
public List<Map<String, Object>> queryForList(String tablename, Map<String, String> param) { //TODO valid @tablename is null StringBuilder sql = new StringBuilder("select sys_id from "); sql.append(tablename); sql.append(" where sys_deleted = ? "); List<String> valueList = new ArrayList<>(); valueList.add("0"); if (!CollectionUtils.isEmpty(param)) { for(Map.Entry<String, String> paramSet:param.entrySet()) { sql.append(" and ").append(paramSet.getKey()).append(" = ? "); valueList.add(paramSet.getValue()); } } String[] params = params = (String[])valueList.toArray(new String[valueList.size()]); return jdbcTemplate.queryForList(sql.toString(), params); } }
注意：
所有查询必需是db表中字段，包括sys_id
2、能批处理不要单个处理，原理及原因：
(1)MySQL批量SQL插入性能优化 - 唐成勇 - SegmentFault 思否
https://segmentfault.com/a/1190000008890065
参考：
spring boot(6)-JdbcTemplate访问数据库 - CSDN博客
https://blog.csdn.net/wangb_java/article/details/72789915
ArrayList和数组间的相互转换
http://wanglihu.iteye.com/blog/243238
二、batchUpdate方法使用
需求：business表中的若干数据，需要把这些数据的id，随机的选一条，插入到a,b,c d这些表中的business列中
package cn.partner4java.dao; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Map; import java.util.Random; import org.apache.commons.lang3.RandomStringUtils; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.jdbc.core.BatchPreparedStatementSetter; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.support.JdbcDaoSupport; import org.springframework.transaction.annotation.Transactional; /** * @author partner4java * 使用JDBC Template处理数据 http://irfen.me/use-jdbc-template-manipulation-data/ * */ public class HelloDaoImpl extends JdbcDaoSupport { @Transactional public void saveHello(String tablename){ List<String> listBusiness = getBusinessId(); final List<Object[]> updateParams = new ArrayList<Object[]>(); List<Map<String, Object>> cis = this.getJdbcTemplate().queryForList("select * from "+tablename); for (Map<String, Object> map : cis) { String cisysid = (String) map.get("sys_id"); java.util.Random random=new java.util.Random();// 定义随机类 int result=random.nextInt(listBusiness.size());// String businessid = listBusiness.get(result); updateParams.add(new Object[] { businessid, cisysid }); } addCIs(tablename, updateParams); } public void addCIs(String tablename , final List<Object[]> updateParams ) { String sql = "update "+ tablename + " set cmdb_ci_business=? where sys_id=?"; this.getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() { @Override public int getBatchSize() { return updateParams.size(); } @Override public void setValues(PreparedStatement ps, int i) throws SQLException { Object[] args = updateParams.get(i); ps.setString(1, (String) args[0]); ps.setString(2, (String) args[1]); } }); } private List<String> getBusinessId() { List<String> listBusiness = new ArrayList<>(); List<Map<String, Object>> cis = this.getJdbcTemplate().queryForList("select * from cmdb_ci_business"); for (Map<String, Object> map : cis) { listBusiness.add((String)map.get("sys_id")); } return listBusiness; } public static void main(String[] args) { ApplicationContext ac = new ClassPathXmlApplicationContext("app-context.xml"); HelloDaoImpl helloDao = (HelloDaoImpl) ac.getBean("helloDao"); List<String> tableList = new ArrayList<>(); tableList.add("a"); tableList.add("b"); tableList.add("c"); tableList.add("d"); tableList.add("e"); tableList.add("f"); tableList.add("g"); tableList.add("h"); for (String table : tableList) { helloDao.saveHello(table); } } }
app.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"> <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://10.128.106.78:3306/itsm?useSSL=false" /> <property name="username" value="root" /> <property name="password" value="OneITSM" /> </bean> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <constructor-arg> <ref bean="dataSource" /> </constructor-arg> </bean> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource" /> </bean> <tx:annotation-driven transaction-manager="transactionManager" /> <aop:aspectj-autoproxy /> <!-- <context:component-scan base-package="cn.partner4java.dao" /> <context:component-scan base-package="cn.partner4java.myptm" /> --> <bean id="helloDao" class="cn.partner4java.dao.HelloDaoImpl"> <property name="jdbcTemplate"> <ref bean="jdbcTemplate"/> </property> </bean> <!-- <bean id="user" class="springjdbc.pojo.User"> <property name="dao"> <ref bean="userDAO"/> </property> </bean> --> </beans>
pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.paymoon</groupId> <artifactId>simplebusiness</artifactId> <packaging>war</packaging> <version>0.0.1-SNAPSHOT</version> <name>simplebusiness Maven Webapp</name> <url>http://maven.apache.org</url> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> </properties> <dependencies> <dependency> <groupId>com.paymoon.basic</groupId> <artifactId>basic-commons</artifactId> <version>0.0.1-SNAPSHOT</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.39</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.0.29</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-gpg-plugin</artifactId> <version>1.4</version> <executions> <execution> <id>sign-artifacts</id> <phase>verify</phase> <goals> <goal>sign</goal> </goals> </execution> </executions> </plugin> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-release-plugin</artifactId> <version>2.2.2</version> <dependencies> <dependency> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-scm-plugin</artifactId> <version>1.8.1</version> </dependency> </dependencies> </plugin> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <version>3.2</version> <configuration> <source>1.8</source> <target>1.8</target> </configuration> </plugin> </plugins> </build> </project>
划重点
1 int result=random.nextInt(listBusiness.size());//
String businessid = listBusiness.get(result)
2 update的使用
String sql = "update "+ tablename + " set cmdb_ci_business=? where sys_id=?"; this.getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() { @Override public int getBatchSize() { return updateParams.size(); } @Override public void setValues(PreparedStatement ps, int i) throws SQLException { Object[] args = updateParams.get(i); ps.setString(1, (String) args[0]); ps.setString(2, (String) args[1]); }
3 jdbctemplate的注入问题
this.getJdbcTemplate()以及app-context.xml配置问题
4 dadasource和jdbctemplate
5 mysql jdbctemplate 批处理的原理
参考：
Spring的JDBCTemplate批量更新的性能问题 - CSDN博客
https://blog.csdn.net/birdben/article/details/51813669
MySQL批量SQL插入性能优化 - 唐成勇 - SegmentFault 思否
https://segmentfault.com/a/1190000008890065