java: framework from BLL、DAL、IDAL、MODEL、Factory using oracle 21c

sql:

?
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
-- 创建 School 表
CREATE TABLE School (
    SchoolId CHAR(5) NOT NULL,
    SchoolName NVARCHAR2(500) NOT NULL,
    SchoolTelNo VARCHAR2(8) NULL,
    PRIMARY KEY (SchoolId)
);
 
CREATE OR REPLACE PROCEDURE addschool(
    p_school_id IN CHAR,
    p_school_name IN NVARCHAR2,
    p_school_tel_no IN VARCHAR2
) AS
BEGIN
    INSERT INTO School (SchoolId, SchoolName, SchoolTelNo)
    VALUES (p_school_id, p_school_name, p_school_tel_no);
    COMMIT;
END;
 
CREATE OR REPLACE PROCEDURE deleteschool(
    p_school_id IN CHAR
) AS
BEGIN
    DELETE FROM School WHERE SchoolId = p_school_id;
    COMMIT;
END;
 
CREATE OR REPLACE PROCEDURE updateschool(
    p_school_id IN CHAR,
    p_school_name IN NVARCHAR2,
    p_school_tel_no IN VARCHAR2
) AS
BEGIN
    UPDATE School
    SET SchoolName = p_school_name, SchoolTelNo = p_school_tel_no
    WHERE SchoolId = p_school_id;
    COMMIT;
END;
 
CREATE OR REPLACE PROCEDURE getschoolbyid(
    p_school_id IN CHAR,
    p_school_name OUT NVARCHAR2,
    p_school_tel_no OUT VARCHAR2
) AS
BEGIN
    SELECT SchoolName, SchoolTelNo
    INTO p_school_name, p_school_tel_no
    FROM School
    WHERE SchoolId = p_school_id;
END;
 
