13.hibernate Native SQL query (go from xiaoluo501395377)

In our hibernate, in addition to our commonly used HQL query, but also very good support of the native SQL query, so since we use Hibernate, why not all use Hibernate recommended HQL query statement? This is because the HQL query statement is convenient for us to query, but the HQL-based query will save the queried object to hibernate cache, if in one of our large projects (the amount of data exceeds the millions), this time if using Hibernate hql query, Once we query the object and put it into the cache, this time will affect our efficiency, so when using hibernate in a large project, our best practice is to use native SQL query statements, rather than using the HQL statement, because the SQL query, is not cached by Hibernate. Next we'll look at Hibernate's native SQL query

1. Scalar queries

In Hibernate, if we use native SQL queries, it is done through the SQLQuery interface, we first look at our most basic queries:

Session.createsqlquery ("SELECT * from T_student S"). List () session.createsqlquery ("Select Id,name,sex from T_student S" ). List ()

This creates the simplest two SQL query statements, where the field values of the returned database tables are stored in a object[] array, and each element in the array is the value of each field in the T_student table that is queried. Hibernate uses ResultSetMetaData to determine the location and type of each field value, and then we look at the scalar query:

List<object[]> Stus = (list<object[]>) session.createsqlquery ("SELECT * from T_student S")                                                    . AddScalar ( "ID").                                                    addscalar ("NAME").                                                    setfirstresult (0). Setmaxresults ().                                                    list ();

This query statement specifies the string of the query and the fields and types returned

This query will still return an array of type object[], but at this point it will not pass through ResultSetMetaData, but rather we explicitly specify the Id,name,sex, at which point all fields are queried in the query statement. But this time only returns the value of the three fields we specify, and the type is specified by Resultsetmetada.

2. Entity Query

① returns an Entity object

The bare data returned by the scalar query above is saved to the object[] array, and we can use the Addentity () method when we want an entity object to be saved to the entity object:

@Test public void TestSql1 () {session session = NULL;            try {session = Hibernateutil.opensession (); /* * Native SQL statement query, will find all fields of t_student table, stored in a object[] array * If you want to convert to a solid object, you only need to call Addentity (Student.class ) can, at this time, will first match * Student object inside the property whether all query out, if not, then error (If this class is an entity Class) * Note: If you want to call the Addentity method, this class must be an entity class, which adds @enti Ty annotations or configuration of entity classes in XML * mapping relationships */list<student> Stus = (list<student>) session.cre                                                    Atesqlquery ("SELECT * from T_student S"). Addentity (Student.class)                                                    . Setfirstresult (0). Setmaxresults (20)            . List ();            for (Student stu:stus) {System.out.println (Stu.getname ());        }} catch (Exception e) {e.printstacktrace ();        } finally{Hibernateutil.close (session); }    }

In this case, we specify the object that will be queried into the student entity class, note : If you use the entity object, then the entity object in the query to all the attributes in the SQL statement query, otherwise it will be an error .

② Returning multiple Entity objects

Sometimes we may not only query an entity object, when using the connection query, we may need to query the data of several tables, and stored in the corresponding entity object, this time we should write it? First look at the following:

List<object[]> Stus = (list<object[]>) session.createsqlquery ("Select Stu.*, cla.*, spe.*"                    + "From T_student Stu left join T_classroom CLA on stu.rid=cla.id"                    + "left join T_special spe on SPE.ID=CLA.SID where Stu.name like? ")                                                    . Addentity ("Stu", Student.class).                                                    addentity ("CLA", Classroom.class).                                                    addentity ("SPE", Special.class)                                                    . Setfirstresult (0). Setmaxresults.                                                    setparameter (0, "% of%")                                                    . List ();

We are here through an overloaded method of addentity to each alias to specify an associated entity class, this time there will be a field name collision problem, we mean to query out three entity objects, get its Name property, but the Name property in the three table field is name, The property values in the three entity objects queried at this time are the same. If we want to work around this method, we just need to enclose all the property values of each table with a {} curly brace placeholder. As follows:

