I have a simple HQL query I'm trying to execute to return a list of dates. My mapping has the dates defined as calendars. The mapping inserts can be inserted successfully without issues and retrieved with the correct data values mapped back to their properties. Example:
@Entity
@org.hibernate.annotations.Proxy(lazy = false)
@Table(name = "TABLE_T")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
public class Table {
@Column(name = "SOME_ID", nullable = false)
private Long stdIdNum = null;
@Column(name = "END_DATE", nullable = true)
private Calendar endDate;
However, if I perform the following HQL statment:
Query query = hsession.createQuery("select endDate from Table");
results = query.list();
I get the following exception:
java.lang.IllegalStateException: No data type for node: org.hibernate.hql.internal.ast.tree.IdentNode -[IDENT] IdentNode: 'end_date' {originalText=end_date}
at org.hibernate.hql.internal.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:154) at org.hibernate.hql.internal.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:865) at org.hibernate.hql.internal.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:650) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:651) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:287) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:235) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:248) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:183) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136) at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:101) at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:80) at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:119) at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:214) at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:192) at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1537) at
I'm obviously working with hibernate, version 4.0.0, with a sqlite back end, sqlite-jdbc-3.7.2.jar. Here's my SQLDialect:
import java.sql.Types;
import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.function.NoArgSQLFunction;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.dialect.function.VarArgsSQLFunction;
import org.hibernate.type.DateType;
import org.hibernate.type.IntegerType;
public class SQLiteDialect extends Dialect {
public SQLiteDialect() {
super();
registerColumnType(Types.BIT, "integer");
registerColumnType(Types.TINYINT, "tinyint");
registerColumnType(Types.SMALLINT, "smallint");
registerColumnType(Types.INTEGER, "integer");
registerColumnType(Types.BIGINT, "bigint");
registerColumnType(Types.FLOAT, "float");
registerColumnType(Types.REAL, "real");
registerColumnType(Types.DOUBLE, "double");
registerColumnType(Types.NUMERIC, "numeric");
registerColumnType(Types.DECIMAL, "decimal");
registerColumnType(Types.CHAR, "char");
registerColumnType(Types.VARCHAR, "varchar");
registerColumnType(Types.LONGVARCHAR, "longvarchar");
registerColumnType(Types.DATE, "date");
registerColumnType(Types.TIME, "time");
registerColumnType(Types.TIMESTAMP, "longvarchar");
registerColumnType(Types.BINARY, "blob");
registerColumnType(Types.VARBINARY, "blob");
registerColumnType(Types.LONGVARBINARY, "blob");
// registerColumnType(Types.NULL, "null");
registerColumnType(Types.BLOB, "blob");
registerColumnType(Types.CLOB, "clob");
registerColumnType(Types.BOOLEAN, "integer");
registerColumnType(Types.NULL, "null");
registerHibernateType(Types.NULL, "null");
registerFunction("concat", new VarArgsSQLFunction(IntegerType.INSTANCE, "",
"||", ""));
registerFunction("mod", new SQLFunctionTemplate(IntegerType.INSTANCE,
"?1 % ?2"));
registerFunction("substr", new StandardSQLFunction("substr",
IntegerType.INSTANCE));
registerFunction("substring", new StandardSQLFunction("substr",
IntegerType.INSTANCE));
registerFunction("replace", new StandardSQLFunction("replace",
IntegerType.INSTANCE));
registerFunction( "current_time", new NoArgSQLFunction( "date('now')", DateType.INSTANCE, false ) );
registerFunction( "current_timestamp", new NoArgSQLFunction( "date('now')", DateType.INSTANCE, false ) );
registerFunction( "current_date", new NoArgSQLFunction( "date('now')", DateType.INSTANCE, false ) );
}
@Override
public boolean supportsIdentityColumns() {
return true;
}
/*
public boolean supportsInsertSelectIdentity() {
return true; // As specify in NHibernate dialect
}
*/
@Override
public boolean hasDataTypeInIdentityColumn() {
return false; // As specify in NHibernate dialect
}
/*
public String appendIdentitySelectToInsert(String insertString) {
return new StringBuffer(insertString.length()+30). // As specify in NHibernate dialect
append(insertString).
append("; ").append(getIdentitySelectString()).
toString();
}
*/
@Override
public String getIdentityColumnString() {
// return "integer primary key autoincrement";
return "integer";
}
@Override
public String getIdentitySelectString() {
return "select last_insert_rowid()";
}
@Override
public boolean supportsLimit() {
return true;
}
// Added fix from bug, http://code.google.com/p/hibernate-sqlite/issues/detail?id=1
@Override
public boolean bindLimitParametersInReverseOrder() {
return true;
}
@Override
public String getLimitString(String query, boolean hasOffset) {
return new StringBuffer(query.length() + 20).append(query).append(
hasOffset ? " limit ? offset ?" : " limit ?").toString();
}
@Override
public String getLimitString(final String s, final int min, final int max) {
String limitString = super.getLimitString(s, min, max);
return limitString;
}
@Override
public boolean supportsTemporaryTables() {
return true;
}
@Override
public String getCreateTemporaryTableString() {
return "create temporary table if not exists";
}
@Override
public boolean dropTemporaryTableAfterUse() {
return false;
}
@Override
public boolean supportsCurrentTimestampSelection() {
return true;
}
@Override
public boolean isCurrentTimestampSelectStringCallable() {
return false;
}
@Override
public String getCurrentTimestampSelectString() {
return "select current_timestamp";
}
@Override
public boolean supportsUnionAll() {
return true;
}
@Override
public boolean hasAlterTable() {
return false; // As specify in NHibernate dialect
}
@Override
public boolean dropConstraints() {
return false;
}
@Override
public String getAddColumnString() {
return "add column";
}
@Override
public String getForUpdateString() {
return "";
}
@Override
public boolean supportsOuterJoinForUpdate() {
return false;
}
@Override
public String getDropForeignKeyString() {
throw new UnsupportedOperationException(
"No drop foreign key syntax supported by SQLiteDialect");
}
@Override
public String getAddForeignKeyConstraintString(String constraintName,
String[] foreignKey, String referencedTable, String[] primaryKey,
boolean referencesPrimaryKey) {
throw new UnsupportedOperationException(
"No add foreign key syntax supported by SQLiteDialect");
}
@Override
public String getAddPrimaryKeyConstraintString(String constraintName) {
throw new UnsupportedOperationException(
"No add primary key syntax supported by SQLiteDialect");
}
@Override
public boolean supportsIfExistsBeforeTableName() {
return true;
}
@Override
public boolean supportsCascadeDelete() {
return false;
}
}
UPDATE - Why am I not using Date?
Well, for two related reasons. It was a very poor implementation of the concept of Dates and Times and it was replaced by the Calendar class.
The Calendar class, although an improvement, leaves a lot to be desired as well, so for serious Date/Time work, everyone recommends JodaTime. In Java 7 there will likely be a replacement API for Calendar as well, currently called JSR-310.
select endDate from Table
is it the actual query that you are using? If yes try withselect endDate from Table endDate
. – Bhesh Gurungselect t.endDate from Table t
? – Bhesh Gurung