java.sqlite setObject not setting on PreparedStatement

user_935122 0
Viewed 313

Am I missing something obvious here?

I have the following method to execute queries against an SQLite local database using org.xerial:sqlite-jdbc:3.21.0.

public List<HashMap<String, Object>> executeQuery(String sql, List<Object> vals) throws Exception {
    List<HashMap<String, Object>> rows = new ArrayList<>();
    Connection conn = getConnection();
    try (PreparedStatement stmnt = conn.prepareStatement(sql)) {
        if (!vals.isEmpty()) {
            for (int i = 0; i < vals.size(); i++) {
                stmnt.setObject(i + 1, vals.get(i));
            }
        }
        ResultSet rs = stmnt.executeQuery();
        ResultSetMetaData meta = rs.getMetaData();
        HashMap<String, Object> row;
        while (rs.next()) {
            row = new HashMap<>();
            for (int i = 0; i < meta.getColumnCount(); i++) {
                row.put(meta.getColumnName(i + 1), rs.getObject(i + 1));
            }
            rows.add(row);
        }
    } finally {
        putConnection(conn);
    }
    return rows;
}

However, when I pass the following SQL into the method along with the following values, they don't get set (but it also doesn't throw an exception). It's like it internally assigns it but forgets to tell the database.

SELECT * FROM 'airlines' WHERE 'name' LIKE ? LIMIT 1

vals: size = 1 {"MyAirline"}

I can see from debugging that it gets inside the loop to setObject.

enter image description here

jdbc sqlite sql java
1 Answers

In ANSI standard SQL, single quotes (') are used to delimit literal strings and double quotes (") are used to delimit table/column names. So

SELECT * FROM 'airlines' WHERE 'name' LIKE ? LIMIT 1

really means "select all columns from the literal string 'airlines' where the literal string 'name' matches the pattern supplied by the parameter".

Interestingly, SQLite seems to be clever enough to interpret the literal string 'airlines' as the table name "airlines" but it is still interpreting 'name' as a literal string. Therefore, for every row in "airlines" it is comparing the literal string 'name' to the string value 'MyAirline' and it never matches, so the ResultSet contains no rows.

Your SQL command text should be

SELECT * FROM "airlines" WHERE "name" LIKE ? LIMIT 1

so SQLite will compare the contents of the "name" column with the value 'MyAirline'.

user_21443900
Related