Master child details update using JPA

[Suhas Mohamedali]

I have two tables

master_table : 'BenefitFileHdr ' and child_table: BenefitFileDtl

PK of master table is FK of child table.

One column start_date is getting updated from the GUI and needs to to be updated in DB.

Please check the code below.

Query query;

			StringBuffer headerUpdateQry = new StringBuffer(
					"UPDATE BenefitFileHdr hdr SET hdr.status =?1");
			if (aggLimit != null)
				headerUpdateQry.append(" , hdr.aggLimit =?2");
			if (effDate != null && effDate.trim().length() != 0)
				headerUpdateQry.append(" , hdr.id.startDate =?3");

			headerUpdateQry.append(" , hdr.updatedBy =?4");

			headerUpdateQry
					.append(" WHERE hdr.id.policyShortNo =?5 AND hdr.id.categoryCode =?6 AND hdr.id.startDate =?7");

			query = entityManager.createQuery(headerUpdateQry.toString());
			if (status) {
				query.setParameter(1, "Y");
			} else {
				query.setParameter(1, "N");
			}
			if (aggLimit != null)
				query.setParameter(2, new BigDecimal(aggLimit));
			
			if (currentEffDt != null)
				query.setParameter(3, currentEffDt);
			
			query.setParameter(4, loggedInUser);
			query.setParameter(5, policy);
			query.setParameter(6, categoryCode);
			query.setParameter(7, effTempDt);
			
		

		if (detailsList != null && !detailsList.isEmpty()) {
			for (Iterator itr = detailsList.iterator(); itr.hasNext();) {
				DetailsDTO detailsDTO = (DetailsDTO) itr.next();

				String bktCvrg = null;
				if (detailsDTO.getBcktCvrg()) {
					bktCvrg = "Y";
				} else {
					bktCvrg = "N";
				}

				Query detailsUpdateQry;

				StringBuffer upateQry = new StringBuffer(
						"UPDATE BenefitFileDtl dtls SET");

				upateQry.append(" dtls.bucketCoverage =?1");
				// if (detailsDTO.getBenSubLimit() != null)
				upateQry.append(", dtls.sublimit =?2");
				// if (detailsDTO.getCopayFlat() != null)
				upateQry.append(", dtls.copayFlat =?3");
				// if (detailsDTO.getCopayPerc() != null)
				upateQry.append(", dtls.copayPerc =?4");
				// if (detailsDTO.getCopayMax() != null)
				upateQry.append(", dtls.copayMax =?5");
				if ("MWP".equals(detailsDTO.getBcktCd())
						&& detailsDTO.getBcktCvrg()) {
					upateQry.append(", dtls.waitingPeriodEnd =?6");
				}
				if ("MWP".equals(detailsDTO.getBcktCd())
						&& !detailsDTO.getBcktCvrg()
						&& detailsDTO.getWtngPrd() != null) {
					upateQry.append(", dtls.waitingPeriodEnd =?7");
				}
				
				upateQry.append(", dtls.id.startDate =?8");
				
				upateQry.append(" WHERE dtls.id.policyShortNo =?9 AND dtls.id.categoryCode =?10 AND dtls.id.bucketCode =?11 AND dtls.id.startDate =?12");

				detailsUpdateQry = entityManager.createQuery(upateQry
						.toString());

				detailsUpdateQry.setParameter(1, bktCvrg);
				if (detailsDTO.getBenSubLimit() != null)
					detailsUpdateQry.setParameter(2,
							new BigDecimal(detailsDTO.getBenSubLimit()));
				else
					detailsUpdateQry.setParameter(2, null);

				if (detailsDTO.getCopayFlat() != null)
					detailsUpdateQry.setParameter(3,
							new BigDecimal(detailsDTO.getCopayFlat()));
				else
					detailsUpdateQry.setParameter(3, null);

				if (detailsDTO.getCopayPerc() != null)
					detailsUpdateQry.setParameter(4,
							new BigDecimal(detailsDTO.getCopayPerc()));
				else
					detailsUpdateQry.setParameter(4, null);
				if (detailsDTO.getCopayMax() != null)
					detailsUpdateQry.setParameter(5,
							new BigDecimal(detailsDTO.getCopayMax()));
				else
					detailsUpdateQry.setParameter(5, null);
				if ("MWP".equals(detailsDTO.getBcktCd())
						&& detailsDTO.getBcktCvrg()) {

					/*Calendar cal = Calendar.getInstance();
					cal.setTime(Util.stringToDate(detailsDTO.getWtngPrd();));
					cal.add(Calendar.DATE, +280);*/
					
					detailsDTO.getWtngPrd();
					
					log.info("\n The waiting period :::"
							+detailsDTO.getWtngPrd());
					detailsUpdateQry.setParameter(6, detailsDTO.getWtngPrd());
				}

				if ("MWP".equals(detailsDTO.getBcktCd())
						&& !detailsDTO.getBcktCvrg()
						&& detailsDTO.getWtngPrd() != null) {
					detailsUpdateQry.setParameter(7, null);
				}
				
				detailsUpdateQry.setParameter(8, currentEffDt);

				detailsUpdateQry
						.setParameter(9, detailsDTO.getPolicyShortNum());
				detailsUpdateQry.setParameter(10, detailsDTO.getCategory());
				detailsUpdateQry.setParameter(11, detailsDTO.getBcktCd());
				
				detailsUpdateQry.setParameter(12, effTempDt);
				
				detailsUpdateQry.executeUpdate();
				
				
			}
		}
		
		query.executeUpdate();
	//	}
		entityManager.flush();

when try to save the code i get following exception.

Caused by: java.sql.SQLException: ORA-02291: integrity constraint (SLMTEST.FK_BENEFIT_HDR_DTL) violated - parent key not found

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:365)
at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:75)

I tried to save the parent first. Still i got error mentioning children are there.

Mapping has been done as

	@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "benefitFileHdr")
	public Set<BenefitFileDtl> getBenefitFileDtls() {
		return this.benefitFileDtls;
	}

Please suggest a solution for update

[Akhilesh Trivedi]

I googled and found this page. Check if this applies is in your case.