java - 尽管我已经尝试过 SQL 中的代码，但我在 WHERE 子句中遇到错误

Question

我遇到了一个非常奇怪的错误，当我运行连接到 SQL 的程序时，出现一个错误，其中显示

java.sql.SQLSyntaxErrorException: Syntax error: Encountered "WHERE" at line 1, column 40.

其他 SQL 连接中的代码工作正常，但是这个的行为非常不同，我将下面不起作用的 SQL 连接分离出来。

请帮我解决这个问题，这已经让我头疼了两天了

public void pintransferfunds() {

    //PIN FRAME
    enterpinframe = new JFrame("Enter Pin");
    enterpinframe.setSize(WIDTH, HEIGHT);
    enterpinframe.setVisible(true);
    enterpinframe.setResizable(false);
    enterpinframe.setLocationRelativeTo(null);
    enterpinframe.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    //PIN PANEL  
    enterpinbg = new JPanel();
    enterpinbg.setSize(WIDTH, HEIGHT);
    enterpinbg.setBackground(Color.orange);
    enterpinbg.setVisible(true);
    enterpinbg.setLayout(null);
    enterpinframe.add(enterpinbg);
    JPasswordField inputpin = new JPasswordField();
     inputpin.setFont(new Font("Consolas", Font.BOLD, 20));
     inputpin.setForeground(Color.gray);

     //Setbounds
        inputpin.setBounds(250, 550, 100, 40);

    //tester + image
    enteryourpin = new ImageIcon(getClass().getResource("Enter yout    PIN.png"));
    tester = new JLabel(enteryourpin);
    tester.setBounds(50, 50, 500, 500);

    //enterpin Add
    enterpinbg.add(tester);
    enterpinbg.add(inputpin);
     
    //exit and option button
    OpEx();
    this.dispose();
    inputpin.addKeyListener(new KeyListener() {
        @Override

        public void keyTyped(KeyEvent e) {
        }
        @Override

        public void keyPressed(KeyEvent e) {
            if (e.getKeyCode() == (KeyEvent.VK_ENTER)) {

                String pinx = inputpin.getText();
                Connection con = null;
                Statement stmt = null;
                try {

                    Class.forName(JDBC_DRIVER1);
                    Class.forName(JDBC_DRIVER2);
                    con = DriverManager.getConnection(URL, USER, PASS);
                    stmt = con.createStatement();
                    String sql = "SELECT*FROM TBL_BANK WHERE PIN ='" + pinx + "' ";
                    ResultSet rs = stmt.executeQuery(sql);
                    while (rs.next()) {
                        pindb = rs.getString("PIN");
                        account_name = rs.getString("ACCOUNT_NAME");
                        balance = rs.getString("BALANCE");
                        id =rs.getInt("ID");
                   
                    }
                    rs.close();

                } catch (Exception f) {
                    System.out.println(f);
                }

                //if Login Success and Failed
                if (pinx.equals(pindb)) {

                    //BALANCE
                    //PIN FRAME
                   frame = new JFrame("OptionFrame");
                    frame.setSize(WIDTH, HEIGHT);
                    frame.setVisible(true);
                    frame.setResizable(false);
                    frame.setLocationRelativeTo(null);
                    frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

                    //PIN PANEL  
                    panel = new JPanel();
                    panel.setSize(WIDTH, HEIGHT);
                    panel.setBackground(Color.cyan);
                    panel.setVisible(true);
                    panel.setLayout(null);
                    
                    username = new JLabel();
                    username.setBounds(0,0,300,50);
                    username.setText("          Welcome user:"+account_name);
                  panel.add(username);
                    
                    pic = new ImageIcon(getClass().getResource("TranceForm(Sending money).png"));
                    pictester= new JLabel(pic);
                    pictester.setBounds(50,50, 500,500);
                    panel.add(pictester);
                    //Enter amount text
                    JLabel transferFunds = new JLabel("Enter Amount Php:");
                    transferFunds.setBounds(50,400,200,200);
                    transferFunds.setFont(new Font("Century Gothic",Font.BOLD,20));
                    //Enter receiver Id text
                    JLabel receiverId = new JLabel("Enter Receiver ID:");
                    receiverId.setBounds(50,450,200,200);
                    receiverId.setFont(new Font("Century Gothic",Font.BOLD,20));
                     
                    //Textfield Enter Amount Php
                    JTextField transferField = new JTextField();
                    transferField.setBackground(Color.white);
                    transferField.setBounds(250,490,200,30);
                    
                    //TextField Enter receiver Id
                     JTextField receiverField = new JTextField();
                    receiverField.setBackground(Color.white);
                    receiverField.setBounds(250,540,200,30);
                    
                      amount =transferField.getText();
                      receive =receiverField.getText();
                
                    panel.add(receiverId);
                    panel.add(receiverField);
                    
                    panel.add(transferFunds);
                    panel.add(transferField);
                    
                        receiverField.addKeyListener(new KeyListener() {
                       @Override
                       public void keyTyped(KeyEvent e) {
                           
                       }

                       //HELP ME HERE IS WHERE IT DOESNT WORK

                       @Override
                       public void keyPressed(KeyEvent e) {                           
                        if(e.getKeyCode()==KeyEvent.VK_ENTER)
                        {
                              Connection con = null;
                              Statement stmt =null;
                          try
                          {
                               Class.forName(JDBC_DRIVER1);
                               Class.forName(JDBC_DRIVER2);
                              con= DriverManager.getConnection(URL,USER,PASS);
                               stmt=con.createStatement();
                               

                                String receivesql =("UPDATE TBL_BANK SET BALANCE = BALANCE+"+amount+" WHERE ID = "+receive+"");
                                String sendersql =("UPDATE TBL_BANK SET BALANCE= BALANCE-"+amount+" WHERE ACCOUNT_NAME='"+account_name+"'");
                             stmt.executeUpdate(receivesql);
                               stmt.executeUpdate(sendersql);
                               
                          }
                          catch(Exception x)
                          {
                              System.out.println(x);
                          }
                     //END
                              
         if(receiverField.getText().equals(id))
                              {
                               JOptionPane.showMessageDialog(null,"You CANNOT enter your own id");   
                              }
                              else
                              {
                               ImageIcon transImg = new ImageIcon(getClass().getResource("Transform (sending) is sucsseful!.png"));
                               JLabel transimgLoc= new JLabel(transImg);
                              transimgLoc.setBounds(50,50, 500,500);
                              panel.add(transimgLoc);
                              panel.remove(pictester);
                              panel.remove(receiverId);
                              panel.remove(receiverField);
                              panel.remove(transferFunds);
                              panel.remove(transferField);
                              panel.repaint();
                              }
                   
                       }
                       }

                       @Override
                       public void keyReleased(KeyEvent e) {
                         
                       }
                   });
                    
                    

                    frame.add(panel);
                     OpExtrue();
                    enterpinframe.dispose();

                } else if (pinx != (pindb)) {
                    x++;
                    if (x <= 7) {

                        //LoginFailed
                        //if Failed to Login 3 times image icon will change to cop
                        enterpinbg.remove(tester);
                        pinwrong = new ImageIcon(getClass().getResource("PIN Wrong (1_2Times).png"));
                         pinwrongtester = new JLabel(pinwrong);
                         pinwrongtester.setBounds(50, 50, 500, 500);

                        inputpin.setText("");

                        //ADD Frame
                        enterpinbg.add(pinwrongtester);

                        System.out.println(x);
                        enterpinbg.repaint();

                    } else if (x >=8) {
                        enterpinbg.removeAll();
                        
                        enterpinbg.add(inputpin);
                        inputpin.setText("");
                        System.out.println(x);
                        pinwronger = new ImageIcon(getClass().getResource("PIN Wrong2 (Many times).png"));
                        pinwrongertester = new JLabel(pinwronger);

                        pinwrongertester.setBounds(50, 50, 500, 500);
                        enterpinbg.add(pinwrongertester);
                          enterpinbg.add(panelOpEx);
                        enterpinbg.repaint();
                    }
                }
            }
        }

        @Override

        public void keyReleased(KeyEvent e) {

        }

    });

}

