java 连接 access 后插入“insert”语句出错

错误点：insert 语句出错

• 错误句： stmt.execute(“insert into stu (姓名,院系,出生日期,平均成绩,性别) values(‘东田’,‘能动学院’,#1992-3-4#,‘20’,‘男’)”);
• 这里面的 insert into stu (姓名,院系,出生日期,平均成绩,性别) values(‘东田’,‘能动学院’,#1992-3-4#,‘20’,‘男’) 在 access 中是完全正确的，可以正常运行。但是到了 java 中却报错了。
• 多次尝试之后发现，时间的格式是出错的原因，需要把两个“#”都去掉。（其实 access 中时间格式的数据添加也可以不加 # 。。。）
• 修改后的语句： stmt.execute(“insert into stu (姓名,院系,出生日期,平均成绩,性别) values(‘东田’,‘能动学院’,1992-3-4,‘20’,‘男’)”); 可以正常运行

解决的办法

• 原程序中有 try{} catch{} 语句， catch (SQLException se)，这里可以使用 System.out.print(se.getMessage()); 来输出错误的详细详细，比如上面这个错误的详细报错详细是：Syntax error: Stopped parse at #1992-3-4#,‘20’,‘男’)(id14) 可以得出错误是从 “#1992-3-4#”这里开始的,然后再去修改这个格式

package dbPractice;

import java.sql.*;
import java.util.Scanner;

public class dbPracrtice {
    dbPracrtice() {
        try {
            Class.forName("com.hxtt.sql.access.AccessDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        String url = "jdbc:Access:///E:/stu.accdb";
        String username = "";
        String password = "";
        Scanner sc = new Scanner(System.in);
        String flag = new String();
        try {
            Connection conn = DriverManager.getConnection(url, username, password);
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * from stu");
            // 显示原始数据
            while (rs.next()) {
                System.out.print(rs.getString(1));
                System.out.print(" ");
                System.out.print(rs.getString(2));
                System.out.print(" ");
                System.out.print(rs.getDate(3));
                System.out.print(" ");
                System.out.print(rs.getString(4));
                System.out.print(" ");
                System.out.print(rs.getString(5));
                System.out.print("\n");
            }

            // 插入操作
            System.out.print("\n现在插入？Y/N");
            flag = sc.next();
            if (flag.equals("Y") || flag.equals("y")) {
                stmt.execute("insert into stu (姓名,院系,出生日期,平均成绩,性别) values('东田','能动学院',1992-3-4,'20','男')");
                stmt.executeUpdate("insert into stu values('冬梅','人文学院',1998-2-28,'20','女')");
                System.out.print("插入后的结果为：\n");
                rs = stmt.executeQuery("select * from stu");
                while (rs.next()) {
                    System.out.print(rs.getString(1));
                    System.out.print(" ");
                    System.out.print(rs.getString(2));
                    System.out.print(" ");
                    System.out.print(rs.getDate(3));
                    System.out.print(" ");
                    System.out.print(rs.getString(4));
                    System.out.print(" ");
                    System.out.print(rs.getString(5));
                    System.out.print("\n");
                }
            }
            // 查询操作
            System.out.print("\n显示所有男生记录？Y/N");
            flag = sc.next();
            if (flag.equals("Y") || flag.equals("y")) {
                rs = stmt.executeQuery("select * from stu where 性别 = '男'");
                while (rs.next()) {
                    System.out.print(rs.getString(1));
                    System.out.print(" ");
                    System.out.print(rs.getString(2));
                    System.out.print(" ");
                    System.out.print(rs.getDate(3));
                    System.out.print(" ");
                    System.out.print(rs.getString(4));
                    System.out.print(" ");
                    System.out.print(rs.getString(5));
                    System.out.print("\n");
                }
            }
//            ResultSet rs = stmt.executeQuery();
            // 更新数据
            System.out.print("\n现在更新成绩？Y/N");
            flag = sc.next();
            stmt.execute("update stu set 平均成绩=平均成绩*0.7+30 where 院系='控计学院'");
            if (flag.equals("Y") || flag.equals("y")) {
                rs = stmt.executeQuery("select * from stu");
                while (rs.next()) {
                    System.out.print(rs.getString(1));
                    System.out.print(" ");
                    System.out.print(rs.getString(2));
                    System.out.print(" ");
                    System.out.print(rs.getDate(3));
                    System.out.print(" ");
                    System.out.print(rs.getString(4));
                    System.out.print(" ");
                    System.out.print(rs.getString(5));
                    System.out.print("\n");
                }
            }
            // 删除数据
            System.out.print("\n现在删除不及格学生数据？Y/N");
            flag = sc.next();
            stmt.execute("delete from stu where 平均成绩<60");
            if (flag.equals("Y") || flag.equals("y")) {
                rs = stmt.executeQuery("select * from stu");
                while (rs.next()) {
                    System.out.print(rs.getString(1));
                    System.out.print(" ");
                    System.out.print(rs.getString(2));
                    System.out.print(" ");
                    System.out.print(rs.getDate(3));
                    System.out.print(" ");
                    System.out.print(rs.getString(4));
                    System.out.print(" ");
                    System.out.print(rs.getString(5));
                    System.out.print("\n");
                }
            }
            conn.close();
        } catch (SQLException se) {

            System.out.print(se.getMessage());
        }
    }

    public static void main(String[] args) {
        new dbPracrtice();
    }
}