@Formula attributes on parent not refreshing on save of children

Gene.Williams · August 1, 2021, 5:46am

I have a standard OneToMany relationship where the parent has four formula attributes that are counts or sums of children based on a an attribute of the child. For example, the parent has a sum of the child QuantityOrdered attribute where the child record is of a specific type, use Color as an example. A formula summing the child QuantityOrdered where the child color equals RED.

If I change the child QuantityOrdered value I need to see an updated value returned by the parent formula. But it isn’t updating.

I assume the value returned by the parent formula is cached and not refreshed from the database. I have saved the change to the database by calling saveOrRefresh() on the children and then on the parent. Calling refresh() has no effect. I only see an updated value if I initiate a new transaction after calling commit() in a test method, or using an application function refresh the page by executing a query after saving.

Is there a way to refresh formulas within a transaction? Specifically, formulas in a parent that are aggregates of child values? If I delete a child record the parent formula refreshes as expected, as the collection has changed.

The formula is a simple SUM aggregate in Oracle. The formula works properly when the parent and children are retrieved from the database.

Standard Lazy Loading fetch type.

Using Hibernate 5.5.4 (with Spring 5, in an Spring MVC application. No Spring Boot).

beikov · August 2, 2021, 8:06am

I don’t know what Spring Data JPA does when you call saveOrRefersh, but if you use entityManager.refresh on the object, the formula should be properly refreshed. What SQL is executed when you do that?

Gene.Williams · August 2, 2021, 9:09pm

From the output it seems my test (and assume the same in the app) is not refreshing the parent since the new values are not yet committed to the database. which makes sense even to this newbie.

The problem in the app (Spring MVC) is calling the controller method to refresh the screen post-save does not refresh unless the user explicitly clicks ‘Search’ again. But this may be an issue with my Spring app, not Hibernate.

GET PARENT
select
gpoprintbu0_.BUDGET_ID as budget_id1_4_0_,
gpoprintbu0_.CREATED_BY as created_by2_4_0_,
gpoprintbu0_.DATE_CREATED as date_created3_4_0_,
gpoprintbu0_.DATE_MODIFIED as date_modified4_4_0_,
gpoprintbu0_.TERRITORY_ID as territory_id12_4_0_,
gpoprintbu0_.TITLE_ID as title_id13_4_0_,
gpoprintbu0_.IMAX_BUDGET as imax_budget5_4_0_,
gpoprintbu0_.MODIFIED_BY as modified_by6_4_0_,
gpoprintbu0_.MPM_TITLE_ID as mpm_title_id7_4_0_,
gpoprintbu0_.OPTLOCKID as optlockid8_4_0_,
gpoprintbu0_.RELEASE_DATE as release_date9_4_0_,
gpoprintbu0_.SAP_TERRITORY_ID as sap_territory_id10_4_0_,
gpoprintbu0_.SCREENS_BUDGET as screens_budget11_4_0_,
(SELECT
count(*)
FROM
GPO_PRINT_ORDER orders
WHERE
orders.budget_id = gpoprintbu0_.budget_id) as formula1_0_,
(SELECT
NVL(SUM(orders.NUMBER_OF_HARD_DRIVES),
0)
FROM
GPO_PRINT_ORDER orders
WHERE
orders.budget_id = gpoprintbu0_.budget_id) as formula2_0_,
(SELECT
NVL(SUM(orders.NUMBER_ORDERED),
0)
FROM
GPO_PRINT_ORDER orders,
GPO_VERSION version
WHERE
orders.budget_id = gpoprintbu0_.budget_id
AND orders.version_id = version.version_id
AND version.format NOT IN (
‘SAT’,‘ASPERA’,‘S_IMAX’,‘A_IMAX’
)) as formula3_0_,
(SELECT
NVL(SUM(orders.NUMBER_ORDERED),
0)
FROM
GPO_PRINT_ORDER orders,
GPO_VERSION version
WHERE
orders.budget_id = gpoprintbu0_.budget_id
AND orders.version_id = version.version_id
AND version.format IN (
‘SAT’,‘S_IMAX’
)) as formula4_0_
from
GPO_PRINT_BUDGET gpoprintbu0_
where
gpoprintbu0_.BUDGET_ID=?

