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
| package com.rongyichuang.tools;
|
| import org.junit.jupiter.api.Test;
| import org.springframework.beans.factory.annotation.Autowired;
| import org.springframework.boot.test.context.SpringBootTest;
| import org.springframework.jdbc.core.JdbcTemplate;
| import org.springframework.test.context.ActiveProfiles;
|
| import java.util.List;
| import java.util.Map;
|
| @SpringBootTest
| @ActiveProfiles("test")
| public class RatingTemplateInserter {
|
| @Autowired
| private JdbcTemplate jdbcTemplate;
|
| @Test
| public void insertRatingTemplate() {
| System.out.println("开始插入评分模板数据...");
|
| try {
| // 1. 插入评分模板
| String insertSchemeSql = """
| INSERT INTO t_rating_scheme (id, name, description, state, create_time, create_user_id, update_time, update_user_id, version)
| VALUES (1, '编程大赛评分标准', '用于编程比赛的综合评分标准，包含代码质量、功能完整性、用户体验、创新性和项目展示等维度', 1, NOW(), 1, NOW(), 1, 0)
| ON DUPLICATE KEY UPDATE
| name = VALUES(name),
| description = VALUES(description),
| update_time = NOW()
| """;
|
| int schemeResult = jdbcTemplate.update(insertSchemeSql);
| System.out.println("插入评分模板结果: " + schemeResult);
|
| // 2. 插入评分项目
| String[] insertItemSqls = {
| "INSERT INTO t_rating_item (id, scheme_id, name, max_score, order_no, state, create_time, create_user_id, update_time, update_user_id, version) VALUES (1, 1, '代码质量', 30, 1, 1, NOW(), 1, NOW(), 1, 0) ON DUPLICATE KEY UPDATE name = VALUES(name), max_score = VALUES(max_score), order_no = VALUES(order_no), state = VALUES(state), update_time = NOW()",
| "INSERT INTO t_rating_item (id, scheme_id, name, max_score, order_no, state, create_time, create_user_id, update_time, update_user_id, version) VALUES (2, 1, '功能完整性', 25, 2, 1, NOW(), 1, NOW(), 1, 0) ON DUPLICATE KEY UPDATE name = VALUES(name), max_score = VALUES(max_score), order_no = VALUES(order_no), state = VALUES(state), update_time = NOW()",
| "INSERT INTO t_rating_item (id, scheme_id, name, max_score, order_no, state, create_time, create_user_id, update_time, update_user_id, version) VALUES (3, 1, '用户体验', 20, 3, 1, NOW(), 1, NOW(), 1, 0) ON DUPLICATE KEY UPDATE name = VALUES(name), max_score = VALUES(max_score), order_no = VALUES(order_no), state = VALUES(state), update_time = NOW()",
| "INSERT INTO t_rating_item (id, scheme_id, name, max_score, order_no, state, create_time, create_user_id, update_time, update_user_id, version) VALUES (4, 1, '创新性', 15, 4, 1, NOW(), 1, NOW(), 1, 0) ON DUPLICATE KEY UPDATE name = VALUES(name), max_score = VALUES(max_score), order_no = VALUES(order_no), state = VALUES(state), update_time = NOW()",
| "INSERT INTO t_rating_item (id, scheme_id, name, max_score, order_no, state, create_time, create_user_id, update_time, update_user_id, version) VALUES (5, 1, '项目展示', 10, 5, 1, NOW(), 1, NOW(), 1, 0) ON DUPLICATE KEY UPDATE name = VALUES(name), max_score = VALUES(max_score), order_no = VALUES(order_no), state = VALUES(state), update_time = NOW()"
| };
|
| for (int i = 0; i < insertItemSqls.length; i++) {
| try {
| System.out.println("执行SQL: " + insertItemSqls[i]);
| int itemResult = jdbcTemplate.update(insertItemSqls[i]);
| System.out.println("插入评分项目 " + (i + 1) + " 结果: " + itemResult);
| } catch (Exception e) {
| System.err.println("插入评分项目 " + (i + 1) + " 失败: " + e.getMessage());
| e.printStackTrace();
| }
| }
|
| // 3. 验证插入结果
| System.out.println("\n=== 验证插入结果 ===");
|
| // 查询评分模板
| String querySchemeSql = "SELECT * FROM t_rating_scheme WHERE id = 1";
| List<Map<String, Object>> schemeResults = jdbcTemplate.queryForList(querySchemeSql);
| System.out.println("评分模板:");
| for (Map<String, Object> row : schemeResults) {
| System.out.println(row);
| }
|
| // 查询评分项目
| String queryItemSql = "SELECT * FROM t_rating_item WHERE scheme_id = 1 ORDER BY order_no";
| List<Map<String, Object>> itemResults = jdbcTemplate.queryForList(queryItemSql);
| System.out.println("\n评分项目:");
| for (Map<String, Object> row : itemResults) {
| System.out.println(row);
| }
|
| // 计算总分
| String totalScoreSql = """
| SELECT
| rs.name as scheme_name,
| SUM(ri.max_score) as total_max_score
| FROM t_rating_scheme rs
| LEFT JOIN t_rating_item ri ON rs.id = ri.scheme_id
| WHERE rs.id = 1
| GROUP BY rs.id, rs.name
| """;
| List<Map<String, Object>> totalResults = jdbcTemplate.queryForList(totalScoreSql);
| System.out.println("\n总分验证:");
| for (Map<String, Object> row : totalResults) {
| System.out.println(row);
| }
|
| System.out.println("\n✅ 评分模板数据插入完成！");
|
| } catch (Exception e) {
| System.err.println("❌ 插入评分模板数据失败: " + e.getMessage());
| e.printStackTrace();
| }
| }
| }
|
|
