Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:


Hi this is edward.

I am using Hibernate with Struts. And I also using DisplayTags for Display sorting and pagination.

My Doubt is what is the difference between
Query and CreateSQLQuery in Hibernate

Using this query 1.Query query = session.CreateQuery("from AuditFinding") , Using Dispaly Tags I have done Sorting, and Pagination Using the Query list.

But Using the below Query
2.SQLQuery query = session.createSQLQuery("select id,shipname,shipcode,fromdate,todate,auditfindingstitle,status,assignedto,priority,updatedby,updateddate from Auditfinding af");
I was not able to Sorting and Pagination.

Using CreateSQLQuery in hibernate, here all the values are stored in object format.
How can i sort and pagination using displaytags.

Thank You

CreateSQLQuery is typically for executing nativel DB SQL. In cases where HQL does not support the exact functionality that is requried, you will want to use a Native SQL.

When using Query interface and setting the number of rows and the starting row, since the SQL is generated by Hiberate it in addition adds DB Specific SQLclauses that will help in acheiving this pagination functionality. This is not the case with createSQLQuery.

If there's no DB specific clauses that you are using, use the createQuery with a valid HQL instead of createSQL Query.

Could You tell any Other Solutions for pagination/sorting Using createSQLQuery.


Or if i want to get the particular fields in the database, Is it possible to use createQuery? if yes tell me.

Thanks

For query 1 [.createQuery(...).list()], the result is a List of AuditFinding objects.
From the limited information you provided, query 2 [createSQLQuery(...).list()] would return a List of Object[].

Is your issue with paging and sorting have to do with the difference in the results?

.createQuery(...) can also be used in the form "SELECT a.prop1, a.prop2 FROM Object1 a" -- each result will be a Object[]
OR the form "SELECT new MyNonPersistentObj(a.prop1, a.prop2) FROM Object1 a" -- each result will be an instance of MyNonPersistentObj

Hope this helps,
Curtis ...

Hi,

Thanks for your reply.

If I am using "from AuditFindings" means I can get all the records in list. and using display tags, I can sorting and pagination is working well.

If I get the particualr fields using "select id,shipname,shipcode from AuditFindings" means.I can get all the records using list. but each consider as a objects.

So i can't able to apply display tags. I don't know the exact problem when i select the particualr fields in table.

But in "from AuditFindings" Query, I have no problem to apply display tags for sorting and pagination.

Please tell me the problem. And how to solve this probs.

Thanks for your reply

A.Edward Duari

Edward,

Use following feature of Hibernate of using normal java beans in HQL, i mean to say usng createQuery()

select new ItemRow( item.id, item.description, bid.amount )
from Item item join item.bids bid
where bid.amount > 100

Assuming that the ItemRow class has an appropriate constructor (you have to write
that class), this query returns newly instantiated (transient) instances of ItemRow,
as you can see in the next example:
Iterator i = session.createQuery(
"select new ItemRow( item.id, item.description, bid.amount ) " +
"from Item item join item.bids bid " +
"where bid.amount > 100"
)
.list()
.iterator();
while ( i.hasNext() ) {
ItemRow row = (ItemRow) i.next();


this snippet is taken from Hibernate bible, "Hibernate in action" page 271

for your pagination thing here is expln taken from same book, page 244

Query query =
session.createQuery("from User u order by u.name asc");
query.setFirstResult(0);
query.setMaxResults(10);
The call to setMaxResults(10) limits the query result set to the first 10 objects
selected by the database. In this criteria query, the requested page starts in the
“middle” of the result set:


Hope these both e.g gives you soln u r looking for........I would suggest, go through Hibernte in Action book first for any of the queries you have

sudhir

Actually I want to use Display Tags for sorting and paging.

if my hibernate query is like

1.Query query = session.createQuery("from Auditfinding af ");

System.out.println("select =>"+usrlist.toString());
==========================================
the output is:
==========================================
select =>[com.lib.Auditfinding@276, com.lib.Auditfinding@277,
com.lib.Auditfinding@278, com.lib.Auditfinding@279]
==========================================

Using the above list i can use display tag. sorting and pagination working fine.

But

2. Query query = session.createQuery("select af.id,af.shipname from Auditfinding af ");
System.out.println("select =>"+usrlist.toString());
==========================================
the output is:
==========================================
select =>[[Ljava.lang.Object;@3c0007, [Ljava.lang.Object;@125fefa, [Ljava.lang.Object;@186df0f, [Ljava.lang.Object;@19e8f17]


Please see above, Why the second query are like in object storage, when i select particualr fields in one Class/Table.

Why....??? Pls describe.

How to change the second query object formate to first query list formate.

When you select specific columns from a Persistent Class, hibernate will return it as a Array of Objects (called a Tuple I think).

You can use this syntax if you want Hibernate to return Objects of a specific class instead:



Of course it's assumed that you have such a constructor. The class that follows new need not be a Persistent Class (i.e. does not need a mapping XML etc.). So you could do something like:

code]select new DisplayRow(af.id,af.shipname) from Auditfinding af [/code]

Once this is done, the list will have a list of "DisplayRow" objects instead of a 2d array of Objects.