java.sql.SQLSyntaxErrorException: Select Statement won't work

Question

My select statement shows the error

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 'condition from food' at line 1.

My parameter values are object = "food" and columns[] = {"foodName, foodPrice, condition"} This function still works for my login though

I already use different versions of mySQL and also put brackets around the statement and it still doesn't work

public static String[][] checkDatabase(String object, String[] columns){
        Connection myCon = null;
        Statement myStm = null;
        ResultSet myRs = null;
        try {
            myCon = DriverManager.getConnection("jdbc:mysql://localhost:3306/softwaredesignorder", "root","SeanLink_11");
            myStm = myCon.createStatement();
            String temp = "select ";
            for (int i = 0; i < columns.length; i++) {
                if (i < columns.length - 1) {
                    temp = temp + columns[i] + ", ";
                } else {
                    temp = temp + columns[i];
                }
            }
            temp = temp + " from " + object;
            //PreparedStatement pStm = myCon.prepareStatement(temp);
            //pStm.execute();
            myRs = myStm.executeQuery(temp);
            myRs.last();
            String[][] returner = new String[myRs.getRow()][columns.length];
            myRs.beforeFirst();
            int row = 0;
            while (myRs.next()){
                int length = columns.length, col = 0;

                while (length != 0) {
                    try {
                        returner[row][col] = myRs.getString(columns[col]);
                    } catch (IllegalArgumentException e1) {
                        try {
                            returner[row][col] = Integer.toString(myRs.getInt(columns[col]));
                        } catch (IllegalArgumentException e2) {
                            try {
                                returner[row][col] = Double.toString(myRs.getDouble(columns[col]));
                            } catch (IllegalArgumentException e3) {
                                try {
                                    Date date = myRs.getDate(columns[col]);
                                    DateFormat dateFormat = new SimpleDateFormat("yyyy-mm-dd");  
                                    returner[row][col] = dateFormat.format(date); 
                                } catch (IllegalArgumentException e4) {
                                    System.err.println("Could not retrieve data");
                                } catch (Exception e) {
                                    System.err.println(e);
                                }
                            }
                        }
                    }
                    col += 1;
                    length -= 1;
                }
                row += 1;
            }

            return returner;
        } catch (SQLException exSQL) {
            System.err.println(exSQL);
        }
        return null;
    };

I want the output to have the data from the entire table but only the selected column.

Condition is a keyword. You can't use a keyword as a column name (without quoting it). — Elliott Frisch, Jul 24 '19 at 03:18

score 0 · Answer 1 · answered Jul 24 '19 at 03:25

I believe your problem is that condition is a reserved work in most SQL languages as detailed in https://dev.mysql.com/doc/refman/5.5/en/keywords.html#keywords-5-5-detailed-C, that is why your code works on some cases, is because you don't always have a column named condition you need to escape it using backticks.

score 0 · Answer 2 · edited Jul 24 '19 at 06:09

0

Have you try to output back the query you made? i think this one have issue on your query builder. Just simply System.out.println(temp) and check the query syntax before you query on MySQL.

edited Jul 24 '19 at 06:09

jkdev

11,360
15
54
77

answered Jul 24 '19 at 04:39

ShaziqZulkpli

31
5

score 0 · Answer 3 · answered Jul 24 '19 at 06:36

"conditional" is a reserved word in MySQL. You could avoid this error by surrounding your select list items with quotes:

for (int i = 0; i < columns.length; i++) {
   String column = "\"" + colunms[i] + "\"";
   if (i < columns.length - 1) {
        temp = temp + column + ", ";
    } else {
        temp = temp + column;
    }
}

java.sql.SQLSyntaxErrorException: Select Statement won't work

3 Answers3