sql语法错误

cu6pst1q 于 2021-07-03 发布在 Java

关注(0)|答案(2)|浏览(454)

下面是当用户单击ApplyBalance时的代码。这适用于第一部分，用户的余额更新很好，但当我尝试执行第二条语句时，会出现sql语法错误。是什么导致了问题？

public void mouseClicked(MouseEvent e) {
            if (cal == true) {
                try {
                int balchange = updatebal;
                String username = (String) userPicker.getSelectedItem();
                Connection conn = DriverManager.getConnection( Host, Name, Pass );  
                PreparedStatement pst = conn.prepareStatement("UPDATE table_1 SET user_bal='"+balchange+"' WHERE user_name='"+username+"'");
                pst.execute();

                String sign = "£";
                String PayName = textField_1.getText();
                PreparedStatement pst2 = conn.prepareStatement("INSERT INTO payment_info (payment_name, payment_amount, payment_date, username)"+" VALUES ('"+PayName+"', '"+sign+balchange+"', '"+Date+"', '"+username+"'");
                pst2.execute();
                cal = false;
                } 
                catch (Exception e3) {
                e3.printStackTrace();
                }
            }
            else {
                JOptionPane.showMessageDialog(null, "Please use the Calculator First!");
            }
        }

下面是我运行这个时得到的堆栈跟踪。

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 '' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
at com.mysql.jdbc.Util.getInstance(Util.java:387)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192)
at AdminPanelMain$7.mouseClicked(AdminPanelMain.java:444)
at java.awt.Component.processMouseEvent(Component.java:6538)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3324)
at java.awt.Component.processEvent(Component.java:6300)
at java.awt.Container.processEvent(Container.java:2236)
at java.awt.Component.dispatchEventImpl(Component.java:4891)
at java.awt.Container.dispatchEventImpl(Container.java:2294)
at java.awt.Component.dispatchEvent(Component.java:4713)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4888)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4534)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4466)
at java.awt.Container.dispatchEventImpl(Container.java:2280)
at java.awt.Window.dispatchEventImpl(Window.java:2750)
at java.awt.Component.dispatchEvent(Component.java:4713)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)
at java.awt.EventQueue.access$500(EventQueue.java:97)
at java.awt.EventQueue$3.run(EventQueue.java:709)
at java.awt.EventQueue$3.run(EventQueue.java:703)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:86)
at java.awt.EventQueue$4.run(EventQueue.java:731)
at java.awt.EventQueue$4.run(EventQueue.java:729)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)

Java sql mysql jdbc

来源：https://stackoverflow.com/questions/37141068/sql-syntax-error-when-inserting-new-row

2条答案

按热度按时间

wbrvyc0a1#

您的第二个查询缺少右括号 Values 条款。
使用参数化查询，而不是直接在查询中附加参数。

public void mouseClicked(MouseEvent e) {
    if (cal == true) {
        try {
        int balchange = updatebal;
        String username = (String) userPicker.getSelectedItem();
        Connection conn = DriverManager.getConnection( Host, Name, Pass );  
        PreparedStatement pst = conn.prepareStatement("UPDATE table_1 SET user_bal=? WHERE user_name=?");

        pst.setInt(1, balchange);
        pst.setString(2, username);

        pst.execute();

        String sign = "£";
        String PayName = textField_1.getText();
        PreparedStatement pst2 = conn.prepareStatement("INSERT INTO payment_info (payment_name, payment_amount, payment_date, username)"
        + " VALUES (?, ?, ?, ?)");

        pst2.setString(1, PayName);
        pst2.setString(2, sign + balchange);
        pst2.setString(3, "Date");//if it's date column use ps2.setDate(3, new Date());
        pst2.setString(4, username);

        pst2.execute();
        cal = false;
        } 
        catch (Exception e3) {
        e3.printStackTrace();
        }
    }
    else {
        JOptionPane.showMessageDialog(null, "Please use the Calculator First!");
    }
}

这看起来会更干净，更容易写。最重要的是，它将使您免受sql注入攻击。
以下是用于参数化查询的oracle文档https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

赞(0）回复(0）举报 2021-07-03

vuktfyat2#

看看这里：

PreparedStatement pst2 = conn.prepareStatement("INSERT INTO payment_info (payment_name, payment_amount, payment_date, username)"+" VALUES ('"+PayName+"', '"+sign+balchange+"', '"+Date+"', '"+username+"'")

似乎在sql语句中缺少正确的括号，应该是 VALUES() 不是 VALUES( .
顺便说一下，有几种orm系统，例如 mybatis 或者 hibernate ，为什么不选一个呢？它们不仅可以帮助您减少工作量，还可以方便地访问您的数据库。

赞(0）回复(0）举报 2021-07-03

我来回答

sql语法错误

2条答案

相关问题

热门标签

最新问答