title: JDBC PreparedStatement setObject compile time safety
body: <p>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 (<a href="https://stackoverflow.com/questions/5927363/java-resultset-setobject-vs-setstring-setdate-etc">see question discussing a similar approach</a>). Some pseudocode to illustrate:</p>
  
  <pre><code>public ResultSet getResultSet(String sql, List&lt;Object&gt; parameters) throws SQLException {
  
    PreparedStatement preparedStatement = db.getConnection().prepareStatement(sql);
  
    for(int i = 0; i &lt; parameters.size(); i++) {
      preparedStatement.setObject(i+1, parameters.get(i));
    }
  
    return preparedStatement.executeQuery();
  }
  </code></pre>
  
  <p>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:</p>
  
  <ol>
  <li>Someone invokes getResultSet("SELECT columnWithVarchar2Type FROM someTable WHERE columnWithVarchar2Type = ?", Arrays.asList(someCustomObject));</li>
  <li>db.getConnection().prepareStatement(sql) returns an <a href="http://download.oracle.com/otn_hosted_doc/jdeveloper/1012/jdbc-javadoc/oracle/jdbc/driver/OraclePreparedStatement.html" rel="nofollow noreferrer">OraclePreparedStatement</a></li>
  <li>preparedStatement.setObject throws a java.sql.SQLException 'Invalid column type'</li>
  </ol>
  
  <p>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, <em>the caller provided a list with an object, just as was requested!</em></p>
  
  <p><strong>The question is:</strong> 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?</p>
  
  <p>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 <strong>always</strong> result in an SQLException independently of the SQL that is passed?</p>
  
  <p><em>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.</em></p>