VALUES:
ID: 14115
Formula 17
Formula 43
Formula 55

GET CHLDREN collection
select
gpoprintor0_.BUDGET_ID as budget_id14_5_0_,
gpoprintor0_.ORDER_ID as order_id1_5_0_,
gpoprintor0_.ORDER_ID as order_id1_5_1_,
gpoprintor0_.CREATED_BY as created_by2_5_1_,
gpoprintor0_.DATE_CREATED as date_created3_5_1_,
gpoprintor0_.DATE_MODIFIED as date_modified4_5_1_,
gpoprintor0_.DELIVERY_DATE_REQUIRED as delivery_date_requ5_5_1_,
gpoprintor0_.BUDGET_ID as budget_id14_5_1_,
gpoprintor0_.TERRITORY_ID as territory_id15_5_1_,
gpoprintor0_.TITLE_ID as title_id16_5_1_,
gpoprintor0_.VERSION_ID as version_id17_5_1_,
gpoprintor0_.MODIFIED_BY as modified_by6_5_1_,
gpoprintor0_.NOTES as notes7_5_1_,
gpoprintor0_.NUMBER_BUDGETED as number_budgeted8_5_1_,
gpoprintor0_.NUMBER_OF_HARD_DRIVES as number_of_hard_dri9_5_1_,
gpoprintor0_.NUMBER_ORDERED as number_ordered10_5_1_,
gpoprintor0_.OPTLOCKID as optlockid11_5_1_,
gpoprintor0_.ORDER_COMPLETED as order_completed12_5_1_,
gpoprintor0_.ORDER_DATE_REQUIRED as order_date_requir13_5_1_,
(SELECT
version.description||gpoprintor0_.order_id
FROM
GPO_VERSION version
WHERE
version.version_id = gpoprintor0_.version_id ) as formula5_1_
from
GPO_PRINT_ORDER gpoprintor0_
where
gpoprintor0_.BUDGET_ID=?
order by
(SELECT
version.description||gpoprintor0_.order_id
FROM
GPO_VERSION version
WHERE
version.version_id = gpoprintor0_.version_id ) asc nulls last

update order -
update order -
update order -
update budget -
refresh budget -

GET PARENT

select
    gpoprintbu0_.BUDGET_ID as budget_id1_4_0_,
    gpoprintbu0_.CREATED_BY as created_by2_4_0_,
    gpoprintbu0_.DATE_CREATED as date_created3_4_0_,
    gpoprintbu0_.DATE_MODIFIED as date_modified4_4_0_,
    gpoprintbu0_.TERRITORY_ID as territory_id12_4_0_,
    gpoprintbu0_.TITLE_ID as title_id13_4_0_,
    gpoprintbu0_.IMAX_BUDGET as imax_budget5_4_0_,
    gpoprintbu0_.MODIFIED_BY as modified_by6_4_0_,
    gpoprintbu0_.MPM_TITLE_ID as mpm_title_id7_4_0_,
    gpoprintbu0_.OPTLOCKID as optlockid8_4_0_,
    gpoprintbu0_.RELEASE_DATE as release_date9_4_0_,
    gpoprintbu0_.SAP_TERRITORY_ID as sap_territory_id10_4_0_,
    gpoprintbu0_.SCREENS_BUDGET as screens_budget11_4_0_,
    (SELECT
        count(*) 
    FROM
        GPO_PRINT_ORDER orders 
    WHERE
        orders.budget_id = gpoprintbu0_.budget_id) as formula1_0_,
    (SELECT
        NVL(SUM(orders.NUMBER_OF_HARD_DRIVES),
        0) 
    FROM
        GPO_PRINT_ORDER orders 
    WHERE
        orders.budget_id = gpoprintbu0_.budget_id) as formula2_0_,
    (SELECT
        NVL(SUM(orders.NUMBER_ORDERED),
        0) 
    FROM
        GPO_PRINT_ORDER orders,
        GPO_VERSION version 
    WHERE
        orders.budget_id = gpoprintbu0_.budget_id 
        AND orders.version_id = version.version_id 
        AND version.format NOT IN (
            'SAT','ASPERA','S_IMAX','A_IMAX'
        )) as formula3_0_,
    (SELECT
        NVL(SUM(orders.NUMBER_ORDERED),
        0) 
    FROM
        GPO_PRINT_ORDER orders,
        GPO_VERSION version 
    WHERE
        orders.budget_id = gpoprintbu0_.budget_id 
        AND orders.version_id = version.version_id 
        AND version.format IN (
            'SAT','S_IMAX'
        )) as formula4_0_ 
