Hibernate table not mapped error in HQL query
JavaEclipseSpringHibernateHibernate MappingJava Problem Overview
I have a web application that use Hibernate to make CRUD operations over a database. I got an error saying that the table is not mapped. See the Java files:
Error message:
org.springframework.orm.hibernate3.HibernateQueryException: Books is not mapped [SELECT COUNT(*) FROM Books]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: Books is not mapped [SELECT COUNT(*) FROM Books]
at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:660)
at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411)
...
Caused by: org.hibernate.hql.ast.QuerySyntaxException: Books is not mapped [SELECT COUNT(*) FROM Books]
at org.hibernate.hql.ast.util.SessionFactoryHelper.requireClassPersister(SessionFactoryHelper.java:181)
at org.hibernate.hql.ast.tree.FromElementFactory.addFromElement(FromElementFactory.java:111)
at org.hibernate.hql.ast.tree.FromClause.addFromElement(FromClause.java:93)
...
Here's my DAO.java
method:
public int getTotalBooks(){
return DataAccessUtils.intResult(hibernateTemplate.find(
"SELECT COUNT(*) FROM Books"));
}
Book.java
:
@Entity
@Table(name="Books")
public class Book {
@Id
@GeneratedValue
@Column(name="id")
private int id;
@Column(name="title", nullable=false)
private String title;
...
}
How should I modify it in order to work?
Java Solutions
Solution 1 - Java
The exception message says:
> Books is not mapped [SELECT COUNT(*) FROM Books]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: Books is not mapped [SELECT COUNT(*) FROM Books]
Books
is not mapped. That is, that there is no mapped type called Books
.
And indeed, there isn't. Your mapped type is called Book
. It's mapped to a table called Books
, but the type is called Book
. When you write HQL (or JPQL) queries, you use the names of the types, not the tables.
So, change your query to:
> select count(*) from Book
Although I think it may need to be
> select count(b) from Book b
If HQL doesn't support the *
notation.
Solution 2 - Java
> hibernate3.HibernateQueryException: Books is not mapped [SELECT COUNT(*) FROM Books];
Hibernate is trying to say that it does not know an entity named "Books". Let's look at your entity:
@javax.persistence.Entity
@javax.persistence.Table(name = "Books")
public class Book {
Right. The table name for Book
has been renamed to "Books" but the entity name is still "Book" from the class name. If you want to set the entity name, you should use the @Entity
annotation's name instead:
// this allows you to use the entity Books in HQL queries
@javax.persistence.Entity(name = "Books")
public class Book {
That sets both the entity name and the table name.
The opposite problem happened to me when I was migrating from the Person.hbm.xml
file to using the Java annotations to describe the hibernate fields. My old XML file had:
<hibernate-mapping package="...">
<class name="Person" table="persons" lazy="true">
...
</hibernate-mapping>
And my new entity had a @Entity(name=...)
which I needed to set the name of the table.
// this renames the entity and sets the table name
@javax.persistence.Entity(name = "persons")
public class Person {
...
What I then was seeing was HQL errors like:
QuerySyntaxException: Person is not mapped
[SELECT id FROM Person WHERE id in (:ids)]
The problem with this was that the entity name was being renamed to persons
as well. I should have set the table name using:
// no name = here so the entity can be used as Person
@javax.persistence.Entity
// table name specified here
@javax.persistence.Table(name = "persons")
public class Person extends BaseGeneratedId {
Solution 3 - Java
This answer comes late but summarizes the concept involved in the "table not mapped" exception(in order to help those who come across this problem since its very common for hibernate newbies). This error can appear due to many reasons but the target is to address the most common one that is faced by a number of novice hibernate developers to save them hours of research. I am using my own example for a simple demonstration below.
The exception:
org.hibernate.hql.internal.ast.QuerySyntaxException: subscriber is not mapped [ from subscriber]
In simple words, this very usual exception only tells that the query is wrong in the below code.
Session session = this.sessionFactory.getCurrentSession();
List<Subscriber> personsList = session.createQuery(" from subscriber").list();
This is how my POJO class is declared:
@Entity
@Table(name = "subscriber")
public class Subscriber
But the query syntax "from subscriber" is correct and the table subscriber
exists. Which brings me to a key point:
- It is an HQL query not SQL.
and how its explained here
> HQL works with persistent objects and their properties not with the database tables and columns.
Since the above query is an HQL one, the subscriber
is supposed to be an entity name not a table name. Since I have my table subscriber
mapped with the entity Subscriber
. My problem solves if I change the code to this:
Session session = this.sessionFactory.getCurrentSession();
List<Subscriber> personsList = session.createQuery(" from Subscriber").list();
Just to keep you from getting confused. Please note that HQL is case sensitive in a number of cases. Otherwise it would have worked in my case.
> Keywords like SELECT , FROM and WHERE etc. are not case sensitive but > properties like table and column names are case sensitive in HQL.
https://www.tutorialspoint.com/hibernate/hibernate_query_language.htm
To further understand how hibernate mapping works, please read this
Solution 4 - Java
Ensure your have the Entity annotation and Table annotation and set the table name on the table annotation.
@Entity
@Table(name = "table_name")
public class TableName {
}
Solution 5 - Java
Thanks everybody. Lots of good ideas. This is my first application in Spring and Hibernate.. so a little more patience when dealing with "novices" like me..
Please read Tom Anderson and Roman C.'s answers. They explained very well the problem. And all of you helped me.I replaced
SELECT COUNT(*) FROM Books
with
select count(book.id) from Book book
And of course, I have this Spring config:
<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
<property name="packagesToScan" value="extjs.model"/>
Thank you all again!
Solution 6 - Java
In addition to the accepted answer, one other check is to make sure that you have the right reference to your entity package in sessionFactory.setPackagesToScan(...) while setting up your session factory.
Solution 7 - Java
In the Spring configuration typo applicationContext.xml
where the sessionFactory
configured put this property
<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
<property name="packagesToScan" value="${package.name}"/>
Solution 8 - Java
Hibernate also is picky about the capitalization. By default it's going to be the class name with the First letter Capitalized. So if your class is called FooBar
, don't pass "foobar"
. You have to pass "FooBar"
with that exact capitalization for it to work.
Solution 9 - Java
I had same problem , instead @Entity I used following code for getting records
List<Map<String, Object>> list = null;
list = incidentHibernateTemplate.execute(new HibernateCallback<List<Map<String, Object>>>() {
@Override
public List<Map<String, Object>> doInHibernate(Session session) throws HibernateException {
Query query = session.createSQLQuery("SELECT * from table where appcode = :app");
query.setParameter("app", apptype);
query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
return query.list();
}
});
I used following code for update
private @Autowired HibernateTemplate incidentHibernateTemplate;
Integer updateCount = 0;
updateCount = incidentHibernateTemplate.execute((Session session) -> {
Query<?> query = session
.createSQLQuery("UPDATE tablename SET key = :apiurl, data_mode = :mode WHERE apiname= :api ");
query.setParameter("apiurl", url);
query.setParameter("api", api);
query.setParameter("mode", mode);
return query.executeUpdate();
}
);