CREATE OR REPLACE PROCEDURE getallschools(
    p_cursor OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_cursor FOR SELECT * FROM School;
END;

　　

?
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
/**
 * encoding: utf-8
 * 版权所有 2025 涂聚文有限公司
 * 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
 * 描述：
 * # Author    : geovindu,Geovin Du 涂聚文.
 * # IDE       : IntelliJ IDEA 2023.1 Java 21
 * # database  : Oracle21c,MySQL 9.0,SQL Server 2019,PostgreSQL 17.1
 * # OS        : window10
 * # Datetime  : 2025 - 2025/2/9 - 16:30
 * # User      : geovindu
 * # Product   : IntelliJ IDEA
 * # Project   : oracledemo
 * # File      : Model/School.java  类
 * # explain   : 学习
 **/
 
package Geovin.Model;
 
/**
 * 学校表 实体
 */
public class School {
 
    /**
     *
     */
    private String schoolId;
    /**
     *
     */
    private String schoolName;
    /**
     *
     */
    private String schoolTelNo;
 
    /**
     * 构造方法
     */
    public School() {
    }
 
    /**
     *
     * @param schoolId
     * @param schoolName
     * @param schoolTelNo
     */
    public School(String schoolId, String schoolName, String schoolTelNo) {
        this.schoolId = schoolId;
        this.schoolName = schoolName;
        this.schoolTelNo = schoolTelNo;
    }
    // Getter 和 Setter 方法
 
    /**
     *
     * @return
     */
    public String getSchoolId() {
        return schoolId;
    }
 
    /**
     *
     * @param schoolId
     */
    public void setSchoolId(String schoolId) {
        this.schoolId = schoolId;
    }
 
    /**
     *
     * @return
     */
    public String getSchoolName() {
        return schoolName;
    }
 
    /**
     *
     * @param schoolName
     */
    public void setSchoolName(String schoolName) {
        this.schoolName = schoolName;
    }
 
    /**
     *
     * @return
     */
    public String getSchoolTelNo() {
        return schoolTelNo;
    }
 
    /**
     *
     * @param schoolTelNo
     */
    public void setSchoolTelNo(String schoolTelNo) {
        this.schoolTelNo = schoolTelNo;
    }
 
    /**
     *
     * @return
     */
    @Override
    public String toString() {
        return "School [schoolId=" + schoolId + ", schoolName=" + schoolName + ", schoolTelNo=" + schoolTelNo + "]";
    }
}

　　

?
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
/**
 * encoding: utf-8
 * 版权所有 2025 涂聚文有限公司
 * 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
 * 描述：
 * # Author    : geovindu,Geovin Du 涂聚文.
 * # IDE       : IntelliJ IDEA 2023.1 Java 21
 * # database  : Oracle21c,MySQL 9.0,SQL Server 2019,PostgreSQL 17.1
 * # OS        : window10
 * # Datetime  : 2025 - 2025/2/9 - 16:33
 * # User      : geovindu
 * # Product   : IntelliJ IDEA
 * # Project   : oracledemo
 * # File      : Interface/ISchool.java  类
 * # explain   : 学习
 **/
 
package Geovin.Interface;
import java.util.List;
import Geovin.Model.School;
 
/**
 * 接口
 */
public interface  ISchool {
 
    /**
     * 添加学校
     * @param school
     */
    void addSchool(School school);
 
    /**
     * 删除学校
     * @param schoolId
     */
    void deleteSchool(String schoolId);
 
    /**
     * 更新学校
     * @param school
     */
    void updateSchool(School school);
 
    /**
     * 查询学校
     * @param schoolId
     * @return
     */
    School getSchoolById(String schoolId);
 
    /**
     * 查询所有学校
     * @return
     */
    List<School> getAllSchools();
 
    /**
     * 储存过程 添加
     * @param school
     * @return
     */
    boolean addProc(School school);
 
    /**
     * 储存过程
     * @param schoolId
     * @return
     */
    boolean deleteProc(String schoolId);
 
    /**
     * 储存过程
     * @param school
     * @return
     */
    boolean updateProc(School school);
 
    /**
     * 储存过程
     * @param schoolId
     * @return
     */
    School getProcById(String schoolId);
 
    /**
     * 储存过程
     * @return
     */
    List<School> getAllProc();
 
 
 
}

　　

?
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
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
/**
 * encoding: utf-8
 * 版权所有 2025 涂聚文有限公司
 * 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
 * 描述：
 * # Author    : geovindu,Geovin Du 涂聚文.
 * # IDE       : IntelliJ IDEA 2023.1 Java 21
 * # database  : Oracle21c,MySQL 9.0,SQL Server 2019,PostgreSQL 17.1
 * # OS        : window10
 * # Datetime  : 2025 - 2025/2/9 - 16:36
 * # User      : geovindu
 * # Product   : IntelliJ IDEA
 * # Project   : oracledemo
 * # File      : DAL/SchoolDAL.java  类
 * # explain   : 学习
 **/
 
package Geovin.DAL;
import java.sql.*;
import java.sql.Driver.*;
import oracle.jdbc.*;
import java.util.ArrayList;
import java.util.List;
import Geovin.Model.School;
import Geovin.Interface.ISchool;
import Geovin.UtilitieDB.SqlHelper;
 
/**
 * 数据处理
 */
public class SchoolDAL implements ISchool {
 
 
    /**
     * 构造方法
     */
    public SchoolDAL() {
    }
 
    /**
     *
     * @param school
     */
    @Override
    public void addSchool(School school) {
        String sql = "INSERT INTO School (SchoolId, SchoolName, SchoolTelNo) VALUES (?, ?, ?)";
        try (PreparedStatement pstmt = SqlHelper.getConnect().prepareStatement(sql)) {
            pstmt.setString(1, school.getSchoolId());
            pstmt.setString(2, school.getSchoolName());
            pstmt.setString(3, school.getSchoolTelNo());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
 
    /**
     *
     * @param schoolId
     */
    @Override
    public void deleteSchool(String schoolId) {
        String sql = "DELETE FROM School WHERE SchoolId = ?";
        try (PreparedStatement pstmt = SqlHelper.getConnect().prepareStatement(sql)) {
            pstmt.setString(1, schoolId);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
 
    /**
     *
     * @param school
     */
    @Override
    public void updateSchool(School school) {
        String sql = "UPDATE School SET SchoolName = ?, SchoolTelNo = ? WHERE SchoolId = ?";
        try (PreparedStatement pstmt = SqlHelper.getConnect().prepareStatement(sql)) {
            pstmt.setString(1, school.getSchoolName());
            pstmt.setString(2, school.getSchoolTelNo());
            pstmt.setString(3, school.getSchoolId());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
 
    /**
     *
     * @param schoolId
     * @return
     */
    @Override
    public School getSchoolById(String schoolId) {
        String sql = "SELECT * FROM School WHERE SchoolId = ?";
        try (PreparedStatement pstmt = SqlHelper.getConnect().prepareStatement(sql)) {
            pstmt.setString(1, schoolId);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return new School(
                        rs.getString("SchoolId"),
                        rs.getString("SchoolName"),
                        rs.getString("SchoolTelNo")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
 
    /**
     *
     * @return
     */
    @Override
    public List<School> getAllSchools() {
        List<School> schools = new ArrayList<>();
        String sql = "SELECT * FROM School";
        try (Statement stmt =SqlHelper.getConnect().createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                schools.add(new School(
                        rs.getString("SchoolId"),
                        rs.getString("SchoolName"),
                        rs.getString("SchoolTelNo")
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return schools;
    }
 
    /**
     * 储存过程 添加
     * @param school
     * @return
     */
    public boolean addProc(School school) {
        String sql = "{call ADDSCHOOL(?,?,?)}";
        try (
             CallableStatement cstmt = SqlHelper.getConnect().prepareCall(sql)) {
            cstmt.setString(1, school.getSchoolId());
            cstmt.setString(2, school.getSchoolName());
            cstmt.setString(3, school.getSchoolTelNo());
            cstmt.execute();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
 
    /**
     * 储存过程
     * @param schoolId
     * @return
     */
    public boolean deleteProc(String schoolId) {
        String sql = "{call DELETESCHOOL(?)}";
        try (
             CallableStatement cstmt = SqlHelper.getConnect().prepareCall(sql)) {
            cstmt.setString(1, schoolId);
            cstmt.execute();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
 
    /**
     * 储存过程
     * @param school
     * @return
     */
    public boolean updateProc(School school) {
        String sql = "{call UPDATESCHOOL(?,?,?)}";
        try (
             CallableStatement cstmt = SqlHelper.getConnect().prepareCall(sql)) {
            cstmt.setString(1, school.getSchoolId());
            cstmt.setString(2, school.getSchoolName());
            cstmt.setString(3, school.getSchoolTelNo());
            cstmt.execute();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
 
    /**
     * 储存过程
     * @param schoolId
     * @return
     */
    public School getProcById(String schoolId) {
        String sql = "{call GETSCHOOLBYID(?,?)}";
        try (
             CallableStatement cstmt = SqlHelper.getConnect().prepareCall(sql)) {
            cstmt.setString(1, schoolId);
            cstmt.registerOutParameter(2, OracleTypes.CURSOR);
            cstmt.execute();
            ResultSet rs = (ResultSet) cstmt.getObject(2);
            if (rs.next()) {
                String id = rs.getString("SchoolId");
                String name = rs.getString("SchoolName");
                String telNo = rs.getString("SchoolTelNo");
                return new School(id, name, telNo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
 
    /**
     * 储存过程
     * @return
     */
    public List<School> getAllProc() {
        List<School> schoolList = new ArrayList<>();
        String sql = "{call GETALLSCHOOLS(?)}";
        try (
             CallableStatement cstmt = SqlHelper.getConnect().prepareCall(sql)) {
            cstmt.registerOutParameter(1, OracleTypes.CURSOR);
            cstmt.execute();
            ResultSet rs = (ResultSet) cstmt.getObject(1);
            while (rs.next()) {
                String id = rs.getString("SchoolId");
                String name = rs.getString("SchoolName");
                String telNo = rs.getString("SchoolTelNo");
                schoolList.add(new School(id, name, telNo));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return schoolList;
    }
 
}

　　

?
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
/**
 * encoding: utf-8
 * 版权所有 2025 涂聚文有限公司
 * 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
 * 描述：
 * # Author    : geovindu,Geovin Du 涂聚文.
 * # IDE       : IntelliJ IDEA 2023.1 Java 21
 * # database  : Oracle21c,MySQL 9.0,SQL Server 2019,PostgreSQL 17.1
 * # OS        : window10
 * # Datetime  : 2025 - 2025/2/9 - 16:42
 * # User      : geovindu
 * # Product   : IntelliJ IDEA
 * # Project   : oracledemo
 * # File      : BLL/SchoolBLL.java  类
 * # explain   : 学习
 **/
 
package Geovin.BLL;
 
import Geovin.Interface.ISchool;
import Geovin.Factory.AbstractFactory;
import Geovin.Model.School;
import java.util.List;
 
/**
 * 业务处理层
 */
public class SchoolBLL {
 
    private ISchool dal;
 
    public SchoolBLL() {
        dal = AbstractFactory.getDAL(); // 通过工厂获取 DAL 实例
    }
 
    /**
     * 添加学校
     * @param school
     */
    public void addSchool(School school) {
        dal.addSchool(school);
    }
 
    /**
     * 删除学校
     * @param schoolId
     */
    public void deleteSchool(String schoolId) {
        dal.deleteSchool(schoolId);
    }
 
    /**
     * 更新学校
     * @param school
     */
    public void updateSchool(School school) {
        dal.updateSchool(school);
    }
 
    /**
     * 查询学校
     * @param schoolId
     * @return
     */
    public School getSchoolById(String schoolId) {
        return dal.getSchoolById(schoolId);
    }
 
    /**
     * 查询所有学校
     * @return
     */
    public List<School> getAllSchools() {
        return dal.getAllSchools();
    }
 
    /**
     * 储存过程 添加
     * @param school
     * @return
     */
    public boolean addProc(School school)
    {
        return dal.addProc(school);
 
    }
 
    /**
     * 储存过程
     * @param schoolId
     * @return
     */
    public boolean deleteProc(String schoolId)
    {
        return dal.deleteProc(schoolId);
    }
 
    /**
     * 储存过程
     * @param school
     * @return
     */
    public boolean updateProc(School school)
    {
        return dal.updateProc(school);
    }
 
 
    /**
     * 储存过程
     * @param schoolId
     * @return
     */
    public School getProcById(String schoolId)
    {
        return dal.getProcById(schoolId);
    }
 
    /**
     * 储存过程
     * @return
     */
    public List<School> getAllProc()
    {
        return dal.getAllProc();
    }
 
}

　　

调用：

?
1
2
3
//sql
//schoolBLL.getAllSchools().forEach(System.out::println);
schoolBLL.getAllProc().forEach(System.out::println);

　　

输出：

 

posted @   ®Geovin Du Dream Park™  阅读(6)  评论(0)  编辑  收藏  举报
相关博文：
·  java: framework from BLL、DAL、IDAL、MODEL、Factory using postgresql 17.0
·  python: more Layer Architecture in Python and sql server 2019
·  JAVA（十二）学生、班级、年级管理系统
·  数据库笔记
·  Oracle数据库复习
阅读排行：
· 10年+ .NET Coder 心语 ── 封装的思维：从隐藏、稳定开始理解其本质意义
· 地球OL攻略 —— 某应届生求职总结
· 提示词工程——AI应用必不可少的技术
· Open-Sora 2.0 重磅开源！
· 周边上新：园子的第一款马克杯温暖上架
历史上的今天：
2021-02-09 javascript: detect screen size
2018-02-09 SQL Anywhere5.5: Metadata
2017-02-09 安装SQL server 2016遇到问题
< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 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 1 2 3 4 5
点击右上角即可分享
微信分享提示