I'm using Hibernate 5.4.1 in my Spring Boot (REST, JPA, HATEOAS) application. I'm using Mysql 5.7.22 and org.hibernate.dialect.MySQL57Dialect dialect.
I'm using Mysql connector 8.0.12.
This is my enumeration class:
{code:java}
public enum ReasonForAdjustment {
SALE, PURCHASE, CORRECTION, THEFT, RETURN_FROM_CUSTOMER, RETURN_TO_SUPPLIER, DEFECTIVE, LOSS, EXPIRED, TRANSFER
}
{code}
I've created a custom method in my repository class:
{code:java}
@Override
public void test(Pageable pageable) {
TypedQuery<WarehouseTransaction> query = entityManager.createQuery("SELECT wt FROM WarehouseTransaction wt JOIN wt.contact c WHERE reasonForAdjustment=:reasonForAdjustment", WarehouseTransaction.class);
TypedQuery<Long> queryCount = entityManager.createQuery("SELECT COUNT(*) FROM WarehouseTransaction wt JOIN wt.contact c WHERE reasonForAdjustment=:reasonForAdjustment", Long.class);
query.setParameter("reasonForAdjustment", ReasonForAdjustment.PURCHASE);
queryCount.setParameter("reasonForAdjustment", ReasonForAdjustment.PURCHASE);
query.setFirstResult((int) pageable.getOffset());
query.setMaxResults(pageable.getPageSize());
long totalResult = queryCount.getSingleResult();
query.getResultList();
}
{code}
I did a JOIN with Customer on purpuse. Here you don't see any filter on customer's properties because it's not relevant for the explanation of the problem.
The query sent to Mysql is:
{code:java}
2019-04-03T12:35:45.238747Z 460010 Execute select warehouset0_.`id` as id1_34_, warehouset0_.`createdBy` as createdB2_34_, warehouset0_.`createdDate` as createdD3_34_, warehouset0_.`lastModifiedBy` as lastModi4_34_, warehouset0_.`lastModifiedDate` as lastModi5_34_, warehouset0_.`sid` as sid6_34_, warehouset0_.`version` as version7_34_, warehouset0_.`contact_id` as contact21_34_, warehouset0_.`date` as date8_34_, warehouset0_.`description` as descript9_34_, warehouset0_.`document_id` as documen22_34_, warehouset0_.`documentRow_id` as documen23_34_, warehouset0_.`productGroup` as product10_34_, warehouset0_.`product_id` as product11_34_, warehouset0_.`productManufacturer` as product12_34_, warehouset0_.`productName` as product13_34_, warehouset0_.`productType` as product14_34_, warehouset0_.`productVariant` as product15_34_, warehouset0_.`purchaseUnitPrice` as purchas16_34_, warehouset0_.`qty` as qty17_34_, warehouset0_.`reasonForAdjustment` as reasonF18_34_, warehouset0_.`store_id` as store_i24_34_, warehouset0_.`type` as type19_34_, warehouset0_.`unitPrice` as unitPri20_34_ from `WarehouseTransaction` warehouset0_ inner join `Contact` contact1_ on warehouset0_.`contact_id`=contact1_.`id` where reasonForAdjustment='�\�\0~r\0<cloud.optix.server.model.enums.warehouse.ReasonForAdjustment\0\0\0\0\0\0\0\0\0\0xr\0java.lang.Enum\0\0\0\0\0\0\0\0\0\0xpt\0PURCHASE' limit 2147483647
{code}
As you can see it's wrong: the enumeration is not translated well.
If I change the query removing the JOIN:
{code:java}
@Override
public void test(Pageable pageable) {
TypedQuery<WarehouseTransaction> query = entityManager.createQuery("SELECT wt FROM WarehouseTransaction wt WHERE reasonForAdjustment=:reasonForAdjustment", WarehouseTransaction.class);
TypedQuery<Long> queryCount = entityManager.createQuery("SELECT COUNT(*) FROM WarehouseTransaction wt WHERE reasonForAdjustment=:reasonForAdjustment", Long.class);
query.setParameter("reasonForAdjustment", ReasonForAdjustment.PURCHASE);
queryCount.setParameter("reasonForAdjustment", ReasonForAdjustment.PURCHASE);
query.setFirstResult((int) pageable.getOffset());
query.setMaxResults(pageable.getPageSize());
long totalResult = queryCount.getSingleResult();
query.getResultList();
}
{code}
the query sent to Mysql this time is correct:
{code:java}
2019-04-03T12:34:19.840542Z 459999 Execute select warehouset0_.`id` as id1_34_, warehouset0_.`createdBy` as createdB2_34_, warehouset0_.`createdDate` as createdD3_34_, warehouset0_.`lastModifiedBy` as lastModi4_34_, warehouset0_.`lastModifiedDate` as lastModi5_34_, warehouset0_.`sid` as sid6_34_, warehouset0_.`version` as version7_34_, warehouset0_.`contact_id` as contact21_34_, warehouset0_.`date` as date8_34_, warehouset0_.`description` as descript9_34_, warehouset0_.`document_id` as documen22_34_, warehouset0_.`documentRow_id` as documen23_34_, warehouset0_.`productGroup` as product10_34_, warehouset0_.`product_id` as product11_34_, warehouset0_.`productManufacturer` as product12_34_, warehouset0_.`productName` as product13_34_, warehouset0_.`productType` as product14_34_, warehouset0_.`productVariant` as product15_34_, warehouset0_.`purchaseUnitPrice` as purchas16_34_, warehouset0_.`qty` as qty17_34_, warehouset0_.`reasonForAdjustment` as reasonF18_34_, warehouset0_.`store_id` as store_i24_34_, warehouset0_.`type` as type19_34_, warehouset0_.`unitPrice` as unitPri20_34_ from `WarehouseTransaction` warehouset0_ where warehouset0_.`reasonForAdjustment`='PURCHASE' limit 2147483647
{code}
I found the problem, it seems to be the absense of the table qualifier in front of reasonForAdjustment property.
Even with JOIN, rewriting the query:
{code:java}
SELECT wt FROM WarehouseTransaction wt JOIN wt.contact c WHERE wt.reasonForAdjustment=:reasonForAdjustment
{code}
everything works. However I don't expect this behaviour, I think I should at least get a syntax exception in this case.
|
|
