# 项目搭建

## 1. 环境准备

### 1.1 数据库环境
– 安装PostgreSQL 18
– 创建数据库fgedudb
– 创建用户fgedu，密码fgedu_password
– 授予用户权限

### 1.2 开发环境
– Python：安装psycopg2
– Java：安装JDBC驱动
– Go：安装pgx

## 2. 项目结构

### 2.1 Python项目结构
– fgapp/
– models/：数据模型
– controllers/：业务逻辑
– views/：表示层
– utils/：工具函数
– config.py：配置文件
– main.py：入口文件

### 2.2 Java项目结构
– src/
– main/
– java/
– com/fgedu/fgapp/
– model/：数据模型
– dao/：数据访问
– service/：业务逻辑
– controller/：控制器
– util/：工具类
– config/：配置
– resources/：资源文件
– test/：测试代码

### 2.3 Go项目结构
– fgapp/
– model/：数据模型
– repository/：数据访问
– service/：业务逻辑
– handler/：处理器
– config/：配置
– main.go：入口文件

## 3. 配置文件

### 3.1 数据库配置
– 主机：localfgedu.net.cn
– 端口：5432
– 数据库：fgedudb
– 用户：fgedu
– 密码：fgedu_password

### 3.2 应用配置
– 服务端口：8080
– 日志级别：info
– 连接池配置：最小连接数5，最大连接数20

# 数据库设计

## 1. 表结构

### 1.1 学生表（fgedu_student）
– id：SERIAL PRIMARY KEY
– name：VARCHAR(50) NOT NULL
– age：INTEGER NOT NULL
– gender：VARCHAR(10) NOT NULL
– major：VARCHAR(50) NOT NULL
– created_at：TIMESTAMP DEFAULT CURRENT_TIMESTAMP
– updated_at：TIMESTAMP DEFAULT CURRENT_TIMESTAMP

### 1.2 课程表（fgedu_course）
– id：SERIAL PRIMARY KEY
– name：VARCHAR(50) NOT NULL
– credit：INTEGER NOT NULL
– teacher：VARCHAR(50) NOT NULL
– created_at：TIMESTAMP DEFAULT CURRENT_TIMESTAMP
– updated_at：TIMESTAMP DEFAULT CURRENT_TIMESTAMP

### 1.3 选课表（fgedu_enrollment）
– id：SERIAL PRIMARY KEY
– student_id：INTEGER REFERENCES fgedu_student(id)
– course_id：INTEGER REFERENCES fgedu_course(id)
– grade：INTEGER
– created_at：TIMESTAMP DEFAULT CURRENT_TIMESTAMP
– updated_at：TIMESTAMP DEFAULT CURRENT_TIMESTAMP

## 2. 索引设计
– fgedu_student：
– INDEX idx_student_name (name)
– INDEX idx_student_major (major)
– fgedu_course：
– INDEX idx_course_name (name)
– INDEX idx_course_teacher (teacher)
– fgedu_enrollment：
– INDEX idx_enrollment_student_id (student_id)
– INDEX idx_enrollment_course_id (course_id)

## 3. 触发器
– 自动更新updated_at字段的触发器

## 4. 存储过程
– 计算学生平均成绩的存储过程
– 统计课程选课人数的存储过程

# 应用实现

## 1. 数据访问层
– 封装CRUD操作
– 处理数据库连接
– 事务管理
– 错误处理

## 2. 业务逻辑层
– 实现业务规则
– 数据验证
– 调用数据访问层
– 处理业务异常

## 3. 表示层
– 处理用户请求
– 调用业务逻辑层
– 返回响应
– 错误处理

## 4. 工具类
– 数据库连接管理
– 日志处理
– 配置管理
– 工具函数

## 5. 测试
– 单元测试
– 集成测试
– 性能测试
– 安全测试

#!/usr/bin/env python3
# crud_fgapplication_python.py
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

import psycopg2
from psycopg2 import pool
import datetime

# 数据库连接参数
DB_PARAMS = {
“fgedu.net.cn”: “localfgedu.net.cn”,
“port”: 5432,
“fgedudb”: “fgedudb”,
“fgedu”: “fgedu”,
“password”: “fgedu_password”
}

# 创建连接池
postgres_pool = None

