May be dumb questions /Suggestions, but I have the following:
1) If you add System.out.println("*****") before and after "List res =
query.getResultList(); "
then all these SQL traces are shown between these "*****" ?
I think the answer for above is true. Please let me know if that is not
the case.
2) Also can you please try to remove map from the entity and see if it makes
any difference ?
Reason for this is because recently I have noticed that, in case
collections are part of entity the SQL generation is not efficient in some
scenarios. I opened a JIRA for this OPENJPA-1140.
This is related to modifying the collections and detaching the object. In
your case Map is transient and hence may not be related to the JIRA but just
curious to see if removing collections is making any difference.
3) I gave a simple testcase (unable to reproduce with it) in another post on
the same subject.
Can you please try to modify it and break it ?
Regards,
Ravi.
Kevin Sutter 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.
>>
>
>
--
View this message in context:
http://n2.nabble.com/openJPA-generates-select-per-row---impossible-to-use-for-simple-select-statements-tp3261512p3264957.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