Answer 1

试试这个，

    String receivesql = "UPDATE TBL_BANK SET BALANCE = BALANCE + " + amount + " WHERE ID = " + receive + ";";

    String sendersql = "UPDATE TBL_BANK SET BALANCE = BALANCE - " + amount + " WHERE ACCOUNT_NAME = '" + account_name + "';";

    stmt.addBatch(receivesql);
    stmt.addBatch(sendersql);     
    stmt.executeBatch();

您也可以尝试此操作，因为您同时执行两个 SQL 查询，

Connection con = null;
Statement stmt = null;
try
{
    Class.forName(JDBC_DRIVER1);
    Class.forName(JDBC_DRIVER2);
    con = DriverManager.getConnection(URL,USER,PASS);
    con.setAutoCommit(false);
    stmt = con.createStatement();

    String receivesql = "UPDATE TBL_BANK SET BALANCE = BALANCE + " + amount + " WHERE ID = " + receive + ";";

    String sendersql = "UPDATE TBL_BANK SET BALANCE = BALANCE - " + amount + " WHERE ACCOUNT_NAME = '" + account_name + "';";

    stmt.executeUpdate(receivesql);
    stmt.executeUpdate(sendersql);

    con.commit();
} catch(Exception x)
{
    System.out.println(x);
}