tags:

views:

1503

answers:

1

NOTE: I have changed the details provided due to comments provided. The new technical details are actually code that I am trying to run currently.

I have a weird problem going on currently with one of my OneToMany / ManyToOne bidirectional relationships.

In the parent table I have a long which is the identifier and the child also has that column in addition to another field, which makes up its composite key.

In my sample data that I'm querying I have 1 parent record and 3 related records in the child table. Currently when I use the criteria object to query the table I get back 3 parent (CoPolicyPointer) records, 2 of which don't have the associated child (CoTransactionSummary) table linked and then a third that is populated properly. I would have expected to only get a single result back and that is why I am confused. Do you have any ideas why this is occuring?

I have provided more technical data below.

Parent Table (CoPolicyPointer)
Column            Data Type
SystemAssignId    String               (Primary Key)
PolicyPrefixCd    String
PolicyId          String
PolicySeqNo       int




Child Table (CoTransaction Summary)
Column            Data Type
SystemAssignId    String                  (composite key)
TransSeqId        int                     (composite key)
TransType  varchar(4)

The Criteria statement looks like the following:

Criteria criteria = session.createCriteria(classToUse);
criteria.createAlias("coTransactionSummaries", "ts");
criteria.add(Restrictions.ne("ts.transInProcessCd", "VIEW"));
criteria.add(Restrictions.gt("ts.transSeqId", Short.valueOf("0")));
criteria.add(Restrictions.eq(BaseCoPolicyPointer.COL_POLICY_PREFIX_CD,
   policyNumber.getPrefix().getLiteral()));
criteria.add(Restrictions.eq(BaseCoPolicyPointer.COL_POLICY_ID,
   policyNumber.getId()));

The Parent class looks like:

@Entity(name = "CoPolicyPointer")
public class CoPolicyPointerRedo {
private static final String CLASS_NAME = CoPolicyPointerRedo.class
  .getName();
private static final Logger logger = Logger.getLogger(CLASS_NAME);

private String systemAssignId;
private String policyPrefixCd;
private String policyId;
private String policyNumber;
private Short policySeqNo;
private Set coTransactionSummaries;

@Id
public String getSystemAssignId() {
 return systemAssignId;
}

public void setSystemAssignId(String systemAssignId) {
 this.systemAssignId = systemAssignId;
}

@Column
public String getPolicyId() {
 return policyId;
}

public void setPolicyId(String policyId) {
 this.policyId = policyId;
}

@Column
public String getPolicyPrefixCd() {
 return policyPrefixCd;
}

public void setPolicyPrefixCd(String policyPrefixCd) {
 this.policyPrefixCd = policyPrefixCd;
}

@Column
public Short getPolicySeqNo() {
 return policySeqNo;
}

public void setPolicySeqNo(Short policySeqNo) {
 this.policySeqNo = policySeqNo;
}


@OneToMany(targetEntity = CoTransactionSummaryRedo.class, mappedBy = "coPolicyPointer")
public Set getCoTransactionSummaries() {
 return coTransactionSummaries;
}

public void setCoTransactionSummaries(Set coTransactionSummaries) {
 this.coTransactionSummaries = coTransactionSummaries;
}

}

The child class looks like:

@Entity(name = "CoTransactionSummary")
public class CoTransactionSummaryRedo {
private static final String CLASS_NAME = CoTransactionSummaryRedo.class
  .getName();
private static final Logger logger = Logger.getLogger(CLASS_NAME);

private String transTypeCd;
private String transInProcessCd;
private CoPolicyPointerRedo coPolicyPointer;

private CoTransactionSummaryIdRedo id;

@EmbeddedId
public CoTransactionSummaryIdRedo getId() {
 return id;
}

public void setId(CoTransactionSummaryIdRedo id) {
 this.id = id;
}

@Column
public String getTransInProcessCd() {
 return transInProcessCd;
}

public void setTransInProcessCd(String transInProcessCd) {
 this.transInProcessCd = transInProcessCd;
}

@Column
public String getTransTypeCd() {
 return transTypeCd;
}

public void setTransTypeCd(String transTypeCd) {
 this.transTypeCd = transTypeCd;
}

@ManyToOne()
@JoinColumn(name = "systemAssignId", insertable = false, updatable = false)
public CoPolicyPointerRedo getCoPolicyPointer() {
 return coPolicyPointer;
}

public void setCoPolicyPointer(CoPolicyPointerRedo coPolicyPointer) {
 this.coPolicyPointer = coPolicyPointer;
}


}

