java - 通过java在数据库中插入数据时在where子句中获取SQL异常

Question

我想使用 java 存储所需 ID 的密码。一切正常，除了我得到这个异常

"SQL Exception thrown: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(Pass_word) set Pass_word = 'pass' where ID = 2' at line 1".

我只在更新查询中遇到此异常，在选择查询中却没有。我正在使用 Eclipse。谁能告诉我我在做什么错了？

 import java.sql.DriverManager;
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Scanner;

    public class information {

        public static void main(String[] args) {
             String password;
                ResultSet rs;
                String queryString;
                int x=1;
            try
        {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = null;
            conn = DriverManager.getConnection("jdbc:mysql://localhost/onlineexam","root", "batch12@nitap");
            System.out.print("Database is connected !");
            Statement stmt = conn.createStatement();
            PreparedStatement pstmt = null;
            while(x==1)
            {
                System.out.println("Press 1 to enter student id");
                System.out.println("Press 2 to exit");
                Scanner s= new Scanner(System.in);
                int choice = s.nextInt();
                switch(choice)
                {
                case 1: System.out.println("Enter the ID of student");
                        int id = s.nextInt();

                        queryString = "select ID,Roll_no, Course_name, Course_code, Date,Time from student_reg where ID=" +id;
                        rs= stmt.executeQuery(queryString);
                        //System.out.println(rs.getInt("ID"));
                        while(rs.next())
                        {
                        if(rs.getInt("ID")== id)
                        {
                            String roll = rs.getString("Roll_no");
                            String date = rs.getString("Date");
                            String time =  rs.getString("Time");
                            String c_name = rs.getString("Course_name");
                            String c_code = rs.getString("Course_code");
                            password pass1= new password(roll,date,time,c_name,c_code);
                            pass= pass1.passwd();
                            System.out.println(pass);
                            queryString =" Update student_reg(Pass_word) set Pass_word = 'pass' where ID = ?";
                            //queryString= "INSERT INTO student_reg(Password) VALUES ('password') where ID = ?";
                            //stmt.executeUpdate(queryString);
                            //PreparedStatemenet pstmt = conn.preparedStatement("INSERT INTO student_reg(Password) VALUES ('password') where ID = ?");
                            //pstmt.setLong(1, id);

                            pstmt = conn.prepareStatement(queryString);

                            pstmt.setInt(1, id);
                            int numberOfUpdatedRecords = pstmt.executeUpdate();
                            s.close();
                        }
                        }
                        break;
                case 2: x=0;
                }
            }

            if(conn!= null)
            {
                stmt.close();
                pstmt.close();
            conn.close();
            conn = null;
            }
        }
            catch(ClassNotFoundException cnf)
            {
                 System.out.println("Driver could not be loaded: " + cnf);
            }
        catch(SQLException sqle)
        {
            System.out.println("SQL Exception thrown: " + sqle);
        }
        catch(Exception e)
        {
        System.out.print("Do not connect to DB - Error:"+e);
        }

        }

    }

Answer 1

你的代码有很多问题:

queryString = "select ID,Roll_no, Course_name, Course_code, Date,Time from student_reg where ID= id";

这一行你有条件但是你还没有设置值，你应该设置

queryString = "select ID,Roll_no, Course_name, Course_code, Date,Time from student_reg where ID = " + id;

最好看看PreparedStatement也用于防止 SQL 注入(inject)。

最后一个:

queryString= "INSERT INTO student_reg(Password) VALUES ('password') where ID = id";

这一行似乎你想更新一些东西。请审核。