java - 使用 Tomcat 和 MySQL 的 RuntimeException

Question

我无法解决这个问题!

java.lang.RuntimeException: 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
'SELECT user_id FROM user WHERE USER_NAME ='test';'' at line 1

你能帮帮我吗？我不知道，可能出了什么问题!语法没问题，不是吗？

Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
DataSource ds = (DataSource) envCtx.lookup("jdbc/onlinebanking");
connection = ds.getConnection();
Statement statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
resultSetKunden_id = statement.executeQuery(kunde_id);
resultSetKontostand = statement.executeQuery(kontostand);
resultSetTest = statement.executeQuery(test);
ResultSetMetaData metaData = resultSet.getMetaData();

这是 SQL 语句:

String sql = "select tr.EMPFÄNGER_KONTO_ID, tr.BETRAG " +
"from TRANSAKTION tr, KONTO ko, KUNDE ku " +
"where tr.KONTO_ID = ko.KONTO_ID " +
"and ko.KUNDE_ID = ku.KUNDE_ID " +
"and ku.KUNDE_NAME = '" + username +"' " +
"and ku.KUNDE_PASSWORD = '" + password +"';";

kunde_id = "SELECT kunde_id FROM kunde WHERE KUNDE_NAME ='" + username + "';";
String test = "SELECT * FROM kunde WHERE KUNDE_NAME ='" + username + "';";
kontostand = "Select betrag From Transaktion Where Konto_id = " + kunde_id + "';";

(有些词是德语)

异常(exception)情况:

java.lang.RuntimeException: 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 'Janssen''' at line 1
de.java2enterprise.onlinebanking.HalloWeltServlet.getData(HalloWeltServlet.java:112)
de.java2enterprise.onlinebanking.HalloWeltServlet.doGet(HalloWeltServlet.java:32)
javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)

谢谢!

Answer 1

我建议使用准备好的语句甚至 ORM 框架。

但是要回答你的问题，你应该替换:

kontostand = "Select betrag From Transaktion Where Konto_id = '" + kunde_id + "'";

与

kontostand = "Select betrag From Transaktion Where Konto_id = (" + kunde_id + ")";
                                                              ^                ^