CODEWITHSUNDEEP
javasqlpostgresqljdbc
Community
Community

Reputation: 143

postgresql exist check error, wrong syntax?

I'm trying to check if row already exists, but it fails and gives me a syntax error this is the code:

public boolean checkExist(String table, String info) {
    try {
        PreparedStatement st = con.prepareStatement("SELECT EXISTS(SELECT 1 FROM " + table + " WHERE info=" + info + ")");
        return st.execute();
    } catch (SQLException e) {
        e.printStackTrace();
        return false;
    }
}

Query:

"SELECT EXISTS(SELECT 1 FROM " + table + " WHERE info=" + info + ")"

Throws: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")" Position: 104

Upvotes: 1

Views: 1015

Answers (2)

Mick Mnemonic
Mick Mnemonic

Reputation: 7956

The problem with your query is that you're appending the value of info into the query literal, but not wrapping it within single quotes as is required in PostgreSQL. So, the query that's sent over to the DB becomes something like

SELECT EXISTS(SELECT 1 FROM myTable WHERE info=valueOfInfo)

instead of

SELECT EXISTS(SELECT 1 FROM myTable WHERE info='valueOfInfo')

The correct way to do this is to use bind variables with the PreparedStatement and only have placeholders (?) in the query literal. This way, you don't need to worry about wrapping the variable in quotes, or more importantly, the possibility of SQL injection.

Additionally, you should take care of closing the statement after use; this is easiest done with the try-with-resources statement (assuming you're on Java 7 or above). So the fixed version of your method could look something like this:

public boolean checkExist(String table, String info) {

    final String query = "SELECT EXISTS(SELECT 1 FROM " + table
            + " WHERE info = ?)";

    try (PreparedStatement st = con.prepareStatement(query,
             ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
        st.setString(1, info); // bind the variable to the placeholder
        try (ResultSet rs = st.executeQuery()) {
            if (rs.next()) {
                return rs.getBoolean(1); // EXISTS() returns a boolean
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return false;
}

Upvotes: 2

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You should actually do 

" SELECT 1 FROM " + table + " WHERE info=" + info + ")" "

This would return 1 when a row exists that matches the where clause.

Also, use distinct if you only need 1 returned, no matter how many rows match the where condition, like

" SELECT distinct 1 FROM " + table + " WHERE info=" + info + ")" "

Upvotes: 1

Related Questions