JPA 2 Criteria Query on Single Table Inheritance (Hibernate)

Question

My project is using JPA 2 with Hibernate and there is a single table inheritance setup for dealing with two types of customer. When I try to query data on customers by using Spring Data JPA Specification, I always get incorrect result. I think it's because I create the query wrong and still got no idea how to make it right.

Here is my test code (I am trying search customer by company name):

@Test
public void test() {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Customer> customerQuery = criteriaBuilder.createQuery(Customer.class);
    Root<Customer> customerRoot = customerQuery.from(Customer.class);
    Root<CompanyCustomer> companyCustomerRoot = customerQuery.from(CompanyCustomer.class);

    CriteriaQuery<Customer> select = customerQuery.select(customerRoot);
    select.where(criteriaBuilder.equal(companyCustomerRoot.get(CompanyCustomer_.companyName), "My Company Name"));

    TypedQuery<Customer> query = entityManager.createQuery(select);
    List<Customer> results = query.getResultList();

    assertEquals(1, results.size()); // always got size 2
}

SQL script in the log:

Hibernate: 
    select
        customer0_.id as id2_16_,
        customer0_.type as type1_16_,
        customer0_.first_name as first_na8_16_,
        customer0_.last_name as last_nam9_16_,
        customer0_.company_name as company13_16_ 
    from
        customers customer0_ cross 
    join
        customers companycus1_ 
    where
        companycus1_.type='COMPANY' 
        and companycus1_.company_name=?

There are two records in my database:

insert into customers (id, type, company_name) values (1, 'COMPANY', 'My Company Name');

insert into customers (id, type, first_name, last_name) values (2, 'PERSONAL', 'My First Name', 'My Last Name');

My single table inheritance setup:

@Entity
@Table(name = "customers")
@DiscriminatorColumn(name = "type", discriminatorType = DiscriminatorType.STRING)
public abstract class Customer {    

    @Enumerated(EnumType.STRING)
    @Column(name = "type", insertable = false, updatable = false)
    private CustomerType type;
    private String contactNumber;
}

@Entity
@DiscriminatorValue("PERSONAL")
public class PersonalCustomer extends Customer {          

    private String firstName;
    private String lastName;    
}

@Entity
@DiscriminatorValue("COMPANY")
public class CompanyCustomer extends Customer {

    private String companyName;
}

public enum CustomerType {

    COMPANY, PERSONAL;
}

It will return 2 rows as you can see there is a cross join in SQL produced by hibernate. Please can you explain what you want to fetch from database — Sangram Jadhav, Jun 01 '16 at 04:02
@SangramJadhav I am trying to search customer by company name. Actually I am working on a search function and test code is one of the use case. User will be able to search by first name or last name as well. — user1831877, Jun 01 '16 at 04:11
You need two different methods to search for company and person. That way you can query by company name or customer name only. Your service layer can decide which method to call depending upon CustomerType — Sangram Jadhav, Jun 01 '16 at 04:18
@SangramJadhav Thanks for quick response. Since there are some common fields in Customer entity. ex: contactNumber. What should I do with searching by common fields and also I have to deal with paging if I use two methods for searching different customer. — user1831877, Jun 01 '16 at 04:25
Then instead of two entities you can have one entity which will represent the database table. You can create mapping layer to create DTO objects from that entity. For search, you can use entity, but for specific requirement, you can use mapping layer to map entity to DTO object based upon type. — Sangram Jadhav, Jun 01 '16 at 05:09
Try this instead of all your used code: `TypedQuery query = entityManager.createQuery("SELECT c FROM CompanyCustomer c", CompanyCustomer.class); List results = query.getResultList();` — Mohamed Nabli, Jun 01 '16 at 07:27
@MohamedNabli I think your solution only pass the test above and test would failed if I add one more company customer record to database. Still many thanks. — user1831877, Jun 01 '16 at 11:17
@MohamedNabli I add one more company customer record and the test failed. Expected result size is 1 but I got 2. — user1831877, Jun 02 '16 at 03:31
when you have added One more company customer that means that you have 2 Company customers the result is 2 and that means that the query I gave you dosn't fetch Personal customers. — Mohamed Nabli, Jun 02 '16 at 07:32

score 0 · Answer 1 · edited May 23 '17 at 10:34

0

I found the answer from JPA Criteria Query over an entity hierarchy using single table inheritance

Solution: Use CriteriaBuilder.treat() to downcast Customer entity instead of using CriteriaQuery.from()

edited May 23 '17 at 10:34

Community

1
1

answered Jun 02 '16 at 05:14

user1831877

113
2
10

JPA 2 Criteria Query on Single Table Inheritance (Hibernate)

1 Answers1

Linked