Error: MySQLSyntaxErrorException in a java-based web app

Question

public List<User> arrangeUsers(String option) {
    String query = "";
    if (option.equals("lastNameAsc")) {
        query = "SELECT * FROM ? ORDER BY lastName ASC;";
    } else if (option.equals("lastNameDesc")) {
        query = "SELECT * FROM ? ORDER BY lastName DESC;";
    } else if (option.equals("dobAsc")) {
        query = "SELECT * FROM ? ORDER BY dateOfBirth ASC;";
    } else if (option.equals("dobDesc")) {
        query = "SELECT * FROM ? ORDER BY dateOfBirth DESC;";
    }
    boolean listChoice;
    try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            PreparedStatement stmt = conn.prepareStatement(query);) {
        if (searchList.size() > 0) {
            stmt.setString(1, "search_results");
            searchList.clear();
            listChoice = true;
        } else {
            stmt.setString(1, "users");
            users.clear();
            listChoice = false;
        }
        ResultSet result = stmt.executeQuery();
        while (result.next()) {
            User user = new User();
            user.setFirstName(result.getString("firstName"));
            user.setLastName(result.getString("lastName"));
            user.setDob(result.getDate("dateOfBirth"));
            user.setPhoneNumber(result.getString("phoneNumber"));
            user.setEmail(result.getString("email"));
            user.setUserID(result.getInt("id"));
            if (listChoice) {
                searchList.add(user);
            } else {
                users.add(user);
            }

        }
    } catch (SQLException sqle) {
        System.err.println(sqle);
        sqle.printStackTrace();
        return null;
    }
    if (listChoice) {
        return searchList;
    } else {
        return users;
    }
}

The above method returns an error:

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 ''users' ORDER BY lastName DESC' at line 1 " error.

The method is supposed to arrange a list of users (a table) according to the order required by the client. Any suggestions what is the reason for the error message? I am ready to provide any additional details if necessary.

How can I fix that , and still use one method for two tables? — Borislava, Oct 13 '16 at 11:27

score 0 · Accepted Answer · answered Oct 13 '16 at 11:42

A table name can't be used as a parameter. It must be hardcoded. You can do something like:

public List<User> arrangeUsers(String option) {
    String query = "SELECT * FROM ";
    if (searchList.size() > 0) {
        query += "search_results ";
        searchList.clear();
        listChoice = true;
    } else {
        query += "users ";
        users.clear();
        listChoice = false;
    }

    if (option.equals("lastNameAsc")) {
        query += "ORDER BY lastName ASC;";
    } else if (option.equals("lastNameDesc")) {
        query +=  "ORDER BY lastName DESC;";
    } else if (option.equals("dobAsc")) {
        query +=  "ORDER BY dateOfBirth ASC;";
    } else if (option.equals("dobDesc")) {
        query += "ORDER BY dateOfBirth DESC;";
    }
    boolean listChoice;
    try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            PreparedStatement stmt = conn.prepareStatement(query);) {
        ResultSet result = stmt.executeQuery();
        while (result.next()) {
            User user = new User();
            user.setFirstName(result.getString("firstName"));
            user.setLastName(result.getString("lastName"));
            user.setDob(result.getDate("dateOfBirth"));
            user.setPhoneNumber(result.getString("phoneNumber"));
            user.setEmail(result.getString("email"));
            user.setUserID(result.getInt("id"));
            if (listChoice) {
                searchList.add(user);
            } else {
                users.add(user);
            }

        }
    } catch (SQLException sqle) {
        System.err.println(sqle);
        sqle.printStackTrace();
        return null;
    }
    if (listChoice) {
        return searchList;
    } else {
        return users;
    }
}

Hope it helps.

Error: MySQLSyntaxErrorException in a java-based web app

1 Answers1