CODEWITHSUNDEEP
javamysqlhibernatehibernate-criteria
Almett
Almett

Reputation: 906

How to implement this multiple select and where query with Hibernate criteria?

I have faced a Hibernate criteria query issues.

Table DataStatus data looks like below:

|-------------|--------------|--------------|---------------------|
|     name    |     info     |    server    |      starttime      |
|-------------|--------------|--------------|---------------------|
|     Bob     | information1 |     www1     | 2018-02-14 10:32:43 |
|     Alice   | information2 |     www3     | 2018-02-14 17:34:43 |
|     Bob     | information3 |     www2     | 2018-02-14 10:32:43 |
|     Alice   | information4 |     www1     | 2018-02-14 11:25:51 |
|     Alice   | information5 |     www2     | 2018-02-14 08:42:25 |
|     Bob     | information6 |     www3     | 2018-02-14 10:32:43 |
|-------------|--------------|--------------|---------------------|

Query looks like this:

SELECT * FROM DataStatus sts 
WHERE sts.server IS NOT NULL 
AND sts.name = 'Bob' 
AND sts.starttime < (
   SELECT starttime FROM DataStatus 
   WHERE name = 'Alice' AND server = sts.server);

And the result looks like this:

|-------------|--------------|--------------|---------------------|
|     name    |     info     |    server    |      starttime      |
|-------------|--------------|--------------|---------------------|
|     Bob     | information1 |     www1     | 2018-02-14 10:32:43 |
|     Bob     | information6 |     www3     | 2018-02-14 10:32:43 |
|-------------|--------------|--------------|---------------------|

I have tried something like below:

Criteria criteria = session.CreateCriteria(DataStatus.class);
criteria.add(
    Restrictions.and(
        criteria.add(Restrictions.isNotNull("server")),
        criteria.add(Restrictions.eq("name", "Bob")),
        criteria.add(Restrictions.lt("starttime", ))
    )
);

I have no idea how to implement this nested where and select query with Hibernate criteria?

Thanks in advance.

Upvotes: 1

Views: 2403

Answers (2)

M A
M A

Reputation: 72844

My advice is to keep trying, look more in the Javadocs, experiment in your IDE. And if you give up, try the below (assuming your table is modeled in class DataStatus):

CriteriaBuilder criteriaBuilder = session.getCriteriaBuilder();
CriteriaQuery<DataStatus> criteriaQuery = criteriaBuilder.createQuery(DataStatus.class);
Root<DataStatus> root = criteriaQuery.from(DataStatus.class);

Subquery<Date> subquery = criteriaQuery.subquery(Date.class);
Root<DataStatus> innerRoot = subquery.from(DataStatus.class);

subquery.select(innerRoot.get("startTime"))
        .where(criteriaBuilder.and(criteriaBuilder.equal(innerRoot.get("name"), "Alice"),
                criteriaBuilder.equal(innerRoot.get("server"), root.get("server"))));

criteriaQuery.select(root).where(
        criteriaBuilder.and( criteriaBuilder.isNotNull( root.get( "server" ) ),
                criteriaBuilder.equal(root.get("name"), "Bob" ),
                criteriaBuilder.lessThan(root.<Date> get("startTime"), subquery)

        ) );

Query<DataStatus> query = session.createQuery(criteriaQuery);
List<DataStatus> resultList = query.getResultList();

Upvotes: 4

Arth
Arth

Reputation: 13110

I'm not familiar with Hibernate, but you can rewrite the query to avoid the subselect

   SELECT bob.* /** Only include cols from Bob records */
     FROM DataStatus bob 

          /** Only include rows with a later Alice record on the same server */   
     JOIN DataStatus alice
       ON alice.name = 'Alice'
      AND alice.server = bob.server
      AND alice.starttime > bob.starttime

    WHERE bob.name = 'Bob'

You may find this plays nicer with the Hibernate syntax

N.B. This assumes that you have a maximum of one record per name and server. This could be enforced by a UNIQUE KEY on (name,server)

This assumption is based on the condition WHERE {bob server row starttime} < SELECT {alice server row starttime}from your original query, which doesn't really make sense if the SELECT can return more than one row

Upvotes: 3

Related Questions