项目结构：
domain
--entities
----school.java
--repositories
----school.java
infrastructure
--database
-----duoraclehelper.java
--model
----school.java
--repositories
----school.java
application
--server
-----school.java
presentation
--controllers
----school.java
--views
----school.java
main.java
区别就是数据库处理，其他架构层是一样
/**
* encoding: utf-8
* 版权所有 2025 ©涂聚文有限公司 ®
* 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
* 描述：
* Author : geovindu,Geovin Du 涂聚文.
* IDE : IntelliJ IDEA 2024.3.6 Java 17
* # database : Oracle21c,MySQL 9.0,SQL Server 2019,PostgreSQL 17.1 Neo4j
* # OS : window10
* Datetime : 2025 - 2025/7/14 - 20:01
* User : geovindu
* Product : IntelliJ IDEA
* Project : sqlserverDDDDemo
* File : DuSqlServerHelper.java
* explain : 学习 类
**/
package infrastructure.database;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
/**
* 数据库操作
*/
public class DuSqlServerHelper {
private static final String CONFIG_FILE = "sqlservercon.json";
/***
*
* @return
* @throws SQLException
* @throws IOException
*/
public static Connection getConnection() throws SQLException, IOException {
Properties props = loadConfig();
String url = String.format("jdbc:sqlserver://%s:%s;databaseName=%s",
props.getProperty("host"),
props.getProperty("port"),
props.getProperty("databaseName"));
return DriverManager.getConnection(url, props.getProperty("user"), props.getProperty("password"));
}
/***
*
* @return
* @throws IOException
*/
private static Properties loadConfig() throws IOException {
Properties props = new Properties();
try (BufferedReader reader = new BufferedReader(new FileReader(CONFIG_FILE))) {
// 解析JSON配置文件
String line;
while ((line = reader.readLine()) != null) {
// 简化处理，实际应用应使用JSON解析库
String[] parts = line.trim().split(":");
if (parts.length == 2) {
String key = parts[0].replaceAll("\"", "").trim();
String value = parts[1].replaceAll("\"", "").replaceAll(",", "").trim();
props.setProperty(key, value);
}
}
}
return props;
}
/**
* 创建并返回存储过程调用的 PreparedStatement
*/
public static CallableStatement getStoredProcedure(Connection conn, String procedureName) throws SQLException {
String callSyntax = "{call " + procedureName + " (?)}"; // 示例语法，根据实际参数调整
return conn.prepareCall(callSyntax);
}
}/**
* encoding: utf-8
* 版权所有 2025 ©涂聚文有限公司 ®
* 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
* 描述：
* Author : geovindu,Geovin Du 涂聚文.
* IDE : IntelliJ IDEA 2024.3.6 Java 17
* # database : Oracle21c,MySQL 9.0,SQL Server 2019,PostgreSQL 17.1 Neo4j
* # OS : window10
* Datetime : 2025 - 2025/7/13 - 18:03
* User : geovindu
* Product : IntelliJ IDEA
* Project : oracleDDDDemo
* File : SchoolRepositoryImpl.java
* explain : 学习 类
**/
package infrastructure.repositories;
import domain.entities.School;
import domain.entities.QueryParams;
import domain.repositories.SchoolRepository;
import infrastructure.database.DuSqlServerHelper;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
/**
*
*/
public class SchoolRepositoryImpl implements SchoolRepository {
/*
@Override
public List<School> findByParamsProc(QueryParams params) {
List<School> schools = new ArrayList<>();
int totalCount = 0;
try (Connection conn = DuSqlServerHelper.getConnection();
CallableStatement cstmt = conn.prepareCall("{call sp_GetSchoolsByPage(?, ?, ?)}")) {
cstmt.setInt(1, params.getPageNumber());
cstmt.setInt(2, params.getPageSize());
if (params.getSearchTerm() != null && !params.getSearchTerm().isEmpty()) {
cstmt.setString(3, params.getSearchTerm());
} else {
cstmt.setNull(3, Types.NVARCHAR);
}
// 执行存储过程
boolean hasResult = cstmt.execute();
// 处理第一个结果集（总记录数）
if (hasResult) {
try (ResultSet rs = cstmt.getResultSet()) {
if (rs.next()) {
totalCount = rs.getInt("TotalCount");
}
}
// 处理第二个结果集（分页数据）
hasResult = cstmt.getMoreResults();
if (hasResult) {
try (ResultSet rs = cstmt.getResultSet()) {
while (rs.next()) {
schools.add(mapToDomain(rs));
}
}
}
}
} catch (SQLException | IOException e) {
throw new RuntimeException("按条件查询学校失败", e);
}
return schools;
}
@Override
public int countByParams(QueryParams params) {
// 存储过程已经在 findByParams 中返回了总记录数，这里可以简化实现
// 实际应用中可以根据需要单独调用存储过程获取计数
return 0;
}
@Override
public void save(School school) {
if (findById(school.getSchoolId()) == null) {
insertSchool(school);
} else {
updateSchool(school);
}
}
private void insertSchoolProc(School school) {
try (Connection conn = DuSqlServerHelper.getConnection();
CallableStatement cstmt = conn.prepareCall("{call sp_AddSchool(?, ?, ?)}")) {
cstmt.setString(1, school.getSchoolId());
cstmt.setString(2, school.getSchoolName());
cstmt.setString(3, school.getSchoolTelNo());
cstmt.execute();
} catch (SQLException | IOException e) {
throw new RuntimeException("添加学校失败", e);
}
}
private void updateSchoolProc(School school) {
try (Connection conn = DuSqlServerHelper.getConnection();
CallableStatement cstmt = conn.prepareCall("{call sp_UpdateSchool(?, ?, ?)}")) {
cstmt.setString(1, school.getSchoolId());
cstmt.setString(2, school.getSchoolName());
cstmt.setString(3, school.getSchoolTelNo());
cstmt.execute();
} catch (SQLException | IOException e) {
throw new RuntimeException("更新学校失败", e);
}
}
@Override
public void deleteProc(String schoolId) {
try (Connection conn = DuSqlServerHelper.getConnection();
CallableStatement cstmt = conn.prepareCall("{call sp_DeleteSchool(?)}")) {
cstmt.setString(1, schoolId);
cstmt.execute();
} catch (SQLException | IOException e) {
throw new RuntimeException("删除学校失败", e);
}
}
*/
@Override
public List<School> findAll(int page, int pageSize) {
List<School> schools = new ArrayList<>();
// SQL Server 2012+ 分页语法
String sql = "SELECT * FROM School " +
"ORDER BY SchoolId " +
"OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
try (Connection conn = DuSqlServerHelper.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, (page - 1) * pageSize);
pstmt.setInt(2, pageSize);
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
schools.add(mapToDomain(rs));
}
}
} catch (SQLException | IOException e) {
throw new RuntimeException("查询学校列表失败", e);
}
return schools;
}
@Override
public List<School> findByParams(QueryParams params) {
List<School> schools = new ArrayList<>();
StringBuilder sql = new StringBuilder(
"SELECT * FROM School WHERE 1=1 "
);
if (params.getSearchTerm() != null && !params.getSearchTerm().isEmpty()) {
sql.append("AND (SchoolId LIKE ? OR SchoolName LIKE ?) ");
}
sql.append("ORDER BY SchoolId OFFSET ? ROWS FETCH NEXT ? ROWS ONLY");
try (Connection conn = DuSqlServerHelper.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {
int paramIndex = 1;
if (params.getSearchTerm() != null && !params.getSearchTerm().isEmpty()) {
String searchTerm = "%" + params.getSearchTerm() + "%";
pstmt.setString(paramIndex++, searchTerm);
pstmt.setString(paramIndex++, searchTerm);
}
pstmt.setInt(paramIndex++, params.getOffset());
pstmt.setInt(paramIndex, params.getPageSize());
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
schools.add(mapToDomain(rs));
}
}
} catch (SQLException | IOException e) {
throw new RuntimeException("按条件查询学校失败", e);
}
return schools;
}
@Override
public int countAll() {
String sql = "SELECT COUNT(*) FROM School";
try (Connection conn = DuSqlServerHelper.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
if (rs.next()) {
return rs.getInt(1);
}
} catch (SQLException | IOException e) {
throw new RuntimeException("统计学校数量失败", e);
}
return 0;
}
@Override
public int countByParams(QueryParams params) {
StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM School WHERE 1=1 ");
// 构建查询条件
if (params.getSearchTerm() != null && !params.getSearchTerm().isEmpty()) {
sql.append("AND (SchoolId LIKE ? OR SchoolName LIKE ?)");
}
try (Connection conn = DuSqlServerHelper.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {
int paramIndex = 1;
// 设置查询参数
if (params.getSearchTerm() != null && !params.getSearchTerm().isEmpty()) {
String searchTerm = "%" + params.getSearchTerm() + "%";
pstmt.setString(paramIndex++, searchTerm);
pstmt.setString(paramIndex, searchTerm);
}
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
return rs.getInt(1);
}
}
} catch (SQLException | IOException e) {
throw new RuntimeException("统计符合条件的学校数量失败", e);
}
return 0;
}
// 其他方法实现...
@Override
public School findById(String schoolId) {
String sql = "SELECT * FROM School WHERE SchoolId = ?";
try (Connection conn = DuSqlServerHelper.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, schoolId);
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
return mapToDomain(rs);
}
}
} catch (SQLException | IOException e) {
throw new RuntimeException("查找学校失败", e);
}
return null;
}
@Override
public void save(School school) {
// 检查是插入还是更新
if (findById(school.getSchoolId()) == null) {
insertSchool(school);
} else {
updateSchool(school);
}
}
private void insertSchool(School school) {
String sql = "INSERT INTO School (SchoolId, SchoolName, SchoolTelNo) VALUES (?, ?, ?)";
try (Connection conn = DuSqlServerHelper.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, school.getSchoolId());
pstmt.setString(2, school.getSchoolName());
pstmt.setString(3, school.getSchoolTelNo());
pstmt.executeUpdate();
} catch (SQLException | IOException e) {
throw new RuntimeException("添加学校失败", e);
}
}
private void updateSchool(School school) {
String sql = "UPDATE School SET SchoolName = ?, SchoolTelNo = ? WHERE SchoolId = ?";
try (Connection conn = DuSqlServerHelper.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, school.getSchoolName());
pstmt.setString(2, school.getSchoolTelNo());
pstmt.setString(3, school.getSchoolId());
pstmt.executeUpdate();
} catch (SQLException | IOException e) {
throw new RuntimeException("更新学校失败", e);
}
}
@Override
public void delete(String schoolId) {
String sql = "DELETE FROM School WHERE SchoolId = ?";
try (Connection conn = DuSqlServerHelper.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, schoolId);
pstmt.executeUpdate();
} catch (SQLException | IOException e) {
throw new RuntimeException("删除学校失败", e);
}
}
private School mapToDomain(ResultSet rs) throws SQLException {
return new School(
rs.getString("SchoolId"),
rs.getString("SchoolName"),
rs.getString("SchoolTelNo")
);
}
}其他都一样的。细节上需要完善。并需要考虑工厂模式切换使用数据库
输出：
