JDBC操作PostgreSQL查询成绩无结果且菜单无响应问题求助

问题背景 #

基于PostgreSQL数据库的Java项目，需要通过学生编号am和课程代码查询课程最终成绩。手动执行SQL可得到正确结果，但Java代码调用时无响应且无法返回正确结果，硬编码参数时代码正常运行，推测问题出在带空格的课程代码（如ΑΓΓ 101）的输入处理上。

手动执行的正确SQL #

SELECT final_grade
FROM "Register"
JOIN "Student" USING (amka) 
WHERE am ='2020000158'
AND course_code ='ΑΓΓ 101';

相关Java代码 #

查询方法 #

public void showGrades(String am, String course_code) {
    
    try {
        Statement st = conn.createStatement();
          
            ResultSet rst = st.executeQuery( "SELECT final_grade \r\n"
                                          + "FROM \"Register\" \r\n"
                                          + "JOIN \"Student\" USING (amka)  \r\n"
                                          + "WHERE am ='"+am+"'\r\n"
                                          + "AND course_code ='"+course_code.trim()+"';");  
            if(rst.next()) { 
            System.out.println("The final grade is: "+rst.getBigDecimal(1));
           }
            else
                System.out.println("Final grade not found!");
            
        rst.close();
    } catch (SQLException e) {
        
        e.printStackTrace();
    }   
    
}

主函数菜单部分 #

public static void main(String[] args) {
    
    PhaseB phB = new PhaseB();
    try (Scanner sc = new Scanner(System.in)) {
        phB.dbConnect("localhost", "Project_2023", "postgres", "asdf2332fdsa"); 
        
        while(true) {
            phB.menu();
            int i = sc.nextInt();
            if(i==1)
            {
                System.out.println("Enter A.M.: ");
                String am = sc.next();
                System.out.println("Enter course code: ");
                String course_code = sc.next();
                phB.showGrades(am, course_code);
            }
            else if(i==3) {
                
            }
            else if(i==4) {
                System.out.println("Enter A.M.:");
                String am = sc.next();
                System.out.println("The detailed grades for student with A.M.: '"+am+"' are:");
                phB.showDetailedGrades(am);
            }
            else
                break;
        }
    }

}

涉及的表结构 #

Student表 #

CREATE TABLE IF NOT EXISTS public."Student"
(
    amka character varying COLLATE pg_catalog."default" NOT NULL,
    am character(10) COLLATE pg_catalog."default",
    entry_date date,
    CONSTRAINT "Student_pkey" PRIMARY KEY (amka),
    CONSTRAINT "Student_am_key" UNIQUE (am),
    CONSTRAINT student_fkey1 FOREIGN KEY (amka)
        REFERENCES public."Person" (amka) MATCH SIMPLE
        ON UPDATE RESTRICT
        ON DELETE RESTRICT
        NOT VALID
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."Student"
    OWNER to postgres;

Register表 #

CREATE TABLE IF NOT EXISTS public."Register"
(
    amka character varying COLLATE pg_catalog."default" NOT NULL,
    serial_number integer NOT NULL,
    course_code character(7) COLLATE pg_catalog."default" NOT NULL,
    exam_grade numeric,
    final_grade numeric,
    lab_grade numeric,
    register_status register_status_type,
    CONSTRAINT "Register_pkey" PRIMARY KEY (course_code, serial_number, amka),
    CONSTRAINT "Register_course_run_fkey" FOREIGN KEY (course_code, serial_number)
        REFERENCES public."CourseRun" (course_code, serial_number) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT "Register_student_fkey" FOREIGN KEY (amka)
        REFERENCES public."Student" (amka) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        NOT VALID
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."Register"
    OWNER to postgres;

问题分析与修复方案 #

核心问题1：Scanner输入截断 #

sc.next()会以空格为分隔符读取输入，当输入带空格的课程代码（如ΑΓΓ 101）时，只会读取到ΑΓΓ，剩余的101留在输入缓冲区，导致程序后续逻辑卡住无响应。

核心问题2：固定长度字符类型不匹配 #

Register表的course_code是character(7)固定长度类型，存储时会自动补空格到7位；而Java输入的字符串若未补全空格，会导致查询时字符串不匹配。

核心问题3：SQL字符串拼接风险 #

原代码使用Statement拼接SQL，不仅存在SQL注入风险，还可能因特殊字符（如空格、希腊字母）导致匹配失败。

修复后的代码 #

修改主函数输入处理

public static void main(String[] args) {
    PhaseB phB = new PhaseB();
    try (Scanner sc = new Scanner(System.in)) {
        phB.dbConnect("localhost", "Project_2023", "postgres", "asdf2332fdsa"); 
        
        while(true) {
            phB.menu();
            int i = sc.nextInt();
            sc.nextLine(); // 清理nextInt()遗留的换行符
            
            if(i==1)
            {
                System.out.println("Enter A.M.: ");
                String am = sc.nextLine();
                System.out.println("Enter course code: ");
                String course_code = sc.nextLine().trim();
                // 补全到7位，匹配表中character(7)类型
                course_code = String.format("%-7s", course_code).substring(0,7);
                phB.showGrades(am, course_code);
            }
            else if(i==3) {
                // 保留原有逻辑
            }
            else if(i==4) {
                System.out.println("Enter A.M.:");
                String am = sc.nextLine();
                System.out.println("The detailed grades for student with A.M.: '"+am+"' are:");
                phB.showDetailedGrades(am);
            }
            else
                break;
        }
    }
}

修改查询方法为PreparedStatement

public void showGrades(String am, String course_code) {
    String sql = "SELECT final_grade "
               + "FROM \"Register\" "
               + "JOIN \"Student\" USING (amka) "
               + "WHERE am = ? "
               + "AND course_code = ?";
    
    try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setString(1, am);
        pstmt.setString(2, course_code);
        
        try (ResultSet rst = pstmt.executeQuery()) {
            if(rst.next()) { 
                System.out.println("The final grade is: "+rst.getBigDecimal(1));
            } else {
                System.out.println("Final grade not found!");
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }   
}

修复说明 #

1. 用sc.nextLine()读取完整的带空格输入，避免截断；同时清理nextInt()后的换行符，防止读取空字符串。
2. 将课程代码补全到7位，匹配数据库中character(7)的固定长度，确保查询字符串完全一致。
3. 使用PreparedStatement避免SQL注入，同时保证特殊字符（如希腊字母、空格）的正确传递。

内容的提问来源于stack exchange，提问作者DilligentSlacker