CODEWITHSUNDEEP
javasqlprepared-statement
Alan
Alan

Reputation: 822

WHERE clause issue

Given the following SQL statement which I want to construct:

SELECT CONCAT(employees.first_name," ", employees.last_name), landlines.number
FROM employees,landlines WHERE employees.id=landlines.emp_id ORDER BY employees.last_name

initially it is created with a question mark parameter like so:

SELECT CONCAT(employees.first_name," ", employees.last_name), landlines.number
FROM employees,landlines WHERE employees.id=? ORDER BY employees.last_name

as you can see from the WHERE condition I'm referencing a field from the employees table with a field from the landlines table. Basically a primary key field referencing a foreign key field. Pretty standard stuff.

My problem is working with the PreparedStatement class. I have a method that uses a switch case that looks like this:

PreparedStatement statement = ...;
...
//wc is a WhereCondition object and getValue() returns an Object which I cast to a particular type
Field.Type value = wc.getKey().getFieldType();
switch (value)
{
  case STRING:
      statement.setString(index, ((String)wc.getValue()));
      index++;
      break;
  case INT:
      if(wc.getValue() instanceof Field)
      {
        Field fld = (Field)wc.getValue();
        statement.setString(index,fld.toString());
      }
      else
        statement.setInt(index, ((Integer)wc.getValue()));
      index++;
      break;
  case FLOAT:
      statement.setFloat(index, ((Float)wc.getValue()));
      index++;
      break;
  case DOUBLE:
      statement.setDouble(index, ((Double)wc.getValue()));
      index++;
      break;
  case LONG:
      statement.setLong(index, ((Long)wc.getValue()));
      index++;
      break;
  case BIGDECIMAL:
      statement.setBigDecimal(index, ((BigDecimal)wc.getValue()));
      index++;
      break;
  case BOOLEAN:
      statement.setBoolean(index, ((Boolean)wc.getValue()));
      index++;
      break;
  case DATE:
      //We don't want to use the setDate(...) method as it expects
      //a java.sql.Date returned which doesn't allow for any time stamp.
      //Let the database perform the conversion from String to Date type.
      statement.setString(index, ((String)wc.getValue()));
      index++;
      break;
  case DBFUNCTION:
      statement.setString(index, ((String)wc.getValue()));
      index++;
      break;
  case IMAGE:
      statement.setString(index, ((String)wc.getValue()));
      index++;
      break;
}

If you look at the case INT I've tried to avoid a ClassCastException by testing for type Field and thus calling statement.setString(index,fld.toString(). The problem is that I wind up with an SQL statement with a WHERE clause that looks like this:

WHERE employees.id = 'landlines.emp_id'

Those pesky quote marks prevent the query from executing properly. Is there a way to setXXXX the parameter for field employees.id which is of type INT so that landlines.emp_id can be entered without the quotes being added?

Upvotes: 0

Views: 52

Answers (1)

Jason Kennaly
Jason Kennaly

Reputation: 652

This separates the JOIN from the parameter. I can't test it in your environment so I'm not sure if it is right, but it shouldn't need the quotes:

SELECT CONCAT(e.first_name," ", e.last_name), l.number
FROM employees e
JOIN landlines l ON e.id=l.emp_id
WHERE e.id=? ORDER BY e.last_name

Upvotes: 1

Related Questions