You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Boblitz John <Jo...@BERTSCHI.com> on 2011/06/29 11:44:07 UTC
Problem retrieving a row from the db with a composite key with a date as a part of the key
Good Morning,
I am having a problem retrieving a row from the database via a composite key, where one of the values
is declared as a date.
I am using postgres and the field is an sql date field
When I persist the Object, the date is set to '2011-06-29' in the DB. When I retrieve the row via it's primary key,
the attribute has the java value '2011-06-29T00:00:00.000+0200'
In my test, I set the Date in Java to '2011-06-29T00:00:00.000+0200' (using the standard stripping methods in Calendar)
but the query fails to return the row.
Below is all of the relevant code. Can anyone spot my error?
Thanks in advance for an assistence!

John
----
Who is General Failure, and why is he reading my hard disk?
** Junit Test Class (relevant portions)
private static String[] keyField = {"rateType", "toCurrency" , "fromCurrency", "fromDate"};
private static Object[] keyValue = { "mj", "ref to curr", "ref to curr" , "ref to date"};
private static void removeTestRow() {
Currency curr1 = mPersistenceManager.findByUniqueId(Currency.class, (long) 1);
Currency curr2 = mPersistenceManager.findByUniqueId(Currency.class, (long) 2);
Date now = new Date(System.currentTimeMillis());
Calendar cal = Calendar.getInstance();
cal.setTime(now);
cal.set(Calendar.HOUR_OF_DAY, 0);
cal.set(Calendar.MINUTE, 0);
cal.set(Calendar.SECOND, 0);
cal.set(Calendar.MILLISECOND, 0);
Date date = new Date(cal.getTimeInMillis());
keyValue[1] = curr1;
keyValue[2] = curr2;
keyValue[3] = date;
ExchangeRate dltObj = mPersistenceManager.findByAltKey(
"getExchangeRateByAltKey", keyField, keyValue,
ExchangeRate.class);
if (dltObj != null) {
mPersistenceManager.delete(dltObj);
}
}
*** Postgres DB row
uid;ratetype;fromcurrency;tocurrency;fromdate;modifier;rate;junkfields ....
12601;"mj";1;2;"2011-06-29";1000;1.54000;TRUE;"2011-06-29 09:03:33.92";4711;"";0;1
** Persisitence Manager Class with query interface
@Override
public <T> T findByAltKey(String pQuery, String[] pKeyName,
Object[] pKeyValue, Class<T> pType) {
return findObjectWithNamedQuery(pQuery, pKeyName, pKeyValue, pType);
}
private <T> T findObjectWithNamedQuery(String pNamedQuery, String[] pKeyName,
Object[] pKeyValue, Class<T> pType) {
try {
TypedQuery<T> query = this.em.createNamedQuery(
pNamedQuery, pType);
for (int i = 0; i< pKeyName.length; i++){
query.setParameter(pKeyName[i], pKeyValue[i]);
}
return query.getSingleResult();
} catch (NoResultException pEx) {
mTrc.println(TraceLevel.HIGH, "Found no results", pEx);
return null;
}
}
** Persistent Class
@Entity
@Table(schema="galaxy11",
name="exchangerate",
uniqueConstraints={
@UniqueConstraint(name="uq_exchangerate_type_to_from_begin",
columnNames ={"ratetype", "tocurrency" , "fromcurrency", "fromdate"})}
)
@NamedQuery(name = "getExchangeRateByAltKey",
query = "select o from ExchangeRate o where" +
" o.rateType = :rateType and " +
" o.fromCurrency = :fromCurrency and " +
" o.toCurrency = :toCurrency and " +
" o.fromDate = :fromDate"
)
public class ExchangeRate extends BaseEntity {
@ManyToOne(fetch=FetchType.LAZY, cascade=CascadeType.MERGE)
@JoinColumn(name="fromcurrency", columnDefinition="int8", nullable=false)
private Currency fromCurrency;
@ManyToOne(fetch=FetchType.LAZY, cascade=CascadeType.MERGE)
@JoinColumn(name="tocurrency", columnDefinition="int8", nullable=false)
private Currency toCurrency;
@Basic
@Column(nullable=false)
private Date fromDate;
@Basic
@Column(columnDefinition="int2")
private short modifier;
@Basic
private double rate;
@Basic
@Column(columnDefinition="bpchar", nullable=false, length=2)
private String rateType;
public ExchangeRate () {
}
** ommitted the standard getters & setters
}
*** Table Definition in Postgres
CREATE TABLE galaxy11.exchangerate
(
uniqueid bigint NOT NULL,
ratetype character(2) NOT NULL,
fromcurrency bigint NOT NULL,
tocurrency bigint NOT NULL,
fromdate date NOT NULL,
modifier smallint NOT NULL DEFAULT 1,
rate numeric(10,5) NOT NULL,
active boolean,
created timestamp without time zone,
createdby bigint,
modified timestamp without time zone,
modifiedby bigint,
"version" bigint,
CONSTRAINT pk_exchangerate PRIMARY KEY (uniqueid),
CONSTRAINT fk_currency_of_exchange_rate_fromcurrency FOREIGN KEY (fromcurrency)
REFERENCES galaxy11.currency (uniqueid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fk_currency_of_exchange_rate_tocurrency FOREIGN KEY (tocurrency)
REFERENCES galaxy11.currency (uniqueid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT uq_exchangrate_type_from_to_begin UNIQUE (ratetype, fromcurrency, tocurrency, fromdate),
CONSTRAINT ct_exchangerate_differentcurrencies CHECK (fromcurrency <> tocurrency)
)
AW: Problem retrieving a row from the db with a composite key with a date as a part of the key
Posted by Boblitz John <Jo...@BERTSCHI.com>.
Thanks for the response Jim,
I forgot to mention that I had used the Temporal annotation at one point and that it did not work in that case either.
To be sure, I readded and tested again with the same result.
As a side note, I am usinf the java.sql.Date instead of java.util!
Lastly, the timezone can't be the problem as both the client I am on, and the server are on the same zone and synch'd.
Further, I write out timestamps to the server to log creation and modification and these are correct, although truncated :( and
afaik, postgres does not store that for a date type anyway ...
Cheers!
> -----Ursprüngliche Nachricht-----
> Von: Jim O'Rourke [mailto:jimorourke77@gmail.com]
> Gesendet: Mittwoch, 29. Juni 2011 16:59
> An: users@openjpa.apache.org
> Betreff: Re: Problem retrieving a row from the db with a
> composite key with a date as a part of the key
>
> Could it be a timezone difference between the jvm and
> Postgres server?
>
> Might also be that your persistent property needs to be annotated with
> @Temporal(javax.persistence.DATE) to match the sql column
> declaration.
>
>
>
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/Problem-retrieving-a-row-f
> rom-the-db-with-a-composite-key-with-a-date-as-a-part-of-the-k
> ey-tp6528286p6529300.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>
Re: Problem retrieving a row from the db with a composite key with
a date as a part of the key
Posted by Jim O'Rourke <ji...@gmail.com>.
Could it be a timezone difference between the jvm and Postgres server?
Might also be that your persistent property needs to be annotated with
@Temporal(javax.persistence.DATE) to match the sql column declaration.
--
View this message in context: http://openjpa.208410.n2.nabble.com/Problem-retrieving-a-row-from-the-db-with-a-composite-key-with-a-date-as-a-part-of-the-key-tp6528286p6529300.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.