Prev: Web Services in Java- Quick Start Tutorial
Next: Collection interfaces (Was: Creating a byte[] of long size)
From: gwoodhouse on 9 Jul 2010 06:40
Hello Everyone :)

I'm hoping you can help, I've been stuck on a project that uses
Hibernate to persist the objects in it, unfortunately the peice of
functionality i have to implement is to pull two Objects out of the
Database which are linked in a one to one relationship.

The project uses Annotations which i need to stick with yet all the
books seem to try and teach you in hbm files. Since the rest of the
hibernate code needs to only pull out a single object i have nothing
to refer to and its driving me mad trying to figure out what i need to
do.

What i'm trying to do corresponds to the sql statement:
SELECT sum(sessions), sum(searches), sum(turnaways) FROM counted,
product_session WHERE counted.session_id=product_session.session_id;

Here are my objects:
@Entity
@Table(name="product_session")
public class ProductSession implements Serializable {

private static final long serialVersionUID =
638116842380420385L;
public ProductSession(int session_id, String userid, String
date,
BSCCountedItem item)
{
this.session_id = session_id;
this.userid = userid;
this.date = date;
this.item = item;
}

public ProductSession()
{ }

@Id
private int session_id;

@Column
private String userid;
@Column
private String date;

@OneToOne
@JoinColumn (name = "session_id")
public CountedItem item;

@SuppressWarnings("deprecation")
public static ProductSession[] getProductSessions(Session
session,
CounterDate date, String userid)
{

Query query = session.createQuery("SELECT
ProductSession" +

"FROM ProductSession "+

"WHERE date <= :enddate AND date >= :startdate AND userid
= :userid");
query.setDate("startdate", date.getStartDate());
query.setDate("enddate", date.getEndDate());
query.setString("userid", userid);

return (ProductSession[]) query.list().toArray();
}
// Getters and Setters here

}

@Entity
@Table(name="counted")
public class CountedItem implements Serializable {

private static final long serialVersionUID =
-3473918695418644968L;
public CountedItem(int session_id, int sessions, int searches,
int
turnaways) { }

@Id
@GeneratedValue
@Column
private int session_id;

@Column
private int sessions;
@Column
private int searches;
@Column
private int turnaways;

// GETTERS & SETTERS //

}

Can you please tell me where i'm going wrong - i've had a really good
go at this and i'm still completely lost as to where i'm not doing
something right!

Thanks for any help!

Graeme
From: Jean-Baptiste Nizet on 9 Jul 2010 07:34
On 9 juil, 12:40, "gwoodho...(a)gmail.com" <gwoodho...(a)gmail.com> wrote:
<snip>
>
> What i'm trying to do corresponds to the sql statement:
> SELECT sum(sessions), sum(searches), sum(turnaways) FROM counted,
> product_session WHERE counted.session_id=product_session.session_id;
>

What have you tried and what didn't work? Your code doesn't show any
HQL query looking like the SQL query you're trying to implement with
Hibernate.
BTW, this SQL query looks strange to me. Let me rewrite it using joins
and aliases :

SELECT sum(counted.sessions), sum(counted.searches),
sum(counted.turnaways)
FROM counted
INNER JOIN product_session ON product_session.session_id =
counted.session_id;

What's the use of the join, since you don't retrieve any information
from the product_session table, and don't have any where clause on
product_session either?
From: gwoodhouse on 9 Jul 2010 08:18
On Jul 9, 12:34 pm, Jean-Baptiste Nizet <jni...(a)gmail.com> wrote:
> On 9 juil, 12:40, "gwoodho...(a)gmail.com" <gwoodho...(a)gmail.com> wrote:
> <snip>

> > What i'm trying to do corresponds to the sql statement:
> > SELECT sum(sessions), sum(searches), sum(turnaways) FROM counted,
> > product_session WHERE counted.session_id=product_session.session_id;
>
> What have you tried and what didn't work? Your code doesn't show any
> HQL query looking like the SQL query you're trying to implement with
> Hibernate.

My understanding is that my HQL will pull back the ProductSession
table, which contains a CountedItem. I've specified that session_id is
the coloumn where CountedItem is joined to the ProductSession table. I
thought that by bringing back ProductSession you would get all of the
related CountedItems (In this case its one-to-one)?

I could be totally and completely wrong. As i said i'm very new to
Hibernate and my book learning is only getting me so far.

> What's the use of the join, since you don't retrieve any information
> from the product_session table, and don't have any where clause on
> product_session either?

I've simplified the example above - What the query is doing is using
limiters that are only present in the ProductSession table to bring
back values located in the CountedItem table. The SQL would have had
"WHERE product_session.date > x" for instance (The HQL in the example
limiting by date is the only bit of HQL i've been able to steal from
other protions of the product that work).

From: gwoodhouse on 9 Jul 2010 08:18
On Jul 9, 12:34 pm, Jean-Baptiste Nizet <jni...(a)gmail.com> wrote:
> On 9 juil, 12:40, "gwoodho...(a)gmail.com" <gwoodho...(a)gmail.com> wrote:
> <snip>

> > What i'm trying to do corresponds to the sql statement:
> > SELECT sum(sessions), sum(searches), sum(turnaways) FROM counted,
> > product_session WHERE counted.session_id=product_session.session_id;
>
> What have you tried and what didn't work? Your code doesn't show any
> HQL query looking like the SQL query you're trying to implement with
> Hibernate.

My understanding is that my HQL will pull back the ProductSession
table, which contains a CountedItem. I've specified that session_id is
the coloumn where CountedItem is joined to the ProductSession table. I
thought that by bringing back ProductSession you would get all of the
related CountedItems (In this case its one-to-one)?

I could be totally and completely wrong. As i said i'm very new to
Hibernate and my book learning is only getting me so far.

> What's the use of the join, since you don't retrieve any information
> from the product_session table, and don't have any where clause on
> product_session either?

I've simplified the example above - What the query is doing is using
limiters that are only present in the ProductSession table to bring
back values located in the CountedItem table. The SQL would have had
"WHERE product_session.date > x" for instance (The HQL in the example
limiting by date is the only bit of HQL i've been able to steal from
other protions of the product that work).

From: gwoodhouse on 14 Jul 2010 11:25
On Jul 9, 1:18 pm, "gwoodho...(a)gmail.com" <gwoodho...(a)gmail.com>
wrote:
> On Jul 9, 12:34 pm, Jean-Baptiste Nizet <jni...(a)gmail.com> wrote:
>
> > On 9 juil, 12:40, "gwoodho...(a)gmail.com" <gwoodho...(a)gmail.com> wrote:
> > <snip>
> > > What i'm trying to do corresponds to the sql statement:
> > > SELECT sum(sessions), sum(searches), sum(turnaways) FROM counted,
> > > product_session WHERE counted.session_id=product_session.session_id;
>
> > What have you tried and what didn't work? Your code doesn't show any
> > HQL query looking like the SQL query you're trying to implement with
> > Hibernate.
>
> My understanding is that my HQL will pull back the ProductSession
> table, which contains a CountedItem. I've specified that session_id is
> the coloumn where CountedItem is joined to the ProductSession table. I
> thought that by bringing back ProductSession you would get all of the
> related CountedItems (In this case its one-to-one)?
>
> I could be totally and completely wrong. As i said i'm very new to
> Hibernate and my book learning is only getting me so far.
>
> > What's the use of the join, since you don't retrieve any information
> > from the product_session table, and don't have any where clause on
> > product_session either?
>
> I've simplified the example above - What the query is doing is using
> limiters that are only present in the ProductSession table to bring
> back values located in the CountedItem table. The SQL would have had
> "WHERE product_session.date > x" for instance (The HQL in the example
> limiting by date is the only bit of HQL i've been able to steal from
> other protions of the product that work).

It seems my problem was the "SELECT" statement. Using just "from"
works fine.

Thanks for the help!

Graeme
 | 
Pages: 1
Prev: Web Services in Java- Quick Start Tutorial
Next: Collection interfaces (Was: Creating a byte[] of long size)