query exceptions

ashiers · **Joined:** Fri Jan 26, 2007 3:51 pm **Posts:** 16

Hi there,

In my Test.java program I have the following statement:

List companies = em2.createQuery("select c.COMPANY_ID, c.COMPANY_NAME, c.ADDRESS_STREET. c.ADDRESS_CITY, c.ADDRESS_STATE, c.ADDRESS_COUNTRY, c.ADDRESS_ZIP_PCODE, c.PHONE, c.FAX, c.WEBSITE_URL, c.EDUCATIONAL, c.LOGO_FILETYPE from COMPANIES c order by c.COMPANY_NAME asc").getResultList();

Before this statement, the test program successfully inserts one row of company data. When trying to execute the query the console prints the following:

[java] Hibernate:
[java] call identity()
[java] Exception in thread "main" java.lang.IllegalArgumentException: org.h
ibernate.hql.ast.QuerySyntaxException: COMPANIES is not mapped [select c.COMPANY
_ID, c.COMPANY_NAME, c.ADDRESS_STREET. c.ADDRESS_CITY, c.ADDRESS_STATE, c.ADDRES
S_COUNTRY, c.ADDRESS_ZIP_PCODE, c.PHONE, c.FAX, c.WEBSITE_URL, c.EDUCATIONAL, c.
LOGO_FILETYPE from COMPANIES c order by c.COMPANY_NAME asc]
[java] at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceE
xception(AbstractEntityManagerImpl.java:616)
[java] at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(Abstr
actEntityManagerImpl.java:95)
[java] at scholastic.models.Test.main(Unknown Source)

There seems to be two exceptions here: java.lang.IllegalArgumentException and org.hibernate.hql.ast.QuerySyntaxException

I don't know what is going on. Could someone please explain what I have to do to fix this?

Please advise,

Alan

tsar bomba · **Joined:** Mon Nov 14, 2005 7:33 pm **Posts:** 73

Is there any particular reason you're specifying every field in the table using a native SQL query?

createQuery() expects a JPA-QL query as a param, not straight SQL. Use createNativeQuery() if you need native SQL for some reason.

Otherwise, query your entity, like so (assuming Company is your entity):

Code:

select c from Company c

That being said, your exception messages make perfect sense. I'd advise you to read through the excellent Hibernate/JPA documentation for more examples.

Hope this helps.

ashiers · **Joined:** Fri Jan 26, 2007 3:51 pm **Posts:** 16

tsar bomba wrote:

Is there any particular reason you're specifying every field in the table using a native SQL query?

createQuery() expects a JPA-QL query as a param, not straight SQL. Use createNativeQuery() if you need native SQL for some reason.

Otherwise, query your entity, like so (assuming Company is your entity):

Code:

select c from Company c

That being said, your exception messages make perfect sense. I'd advise you to read through the excellent Hibernate/JPA documentation for more examples.

This is my first attempt at using Hibernate, and I'm learning as I go along. I've obtained the book "Java Persistence With Hibernate" and have only got as far as the end of chapter 6. So, to date, I can only go by the examples in the book. The earliest example in the book shows a query for the HelloWorld app. I was just following that example.

The table name is "Companies" even though the object name is singular "Company", and when I tried to run the query with
select c from Companies... I wound up with some other exception. I wanted it to return all fields and tried using the * syntax but it didn't like that either. Naming all the fields was my last option. Well, obviously that is causing other problems also.

Notice my class declaration:

Code:

package scholastic.models;

import javax.persistence.*;
import java.io.Serializable;
import java.util.*;

@Entity
@Table(name = "COMPANIES")
public class Company implements Serializable
{
  ...
}

Is this a bad practice? I didn't think it would be, but maybe I'm wrong? If so why?

Alan

andydale · **Posted:** Wed Jan 31, 2007 4:18 am

Hi,

If you want to select all your company entities try this following query (always select from the entity and not the table name):

Code:

FROM Company c

