CODEWITHSUNDEEP
javahibernatehql
Alexandru Severin
Alexandru Severin

Reputation: 6238

Select entity from two tables in hibernate

I have two tables like this:

+------------+             +------------+ 
| Parent     |             | Child      | 
+------------+             +------------+ 
|child_id    |------------>|id          | 
|id          |             |date        | 
|name        |             +------------+ 
+------------+

I need to extract objects of type Parent depending on date

This is my query:

Query query = session.createQuery(
      "SELECT p.child_id, p.id, p.name from Parent p, Child c 
       WHERE p.child_id = c.id
       AND c.date between :date and CURRENT_DATE ");

query.setTimestamp("date",new Date(2012,1,1));

List<Parent> objList = query.list()     // List<Object[]> instead of List<Parent>

I've been trying for hours already and I couldn't get any closer then this. Can you tell me what is wrong with my query or how can I achieve my goal?

EDIT: I found a problem: new Date(2012,1,1) was creating a date of year 3092, I fixed it by adding the date through java.sql.Date

The result is an List<Object[]>, I know I could iterate each objects vector, and create a new Parent with values from objects, but isn't there a way I can get directly a Parent object from query list?

Upvotes: 0

Views: 853

Answers (3)

Alexandru Severin
Alexandru Severin

Reputation: 6238

Turns out there were two problems and this is the solution to each:

  1. Date problem

java.util.Date(int year, int month, int day) should be avoided as it turns out it messes up the date. You can solve this problem by using java.util.Calendar and set the date to it.

Then you need to convert it to java.sql.Date to match the query syntax

  1. Query the list of entitys and not object[]

I managed to get a List directly from the query (without resorting to manually iterating the list of Objects[] and creating Parent entities myself) using this query:

Query query = session.createSQLQuery("select p.* from myscheme.Parent p, myscheme.Child c
     WHERE p.child_id = c.id AND c.date > :date ")
    .addEntity(Parent.class);
query.setTimestamp("date",sqlDate);

Upvotes: 0

user1134181
user1134181

Reputation:

Time is cut off if you use this call:

query.setDate(...);

Instead, use the following call:

query.setTimestamp(...)

Try also to use Criteria Queries

Such as:

criteria.add(Expression.eq("date", new Date()));

If the query works without date restrictions, you can make some experiments by comparing the dates. For example, as follows:

...
Date customDate = new Date(2012, 1, 1);
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");    

StringBuilder query = new StringBuilder("from Child c");
query.append(" where c.date >= '" + simpleDateFormat.format(customDate) + "'");

Query result = session.createQuery(query.toString());
List resultList = result.list();
...

Upvotes: 1

Philipp Sander
Philipp Sander

Reputation: 10249

query.setParameter("date",new Date(2012,1,1));

use the setParameter method instead of setDate

Upvotes: 0

Related Questions