ScheduleRuleServiceImpl.java
5.45 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
package com.bsth.service.schedule.rules;
import com.bsth.entity.schedule.SchedulePlan;
import com.bsth.entity.schedule.SchedulePlanInfo;
import com.bsth.entity.schedule.temp.SchedulePlanRuleResult;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* Created by xu on 17/4/19.
*/
@Service
public class ScheduleRuleServiceImpl implements ScheduleRuleService {
/** 日志记录器 */
private static final Logger logger = LoggerFactory.getLogger(ScheduleRuleServiceImpl.class);
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public List<SchedulePlanRuleResult> findLastByXl(String xlid, Date from) {
String sql = "select * from bsth_c_s_sp_rule_rst a " +
"where exists (select 1 from " +
"(select t.rule_id as rid, max(t.schedule_date) as sd from bsth_c_s_sp_rule_rst t " +
"where t.xl_id = ? and t.schedule_date < ? " +
"group by t.rule_id) a2 " +
"where a.rule_id = rid and a.schedule_date = sd) ";
return jdbcTemplate.query(sql, new Object[]{xlid, from}, new RowMapper<SchedulePlanRuleResult>() {
@Override
public SchedulePlanRuleResult mapRow(ResultSet rs, int i) throws SQLException {
SchedulePlanRuleResult obj = new SchedulePlanRuleResult();
obj.setRuleId(rs.getString("rule_id"));
obj.setScheduleDate(rs.getDate("schedule_date"));
obj.setGidindex(rs.getString("gidindex"));
obj.setEcindex(rs.getString("ecindex"));
// 其他字段没用
return obj;
}
});
}
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED)
public void deleteSchedulePlanAll(Long id) {
String sql1 = "delete from bsth_c_s_sp where id = ?";
String sql2 = "delete from bsth_c_s_sp_info where schedule_plan = ?";
jdbcTemplate.update(sql2, id);
jdbcTemplate.update(sql1, id);
}
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED)
public void deelteSchedulePlanInfo(Integer xlid, Date datefrom, Date dateto) {
String sql = "delete from bsth_c_s_sp_info where xl = ? and schedule_date >= ? and schedule_date <= ?";
jdbcTemplate.update(sql, xlid, datefrom, dateto);
}
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED)
@Override
public void generateSchedulePlan(final SchedulePlan schedulePlan, final List<SchedulePlanInfo> schedulePlanInfos) {
// 1、插入排班计划主表，并获取主键id
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement ps = connection.prepareStatement(
SchedulePlan.generateInsertSql(), new String[] {"id"});
schedulePlan.preparedStatementSet(ps);
return ps;
}
}, keyHolder);
SchedulePlan master = new SchedulePlan();
master.setId(keyHolder.getKey().longValue());
for (SchedulePlanInfo schedulePlanInfo : schedulePlanInfos) {
schedulePlanInfo.setSchedulePlan(master);
}
// 2、批量插入排班计划从表
List<List<SchedulePlanInfo>> lists = new ArrayList<>();
int batchSize = 2000;
int loopCount = schedulePlanInfos.size() / batchSize;
int otherCount = schedulePlanInfos.size() % batchSize;
for (int i = 0; i < loopCount; i++) {
lists.add(schedulePlanInfos.subList(i * batchSize, i * batchSize + batchSize));
}
if (otherCount > 0) {
lists.add(schedulePlanInfos.subList(loopCount * batchSize, loopCount * batchSize + otherCount));
}
for (final List<SchedulePlanInfo> list : lists) {
jdbcTemplate.batchUpdate(SchedulePlanInfo.generateInsertSql(), new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
SchedulePlanInfo schedulePlanInfo = list.get(i);
schedulePlanInfo.preparedStatementSet(preparedStatement);
}
@Override
public int getBatchSize() {
return list.size();
}
});
}
}
}