解决com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
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 'where userid='j2ee'' at line 1
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
at mysql.connector.java@5.1.41-bin/com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at mysql.connector.java@5.1.41-bin/com.mysql.jdbc.Util.getInstance(Util.java:408)
at mysql.connector.java@5.1.41-bin/com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
at mysql.connector.java@5.1.41-bin/com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at mysql.connector.java@5.1.41-bin/com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at mysql.connector.java@5.1.41-bin/com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
at mysql.connector.java@5.1.41-bin/com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at mysql.connector.java@5.1.41-bin/com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2501)
at mysql.connector.java@5.1.41-bin/com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
at mysql.connector.java@5.1.41-bin/com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1966)
at com.a51work6.jpetstore.dao.mysql.AccountDaolmp.findById(AccountDaolmp.java:33)
at com.a51work6.jpetstore.ui.LoginFrame.lambda$0(LoginFrame.java:65)
at java.desktop/javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1967)
at java.desktop/javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2308)
at java.desktop/javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:405)
at java.desktop/javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:262)
at java.desktop/javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:279)
at java.desktop/java.awt.Component.processMouseEvent(Component.java:6632)
at java.desktop/javax.swing.JComponent.processMouseEvent(JComponent.java:3342)
at java.desktop/java.awt.Component.processEvent(Component.java:6397)
at java.desktop/java.awt.Container.processEvent(Container.java:2263)
at java.desktop/java.awt.Component.dispatchEventImpl(Component.java:5008)
at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2321)
at java.desktop/java.awt.Component.dispatchEvent(Component.java:4840)
at java.desktop/java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4918)
at java.desktop/java.awt.LightweightDispatcher.processMouseEvent(Container.java:4547)
at java.desktop/java.awt.LightweightDispatcher.dispatchEvent(Container.java:4488)
at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2307)
at java.desktop/java.awt.Window.dispatchEventImpl(Window.java:2762)
at java.desktop/java.awt.Component.dispatchEvent(Component.java:4840)
at java.desktop/java.awt.EventQueue.dispatchEventImpl(EventQueue.java:772)
at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:721)
at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:715)
at java.base/java.security.AccessController.doPrivileged(AccessController.java:389)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:85)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:95)
at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:745)
at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:743)
at java.base/java.security.AccessController.doPrivileged(AccessController.java:389)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:85)
at java.desktop/java.awt.EventQueue.dispatchEvent(EventQueue.java:742)
at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:203)
at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:124)
at java.desktop/java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:113)
at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:109)
at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
at java.desktop/java.awt.EventDispatchThread.run(EventDispatchThread.java:90)
2. 问题分析
报这个错误时,一般都是自己代码中SQL语句写错了,我的代码如下:
conn=DBHelper.getConnection();
String sql="select userid, password,email,name,addr,userid,city,country,phone" //userid,password,email,name,addr,city,country,phone
+"from account where userid=?";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, id);
rs=pstmt.executeQuery();
它让在where userid='j2ee’附近找错误,我一直以为id有问题,结果最后发现是在进行sql字符串的连接时,中间少加一个空格。
3. 总结
看了别人的博客以及自己犯的错总结遇到这种类型报错时去检查的内容:
1、查看SQL语句的格式是否正确
2、查看SQL语句的所用到的标点符号是否为英文
3、特别注意SQL语句连接时,中间一定要添加空格