我们现在需要 **调整输出结构**：将每个季度、每个责任单位的 `
type1` 到 `
type4` 的数量合并到 **同一条记录中**，而不是每种类型单独一行。
---
### ✅ 新目标：输出格式
希望得到一个 `List<QuarterDuty
TypeSummary>`，每条数据表示：
```java
class QuarterDuty
TypeSummary {
private String year;
private String quarter; // Q1/Q
2/Q3/Q
4
private String dutyUnit; // 责任单位
private Long
type1Count;
private Long
type2Count;
private Long
type3Count;
private Long
type4Count;
}
```
例如：
```json
{
"year": "
20
24",
"quarter": "Q1",
"dutyUnit": "单位A",
"
type1Count": 5,
"
type2Count": 3,
"
type3Count": 0,
"
type4Count":
2
}
```
这样更便于展示为表格或插入宽表。
---
### ✅ 修改后的 Java 代码
```java
import java.util.*;
import java.util.stream.Collectors;
// 结果类：每个责任单位在某个季度的四类统计汇总
class QuarterDuty
TypeSummary {
private String year;
private String quarter;
private String dutyUnit;
private Long
type1Count;
private Long
type2Count;
private Long
type3Count;
private Long
type4Count;
public QuarterDuty
TypeSummary(String year, String quarter, String dutyUnit) {
this.year = year;
this.quarter = quarter;
this.dutyUnit = dutyUnit;
this.
type1Count = 0L;
this.
type2Count = 0L;
this.
type3Count = 0L;
this.
type4Count = 0L;
}
// Getter
和 Setter
public String getYear() { return year; }
public void setYear(String year) { this.year = year; }
public String getQuarter() { return quarter; }
public void setQuarter(String quarter) { this.quarter = quarter; }
public String getDutyUnit() { return dutyUnit; }
public void setDutyUnit(String dutyUnit) { this.dutyUnit = dutyUnit; }
public Long get
Type1Count() { return
type1Count; }
public void set
Type1Count(Long
type1Count) { this.
type1Count =
type1Count; }
public Long get
Type2Count() { return
type2Count; }
public void set
Type2Count(Long
type2Count) { this.
type2Count =
type2Count; }
public Long get
Type3Count() { return
type3Count; }
public void set
Type3Count(Long
type3Count) { this.
type3Count =
type3Count; }
public Long get
Type4Count() { return
type4Count; }
public void set
Type4Count(Long
type4Count) { this.
type4Count =
type4Count; }
}
// 统计处理器
class StatSummaryCalculator {
public static List<QuarterDuty
TypeSummary> calculate(List<PersonFourthPatternDto> dataList) {
String currentYear = String.valueOf(Calendar.getInstance().get(Calendar.YEAR));
return dataList.stream()
.filter(dto -> currentYear.equals(dto.getYear()))
.map(dto -> {
try {
int m = Integer.parseInt(dto.getMonth().trim());
if (m >= 1 && m <= 1
2) return dto;
} catch (Exception e) {
System.err.println("无效月份: " + dto.getMonth());
}
return null;
})
.filter(Objects::nonNull)
// 按 quarter
和 dutyUnit 分组
.collect(Collectors.groupingBy(
dto -> {
int month = Integer.parseInt(dto.getMonth());
return "Q" + ((month - 1) / 3 + 1);
},
Collectors.groupingBy(PersonFourthPatternDto::getDutyUnit)
))
.entrySet()
.stream()
.flatMap(quarterEntry ->
quarterEntry.getValue().entrySet().stream()
.map(dutyUnitEntry -> {
QuarterDuty
TypeSummary summary = new QuarterDuty
TypeSummary(
currentYear,
quarterEntry.getKey(),
dutyUnitEntry.getKey()
);
// 初始化 counts
long[] counts = new long[5]; // index 1~
4 对应
type1~
4
dutyUnitEntry.getValue().forEach(dto -> {
switch (dto.get
Type()) {
case "
type1": counts[1]++; break;
case "
type2": counts[
2]++; break;
case "
type3": counts[3]++; break;
case "
type4": counts[
4]++; break;
default: break; // 忽略非法类型
}
});
summary.set
Type1Count(counts[1]);
summary.set
Type2Count(counts[
2]);
summary.set
Type3Count(counts[3]);
summary.set
Type4Count(counts[
4]);
return summary;
})
)
.collect(Collectors.toList());
}
}
```
---
### ✅ 输出示例
输入数据：
```java
List<PersonFourthPatternDto> data = Arrays.asList(
new PersonFourthPatternDto("
20
24", "1", "单位A", "
type1"),
new PersonFourthPatternDto("
20
24", "
2", "单位A", "
type1"),
new PersonFourthPatternDto("
20
24", "3", "单位A", "
type2"),
new PersonFourthPatternDto("
20
24", "
4", "单位B", "
type3"),
new PersonFourthPatternDto("
20
24", "5", "单位B", "
type4"),
new PersonFourthPatternDto("
20
24", "6", "单位B", "
type4")
);
```
输出结果：
```json
[
{
"year": "
20
24",
"quarter": "Q1",
"dutyUnit": "单位A",
"
type1Count":
2,
"
type2Count": 1,
"
type3Count": 0,
"
type4Count": 0
},
{
"year": "
20
24",
"quarter": "Q
2",
"dutyUnit": "单位B",
"
type1Count": 0,
"
type2Count": 0,
"
type3Count": 1,
"
type4Count":
2
}
]
```
---
### ✅ 对应
数据库表设计（宽表）
```sql
CREATE TABLE person_fourth_summary (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
year VARCHAR(
4) NOT NULL,
quarter VARCHAR(
2) NOT NULL,
duty_unit VARCHAR(100) NOT NULL,
type1_count BIGINT DEFAULT 0,
type2_count BIGINT DEFAULT 0,
type3_count BIGINT DEFAULT 0,
type4_count BIGINT DEFAULT 0,
UNIQUE KEY uk_year_quarter_duty (year, quarter, duty_unit)
);
```
---
### ✅ MyBatis 插入语句（批量 upsert）
```xml
<insert id="batchUpsertSummary" parameter
Type="java.util.List">
INSERT INTO person_fourth_summary (
year, quarter, duty_unit,
type1_count,
type2_count,
type3_count,
type4_count
)
VALUES
<foreach collection="list" item="item" separator=",">
(
#{item.year},
#{item.quarter},
#{item.dutyUnit},
#{item.
type1Count},
#{item.
type2Count},
#{item.
type3Count},
#{item.
type4Count}
)
</foreach>
ON DUPLICATE KEY UPDATE
type1_count = VALUES(
type1_count),
type2_count = VALUES(
type2_count),
type3_count = VALUES(
type3_count),
type4_count = VALUES(
type4_count)
</insert>
```
---
### ✅ Mapper 接口
```java
@Mapper
public interface SummaryMapper {
void batchUpsertSummary(@Param("list") List<QuarterDuty
TypeSummary> summaries);
}
```
---
本文详细介绍了IBM DB2的JDBC驱动程序，包括新的通用驱动com.ibm.db2.jcc.DB2Driver及其所在文件db2jcc.jar和sqlj.zip。此外还对比了Legacy驱动与通用驱动之间的主要区别，以及类型2与类型4驱动的不同之处。
扫一扫
6052
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
