ok - let's start again then:
Tables:
Code:
CREATE TABLE `primary_table` (
`primary_pk` int(11) NOT NULL auto_increment,
`PRIMARY_ID` int(11) default NULL,
PRIMARY KEY (`primary_pk`),
UNIQUE KEY `Index_2` USING BTREE (`PRIMARY_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
insert into `primary_table`(`primary_pk`,`PRIMARY_ID`) values (1,255);
CREATE TABLE `secondary_table` (
`ID` int(11) NOT NULL auto_increment,
`STATUS` varchar(255) default NULL,
`primary_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `Index_2` (`primary_id`,`STATUS`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
insert into `secondary_table`(`ID`,`STATUS`,`primary_id`) values (1,'A',255);
insert into `secondary_table`(`ID`,`STATUS`,`primary_id`) values (2,'C',255);
insert into `secondary_table`(`ID`,`STATUS`,`primary_id`) values (3,'I',255);
insert into `secondary_table`(`ID`,`STATUS`,`primary_id`) values (4,'P',255);
Mappings:
Code:
@Entity
@Table( name = "primary_table", catalog = "premierdb", uniqueConstraints = @UniqueConstraint( columnNames = "PRIMARY_ID" ) )
public class PrimaryTable implements java.io.Serializable
{
// Fields
private Integer primaryPk;
private Integer primaryId;
private Set<SecondaryTable> secondaries;
// Constructors
/** default constructor */
public PrimaryTable()
{
}
/** full constructor */
public PrimaryTable( Integer primaryId )
{
this.primaryId = primaryId;
}
// Property accessors
@Id
@GeneratedValue( strategy = IDENTITY )
@Column( name = "primary_pk", unique = true, nullable = false )
public Integer getPrimaryPk()
{
return this.primaryPk;
}
public void setPrimaryPk( Integer primaryPk )
{
this.primaryPk = primaryPk;
}
@Column( name = "PRIMARY_ID", unique = true )
public Integer getPrimaryId()
{
return this.primaryId;
}
public void setPrimaryId( Integer primaryId )
{
this.primaryId = primaryId;
}
@OneToMany( cascade = CascadeType.ALL, mappedBy = "primary" )
public Set<SecondaryTable> getSecondaries()
{
return secondaries;
}
public void setSecondaries( Set<SecondaryTable> secondaries )
{
this.secondaries = secondaries;
}
}
@Entity
@Table( name = "secondary_table", catalog = "premierdb", uniqueConstraints = @UniqueConstraint( columnNames = {
"primary_id",
"STATUS" } ) )
public class SecondaryTable implements java.io.Serializable
{
// Fields
private Integer id;
private String status;
private Integer primaryId;
private PrimaryTable primary;
// Constructors
/** default constructor */
public SecondaryTable()
{
}
/** minimal constructor */
public SecondaryTable( Integer primaryId )
{
this.primaryId = primaryId;
}
/** full constructor */
public SecondaryTable( String status, Integer primaryId )
{
this.status = status;
this.primaryId = primaryId;
}
// Property accessors
@Id
@GeneratedValue( strategy = IDENTITY )
@Column( name = "ID", unique = true, nullable = false )
public Integer getId()
{
return this.id;
}
public void setId( Integer id )
{
this.id = id;
}
@Column( name = "STATUS" )
public String getStatus()
{
return this.status;
}
public void setStatus( String status )
{
this.status = status;
}
@Column( name = "primary_id", nullable = false )
public Integer getPrimaryId()
{
return this.primaryId;
}
public void setPrimaryId( Integer primaryId )
{
this.primaryId = primaryId;
}
@ManyToOne( fetch = FetchType.LAZY )
@JoinColumn( name = "PRIMARY_ID", referencedColumnName = "PRIMARY_ID", nullable = false, insertable = false, updatable = false )
public PrimaryTable getPrimary()
{
return primary;
}
public void setPrimary( PrimaryTable primary )
{
this.primary = primary;
}
}
Junit test:
Code:
@Test
public void test1()
{
Criteria criteria =
ProviderEntityManager.getEntityManager().getSession().createCriteria( PrimaryTable.class );
criteria.add( Restrictions.eq( "primaryId", 255 ) );
criteria.createCriteria( "secondaries", "secondary", CriteriaSpecification.LEFT_JOIN );
criteria.add( Restrictions.eq( "secondary.status", "A" ) );
List<PrimaryTable> pwns = criteria.list();
System.out.println( "Found Primaries: " + pwns.size() );
for( PrimaryTable p : pwns )
{
for( SecondaryTable s : p.getSecondaries() )
{
System.out.println( "\tStatus: " + s.getStatus() );
}
}
}
This JUnit ends up giving 2 SQL's...
Join query
Quote:
select this_.primary_pk as primary1_93_1_, this_.PRIMARY_ID as PRIMARY2_93_1_, secondary1_.PRIMARY_ID as PRIMARY2_3_, secondary1_.ID as ID3_, secondary1_.ID as ID94_0_, secondary1_.PRIMARY_ID as PRIMARY2_94_0_, secondary1_.primary_id as primary2_94_0_, secondary1_.STATUS as STATUS94_0_ from premierdb.primary_table this_ left outer join premierdb.secondary_table secondary1_ on this_.PRIMARY_ID=secondary1_.PRIMARY_ID where this_.PRIMARY_ID=? and secondary1_.STATUS=?
and ..
query based off of SecondaryTable object creation creating another query to get the PrimaryTable object - this additional query makes ZERO sense considering the object was already gotten from the first query!
Quote:
select primarytab0_.primary_pk as primary1_93_0_, primarytab0_.PRIMARY_ID as PRIMARY2_93_0_ from premierdb.primary_table primarytab0_ where primarytab0_.PRIMARY_ID=?
output is:
Quote:
Found Primaries: 1
Status: A
Now, to add another twist: Changing SeconaryTable's getPrimary() method to to be annotated as:
Code:
@LazyCollection( LazyCollectionOption.TRUE )
@ManyToOne
@JoinColumn( name = "PRIMARY_ID", referencedColumnName = "PRIMARY_ID", nullable = false, insertable = false, updatable = false )
public PrimaryTable getPrimary()
ONLY generates ONE query, and the output is:
Quote:
Found Primaries: 1
Status: A
Login
FAQ
Search
