I've inherited a project which has the following setup:
The declared class and one of the methods that is used to do the queries looks like the following
public class DataSourceServiceImpl extends SimpleJdbcDaoSupport implements DataSourceService {
...
public List<Map<String, Object>> valueList(String dataSource, Object[] params, String sql) throws DataAccessException {
DataSourceContextHolder.setDataSource(dataSource);
return getSimpleJdbcTemplate().getJdbcOperations().queryForList(sql, params);
}
}
The sample SQL:
SELECT samplefield FROM sampletable WHERE SDE.ST_INTERSECTS(SHAPE, SDE.ST_GEOMETRY(?, ?)) = 1 AND SHAPE IS NOT NULL
The problem is, that if the input string (representing geometry) exceeds the Oracle driver's 4000 character limit, we get the
ORA-01460: unimplemented or unreasonable conversion requested
In other words, this means that queryForList (and whatever is behind it) doesnt automatically handle the strings that exceed the limit.
After some research, I've realized that I have to use the c3p0 OracleUtils to generate a temporary Clob. So I've modified the code to check for the parameters and modify them accordingly:
try {
Connection conn = getConnection();
for (Object obj: params){
if (obj instanceof String && obj.toString().length() > 4000){
Clob clob = OracleUtils.createTemporaryCLOB(conn, true, 10);
clob.setString(1, (String)obj);
clobs.add(clob);
params[i] = clob; // re-assign the parameter back
}
i++;
}
List<Map<String, Object>> result = getSimpleJdbcTemplate().getJdbcOperations().queryForList(sql, params);
if (!clobs.isEmpty())
for (Clob c: clobs) c.free();
} catch (SQLException e) {
e.printStackTrace();
}
Unfortunately, this resulted in another Oracle error:
ORA-22922: Nonexistent LOB value
After another research I've realized that the pointer to the temporary Clob that I've assigned to the "params" parameter is empty, and most likely due to the "queryForList" being executed in different connection (!) than the one used for generating the temporaryClob. So I've ended up with the following:
JdbcTemplate t = new JdbcTemplate(new SingleConnectionDataSource(conn, false));
result = t.queryForList(sql, params);
which worked, but I'm afraid this is not optimal and will only be causing issues at some point in the future.
My question is, is there any way to reuse the connection used to generate the CLOB for the actual query?
The straightforward thing to do would be to evade the overcomplicated middleware.
public List<Map<String, Object>> valueList(String dataSource, Object[] params, String sql) throws DataAccessException {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
Clob clob = null;
try {
con = dataSource.getConnection();
ps = con.prepareStatement( "SELECT samplefield FROM sampletable WHERE SDE.ST_INTERSECTS(SHAPE, SDE.ST_GEOMETRY(?, ?)) = 1 AND SHAPE IS NOT NULL" ); // probably externalize this SQL string somewhere
// Note that Oracle's two-arg ST_GEOMETRY function takes a CLOB and an INTEGER
// see http://resources.arcgis.com/en/help/main/10.1/index.html#//006z00000050000000
Clob clob = OracleUtils.createTemporaryCLOB(conn, true, 10);
clob.setString(1, (String) params[0]);
ps.setClob( 1, clob );
ps.setObject( 2, params[1], java.sql.Types.INTEGER );
rs = ps.executeQuery();
// inefficient and overdone output format, but hey.
List<Map<String,Object>> out = new LinkedList()
while (rs.next()) {
Map<String,Object> oneBindingMap = new HashMap(1);
oneBindingMap.put("samplefield", rs.getObject(1)); //again, maybe externalize the field name
out.add( oneBindingMap );
}
return out;
}
catch ( SQLException e ) {
throw new DataAccessException( e.getMessage(), e ); // adapt to expected Exception type
}
finally {
try { if ( clob != null ) clob.free(); } catch ( Exception e ) { e.printStackTrace(); }
// in java 7+ you could avoid the rest of this via the try-with-resources construct
try { if ( rs != null ) rs.close(); } catch ( Exception e ) { e.printStackTrace(); }
try { if ( ps != null ) ps.close(); } catch ( Exception e ) { e.printStackTrace(); }
try { if ( con != null ) con.close(); } catch ( Exception e ) { e.printStackTrace(); }
}
}
I'm just typing this into a web page; I've not tested whether it compiles, and I'm making some assumptions about the semantics you want (e.g. the format and key name in the output maps). But I think that this should bring you pretty close to where you want to be, without having to convolute your logic to adapt it to middleware not intended to do what you want.
Alternatively, I don't see much wrong or likely to fail with your SingleConnectionDataSource hack. It just strikes me as opaque. My solution is much more verbose, but I think logically very straightforward and accessible for maintenance purposes to anyone who understands JDBC.
Good luck!