Henry’s Blog

There were multiple times in recent projects where I had to write SQL for queries that had lists or arrays as parameters. There are a few ways of dealing with this issue, since API do not allow this natively.

One way is to use temporary table, insert all the ids in it and then join to that table in the query. Sybase has ‘native’ support for such temp tables, as long as you are using the same connection to insert the ids, and then execute the query.
To get the single connection out of the pool,use the following code:
JdbcTemplate scdsJdbcTemplate = null;
final String tempTableName = “#assets”;
try {
scds = new SingleConnectionDataSource(getJdbcTemplate().getDataSource().getConnection(), true);
scdsJdbcTemplate = new JdbcTemplate(scds);

createTempTableForIds(ids, tempTableName, scdsJdbcTemplate);

To insert ids into temp table and drop it after it was used, use the following code:
protected void createTempTableForIds(final List<String> ids, String tempTableName, JdbcTemplate jdbcTemplate) throws SQLException {
if (!tempTableName.startsWith(“#”)) {
throw new RuntimeException(“Temp table ” + tempTableName + ” does not start with #, which is a” +
“requirement for a temp table”);
}

final String TABLE_CREATE = “create table #_TABLE_NAME_ (barraId varchar(32))”;
final String TABLE_POPULATE = “insert into #_TABLE_NAME_ (barraId) values(?)”;

jdbcTemplate.execute(“sp_dboption asgp_db, ‘ddl in tran’, true”);
jdbcTemplate.execute(TEMP_TABLE_CREATE.replaceFirst(“#_TABLE_NAME_”, tempTableName));

String insertSql = TABLE_POPULATE.replaceFirst(“#_TABLE_NAME_”, tempTableName);

doInsertValuesIntoTempable(ids, jdbcTemplate, insertSql);
}

protected void doInsertValuesIntoTempable(final List<String> ids, JdbcTemplate jdbcTemplate, String insertSql) throws SQLException {
BatchPreparedStatementSetter batchObject = new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setString(1, ids.get(i));
}

public int getBatchSize() {
return ids.size();
}
};

int[] updateCounts = jdbcTemplate.batchUpdate(insertSql, batchObject);

/* sanity checking results */
if (updateCounts.length != barraIdAssets.size()) {
throw new SQLException(“Not all Ids were inserted # of inserts ” +
updateCounts.length + ” vs. # of Ids needed to be inserted ” + barraIdAssets.size());
}
for(int i=0; i< updateCounts.length; ++i) {
if (updateCounts[i] != 1) {
throw new SQLException(“Could not insert id” + ids.get(i)
+ ” inserted ” + updateCounts[i] + ” instead of 1 row”);
}
}
}

public static void dropTempTable(String tempTableName, JdbcTemplate jdbcTemplate) {
if (!tempTableName.startsWith(“#”)) {
throw new RuntimeException(“Temp table ” + tempTableName + ” does not start with #, which is a” +
“requirement for a temp table”);
}
String TABLE_DROP = “drop table #_TABLE_NAME_ “;
jdbcTemplate.execute(TABLE_DROP.replaceFirst(“#_TABLE_NAME_”, tempTableName));
}

The second way, you can dynamically create an in-clause and populate it with the ids, where you will have to make sure to batch #, e.g. possibly create a few queries since each one would work on subset of ids. You might be able then fire these queries in parallel and get the results that way.