You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Andrey Tedeev <te...@hotmail.com> on 2007/07/23 21:20:08 UTC
InheritanceType.SINGLE_TABLE generated sql selects
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.
Re: InheritanceType.SINGLE_TABLE generated sql selects
Posted by Andrey Tedeev <te...@hotmail.com>.
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",
joinColumns=@JoinColumn(name="USER_ID", referencedColumnName="USER_ID"),
inverseJoinColumns=@JoinColumn(name="SCHEMA_ID",
referencedColumnName="SCHEMA_ID")
)
private List<UserCompany> companies = new ArrayList<UserCompany>();
@ManyToMany(fetch=FetchType.EAGER)
@JoinTable(name="USER_PROFILE_STORE",
joinColumns=@JoinColumn(name="USER_ID", referencedColumnName="USER_ID"),
inverseJoinColumns=@JoinColumn(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.
Re: InheritanceType.SINGLE_TABLE generated sql selects
Posted by Marc Prud'hommeaux <mp...@apache.org>.
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.
>