-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 9 posts ] 
  Previous topic | Next topic 
Author Message
nweddle
 Post subject: Newest 500 records, instead of the first 500.
PostPosted: Fri Jul 23, 2004 12:56 pm 
Beginner
Beginner

Joined: Mon Dec 01, 2003 8:48 pm
Posts: 47
Location: Texas, United States
I am using v. 2.0.1 with Oracle 9i.

I am using setMaxResults(500), which works fine until I get more than 500 records.

My database has more than 1 millon records.

When I execute my query, I get the first 500 records in the database. How can get the newest 500 records in one query?

Executing two queries is not an option for me. I tried to use SQL queries instead of HQL queries, but I am running into a problem using those as well. I need to stick to HQL. I don't have time to do manual JDBC either.

Thanks for your help!
Top
 Profile  
 
gus
 Post subject:
PostPosted: Fri Jul 23, 2004 1:36 pm 
Beginner
Beginner

Joined: Thu Jun 24, 2004 1:04 pm
Posts: 35
Location: Minnesota - USA
You must define 'newest' in terms of your HQL/SQL. For example, if you have a createDate property in your database:

Code:
from MyTable table order by table.createDate desc


Otherwise, you get them back in the database's order du jour. ;)

--gus
Top
 Profile  
 
nweddle
 Post subject:
PostPosted: Fri Jul 23, 2004 1:49 pm 
Beginner
Beginner

Joined: Mon Dec 01, 2003 8:48 pm
Posts: 47
Location: Texas, United States
Sorry, I should be more clear.

Here's my HQL query:

from CommonLogEvent cle
where cle.componentIdentification.applicationName = 'blah'
order by cle.observedTime desc

This produces Oracle SQL that selects the first 500 records, and then orders them by observedTime.
Top
 Profile  
 
gus
 Post subject:
PostPosted: Fri Jul 23, 2004 2:09 pm 
Beginner
Beginner

Joined: Thu Jun 24, 2004 1:04 pm
Posts: 35
Location: Minnesota - USA
Is the order by in the SQL Hibernate fires? If not, that surprises me I guess, not what I would expect. But I'm relatively new to this.

Or are you saying the generated SQL contains a result limiter annotation? Looking at the code for Oracle9Dialect, I'm now almost positive that's what's happening. You might need to tweak it for your use. Depending if the SQL the getLimitString() is given has the 'order by' in it already, you may not be able to do it make it work. Fun! ;)

Good luck.

--gus
Top
 Profile  
 
michael
 Post subject:
PostPosted: Fri Jul 23, 2004 2:49 pm 
Hibernate Team
Hibernate Team

Joined: Tue Sep 09, 2003 2:10 pm
Posts: 3246
Location: Passau, Germany
Use a HQL query with an order by on the create date, and then use .setMaxResults()
Top
 Profile  
 
nweddle
 Post subject:
PostPosted: Fri Jul 23, 2004 2:54 pm 
Beginner
Beginner

Joined: Mon Dec 01, 2003 8:48 pm
Posts: 47
Location: Texas, United States
I think that is what I have done.

Here's my Java code. See my HQL in previous post:

Code:
  Session session = null;
        List result = null;
        try {
            session = sessionFactory.openSession();

            Query q = session.createQuery(query);
            q.setMaxResults(maxResults);
            result = q.list();
        } catch (HibernateException ex) {
            ex.printStackTrace();
        } finally {
            close(session);
        }
        return result;
    }
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Fri Jul 23, 2004 3:06 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
we can't help you if you don't show sql
Top
 Profile  
 
nweddle
 Post subject:
PostPosted: Fri Jul 23, 2004 3:12 pm 
Beginner
Beginner

Joined: Mon Dec 01, 2003 8:48 pm
Posts: 47
Location: Texas, United States
BTW, this Oracle SQL does the trick. I can't get this to work in createSQLQuery() for all my queries because I don't know how I can join two tables using createSQLQuery().

Code:
SELECT * FROM (SELECT {cle.*} FROM ul_common_log_event {cle} where {cle.application_name} = 'blah' order by {cle}.observed_time desc) WHERE ROWNUM <= 500


If somehow I can do something like this, I would be in good shape.

My cle object contains a Map that I need to get data from. So I need to join ul_common_log_event(cle) to ul_correlation_data with a SQL query, but I don't see a way to use the aliases correctly.

1. I have one mapping class for three tables.
2. I need to join one table with another using SQL queries

Is it possible to join two tables that have the same mapping object?
Top
 Profile  
 
nweddle
 Post subject:
PostPosted: Fri Jul 23, 2004 3:21 pm 
Beginner
Beginner

Joined: Mon Dec 01, 2003 8:48 pm
Posts: 47
Location: Texas, United States
Here is my SQL Query with the corresponding Oracle SQL:

Code:
SELECT * FROM (SELECT {cle.*} FROM ul_common_log_event {cle} WHERE {cle}.component_name = 'Attribute Invoked Logging: IQuoteService, SearchForQuoteAttachments'  ORDER BY {cle}.observed_time DESC) WHERE ROWNUM <= 500


Hibernate:
Code:
SELECT * FROM (SELECT cle.cle_id as cle_id0_, cle.observed_time as observed2_0_, cle.version as version0_, cle.situation as situation0_, cle.message as message0_, cle.status as status0_, cle.severity as severity0_, cle.log_level as log_level0_, cle.address_type as address_9_0_, cle.address_name as address10_0_, cle.component_name as compone11_0_, cle.application_name as applica12_0_, cle.instance_name as instanc13_0_, cle.thread_name as thread_14_0_, cle.environment_name as environ15_0_ FROM ul_common_log_event cle WHERE cle.component_name = 'Attribute Invoked Logging? IQuoteService, SearchForQuoteAttachments'  ORDER BY cle.observed_time DESC) WHERE ROWNUM <= 500


This SQL does the trick for one of my queries ... BUT...

I have one mapping class. My object contains two Maps, and another custom object. I need to query the data from one of the Maps using createSQLQuery().

Code:
session = sessionFactory.openSession();
            Query q = session.createSQLQuery(query, "cle", CommonLogEvent.class);
            result = q.list();


Can I query two tables that are part of the same object this way? I realize that I can have arrays of aliases and classes, but I only have one class.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 9 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.