java: framework from BLL、DAL、IDAL、MODEL、Factory using postgresql 17.0

postgresql 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
58
59
60
61
62
63
64
65
66
67
68
69
70
CREATE TABLE School (
    SchoolId CHAR(5) PRIMARY KEY,  -- 主键
    SchoolName VARCHAR(500) NOT NULL,
    SchoolTelNo VARCHAR(8) NULL
);
 
 
CREATE OR REPLACE FUNCTION addschool(
    p_school_id CHAR(5),
    p_school_name VARCHAR(500),
    p_school_tel_no VARCHAR(8)
) RETURNS VOID AS $$
BEGIN
    INSERT INTO School (SchoolId, SchoolName, SchoolTelNo)
    VALUES (p_school_id, p_school_name, p_school_tel_no);
END;
$$ LANGUAGE plpgsql;
 
 
CREATE OR REPLACE FUNCTION deleteschool(
    p_school_id CHAR(5)
) RETURNS VOID AS $$
BEGIN
    DELETE FROM School WHERE SchoolId = p_school_id;
END;
$$ LANGUAGE plpgsql;
 
 
CREATE OR REPLACE FUNCTION updateschool(
    p_school_id CHAR(5),
    p_school_name VARCHAR(500),
    p_school_tel_no VARCHAR(8)
) RETURNS VOID AS $$
BEGIN
    UPDATE School
    SET SchoolName = p_school_name, SchoolTelNo = p_school_tel_no
    WHERE SchoolId = p_school_id;
END;
$$ LANGUAGE plpgsql;
 
 
CREATE OR REPLACE FUNCTION getschoolbyid(
    p_school_id CHAR(5)
) RETURNS TABLE (
    SchoolId CHAR(5),
    SchoolName VARCHAR(500),
    SchoolTelNo VARCHAR(8)
) AS $$
BEGIN
    RETURN QUERY
    SELECT SchoolId, SchoolName, SchoolTelNo
    FROM School
    WHERE SchoolId = p_school_id;
END;
$$ LANGUAGE plpgsql;
 
 
DROP FUNCTION IF EXISTS getallschools();
CREATE OR REPLACE FUNCTION getallschools()
RETURNS TABLE (
    school_id CHAR(5),
    school_name VARCHAR(500),
    school_tel_no VARCHAR(8)
) AS $$
BEGIN
    RETURN QUERY
    SELECT School.SchoolId, School.SchoolName, School.SchoolTelNo
    FROM School;
END;
$$ LANGUAGE plpgsql;

　　

java: 只需写DAL有点区别，其它和上篇一样

?
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 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, Types.OTHER);
            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 = "select * from getallschools()";
        try (
             CallableStatement cstmt = SqlHelper.getConnect().prepareCall(sql)) {
            //cstmt.registerOutParameter(1, Types.OTHER);
            //cstmt.execute();
            ResultSet rs = cstmt.executeQuery();//(ResultSet) cstmt.getObject(1);
            while (rs.next()) {
                String id = rs.getString("school_id");
                String name = rs.getString("school_name");
                String telNo = rs.getString("school_tel_no");
                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
/**
 * encoding: utf-8
 * 版权所有 2025 涂聚文有限公司
 * 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
 * 描述：https://jdbc.postgresql.org/download/
 * # Author    : geovindu,Geovin Du 涂聚文.
 * # IDE       : IntelliJ IDEA 2023.1 Java JDK 21
 * # database  : Oracle 21c
 * # OS        : window10
 * # Datetime  : 2025 - 2025/2/9 - 12:27
 * # User      : geovindu
 * # Product   : IntelliJ IDEA
 * # Project   : oracledemo
 * # File      : Main.java  类
 * # explain   : 学习
 **/
 
 
import Geovin.BLL.SchoolBLL;
import Geovin.Model.School;
 
 
public class Main {
    public static void main(String[] args) {
 
        SchoolBLL schoolBLL = new SchoolBLL();
        //sql
        //schoolBLL.getAllSchools().forEach(System.out::println);
        System.out.println(schoolBLL.getSchoolById("U0001").getSchoolName());
        schoolBLL.getAllProc().forEach(System.out::println);
        System.out.println("Hello Java world!");
    }
}

　　

输出：

 

posted @   ®Geovin Du Dream Park™  阅读(6)  评论(0)  编辑  收藏  举报
相关博文：
·  java: framework from BLL、DAL、IDAL、MODEL、Factory using oracle 21c
·  more Layer Architecture in Python and postgreSQL17.0
·  school 代码 -2022-11-30
·  JAVA（十二）学生、班级、年级管理系统
·  MySQL完整版详解
阅读排行：
· 10年+ .NET Coder 心语 ── 封装的思维：从隐藏、稳定开始理解其本质意义
· 地球OL攻略 —— 某应届生求职总结
· 提示词工程——AI应用必不可少的技术
· Open-Sora 2.0 重磅开源！
· 周边上新：园子的第一款马克杯温暖上架
历史上的今天：
2015-02-10 sql:Mysql create view,function,procedure
2015-02-10 sql: MySQL and Microsoft SQL Server Stored Procedures IN, OUT using csharp code
2015-02-10 Working C# code for MySql5.5 Stored Procedures IN parameters
2010-02-10 Regular expression notation(正则表达式）
< 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
点击右上角即可分享
微信分享提示