Hibernate SQLQuery Interface Addscalar method (native SQL query)

in the normal development of hibernate provided by HQL Basic can meet our daily needs. However, in some special cases, you still need to use native SQL, and you want the results of SQL queries to be bound to Pojo. The Createsqlquery and CreateQuery interfaces in the Hibernate API. as in this project, there is a new requirement for the table structure to change: to query the very individual fields from a table and a few more of the data, we've been using the Hibernate API before, using mapped objects. the original object and table structure is this:

@Entity @table (name= "T_bill_acct_item") @NamedQuery (name= "Tbillacctitem.findall", query= "select T from Tbillacctitem T ") public class Tbillacctitem implements Serializable {private static final long serialversionuid = 1L; @Id @column (name=" A cct_item_id ") Private long Acctitemid; @Column (name=" acct_id ") private long Acctid; @Column (name=" billing_cycle_id ") private string Billingcycleid; @Column (name= "product_id") private string productId; @Column (name= "service_id") private string serviceId; @Column (name= "Acct_item_code") private String Acctitemcode; @Column (name= "Original_amount") private int originalamount; @Column (name= "Cdr_discount") private int cdrdiscount; @Column (name= "Acct_discount") private int Acctdiscount; @Column (name= "Rece_amount") private int receamount; @Column (name= "Real_amount") private int realamount;@ Column (name= "Charge_off_source") private int chargeoffsource; @Column (name= "state") private int state;

But the information that I need to query now is such a Pojo class:

public class Productbillinfo implements serializable{private static final long serialversionuid = 1l;private String produc tid;//Product Identification private String serverid;//service flag private int realamount;//paid amount (sum sum) private int receamount;// Receivable amount (sum sum) private int state;//account status private String billingcycle;//account period (month)

Becausehibernate the object to query is to be mapped with the table structure one by one, now the sum field these are not mapped,
There is a problem with the previous method. Looked for a lot of hibernate APIs to find this thing.as follows：

approximate use of the process:

        StringBuffer B = new StringBuffer ();  //... Omit SQL concatenation code  String sql = b.tostring ();  SQLQuery query = session.createsqlquery (SQL); Query.addscalar ("ProductId", standardbasictypes.string);//fields to be queried, type list<productbillinfo> lnfo =query.list ();

Here is the implementation of the query Pojo method I used:

@SuppressWarnings ("unchecked") public list<productbillinfo> findbyacctidandcycle (Long acctid,string Cyclebegin, String cycleend, int state) {list<string> cycles = dateutil.getallcycle (Cyclebegin, cycleend); StringBuffer buf = new StringBuffer () buf.append ("Select t.product_id as ProductId, t.service_id as ServiceId," + "sum (t . Real_amount) as Realamount, sum (t.rece_amount) as Receamount, "+" t.state as state, t.billing_cycle_id as Billingcycle Id "+" from T_bill_acct_item T where t.state= ' "+ state +" ' and t.acct_id= ' "+ Acctid +" ' and t.billing_cycle_id in (" );   StringBuffer bf = new StringBuffer (); for (String id:cycles) {if (bf.tostring (). Equals ("")) {bf.append ("'" +id+ "'");   } else{Bf.append ("," + "'" "+id+" "); }}buf.append (Bf.tostring ()); Buf.append (") GROUP by t.product_id,t.billing_cycle_id,t.service_id"); System.out.printf (Buf.tostring ());  SQLQuery query = GetSession (). Createsqlquery (Buf.tostring ()); Query.addscalar ("ProductId", standardbasictypes.string); querY.addscalar ("ServiceId", standardbasictypes.string); Query.addscalar ("Realamount", Standardbasictypes.integer); Query.addscalar ("Receamount", Standardbasictypes.integer); Query.addscalar ("state", Standardbasictypes.integer); Query.addscalar ("Billingcycleid", standardbasictypes.string); List<productbillinfo> lnfo =query.list (); return lnfo;}

the point to note isQuery.addscalar ("DeviceId", hibernate.string); , the old version of the data used is the type of the Org.hibernate.type package below,
The new version is in the following: Org.hibernate.type.StandardBasicTypes package
and in the syntax to use the In (",", ") syntax, so wrote the following a small stitching method:

public static void Main (string[] args) {list<string> C = new arraylist<string> () C.add ("1"); C.add ("2"); C.add ("3"); StringBuffer bf = new StringBuffer (); for (String id:c) {   if (bf.tostring (). Equals ("")) {   bf.append ("'" +id+ "'");   } else{       bf.append ("," + "'" "+id+" ");}   } System.out.println (Bf.tostring ());}

The printing results are: ' 1 ', ' 2 ', ' 3 'Usestringbuffer stitching into native SQL statements

Hibernate SQLQuery Interface Addscalar method (native SQL query)