This should bring back every Company entity persisted.

Cheers,

Andy

tsar bomba · **Joined:** Mon Nov 14, 2005 7:33 pm **Posts:** 73

ashiers wrote:

tsar bomba wrote:

Is there any particular reason you're specifying every field in the table using a native SQL query?

createQuery() expects a JPA-QL query as a param, not straight SQL. Use createNativeQuery() if you need native SQL for some reason.

Otherwise, query your entity, like so (assuming Company is your entity):

Code:

select c from Company c

That being said, your exception messages make perfect sense. I'd advise you to read through the excellent Hibernate/JPA documentation for more examples.

This is my first attempt at using Hibernate, and I'm learning as I go along. I've obtained the book "Java Persistence With Hibernate" and have only got as far as the end of chapter 6. So, to date, I can only go by the examples in the book. The earliest example in the book shows a query for the HelloWorld app. I was just following that example.

The table name is "Companies" even though the object name is singular "Company", and when I tried to run the query with
select c from Companies... I wound up with some other exception. I wanted it to return all fields and tried using the * syntax but it didn't like that either. Naming all the fields was my last option. Well, obviously that is causing other problems also.

Notice my class declaration:

Code:

package scholastic.models;

import javax.persistence.*;
import java.io.Serializable;
import java.util.*;

@Entity
@Table(name = "COMPANIES")
public class Company implements Serializable
{
  ...
}

Is this a bad practice? I didn't think it would be, but maybe I'm wrong? If so why?

Alan

To make a long story short, you're trying to write standard SQL code where you should be using Hibernate's HQL or standard JPA-QL - both are shortened query languages for querying your entity classes, not database tables.

The idea is to abstract your database w/ entities...so you're working w/ objects, not tables, i.e. object graphs vs. relational data.

You'll find once you start building apps this way, it's much, much easier to manage and maintain them.

Read through the chapters in your book about Hibernate Query Language and JPA Query Language....this should help clear things up.

ashiers · **Joined:** Fri Jan 26, 2007 3:51 pm **Posts:** 16

Quote:

To make a long story short, you're trying to write standard SQL code where you should be using Hibernate's HQL or standard JPA-QL - both are shortened query languages for querying your entity classes, not database tables.

The idea is to abstract your database w/ entities...so you're working w/ objects, not tables, i.e. object graphs vs. relational data.

You'll find once you start building apps this way, it's much, much easier to manage and maintain them.

Read through the chapters in your book about Hibernate Query Language and JPA Query Language....this should help clear things up.

Well, that certainly helps to clear things up a bit. In the HelloWorld example the authors just provide a couple of test programs which have queries in them (which look just like SQL) but don't explain they they aren't really. That is they don't go into any great depth of explanation. I just assumed we continued to use SQL queries!

I tried changing the query to "select c from COMPANY c" but I'm still getting this silly exception:

[java] Exception in thread "main" java.lang.IllegalArgumentException: org.h
ibernate.hql.ast.QuerySyntaxException: COMPANY is not mapped [select c from COMPANY c]
[java] at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:616)
[java] at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:95)
[java] at scholastic.models.Test.main(Unknown Source)

What does it mean it's not mapped? Of course it is. I proved that is was earlier:

Code:

package scholastic.models;

//import org.hibernate.annotations.*;
import javax.persistence.*;
import java.io.Serializable;
import java.util.*;

@Entity
@Table(name = "COMPANIES")
public class Company implements Serializable
{
   @Id @GeneratedValue(strategy = GenerationType.AUTO)
   @Column(name = "COMPANY_ID")
   private Long id = null;

   ...
}

What does it really want me to do?

Alan

tsar bomba · **Joined:** Mon Nov 14, 2005 7:33 pm **Posts:** 73

The name of your entity is Company not COMPANY ;)

ashiers · **Joined:** Fri Jan 26, 2007 3:51 pm **Posts:** 16

Aah!! So it's case sensitive too!

Thanks,

Alan