java/w9/DatabaseUtil.java

package com.example;

import com.example.entity.Course;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.HashMap;

/**
 * 数据库工具类
 * 提供数据库操作相关方法
 */
public class DatabaseUtil {
    // 数据库URL
    private static final String DB_URL = "jdbc:sqlite:course.db";
    
    // 静态初始化块，加载SQLite驱动
    static {
        try {
            Class.forName("org.sqlite.JDBC");
            System.out.println("SQLite驱动加载成功");
        } catch (ClassNotFoundException e) {
            System.err.println("SQLite驱动加载失败: " + e.getMessage());
        }
    }
    
    // 初始化数据库
    public static void initDatabase() {
        try (Connection conn = DriverManager.getConnection(DB_URL);
             Statement stmt = conn.createStatement()) {
            
            // 创建courses表
            String createTableSQL = "CREATE TABLE IF NOT EXISTS courses (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "course_code TEXT," +
                    "course_name TEXT," +
                    "credit REAL," +
                    "teacher TEXT," +
                    "department TEXT," +
                    "capacity INTEGER," +
                    "enrolled INTEGER," +
                    "class_time TEXT," +
                    "class_room TEXT," +
                    "course_type TEXT," +
                    "semester TEXT," +
                    "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
                    ")";
            stmt.executeUpdate(createTableSQL);
            
            System.out.println("数据库初始化成功");
        } catch (SQLException e) {
            System.err.println("数据库初始化失败: " + e.getMessage());
        }
    }
    
    // 获取所有课程
    public static List<Course> getAllCourses() {
        List<Course> courses = new ArrayList<>();
        String sql = "SELECT * FROM courses";
        
        try (Connection conn = DriverManager.getConnection(DB_URL);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                Course course = new Course();
                course.setId(rs.getLong("id"));
                course.setCourseCode(rs.getString("course_code"));
                course.setCourseName(rs.getString("course_name"));
                course.setCredit(rs.getDouble("credit"));
                course.setTeacher(rs.getString("teacher"));
                course.setDepartment(rs.getString("department"));
                course.setCapacity(rs.getInt("capacity"));
                course.setEnrolled(rs.getInt("enrolled"));
                course.setClassTime(rs.getString("class_time"));
                course.setClassRoom(rs.getString("class_room"));
                course.setCourseType(rs.getString("course_type"));
                course.setSemester(rs.getString("semester"));
                courses.add(course);
            }
        } catch (SQLException e) {
            System.err.println("获取课程列表失败: " + e.getMessage());
        }
        
        return courses;
    }
    
    // 获取课程类型分布
    public static Map<String, Integer> getCourseTypeDistribution() {
        Map<String, Integer> distribution = new HashMap<>();
        String sql = "SELECT course_type, COUNT(*) as count FROM courses GROUP BY course_type";
        
        try (Connection conn = DriverManager.getConnection(DB_URL);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                String type = rs.getString("course_type");
                int count = rs.getInt("count");
                distribution.put(type, count);
            }
        } catch (SQLException e) {
            System.err.println("获取课程类型分布失败: " + e.getMessage());
        }
        
        return distribution;
    }
    
    // 获取院系分布
    public static Map<String, Integer> getDepartmentDistribution() {
        Map<String, Integer> distribution = new HashMap<>();
        String sql = "SELECT department, COUNT(*) as count FROM courses GROUP BY department";
        
        try (Connection conn = DriverManager.getConnection(DB_URL);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                String department = rs.getString("department");
                int count = rs.getInt("count");
                distribution.put(department, count);
            }
        } catch (SQLException e) {
            System.err.println("获取院系分布失败: " + e.getMessage());
        }
        
        return distribution;
    }
    
    // 获取热门课程
    public static List<Map<String, Object>> getTopCourses() {
        List<Map<String, Object>> topCourses = new ArrayList<>();
        String sql = "SELECT course_name, teacher, department, capacity, enrolled FROM courses ORDER BY enrolled DESC LIMIT 10";
        
        try (Connection conn = DriverManager.getConnection(DB_URL);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                Map<String, Object> course = new HashMap<>();
                course.put("courseName", rs.getString("course_name"));
                course.put("teacher", rs.getString("teacher"));
                course.put("department", rs.getString("department"));
                course.put("capacity", rs.getInt("capacity"));
                course.put("enrolled", rs.getInt("enrolled"));
                topCourses.add(course);
            }
        } catch (SQLException e) {
            System.err.println("获取热门课程失败: " + e.getMessage());
        }
        
        return topCourses;
    }
    
    // 保存课程
    public static void saveCourse(Course course) {
        String sql = "INSERT INTO courses (course_code, course_name, credit, teacher, department, capacity, enrolled, class_time, class_room, course_type, semester) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        try (Connection conn = DriverManager.getConnection(DB_URL);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, course.getCourseCode());
            pstmt.setString(2, course.getCourseName());
            pstmt.setDouble(3, course.getCredit());
            pstmt.setString(4, course.getTeacher());
            pstmt.setString(5, course.getDepartment());
            pstmt.setInt(6, course.getCapacity());
            pstmt.setInt(7, course.getEnrolled());
            pstmt.setString(8, course.getClassTime());
            pstmt.setString(9, course.getClassRoom());
            pstmt.setString(10, course.getCourseType());
            pstmt.setString(11, course.getSemester());
            
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.err.println("保存课程失败: " + e.getMessage());
        }
    }
    
    // 清空课程数据
    public static void clearCourses() {
        String sql = "DELETE FROM courses";
        
        try (Connection conn = DriverManager.getConnection(DB_URL);
             Statement stmt = conn.createStatement()) {
            
            stmt.executeUpdate(sql);
            System.out.println("课程数据清空成功");
        } catch (SQLException e) {
            System.err.println("清空课程数据失败: " + e.getMessage());
        }
    }
}