I'm getting exception when trying to use substring with criteria. (could use "or" Restrictions instead of "in" to accomplish the same thing, but really would like to get this to work) The SQL generated will run in both DBVisualizer and seems to be running thru Hibernate, but when the resultset is returned, I get an exception:
Code:
Session session = this.getSession();
Criteria criteria = session.createCriteria(xyz.class);
ClassMetadata metaOwner = session.getSessionFactory().getClassMetadata(xyz.class);
criteria.add(Restrictions.isNull("obseleteDate"));
criteria.add(Restrictions.like("submittedSequenceId", "CEIRS-______%"));
criteria.add(Restrictions.not(Restrictions.like("ncbiAccession", "concat_%")));
String function = "substr(SUBMITTED_SEQ_IDENTIFIER, 7, 6)";
ProjectionList projList = Projections.projectionList();
projList.add(Projections.alias(Projections.sqlGroupProjection(
"substr(" + "SUBMITTED_SEQ_IDENTIFIER" + ",7,6) as y1",
function, //group by function
new String[]{function}, //column alias used in the "in" statement
new Type[]{ metaOwner.getPropertyType("submittedSequenceId") }),
function)); //global value used in replace of looking at the class for the property
criteria.add(Restrictions.in(function, centerNames));
projList.add(Projections.max("submissionDate"));
criteria.setProjection(projList);
return (List<Object[]>) criteria.list();
Query generated:select
substr(SUBMITTED_SEQ_IDENTIFIER,7,6) as y1,
max(this_.GB_SUBMISSION_DATE) as y1_
from xyzTable this_
where this_.OBSOLETE_DATE is null and
this_.SUBMITTED_SEQ_IDENTIFIER like 'CEIRS-______%' and
not this_.NCBIGenomeAccession like 'concat_%' and
substr(SUBMITTED_SEQ_IDENTIFIER, 7, 6)
in ('ATL023', 'CIP045', 'CIP046', 'CLA032', 'SJC001', 'SJC002', 'UMN026', 'UMN028')
group by substr(SUBMITTED_SEQ_IDENTIFIER, 7, 6)
Exception:[09:44:21][DEBUG] about to open ResultSet (open ResultSets: 0, globally: 0)
[09:44:21][DEBUG] processing result set
[09:44:21][DEBUG] result set row: 0
[09:44:21][DEBUG] result row:
[09:44:21][INFO] could not read column value from result set: substr(SUBMITTED_SEQ_IDENTIFIER, 7, 6); Invalid column name
[09:44:21][DEBUG] about to close ResultSet (open ResultSets: 1, globally: 1)
[09:44:21][DEBUG] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
[09:44:21][DEBUG] closing statement
[09:44:21][DEBUG] could not execute query [select substr(SUBMITTED_SEQ_IDENTIFIER,7,6) as y1, max(this_.GB_SUBMISSION_DATE) as y1_ from xyzTable this_ where this_.OBSOLETE_DATE is null and this_.SUBMITTED_SEQ_IDENTIFIER like ? and not this_.NCBIGenomeAccession like ? and substr(SUBMITTED_SEQ_IDENTIFIER, 7, 6) in (?, ?, ?, ?, ?, ?, ?, ?) group by substr(SUBMITTED_SEQ_IDENTIFIER, 7, 6)]
java.sql.SQLException: Invalid column name
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:158)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:305)
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java:3720)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:2583)
at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:1559)
at com.p6spy.engine.spy.P6ResultSet.getString(P6ResultSet.java:260)
at org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:224)
at org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:224)
at org.hibernate.type.StringType.get(StringType.java:18)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:189)
at org.hibernate.loader.criteria.CriteriaLoader.getResultColumnOrRow(CriteriaLoader.java:107)
Thanks for any suggestions.
Login
FAQ
Search