How do I reference java Calendar types in an HQL select statement?

0
votes

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.

Why were most java.util.Date methods deprecated?

2
javadatabasehibernatesqlitehql
Another good post regarding Date deprecation, stackoverflow.com/questions/460423/… – Jason Huntley
select endDate from Table is it the actual query that you are using? If yes try with select endDate from Table endDate. – Bhesh Gurung
Hi Bhesh, It's the simplest version of it. I may end up adding a where clause and checking for dates above a certain range. I was able to successfully run your format of the HQL. The query returns results, only they are the original parent object, not the individual date property. – Jason Huntley
The problem unlike sql with HQL you can not retrieve individual columns, it's always the list of entities. – Bhesh Gurung
Did you try select t.endDate from Table t? – Bhesh Gurung

2 Answers

0
votes

I didn't go over your SQLDialect, but have you tried using Date instead of Calendar 

private Calendar endDate

private Date endDate

I remembered java.util.date was working once with hibernate but java.sql.date was not. It might be the solution. In the exception it shows the end_date, so it means that it is reading from your annotations but at the database level you are having a problem.

0
votes

You can use java.util.Date for the reference (actually object might come from java.sql package). And use @Temporal (TemporalType.TIMESTAMP) for example if it is timestamp in the database table.

And if it's a date type the it should something as follows -

@Temporal (TemporalType.DATE)
@Column(name = "END_DATE", nullable = true) 
private Date endDate;

Update (following your comment)

select t.endDate from Table as t