java.sqlite setObject not setting on PreparedStatement

Question

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.

Gord Thompson · Accepted Answer · 2017-11-26 16:35:28Z

2

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'.

edited Nov 26, 2017 at 16:35

answered Nov 26, 2017 at 16:28

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Christian Over a year ago

Owwwwwwwww!! :) Fantastic, it's now working. Many thanks.

Collectives™ on Stack Overflow

java.sqlite setObject not setting on PreparedStatement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related