使用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 + "';";

(有些词是德语的)

这是一个例外：

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 + ")";
                                                              ^                ^

Answer 2

不要将；(分号)放在查询中。

因此，您的查询将是：

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 + "'";