openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Boblitz John <>
Subject Problem retrieving a row from the db with a composite key with a date as a part of the key
Date Wed, 29 Jun 2011 09:44:07 GMT
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!




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.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,

	if (dltObj != null) {

*** 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

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

             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;
 private Date fromDate;
 private short modifier;
 private double rate;
 @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
  CONSTRAINT fk_currency_of_exchange_rate_tocurrency FOREIGN KEY (tocurrency)
      REFERENCES galaxy11.currency (uniqueid) MATCH SIMPLE
  CONSTRAINT uq_exchangrate_type_from_to_begin UNIQUE (ratetype, fromcurrency, tocurrency,
  CONSTRAINT ct_exchangerate_differentcurrencies CHECK (fromcurrency <> tocurrency)

View raw message