I have the following snippet that we use to persist an entity into our DB:
private void singleStatusChange( Employee employee , OrderStatusType type , String comment , String classification ){
try( CloseableEntityManager em = HibernateUtil.getEntityManager() ){
em.beginTransaction();
OrderStatusChangeRequest orderStatusChangeRequest = new OrderStatusChangeRequest();
orderStatusChangeRequest.setOrder( em.getEntityReferenceById( Order.class , this.getId() ) );
orderStatusChangeRequest.setOrderStatusType( type );
orderStatusChangeRequest.setEmployee( employee );
orderStatusChangeRequest.setComment( comment );
orderStatusChangeRequest.setClassification( classification );
//When this entry is being persisted a BEFORE INSERT Trigger is activated and 1.- Check if status change is feasible, 2.- Expire all previous order statuses, 3.- Updates order lastHandledBy
em.persist( orderStatusChangeRequest );
em.commit();
}catch ( Exception e ){
e.printStackTrace();
throw new InvalidOrderStatusChangeException( e );
}
}
When this occurs, a trigger is fired on BEFORE INSERT that takes care of additional business logic for our app. Everything except one statement is completed, and Im not entirely sure why this is the case. This is our trigger:
CREATE DEFINER=`gofarma`@`%` TRIGGER OrderStatusChangeRequestsBeforeInsert
BEFORE INSERT
ON OrderStatusChangeRequests FOR EACH ROW
BEGIN
SELECT COUNT(1) INTO @IsNewStatusPossible FROM OrderStatusTypeFlows ostf WHERE ostf.`To` = NEW.Status AND ostf.`From` IS NULL ;
SELECT COUNT(1) INTO @IsPossible FROM OrderStatusTypeFlows ostf WHERE ostf.`To` = NEW.Status AND ostf.`From` = ( SELECT o.Status FROM Orders o WHERE o.ID = NEW.`Order` LIMIT 1 );
SELECT COUNT(1) INTO @IsImpossible FROM OrderStatusTypeFlows ostf WHERE ostf.`To` = NEW.Status AND ostf.`NeverIfThisPresent` IN ( SELECT `OrderStatusType` FROM OrderHistoryEntries WHERE `Order` = NEW.`Order` ) ;
SELECT COUNT(1) INTO @IsAdmin FROM Employees e LEFT JOIN EmployeeRoles er ON e.ID = er.EmployeeID LEFT JOIN Roles r ON er.RoleID = r.ID
WHERE r.IsAdmin AND e.ID = NEW.Employee;
IF( ( ( @IsPossible = 0 AND @IsNewStatusPossible = 0 ) OR @IsImpossible > 0 ) AND @IsAdmin = 0 ) THEN
SIGNAL SQLSTATE '12345' set message_text='com.gofarma.olimpo.exceptions.InvalidOrderStatusChangeException';
END IF;
UPDATE OrderHistoryEntries os SET os.EffectiveTo = CURRENT_TIMESTAMP WHERE os.`Order` = NEW.`Order` AND os.EffectiveTo > CURRENT_TIMESTAMP;
INSERT INTO OrderHistoryEntries
(ID, Comment, EffectiveFrom, EffectiveTo, OrderStatusType, PrescriptionAction, SystemFrom, SystemTo, `Type`, Employee, `Order`)
VALUES(UUID(), NEW.Comment, current_timestamp(), '9999-12-31 23:59:59', CASE
WHEN NEW.Status = 0 THEN 'NEW'
WHEN NEW.Status = 1 THEN 'PACKED'
WHEN NEW.Status = 2 THEN 'SENT'
WHEN NEW.Status = 3 THEN 'COMPLETED'
WHEN NEW.Status = 4 THEN 'CANCELED'
WHEN NEW.Status = 5 THEN 'UNKNOWN'
WHEN NEW.Status = 6 THEN 'COLLECTED'
WHEN NEW.Status = 7 THEN 'DELIVERED'
WHEN NEW.Status = 8 THEN 'PRECOMPLETED'
WHEN NEW.Status = 9 THEN 'RESENT'
WHEN NEW.Status = 10 THEN 'UNPAID'
WHEN NEW.Status = 11 THEN 'UNDELIVERED'
WHEN NEW.Status = 12 THEN 'PENDING'
WHEN NEW.Status = 13 THEN 'WAITING_FOR_ADVANCE'
WHEN NEW.Status = 14 THEN 'ADVANCE_PICKED'
WHEN NEW.Status = 15 THEN 'ADVANCE_NOT_PICKED'
END, NULL, current_timestamp(), '9999-12-31 23:59:59', 'ORDER_STATUS', NEW.Employee, NEW.`Order`);
UPDATE Orders o SET o.LastHandledBy = NEW.Employee , o.Status = NEW.Status WHERE o.ID = NEW.`Order`;
IF( NEW.Status IN ( 3 , 8 , 9 , 10 , 12 ) ) THEN
UPDATE Trips t JOIN TripOrders ot ON ot.TripID = t.ID JOIN DriverShifts ds ON ds.Driver = t.Driver
SET t.status = 1 , t.closeComment = NEW.Comment , t.arrivalTime = CURRENT_TIMESTAMP , t.realEndTime = COALESCE( t.realEndTime , CURRENT_TIMESTAMP ) , ds.AvailableSince = CURRENT_TIMESTAMP
WHERE ot.OrderID = NEW.`Order` AND t.Status = 0 AND !t.IsTransporter;
END IF;
IF( (SELECT `PrescriptionAction` FROM `Orders` `o` WHERE `o`.`ID` = NEW.`Order`) <> 0 AND NEW.`Status` = 8) THEN
UPDATE `Orders` `o` SET `o`.`PrescriptionNumber` = (
SELECT
COALESCE(MAX(`PrescriptionNumber`),0)+1
FROM
Orders
WHERE
`PrescriptionAction` = `o`.`PrescriptionAction`
AND
`Branch` = `o`.`Branch`
GROUP BY `Branch`) WHERE `o`.`ID` = NEW.`Order`;
END IF;
END
The bit that does not execute properly is the following:
UPDATE Orders o SET o.LastHandledBy = NEW.Employee , o.Status = NEW.Status WHERE o.ID = NEW.`Order`;
Everything else gets Inserted and updated. However, this statement does not execute at all. This only happens whenever we persist an entity through Hibernate, if we were to run a query to insert a new entry into our OrderStatusChangeRequest table, everything runs as expected. Is there something we are missing? Our hibernate.cfg.xml file contains the following:
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-configuration">
<hibernate-configuration>
<session-factory>
<property name="connection.driver_class">com.mysql.cj.jdbc.Driver</property>
<property name="connection.provider_class">org.hibernate.hikaricp.internal.HikariCPConnectionProvider</property>
<property name="connection.pool_size">500</property>
<!--<property name="connection.autoReconnect">true</property>-->
<!--<property name="connection.autoReconnectForPools">true</property>-->
<property name="hibernate.dialect.storage_engine">innodb</property>
<!--<property name="connection.release_mode">ON_CLOSE</property>-->
<!--<property name="show_sql">true</property>-->
<!--<property name="hbm2ddl.auto">update</property>-->
<property name="hibernate.hikari.leakDetectionThreshold">30000</property>
<property name="hibernate.connection.autocommit">true</property>
</session-factory>
</hibernate-configuration>
As additional info, CloseableEntityManager is just a wrapper for EntityManager that implements Autocloseable:
public CloseableEntityManager beginTransaction() {
if (this.getTransaction().isActive()) return this;
this.getTransaction().begin();
return this;
}
public void commit() {
this.getTransaction().commit();
}
@Override
public void close() {
logger.debug( String.format("Entity Manager %s close call",this.id) );
if( this.entityManager == null ) return;
boolean isActive = this.entityManager.getTransaction().isActive();
if( isActive || readOnly ){
logger.debug( String.format("Entity Manager %s transaction was active, rolling back",this.id) );
this.entityManager.getTransaction().rollback();
}
this.entityManager.close();
}
