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

sql:

-- 创建 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;

　　

/**
 * 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 + "]";
    }
}

　　

/**
 * 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();



}

　　

/**
 * 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;
    }

}

　　

/**
 * 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();
    }

}

　　

调用：

        //sql
        //schoolBLL.getAllSchools().forEach(System.out::println);
        schoolBLL.getAllProc().forEach(System.out::println);

　　

输出：