CODEWITHSUNDEEP
spring-mvcjdbctemplatespring-jdbc
devdar
devdar

Reputation: 5654

Escaping a single quote when using JdbcTemplate For Dynamic WHERE

I am using JdbcTemplate to query the database however i am building a dynamic WHERE clause and i want to escape quotes. Under is an example of how my string looks :

There are times that they would not be any where clause since the user may want to return all records. So using prepared statement may not be feasible here.

JdbcTemplate

String sql = "select crime.*, "+
                     "criminalSocialSecurityNumber,criminal.fName as criminalFName,criminal.lName as criminalLName,"+
                     "criminal.photo as criminalPhoto,criminal.dob as criminalDob,victimSocialSecurityNumber,"+
                     "victim.fName as victimFName,victim.lName as victimLName,victim.photo as victimPhoto, victim.dob as victimDob "+ 
                     "from tblcrimes crime "+
                     "left join tblcriminalcrime on crime.crimeRecNo = tblcriminalcrime.crimeRecNo "+
                     "left join tblvictimcrime on crime.crimeRecNo = tblvictimcrime.crimeRecNo "+
                     "inner join tblcitizens criminal on criminal.socialSecurityNumber = tblcriminalcrime.criminalSocialSecurityNumber "+
                     "inner join tblcitizens victim on victim.socialSecurityNumber = tblvictimcrime.victimSocialSecurityNumber " + where_clause;

Upvotes: 0

Views: 2775

Answers (1)

JB Nizet
JB Nizet

Reputation: 691705

Using prepared statement is perfectly possible, and is what you should do.

Build your query dynamically, using placeholders (?) for every argument, and each time you add a placeholder, also add the argument value to a list of arguments. In the end, you have a parameterized SQL query, and a list of argument values to bind to the prepared statement.

Something like

List<Object> args = new ArrayList<Object>();
StringBuilder whereClause = new StringBuilder();
if (criteria.getFoo() != null) {
    whereClause.append(" and foo = ?");
    args.add(criteria.getFoo());
}
if (criteria.getBar() != null) {
    whereClause.append(" and bar = ?");
    args.add(criteria.getBar());
}
// ...

PreparedStatement stmt = connection.prepareStatement(query + whereClause);
int i = 1;
for (Object arg : args) {
    stmt.setObject(i, arg);
    i++;
}

Upvotes: 2

Related Questions