Description
In my project I am using OpenGeoDB (unmapped).
Now I want to get some data from there using native sql.
However I get "java.sql.SQLException: Column 'text_val' not found. " when I define column aliases (see log below).
Can anyone help, please?
Is this a bug?
The query works fine if pasted into MySQLQuery Browser.
Hibernate version:
Hibernate 3.2.0
Code between sessionFactory.openSession() and session.close():
Code:
Query query = null;
List<TreeEntry> treeEntries = new ArrayList<TreeEntry>();
String sql =
"SELECT " +
"i.text_val as iso, n.text_val as name, i.loc_id as loc_id " +
"FROM " +
"geodb.geodb_hierarchies hi, " +
"geodb.geodb_textdata c, " +
"geodb.geodb_textdata n, " +
"geodb.geodb_textdata i " +
"WHERE " +
"hi.id_lvl2=c.loc_id AND " +
"n.loc_id=hi.loc_id AND " +
"n.text_type=500100000 AND " +
"i.loc_id=n.loc_id AND " +
"i.text_type=500100001 AND " +
"c.text_val= :country AND " +
"level=3 order by n.loc_id ";
query = getSession().createSQLQuery(sql)
.setString("country", country);
Name and version of the database you are using:Mysql5.0.24a-community-nt
Debug level Hibernate log excerpt:Code:
[18:43:58,390 INFO Environment.<clinit>]
[18:43:58,406 INFO Environment.<clinit>] hibernate.properties not found
[18:43:58,406 INFO Environment.buildBytecodeProvider] Bytecode provider name : cglib
[18:43:58,421 INFO Environment.<clinit>] using JDK 1.4 java.sql.Timestamp handling
[18:43:58,546 INFO Configuration.configure] configuring from resource: /hibernate.cfg.xml
[18:43:58,546 INFO Configuration.getConfigurationInputStream] Configuration resource: /hibernate.cfg.xml
[18:43:59,000 INFO Configuration.addResource] Reading mappings from resource: mapping/ImportInfo.hbm.xml
[18:43:59,203 INFO HbmBinder.bindRootPersistentClassCommonValues] Mapping class: de.doubleslash.crm.server.model.ImportInfo -> crm_importinfo
[18:43:59,234 INFO Configuration.addResource] Reading mappings from resource: mapping/Category.hbm.xml
[18:43:59,296 INFO HbmBinder.bindRootPersistentClassCommonValues] Mapping class: de.doubleslash.crm.server.model.Category -> crm_category
[18:43:59,421 INFO HbmBinder.bindCollection] Mapping collection: de.doubleslash.crm.server.model.Category.contacts -> crm_contact_category
[18:43:59,421 INFO Configuration.addResource] Reading mappings from resource: mapping/Person.hbm.xml
[18:43:59,484 INFO HbmBinder.bindRootPersistentClassCommonValues] Mapping class: de.doubleslash.crm.server.model.Person -> crm_person
[18:43:59,484 INFO Configuration.addResource] Reading mappings from resource: mapping/Incorporate.hbm.xml
[18:43:59,515 INFO HbmBinder.bindRootPersistentClassCommonValues] Mapping class: de.doubleslash.crm.server.model.Incorporate -> crm_incorporate
[18:43:59,515 INFO Configuration.addResource] Reading mappings from resource: mapping/Contact.hbm.xml
[18:43:59,562 INFO HbmBinder.bindRootPersistentClassCommonValues] Mapping class: de.doubleslash.crm.server.model.Contact -> crm_contact
[18:43:59,562 INFO HbmBinder.bindCollection] Mapping collection: de.doubleslash.crm.server.model.Contact.categories -> crm_contact_category
[18:43:59,562 INFO HbmBinder.bindCollection] Mapping collection: de.doubleslash.crm.server.model.Contact.eventInterests -> crm_contact_eventclassification
[18:43:59,562 INFO Configuration.addResource] Reading mappings from resource: mapping/Address.hbm.xml
[18:43:59,640 INFO HbmBinder.bindRootPersistentClassCommonValues] Mapping class: de.doubleslash.crm.server.model.Address -> crm_address
[18:43:59,640 INFO Configuration.addResource] Reading mappings from resource: mapping/BankData.hbm.xml
[18:43:59,656 INFO HbmBinder.bindRootPersistentClassCommonValues] Mapping class: de.doubleslash.crm.server.model.BankData -> crm_bankdata
[18:43:59,656 INFO Configuration.addResource] Reading mappings from resource: mapping/ContactMedia.hbm.xml
[18:43:59,687 INFO HbmBinder.bindRootPersistentClassCommonValues] Mapping class: de.doubleslash.crm.server.model.ContactMedia -> crm_contactmedia
[18:43:59,687 INFO Configuration.addResource] Reading mappings from resource: mapping/WorkInfo.hbm.xml
[18:43:59,718 INFO HbmBinder.bindRootPersistentClassCommonValues] Mapping class: de.doubleslash.crm.server.model.WorkInfo -> crm_workinfo
[18:43:59,718 INFO Configuration.addResource] Reading mappings from resource: mapping/Supplement.hbm.xml
[18:43:59,750 INFO HbmBinder.bindRootPersistentClassCommonValues] Mapping class: de.doubleslash.crm.server.model.Supplement -> crm_supplement
[18:43:59,796 INFO Configuration.addResource] Reading mappings from resource: mapping/ConsultantInfo.hbm.xml
[18:43:59,812 INFO HbmBinder.bindRootPersistentClassCommonValues] Mapping class: de.doubleslash.crm.server.model.ConsultantInfo -> crm_consultantinfo
[18:43:59,812 INFO Configuration.addResource] Reading mappings from resource: mapping/Event.hbm.xml
[18:43:59,828 INFO HbmBinder.bindRootPersistentClassCommonValues] Mapping class: de.doubleslash.crm.server.model.Event -> crm_event
[18:43:59,828 INFO HbmBinder.bindCollection] Mapping collection: de.doubleslash.crm.server.model.Event.classifications -> crm_event_eventclassification
[18:43:59,828 INFO Configuration.addResource] Reading mappings from resource: mapping/EventClassification.hbm.xml
[18:43:59,859 INFO HbmBinder.bindRootPersistentClassCommonValues] Mapping class: de.doubleslash.crm.server.model.EventClassification -> crm_eventclassification
[18:43:59,859 INFO Configuration.addResource] Reading mappings from resource: mapping/Participation.hbm.xml
[18:43:59,875 INFO HbmBinder.bindRootPersistentClassCommonValues] Mapping class: de.doubleslash.crm.server.model.Participation -> crm_participation
[18:43:59,875 INFO Configuration.addResource] Reading mappings from resource: mapping/Location.hbm.xml
[18:43:59,937 INFO HbmBinder.bindRootPersistentClassCommonValues] Mapping class: de.doubleslash.crm.server.model.Location -> crm_person_location
[18:43:59,937 INFO Configuration.doConfigure] Configured SessionFactory: null
[18:43:59,953 INFO HbmBinder.bindCollectionSecondPass] Mapping collection: de.doubleslash.crm.server.model.Category.childCategories -> crm_category
[18:43:59,953 INFO HbmBinder.bindCollectionSecondPass] Mapping collection: de.doubleslash.crm.server.model.Person.contacts -> crm_contact
[18:43:59,953 INFO HbmBinder.bindCollectionSecondPass] Mapping collection: de.doubleslash.crm.server.model.Person.locations -> crm_person_location
[18:43:59,953 INFO HbmBinder.bindCollectionSecondPass] Mapping collection: de.doubleslash.crm.server.model.Incorporate.contacts -> crm_contact
[18:43:59,953 INFO HbmBinder.bindCollectionSecondPass] Mapping collection: de.doubleslash.crm.server.model.Contact.eventParticipations -> crm_participation
[18:43:59,953 INFO HbmBinder.bindCollectionSecondPass] Mapping collection: de.doubleslash.crm.server.model.Event.participations -> crm_participation
[18:44:00,015 INFO DriverManagerConnectionProvider.configure] Using Hibernate built-in connection pool (not for production use!)
[18:44:00,015 INFO DriverManagerConnectionProvider.configure] Hibernate connection pool size: 10
[18:44:00,015 INFO DriverManagerConnectionProvider.configure] autocommit mode: false
[18:44:00,031 INFO DriverManagerConnectionProvider.configure] using driver: com.mysql.jdbc.Driver at URL: jdbc:mysql://horizon:3306/crmdb
[18:44:00,031 INFO DriverManagerConnectionProvider.configure] connection properties: {user=crm, password=****}
[18:44:00,375 INFO SettingsFactory.buildSettings] RDBMS: MySQL, version: 5.0.24a-community-nt
[18:44:00,375 INFO SettingsFactory.buildSettings] JDBC driver: MySQL-AB JDBC Driver, version: mysql-connector-java-5.0.3 ( $Date: 2006-07-26 17:26:47 +0200 (Wed, 26 Jul 2006) $, $Revision: 5553 $ )
[18:44:00,390 INFO Dialect.<init>] Using dialect: org.hibernate.dialect.MySQLInnoDBDialect
[18:44:00,406 INFO TransactionFactoryFactory.buildTransactionFactory] Using default transaction strategy (direct JDBC transactions)
[18:44:00,421 INFO TransactionManagerLookupFactory.getTransactionManagerLookup] No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
[18:44:00,421 INFO SettingsFactory.buildSettings] Automatic flush during beforeCompletion(): disabled
[18:44:00,421 INFO SettingsFactory.buildSettings] Automatic session close at end of transaction: disabled
[18:44:00,421 INFO SettingsFactory.buildSettings] JDBC batch size: 15
[18:44:00,421 INFO SettingsFactory.buildSettings] JDBC batch updates for versioned data: disabled
[18:44:00,421 INFO SettingsFactory.buildSettings] Scrollable result sets: enabled
[18:44:00,421 INFO SettingsFactory.buildSettings] JDBC3 getGeneratedKeys(): enabled
[18:44:00,421 INFO SettingsFactory.buildSettings] Connection release mode: auto
[18:44:00,421 INFO SettingsFactory.buildSettings] Default catalog: crmdb
[18:44:00,437 INFO SettingsFactory.buildSettings] Maximum outer join fetch depth: 2
[18:44:00,437 INFO SettingsFactory.buildSettings] Default batch fetch size: 1
[18:44:00,437 INFO SettingsFactory.buildSettings] Generate SQL with comments: disabled
[18:44:00,437 INFO SettingsFactory.buildSettings] Order SQL updates by primary key: disabled
[18:44:00,437 INFO SettingsFactory.createQueryTranslatorFactory] Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
[18:44:00,437 INFO ASTQueryTranslatorFactory.<init>] Using ASTQueryTranslatorFactory
[18:44:00,437 INFO SettingsFactory.buildSettings] Query language substitutions: {}
[18:44:00,437 INFO SettingsFactory.buildSettings] JPA-QL strict compliance: disabled
[18:44:00,437 INFO SettingsFactory.buildSettings] Second-level cache: enabled
[18:44:00,437 INFO SettingsFactory.buildSettings] Query cache: disabled
[18:44:00,437 INFO SettingsFactory.createCacheProvider] Cache provider: org.hibernate.cache.NoCacheProvider
[18:44:00,437 INFO SettingsFactory.buildSettings] Optimize cache for minimal puts: disabled
[18:44:00,437 INFO SettingsFactory.buildSettings] Structured second-level cache entries: disabled
[18:44:00,453 INFO SettingsFactory.buildSettings] Statistics: disabled
[18:44:00,453 INFO SettingsFactory.buildSettings] Deleted entity synthetic identifier rollback: disabled
[18:44:00,453 INFO SettingsFactory.buildSettings] Default entity-mode: pojo
[18:44:00,515 INFO SessionFactoryImpl.<init>] building session factory
[18:44:01,421 INFO SessionFactoryObjectFactory.addInstance] Not binding factory to JNDI, no JNDI name configured
SELECT i.text_val as iso, n.text_val as name, i.loc_id as loc_id FROM geodb.geodb_hierarchies hi, geodb.geodb_textdata c, geodb.geodb_textdata n, geodb.geodb_textdata i WHERE hi.id_lvl2=c.loc_id AND n.loc_id=hi.loc_id AND n.text_type=500100000 AND i.loc_id=n.loc_id AND i.text_type=500100001 AND c.text_val= :country AND level=3 order by n.loc_id
[18:44:01,750 DEBUG AbstractBatcher.log] SELECT i.text_val as iso, n.text_val as name, i.loc_id as loc_id FROM geodb.geodb_hierarchies hi, geodb.geodb_textdata c, geodb.geodb_textdata n, geodb.geodb_textdata i WHERE hi.id_lvl2=c.loc_id AND n.loc_id=hi.loc_id AND n.text_type=500100000 AND i.loc_id=n.loc_id AND i.text_type=500100001 AND c.text_val= ? AND level=3 order by n.loc_id
[18:44:01,750 DEBUG AbstractBatcher.log] SELECT i.text_val as iso, n.text_val as name, i.loc_id as loc_id FROM geodb.geodb_hierarchies hi, geodb.geodb_textdata c, geodb.geodb_textdata n, geodb.geodb_textdata i WHERE hi.id_lvl2=c.loc_id AND n.loc_id=hi.loc_id AND n.text_type=500100000 AND i.loc_id=n.loc_id AND i.text_type=500100001 AND c.text_val= ? AND level=3 order by n.loc_id
[18:44:02,421 INFO NullableType.nullSafeGet] could not read column value from result set: text_val; Column 'text_val' not found.
[18:44:02,421 WARN JDBCExceptionReporter.logExceptions] SQL Error: 0, SQLState: S0022
[18:44:02,421 ERROR JDBCExceptionReporter.logExceptions] Column 'text_val' not found.
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2147)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)
at org.hibernate.loader.Loader.list(Loader.java:2023)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:150)
at de.doubleslash.crm.server.dao.hibernate.EventClassificationDAO.findTopRegions(EventClassificationDAO.java:111)
at de.doubleslash.crm.server.__TestHibernateQuery.run(__TestHibernateQuery.java:56)
at de.doubleslash.crm.server.__TestHibernateQuery.main(__TestHibernateQuery.java:78)
Caused by: java.sql.SQLException: Column 'text_val' not found.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:955)
at com.mysql.jdbc.ResultSet.getString(ResultSet.java:5447)
at org.hibernate.type.StringType.get(StringType.java:18)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:113)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:139)
at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.extract(CustomLoader.java:474)
at org.hibernate.loader.custom.CustomLoader$ResultRowProcessor.buildResultRow(CustomLoader.java:420)
at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:317)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:594)
at org.hibernate.loader.Loader.doQuery(Loader.java:689)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2144)
... 9 more
Login
FAQ
Search