2

I have an application that accesses the database by using JDBC PreparedStatements. To keep the data access generic, the method that accesses the database uses the PreparedStatement setObject method heavily (see question discussing a similar approach). Some pseudocode to illustrate:

public ResultSet getResultSet(String sql, List<Object> parameters) throws SQLException {

  PreparedStatement preparedStatement = db.getConnection().prepareStatement(sql);

  for(int i = 0; i < parameters.size(); i++) {
    preparedStatement.setObject(i+1, parameters.get(i));
  }

  return preparedStatement.executeQuery();
}

This reduces code duplication, as getResultSet(String, List) can be used to perform different queries. However, it also risks SQLExceptions being thrown at runtime. Example situation:

  1. Someone invokes getResultSet("SELECT columnWithVarchar2Type FROM someTable WHERE columnWithVarchar2Type = ?", Arrays.asList(someCustomObject));
  2. db.getConnection().prepareStatement(sql) returns an OraclePreparedStatement
  3. preparedStatement.setObject throws a java.sql.SQLException 'Invalid column type'

The behavior of OraclePreparedStatement is probably justified; it doesn't know how to map someCustomObject to columnWithVarchar2Type. However, to the caller of getResultSet(String, List) this is a surprise - after all, the caller provided a list with an object, just as was requested!

The question is: Given the differences between the implementations of PreparedStatement, is there a way to introduce any compile-time safety to the signature of the getResultSet method to prevent callers from passing parameters that will throw SQLExceptions at runtime?

Complete compile-time safety is of course impossible, but is there a way to use e.g. generics to prevent someone passing in parameters that will always result in an SQLException independently of the SQL that is passed?

Edit: This is in all likelihood even theoretically impossible (nothing prevents someone from implementing PreparedStatement and deciding to never throw SQLExceptions or to always throw SQLExceptions from setObject()). However, it would be nice to know how ORMs go around this problem.

Community
  • 1
  • 1
Lauri Harpf
  • 1,448
  • 1
  • 12
  • 30
  • I think that's what an ORM like Hibernate does. Java talks using typed beans. On start-up Hibernate checks if the schema found in the DB actually matches that, and the application won't start if it does not. – Thilo Sep 19 '13 at 07:34
  • 2
    You're making a trade-off between code duplication and compile-time type safety/code robustness. Do you think it is worth it? In a real world application you'd never do something like this, although you probably wouldn't use raw PreparedStatements in the first place... – Kayaman Sep 19 '13 at 07:45
  • JPA, like eclipseLink would be a fine (type-safe) Object Relational Mapping. To do it your way a `String sql` does not suffice; you would need your own "statement" class; that could check the number of parameters and specify the parameter types. – Joop Eggen Sep 19 '13 at 07:53

0 Answers0