com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException を解決します。

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 [email protected]/com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at [email protected]/com.mysql.jdbc.Util.getInstance(Util.java:408)
	at [email protected]/com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
	at [email protected]/com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
	at [email protected]/com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
	at [email protected]/com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
	at [email protected]/com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
	at [email protected]/com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2501)
	at [email protected]/com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
	at [email protected]/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();

userid='j2ee'付近のエラーを探せということで、idがおかしいのかとずっと思っていたら、sql文字列の結合の際に真ん中のスペースが抜けていたことが判明しました。

3. まとめ
他の人のブログを読んだり、自分で失敗したりした結果、この手のエラーに遭遇したときに確認すべきことのまとめは。
1. SQL文の書式が正しいかどうか確認する
2、SQL文に使用されている句読点が英語であるかどうかを確認する
3. <マーク SQL文の接続に特に注意し、間にスペースを追加する必要があります。