tjholmes66 tjholmes66 - 1 year ago 142
MySQL Question

Hibernate 5 Query By Example adds criteria

So, I am using the latest Hibernate 5.x version. I am trying to do a simple Query By Example.

Here is the hibernate entity class:

@Table(name = "company")
public class CompanyEntity implements Serializable
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "company_id")
private long companyId;

// `account_enabled` varchar(15) NOT NULL,
@Column(name = "company_enabled", columnDefinition = "BIT")
private boolean companyEnabled;

@Column(name = "company_name")
private String companyName;

@Column(name = "company_address1")
private String address1;

@Column(name = "company_address2")
private String address2;

@Column(name = "company_city")
private String addressCity;

@Column(name = "company_state")
private String addressState;

@Column(name = "company_postal_code")
private String addressPostalCode;

@Column(name = "company_country")
private String addressCountry;

@Column(name = "company_phone")
private String phone;

@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "company_parent")
private CompanyEntity parent;

@Column(name = "entered_by")
private long enteredBy;

@Column(name = "entered_date")
private Date enteredDate;

@Column(name = "edited_by")
private long editedBy;

@Column(name = "edited_date")
private Date editedDate;

... getters, setters, hashCode, equals, toString
as auto-generated by Eclipse

and here is the query by example in the Dao:

public List<CompanyEntity> getCompanyEntityByExample(CompanyEntity exampleEntity)
Criteria criteria = this.sessionFactory.getCurrentSession().createCriteria(CompanyEntity.class).add(Example.create(exampleEntity));

List<CompanyEntity> companyEntityList = criteria.list();

System.out.println("getCompanyEntityByExample: companyEntityList: size=" + companyEntityList.size());
System.out.println("getCompanyEntityByExample: companyEntityList=" + companyEntityList);

return companyEntityList;

and here is the test to test this out:

public void testGetCompanyByExample()
String addressCity = "Boston";
CompanyEntity exampleEntity = new CompanyEntity();
List<CompanyEntity> companyList = companyDao.getCompanyEntityByExample(exampleEntity);
assertEquals(true, companyList.size() == 1);

I know with the database records, there should be at least one record returned, but in this case, I don't get any data.

Here is the actual query getting executed:

select this_.company_id as company_1_1_1_, this_.company_address1 as company_2_1_1_, this_.company_address2 as company_3_1_1_, this_.company_city as company_4_1_1_, this_.company_country as company_5_1_1_, this_.company_postal_code as company_6_1_1_, this_.company_state as company_7_1_1_, this_.company_enabled as company_8_1_1_, this_.company_name as company_9_1_1_, this_.edited_by as edited_10_1_1_, this_.edited_date as edited_11_1_1_, this_.entered_by as entered12_1_1_, this_.entered_date as entered13_1_1_, this_.company_parent as company15_1_1_, this_.company_phone as company14_1_1_, companyent2_.company_id as company_1_1_0_, companyent2_.company_address1 as company_2_1_0_, companyent2_.company_address2 as company_3_1_0_, companyent2_.company_city as company_4_1_0_, companyent2_.company_country as company_5_1_0_, companyent2_.company_postal_code as company_6_1_0_, companyent2_.company_state as company_7_1_0_, companyent2_.company_enabled as company_8_1_0_, companyent2_.company_name as company_9_1_0_, companyent2_.edited_by as edited_10_1_0_, companyent2_.edited_date as edited_11_1_0_, companyent2_.entered_by as entered12_1_0_, companyent2_.entered_date as entered13_1_0_, companyent2_.company_parent as company15_1_0_, companyent2_.company_phone as company14_1_0_ from company this_ left outer join company companyent2_ on this_.company_parent=companyent2_.company_id where (this_.company_city=? and this_.company_enabled=? and this_.edited_by=? and this_.entered_by=?)

If you notice the where clause, it says:

where (this_.company_city=? and this_.company_enabled=? and this_.edited_by=? and this_.entered_by=?)

and it should be only:

where (this_.company_city=?)

So, the extra criteria is messing with my query and I am unable to get the correct results.

I was wondering how I can correct and fix this? I searched several times against Google, and checked the hibernate forums first before I came here.
Any help is much appreciated.


Answer Source

The fields company_enabled, edited_by and entered_by are primitives, so they have a value on creation, for boolean it is false and for long it is 0. So when the object is created the values are there, and when you invoke the filter hibernate sees the fields values and put them on query.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download