The child class's ID class looks like:

@Embeddable
public class CoTransactionSummaryIdRedo implements java.io.Serializable {

private String systemAssignId;
private short transSeqId;

public CoTransactionSummaryIdRedo() {
}

public CoTransactionSummaryIdRedo(String systemAssignId, short transSeqId) {
 this.systemAssignId = systemAssignId;
 this.transSeqId = transSeqId;
}

@Column
public String getSystemAssignId() {
 return systemAssignId;
}

public void setSystemAssignId(String systemAssignId) {
 this.systemAssignId = systemAssignId;
}

@Column
public short getTransSeqId() {
 return transSeqId;
}

public void setTransSeqId(short transSeqId) {
 this.transSeqId = transSeqId;
}

@Override
public boolean equals(Object other) {
 if ((this == other))
  return true;
 if ((other == null))
  return false;
 if (!(other instanceof CoTransactionSummaryIdRedo))
  return false;
 CoTransactionSummaryIdRedo castOther = (CoTransactionSummaryIdRedo) other;

 return ((this.getSystemAssignId() == castOther.getSystemAssignId()) || (this
   .getSystemAssignId() != null
   && castOther.getSystemAssignId() != null && this
   .getSystemAssignId().equals(castOther.getSystemAssignId())))
   && (this.getTransSeqId() == castOther.getTransSeqId());
}

@Override
public int hashCode() {
 int result = 17;

 result = 37
   * result
   + (getSystemAssignId() == null ? 0 : this.getSystemAssignId()
     .hashCode());
 result = 37 * result + this.getTransSeqId();
 return result;
}

}

I've got logging turned on TRACE and here is what is being generated.

