Using native SQL statements in Hibernate

Use native SQL queries must be aware that the program must select all data columns before it can be converted to a persisted entity.
Suppose the entity has a <many-to-one when mapping: The/> Association points to another entity, the <many-to-one must be returned in the SQL query. /> The foreign key column of the map, otherwise it will cause the "column not found" exception to be thrown. The simplest approach is to use a star (*) in the SQL string to indicate that all columns are returned.
Arguments are supported in native SQL statements, which can use the question mark argument (?), or use the name parameter.

Example 1: Name parameter
publicvoid  entityquery () {    seesion session=hibernateutil.currentsession ();    Transaction tx=session.begintransaction ();    String sqlString= "SELECT * from strudent Where year =:year";    List l=session.createsqlquery (sqlString)            . Addentity (strudent. class )            . Setparameter ("Year")            . List ();    Tx.commit ();    Hibernateutil.closesession ();}

 Example 2: Question mark (?) Parameters
Public voidListinstalledorun () {Try{StringBuilder MySQL=NewStringBuilder (); Mysql.append ("Select t.*,c.*,p.* from (SELECT * to Vrv_paw_reportlog WHERE TYPE in (1001,1002) GROUP by Pcinfoid ORDER by Time DESC) "
+ "T INNER join Vrv_paw_cloudaccount C on t.cloudaccountid=c.id INNER join Vrv_paw_pcinfo p on t.pcinfoid=p.id WHERE 1=1"); List<Object> getparamlist =NewArraylist<object>();            Createsqlwherecondition (MySQL, getparamlist); Griddata<ReportLog> Griddata =Reportlogservice.getpageview (mysql.tostring (), Getparamlist, Getpagenum (), getpagesize ());        Print (Actionutil.jsonobj (griddata)); } Catch(Exception e) {log.info ("Get list Failed");        E.printstacktrace (); }}Private voidCreatesqlwherecondition (StringBuilder MySQL, list<object>getparamlist) {        if(Stringutils.isnotblank (userName)) { mysql.append ( "and c.name like?"); Getparamlist.add ("%" + userName + "%"); }        if(Stringutils.isnotblank (IP)) {mysql.append ("And p.ip like?"); Getparamlist.add ("%" + IP + "%"); }        if(Stringutils.isnotblank (Mac)) {Mysql.append ("And P.mac like?"); Getparamlist.add ("%" + Mac + "%"); }        if(Stringutils.isnotblank (version)) {Mysql.append ("And VERSION like?"); Getparamlist.add ("%" + version + "%"); }        if(Stringutils.isnotblank (type)) { mysql.append ( "and type=?");        Getparamlist.add (type); }        if(Stringutils.isnotblank (startTime)) { mysql.append ( "and Time >=?");        Getparamlist.add (startTime); }        if(Stringutils.isnotblank (endTime)) {Mysql.append ("And Time <=?");        Getparamlist.add (EndTime); }    }

method Getpageview:
@SuppressWarnings ("Unchecked")
@Override
Public griddata<reportlog> Getpageview (String sql, list<object> getparamlist, int pagenum, int pageSize) {
list<object> paramlist = getparamlist;

First, the total number of query records
A, generate a query object that queries RecordCount, and set the parameters
Query countquery = This.getsession (). createsqlquery (SQL). addentity (Reportlog.class);
for (int i = 0; i < paramlist.size (); i++) {
Countquery.setparameter (i, Paramlist.get (i));
}

b, check out the total number of records
int recordCount = Countquery.list (). Size ();

Calculate Total Pages
int maxpagenum = (recordCount + pageSize-1)/pageSize;
if (Pagenum > Maxpagenum)
Pagenum = Maxpagenum;

List<reportlog> recordlist = null;
if (RecordCount > 0) {
// ===============
Second, query the data list of the specified page
A, generate a query object that queries Recordlist, and set the parameters
Countquery.setfirstresult ((pageNum-1) * pageSize);
Countquery.setmaxresults (pageSize);

b, query the data list of the current page
Recordlist = Countquery.list ();
}

return new Griddata<reportlog> (RecordCount, recordlist);
}

 PackageCom.vrv.paw.bean;Importjava.io.Serializable;Importjava.util.List;/*** JavaBean: A grid representing the UI*/ Public classGriddata<t>ImplementsSerializable {Private Static Final LongSerialversionuid = 1L; /**Total number of rows **/    Private intTotal ; /**Row Data **/    PrivateList<t>rows;  PublicGriddata () {} PublicGriddata (intTotal, list<t>rows) {         This. Total =Total ;  This. rows =rows; }         Public intgettotal () {returnTotal ; }     Public voidSettotal (intTotal ) {         This. Total =Total ; }     PublicList<t>getRows () {returnrows; }     Public voidSetrows (list<t>rows) {         This. rows =rows; }    }

Moreover, if multiple table joins are displayed in the SQL statement, the SQL statement can select data from multiple data tables. Hibernate also supports the conversion of query results into multiple entities. If you want to convert the query results to multiple entities, you should specify different aliases for different data tables in the SQL string and call the Addentity (String alias,class Entityclass) method to convert different data tables to different entities.

 Public void Multientityquery () {    session session=hibernateutil.currentsession ();    Transaction tx=session.begintransaction ();    String SQL= "Select s.*,e.*,c.* from student s,enrolment e,course C Where s.id=e.id and E.code=c.code";    List l=session.createsqlquery (sql)            . Addentity ("s", Student.  Class)            . Addentity ("E", enrolment.  Class)            . Addentity ("C", Course.  Class)            . List ();    Tx.commit ();    Hibernateutil.closesession ();}

Using native SQL statements in Hibernate