i am new guy of hibernate ,and i want to change this sql to HQL, thanks.can somebady can write the HQL to me, thanks
the sql likes:

I dont know if you knew this, but you can run a native SQL query so this will run and return hibernate objects (if mapped correctly). You might need to add {} brackets, check out native queries.

If you already knew that, and wanted HQL, then nevermind. :)

Chris

Assuming that DB looks like this:
drop table b1;
drop table a1;

create table a1(
APPLICATIONSERIALNO varchar(20),
CONFIRMEDDATETIME varchar(20),
SENDEDDATETIME varchar(20),
nextconfirmseq varchar(20),
STATUS int
);


create table b1(
CONFIRMATIONSEQ varchar(20),
APPLICATIONSERIALNO varchar(20),
CONFIRMERID int,
UNSEALEDFLAG int,
CONFIRMATIONNO int,
STATUS int
);


insert into a1 values ( '123','10-22-2003 10:56', '10-11-2003 10:56','10', 25);
insert into a1 values ( '124',null, '10-12-2003 10:56', '11', 25);
insert into b1 values ('10', '123',1,2,3,4 );
insert into b1 values ('11', '124',1,2,3,4 );

Classes and mappings like those:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping package="ft945689">

<class table="a1" name="A1">
<id name="applicationserialno" access="field">
<generator class="assigned"/>
</id>
<property name="confirmeddatetime" access="field"/>
<property name="sendeddatetime" access="field"/>
<property name="nextconfirmseq" access="field"/>
<property name="status" access="field"/>
</class>

<class table="b1" name="B1">
<composite-id>
<key-property name="applicationserialno" access="field"/>
<key-property name="confirmationseq" access="field"/>
</composite-id>
<property name="confirmerId" access="field"/>
<property name="unsealedFlag" access="field"/>
<property name="confirmationNo" access="field"/>
<property name="status" access="field"/>
</class>

</hibernate-mapping>

==============
public class A1 {
public String applicationserialno;
public String confirmeddatetime;
public String sendeddatetime;
public String nextconfirmseq;
public int status;

//not exactly the logic of SQL for clarity
public String getNullDependentData(){
if( confirmeddatetime == null ){
return sendeddatetime;//do subtraction here
}
return confirmeddatetime;//do subtraction here
}
}

==============

public class B1 implements Serializable {
public String applicationserialno;
public String confirmationseq;
public int confirmerId;
public int unsealedFlag;
public int confirmationNo;
public int status;



public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;

final B1 b1 = (B1) o;

if (applicationserialno != null ? !applicationserialno.equals(b1.applicationserialno) : b1.applicationserialno != null) return false;
if (confirmationseq != null ? !confirmationseq.equals(b1.confirmationseq) : b1.confirmationseq != null) return false;

return true;
}

public int hashCode() {
int result;
result = (applicationserialno != null ? applicationserialno.hashCode() : 0);
result = 29 * result + (confirmationseq != null ? confirmationseq.hashCode() : 0);
return result;
}
}

================

The HQL could look like this: "select a, b from A1 a, B1 b " +
" where a.nextconfirmseq = b.id.confirmationseq and " +
"a.applicationserialno = b.id.applicationserialno " +
"and b.status != 7" // extend this for your statuses

Also note that former SQL MSSQL specific logic now is supposed to be implemented in A1 class

thanks for your help. but i don't know what's mean of 'You might need to add {} brackets, check out native queries". can your give me a example.
thanks very much

http://www.hibernate.org/hib_docs/v3/re ... -nativesql

I think the majority of Hibernate people like HQL because it is Object Oriented, less verbose, based on Java fields and not DB columns, etc. I liked it at first, but after hitting the wall trying to convert complex SQL to HQL I decided to stick with only native SQL and I think my productivity has improved, performance of queries has remained high, and ease of transitioning support of systems to people who dont know the ins and outs of hibernate/HQL has improved. Why learn another proprietary language if you do not have to?

Regards,
Chris

Because:
- it handles abstractness of you DB
- it handles more concepts eg polymorphism
- it's less verbose
- it's no longer proprietary (it's EJB3QL)
- etc...