QueryRunner

[jeff mutonho]

I'm trying to use the QueryRunner from the commons DBUtils to do a select from an oracle database.My code looks as follows :

 public List selectRows() throws SQLException {
StringBuffer sql = new StringBuffer();
StringBuffer row = new StringBuffer();
sql.append("SELECT ");
sql.append(generateBinders(configs.getColumnCount()));
sql.append(" FROM ");
sql.append(configs.getDatabaseConfig().getTableName());
 
// get column names and do formatting
List colNames = configs.getColumns();
 
Object[] params = getParams();
ResultSetHandler handler = new ResultSetHandler() {
public Object handle(ResultSet rs) throws SQLException {
ResultSetMetaData rsmd = rs.getMetaData();
List results = new ArrayList();
int colCount = rsmd.getColumnCount();
if (rs.next()) {
do {
for (int column = 1; column <= colCount; column++) {
StringBuffer result = new StringBuffer();
result.append(rs.getString(column));
result.append("#");
                                                        results.add(result);
}
} while (rs.next());
}
return results;
}
};
return (List) queryRunner.query(sql.toString(), params, handler);
}
   

params is an array of the columns I wish to specify in my SELECT statement and contains the following colname1, TO_CHAR(coldate1,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(colnum1,'99999990.99')

When I run this I get an exception saying :

java.sql.SQLException: Invalid column type Query: SELECT (?, ?, ?) FROM schema.table Parameters: [colname1, TO_CHAR(coldate1,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(colnum1,'99999990.99')]
at org.apache.commons.dbutils.QueryRunner.rethrow(QueryRunner.java:330)
at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:213)
at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:279)
at co.za.scs.scvpull.SCVDatabaseToMessagingClient.selectRows(SCVDatabaseToMessagingClient.java:192)
at co.za.scs.scvpull.SCVDatabaseToMessagingClient.main(SCVDatabaseToMessagingClient.java:60)

Does this mean , one cannot have a select statement that takes arguments specified as :

SELECT (?, ?, ?) FROM schema.table


jeff mutonho

[Christophe Verré]

Why don't you set the TO_CHAR in your select statement,
and just set colname1, coldate1, colnum1 in params ?

[jeff mutonho]

I can't because the decision whether or not to do the formatting(i.e adding TO_CHAR) is based on each parameter having formatting info as shown in the method below:

public Object[] getParams() {
Object[] params = new Object[configs.getColumnCount()];
StringBuffer sqlBuf = new StringBuffer();
List colNames = configs.getColumns();
Iterator itr = colNames.iterator();
int i = 0;

while (itr.hasNext()) {
Column col = (Column) itr.next();
System.out.println("col:"+col);
if (col.getDataTypeFormat().equals("")) {
params[i] = col.getName();
} else {
StringBuffer sb = new StringBuffer();
sb.append("TO_CHAR(");
sb.append(col.getName());
sb.append(",'");
sb.append(col.getDataTypeFormat());
sb.append("')");
params[i]=sb;
}
i++;
}
return params;
}   

[Christophe Verré]

I'm not a pro about this,
but can you actually pass parameters for column names ?
Shouldn't that be used for items values only ?
Please confirm my doubt first

[jeff mutonho]

Yes you can.I tested with a single column(with no TO_CHAR stuff) and it worked .Anyway I've changed the whole implementation to return the generated SQL as follows:

public StringBuffer generateSQL() {
StringBuffer sqlBuf = new StringBuffer("SELECT ");
List colNames = configs.getColumns();
Iterator itr = colNames.iterator();
int i = 0;
while (itr.hasNext()) {
Column col = (Column) itr.next();
System.out.println("col:" + col);
if (col.getDataTypeFormat().equals("")) {
sqlBuf.append(col.getName());
if (itr.hasNext()) {
sqlBuf.append(",");
}
} else {
StringBuffer sb = new StringBuffer();
sqlBuf.append(" TO_CHAR(");
sqlBuf.append(col.getName());
sqlBuf.append(",'");
sqlBuf.append(col.getDataTypeFormat());
sqlBuf.append("')");
if (itr.hasNext()) {
sqlBuf.append(",");
}
//params[i] = sb;
}
i++;
}
sqlBuf.append(" FROM ");
sqlBuf.append(configs.getDatabaseConfig().getTableName());
return sqlBuf;
}    

and then call QUeryRunner as follows :

 queryRunner.query(newSql.toString(), null, handler)   

i.e pass a null object for the parameter array.Works