08:20:47,907 DEBUG SQL:111 - select this_.systemAssignId as systemAs1_1_2_, this_.policyId as policyId1_2_, this_.policyPrefixCd as policyPr3_1_2_, this_.policySeqNo as policySe4_1_2_, ts1_.systemAssignId as systemAs1_0_0_, ts1_.transSeqId as transSeqId0_0_, ts1_.transInProcessCd as transInP3_0_0_, ts1_.transTypeCd as transTyp4_0_0_, copolicypo4_.systemAssignId as systemAs1_1_1_, copolicypo4_.policyId as policyId1_1_, copolicypo4_.policyPrefixCd as policyPr3_1_1_, copolicypo4_.policySeqNo as policySe4_1_1_ from CoPolicyPointer this_ inner join CoTransactionSummary ts1_ on this_.systemAssignId=ts1_.systemAssignId left outer join CoPolicyPointer copolicypo4_ on ts1_.systemAssignId=copolicypo4_.systemAssignId where this_.policyPrefixCd=? and this_.policyId=? and this_.policySeqNo=?
Hibernate: select this_.systemAssignId as systemAs1_1_2_, this_.policyId as policyId1_2_, this_.policyPrefixCd as policyPr3_1_2_, this_.policySeqNo as policySe4_1_2_, ts1_.systemAssignId as systemAs1_0_0_, ts1_.transSeqId as transSeqId0_0_, ts1_.transInProcessCd as transInP3_0_0_, ts1_.transTypeCd as transTyp4_0_0_, copolicypo4_.systemAssignId as systemAs1_1_1_, copolicypo4_.policyId as policyId1_1_, copolicypo4_.policyPrefixCd as policyPr3_1_1_, copolicypo4_.policySeqNo as policySe4_1_1_ from CoPolicyPointer this_ inner join CoTransactionSummary ts1_ on this_.systemAssignId=ts1_.systemAssignId left outer join CoPolicyPointer copolicypo4_ on ts1_.systemAssignId=copolicypo4_.systemAssignId where this_.policyPrefixCd=? and this_.policyId=? and this_.policySeqNo=?
19:14:30,938 DEBUG AbstractBatcher:513 - preparing statement
19:14:30,969 DEBUG StringType:151 - binding 'WA' to parameter: 1
19:14:30,969 DEBUG StringType:151 - binding '0100036449' to parameter: 2
19:14:30,969 DEBUG ShortType:151 - binding '0' to parameter: 3
19:14:30,969 DEBUG AbstractBatcher:426 - about to open ResultSet (open ResultSets: 0, globally: 0)
19:14:30,969 DEBUG Loader:717 - processing result set
19:14:30,969 DEBUG Loader:722 - result set row: 0
19:14:30,969 DEBUG StringType:193 - returning '00502000000000' as column: systemAs1_0_0_
19:14:30,969 DEBUG ShortType:193 - returning '0' as column: transSeqId0_0_
19:14:30,969 DEBUG StringType:193 - returning '00502000000000' as column: systemAs1_1_1_
19:14:30,969 DEBUG StringType:193 - returning '00502000000000' as column: systemAs1_1_2_
19:14:30,969 DEBUG Loader:1197 - result row: EntityKey[CoTransactionSummaryRedo#component[systemAssignId,transSeqId]{transSeqId=0, systemAssignId=00502000000000}], EntityKey[CoPolicyPointerRedo#00502000000000], EntityKey[CoPolicyPointerRedo#00502000000000]
19:14:30,969 DEBUG Loader:1379 - Initializing object from ResultSet: [CoTransactionSummaryRedo#component[systemAssignId,transSeqId]{transSeqId=0, systemAssignId=00502000000000}]
19:14:30,969 DEBUG AbstractEntityPersister:2059 - Hydrating entity: [CoTransactionSummaryRedo#component[systemAssignId,transSeqId]{transSeqId=0, systemAssignId=00502000000000}]
19:14:30,969 DEBUG StringType:193 - returning '00502000000000' as column: systemAs1_0_0_
19:14:30,969 DEBUG StringType:193 - returning 'PCNM' as column: transInP3_0_0_
19:14:30,969 DEBUG StringType:193 - returning 'PCNM' as column: transTyp4_0_0_
19:14:30,969 DEBUG Loader:1379 - Initializing object from ResultSet: [CoPolicyPointerRedo#00502000000000]
19:14:30,969 DEBUG AbstractEntityPersister:2059 - Hydrating entity: [CoPolicyPointerRedo#00502000000000]
19:14:30,985 DEBUG StringType:193 - returning '0100036449          ' as column: policyId1_1_
19:14:30,985 DEBUG StringType:193 - returning 'WA    ' as column: policyPr3_1_1_
19:14:30,985 DEBUG ShortType:193 - returning '0' as column: policySe4_1_1_
19:14:30,985 DEBUG Loader:722 - result set row: 1
19:14:30,985 DEBUG StringType:193 - returning '00502000000000' as column: systemAs1_0_0_
19:14:30,985 DEBUG ShortType:193 - returning '1' as column: transSeqId0_0_
19:14:30,985 DEBUG StringType:193 - returning '00502000000000' as column: systemAs1_1_1_
19:14:30,985 DEBUG StringType:193 - returning '00502000000000' as column: systemAs1_1_2_
19:14:30,985 DEBUG Loader:1197 - result row: EntityKey[CoTransactionSummaryRedo#component[systemAssignId,transSeqId]{transSeqId=1, systemAssignId=00502000000000}], EntityKey[CoPolicyPointerRedo#00502000000000], EntityKey[CoPolicyPointerRedo#00502000000000]
19:14:30,985 DEBUG Loader:1379 - Initializing object from ResultSet: [CoTransactionSummaryRedo#component[systemAssignId,transSeqId]{transSeqId=1, systemAssignId=00502000000000}]
19:14:30,985 DEBUG AbstractEntityPersister:2059 - Hydrating entity: [CoTransactionSummaryRedo#component[systemAssignId,transSeqId]{transSeqId=1, systemAssignId=00502000000000}]
19:14:30,985 DEBUG StringType:193 - returning '00502000000000' as column: systemAs1_0_0_
19:14:30,985 DEBUG StringType:193 - returning 'PCNM' as column: transInP3_0_0_
19:14:30,985 DEBUG StringType:193 - returning 'PCNM' as column: transTyp4_0_0_
19:14:30,985 DEBUG Loader:722 - result set row: 2
19:14:30,985 DEBUG StringType:193 - returning '00502000000000' as column: systemAs1_0_0_
19:14:30,985 DEBUG ShortType:193 - returning '5' as column: transSeqId0_0_
19:14:30,985 DEBUG StringType:193 - returning '00502000000000' as column: systemAs1_1_1_
19:14:30,985 DEBUG StringType:193 - returning '00502000000000' as column: systemAs1_1_2_
19:14:30,985 DEBUG Loader:1197 - result row: EntityKey[CoTransactionSummaryRedo#component[systemAssignId,transSeqId]{transSeqId=5, systemAssignId=00502000000000}], EntityKey[CoPolicyPointerRedo#00502000000000], EntityKey[CoPolicyPointerRedo#00502000000000]
19:14:30,985 DEBUG Loader:1379 - Initializing object from ResultSet: [CoTransactionSummaryRedo#component[systemAssignId,transSeqId]{transSeqId=5, systemAssignId=00502000000000}]
19:14:30,985 DEBUG AbstractEntityPersister:2059 - Hydrating entity: [CoTransactionSummaryRedo#component[systemAssignId,transSeqId]{transSeqId=5, systemAssignId=00502000000000}]
19:14:30,985 DEBUG StringType:193 - returning '00502000000000' as column: systemAs1_0_0_
19:14:30,985 DEBUG StringType:193 - returning 'PCNM' as column: transInP3_0_0_
19:14:30,985 DEBUG StringType:193 - returning 'PCNM' as column: transTyp4_0_0_
19:14:30,985 DEBUG Loader:722 - result set row: 3
19:14:30,985 DEBUG StringType:193 - returning '00502000000000' as column: systemAs1_0_0_
19:14:30,985 DEBUG ShortType:193 - returning '6' as column: transSeqId0_0_
19:14:30,985 DEBUG StringType:193 - returning '00502000000000' as column: systemAs1_1_1_
19:14:30,985 DEBUG StringType:193 - returning '00502000000000' as column: systemAs1_1_2_
19:14:30,985 DEBUG Loader:1197 - result row: EntityKey[CoTransactionSummaryRedo#component[systemAssignId,transSeqId]{transSeqId=6, systemAssignId=00502000000000}], EntityKey[CoPolicyPointerRedo#00502000000000], EntityKey[CoPolicyPointerRedo#00502000000000]
19:14:30,985 DEBUG Loader:1379 - Initializing object from ResultSet: [CoTransactionSummaryRedo#component[systemAssignId,transSeqId]{transSeqId=6, systemAssignId=00502000000000}]
19:14:30,985 DEBUG AbstractEntityPersister:2059 - Hydrating entity: [CoTransactionSummaryRedo#component[systemAssignId,transSeqId]{transSeqId=6, systemAssignId=00502000000000}]
19:14:30,985 DEBUG StringType:193 - returning '00502000000000' as column: systemAs1_0_0_
19:14:30,985 DEBUG StringType:193 - returning 'PCNM' as column: transInP3_0_0_
19:14:30,985 DEBUG StringType:193 - returning 'PCNM' as column: transTyp4_0_0_
19:14:30,985 DEBUG Loader:744 - done processing result set (4 rows)
19:14:30,985 DEBUG AbstractBatcher:433 - about to close ResultSet (open ResultSets: 1, globally: 1)
19:14:30,985 DEBUG AbstractBatcher:418 - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
19:14:30,985 DEBUG AbstractBatcher:562 - closing statement
19:14:30,985 DEBUG ConnectionManager:427 - aggressively releasing JDBC connection
19:14:30,985 DEBUG ConnectionManager:464 - releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
19:14:30,985 DEBUG DriverManagerConnectionProvider:152 - returning connection to pool, pool size: 1
19:14:30,985 DEBUG Loader:874 - total objects hydrated: 5
19:14:30,985 DEBUG TwoPhaseLoad:130 - resolving associations for [CoTransactionSummaryRedo#component[systemAssignId,transSeqId]{transSeqId=0, systemAssignId=00502000000000}]
19:14:30,985 DEBUG DefaultLoadEventListener:199 - loading entity: [CoPolicyPointerRedo#00502000000000]
19:14:30,985 DEBUG DefaultLoadEventListener:372 - attempting to resolve: [CoPolicyPointerRedo#00502000000000]
19:14:30,985 DEBUG DefaultLoadEventListener:389 - resolved object in session cache: [CoPolicyPointerRedo#00502000000000]
19:14:30,985 DEBUG TwoPhaseLoad:226 - done materializing entity [CoTransactionSummaryRedo#component[systemAssignId,transSeqId]{transSeqId=0, systemAssignId=00502000000000}]
19:14:30,985 DEBUG TwoPhaseLoad:130 - resolving associations for [CoPolicyPointerRedo#00502000000000]
19:14:30,985 DEBUG LoadContexts:218 - creating collection wrapper:[CoPolicyPointerRedo.coTransactionSummaries#00502000000000]
19:14:31,047 DEBUG TwoPhaseLoad:226 - done materializing entity [CoPolicyPointerRedo#00502000000000]
19:14:31,047 DEBUG TwoPhaseLoad:130 - resolving associations for [CoTransactionSummaryRedo#component[systemAssignId,transSeqId]{transSeqId=1, systemAssignId=00502000000000}]
19:14:31,047 DEBUG DefaultLoadEventListener:199 - loading entity: [CoPolicyPointerRedo#00502000000000]
19:14:31,047 DEBUG DefaultLoadEventListener:372 - attempting to resolve: [CoPolicyPointerRedo#00502000000000]
19:14:31,047 DEBUG DefaultLoadEventListener:389 - resolved object in session cache: [CoPolicyPointerRedo#00502000000000]
19:14:31,047 DEBUG TwoPhaseLoad:226 - done materializing entity [CoTransactionSummaryRedo#component[systemAssignId,transSeqId]{transSeqId=1, systemAssignId=00502000000000}]
19:14:31,047 DEBUG TwoPhaseLoad:130 - resolving associations for [CoTransactionSummaryRedo#component[systemAssignId,transSeqId]{transSeqId=5, systemAssignId=00502000000000}]
19:14:31,047 DEBUG DefaultLoadEventListener:199 - loading entity: [CoPolicyPointerRedo#00502000000000]
19:14:31,047 DEBUG DefaultLoadEventListener:372 - attempting to resolve: [CoPolicyPointerRedo#00502000000000]
19:14:31,047 DEBUG DefaultLoadEventListener:389 - resolved object in session cache: [CoPolicyPointerRedo#00502000000000]
19:14:31,047 DEBUG TwoPhaseLoad:226 - done materializing entity [CoTransactionSummaryRedo#component[systemAssignId,transSeqId]{transSeqId=5, systemAssignId=00502000000000}]
19:14:31,047 DEBUG TwoPhaseLoad:130 - resolving associations for [CoTransactionSummaryRedo#component[systemAssignId,transSeqId]{transSeqId=6, systemAssignId=00502000000000}]
19:14:31,047 DEBUG DefaultLoadEventListener:199 - loading entity: [CoPolicyPointerRedo#00502000000000]
19:14:31,047 DEBUG DefaultLoadEventListener:372 - attempting to resolve: [CoPolicyPointerRedo#00502000000000]
19:14:31,047 DEBUG DefaultLoadEventListener:389 - resolved object in session cache: [CoPolicyPointerRedo#00502000000000]
19:14:31,047 DEBUG TwoPhaseLoad:226 - done materializing entity [CoTransactionSummaryRedo#component[systemAssignId,transSeqId]{transSeqId=6, systemAssignId=00502000000000}]
19:14:31,047 DEBUG StatefulPersistenceContext:860 - initializing non-lazy collections
19:14:31,047 DEBUG JDBCContext:260 - after autocommit
19:14:31,047 DEBUG ConnectionManager:427 - aggressively releasing JDBC connection
19:14:31,047 DEBUG SessionImpl:449 - after transaction completion
A: 

The mapping you've posted and the description you provided conflict with each other:

  1. Mapping join column on inverse association as non-insertable/updatable is not going to work. How is that column going to be updated (and your parent-child association maintained)?
  2. Your child mapping defines Child's PK as childId (using @Id annotation). That's NOT how a composite key is mapped; Hibernate will consider childId to be a simple primary key.
  3. Your criteria doesn't match your mapping (ParentTable vs Parent, etc...) which is probably a conversion artifact, but other then that looks OK.

I'd use aliases, though:

Criteria criteria = session.createCriteria(Parent.class)
 .add(Restrictions.eq("policyNo", "1234"))
 .createAlias("child", "children")
 .add(Restrictions.ne("children.transType", "VIEW"));

Update (based on criteria / generated SQL):

Take a look at the SQL generated by Hibernate based on your criteria (shortened for readability):

SELECT this_.*, ts1_.*, copolicypo4_.*
  FROM CoPolicyPointer this_
 INNER JOIN CoTransactionSummary ts1_ ON this_.systemAssignId=ts1_.systemAssignId
  LEFT JOIN CoPolicyPointer copolicypo4_ ON ts1_.systemAssignId=copolicypo4_.systemAssignId

You'll notice that your "parent" table (CoPolicyPointer) is joined twice even though your criteria only joins the "children" table. That happens because you're specifying entity names in @Entity annotations but creating your criteria using original class name, making Hibernate think you're joining three separate entities:

  1. Criteria is based on CoPolicyPointerRedo.class
  2. It joins to CoTransactionSummaryRedo.class whose entity name is CoTransactionSummary
  3. Which joins back to CoPolicyPointerRedo.class as `CoPolicyPointer using @ManyToOne

You need to be consistent with naming. Either remove name from @Entity declarations (you can use @Table instead to specify table name) or create your criteria using session.createCriteria(String entityName) method which takes entity name rather than class name as parameter.

ChssPly76
I have removed the insertable=false and updatable=false from the annotation as suggested. In regards to your second point, you are correct that the @ID annotation was incorrect and my code actually has @EmbeddedId. I had to create a seperate example because I'm not supposed to share the companies code on the internet and didn't realize I used the wrong annotation. Sorry for the confusion.I changed the criteria code to what you have above and I still get back 3 of the Parent objects instead of just 1 with the child records attached.Any other ideas?
jwmajors81
I sympathize with "not sharing the code" but I don't think posting just the relevant fragments (and changing entity names like you did) would be objectionable - otherwise it's hard to suggest the solution without seeing the exact problem. @EmbeddedId you've mentioned should be (is?) applied to another (component) class. How is the cihld's part of that composite id assigned? Did you enable SQL output in Hibernate? If so, what query is generated? Does it look reasonable based on your data or do you see something obviously wrong?
ChssPly76
I have tried what you suggested, but I still get an extra left outer join tacked onto the end of the SQL. I was able to get a distinct result back by adding the following to the criteria statement: criteria .setResultTransformer(DistinctRootEntityResultTransformer.INSTANCE);However, that is less than idea because all of the records are returned from the database and hibernate has to determine which record to use, so I would still like to find another option.
jwmajors81
Left outer join is there because you're querying children - how else would you do it? However, "parent" instances you get back should all be the same in that case, so applying transformer to results is the proper way to do this.
ChssPly76
Within the query hibernate first does an inner join between CoPolicyPointer and CoTransactionSummary and then joins again between the same two tables doing a left outer join. Wouldn't the second query be redundant? Also, why would it return multiple parent entities, a couple that don't have any children while the third does have the expected child records. I know that there is only one record in the parent table that matches the criteria and then 3 CoTransactionSummary records. I would expect hibernate to return a single parent entity with 3 child entities.
jwmajors81
So this happens _after_ you've removed the entity names from your annotations? There should be no 2nd join and the 1st join should be outer.
ChssPly76