from
    GPO_PRINT_BUDGET gpoprintbu0_ 
where
    gpoprintbu0_.BUDGET_ID=?

SAME VALUES ?

** 14115
** 17
** 43
** 55

COMMIT (3 children)
update
GPO_PRINT_ORDER
set
NUMBER_ORDERED=?,
OPTLOCKID=?
where
ORDER_ID=?
and OPTLOCKID=?
Hibernate:
update
GPO_PRINT_ORDER
set
NUMBER_ORDERED=?,
OPTLOCKID=?
where
ORDER_ID=?
and OPTLOCKID=?
Hibernate:
update
GPO_PRINT_ORDER
set
NUMBER_ORDERED=?,
OPTLOCKID=?
where
ORDER_ID=?
and OPTLOCKID=?

beikov · August 3, 2021, 11:56am

Did you do a flush before calling refresh? If not, this will obviously not work. I don’t know what “post-save” means exactly, but depending on the transaction isolation level you are using, a dedicated transaction with repeatabe read or serializable isolation level, that is started before the transaction that does the flush of children, will not see the changes of the write transaction. In such a scenario, you would have to ensure the read transaction is started after the write transaction committed.

Gene.Williams · August 3, 2021, 4:52pm

Hi @beikov

I am relying on Spring 5 to handle transactions. My DAO method is simply:

@Override
@Transactional
public void save(List<GpoPrintBudget> budgets) {
	Session session = sessionFactory.getCurrentSession();
	for (int i = 0; i < budgets.size(); i++) {
		GpoPrintBudget budget = budgets.get(i);
		budget.setGpoPrintOrders(budget.getGpoPrintOrders());
		List<GpoPrintOrder> orders = budget.getGpoPrintOrders();
		for (GpoPrintOrder order : orders) {
			// reset the relationship to the parent budget
			order.setGpoPrintBudget(budget);
			session.saveOrUpdate(order);
		}
		// save
		session.saveOrUpdate(budget);
	}
}

I have tried calling session.refresh(budget) after session.saveOrUpdate(budget) but this does work, and I presume because the transaction is not yet committed to the database. Which makes sense based on what you described above.

By ‘post-save’ I am referring to a Spring controller method to save the data, executing the DAO method above though a service layer. After the controller request to save the data is finished the method calls another method to refresh the page. This is not refreshing the formulas on displayed on the page. However, the formulas data is refreshed from the database if the user initiates a refresh by submitting a new request.

Thanks for you replies.

beikov · August 3, 2021, 8:04pm

Just put a session.flush at the end of the method. After that, a refresh should work fine.

Gene.Williams · August 3, 2021, 9:20pm

Thanks @beikov

session.flush() did not work, but I will keep trying to resolve.

beikov · August 4, 2021, 6:42am

I will have to see how the code interacts i.e. the method from which you call the save method and then the refresh to be able to help you. The flushing will cause all insert/update/delete statements to be flushed to the database after which a refresh should do what you want.

Gene.Williams · August 5, 2021, 1:54am

@beikov

Thank you for all your advice. The controller methods and DAO methods are below.

