hibernate: how to select all rows in a table

Question

I try to do something like Select * from LogEntry with Hibernate. insert works fine:

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

[...]
protected EntityManager manager;

protected final String tableName = "LogEntry";

public DatabaseImpl(DB_TYPE db) {
    this.db = db;
    if (entityManagerFactory != null && entityManagerFactory.isOpen()) {
        entityManagerFactory.close();
    }
    entityManagerFactory = Persistence.createEntityManagerFactory(db.getPersUnit());
    manager = entityManagerFactory.createEntityManager();
}
public void insert(LogEntry entry) {

    manager.getTransaction().begin();
    manager.persist(entry);
    manager.getTransaction().commit();
}

But when I try to get the inserted values using this method: public LogEntryList getAll() {

    manager.getTransaction().begin();

    Query query = manager.createNativeQuery("SELECT * FROM " + tableName + ";");
    ArrayList<LogEntry> entries = (ArrayList<LogEntry>) query.getResultList();
    manager.getTransaction().commit();

    return new LogEntryList(entries);
}

I always get the Exception: java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to de.motza.entities.LogEntry

I know the problem is the casting of the Query result to the object, but I can't find anywhere how to cast the objects properly, or how to get more than one row from the table.

does anybody have any advice? If requested, I can post my persistence.xml and more code

Solution 1

Becuase you have used nativeQuery so you need to transfer result by using setResultTransormer method.

Query query = manager.createNativeQuery("SELECT * FROM " + tableName + ";");
query.setResultTransformer(Transformers.aliasToBean(LogEntry.class))
ArrayList<LogEntry> entries = (ArrayList<LogEntry>) query.getResultList();

Solution 2

You can use session.createCriteria(MyEntity.class).list(); for example.

ref: Retrieving all rows of a table without HQL?

Solution 3

For starters, you should try to take advantage of HQL, Hibernate Query Language. In the example you gave above, you are trying to execute a native SQL query. The reason you are getting the ClassCastException is that the native query is circumventing the framework and returning raw Objects instead of the type you want.

Try using this code instead for your SELECT *:

String hql = "from LogEntry";
Session session = entityManagerFactory.openSession();
Query query = session.createQuery(hql);
List<LogEntry> logEntries = query.list();      // no ClassCastException here

Solution 4

The "problem" is, that you are sending a native query which will return an Object[] array, with one value for each dolumn. You do not need to call a native query, but a hibernate query, for example...

manager.createQuery("SELECT l FROM LogEntry");

see, for example, this answer.

Solution 5

If you are using Spring HibernateDaoSupport, you can use these methods to load Entities from database:

a) single entity by ID

@Override
public DeleteProcessNotification getDeleteProcessNotificationById(Long id) {
    return this.getHibernateTemplate().load(DeleteProcessNotification.class, id);
}

b) all entities

@Override
public List<DeleteProcessNotification> getDeleteProcessNotificationsInQueue() {
    return this.getHibernateTemplate().loadAll(DeleteProcessNotification.class);
}

c) entities with a HQL query

@Override
public List<DeleteProcessNotification> getPrioritizedDeleteProcessNotifications() {
    return (List<DeleteProcessNotification>) this.getHibernateTemplate().find(
            "FROM DeleteProcessNotification dpn \n" +
                    "WHERE dpn.priority = 'HIGH'");
}

HibernateDaoSupport documentation: https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/orm/hibernate5/support/HibernateDaoSupport.html

View more solutions

Comments

[gumuruh]

where do you get Transformers.aliasToBean() come from?

[Venkatesh Manohar]

The method createCriteria(Class) from the type SharedSessionContract is deprecated now.

[Matthew]

While not exactly what the user wanted, this is certainly beneficial information. It was for me. Thank you for your answer @Tim Biegeleisen :)