背景:
直接上需求:在一个方法中查询多个数据库表的结果,然后汇总返回;
由于我们单独查询一个数据库表速度较慢(大字段查询),此时如果串行查询多个表的话效率会非常低,所以需要多线程同时查询数据库,等全部查询完毕后再汇总!
注:项目为 springboot 项目,数据库我直接用公司业务库表名,模仿的话需要自定义数据库表
1、测试项目基础结构搭建
整体结构:
pom.xml:
<?xml version="1.0" encoding="UTF-8"?>
<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/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.moerlong</groupId>
<artifactId>yj</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>yj</name>
<description>Demo project for Callable</description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.2.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-api</artifactId>
<version>RELEASE</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
application.yml:
server:
port: 80
spring:
datasource:
url: jdbc:mysql://localhost:3306/cis?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull
username: root
password: root
driver-class-name: com.mysql.jdbc.Driver
springboot 启动入口类:
package com.moerlong.yj;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.moerlong.yj.mapper") //加上mapper包扫描
public class YjApplication {
public static void main(String[] args) {
SpringApplication.run(YjApplication.class, args);
}
}
BaseMapper 接口:
package com.moerlong.yj.mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
/**
* @Author: yuanj
* @Date: 2018/5/19 10:19
*/
@Repository
public interface BaseMapper {
@Select("select hf_industry_record from msd_hf_industry_person_srcdata where card_id = #{cardId}")
public String selectIndustryData(String cardId);
@Select("select hf_judicial_record from msd_hf_judicial_person_srcdata where card_id = #{cardId}")
public String selectJudicialData(String cardId);
@Select("select td_record from msd_td_preloan_srcdata where card_id = #{cardId}")
public String selectTdData(String cardId);
}
2、串行执行
TestNoCallable 服务类:
package com.moerlong.yj.Service;
import com.moerlong.yj.mapper.BaseMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
/**
* @Author: yuanj
* @Date: 2018/5/19 10:34
*/
@Service
public class TestNoCallable {
@Autowired
private BaseMapper baseMapper;
public String test1(String cardId) throws Exception{
long start = System.currentTimeMillis();
//三个串行查询
String industryData = baseMapper.selectIndustryData(cardId);
String judicialData = baseMapper.selectJudicialData(cardId);
String tdData = baseMapper.selectTdData(cardId);
Thread.sleep(3000); //此处模拟每个查询添加1s耗时
String result = industryData + judicialData + tdData;
long end = System.currentTimeMillis();
System.out.println("串行执行:" + (end-start));
return result;
}
}
3、并行执行
TestCallable 服务类:(重点!)
package com.moerlong.yj.Service;
import com.moerlong.yj.mapper.BaseMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.lang.reflect.Method;
import java.util.concurrent.*;
/**
* @Author: yuanj
* @Date: 2018/5/19 10:28
*/
@Service
public class TestCallable {
@Autowired
private BaseMapper baseMapper;
public String test2(String cardId) throws Exception{
// 三个线程的线程池,核心线程=最大线程,没有临时线程,阻塞队列无界
ExecutorService executorService = Executors.newFixedThreadPool(3);
long start = System.currentTimeMillis();
// 开启线程执行
// 注意,此处Future对象接收线程执行结果不会阻塞,只有future.get()时候才会阻塞(直到线程执行完返回结果)
Future future1 = executorService.submit(new SelectTask<>(this, "selectIndustryData", new Object[]{cardId}));
Future future2 = executorService.submit(new SelectTask<>(this, "selectJudicialData", new Object[]{cardId}));
Future future3 = executorService.submit(new SelectTask<>(this, "selectTdData", new Object[]{cardId}));
//此处用循环保证三个线程执行完毕,再去拼接三个结果
do{
System.out.println("多任务同时执行中...");
}while (!(future1.isDone() && future2.isDone() && future3.isDone()));
String result = (String)future1.get() + future2.get() + future3.get();
long end = System.currentTimeMillis();
System.out.println("并行执行:" + (end-start));
return result;
}
//下面是三个真正执行任务(查数据库)的方法
public String selectIndustryData(String cardId) throws Exception{
String result = baseMapper.selectIndustryData(cardId);
Thread.sleep(1000); //模拟添加1s耗时
return result;
}
public String selectJudicialData(String cardId) throws Exception{
String result = baseMapper.selectJudicialData(cardId);
Thread.sleep(1000);
return result;
}
public String selectTdData(String cardId) throws Exception{
String result = baseMapper.selectTdData(cardId);
Thread.sleep(1000);
return result;
}
//任务线程类
class SelectTask<T> implements Callable<T> {
private Object object;
private Object[] args;
private String methodName;
public SelectTask(Object object, String methodName, Object[] args) {
this.object = object;
this.args = args;
this.methodName = methodName;
}
@Override
public T call() throws Exception {
Method method = object.getClass().getMethod(methodName,String.class); //此处应用反射机制,String.class是根据实际方法参数设置的
return (T) method.invoke(object, args);
}
}
}
注:要是不了解Callable使用方法,请自行查阅资料
4、测试结果
TestController :
package com.moerlong.yj.controller;
import com.moerlong.yj.Service.TestCallable;
import com.moerlong.yj.Service.TestNoCallable;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
/**
* @Author: yuanj
* @Date: 2018/5/19 10:42
*/
@RestController
public class TestController {
@Autowired
private TestNoCallable testNoCallable;
@Autowired
private TestCallable testCallable;
@RequestMapping(value = "/test1/{cardId}")
public String test1(@PathVariable String cardId) throws Exception{
String result = testNoCallable.test1(cardId);
return result;
}
@RequestMapping(value = "/test2/{cardId}")
public String test2(@PathVariable String cardId) throws Exception{
String result = testCallable.test2(cardId);
return result;
}
}
结果:
可以看到,并行确实能大幅度提高效率!
串行执行:3335
并行执行:1366
需要注意的是:MyBatis 默认开启了一级缓存,一级缓存是在SqlSession 层面进行缓存的。即,同一个SqlSession ,多次调用同一个Mapper和同一个方法的同一个参数,只会进行一次数据库查询,然后把数据缓存到缓冲中,以后直接先从缓存中取出数据,不会直接去查数据库。
所以为了公平测试,最好test1测试完重启程序再测试test2。