How can I use a database index with HQL

Hibernate ORM
1

I created a table use SQL:

CREATE TABLE system_signal_info
(
	id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	ctime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	mtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	config_def BOOLEAN NOT NULL DEFAULT TRUE,
	signal_id INT UNSIGNED NOT NULL, 
	UNIQUE INDEX i_signal_id(signal_id)
);

And the “i_signal_id” is the index that I want to use with HQL when searching.

Whether can I configure the xml to map the index like following?
If not, is there an another way to work out?

\<hibernate-mapping package="db"/\>    
    \<class name="SystemSignalInfoHbn" table="system_signal_info"\>  
        \<id name="id" column="id"\>  
            \<generator class="native" /\>
        \</id\>  
        \<property name="ifConfigDef" column="config_def" /\>  
        \<property name="signalId" column="signal_id"/\>
        \<??? ANY WAY TO DO???\>
    \</class\>    
\</hibernate-mapping\>
2

You don’t need to map an index in Hibernate mappings. HQL is translated to SQL, and if the DB decides if an index makes sense to be used for the SQL query, it will be used automatically.

1 Like
3

I do something like:

session.createQuery("from SystemSignalInfoHbn where i_signal_id = :signal_id").setParameter("signal_id", it.next().getId()).uniqueResult();

but it cause an error followed(ERROR: Unknown column ‘i_signal_id’ in ‘where clause’):

Hibernate: select systemsign0_.id as id1_24_, systemsign0_.config_def as config_d2_24_, systemsign0_.signal_id as signal_i3_24_ from system_signal_info systemsign0_ where i_signal_id=?
五月 21, 2018 2:35:42 下午 org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 1054, SQLState: 42S22
五月 21, 2018 2:35:42 下午 org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: Unknown column 'i_signal_id' in 'where clause'
[main] ERROR db.BeecomDB - javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:149)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157)
	at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1423)
	at org.hibernate.query.internal.AbstractProducedQuery.uniqueResult(AbstractProducedQuery.java:1457)
	at db.BeecomDB.getSysSigInfoHbnLst(BeecomDB.java:465)
	at db.BeecomDB.getSystemSignalUnitLst(BeecomDB.java:370)
	at bc_server.BcServerMain.main(BcServerMain.java:121)
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
	at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:69)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:2168)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1931)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1893)
	at org.hibernate.loader.Loader.doQuery(Loader.java:938)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
	at org.hibernate.loader.Loader.doList(Loader.java:2692)
	at org.hibernate.loader.Loader.doList(Loader.java:2675)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2507)
	at org.hibernate.loader.Loader.list(Loader.java:2502)
	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:502)
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:392)
	at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
	at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1489)
	at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1445)
	at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414)
	... 4 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'i_signal_id' in 'where clause'
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
	at java.lang.reflect.Constructor.newInstance(Unknown Source)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2487)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1966)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)
	... 19 more

Exception in thread "main" java.lang.NullPointerException
	at bc_server.BcServerMain.main(BcServerMain.java:122)
4

You get an error because in your JPQL query you are not referencing an entity property.

So, instead of:

You should use:

session.createQuery(“from SystemSignalInfoHbn where signalId = :signal_id”).setParameter(“signal_id”, it.next().getId()).uniqueResult();

Because you mapped the signal_id column to a signalId property, and that’s what you need to use in your entity query.

Remember that JPQL queries use entities and properties, while SQL queries use tables and columns.

Now, about this:

Exception in thread "main" java.lang.NullPointerException
	at bc_server.BcServerMain.main(BcServerMain.java:122)

You have to check the BcServerMain class at line 122 and see which variable is null.

5

I think I start to get a little sense:
Do you mean that:
if I query like
session.createQuery(“from SystemSignalInfoHbn where signalId = :signal_id”).setParameter(“signal_id”, it.next().getId()).uniqueResult();

and the hibernate will help me use the ‘index-searching’(like search ‘i_signal_id’ with sql) automaticly?

6

and the hibernate will help me use the ‘index-searching’(like search ‘i_signal_id’ with sql) automaticly?

No. That’s not how it works.

Again, Hibernate does not do anything related to indexes. The fact that the signal_id column has an index will be considered by the query optimizer. For getting one record, it will. For getting a large portion of a table, it will not. This has nothing to do with Hibernate. The same will happen with any SQL executed from an SQL console or from plain JDBC. It’s just how a DB works.

7

I think I understand it now.
Really really really thanks!

8

You are very welcome.