sql语法错误

cu6pst1q  于 2021-07-03  发布在  Java
关注(0)|答案(2)|浏览(483)

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

  1. public void mouseClicked(MouseEvent e) {
  2.             if (cal == true) {
  3.                 try {
  4.                 int balchange = updatebal;
  5.                 String username = (String) userPicker.getSelectedItem();
  6.                 Connection conn = DriverManager.getConnection( Host, Name, Pass );  
  7.                 PreparedStatement pst = conn.prepareStatement("UPDATE table_1 SET user_bal='"+balchange+"' WHERE user_name='"+username+"'");
  8.                 pst.execute();
  10.                 String sign = "£";
  11.                 String PayName = textField_1.getText();
  12.                 PreparedStatement pst2 = conn.prepareStatement("INSERT INTO payment_info (payment_name, payment_amount, payment_date, username)"+" VALUES ('"+PayName+"', '"+sign+balchange+"', '"+Date+"', '"+username+"'");
  13.                 pst2.execute();
  14.                 cal = false;
  15.                 } 
  16.                 catch (Exception e3) {
  17.                 e3.printStackTrace();
  18.                 }
  19.             }
  20.             else {
  21.                 JOptionPane.showMessageDialog(null, "Please use the Calculator First!");
  22.             }
  23.         }

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

  1. 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
  2. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
  3. at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
  4. at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
  5. at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
  6. at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
  7. at com.mysql.jdbc.Util.getInstance(Util.java:387)
  8. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939)
  9. at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)
  10. at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
  11. at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)
  12. at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
  13. at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551)
  14. at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
  15. at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192)
  16. at AdminPanelMain$7.mouseClicked(AdminPanelMain.java:444)
  17. at java.awt.Component.processMouseEvent(Component.java:6538)
  18. at javax.swing.JComponent.processMouseEvent(JComponent.java:3324)
  19. at java.awt.Component.processEvent(Component.java:6300)
  20. at java.awt.Container.processEvent(Container.java:2236)
  21. at java.awt.Component.dispatchEventImpl(Component.java:4891)
  22. at java.awt.Container.dispatchEventImpl(Container.java:2294)
  23. at java.awt.Component.dispatchEvent(Component.java:4713)
  24. at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4888)
  25. at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4534)
  26. at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4466)
  27. at java.awt.Container.dispatchEventImpl(Container.java:2280)
  28. at java.awt.Window.dispatchEventImpl(Window.java:2750)
  29. at java.awt.Component.dispatchEvent(Component.java:4713)
  30. at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)
  31. at java.awt.EventQueue.access$500(EventQueue.java:97)
  32. at java.awt.EventQueue$3.run(EventQueue.java:709)
  33. at java.awt.EventQueue$3.run(EventQueue.java:703)
  34. at java.security.AccessController.doPrivileged(Native Method)
  35. at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76)
  36. at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:86)
  37. at java.awt.EventQueue$4.run(EventQueue.java:731)
  38. at java.awt.EventQueue$4.run(EventQueue.java:729)
  39. at java.security.AccessController.doPrivileged(Native Method)
  40. at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76)
  41. at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)
  42. at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
  43. at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
  44. at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
  45. at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
  46. at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
  47. at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)
Javasqlmysqljdbc

2条答案

按热度按时间
wbrvyc0a

wbrvyc0a1#

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

  1. public void mouseClicked(MouseEvent e) {
  2.     if (cal == true) {
  3.         try {
  4.         int balchange = updatebal;
  5.         String username = (String) userPicker.getSelectedItem();
  6.         Connection conn = DriverManager.getConnection( Host, Name, Pass );  
  7.         PreparedStatement pst = conn.prepareStatement("UPDATE table_1 SET user_bal=? WHERE user_name=?");
  9.         pst.setInt(1, balchange);
  10.         pst.setString(2, username);
  12.         pst.execute();
  14.         String sign = "£";
  15.         String PayName = textField_1.getText();
  16.         PreparedStatement pst2 = conn.prepareStatement("INSERT INTO payment_info (payment_name, payment_amount, payment_date, username)"
  17.         + " VALUES (?, ?, ?, ?)");
  19.         pst2.setString(1, PayName);
  20.         pst2.setString(2, sign + balchange);
  21.         pst2.setString(3, "Date");//if it's date column use ps2.setDate(3, new Date());
  22.         pst2.setString(4, username);
  24.         pst2.execute();
  25.         cal = false;
  26.         } 
  27.         catch (Exception e3) {
  28.         e3.printStackTrace();
  29.         }
  30.     }
  31.     else {
  32.         JOptionPane.showMessageDialog(null, "Please use the Calculator First!");
  33.     }
  34. }

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

展开查看全部
赞(0）分享  回复(0）举报  2021-07-03
vuktfyat

vuktfyat2#

看看这里：

  1. 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
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com