spring mvc hiberate, use CriteriaQuery to run select * from table where

Question

what i need is to run a sql query something like :

select * from table where alpahbetcolumn="A" and numbercolumn="10" and shelfcolumn="upper";

i want to know how to do this query in hibernate using EntityManager

currently this is my own try out, but not working....

@PersistenceContext
    EntityManager em;

@Transactional
    public List<Item> listItems(String alpahbet, String number, String shelf) {
        CriteriaQuery<Item> c = em.getCriteriaBuilder().createQuery(Item.class);
        c.from(Item.class);
        c..where( em.equal( alpahbet, "alpahbetcolumn" ) && em.equal( number, "numbercolumn" ) && em.equal( shelf, "shelfcolumn" ));
        return em.createQuery(c).getResultList();
    }

i only have a very vague understanding on spring hibernate topic..still learning... can someone please point me out how to do this sql query properly, with code example. thanks

hello, can you use HQL instead of Criteria use? for me its more readeable than criteria ;) — Mançaux Pierre-Alexandre, Dec 12 '14 at 16:05
err, if u could provide me an code example with hql, that s fine :) i would like to learn that too — sefirosu, Dec 12 '14 at 16:08
see my answer (the second one ;)) because someone answer you to create a nativequery directly, but not what you want i think ;) — Mançaux Pierre-Alexandre, Dec 12 '14 at 16:13

Predrag Maric · Answer 1 · 2014-12-12T16:16:19.580

Try this

Query q = em.createNativeQuery("select * from table where alpahbetcolumn='A' and numbercolumn= 10 and shelfcolumn='upper'");
q.getResultList();

createNativeQuery() accepts plain SQL as parameter. If you expect Item as result, you can use this

em.createNativeQuery("select * from table where alpahbetcolumn='A' and numbercolumn= 10 and shelfcolumn='upper'", Item.class);

If you want to use JPQL (JPA Query Language), then we need your entity code to be sure, but it would be something like this

em.createQuery("select i from Item i where i.alphabetColumn = 'A' ");

For parameterized queries, use this

Query q = em.createNativeQuery("select * from table where alpahbetcolumn=? and numbercolumn=? and shelfcolumn=?");
q.setParameter(1, "A");
q.setParameter(2, 10);
q.setParameter(3, "upper");

as 'A' '10' 'upper' are variables... how do i deal with variables in this query? replace 'A' something like ...+ variable +... ? — sefirosu, Dec 12 '14 at 16:13

score 1 · Answer 2 · edited May 23 '17 at 10:28

HQL is like this:

    StringBuilder queryBuilder = new StringBuilder();
    queryBuilder.append(" FROM table");
    queryBuilder.append(" WHERE alpahbetcolumn= :codeA");
    queryBuilder.append(" AND numbercolumn= :numColumn");
    queryBuilder.append(" AND shelfcolumn= :upper");

    Query query = getSession().createQuery(queryBuilder.toString());
    query = query.setParameter("codeA", "A");
    query.setParameter("numColumn", "10");
    query.setParameter("upper", "upper");


    query.list(); to get your result ;)

and getSession() come from :

private SessionFactory sessionFactory;

@Required
@Autowired
public void setSessionFactory(SessionFactory sessionFactory) {
    this.sessionFactory = sessionFactory;
}

protected Session getSession() {
    return sessionFactory.getCurrentSession();
}

see spring configuration to configure your sessionfactory ;)

or if you use JPA try this link

get session from entityManager

score 0 · Accepted Answer · answered Dec 12 '14 at 17:04

i have worked out using criteriaquery to fullfill my needs, here is the code. it works

 @Transactional
    public List<Item> listItems(String alpahbet, String number, String shelf) {
        CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
        CriteriaQuery<Item> criteriaQuery = criteriaBuilder.createQuery(Item.class);
        Root<Item> itemRoot = criteriaQuery.from(Item.class);
        criteriaQuery.select(itemRoot).where(criteriaBuilder.equal(itemRoot.get("alpahbetField"), alpahbet), criteriaBuilder.equal(itemRoot.get("numberField"), number), criteriaBuilder.equal(itemRoot.get("shelfField"), shelf));
        return em.createQuery(criteriaQuery).getResultList();
    }

spring mvc hiberate, use CriteriaQuery to run select * from table where

3 Answers3