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.
