I've been trying with just Derby and letting OpenJPA create the necessary tables. What database are you using? And, how are the tables getting created, or did they pre-exist? If the pre-existed, then could you provide the ddl for the database and tables?
Any other configuration properties that are in use? Thanks, Kevin On Wed, Jul 15, 2009 at 11:27 AM, Kevin Sutter <[email protected]> wrote: > Of course, pull the Hibernate card... Now you have my attention! :-) > > I've been trying every which way to reproduce this problem with no luck. > For some reason, OpenJPA is interpreting these Account entities as not being > complete after the initial Select, and then proceeds to query each > individual row to fill in the other values. There doesn't seem to be > anything special with these fields that are supposedly incomplete - three > Strings and a BigDecimal. At first I thought it might be due to the TABLE > id generation type, but no luck. I tried your various field definitions, no > luck. I tried querying for a specific Date like you did, no luck. > > Is there anything special in the IFilterTO interface? > > I'll continue to experiment, but if anybody has another ideas, I would be > interested. > > Kevin > > > On Wed, Jul 15, 2009 at 8:26 AM, om <[email protected]> wrote: > >> >> Hi Kevin, >> Below is the object. It's the full version, but when I tried to make it as >> simple as possible, the problem did not disappear. >> Moreover, I've tried Hibernate JPA, and with it same code works perfectly, >> with no additional queries. But OPENJPA continues generating additional >> queries per entity. >> >> Here is Hibernate usage I tried instead of OPENJPA : >> HibernatePersistence per = new HibernatePersistence(); >> EntityManagerFactory fac = >> per.createEntityManagerFactory("MainPersistence", System.getProperties()); >> EntityManager man = fac.createEntityManager(); >> >> Account object : >> >> public class Account implements Serializable, Comparable, IFilterTO { >> >> public static final String FIELD_NAME = "Account"; >> public static final String FIELD_MASK = "Mask"; >> public static final String FIELD_CURRENCY_CODE = "Currency Code"; >> >> public static final String[] FILTER_FIELDS = new String[]{ >> FIELD_NAME, FIELD_MASK, FIELD_CURRENCY_CODE >> }; >> >> Map mapVals = new HashMap(); >> >> Long id; >> String name; >> String seq; >> String mask; >> String curCode; >> Calendar date; >> BigDecimal val; >> Integer version; >> boolean clearAccountDataFlag; >> >> public Account() { >> } >> >> public Account(String account, String seq, String mask, String curCode, >> Calendar date, BigDecimal val, boolean >> clearAccountDataFlag) { >> setName(account); >> setMask(mask); >> setSeq(seq); >> setDate(date); >> setVal(val); >> setClearAccountDataFlag(clearAccountDataFlag); >> setCurCode(curCode); >> } >> >> @Override >> public boolean equals(Object o) { >> if (this == o) return true; >> if (!(o instanceof Account)) return false; >> >> Account account1 = (Account) o; >> >> if (!name.equals(account1.name)) return false; >> if (!seq.equals(account1.seq)) return false; >> if (!mask.equals(account1.mask)) return false; >> if (!curCode.equals(account1.curCode)) return false; >> if (!date.equals(account1.date)) return false; >> >> return true; >> } >> >> @Override >> public int hashCode() { >> int result = name.hashCode(); >> result = 31 * result + seq.hashCode(); >> result = 31 * result + mask.hashCode(); >> result = 31 * result + curCode.hashCode(); >> result = 31 * result + date.hashCode(); >> return result; >> } >> >> public int compareTo(Object o) { >> if (this == o) return 0; >> if (!(o instanceof Account)) return 0; >> >> Account account1 = (Account) o; >> >> if (!name.equals(account1.name)) return name.compareTo(account1.name >> ); >> if (!seq.equals(account1.seq)) return seq.compareTo(account1.seq); >> if (!mask.equals(account1.mask)) return >> mask.compareTo(account1.mask); >> if (!curCode.equals(account1.curCode)) return >> curCode.compareTo(account1.curCode); >> if (!date.equals(account1.date)) return >> date.compareTo(account1.date); >> >> return 0; >> } >> >> >> @Transient >> public Map getValues() { >> return mapVals; >> } >> >> public void setValues(Map mapVals) { >> this.mapVals = mapVals; >> } >> >> >> @Id >> @GeneratedValue(strategy=GenerationType.TABLE) >> public Long getId() { >> return id; >> } >> >> public void setId(Long id) { >> this.id = id; >> } >> >> @Column(name="acc_name",length=100) >> public String getName() { >> return name; >> } >> >> public void setName(String name) { >> this.name = name; >> setValue(FIELD_NAME, name); >> } >> >> @Column(name="acc_seq",length=10) >> public String getSeq() { >> return seq; >> } >> >> public void setSeq(String seq) { >> this.seq = seq; >> } >> >> @Column(name="mask",length=10) >> public String getMask() { >> return mask; >> } >> >> public void setMask(String mask) { >> this.mask = mask; >> setValue(FIELD_MASK, mask); >> } >> >> @Column(name="cur_code",length=10) >> public String getCurCode() { >> return curCode; >> } >> >> public void setCurCode(String curCode) { >> this.curCode = curCode; >> setValue(FIELD_CURRENCY_CODE, curCode); >> } >> >> @Column(name="acc_date") >> public Calendar getDate() { >> return date; >> } >> >> public void setDate(Calendar date) { >> this.date = Util.resetTime(date); >> } >> >> @Column(name="value") >> public BigDecimal getVal() { >> return val; >> } >> >> public void setVal(BigDecimal val) { >> this.val = val; >> } >> >> @Version >> public Integer getVersion() { >> return version; >> } >> >> public void setVersion(Integer version) { >> this.version = version; >> } >> >> @Transient >> public boolean isClearAccountDataFlag() { >> return clearAccountDataFlag; >> } >> >> public void setClearAccountDataFlag(boolean clearAccountDataFlag) { >> this.clearAccountDataFlag = clearAccountDataFlag; >> } >> >> public void setValue( String key, String value ) { >> mapVals.put(key, value); >> } >> >> @Transient >> public String getValue( String key ) { >> return (String)mapVals.get(key); >> } >> >> } >> >> >> >> >> Kevin Sutter wrote: >> > >> > Hi, >> > How is the Account entity defined? From what you have provided, I don't >> > see >> > a reason for the extra Select statements either. I'm just wondering if >> > something in your Entity definition is (accidentally) triggering these >> > extra >> > interactions with the database. >> > >> > Thanks, >> > Kevin >> > >> > On Wed, Jul 15, 2009 at 2:19 AM, om <[email protected]> wrote: >> > >> >> >> >> Hi All! >> >> >> >> I’m new in openJPA, and didn’t manage to get over the problem with >> simple >> >> select statement for one object after a few days of investigation. >> Please >> >> help! >> >> >> >> For simple select from one object, OpenJPA ( same strategy for 1.0, >> >> 1.2.1, >> >> 2.0 ) fist generates right query to retrieve all rows and fields, and >> >> then >> >> start generating query per object by primary key. >> >> >> >> Code : >> >> StringBuffer queryBuf = new StringBuffer("SELECT a FROM Account AS >> a >> >> WHERE a.date = :date "); >> >> PersistenceProviderImpl impl = new PersistenceProviderImpl(); >> >> OpenJPAEntityManagerFactory fac = >> >> impl.createEntityManagerFactory("MainPersistence", >> >> System.getProperties()); >> >> OpenJPAEntityManager man = fac.createEntityManager(); >> >> >> >> Query query = man.createQuery(queryBuf.toString()); >> >> query.setParameter("date", reportDate); >> >> List res = query.getResultList(); >> >> >> >> >> >> LOG TRACE >> >> >> >> [7/14/09 16:57:50:475 MSD] R 266 MainPersistence TRACE >> >> openjpa.Runtime >> >> - >> >> Query "SELECT a FROM Account AS a WHERE a.date = :date " is cached as >> >> target >> >> query "null" >> >> [7/14/09 16:57:50:475 MSD] R 266 MainPersistence TRACE openjpa.Query >> - >> >> Executing query: [SELECT a FROM Account AS a WHERE a.date = :date] with >> >> parameters: {date=java.util.GregorianCalendar[]} >> >> [7/14/09 16:57:50:475 MSD] R 266 MainPersistence TRACE >> >> openjpa.jdbc.SQL >> >> - <t 1495423266, conn 1329090360> executing prepstmnt 1388597956 SELECT >> >> t0.id, t0.version, t0.cur_code, t0.acc_date, t0.mask, t0.acc_name, >> >> t0.acc_seq, t0.value FROM ACCOUNT t0 WHERE (t0.acc_date = ?) >> >> [params=(Timestamp) 2009-07-03 00:00:00.0] >> >> [7/14/09 16:57:50:553 MSD] R 344 MainPersistence TRACE >> >> openjpa.jdbc.SQL >> >> - >> >> <t 1495423266, conn 1329090360> [78 ms] spent >> >> >> >> [7/14/09 16:57:50:553 MSD] R 344 MainPersistence TRACE >> >> openjpa.jdbc.SQL >> >> - >> >> <t 1495423266, conn 1329090360> executing prepstmnt 139855958 SELECT >> >> t0.mask, t0.acc_name, t0.acc_seq, t0.value FROM ACCOUNT t0 WHERE t0.id= >> >> ? >> >> [params=(long) 328] >> >> [7/14/09 16:57:50:631 MSD] R 422 MainPersistence TRACE [WebContainer >> : >> >> 2] >> >> openjpa.jdbc.SQL - <t 1495423266, conn 1329090360> [78 ms] spent >> >> [7/14/09 16:57:50:631 MSD] R 422 MainPersistence TRACE [WebContainer >> : >> >> 2] >> >> openjpa.jdbc.SQL - <t 1495423266, conn 1329090360> executing prepstmnt >> >> 646850190 SELECT t0.mask, t0.acc_name, t0.acc_seq, t0.value FROM >> ACCOUNT >> >> t0 >> >> WHERE t0.id = ? [params=(long) 329] >> >> [7/14/09 16:57:50:709 MSD] R 500 MainPersistence TRACE [WebContainer >> : >> >> 2] >> >> openjpa.jdbc.SQL - <t 1495423266, conn 1329090360> [78 ms] spent >> >> [7/14/09 16:57:50:709 MSD] R 500 MainPersistence TRACE [WebContainer >> : >> >> 2] >> >> openjpa.jdbc.SQL - <t 1495423266, conn 1329090360> executing prepstmnt >> >> 2146074602 SELECT t0.mask, t0.acc_name, t0.acc_seq, t0.value FROM >> ACCOUNT >> >> t0 >> >> WHERE t0.id = ? [params=(long) 330] >> >> [7/14/09 16:57:50:787 MSD] R 578 MainPersistence TRACE [WebContainer >> : >> >> 2] >> >> openjpa.jdbc.SQL - <t 1495423266, conn 1329090360> [78 ms] spent >> >> ……………………………….. >> >> >> >> >> >> I need just list of detached objects to show it in grid. As it’s seen >> >> from >> >> log trace above, first query is enough to return all necessary objects >> >> and >> >> fields. >> >> Why OpenJPA makes select per object after that? In this case simple >> code >> >> above works 37 seconds for retrieving 440 rows, since same jdbc select >> >> and >> >> wrap works 1.5 sec. I’ve tried different query hints and a few openjpa >> >> versions, but with no result. >> >> >> >> >> >> -- >> >> View this message in context: >> >> >> http://n2.nabble.com/openJPA-generates-select-per-row---impossible-to-use-for-simple-select-statements-tp3261512p3261512.html >> >> Sent from the OpenJPA Users mailing list archive at Nabble.com. >> >> >> > >> > >> >> -- >> View this message in context: >> http://n2.nabble.com/openJPA-generates-select-per-row---impossible-to-use-for-simple-select-statements-tp3261512p3263178.html >> Sent from the OpenJPA Users mailing list archive at Nabble.com. >> > >
