Using native SQL statements in Hibernate

Source: Internet
Author: User

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 INNER join Vrv_paw_pcinfo p on 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) { ("Get list Failed"); E.printstacktrace (); }}Private voidCreatesqlwherecondition (StringBuilder MySQL, list<object>getparamlist) { if(Stringutils.isnotblank (userName)) { mysql.append ( "and 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")
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.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 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.