Statement.executeQuery() throws java.sql.SQLSyntaxErrorException when executing SELECT command in MySQL

Question

I have a method that fetches records from MySQL db table using JDBC API. The command that I have been using is:

"SELECT column_1, column_2, ... FROM table;"

The column names are provided to the method in form of ArrayList a query is being constructed based on the column & table names using StringBuilder.

When executing the constructed query using createStatement(), it is throwing: "java.sql.SQLSyntaxErrorException: 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 from $tableName' at line 1"

This is the full code of the method:

public ObservableList<ObservableList<String>> getTableData(String tableName, List<String> selectedParams, int rowCount) {
 try {
 StringBuilder query = new StringBuilder("select ");
 for (int i = 0; i < selectedParams.size(); i++) {
 query.append(selectedParams.get(i)).append(", ");
 }
 query.append("\b\b from ").append(tableName).append(" limit ").append(rowCount);
 System.out.println("query:" + query);
 ObservableList<ObservableList<String>> rows;
 try (ResultSet rset = st.executeQuery(query.toString())) {
 ResultSetMetaData rsmd = rset.getMetaData();
 rows = FXCollections.observableArrayList();
 while (rset.next()) {
 ObservableList<String> row = FXCollections.observableArrayList();
 int count = 1;
 while (count <= rsmd.getColumnCount()) {
 row.add(rset.getString(count));
 count++;
 }
 rows.add(row);
 }
 }
 rows.forEach((row) -> {
 System.out.println(row);
 });
 return rows;
 } catch (SQLException ex) {
 Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
 }
 return null;
 }

When running the code, the generated query form print statement looks something like this:

"query:select territory from offices"

I have tested this query against db directly and another simple JDBC program and they both run fine. Except for in this method. Please help.

---------------Edit: forgot to paste the exception message:---------------

java.sql.SQLSyntaxErrorException: 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 from offices limit 10' at line 1
 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
 at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
 at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1218)

• Mark Rotteveel: Notice that your select list ends with a , in the code as shown. That is likely the cause of the problem. Also, why do you have \b in your string? That likely doesn't work like you think it does. Try deleting the trailing , from the StringBuilder instead.
• ShashiKanth Chill: I'm sorry, I forgot to paste the exception message. I have edited the question. The query doesn't seems to end with a ",". I have used "\b\b" to remove the extra comma and extra space after the last column name has appended to the string.
• ShashiKanth Chill: @MarkRotteveel you were right. It was "\b\b" that was creating the issue. Thanks for pointing it out.

Answer 1

issue id in

 for (int i = 0; i < selectedParams.size(); i++) {
 query.append(selectedParams.get(i)).append(", ");
 }

the above code will append an extra ',' after param names instead you can use

query.append(String.join(", ", selectedParams))

• ShashiKanth Chill: This made my spaghetti code lot cleaner. Thank you! :)

Answer 2

The problem is that

query.append("\b\b from ")

does not remove the trailing comma and space from the StringBuilder. It may look like it does when you print it to the console, but the string actually does contain

select territory, ␈␈ from offices

and MySQL apparently doesn't like that.

Instead, you want to actually delete the comma from the StringBuilder (and leave the space):

query.deleteCharAt(query.length() - 2).append("from ")