@Test public void TestSql3 () {session session = NULL;            try {session = Hibernateutil.opensession (); /** * When querying multiple objects using a connection query, the addentity ("Alias", Xxx.class) method can be used to introduce multiple entity classes based on the alias of the * database table, if you need to save all the queried objects separately into the entity class, * only need to add the {} number on the object to be queried, this will automatically help us classify */list<object[]> Stus = (list<obje ct[]>) Session.createsqlquery ("Select {stu.*}, {cla.*}, {spe.*}"+" from T_student Stu left join T_classroom CLA on stu.rid=cla.id "+" left Join T_special spe on Spe.id=cla.sid where stu.name like? ").                                                    Addentity ("Stu", Student.class). Addentity ("CLA", Classroom.class)                                                    . Addentity ("SPE", Special.class)                                                    . Setfirstresult (0). Setmaxresults. Setparameter (0, "% of%")            . List ();                For (object[] obj:stus) {Student stu = (Student) obj[0];                Classroom CLA = (classroom) obj[1];                Special SPE = (special) obj[2];            System.out.println (Stu.getname () + "," + cla.getname () + "," + spe.getname ()); }} catch (Exception e) {           E.printstacktrace ();        } finally {Hibernateutil.close (session); }    }

③ returning entity objects that are not managed by hibernate

We sometimes need to look at some of the fields in each table and then store them in a JavaBean object, but our Bean object does not need to be stored in the database, it does not need to be set to a physical object, we often create a DTO Data transfer object to hold the attributes we want to store, such as defining a Studentdto object:

public class studentdto{    private int sid;    Student ID    private String sname;　　Student name    private String sex;　　Student Sex    private String cname;　　Class name    private String spename; Professional name Public        studentdto () {} public    studentdto (int sid, String sname, String sex, String cname,            string Spena Me)    {        super ();        This.sid = SID;        This.sname = sname;        This.sex = sex;        This.cname = CNAME;        This.spename = Spename;    } ................}

Let's take a look at our query statements at this time:

@Test public void TestSql4 () {session session = NULL; try {/** * for classes of non-entity entity objects, if we want to persist data, we can define a DTO object * and then call Setresulttransform ER (Transformers.aliastobean (studentdto.class)) method * To return an out-of-control Bean Object */session = Hiberna            Teutil.opensession (); List<studentdto> Stus = (list<studentdto>) session.createsqlquery ("select" + "Stu.id as Sid , Stu.name as sname, stu.sex as sex, cla.name as CNAME, spe.name as spename "+" from T_student Stu Lef T join T_classroom CLA on stu.rid=cla.id "+" left join T_special spe on SPE.ID=CLA.SID where stu.name Like? "). Setresulttransformer (Transformers.aliastobean (Studentdto.class)). Setfi Rstresult (0). Setmaxresults. Setparameter (0,% of "%"). List (); for (Studentdto std:stus) {System.out.println (Std.getsname () + "," + std.getcname () + "," +            Std.getspename ());        }} catch (Exception e) {e.printstacktrace ();        } finally {Hibernateutil.close (session); }    }

We're going to be able to call the fields of the different tables that we've queried to be stored in a non-entity object. Setresulttransformer (Transformers.aliastobean (Studentdto.class)) method to create an unmanaged bean object, hibernate will store the attribute values in the Bean object, and Note that the field values of the queried table are stored in the Bean object by invoking the setter method of the Bean object. If the property does not have a setter method in the Bean object, an error occurs .

This essay mainly analyzes how to query the information we need through the native SQL statements, we must remember that when the amount of data is very large, it is strongly recommended to use the native SQL to query the data, instead of using HQL to query, so in fact, using Hibernate is actually not bad, However, our increase, deletion, and modification of the operation can be completely given to hibernate to complete.

13.hibernate Native SQL query (go from xiaoluo501395377)