You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by tlianza <na...@lianza.org> on 2013/09/03 21:56:08 UTC
Re: Using QueryCache, still seeing queries sent to the DB
tlianza wrote
> SELECT m from ImageRepModel m where m._image = :image AND m._type = :type
So, we've done quite a bit more troubleshooting here and found that the
scenarios in which this query is *not* being cached is then the :image param
that's passed in is a *detached* ImageModel object. Is there any reason
that should affect whether or not the query misses the cache?
Keep in mind the relationship here is such that this query only needs the
ImageModel's id, which is both available in the detatched object and is
marked as that object's primary key.
Thanks,
Tom
--
View this message in context: http://openjpa.208410.n2.nabble.com/Using-QueryCache-still-seeing-queries-sent-to-the-DB-tp7584807p7584837.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: How to compare fields of type TemporalType.TIMESTAMP - but just
by Date?
Posted by Chris <cw...@gmail.com>.
John,
I see where you're going with that - but I need a query that selects
data on a single given date, not "greater or equal" to the date. So I
took your idea, but used BETWEEN and an extra date, like so:
select bd from MdBaseData bd
where bd.upsertDate between :upsertDateStart and :upsertDateEnd
SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
Date upsertDateStart = df.parse("20130913");
Date upsertDateEnd = df.parse("20130914");
The resulting generated SQL is as follows:
SELECT t0.BASE_DATA_ID, t0.CRNCY_CODE, t0.datadate, t0.ident,
t0.LAST_PUBLISHED_DATE, t0.PRICE_VAL_1, t0.PRICE_VAL_2,
t0.TYPE_NAME,
t0.UPSERT_DATE FROM MD_BASE_DATA t0
WHERE (t0.UPSERT_DATE >= ? AND t0.UPSERT_DATE <= ?)
[params=(Timestamp) 2013-09-13 00:00:00.0, (Timestamp)
2013-09-14 00:00:00.0]
Now this query will return data on a single date, September 13th, well,
also with a one second overlap
into Sept 14th, but operationally there will never be data inserted at
that second of time.
Thanks for you help!
Regards,
Chris
On 9/13/2013 3:05 AM, Boblitz John wrote:
> Hello Chris,
>
> My entities all contain timestamp fields for the creation date of the record.
>
> as a Test, I created the following:
>
>
> <named-query name="Currency.created">
> <query>
> SELECT c
> FROM Currency c
> WHERE c.created <![CDATA[>=]]>
> :created
> ORDER BY c.code desc
> </query>
> </named-query>
>
> @Test
> public void test() {
>
> EntityManagerFactory emf = Persistence.createEntityManagerFactory("g11dev");
> EntityManager em = emf.createEntityManager();
> SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
> try {
>
> Timestamp date = new Timestamp(df.parse("20100101").getTime()); // This is a java.sql.Timestamp !!
> TypedQuery<Currency> query = em.createNamedQuery("Currency.created", Currency.class);
> query.setParameter("created", date);
> List<Currency> list = query.getResultList();
>
> assertTrue(!list.isEmpty());
> } catch (Exception pEx) {
> pEx.printStackTrace();
> fail();
> }
> }
>
> The resulting query is:
>
> SELECT t0.uniqueid, t0.versionid, t0.active, t0.created, t0.createdby, t0.modified, t0.modifiedby, t0.code, t0.description, t0.includeincustomerlisting, t0.includeinlisting
> FROM galaxy11.currency t0
> WHERE (t0.created >= ?)
> ORDER BY t0.code DESC
> [params=(Timestamp) 2010-01-01 00:00:00.0]
>
> and works as expected.
>
> Hope this helps.
>
> Regards,
>
>
> John
>
>> -----Original Message-----
>> From: Chris [mailto:cwolf.algo@gmail.com]
>> Sent: Thursday, September 12, 2013 7:47 PM
>> To: users@openjpa.apache.org
>> Subject: Re: How to compare fields of type TemporalType.TIMESTAMP - but just
>> by Date?
>>
>> In my last post, I asked, "How can I do this in JPA?", but I meant to ask, "How
>> can I do this in JPQL?".
>>
>> I'm going to guess it's impossible because I'm not seeing any JPQL
>> date/datetime manipulation functions in the docs, other then CURRENT_DATE |
>> CURRENT_TIME | CURRENT_TIMESTAMP, so I resorted to a native SQL query
>> using the syntax shown in my initial post.
>>
>> Thanks,
>>
>>
>> Chris
>>
>> On 9/12/2013 1:12 PM, Chris wrote:
>>> I have an entity with a timestamp field, but for the purpose of a
>>> certain query, I want to only compare by date - and ignore the time
>>> component of the value.
>>>
>>> For example, in Oracle, the raw SQL would look like:
>>>
>>> SELECT t0.BASE_DATA_ID, t0.CLIENT_ID, t0.DB_USER, t0.OS_USER,
>>> t0.UPSERT_DATE,
>>> t0.CRNCY_CODE, t0.datadate, t0.ident, t0.LAST_PUBLISHED_DATE,
>>> t0.PRICE_VAL_1, t0.PRICE_VAL_2, t0.TYPE_NAME
>>> FROM MD_BASE_DATA t0
>>> WHERE (t0.CLIENT_ID = 'JUNIT-1'
>>> and to_date(to_char(t0.UPSERT_DATE,'YYYY-MM-DD'),'YYYY-MM-DD')
>>> = to_date(to_char(current_date - 1,'YYYY-MM-DD'),'YYYY-MM-DD'))
>>>
>>> This query filters by field "UPSERT_DATE" being yesterday, ignoring
>>> the time part.
>>>
>>> How can I do this in JPA?
>>>
>>> Thanks,
>>>
>>>
>>> Chris
RE: How to compare fields of type TemporalType.TIMESTAMP - but just
by Date?
Posted by Boblitz John <jo...@bertschi.com>.
Hello Chris,
My entities all contain timestamp fields for the creation date of the record.
as a Test, I created the following:
<named-query name="Currency.created">
<query>
SELECT c
FROM Currency c
WHERE c.created <![CDATA[>=]]>
:created
ORDER BY c.code desc
</query>
</named-query>
@Test
public void test() {
EntityManagerFactory emf = Persistence.createEntityManagerFactory("g11dev");
EntityManager em = emf.createEntityManager();
SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
try {
Timestamp date = new Timestamp(df.parse("20100101").getTime()); // This is a java.sql.Timestamp !!
TypedQuery<Currency> query = em.createNamedQuery("Currency.created", Currency.class);
query.setParameter("created", date);
List<Currency> list = query.getResultList();
assertTrue(!list.isEmpty());
} catch (Exception pEx) {
pEx.printStackTrace();
fail();
}
}
The resulting query is:
SELECT t0.uniqueid, t0.versionid, t0.active, t0.created, t0.createdby, t0.modified, t0.modifiedby, t0.code, t0.description, t0.includeincustomerlisting, t0.includeinlisting
FROM galaxy11.currency t0
WHERE (t0.created >= ?)
ORDER BY t0.code DESC
[params=(Timestamp) 2010-01-01 00:00:00.0]
and works as expected.
Hope this helps.
Regards,
John
> -----Original Message-----
> From: Chris [mailto:cwolf.algo@gmail.com]
> Sent: Thursday, September 12, 2013 7:47 PM
> To: users@openjpa.apache.org
> Subject: Re: How to compare fields of type TemporalType.TIMESTAMP - but just
> by Date?
>
> In my last post, I asked, "How can I do this in JPA?", but I meant to ask, "How
> can I do this in JPQL?".
>
> I'm going to guess it's impossible because I'm not seeing any JPQL
> date/datetime manipulation functions in the docs, other then CURRENT_DATE |
> CURRENT_TIME | CURRENT_TIMESTAMP, so I resorted to a native SQL query
> using the syntax shown in my initial post.
>
> Thanks,
>
>
> Chris
>
> On 9/12/2013 1:12 PM, Chris wrote:
> > I have an entity with a timestamp field, but for the purpose of a
> > certain query, I want to only compare by date - and ignore the time
> > component of the value.
> >
> > For example, in Oracle, the raw SQL would look like:
> >
> > SELECT t0.BASE_DATA_ID, t0.CLIENT_ID, t0.DB_USER, t0.OS_USER,
> > t0.UPSERT_DATE,
> > t0.CRNCY_CODE, t0.datadate, t0.ident, t0.LAST_PUBLISHED_DATE,
> > t0.PRICE_VAL_1, t0.PRICE_VAL_2, t0.TYPE_NAME
> > FROM MD_BASE_DATA t0
> > WHERE (t0.CLIENT_ID = 'JUNIT-1'
> > and to_date(to_char(t0.UPSERT_DATE,'YYYY-MM-DD'),'YYYY-MM-DD')
> > = to_date(to_char(current_date - 1,'YYYY-MM-DD'),'YYYY-MM-DD'))
> >
> > This query filters by field "UPSERT_DATE" being yesterday, ignoring
> > the time part.
> >
> > How can I do this in JPA?
> >
> > Thanks,
> >
> >
> > Chris
Re: How to compare fields of type TemporalType.TIMESTAMP - but just
by Date?
Posted by Chris <cw...@gmail.com>.
In my last post, I asked, "How can I do this in JPA?", but I meant to
ask, "How can I do this in JPQL?".
I'm going to guess it's impossible because I'm not seeing any JPQL
date/datetime manipulation functions in the docs, other then
CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP, so I resorted to a
native SQL query using the syntax shown in my initial post.
Thanks,
Chris
On 9/12/2013 1:12 PM, Chris wrote:
> I have an entity with a timestamp field, but for the purpose of a
> certain query, I want to only compare by date - and ignore the time
> component of the value.
>
> For example, in Oracle, the raw SQL would look like:
>
> SELECT t0.BASE_DATA_ID, t0.CLIENT_ID, t0.DB_USER, t0.OS_USER,
> t0.UPSERT_DATE,
> t0.CRNCY_CODE, t0.datadate, t0.ident, t0.LAST_PUBLISHED_DATE,
> t0.PRICE_VAL_1, t0.PRICE_VAL_2, t0.TYPE_NAME
> FROM MD_BASE_DATA t0
> WHERE (t0.CLIENT_ID = 'JUNIT-1'
> and to_date(to_char(t0.UPSERT_DATE,'YYYY-MM-DD'),'YYYY-MM-DD')
> = to_date(to_char(current_date - 1,'YYYY-MM-DD'),'YYYY-MM-DD'))
>
> This query filters by field "UPSERT_DATE" being yesterday, ignoring
> the time part.
>
> How can I do this in JPA?
>
> Thanks,
>
>
> Chris
How to compare fields of type TemporalType.TIMESTAMP - but just by
Date?
Posted by Chris <cw...@gmail.com>.
I have an entity with a timestamp field, but for the purpose of a
certain query, I want to only compare by date - and ignore the time
component of the value.
For example, in Oracle, the raw SQL would look like:
SELECT t0.BASE_DATA_ID, t0.CLIENT_ID, t0.DB_USER, t0.OS_USER,
t0.UPSERT_DATE,
t0.CRNCY_CODE, t0.datadate, t0.ident, t0.LAST_PUBLISHED_DATE,
t0.PRICE_VAL_1, t0.PRICE_VAL_2, t0.TYPE_NAME
FROM MD_BASE_DATA t0
WHERE (t0.CLIENT_ID = 'JUNIT-1'
and to_date(to_char(t0.UPSERT_DATE,'YYYY-MM-DD'),'YYYY-MM-DD')
= to_date(to_char(current_date - 1,'YYYY-MM-DD'),'YYYY-MM-DD'))
This query filters by field "UPSERT_DATE" being yesterday, ignoring the
time part.
How can I do this in JPA?
Thanks,
Chris
Re: Using QueryCache, still seeing queries sent to the DB
Posted by Rick Curtis <cu...@gmail.com>.
>Is there any reason that should affect whether or not the query misses the
cache?
The only thing that comes to mind, is do you have .equals() and .hashCode()
implemented? Then again, I don't know if that is important or not.
For giggles, have you tried to change your query to be something like 'where
m._image.id = :image.id'?
On Tue, Sep 3, 2013 at 2:56 PM, tlianza <na...@lianza.org> wrote:
> tlianza wrote
> > SELECT m from ImageRepModel m where m._image = :image AND m._type = :type
>
> So, we've done quite a bit more troubleshooting here and found that the
> scenarios in which this query is *not* being cached is then the :image
> param
> that's passed in is a *detached* ImageModel object. Is there any reason
> that should affect whether or not the query misses the cache?
>
> Keep in mind the relationship here is such that this query only needs the
> ImageModel's id, which is both available in the detatched object and is
> marked as that object's primary key.
>
> Thanks,
> Tom
>
>
>
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/Using-QueryCache-still-seeing-queries-sent-to-the-DB-tp7584807p7584837.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>
--
*Rick Curtis*