jdbcTemplate小用总结

一、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