Calling the controller list() method at the end of save() is where I think I’m stuck because Spring is not refreshing the data from the database. After some more reading its clear the result of my approach is both methods are within a single transaction.

The data is refreshed if the user clicks a ‘search’ button on the page and submits a new request.

I think I may need a redirect or forward but cannot figure out the proper syntax in Spring to pass a parameter invoked by clicking a submit button.

Spring Controller methods:

@RequestMapping(params = "save", method = RequestMethod.POST)
public ModelAndView save(@ModelAttribute("budgetForm") GpoPrintBudgetForm<Object> budgetForm,
		BindingResult result) {
	budgetForm.setUserInfo(userInfoBean);
	if (budgetForm.getPrintBudgets() != null) {
		budgetValidator.validate(budgetForm, result);
		if (!result.hasErrors()) {
			printBudgetsService.save(budgetForm.getPrintBudgets());
		} else {
			return new ModelAndView("printbudgets", "budgetForm", budgetForm);
		}
	} // is not refreshing from the database
    return list(budgetForm);
}

@RequestMapping(params = "search", method = RequestMethod.POST)
public ModelAndView list(@ModelAttribute("budgetForm") GpoPrintBudgetForm<Object> budgetForm) {
	if (budgetForm.getSearchInfo().getTitleId() != null) {
		// if the user is a Territory Manager
		// filter orders by their assigned territories
		budgetForm.setUserInfo(userInfoBean);
		if (userInfoBean != null && userInfoBean.getUserRole() != null
				&& userInfoBean.getUserRole().equalsIgnoreCase("TERR")) {
			budgetForm.setPrintBudgets(
					printBudgetsService.listByUserLogin(budgetForm.getSearchInfo(), userInfoBean.getUserLogin()));
		} else {
		budgetForm.setPrintBudgets(printBudgetsService.list(budgetForm.getSearchInfo()));
		}
	} else {
		budgetForm = new GpoPrintBudgetForm<Object>();
	}
	return new ModelAndView("printbudgets", "budgetForm", budgetForm);
}

DAO/Hibernate, called by methods in the Service layer

@Override
@Transactional
public void save(List<GpoPrintBudget> budgets) {
	Session session = sessionFactory.getCurrentSession();
	for (GpoPrintBudget budget: budgets) { 	
		budget.setGpoPrintOrders(budget.getGpoPrintOrders());
		List<GpoPrintOrder> orders = budget.getGpoPrintOrders();
		for (GpoPrintOrder order : orders) {
			// reset the relationship to the parent budget
			order.setGpoPrintBudget(budget);
			session.saveOrUpdate(order);
		}
		// save
		session.saveOrUpdate(budget);
	}
}

@Override
public List<GpoPrintBudget> list(PrintSearchInfoBean searchInfo) {
	Session session = sessionFactory.getCurrentSession();

	CriteriaBuilder builder = session.getCriteriaBuilder();
	CriteriaQuery<GpoPrintBudget> criteria = builder.createQuery(GpoPrintBudget.class);
	Root<GpoPrintBudget> budget = criteria.from(GpoPrintBudget.class);
	Join<GpoPrintBudget, GpoTitle> title = budget.join(GpoPrintBudget_.gpoTitle);
	Join<GpoPrintBudget, GpoTerritory> territory = budget.join(GpoPrintBudget_.gpoTerritory);
	Join<GpoTerritory, GpoRegion> region = territory.join(GpoTerritory_.gpoRegion);

	if (searchInfo != null) {
		List<Predicate> predicates = new ArrayList<Predicate>();
		// add title search criteria
		if (searchInfo.getTitleId() != null) {
			predicates.add(builder.equal(title.get(GpoTitle_.titleId), searchInfo.getTitleId()));
		}
		// add territory search criteria
		if (searchInfo.getTerritoryIds() != null && searchInfo.getTerritoryIds().size() > 0) {
			In<Integer> inClause = builder.in(territory.get(GpoTerritory_.territoryId));
			for (Integer territoryId : searchInfo.getTerritoryIds()) {
				inClause.value(territoryId);
			}
			predicates.add(inClause);
		}
		// add region search criteria
		else if (searchInfo.getRegionId() != null) {
			predicates.add(builder.equal(region.get(GpoRegion_.regionId), searchInfo.getRegionId()));
		}

		Predicate[] predArray = new Predicate[predicates.size()];
		predicates.toArray(predArray);
		criteria.where(predArray);
	}
	criteria.select(budget).orderBy(builder.asc(territory.get(GpoTerritory_.territoryName)));

	TypedQuery<GpoPrintBudget> query = session.createQuery(criteria);
	return query.getResultList();
}

