Hibernate3.5 & HyperSQL - could not execute query

**fred2k8** · 21/04/2015, 21h48

Je viens de commencer à mettre en place un environnement de test d'Hibernate3 avec la base de donnée HyperSQL le tout sous Eclipse.
Après avoir pu générer avec succès les classes d'accès à cette DB, j'obtiens malheureusement une erreur lors d'une tentative de lecture de la table Customer à l'aide de la commande createQuery:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See <a href="http://www.slf4j.org/codes.html#StaticLoggerBinder" target="_blank">http://www.slf4j.org/codes.html#StaticLoggerBinder</a> for further details.
Hibernate: select customer0_.id as id1_, customer0_.nom as nom1_ from PUBLIC.PUBLIC.CUSTOMER customer0_
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.loader.Loader.doList(Loader.java:2545)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
	at org.hibernate.loader.Loader.list(Loader.java:2271)
	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:459)
	at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:365)
	at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
	at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268)
	at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
	at Test_hsql.read(Test_hsql.java:63)
	at Test_hsql.main(Test_hsql.java:14)
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: CUSTOMER0_.ID
	at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
	at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
	at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534)
	at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:452)
	at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161)
	at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1700)
	at org.hibernate.loader.Loader.doQuery(Loader.java:801)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
	at org.hibernate.loader.Loader.doList(Loader.java:2542)
	... 9 more

En ce qui concerne les détails de mon installation:

Eclipse Java EE IDE for Web Developers version Kepler Service Release 2.
jdk1.6.0_45
Hibernate 3.6 (mais utilisé sous Eclipse comme 3.5)
hsqldb-2.3.2

Le fichier de configuration d'Hibernate:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
                                         "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
 <session-factory name="HibernateTest_SessionFactory">
  <property name="hibernate.bytecode.use_reflection_optimizer">false</property>
  <property name="hibernate.connection.driver_class">org.hsqldb.jdbcDriver</property>
  <property name="hibernate.connection.url">jdbc:hsqldb:hsql://localhost/testdb</property>
  <property name="hibernate.connection.username">SA</property>
  <property name="hibernate.dialect">org.hibernate.dialect.HSQLDialect</property>
  <property name="hibernate.search.autoregister_listeners">false</property>
  <property name="hibernate.show_sql">true</property>
  <property name="hibernate.hbm2ddl.auto">update</property>
  <mapping resource="com/fco/hibernate/Customer.hbm.xml"/>
 </session-factory>
</hibernate-configuration>

Le fichier de mapping:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 21-avr.-2015 16:33:41 by Hibernate Tools 3.4.0.CR1 -->
<hibernate-mapping>
    <class name="com.fco.hibernate.Customer" table="CUSTOMER" schema="PUBLIC" catalog="PUBLIC">
        <id name="id" type="java.lang.Integer">
            <column name="id" />
            <generator class="identity" />
        </id>
        <property name="nom" type="string">
            <column name="nom" length="50" />
        </property>
    </class>
</hibernate-mapping>

La classe POJO:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
package com.fco.hibernate;
 
// Generated 21-avr.-2015 16:33:41 by Hibernate Tools 3.4.0.CR1
 
/**
 * Customer generated by hbm2java
 */
public class Customer implements java.io.Serializable {
 
	private Integer id;
	private String nom;
 
	public Customer() {
	}
 
	public Customer(String nom) {
		this.nom = nom;
	}
 
	public Integer getId() {
		return this.id;
	}
 
	public void setId(Integer id) {
		this.id = id;
	}
 
	public String getNom() {
		return this.nom;
	}
 
	public void setNom(String nom) {
		this.nom = nom;
	}
 
}

La classe DAO:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
package com.fco.hibernate;
 
// Generated 21-avr.-2015 16:33:41 by Hibernate Tools 3.4.0.CR1
 
import java.util.List;
import javax.naming.InitialContext;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.LockMode;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Example;
 
/**
 * Home object for domain model class Customer.
 * @see com.fco.hibernate.Customer
 * @author Hibernate Tools
 */
public class CustomerHome {
 
	private static final Log log = LogFactory.getLog(CustomerHome.class);
 
	private final SessionFactory sessionFactory = getSessionFactory();
 