def init_pool():
global postgres_pool
postgres_pool = pool.ThreadedConnectionPool(
minconn=5,
maxconn=20,
**DB_PARAMS
)
print(“连接池初始化成功”)

def get_connection():
“””从连接池获取连接”””
return postgres_pool.getconn()

def release_connection(conn):
“””将连接归还到连接池”””
postgres_pool.putconn(conn)

def execute_query(query, params=None):
“””执行SQL查询”””
conn = None
try:
conn = get_connection()
with conn.cursor() as cur:
cur.execute(query, params)
if query.strip().upper().startswith(“SELECT”):
result = cur.fetchall()
conn.commit()
return result
else:
conn.commit()
return cur.rowcount
except Exception as e:
if conn:
conn.rollback()
print(f”执行查询失败: {e}”)
raise
finally:
if conn:
release_connection(conn)

def create_tables():
“””创建表”””
# 创建学生表
student_table = “””
CREATE TABLE fgedu_IF NOT EXISTS fgedu_student (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INTEGER NOT NULL,
gender VARCHAR(10) NOT NULL,
major VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
“””
execute_query(student_table)
print(“学生表创建成功”)

# 创建课程表
course_table = “””
CREATE TABLE fgedu_IF NOT EXISTS fgedu_course (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
credit INTEGER NOT NULL,
teacher VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
“””
execute_query(course_table)
print(“课程表创建成功”)

# 创建选课表
enrollment_table = “””
CREATE TABLE fgedu_IF NOT EXISTS fgedu_enrollment (
id SERIAL PRIMARY KEY,
student_id INTEGER REFERENCES fgedu_student(id),
course_id INTEGER REFERENCES fgedu_course(id),
grade INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
“””
execute_query(enrollment_table)
print(“选课表创建成功”)

def insert_student(name, age, gender, major):
“””插入学生”””
query = “INSERT INTO fgedu_student (name, age, gender, major) VALUES (%s, %s, %s, %s)”
params = (name, age, gender, major)
return execute_query(query, params)

def get_student(id):
“””获取学生”””
query = “SELECT * FROM fgedu_student WHERE id = %s”
params = (id,)
return execute_query(query, params)

def get_all_students():
“””获取所有学生”””
query = “SELECT * FROM fgedu_student”
return execute_query(query)

def update_student(id, name, age, gender, major):
“””更新学生”””
query = “UPDATE fgedu_student SET name = %s, age = %s, gender = %s, major = %s, updated_at =
CURRENT_TIMESTAMP WHERE id = %s”
params = (name, age, gender, major, id)
return execute_query(query, params)

def delete_student(id):
“””删除学生”””
query = “DELETE FROM fgedu_student WHERE id = %s”
params = (id,)
return execute_query(query, params)

def insert_course(name, credit, teacher):
“””插入课程”””
query = “INSERT INTO fgedu_course (name, credit, teacher) VALUES (%s, %s, %s)”
params = (name, credit, teacher)
return execute_query(query, params)

def get_course(id):
“””获取课程”””
query = “SELECT * FROM fgedu_course WHERE id = %s”
params = (id,)
return execute_query(query, params)

def get_all_courses():
“””获取所有课程”””
query = “SELECT * FROM fgedu_course”
return execute_query(query)

def update_course(id, name, credit, teacher):
“””更新课程”””
query = “UPDATE fgedu_course SET name = %s, credit = %s, teacher = %s, updated_at = CURRENT_TIMESTAMP WHERE
id = %s”
params = (name, credit, teacher, id)
return execute_query(query, params)

def delete_course(id):
“””删除课程”””
query = “DELETE FROM fgedu_course WHERE id = %s”
params = (id,)
return execute_query(query, params)

def enroll_student(student_id, course_id, grade=None):
“””学生选课”””
query = “INSERT INTO fgedu_enrollment (student_id, course_id, grade) VALUES (%s, %s, %s)”
params = (student_id, course_id, grade)
return execute_query(query, params)

def get_student_courses(student_id):
“””获取学生的课程”””
query = “””
SELECT c.id, c.name, c.credit, c.teacher, e.grade
FROM fgedu_course c
JOIN fgedu_enrollment e ON c.id = e.course_id
WHERE e.student_id = %s
“””
params = (student_id,)
return execute_query(query, params)

def update_grade(student_id, course_id, grade):
“””更新成绩”””
query = “UPDATE fgedu_enrollment SET grade = %s, updated_at = CURRENT_TIMESTAMP WHERE student_id = %s AND
course_id = %s”
params = (grade, student_id, course_id)
return execute_query(query, params)

def drop_course(student_id, course_id):
“””退课”””
query = “DELETE FROM fgedu_enrollment WHERE student_id = %s AND course_id = %s”
params = (student_id, course_id)
return execute_query(query, params)

def main():
“””主函数”””
# 初始化连接池
init_pool()

try:
# 创建表
create_tables()

# 插入学生
print(“\n插入学生:”)
insert_student(“风哥1号”, 20, “男”, “计算机科学”)
insert_student(“风哥2号”, 21, “女”, “软件工程”)
insert_student(“王五”, 19, “男”, “数据科学”)

# 获取所有学生
print(“\n所有学生:”)
students = get_all_students()
for student in students:
print(student)

# 更新学生
print(“\n更新学生:”)
update_student(1, “风哥1号”, 21, “男”, “人工智能”)

# 获取更新后的学生
print(“\n更新后的学生:”)
student = get_student(1)
print(student)

# 插入课程
print(“\n插入课程:”)
insert_course(“数据库系统”, 4, “张老师”)
insert_course(“操作系统”, 4, “李老师”)
insert_course(“数据结构”, 3, “王老师”)

# 获取所有课程
print(“\n所有课程:”)
courses = get_all_courses()
for course in courses:
print(course)

# 学生选课
print(“\n学生选课:”)
enroll_student(1, 1)
enroll_student(1, 2)
enroll_student(2, 1)
enroll_student(3, 3)

# 获取学生的课程
print(“\n学生1的课程:”)
student_courses = get_student_courses(1)
for course in student_courses:
print(course)

# 更新成绩
print(“\n更新成绩:”)
update_grade(1, 1, 90)
update_grade(1, 2, 85)

# 获取更新后的课程和成绩
print(“\n更新后的学生1的课程和成绩:”)
student_courses = get_student_courses(1)
for course in student_courses:
print(course)

# 退课
print(“\n退课:”)
drop_course(1, 2)

# 获取退课后的课程
print(“\n退课后的学生1的课程:”)
student_courses = get_student_courses(1)
for course in student_courses:
print(course)

# 删除学生
print(“\n删除学生:”)
delete_student(3)

# 获取删除后的所有学生
print(“\n删除后的所有学生:”)
students = get_all_students()
for student in students:
print(student)

finally:
# 关闭连接池
postgres_pool.closeall()
print(“\n连接池关闭成功”)

if __name__ == “__main__”:
main()

# 执行结果
$ python crud_fgapplication_python.py
连接池初始化成功
学生表创建成功
课程表创建成功
选课表创建成功

插入学生:

所有学生:
(1, ‘风哥1号’, 20, ‘男’, ‘计算机科学’, datetime.datetime(2026, 4, 2, 10, 0, 0, 123456), datetime.datetime(2026, 4, 2,
10, 0, 0, 123456))
(2, ‘风哥2号’, 21, ‘女’, ‘软件工程’, datetime.datetime(2026, 4, 2, 10, 0, 0, 234567), datetime.datetime(2026, 4, 2,
10, 0, 0, 234567))
(3, ‘王五’, 19, ‘男’, ‘数据科学’, datetime.datetime(2026, 4, 2, 10, 0, 0, 345678), datetime.datetime(2026, 4, 2,
10, 0, 0, 345678))

更新学生:

更新后的学生:
(1, ‘风哥1号’, 21, ‘男’, ‘人工智能’, datetime.datetime(2026, 4, 2, 10, 0, 0, 123456), datetime.datetime(2026, 4, 2,
10, 0, 1, 456789))

插入课程:

所有课程:
(1, ‘数据库系统’, 4, ‘张老师’, datetime.datetime(2026, 4, 2, 10, 0, 2, 567890), datetime.datetime(2026, 4, 2, 10, 0,
2, 567890))
(2, ‘操作系统’, 4, ‘李老师’, datetime.datetime(2026, 4, 2, 10, 0, 2, 678901), datetime.datetime(2026, 4, 2, 10, 0,
2, 678901))
(3, ‘数据结构’, 3, ‘王老师’, datetime.datetime(2026, 4, 2, 10, 0, 2, 789012), datetime.datetime(2026, 4, 2, 10, 0,
2, 789012))

学生选课:

学生1的课程:
(1, ‘数据库系统’, 4, ‘张老师’, None)
(2, ‘操作系统’, 4, ‘李老师’, None)

更新成绩:

更新后的学生1的课程和成绩:
(1, ‘数据库系统’, 4, ‘张老师’, 90)
(2, ‘操作系统’, 4, ‘李老师’, 85)

退课:

退课后的学生1的课程:
(1, ‘数据库系统’, 4, ‘张老师’, 90)

删除学生:

删除后的所有学生:
(1, ‘风哥1号’, 21, ‘男’, ‘人工智能’, datetime.datetime(2026, 4, 2, 10, 0, 0, 123456), datetime.datetime(2026, 4, 2,
10, 0, 1, 456789))
(2, ‘风哥2号’, 21, ‘女’, ‘软件工程’, datetime.datetime(2026, 4, 2, 10, 0, 0, 234567), datetime.datetime(2026, 4, 2,
10, 0, 0, 234567))

连接池关闭成功

// CrudApplicationJava.java
// from:www.itpux.com.qq113257174.wx:itpux-com
// web: `http://www.fgedu.net.cn`

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;

public class CrudApplicationJava {
private static HikariDataSource dataSource;

// 初始化连接池
public static void initPool() {
HikariConfig config = new HikariConfig();
config.setJdbcUrl(“jdbc:postgresql://localfgedu.net.cn:5432/fgedudb”);
config.setUsername(“fgedu”);
config.setPassword(“fgedu_password”);
config.setMinimumIdle(5);
config.setMaximumPoolSize(20);
config.setConnectionTimeout(30000);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000);

dataSource = new HikariDataSource(config);
System.out.println(“连接池初始化成功”);
}

// 执行SQL查询
public static ResultSet executeQuery(String query, Object… params) throws SQLException {
Connection conn = dataSource.getConnection();
PreparedStatement pstmt = conn.prepareStatement(query);

// 设置参数
for (int i = 0; i < params.length; i++) { pstmt.setObject(i + 1, params[i]); } // 执行查询 return pstmt.executeQuery(); } // 执行SQL更新 public static int executeUpdate(String query, Object... params) throws SQLException { try (Connection conn=dataSource.getConnection(); PreparedStatement pstmt=conn.prepareStatement(query)) { // 设置参数 for (int i=0; i < params.length; i++) { pstmt.setObject(i + 1, params[i]); } // 执行更新 return pstmt.executeUpdate(); } } // 创建表 public static void createTables() throws SQLException { // 创建学生表 String studentTable=""" CREATE TABLE fgedu_IF NOT EXISTS fgedu_student ( id SERIAL PRIMARY KEY, name VARCHAR(50) NOT NULL, age INTEGER NOT NULL, gender VARCHAR(10) NOT NULL, major VARCHAR(50) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) """ ; executeUpdate(studentTable); System.out.println("学生表创建成功"); // 创建课程表 String courseTable=""" CREATE TABLE fgedu_IF NOT EXISTS fgedu_course ( id SERIAL PRIMARY KEY, name VARCHAR(50) NOT NULL, credit INTEGER NOT NULL, teacher VARCHAR(50) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) """ ; executeUpdate(courseTable); System.out.println("课程表创建成功"); // 创建选课表 String enrollmentTable=""" CREATE TABLE fgedu_IF NOT EXISTS fgedu_enrollment ( id SERIAL PRIMARY KEY, student_id INTEGER REFERENCES fgedu_student(id), course_id INTEGER REFERENCES fgedu_course(id), grade INTEGER, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) """ ; executeUpdate(enrollmentTable); System.out.println("选课表创建成功"); } // 插入学生 public static int insertStudent(String name, int age, String gender, String major) throws SQLException { String query="INSERT INTO fgedu_student (name, age, gender, major) VALUES (?, ?, ?, ?)" ; return executeUpdate(query, name, age, gender, major); } // 获取学生 public static void getStudent(int id) throws SQLException { String query="SELECT * FROM fgedu_student WHERE id = ?" ; try (ResultSet rs=executeQuery(query, id)) { if (rs.next()) { System.out.println(rs.getInt("id") + ", " + rs.getString("name") + ", " + rs.getInt("age") + ", " + rs.getString("gender") + ", " + rs.getString("major") + ", " + rs.getTimestamp("created_at") + ", " + rs.getTimestamp("updated_at")); } } } // 获取所有学生 public static void getAllStudents() throws SQLException { String query="SELECT * FROM fgedu_student" ; try (ResultSet rs=executeQuery(query)) { while (rs.next()) { System.out.println(rs.getInt("id") + ", " + rs.getString("name") + ", " + rs.getInt("age") + ", " + rs.getString("gender") + ", " + rs.getString("major") + ", " + rs.getTimestamp("created_at") + ", " + rs.getTimestamp("updated_at")); } } } // 更新学生 public static int updateStudent(int id, String name, int age, String gender, String major) throws SQLException { String query="UPDATE fgedu_student SET name = ?, age = ?, gender = ?, major = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?" ; return executeUpdate(query, name, age, gender, major, id); } // 删除学生 public static int deleteStudent(int id) throws SQLException { String query="DELETE FROM fgedu_student WHERE id = ?" ; return executeUpdate(query, id); } // 插入课程 public static int insertCourse(String name, int credit, String teacher) throws SQLException { String query="INSERT INTO fgedu_course (name, credit, teacher) VALUES (?, ?, ?)" ; return executeUpdate(query, name, credit, teacher); } // 获取课程 public static void getCourse(int id) throws SQLException { String query="SELECT * FROM fgedu_course WHERE id = ?" ; try (ResultSet rs=executeQuery(query, id)) { if (rs.next()) { System.out.println(rs.getInt("id") + ", " + rs.getString("name") + ", " + rs.getInt("credit") + ", " + rs.getString("teacher") + ", " + rs.getTimestamp("created_at") + ", " + rs.getTimestamp("updated_at")); } } } // 获取所有课程 public static void getAllCourses() throws SQLException { String query="SELECT * FROM fgedu_course" ; try (ResultSet rs=executeQuery(query)) { while (rs.next()) { System.out.println(rs.getInt("id") + ", " + rs.getString("name") + ", " + rs.getInt("credit") + ", " + rs.getString("teacher") + ", " + rs.getTimestamp("created_at") + ", " + rs.getTimestamp("updated_at")); } } } // 更新课程 public static int updateCourse(int id, String name, int credit, String teacher) throws SQLException { String query="UPDATE fgedu_course SET name = ?, credit = ?, teacher = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?" ; return executeUpdate(query, name, credit, teacher, id); } // 删除课程 public static int deleteCourse(int id) throws SQLException { String query="DELETE FROM fgedu_course WHERE id = ?" ; return executeUpdate(query, id); } // 学生选课 public static int enrollStudent(int studentId, int courseId, Integer grade) throws SQLException { String query="INSERT INTO fgedu_enrollment (student_id, course_id, grade) VALUES (?, ?, ?)" ; return executeUpdate(query, studentId, courseId, grade); } // 获取学生的课程 public static void getStudentCourses(int studentId) throws SQLException { String query=""" SELECT c.id, c.name, c.credit, c.teacher, e.grade FROM fgedu_course c JOIN fgedu_enrollment e ON c.id = e.course_id WHERE e.student_id = ? """ ; try (ResultSet rs=executeQuery(query, studentId)) { while (rs.next()) { System.out.println(rs.getInt("id") + ", " + rs.getString("name") + ", " + rs.getInt("credit") + ", " + rs.getString("teacher") + ", " + rs.getInt("grade")); } } } // 更新成绩 public static int updateGrade(int studentId, int courseId, int grade) throws SQLException { String query="UPDATE fgedu_enrollment SET grade = ?, updated_at = CURRENT_TIMESTAMP WHERE student_id = ? AND course_id = ?" ; return executeUpdate(query, grade, studentId, courseId); } // 退课 public static int dropCourse(int studentId, int courseId) throws SQLException { String query="DELETE FROM fgedu_enrollment WHERE student_id = ? AND course_id = ?" ; return executeUpdate(query, studentId, courseId); } public static void main(String[] args) { // 初始化连接池 initPool(); try { // 创建表 createTables(); // 插入学生 System.out.println("\n插入学生:"); insertStudent("风哥1号", 20, "男" , "计算机科学" ); insertStudent("风哥2号", 21, "女" , "软件工程" ); insertStudent("王五", 19, "男" , "数据科学" ); // 获取所有学生 System.out.println("\n所有学生:"); getAllStudents(); // 更新学生 System.out.println("\n更新学生:"); updateStudent(1, "风哥1号" , 21, "男" , "人工智能" ); // 获取更新后的学生 System.out.println("\n更新后的学生:"); getStudent(1); // 插入课程 System.out.println("\n插入课程:"); insertCourse("数据库系统", 4, "张老师" ); insertCourse("操作系统", 4, "李老师" ); insertCourse("数据结构", 3, "王老师" ); // 获取所有课程 System.out.println("\n所有课程:"); getAllCourses(); // 学生选课 System.out.println("\n学生选课:"); enrollStudent(1, 1, null); enrollStudent(1, 2, null); enrollStudent(2, 1, null); enrollStudent(3, 3, null); // 获取学生的课程 System.out.println("\n学生1的课程:"); getStudentCourses(1); // 更新成绩 System.out.println("\n更新成绩:"); updateGrade(1, 1, 90); updateGrade(1, 2, 85); // 获取更新后的课程和成绩 System.out.println("\n更新后的学生1的课程和成绩:"); getStudentCourses(1); // 退课 System.out.println("\n退课:"); dropCourse(1, 2); // 获取退课后的课程 System.out.println("\n退课后的学生1的课程:"); getStudentCourses(1); // 删除学生 System.out.println("\n删除学生:"); deleteStudent(3); // 获取删除后的所有学生 System.out.println("\n删除后的所有学生:"); getAllStudents(); } catch (SQLException e) { e.printStackTrace(); } finally { // 关闭连接池 dataSource.close(); System.out.println("\n连接池关闭成功"); } } }

// crud_fgapplication_go.go
// from:www.itpux.com.qq113257174.wx:itpux-com
// web: `http://www.fgedu.net.cn`

package main

import (
“context”
“fmt”
“log”
“time”

“github.com/jackc/pgx/v5/pgxpool”
)

var pool *pgxpool.Pool

// 初始化连接池
func initPool() {
config, err := pgxpool.ParseConfig(“pgsql: //fgedu:fgedu_password@localfgedu.net.cn:5432/fgedudb”)
if err != nil {
log.Fatalf(“Unable to parse config: %v”, err)
}

// 配置连接池
config.MaxConns = 20
config.MinConns = 5
config.MaxConnLifetime = time.Hour
config.MaxConnIdleTime = 10 * time.Minute
config.HealthCheckPeriod = time.Minute

// 创建连接池
var err2 error
pool, err2 = pgxpool.NewWithConfig(context.Background(), config)
if err2 != nil {
log.Fatalf(“Unable to create connection pool: %v”, err2)
}

fmt.Println(“连接池初始化成功”)
}

// 创建表
func createTables() {
// 创建学生表
studentTable := `
CREATE TABLE fgedu_IF NOT EXISTS fgedu_student (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INTEGER NOT NULL,
gender VARCHAR(10) NOT NULL,
major VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
`
_, err := pool.Exec(context.Background(), studentTable)
if err != nil {
log.Fatalf(“创建学生表失败: %v”, err)
}
fmt.Println(“学生表创建成功”)

// 创建课程表
courseTable := `
CREATE TABLE fgedu_IF NOT EXISTS fgedu_course (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
credit INTEGER NOT NULL,
teacher VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
`
_, err = pool.Exec(context.Background(), courseTable)
if err != nil {
log.Fatalf(“创建课程表失败: %v”, err)
}
fmt.Println(“课程表创建成功”)

// 创建选课表
enrollmentTable := `
CREATE TABLE fgedu_IF NOT EXISTS fgedu_enrollment (
id SERIAL PRIMARY KEY,
student_id INTEGER REFERENCES fgedu_student(id),
course_id INTEGER REFERENCES fgedu_course(id),
grade INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
`
_, err = pool.Exec(context.Background(), enrollmentTable)
if err != nil {
log.Fatalf(“创建选课表失败: %v”, err)
}
fmt.Println(“选课表创建成功”)
}

// 插入学生
func insertStudent(name string, age int, gender string, major string) {
query := “INSERT INTO fgedu_student (name, age, gender, major) VALUES ($1, $2, $3, $4)”
_, err := pool.Exec(context.Background(), query, name, age, gender, major)
if err != nil {
log.Fatalf(“插入学生失败: %v”, err)
}
}

// 获取学生
func getStudent(id int) {
query := “SELECT * FROM fgedu_student WHERE id = $1”
row := pool.QueryRow(context.Background(), query, id)

var studentID int
var studentName string
var studentAge int
var studentGender string
var studentMajor string
var createdAt time.Time
var updatedAt time.Time

err := row.Scan(&studentID, &studentName, &studentAge, &studentGender, &studentMajor, &createdAt,
&updatedAt)
if err != nil {
log.Fatalf(“获取学生失败: %v”, err)
}

fmt.Printf(“%d, %s, %d, %s, %s, %v, %v\n”, studentID, studentName, studentAge, studentGender,
studentMajor, createdAt, updatedAt)
}

// 获取所有学生
func getAllStudents() {
query := “SELECT * FROM fgedu_student”
rows, err := pool.Query(context.Background(), query)
if err != nil {
log.Fatalf(“获取所有学生失败: %v”, err)
}
defer rows.Close()

for rows.Next() {
var studentID int
var studentName string
var studentAge int
var studentGender string
var studentMajor string
var createdAt time.Time
var updatedAt time.Time

err := rows.Scan(&studentID, &studentName, &studentAge, &studentGender, &studentMajor, &createdAt,
&updatedAt)
if err != nil {
log.Fatalf(“扫描学生行失败: %v”, err)
}

fmt.Printf(“%d, %s, %d, %s, %s, %v, %v\n”, studentID, studentName, studentAge, studentGender,
studentMajor, createdAt, updatedAt)
}

if err := rows.Err(); err != nil {
log.Fatalf(“遍历学生行失败: %v”, err)
}
}

// 更新学生
func updateStudent(id int, name string, age int, gender string, major string) {
query := “UPDATE fgedu_student SET name = $1, age = $2, gender = $3, major = $4, updated_at =
CURRENT_TIMESTAMP WHERE id = $5”
_, err := pool.Exec(context.Background(), query, name, age, gender, major, id)
if err != nil {
log.Fatalf(“更新学生失败: %v”, err)
}
}

// 删除学生
func deleteStudent(id int) {
query := “DELETE FROM fgedu_student WHERE id = $1”
_, err := pool.Exec(context.Background(), query, id)
if err != nil {
log.Fatalf(“删除学生失败: %v”, err)
}
}

// 插入课程
func insertCourse(name string, credit int, teacher string) {
query := “INSERT INTO fgedu_course (name, credit, teacher) VALUES ($1, $2, $3)”
_, err := pool.Exec(context.Background(), query, name, credit, teacher)
if err != nil {
log.Fatalf(“插入课程失败: %v”, err)
}
}

// 获取课程
func getCourse(id int) {
query := “SELECT * FROM fgedu_course WHERE id = $1”
row := pool.QueryRow(context.Background(), query, id)

var courseID int
var courseName string
var courseCredit int
var courseTeacher string
var createdAt time.Time
var updatedAt time.Time

err := row.Scan(&courseID, &courseName, &courseCredit, &courseTeacher, &createdAt, &updatedAt)
if err != nil {
log.Fatalf(“获取课程失败: %v”, err)
}

fmt.Printf(“%d, %s, %d, %s, %v, %v\n”, courseID, courseName, courseCredit, courseTeacher, createdAt,
updatedAt)
}

// 获取所有课程
func getAllCourses() {
query := “SELECT * FROM fgedu_course”
rows, err := pool.Query(context.Background(), query)
if err != nil {
log.Fatalf(“获取所有课程失败: %v”, err)
}
defer rows.Close()

for rows.Next() {
var courseID int
var courseName string
var courseCredit int
var courseTeacher string
var createdAt time.Time
var updatedAt time.Time

err := rows.Scan(&courseID, &courseName, &courseCredit, &courseTeacher, &createdAt, &updatedAt)
if err != nil {
log.Fatalf(“扫描课程行失败: %v”, err)
}

fmt.Printf(“%d, %s, %d, %s, %v, %v\n”, courseID, courseName, courseCredit, courseTeacher, createdAt,
updatedAt)
}

if err := rows.Err(); err != nil {
log.Fatalf(“遍历课程行失败: %v”, err)
}
}

// 更新课程
func updateCourse(id int, name string, credit int, teacher string) {
query := “UPDATE fgedu_course SET name = $1, credit = $2, teacher = $3, updated_at =
CURRENT_TIMESTAMP WHERE id = $4”
_, err := pool.Exec(context.Background(), query, name, credit, teacher, id)
if err != nil {
log.Fatalf(“更新课程失败: %v”, err)
}
}

// 删除课程
func deleteCourse(id int) {
query := “DELETE FROM fgedu_course WHERE id = $1”
_, err := pool.Exec(context.Background(), query, id)
if err != nil {
log.Fatalf(“删除课程失败: %v”, err)
}
}

// 学生选课
func enrollStudent(studentID int, courseID int, grade *int) {
query := “INSERT INTO fgedu_enrollment (student_id, course_id, grade) VALUES ($1, $2, $3)”
_, err := pool.Exec(context.Background(), query, studentID, courseID, grade)
if err != nil {
log.Fatalf(“学生选课失败: %v”, err)
}
}

// 获取学生的课程
func getStudentCourses(studentID int) {
query := `
SELECT c.id, c.name, c.credit, c.teacher, e.grade
FROM fgedu_course c
JOIN fgedu_enrollment e ON c.id = e.course_id
WHERE e.student_id = $1
`
rows, err := pool.Query(context.Background(), query, studentID)
if err != nil {
log.Fatalf(“获取学生课程失败: %v”, err)
}
defer rows.Close()

for rows.Next() {
var courseID int
var courseName string
var courseCredit int
var courseTeacher string
var grade int

err := rows.Scan(&courseID, &courseName, &courseCredit, &courseTeacher, &grade)
if err != nil {
log.Fatalf(“扫描课程行失败: %v”, err)
}

fmt.Printf(“%d, %s, %d, %s, %d\n”, courseID, courseName, courseCredit, courseTeacher, grade)
}

if err := rows.Err(); err != nil {
log.Fatalf(“遍历课程行失败: %v”, err)
}
}

// 更新成绩
func updateGrade(studentID int, courseID int, grade int) {
query := “UPDATE fgedu_enrollment SET grade = $1, updated_at = CURRENT_TIMESTAMP WHERE student_id =
$2 AND course_id = $3”
_, err := pool.Exec(context.Background(), query, grade, studentID, courseID)
if err != nil {
log.Fatalf(“更新成绩失败: %v”, err)
}
}

// 退课
func dropCourse(studentID int, courseID int) {
query := “DELETE FROM fgedu_enrollment WHERE student_id = $1 AND course_id = $2”
_, err := pool.Exec(context.Background(), query, studentID, courseID)
if err != nil {
log.Fatalf(“退课失败: %v”, err)
}
}

func main() {
// 初始化连接池
initPool()
defer pool.Close()

// 创建表
createTables()

// 插入学生
fmt.Println(“\n插入学生:”)
insertStudent(“风哥1号”, 20, “男”, “计算机科学”)
insertStudent(“风哥2号”, 21, “女”, “软件工程”)
insertStudent(“王五”, 19, “男”, “数据科学”)

// 获取所有学生
fmt.Println(“\n所有学生:”)
getAllStudents()

// 更新学生
fmt.Println(“\n更新学生:”)
updateStudent(1, “风哥1号”, 21, “男”, “人工智能”)

// 获取更新后的学生
fmt.Println(“\n更新后的学生:”)
getStudent(1)

// 插入课程
fmt.Println(“\n插入课程:”)
insertCourse(“数据库系统”, 4, “张老师”)
insertCourse(“操作系统”, 4, “李老师”)
insertCourse(“数据结构”, 3, “王老师”)

// 获取所有课程
fmt.Println(“\n所有课程:”)
getAllCourses()

// 学生选课
fmt.Println(“\n学生选课:”)
enrollStudent(1, 1, nil)
enrollStudent(1, 2, nil)
enrollStudent(2, 1, nil)
enrollStudent(3, 3, nil)

// 获取学生的课程
fmt.Println(“\n学生1的课程:”)
getStudentCourses(1)

// 更新成绩
fmt.Println(“\n更新成绩:”)