|
I've got two tables, one with three keys (city_code, item_code, language_code) and the second with two keys (city_code, item_code). The second table is sparse (i.e., it does not have all the city/item codes that are in table one). What is the best way to do an outer join between table one to table two (as it is ok that there is not a match in table two)?
I've tried many approaches and finally got hibernate to generate an outer join (which looks fine). However, the hibernate logic then goes into TwoPhaseLoad and does a select on table two for each row it found in table one. If the code below is the correct way to do the join, how do I stop TwoPhaseLoad from doing the extra selects?
Thanks! Kevin
Hibernate version: 3.2.0.CR2
==========================================
This is the code for table one (OctopusHotels):
@Entity
@Table(name = "octopus_hotels")
@TypeDefs({ @TypeDef(
name = "caster",
typeClass = CasterStringType.class
) })
public class OctopusHotels implements java.io.Serializable {
//~ Instance fields --------------------------------------------------------------------------------------
private OctopusEzrezHotelData _octopusEzrezHotelData;
private String address;
private String hotelName;
private OctopusHotelsId id;
private Date lastModified;
private Integer rating;
private String xml;
//~ Constructors -----------------------------------------------------------------------------------------
/** default constructor */
public OctopusHotels() {
}
/** minimal constructor */
public OctopusHotels(OctopusHotelsId id) {
this.id = id;
}
/** full constructor */
public OctopusHotels(OctopusHotelsId id, String xml, String address, String hotelName, Date lastModified,
Integer rating) {
this.id = id;
this.xml = xml;
this.address = address;
this.hotelName = hotelName;
this.lastModified = lastModified;
this.rating = rating;
}
//~ Methods ----------------------------------------------------------------------------------------------
public static OctopusHotels findById(HibernateEntityManager em, OctopusHotelsId id)
throws EzRezException
{
try {
OctopusHotels instance = (OctopusHotels) em.getSession().get(
"com.ezrez.crs.gulliver.hibernate2.OctopusHotels", id);
return instance;
} catch (Exception re) {
throw EzRezException.fromException(re);
}
}
// Property accessors
@AttributeOverrides(
{
@AttributeOverride(
name = "cityCode",
column =
@Column(
name = "city_code",
unique = false,
nullable = false,
insertable = true,
updatable = true
)
),
@AttributeOverride(
name = "itemCode",
column =
@Column(
name = "item_code",
unique = false,
nullable = false,
insertable = true,
updatable = true
)
),
@AttributeOverride(
name = "languageCode",
column =
@Column(
name = "language_code",
unique = false,
nullable = false,
insertable = true,
updatable = true,
length = 2
)
)
}
)
@EmbeddedId public OctopusHotelsId getId() {
return this.id;
}
public void setId(OctopusHotelsId id) {
this.id = id;
}
@Column(
name = "xml",
unique = false,
nullable = true,
insertable = true,
updatable = true
)
public String getXml() {
return this.xml;
}
public void setXml(String xml) {
this.xml = xml;
}
@Column(
name = "address",
unique = false,
nullable = true,
insertable = true,
updatable = true
)
public String getAddress() {
return this.address;
}
public void setAddress(String address) {
this.address = address;
}
@Column(
name = "hotel_name",
unique = false,
nullable = true,
insertable = true,
updatable = true
)
public String getHotelName() {
return this.hotelName;
}
public void setHotelName(String hotelName) {
this.hotelName = hotelName;
}
@Column(
name = "last_modified",
unique = false,
nullable = true,
insertable = true,
updatable = true,
length = 8
)
public Date getLastModified() {
return this.lastModified;
}
public void setLastModified(Date lastModified) {
this.lastModified = lastModified;
}
@Column(
name = "rating",
unique = false,
nullable = true,
insertable = true,
updatable = true
)
public Integer getRating() {
return this.rating;
}
public void setRating(Integer rating) {
this.rating = rating;
}
@JoinColumns(
{
@JoinColumn(
name = "city_code",
referencedColumnName = "city_code",
nullable = true,
insertable = false,
updatable = false
),
@JoinColumn(
name = "item_code",
referencedColumnName = "item_code",
nullable = true,
insertable = false,
updatable = false
)
}
)
@ManyToOne
@NotFound(action = NotFoundAction.IGNORE)
public OctopusEzrezHotelData getOctopusEzrezHotelData() {
return _octopusEzrezHotelData;
}
public void setOctopusEzrezHotelData(OctopusEzrezHotelData hotelData) {
_octopusEzrezHotelData = hotelData;
}
=========================================
This is the code for table two:
@Entity
@Table(
name = "octopus_ezrez_hotel_data",
schema = "public",
uniqueConstraints = {}
)
public class OctopusEzrezHotelData implements java.io.Serializable {
//~ Instance fields --------------------------------------------------------------------------------------
// Fields
private OctopusEzrezHotelDataId id;
private int octopusMergedHotelsid;
private Integer preference;
// Constructors
//~ Constructors -----------------------------------------------------------------------------------------
/** default constructor */
public OctopusEzrezHotelData() {
}
/** minimal constructor */
public OctopusEzrezHotelData(OctopusEzrezHotelDataId id, int octopusMergedHotelsid) {
this.id = id;
this.octopusMergedHotelsid = octopusMergedHotelsid;
}
/** full constructor */
public OctopusEzrezHotelData(OctopusEzrezHotelDataId id, int octopusMergedHotelsid, Integer preference) {
this.id = id;
this.octopusMergedHotelsid = octopusMergedHotelsid;
this.preference = preference;
}
//~ Methods ----------------------------------------------------------------------------------------------
public static OctopusEzrezHotelData findById(HibernateEntityManager em, OctopusEzrezHotelDataId id)
throws EzRezException
{
try {
OctopusEzrezHotelData instance = (OctopusEzrezHotelData) em.getSession().get(
"com.ezrez.crs.gulliver.hibernate2.OctopusEzrezHotelData", id);
return instance;
} catch (Exception re) {
throw EzRezException.fromException(re);
}
}
// Property accessors
@AttributeOverrides(
{
@AttributeOverride(
name = "cityCode",
column =
@Column(
name = "city_code",
unique = false,
nullable = false,
insertable = true,
updatable = true
)
),
@AttributeOverride(
name = "itemCode",
column =
@Column(
name = "item_code",
unique = false,
nullable = false,
insertable = true,
updatable = true
)
)
}
)
@EmbeddedId
public OctopusEzrezHotelDataId getId() {
return this.id;
}
public void setId(OctopusEzrezHotelDataId id) {
this.id = id;
}
@Column(
name = "octopus_merged_hotelsid",
unique = false,
nullable = false,
insertable = true,
updatable = true
)
public int getOctopusMergedHotelsid() {
return this.octopusMergedHotelsid;
}
public void setOctopusMergedHotelsid(int octopusMergedHotelsid) {
this.octopusMergedHotelsid = octopusMergedHotelsid;
}
@Column(
name = "preference",
unique = false,
nullable = true,
insertable = true,
updatable = true
)
public Integer getPreference() {
return this.preference;
}
public void setPreference(Integer preference) {
this.preference = preference;
}
=========================================
This is the log for the correct select that does the outer join:
10:37:30,046 INFO TableMetadata:40 - table found: public.octopus_hotels
10:37:30,046 INFO TableMetadata:41 - columns: [last_modified, item_code, city_code, hotel_name, address, language_code, rating, xml]
10:37:30,093 INFO TableMetadata:40 - table found: public.octopus_ezrez_hotel_data
10:37:30,093 INFO TableMetadata:41 - columns: [item_code, city_code, preference, octopus_merged_hotelsid]
10:37:30,109 DEBUG DriverManagerConnectionProvider:129 - returning connection to pool, pool size: 1
10:37:30,109 DEBUG SessionFactoryImpl:387 - Checking 0 named HQL queries
10:37:30,109 DEBUG SessionFactoryImpl:407 - Checking 0 named SQL queries
10:37:30,203 DEBUG SessionImpl:222 - opened session at timestamp: 4738596250112000
10:37:30,250 DEBUG AbstractBatcher:358 - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
10:37:30,250 DEBUG ConnectionManager:415 - opening JDBC connection
10:37:30,250 DEBUG DriverManagerConnectionProvider:93 - total checked-out connections: 0
10:37:30,250 DEBUG DriverManagerConnectionProvider:99 - using pooled JDBC connection, pool size: 0
10:37:30,265 DEBUG SQL:393 -
select
this_.city_code as city1_0_1_,
this_.item_code as item2_0_1_,
this_.language_code as language3_0_1_,
this_.address as address0_1_,
this_.last_modified as last5_0_1_,
this_.xml as xml0_1_,
this_.hotel_name as hotel7_0_1_,
this_.rating as rating0_1_,
octopusezr2_.city_code as city1_1_0_,
octopusezr2_.item_code as item2_1_0_,
octopusezr2_.octopus_merged_hotelsid as octopus3_1_0_,
octopusezr2_.preference as preference1_0_
from
octopus_hotels this_
left outer join
public.octopus_ezrez_hotel_data octopusezr2_
on this_.city_code=octopusezr2_.city_code
and this_.item_code=octopusezr2_.item_code
where
this_.hotel_name ilike ?
===================================
This is the log for the second select on table two:
0:37:30,750 DEBUG Loader:1808 - done entity load
10:37:30,750 DEBUG TwoPhaseLoad:135 - adding entity to second-level cache: [com.ezrez.crs.gulliver.hibernate2.OctopusHotels#component[cityCode,itemCode,languageCode]{languageCode=KO, itemCode=OPA, cityCode=OPAT}]
10:37:30,750 DEBUG NonstrictReadWriteCache:71 - Caching: com.ezrez.crs.gulliver.hibernate2.OctopusHotels#com.ezrez.crs.gulliver.hibernate2.OctopusHotelsId@c6b5fc45
10:37:30,765 DEBUG Cache:1260 - com.ezrez.crs.gulliver.hibernate2.OctopusHotels#com.ezrez.crs.gulliver.hibernate2.OctopusHotelsId@c6b5fe21 now: 1156883850765
10:37:30,765 DEBUG Cache:1261 - com.ezrez.crs.gulliver.hibernate2.OctopusHotels#com.ezrez.crs.gulliver.hibernate2.OctopusHotelsId@c6b5fe21 Creation Time: 1156883850687 Next To Last Access Time: 0
10:37:30,765 DEBUG Cache:1263 - com.ezrez.crs.gulliver.hibernate2.OctopusHotels#com.ezrez.crs.gulliver.hibernate2.OctopusHotelsId@c6b5fe21 mostRecentTime: 1156883850687
10:37:30,765 DEBUG Cache:1264 - com.ezrez.crs.gulliver.hibernate2.OctopusHotels#com.ezrez.crs.gulliver.hibernate2.OctopusHotelsId@c6b5fe21 Age to Idle: 120000 Age Idled: 78
10:37:30,765 DEBUG Cache:1242 - ezrez: Is element with key com.ezrez.crs.gulliver.hibernate2.OctopusHotels#com.ezrez.crs.gulliver.hibernate2.OctopusHotelsId@c6b5fe21 expired?: false
10:37:30,765 DEBUG TwoPhaseLoad:206 - done materializing entity [com.ezrez.crs.gulliver.hibernate2.OctopusHotels#component[cityCode,itemCode,languageCode]{languageCode=KO, itemCode=OPA, cityCode=OPAT}]
10:37:30,781 DEBUG TwoPhaseLoad:107 - resolving associations for [com.ezrez.crs.gulliver.hibernate2.OctopusHotels#component[cityCode,itemCode,languageCode]{languageCode=EN, itemCode=OPA, cityCode=OPAT}]
10:37:30,781 DEBUG DefaultLoadEventListener:154 - loading entity: [com.ezrez.crs.gulliver.hibernate2.OctopusEzrezHotelData#component[cityCode,itemCode]{itemCode=OPA, cityCode=OPAT}]
10:37:30,781 DEBUG DefaultLoadEventListener:309 - attempting to resolve: [com.ezrez.crs.gulliver.hibernate2.OctopusEzrezHotelData#component[cityCode,itemCode]{itemCode=OPA, cityCode=OPAT}]
10:37:30,781 DEBUG DefaultLoadEventListener:345 - object not resolved in any cache: [com.ezrez.crs.gulliver.hibernate2.OctopusEzrezHotelData#component[cityCode,itemCode]{itemCode=OPA, cityCode=OPAT}]
10:37:30,781 DEBUG AbstractEntityPersister:2814 - Fetching entity: [com.ezrez.crs.gulliver.hibernate2.OctopusEzrezHotelData#component[cityCode,itemCode]{itemCode=OPA, cityCode=OPAT}]
10:37:30,781 DEBUG Loader:1777 - loading entity: [com.ezrez.crs.gulliver.hibernate2.OctopusEzrezHotelData#component[cityCode,itemCode]{itemCode=OPA, cityCode=OPAT}]
10:37:30,781 DEBUG AbstractBatcher:358 - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
10:37:30,781 DEBUG ConnectionManager:415 - opening JDBC connection
10:37:30,781 DEBUG DriverManagerConnectionProvider:93 - total checked-out connections: 0
10:37:30,781 DEBUG DriverManagerConnectionProvider:99 - using pooled JDBC connection, pool size: 0
10:37:30,781 DEBUG SQL:393 -
select
octopusezr0_.city_code as city1_1_0_,
octopusezr0_.item_code as item2_1_0_,
octopusezr0_.octopus_merged_hotelsid as octopus3_1_0_,
octopusezr0_.preference as preference1_0_
from
public.octopus_ezrez_hotel_data octopusezr0_
where
octopusezr0_.city_code=?
and octopusezr0_.item_code=?
|
Login
FAQ
Search