	protected SessionFactory getSessionFactory() {
		try {
			return (SessionFactory) new InitialContext()
					.lookup("SessionFactory");
		} catch (Exception e) {
			log.error("Could not locate SessionFactory in JNDI", e);
			throw new IllegalStateException(
					"Could not locate SessionFactory in JNDI");
		}
	}
 
	public void persist(Customer transientInstance) {
		log.debug("persisting Customer instance");
		try {
			sessionFactory.getCurrentSession().persist(transientInstance);
			log.debug("persist successful");
		} catch (RuntimeException re) {
			log.error("persist failed", re);
			throw re;
		}
	}
 
	public void attachDirty(Customer instance) {
		log.debug("attaching dirty Customer instance");
		try {
			sessionFactory.getCurrentSession().saveOrUpdate(instance);
			log.debug("attach successful");
		} catch (RuntimeException re) {
			log.error("attach failed", re);
			throw re;
		}
	}
 
	public void attachClean(Customer instance) {
		log.debug("attaching clean Customer instance");
		try {
			sessionFactory.getCurrentSession().lock(instance, LockMode.NONE);
			log.debug("attach successful");
		} catch (RuntimeException re) {
			log.error("attach failed", re);
			throw re;
		}
	}
 
	public void delete(Customer persistentInstance) {
		log.debug("deleting Customer instance");
		try {
			sessionFactory.getCurrentSession().delete(persistentInstance);
			log.debug("delete successful");
		} catch (RuntimeException re) {
			log.error("delete failed", re);
			throw re;
		}
	}
 
	public Customer merge(Customer detachedInstance) {
		log.debug("merging Customer instance");
		try {
			Customer result = (Customer) sessionFactory.getCurrentSession()
					.merge(detachedInstance);
			log.debug("merge successful");
			return result;
		} catch (RuntimeException re) {
			log.error("merge failed", re);
			throw re;
		}
	}
 
	public Customer findById(java.lang.Integer id) {
		log.debug("getting Customer instance with id: " + id);
		try {
			Customer instance = (Customer) sessionFactory.getCurrentSession()
					.get("com.fco.hibernate.Customer", id);
			if (instance == null) {
				log.debug("get successful, no instance found");
			} else {
				log.debug("get successful, instance found");
			}
			return instance;
		} catch (RuntimeException re) {
			log.error("get failed", re);
			throw re;
		}
	}
 
	public List findByExample(Customer instance) {
		log.debug("finding Customer instance by example");
		try {
			List results = sessionFactory.getCurrentSession()
					.createCriteria("com.fco.hibernate.Customer")
					.add(Example.create(instance)).list();
			log.debug("find by example successful, result size: "
					+ results.size());
			return results;
		} catch (RuntimeException re) {
			log.error("find by example failed", re);
			throw re;
		}
	}
}

Les librairies du projet:
Nom : Hibernate3Test_HSQL.png
Affichages : 368
Taille : 95,5 Ko

Pour info le fichier hibernate.reveng.xml qui a servi à produire les fichiers ci-dessus:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-reverse-engineering PUBLIC "-//Hibernate/Hibernate Reverse Engineering DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-reverse-engineering-3.0.dtd" >
 
<hibernate-reverse-engineering>
	<table-filter match-catalog="PUBLIC" match-schema="PUBLIC"
		match-name="CUSTOMER" />
	<table-filter match-name="GAME" match-catalog="PUBLIC"
		match-schema="PUBLIC">
	</table-filter>
</hibernate-reverse-engineering>

Et pour terminer la classe de test qui rencontre l'erreur:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
import java.util.*;
 
import org.hibernate.*;
import org.hibernate.cfg.Configuration;
 
import com.fco.hibernate.*;
 
public class Test_hsql {
 
 public static void main(String[] args) throws HibernateException {
	 read();
 }
 
 public static void read() throws HibernateException {
	 Session session = HibernateUtil.currentSession();
 
	 Query query = session.createQuery("from Customer c"); 
	 List results = query.list();
	 Iterator it = results.iterator();
	 while(it.hasNext())
	 {
	   Customer customer = (Customer)it.next();
	   System.out.println(customer.getId() + " " + customer.getNom());
	 }
 
	 HibernateUtil.closeSession();	 
 } 
}