@Override
public List<GpoPrintBudget> listByUserLogin(PrintSearchInfoBean searchInfo, String userLogin) {
	Session session = sessionFactory.getCurrentSession();

	CriteriaBuilder builder = session.getCriteriaBuilder();
	CriteriaQuery<GpoPrintBudget> criteria = builder.createQuery(GpoPrintBudget.class);
	Root<GpoPrintBudget> budget = criteria.from(GpoPrintBudget.class);
	Join<GpoPrintBudget, GpoTitle> title = budget.join(GpoPrintBudget_.gpoTitle);
	Join<GpoPrintBudget, GpoTerritory> territory = budget.join(GpoPrintBudget_.gpoTerritory);
	Join<GpoTerritory, GpoRegion> region = territory.join(GpoTerritory_.gpoRegion);
	SetJoin<GpoTerritory, GpoUserTerritory> userTerritory = territory.join(GpoTerritory_.gpoUserTerritories);
	Join<GpoUserTerritory, GpoUser> user = userTerritory.join(GpoUserTerritory_.gpoUser);

	List<Predicate> predicates = new ArrayList<Predicate>();
	// add user login
	predicates.add(builder.equal(user.get(GpoUser_.userLogin), userLogin));

	if (searchInfo != null) {
		// add title search criteria
		if (searchInfo.getTitleId() != null) {
			predicates.add(builder.equal(title.get(GpoTitle_.titleId), searchInfo.getTitleId()));
		}
		// add territory search criteria
		if (searchInfo.getTerritoryIds() != null && searchInfo.getTerritoryIds().size() > 0) {
			In<Integer> inClause = builder.in(territory.get(GpoTerritory_.territoryId));
			for (Integer territoryId : searchInfo.getTerritoryIds()) {
				inClause.value(territoryId);
			}
			predicates.add(inClause);
		}
		// add region search criteria
		else if (searchInfo.getRegionId() != null) {
			predicates.add(builder.equal(region.get(GpoRegion_.regionId), searchInfo.getRegionId()));
		}
	}

	Predicate[] predArray = new Predicate[predicates.size()];
	predicates.toArray(predArray);

	criteria.where(predArray);
	criteria.select(budget).orderBy(builder.asc(territory.get(GpoTerritory_.territoryName)));

	TypedQuery<GpoPrintBudget> query = session.createQuery(criteria);
	return query.getResultList();
}

beikov · August 5, 2021, 7:48am

Well, maybe the problem is that you are running a read-only transaction for the whole controller method.

Try the following. At the end of save, add session.flush() and session.clear().

Gene.Williams · August 5, 2021, 5:02pm

@beikov

Adding session.flush() and session.clear() worked. Thank you so much for your assistance!

Topic		Replies	Views
@Where annotation does no refresh after updating the child collection Hibernate ORM	5	1383	April 5, 2018
EntityManager.refresh not working? Hibernate ORM	5	2735	March 23, 2023
Hibernate not maintaining order of List of @OneToMany with @OrderColumn when list updated Hibernate ORM	4	4553	June 5, 2019
@OrderColumn broken order / null values Hibernate ORM	0	1099	September 9, 2019
@Formula with Sort Hibernate ORM	3	671	May 3, 2021

@Formula attributes on parent not refreshing on save of children

Related topics