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\>
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.
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)
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?
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.