private List<List<PropertyPair>> findResults(
Query query, PropertySelectionCollector collector, PlasmaType type, Connection con) {
Object[] params = new Object[0];
JDBCDataConverter converter = JDBCDataConverter.INSTANCE;
if (log.isDebugEnabled()) {
log(query);
}
AliasMap aliasMap = new AliasMap(type);
Map<Type, List<String>> selectMap = collector.getResult();
// construct a filter adding to alias map
JDBCFilterAssembler filterAssembler = null;
Where where = query.findWhereClause();
if (where != null) {
filterAssembler = new JDBCFilterAssembler(where, type, aliasMap);
params = filterAssembler.getParams();
}
JDBCOrderingDeclarationAssembler orderingDeclAssembler = null;
OrderBy orderby = query.findOrderByClause();
if (orderby != null)
orderingDeclAssembler = new JDBCOrderingDeclarationAssembler(orderby, type, aliasMap);
JDBCGroupingDeclarationAssembler groupingDeclAssembler = null;
GroupBy groupby = query.findGroupByClause();
if (groupby != null)
groupingDeclAssembler = new JDBCGroupingDeclarationAssembler(groupby, type, aliasMap);
String rootAlias = aliasMap.getAlias(type);
StringBuilder sqlQuery = new StringBuilder();
sqlQuery.append("SELECT ");
int i = 0;
List<String> names = selectMap.get(type);
for (String name : names) {
PlasmaProperty prop = (PlasmaProperty) type.getProperty(name);
if (prop.isMany() && !prop.getType().isDataType()) continue;
if (i > 0) sqlQuery.append(", ");
sqlQuery.append(rootAlias);
sqlQuery.append(".");
sqlQuery.append(prop.getPhysicalName());
i++;
}
// construct a FROM clause from alias map
sqlQuery.append(" FROM ");
Iterator<PlasmaType> it = aliasMap.getTypes();
int count = 0;
while (it.hasNext()) {
PlasmaType aliasType = it.next();
String alias = aliasMap.getAlias(aliasType);
if (count > 0) sqlQuery.append(", ");
sqlQuery.append(aliasType.getPhysicalName());
sqlQuery.append(" ");
sqlQuery.append(alias);
count++;
}
// append WHERE filter
if (filterAssembler != null) {
sqlQuery.append(" ");
sqlQuery.append(filterAssembler.getFilter());
}
// set the result range
// FIXME: Oracle specific
if (query.getStartRange() != null && query.getEndRange() != null) {
if (where == null) sqlQuery.append(" WHERE ");
else sqlQuery.append(" AND ");
sqlQuery.append("ROWNUM >= ");
sqlQuery.append(String.valueOf(query.getStartRange()));
sqlQuery.append(" AND ROWNUM <= ");
sqlQuery.append(String.valueOf(query.getEndRange()));
}
if (orderingDeclAssembler != null) {
sqlQuery.append(" ");
sqlQuery.append(orderingDeclAssembler.getOrderingDeclaration());
}
if (groupingDeclAssembler != null) {
sqlQuery.append(" ");
sqlQuery.append(groupingDeclAssembler.getGroupingDeclaration());
}
List<List<PropertyPair>> rows = new ArrayList<List<PropertyPair>>();
PreparedStatement statement = null;
ResultSet rs = null;
try {
statement =
con.prepareStatement(
sqlQuery.toString(),
ResultSet.TYPE_FORWARD_ONLY, /*ResultSet.TYPE_SCROLL_INSENSITIVE,*/
ResultSet.CONCUR_READ_ONLY);
// set params
// note params are pre-converted
// to string in filter assembly
// FIXME: are parameters relevant in SQL in this context?
if (filterAssembler != null) {
params = filterAssembler.getParams();
if (params != null)
for (i = 0; i < params.length; i++) statement.setString(i + 1, String.valueOf(params[i]));
}
if (log.isDebugEnabled()) {
if (params == null || params.length == 0) {
log.debug("executing: " + sqlQuery.toString());
} else {
StringBuilder paramBuf = new StringBuilder();
paramBuf.append(" [");
for (int p = 0; p < params.length; p++) {
if (p > 0) paramBuf.append(", ");
paramBuf.append(String.valueOf(params[p]));
}
paramBuf.append("]");
log.debug("executing: " + sqlQuery.toString() + " " + paramBuf.toString());
}
}
statement.execute();
rs = statement.getResultSet();
int numcols = rs.getMetaData().getColumnCount();
ResultSetMetaData rsMeta = rs.getMetaData();
List<PropertyPair> row = null;
PropertyPair pair = null;
while (rs.next()) {
row = new ArrayList<PropertyPair>();
rows.add(row);
for (i = 1; i <= numcols; i++) {
String columnName = rsMeta.getColumnName(i);
int columnType = rsMeta.getColumnType(i);
PlasmaProperty prop = (PlasmaProperty) type.getProperty(columnName);
Object value = converter.fromJDBCDataType(rs, i, columnType, prop);
pair = new PropertyPair(prop, value);
pair.setColumn(i);
row.add(pair);
}
}
} catch (Throwable t) {
StringBuffer buf = this.generateErrorDetail(t, sqlQuery.toString(), filterAssembler);
log.error(buf.toString());
throw new DataAccessException(t);
} finally {
try {
if (rs != null) rs.close();
if (statement != null) statement.close();
} catch (SQLException e) {
log.error(e.getMessage(), e);
}
}
return rows;
}
private int countResults(Connection con, Query query, PlasmaType type) {
int result = 0;
Object[] params = new Object[0];
JDBCFilterAssembler filterAssembler = null;
StringBuilder sqlQuery = new StringBuilder();
AliasMap aliasMap = new AliasMap(type);
sqlQuery.append("SELECT COUNT(*)");
sqlQuery.append(aliasMap.getAlias(type));
Statement statement = null;
ResultSet rs = null;
try {
Where where = query.findWhereClause();
if (where != null) {
filterAssembler = new JDBCFilterAssembler(where, type, aliasMap);
sqlQuery.append(" ");
sqlQuery.append(filterAssembler.getFilter());
params = filterAssembler.getParams();
if (log.isDebugEnabled()) {
log.debug("filter: " + filterAssembler.getFilter());
}
} else {
sqlQuery.append(" FROM ");
sqlQuery.append(type.getPhysicalName());
sqlQuery.append(" ");
sqlQuery.append(aliasMap.getAlias(type));
}
if (query.getStartRange() != null && query.getEndRange() != null)
log.warn(
"query range (start: "
+ query.getStartRange()
+ ", end: "
+ query.getEndRange()
+ ") ignored for count operation");
if (log.isDebugEnabled()) {
log.debug("queryString: " + sqlQuery.toString());
log.debug("executing...");
}
statement =
con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
statement.execute(sqlQuery.toString());
rs = statement.getResultSet();
rs.first();
result = rs.getInt(1);
} catch (Throwable t) {
StringBuffer buf = this.generateErrorDetail(t, sqlQuery.toString(), filterAssembler);
log.error(buf.toString());
throw new DataAccessException(t);
} finally {
try {
if (rs != null) rs.close();
if (statement != null) statement.close();
} catch (SQLException e) {
log.error(e.getMessage(), e);
}
}
return result;
}