基于Java线程池读取数据库中数据（学习+运用）
以下是学习内容
Main.java
import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
public class Main {
private static final int CORE_POOL_SIZE = 5;//核心线程数为5
private static final int MAX_POOL_SIZE = 15;//最大线程数15
private static final int QUEUE_CAPACITY = 5;//任务队列容量为200
private static final Long KEEP_ALIVE_TIME = 200L;//等待时间为1L
public static void main(String[] args) {
//使用阿里巴巴推荐的创建线程池的方式
//通过ThreadPoolExecutor构造函数自定义参数创建
ThreadPoolExecutor executor = new ThreadPoolExecutor(
CORE_POOL_SIZE,
MAX_POOL_SIZE,
KEEP_ALIVE_TIME,
TimeUnit.MILLISECONDS,
new ArrayBlockingQueue<Runnable>(QUEUE_CAPACITY),
new ThreadPoolExecutor.CallerRunsPolicy()); //最后是拒绝策略
long start = System.currentTimeMillis();
System.out.println("activeCountMain1 : " + Thread.activeCount());
for (int i = 1; i <= 10; i++) {
MySQL mysql = new MySQL(i);
executor.execute(mysql);
System.out.println("线程池中线程数目：" + executor.getPoolSize() + "，队列中等待执行的任务数目：" + executor.getQueue().size()
+ "，已执行玩别的任务数目：" + executor.getCompletedTaskCount());
}
executor.shutdown();
while (true) {
if (executor.getActiveCount() == 0)
break;
}
System.out.println("activeCountMain2 : " + Thread.activeCount());
long end = System.currentTimeMillis();
System.out.println("平均每秒可输出: " + 100000 / (end - start) + " 条");
}
}
MySQL.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mysql.jdbc.Statement;
/**
* @author 作者 侯丽娟:
* @version 创建时间：2021年10月7日 下午1:37:14
* 类说明
*/
class MySQL implements Runnable{
private Connection con = null;
private static String driver = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/loan";
private static String username = "root";
private static String password = "root";
private static Statement NULL = null;
private final int taskNum;
public MySQL(int taskNum) {
this.taskNum = taskNum;
}
public static void main(String[] args) {
// MySQL mySQL = new MySQL(1);
// Statement state = mySQL.MysqlOpen();
// System.out.println(state);
// mySQL.readMySQL();
}
public Statement MysqlOpen() {
try {
Class.forName(driver); //加载驱动类
con = DriverManager.getConnection(url, username, password); //连接数据库
if (!con.isClosed())
System.out.println("***数据库成功连接***");
Statement state = (Statement) con.createStatement();
return state;
} catch (ClassNotFoundException e) {
System.out.println("找不到驱动程序类，加载驱动失败");
e.printStackTrace();
} catch (SQLException e) {
System.out.println("数据库连接失败");
e.printStackTrace();
}
return NULL;
}
@Override
public void run() {
readMySQL();
}
public void readMySQL() {
synchronized(MySQL.class){
ResultSet sql = null;
Statement state = MysqlOpen();
try {
sql = state.executeQuery("select * from jd_area_info where AreaID between "+ ((taskNum - 1) * 10) + " and " + (taskNum * 10));
System.out.println("---------task " + taskNum + "正在执行---------");
while (sql.next()) {
int id = sql.getInt(1);
String areaname = sql.getString(3);
String areaname2 = sql.getString(5);
System.out.println(id + "\t" + areaname + "\t" + areaname2);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
sql.close();
state.close();
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
System.out.println("---------task " + taskNum + "执行完毕---------");
}
}
}
运行结果如下图所示：
【参考】https://www.jb51.net/article/168823.htm
以下是实际运用
这是Controller中原本取数据的方法：
@RequestMapping(value = "/getSensorData", method = RequestMethod.GET)
@ApiOperation(value = "通过sensorid获取所有数据")
public Result<datashow> getSensorData(HttpServletRequest request){
long startTime = System.currentTimeMillis();
String startDate = request.getParameter("startDate");
String endDate = request.getParameter("endDate");
String sid = request.getParameter("sid");
System.out.println(sid+":开始时间加结束时间："+startDate+" "+endDate+"传感器编号:"+sid);
List<SensorWindPressure> list = iSensorWindPressureService.getSensorData(sid,startDate,endDate);
log.info("数据量大小"+list.size());
SensorInfo sensorInfo = iSensorWindPressureService.getDownUpLimit(sid);
String uplimit = sensorInfo.getUpLimit();
String downlimit = sensorInfo.getDownLimit();
long endTime = System.currentTimeMillis();
datashow datashow = new datashow();
List<String> xdata = new LinkedList<>();
List<String> ydata = new LinkedList<>();
for (SensorWindPressure sensorWindPressure : list) {
xdata.add(sdf.format(sensorWindPressure.getCreateTime()));
ydata.add(sensorWindPressure.getValue());
}
datashow.setXdata(xdata);
datashow.setYdata(ydata);
System.out.println("代码运行时间：" + (endTime - startTime) + "ms");
datashow.setUplimit(uplimit);
datashow.setDownlimit(downlimit);
return new ResultUtil<datashow>().setData(datashow);
// System.out.println("开始时间加结束时间："+startDate+" "+endDate+"上限下限:"+uplimit+","+downlimit);
// return new ResultUtil<List<SensorWindPressure>>().setData(list,uplimit,downlimit);
}
以下是对以上方法的改进，使用多线程进行数据读取：
private static final int CORE_POOL_SIZE = 5;//核心线程数为5
private static final int MAX_POOL_SIZE = 15;//最大线程数15
private static final int QUEUE_CAPACITY = 5;//任务队列容量为200
private static final Long KEEP_ALIVE_TIME = 10L;//等待时间为10s
@RequestMapping(value = "/getSensorDataByline", method = RequestMethod.GET)
@ApiOperation(value = "通过sensorid获取所有数据")
public Result<datashow> getSensorDataByline(HttpServletRequest request) throws Exception{
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
long startTime = System.currentTimeMillis();
String startDate = request.getParameter("startDate");
String endDate = request.getParameter("endDate");
String sid = request.getParameter("sid");
System.out.println(sid+":开始时间加结束时间："+startDate+" "+endDate+"传感器编号:"+sid);
Date d1=sf.parse(startDate);
Date d2=sf.parse(endDate);
int len = daysBetween(d1,d2);
System.out.println("相差天数："+len);
//使用阿里巴巴推荐的创建线程池的方式
//通过ThreadPoolExecutor构造函数自定义参数创建
ThreadPoolExecutor executor = new ThreadPoolExecutor(
CORE_POOL_SIZE,
MAX_POOL_SIZE,
KEEP_ALIVE_TIME,
TimeUnit.SECONDS,
new ArrayBlockingQueue<Runnable>(QUEUE_CAPACITY),
new ThreadPoolExecutor.CallerRunsPolicy()); //最后是拒绝策略
ArrayList<Future<List<SensorWindPressure>>> results=new ArrayList<>();
for (int i = 1; i <= len; i++) {
// SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
Calendar c = Calendar.getInstance();
c.setTime(d1);
c.add(c.DATE,i);
String endDate2 = sf.format(c.getTime());
System.out.println("endDate2："+endDate2+" "+i);
c.setTime(d1);
c.add(c.DATE,i-1);
String startDate2 = sf.format(c.getTime());
System.out.println("startDate2："+startDate2+" "+i);
SmokeTask test = new SmokeTask(startDate2,endDate2,sid);
Future<List<SensorWindPressure>> future = executor.submit(test);
try{
while (true) {
if(future.isDone() && !future.isCancelled()){
System.out.println(future);
results.add(future);
break;
}else {
Thread.sleep(1000);
}
}
}catch (Exception e) {
e.printStackTrace();
}
/*try {
if(future.isDone()){
System.out.println("以下是future：");
System.out.println(future);
System.out.println(future.get());
}
} catch (Exception e) {
e.printStackTrace();
}*/
System.out.println("线程池中线程数目：" + executor.getPoolSize() + "，队列中等待执行的任务数目：" + executor.getQueue().size()
+ "，已执行玩别的任务数目：" + executor.getCompletedTaskCount());
}
// List<SensorWindPressure> list = iSensorWindPressureService.getSensorData(sid,startDate,endDate);
List<SensorWindPressure> list = new ArrayList<>();
for (Future<List<SensorWindPressure>> future: results) {
try {
System.out.println(future);
System.out.println(future.get());
list.addAll(future.get());
// System.out.println(future.get());
} catch (Exception e) {
e.printStackTrace();
}
finally{
executor.shutdown();
}
}
System.out.println(list.size());
list.sort(Comparator.comparing(SensorWindPressure::getCreateTime));
// log.info("数据量大小"+list.size());
SensorInfo sensorInfo = iSensorWindPressureService.getDownUpLimit(sid);
String uplimit = sensorInfo.getUpLimit();
String downlimit = sensorInfo.getDownLimit();
long endTime = System.currentTimeMillis();
datashow datashow = new datashow();
List<String> xdata = new LinkedList<>();
List<String> ydata = new LinkedList<>();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
for (SensorWindPressure sensorWindPressure : list) {
xdata.add(sdf.format(sensorWindPressure.getCreateTime()));
ydata.add(sensorWindPressure.getValue());
}
datashow.setXdata(xdata);
datashow.setYdata(ydata);
// System.out.println("代码运行时间：" + (endTime - startTime) + "ms");
datashow.setUplimit(uplimit);
datashow.setDownlimit(downlimit);
return new ResultUtil<datashow>().setData(datashow);
/*List<SensorWindPressure> list=new ArrayList<>(); //原来写的，仅供参考
String startDate = request.getParameter("startDate");
String endDate = request.getParameter("endDate");
String sid = request.getParameter("sid");
String daybetween = request.getParameter("daybetween");
System.out.println(sid+":开始时间加结束时间："+startDate+" "+endDate+" "+daybetween);
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
Date date = sf.parse(startDate);
Calendar c = Calendar.getInstance();
c.setTime(date);
c.add(c.DATE,5);
date=c.getTime();
String startDate1 = sf.format(date);
System.out.println(startDate1);
/* BlockingQueue<Runnable> queue = new ArrayBlockingQueue<>(10);
ThreadPoolExecutor pool = new ThreadPoolExecutor(3, 5, 60, TimeUnit.MICROSECONDS, queue);
for(int i=0;i<pool.getPoolSize();i++){
Callable task = new SmokeTask(startDate,endDate,sid);
Future<List<SensorWindPressure>> f1 = pool.submit(task);
System.out.println(f1.get());
}
pool.shutdown();*/
SensorInfo sensorInfo = iSensorWindPressureService.getDownUpLimit(sid);
String uplimit = sensorInfo.getUpLimit();
String downlimit = sensorInfo.getDownLimit();
System.out.println(sid+":开始时间加结束时间："+startDate+" "+endDate+"上限下限:"+uplimit+","+downlimit);
return new ResultUtil<List<SensorWindPressure>>().setData(list,uplimit,downlimit);*/
// return null;
}
取数据单独提取出来了：
package cn.exrick.xboot.modules.your.bim.controller;
import cn.exrick.xboot.modules.your.bim.entity.SensorWindPressure;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.concurrent.Callable;
public class SmokeTask implements Callable<List<SensorWindPressure>> {
private Connection con = null;
private static String driver = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/shannan";
private static String username = "root";
private static String password = "root";
private static Statement NULL = null;
private String startDate;
private String endDate;
private String sid;
public SmokeTask(String startDate,String endDate,String sid){
this.startDate = startDate;
this.endDate = endDate;
this.sid = sid;
}
// @Autowired
// private ISensorWindPressureService iSensorWindPressureService;
public Statement MysqlOpen() {
try {
Class.forName(driver); //加载驱动类
con = (Connection) DriverManager.getConnection(url, username, password); //连接数据库
if (!con.isClosed())
System.out.println("***数据库成功连接***");
Statement state = (Statement) con.createStatement();
return state;
} catch (ClassNotFoundException e) {
System.out.println("找不到驱动程序类，加载驱动失败");
e.printStackTrace();
} catch (SQLException e) {
System.out.println("数据库连接失败");
e.printStackTrace();
}
return NULL;
}
@Override
public List<SensorWindPressure> call() throws Exception {
// List<SensorWindPressure> list = iSensorWindPressureService.getSensorData(sid,startDate,endDate);
synchronized (SmokeTask.class){
List<SensorWindPressure> list = new ArrayList<>();
ResultSet result = null;
Statement state = MysqlOpen();
try {
String sql = "select create_time,value from sensor_wind_pressure where sensor_id = \""+sid+"\" and create_time between \""+startDate+"\" and \""+endDate+"\" order by create_time ASC";
result = state.executeQuery(sql);
while (result.next()) {
Date date = result.getTimestamp(1);
String value = result.getString(2);
// System.out.println(date + "\t" + value);
SensorWindPressure s=new SensorWindPressure();
s.setCreateTime(date);
s.setValue(value);
list.add(s);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
result.close();
state.close();
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
System.out.println("---------task " + endDate + "执行完毕---------");
return list;
}
}
}
前端展示如下：
不过这里的代码还是有些乱，后面可重构得简洁一些。。。
过程中有遇到Java heap space问题，可参考解释：https://www.cnblogs.com/bolang100/p/6478537.html
获取返回结果优化：
1、使用ExecutorService的invokeAll函数
本方法能解决第一个弊端，即并不需要自己去维护一个存储返回结果的容器。当我们需要获取线程池所有的返回结果时，只需调用invokeAll函数即可。
但是，这种方式需要你自己去维护一个用于存储任务的容器。
// 创建一个线程池
ExecutorService executorService = Executors.newFixedThreadPool(10);
// 创建存储任务的容器
List<Callable<String>> tasks = new ArrayList<Callable<String>>();
// 提交10个任务
for ( int i=0; i<10; i++ ) {
Callable<String> task = new Callable<String>(){
public String call(){
int sleepTime = new Random().nextInt(1000);
Thread.sleep(sleepTime);
return "线程"+i+"睡了"+sleepTime+"秒";
}
};
executorService.submit( task );
// 将task添加进任务队列
tasks.add( task );
}
// 获取10个任务的返回结果
List<Future<String>> results = executorService.invokeAll( tasks );
// 输出结果
for ( int i=0; i<10; i++ ) {
// 获取包含返回结果的future对象
Future<String> future = results.get(i);
// 从future中取出执行结果（若尚未返回结果，则get方法被阻塞，直到结果被返回为止）
String result = future.get();
System.out.println(result);
}
2、 使用CompletionService
CompletionService内部维护了一个阻塞队列，只有执行完成的任务结果才会被放入该队列，这样就确保执行时间较短的任务率先被存入阻塞队列中。
ExecutorService exec = Executors.newFixedThreadPool(10);
final BlockingQueue<Future<Integer>> queue = new LinkedBlockingDeque<Future<Integer>>(
10);
//实例化CompletionService
final CompletionService<Integer> completionService = new ExecutorCompletionService<Integer>(
exec, queue);
// 提交10个任务
for ( int i=0; i<10; i++ ) {
executorService.submit( new Callable<String>(){
public String call(){
int sleepTime = new Random().nextInt(1000);
Thread.sleep(sleepTime);
return "线程"+i+"睡了"+sleepTime+"秒";
}
} );
}
// 输出结果
for ( int i=0; i<10; i++ ) {
// 获取包含返回结果的future对象（若整个阻塞队列中还没有一条线程返回结果，那么调用take将会被阻塞，当然你可以调用poll，不会被阻塞，若没有结果会返回null，poll和take返回正确的结果后会将该结果从队列中删除）
Future<String> future = completionService.take();
// 从future中取出执行结果，这里存储的future已经拥有执行结果，get不会被阻塞
String result = future.get();
System.out.println(result);
}
【参考】https://blog.csdn.net/lijingjingchn/article/details/104491862
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
