Really there is no query involved (I mean I wrote).
Also here is another entity might be mentioned "
@Entity
@Table(name="USER_PROFILE")
public class UserProfile {
@Transient public static final int ROLE_ADMIN = 0;
@Transient public static final int ROLE_COMPANY = 1;
@Transient public static final int ROLE_STORE = 2;
@Id
@Column(name="USER_ID", columnDefinition="VARCHAR(10)",
nullable=false)
private String name = null;
@Column(name="USER_ROLE", columnDefinition="INTEGER",
nullable=false)
private int role = ROLE_STORE;
@ManyToMany(fetch=FetchType.EAGER)
@JoinTable(name="USER_PROFILE_COMPANY",
[EMAIL PROTECTED](name="USER_ID",
referencedColumnName="USER_ID"),
[EMAIL PROTECTED](name="SCHEMA_ID",
referencedColumnName="SCHEMA_ID")
)
private List<UserCompany> companies = new ArrayList<UserCompany>();
@ManyToMany(fetch=FetchType.EAGER)
@JoinTable(name="USER_PROFILE_STORE",
[EMAIL PROTECTED](name="USER_ID",
referencedColumnName="USER_ID"),
[EMAIL PROTECTED](name="SCHEMA_ID",
referencedColumnName="SCHEMA_ID")
)
And in program I was just calling userProfile.getCompanies().
Because fetch is EAGER it was getting it by itself. What I really expected
and needed. )
I didn't expected though it will get rach store entity in different select.
Though I have made OpenJPA select it my way (one select per company).
In order to do that I had to change EAGER to LAZY on UserCompany like this :
@Entity
@DiscriminatorValue(value="COMPANY")
public class UserCompany extends UserSchema {
@OneToMany(mappedBy="company")
private List<UserStore> stores = new ArrayList<UserStore>();
and in calling program do it manually. Also it might happen that UserProfile
involved in this mess too
since it has relation to stores too. When I changed EAGER to LAZY and added
manual call in client it started working right. (Added call marked bold and
italic)
Here is how I am calling it from client program :
EntityManager em = emf.createEntityManager();
currentUser = em.find(UserProfile.class, userName);
if (currentUser != null) {
List<UserCompany> list = currentUser.getCompanies();
currentUser.getStores();
for (UserCompany userCompany : list) {
userCompany.getStores();
}
}
em.close();
Marc Prud'hommeaux wrote:
>
> Andrey-
>
>
>> Didn't we already selected all what we need with first select ????
>>
>> SELECT t0.SCHEMA_ID, t0.DTYPE, t0.NAME FROM USER_SCHEMA t0 WHERE
>> t0.company_SCHEMA_ID = ? [params=(String) AAZ01.0000]
>>
>> Why all the per store sql requests executed?
>
> Are you running the query against the "UserSchema" entity, or against
> the "UserCompany"? If it is the "UserSchema", then in the initial
> select, OpenJPA doesn't make any assumptions about which subclass
> might actually will be returned. So when each item UserCompany
> instance iterated over in the results, its eager "company" OneToMany
> field is loaded, causing the join to happen for each instance.
>
> I think that if your query is against "UserCompany" (e.g., "select x
> from UserCompany x"), then you might not see the join happen for each
> returned instance.
>
>
>
> On Jul 23, 2007, at 12:20 PM, Andrey Tedeev wrote:
>
>>
>> I have 3 entities described below (gettters and setters are cut for
>> readability):
>> ----------------------------------------------------------------------
>> -----------------
>> @Entity
>> @Table(name="USER_SCHEMA")
>> @Inheritance(strategy=InheritanceType.SINGLE_TABLE)
>> @DiscriminatorColumn(discriminatorType=DiscriminatorType.STRING,
>> name="DTYPE")
>>
>> public abstract class UserSchema {
>>
>> @Id
>> @Column(name="SCHEMA_ID", columnDefinition="VARCHAR(100)",
>> nullable=false)
>> private String schema = null;
>>
>> @Column(name="NAME",
>> columnDefinition="VARCHAR(100)",
>> nullable=false)
>> private String name = null;
>> ...
>> ----------------------------------------------------------------------
>> -----------------
>> @Entity
>> @DiscriminatorValue(value="COMPANY")
>> public class UserCompany extends UserSchema {
>>
>> @OneToMany(mappedBy="company", fetch=FetchType.EAGER)
>> private List<UserStore> stores = new ArrayList<UserStore>();
>>
>> ...
>>
>> @Entity
>> @DiscriminatorValue(value="STORE")
>> public class UserStore extends UserSchema {
>>
>> @ManyToOne
>> private UserCompany company = null;
>>
>> ...
>> ----------------------------------------------------------------------
>> -----------------
>> SQL Table generated properly by Mapped Tool and has expected
>> structure :
>>
>> CREATE TABLE USER_SCHEMA (
>> SCHEMA_ID VARCHAR(100) NOT NULL ,
>> NAME VARCHAR(100) NOT NULL ,
>> DTYPE VARCHAR(31) DEFAULT NULL ,
>> COMPANY_SCHEMA_ID VARCHAR(100) DEFAULT NULL ,
>> CONSTRAINT PRIMARY KEY( SCHEMA_ID ) ) ;
>> ----------------------------------------------------------------------
>> -----------------
>> I inserted some data into tables. Company with id = AAZ01.0000 and
>> Stores
>> with ids (AAZ01.0070, AAZ01.0135 etc. which belong to AAZ01.0000
>> (COMPANY_SCHEMA_ID = AAZ01.0000).
>> Now I'm reading data from database and when UserCompany reads it's
>> UserStores
>> I can see in SQL TRACE that OpenJPA creates one sql select request
>> per each
>> store of the company like this :
>>
>> 3329 ssi.jpa TRACE [main] openjpa.jdbc.SQL - <t 10038190, conn
>> 179514>
>> executing prepstmnt 30362156 SELECT t0.SCHEMA_ID, t0.DTYPE, t0.NAME
>> FROM
>> USER_SCHEMA t0 WHERE t0.company_SCHEMA_ID = ? [params=(String)
>> AAZ01.0000]
>> 3585 ssi.jpa TRACE [main] openjpa.jdbc.SQL - <t 10038190, conn
>> 179514>
>> [256 ms] spent
>> 3838 ssi.jpa TRACE [main] openjpa.jdbc.SQL - <t 10038190, conn
>> 179514>
>> executing prepstmnt 28171097 SELECT t1.SCHEMA_ID, t1.DTYPE, t1.NAME
>> FROM
>> USER_SCHEMA t0 INNER JOIN USER_SCHEMA t1 ON t0.company_SCHEMA_ID =
>> t1.SCHEMA_ID WHERE t0.SCHEMA_ID = ? [params=(String) AAZ01.0070]
>> 4088 ssi.jpa TRACE [main] openjpa.jdbc.SQL - <t 10038190, conn
>> 179514>
>> [250 ms] spent
>> 4566 ssi.jpa TRACE [main] openjpa.jdbc.SQL - <t 10038190, conn
>> 179514>
>> executing prepstmnt 13359904 SELECT t1.SCHEMA_ID, t1.DTYPE, t1.NAME
>> FROM
>> USER_SCHEMA t0 INNER JOIN USER_SCHEMA t1 ON t0.company_SCHEMA_ID =
>> t1.SCHEMA_ID WHERE t0.SCHEMA_ID = ? [params=(String) AAZ01.0135]
>> 4823 ssi.jpa TRACE [main] openjpa.jdbc.SQL - <t 10038190, conn
>> 179514>
>> [257 ms] spent
>> 5328 ssi.jpa TRACE [main] openjpa.jdbc.SQL - <t 10038190, conn
>> 179514>
>> executing prepstmnt 15177785 SELECT t1.SCHEMA_ID, t1.DTYPE, t1.NAME
>> FROM
>> USER_SCHEMA t0 INNER JOIN USER_SCHEMA t1 ON t0.company_SCHEMA_ID =
>> t1.SCHEMA_ID WHERE t0.SCHEMA_ID = ? [params=(String) AAZ01.0140]
>>
>>
>> !!! FINALLY THE QUESTION :
>>
>> Didn't we already selected all what we need with first select ????
>>
>> SELECT t0.SCHEMA_ID, t0.DTYPE, t0.NAME FROM USER_SCHEMA t0 WHERE
>> t0.company_SCHEMA_ID = ? [params=(String) AAZ01.0000]
>>
>> Why all the per store sql requests executed?
>>
>>
>> --
>> View this message in context: http://www.nabble.com/
>> InheritanceType.SINGLE_TABLE-generated-sql-selects-
>> tf4131850.html#a11750927
>> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>>
>
>
>
--
View this message in context:
http://www.nabble.com/InheritanceType.SINGLE_TABLE-generated-sql-selects-tf4131850.html#a11754781
Sent from the OpenJPA Users mailing list archive at Nabble.com.
