Hibernate's SQL query

I. Introduction to SQL queries

Using SQL queries can take advantage of the characteristics of some databases, or migrate legacy JDBC applications to hibernate applications, or you might need to use native SQL queries. The query steps are as follows:

1. Get Hibernate Session Object

2. Writing SQL statements

3. Call the session's Createsqlquery () method to create the query object with the SQL statement as the parameter

4. Call the Addscalar () huoaddentity () method of the SQLQuery object to associate the selected result with a scalar value or entity, respectively, for scalar queries or entity queries

5. If the SQL statement has parameters, call query's Setxxx () method to assign a value to the parameter

6. Call the list () method of query or the Uniqueresult () method to return the result set of the query

Second, SQL query

1. Scalar query

A scalar query that obtains the list,hibernate of an object array corresponding to a data table column will by default determine the actual order and type of data columns returned by ResultSetMetaData, but such default processing can degrade program performance . Therefore, it is recommended to explicitly return a value type when writing code

　　　　　　  String sql = "SELECT * from Sql_student";             = session.createsqlquery (sql)                    . Addscalar ("id", Standardbasictypes.long)//Explicit return value type, the property name must be the same as the column name in the table                    . Addscalar ("sname", standardbasictypes.string)                    . Addscalar ("teacher_id", Standardbasictypes.long);             = q.list ();              for (Object ob:list) {                = (object[]) ob;                System.out.println (ob1[0] + "|" + ob1[1] + "|" + ob1[2]);            }

Multi-Table Query

String sql = "Select T.tid, t.tname,s.sid,s.sname from Sql_teacher T, sql_student s WHERE t.tid = s.teacher_id"; SQLQuery Q=session.createsqlquery (SQL). Addscalar ("T.tid", Standardbasictypes.long)//explicitly return a value type, the property name must be the same as the column name in the table. Addscalar ("T.tname", standardbasictypes.string). Addscalar ("S.sid", Standardbasictypes.long). Addscalar ("S.sname", standardbasictypes.string); List List=q.list ();  for(Object ob:list) {object[] ob1=(object[]) ob; System.out.println (ob1[0] + "" + ob1[1 "+" | "+ ob1[2] +" "+ ob1[3]); }

2. Entity Query

If the query returns all the data columns of a data table, and the data table has a corresponding persisted class map, the query results can be converted to entities using an entity query

　　　　　　  String sql = "SELECT * from Sql_student";             = session.createsqlquery (sql)                    . addentity (sqlstudent. class );//The program must select all data columns to be converted to persistent entities            List<SQLStudent> list = q.list ();              for (sqlstudent s:list) {                + "|" + s.getsname () + "|" + s.getsqlteacher (). GetName ());            }

Multi-Table query ( using this query, if two tables have the same field, you get the values of both fields in the previous field, the workaround is to make a difference between the names of the same field names in the table)

String sql = "Select s.*,t.* from Sql_teacher t,sql_student s WHERE s.teacher_id = T.tid"; SQLQuery sqlquery=session.createsqlquery (SQL). Addentity ("T", Sqlteacher.class). Addentity ("S", sqlstudent.class); List List=sqlquery.list ();  for(Object obj:list) {object[] objects=(object[]) obj; Sqlteacher Sqlteacher= (Sqlteacher) objects[0]; Sqlstudent sqlstudent= (sqlstudent) objects[1]; System.out.println (Sqlteacher.getid ()+ "" + sqlteacher.gettname () + "|" + sqlstudent.getid () + "" +sqlstudent.getsname ()); }

3. Related queries

String sql = "Select t.*,s.* from Sql_teacher T left joins sql_student s on t.tid = s.teacher_id"; SQLQuery sqlquery=session.createsqlquery (SQL). Addentity ("T", Sqlteacher.class). Addentity ("S", sqlstudent.class); List List=sqlquery.list ();  for(Object obj:list) {object[] objects=(object[]) obj; Sqlteacher Sqlteacher= (Sqlteacher) objects[0]; Sqlstudent sqlstudent= (sqlstudent) objects[1]; System.out.println (Sqlteacher.getid ()+ "" + sqlteacher.gettname () + "|" + sqlstudent.getid () + "" +sqlstudent.getsname ()); }

Note: The field names in two tables cannot be duplicated, otherwise the value of the same name field that results in the result is confused (only the field values in the preceding fields are sorted)

Test entity classes

@Entity @table (name= "Sql_teacher") Public classsqlteacher {@Id @GeneratedValue @Column (name= "Tid")    PrivateLong ID; @Column (Name= "Tname")    PrivateString Tname; @OneToMany (targetentity= Sqlstudent.class, Mappedby = "Sqlteacher")    PrivateSet<sqlstudent> sqlstudents =NewHashset<sqlstudent>();  PublicLong getId () {returnID; }     Public voidsetId (Long id) { This. ID =ID; }     PublicString Gettname () {returnTname; }     Public voidsettname (String tname) { This. Tname =Tname; }     PublicSet<sqlstudent>getsqlstudents () {returnsqlstudents; }     Public voidSetsqlstudents (set<sqlstudent>sqlstudents) {         This. sqlstudents =sqlstudents; }}

@Entity @table (name= "Sql_student") Public classsqlstudent {@Id @GeneratedValue @Column (name= "Sid")    PrivateLong ID; @Column (Name= "Sname")    PrivateString sname; @ManyToOne (targetentity= Sqlteacher.class) @JoinColumn (name= "teacher_id", referencedcolumnname = "Tid", nullable =false)    PrivateSqlteacher Sqlteacher; //@Column (name = "teacher_id")//private Long Teacherid;     PublicLong getId () {returnID; }     Public voidsetId (Long id) { This. ID =ID; }     PublicString Getsname () {returnsname; }     Public voidsetsname (String sname) { This. sname =sname; }     PublicSqlteacher Getsqlteacher () {returnSqlteacher; }     Public voidSetsqlteacher (Sqlteacher sqlteacher) { This. Sqlteacher =Sqlteacher; }    /*Public Long Getteacherid () {return teacherid;    } public void Setteacherid (Long teacherid) {this.teacherid = Teacherid; }*/}

Test class

 Public classSqlcontroller { Public Static voidMain (string[] args) {Configuration CF=NewConfiguration (). Configure (); Sessionfactory SF=cf.buildsessionfactory (); Session Session=sf.opensession (); Transaction TS=session.begintransaction (); Try{Sqlteacher T=NewSqlteacher (); T.settname ("Teacher 3"); Serializable ID=Session.save (t); T= (Sqlteacher) session.get (sqlteacher.class, id); Sqlstudent S1=Newsqlstudent (); S1.setsname ("Student 1");            S1.setsqlteacher (t); Sqlstudent S2=Newsqlstudent (); S2.setsname ("Student 2");            S2.setsqlteacher (t); Sqlstudent S3=Newsqlstudent (); S3.setsname ("Student 3");            S3.setsqlteacher (t);            Session.save (S1);            Session.save (S2);            Session.save (S3);        Ts.commit (); } finally{session.close ();        Sf.close (); }    }}

Code Download: Https://github.com/shaoyesun/hibernate_study.git

Hibernate's SQL query