Re: how to Escape single quotes with PreparedStatment - Mailing list pgsql-jdbc

On 22/08/11 11:26, JavaNoobie wrote:
> Hi,
> The following code works properly.
>     String query="SELECT count(*) over () as ROWCOUNT,
> CONSUMER_ID,WENEXA_ID,CONSUMER_NAME,CONTACT_NO,residing_village from
> db_consumer WHERE lower(CONSUMER_NAME) LIKE (lower(?))OR (lower(?)='') LIMIT
> ? OFFSET ? "; // AND (lower(RESIDING_VILLAGE) LIKE (lower(?) OR
> (lower(?)='')) AND (lower(WENEXA_ID) LIKE (lower(?))OR(lower(?)=''))" ;
>
>
>                 log.info(query);            // Get the wenexa ID to be passed from servlet
> here....
>                 stmt = con.prepareStatement(query);
>                 stmt.setString(1,"%"+name+"%");
>                 stmt.setString(2,"%"+name+"%");
>                 stmt.setInt(3,pageLimit);
>                 stmt.setInt(4,pageOffset);
>
> However , when I try the code -
>    String query="SELECT count(*) over () as ROWCOUNT,
> CONSUMER_ID,WENEXA_ID,CONSUMER_NAME,CONTACT_NO,residing_village from
> db_consumer WHERE lower(CONSUMER_NAME) LIKE (lower(?))OR (lower(?)='')  AND
> (lower(RESIDING_VILLAGE) LIKE (lower(?) OR (lower(?)='')) AND
> (lower(WENEXA_ID) LIKE (lower(?))OR(lower(?)='')) LIMIT ? OFFSET ?";
>                                  stmt = con.prepareStatement(query);
>                                  stmt.setString(1,"%"+name+"%");
>                                  stmt.setString(2,"%"+name+"%");
>                                  stmt.setString(3,"%"+village+"%");
>                                  stmt.setString(4,"%"+village+"%");
>                                 stmt.setInt(5,pageLimit);
>                                  stmt.setInt(6,pageOffset);
>                                     rs= stmt.executeQuery();
>
> It throws the previous error:org.postgresql.util.PSQLException: ERROR:
> syntax error at or near "LIMIT"
>    Position: 302
>          at
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
>          at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
> Any idea why this could be happening?
>

Sigh. I really need more coffee at this time of the morning. The
setters need to having an incrementing index:

StringBuilder query = new StringBuilder("SELECT count(*) over() AS
rowcount, consumer_id, wenexa_id, consumer_name, contact_no,
residing_village FROM db_consumer WHERE TRUE");

if (!name.isEmpty()) {
     query.append(" AND consumer_name ILIKE ?");
}

if (!village.isEmpty()) {
     query.append(" AND residing_village ILIKE ?");
}

if (!wenexaid.isEmpty()) {
     query.append(" AND wenexa_id ILIKE ?");
}

buf.append(" LIMIT ? OFFSET ?");

stmt = con.prepareStatement(query.toString());

int i = 0;

if (!name.isEmpty()) {
     stmt.setString(++i, "%" + name + "%");
}

if (!village.isEmpty()) {
     stmt.setString(++i, "%" + village + "%");
}

if (!wenexaid.isEmpty()) {
     stmt.setString(++i, "%" + wenexaid + "%");
}

stmt.setInt(++i, pageLimit);
stmt.setInt(++i, pageOffset);

rs = stmt.executeQuery();