Hibernate SQL query Addscalar () or addentity ()

Hibernate supports native SQL queries in addition to HQL queries.
The control of the native SQL query execution is performed through the SQLQuery interface, which is obtained by executing session.createsqlquery (). The interface is a sub-interface of the query interface.
The steps to execute the SQL query are as follows:
1. Get Hibernate Session Object
2. Writing SQL statements
3. Create a Query object through the Createsqlquery method of the session
4. Call the Addscalar () or Addentity () 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 contains parameters, call query's Setxxxx method to assign a value to the parameter
6. Call query's list method to return the result set of the query

One, scalar query
The most basic SQL query is to get a scalar list:

Session.createsqlquery ("fromperson_inf"). List ();    Session.createsqlquery ("Fromperson_inf"). List ();   

They all return a list of object arrays, each of which is a field value of the Person_inf table. Hibernate uses ResultSetMetaData to determine the actual order and type of scalar values that are returned.
However, excessive use of resultsetmetadata in JDBC can degrade the performance of the program. So in order to avoid using resultsetmetadata too much or to specify a more explicit return value type, we can use the Addscalar () method:

Session.createsqlquery ("fromperson_inf")    . Addscalar ("Name", standardbasictypes.string)    . Addscalar ("Age", standardbasictypes.int). List ();   

This query specifies the following:
1. SQL query string.
2. The fields and types to return.
It will still return an object array, but instead of using resultsetmetdata at this point, it is clear that name and age are removed from resultset by string and int types. It also indicates that even if query is queried using *, it may get more than the three fields listed, and only the three fields will be returned.
You can use Addscalar (String columnalias) If you only need to select the value of a field, and you do not need to explicitly specify the field's data type.
Examples are as follows:

Publicvoid Scalarquery () {Session session = Hibernateutil.getsession ();          Transaction tx = Session.begintransaction (); String sql ="SELECT * from Person_inf";                      List List = Session.createsqlquery (sql). Addscalar ("person_id", Standardbasictypes.integer). Addscalar ( "name", standardbasictypes.string). Addscalar ( "age", Standardbasictypes.integer). List (); for (Iterator Iterator = List.iterator (); Iterator.hasnext ();) {// Each collection element is an array, array element division person_id,person_name,person_age three column values object[] objects = (object[]) iterator.next (); System. out.println ( "id=" +objects[0]); System. out.println ( "name=" +objects[1]); System. out.println ( "age=" +objects[2]); System. out.println (

  

         as you can see from the above. The Addscalar () method in scalar queries has two functions:  
         1, Specifies which data columns the query results contain---columns that are not addscalar selected will not be included in the query results.  
         2, specifies the data type of the data column in the query result  

         entity query  
         The scalar result set returned by the scalar query above, That is, the "bare" data returned from resultset. If the result we want is an entity of an object, it can be implemented by means of the addentity () method. The Addentity () method can tell the result to be converted to an entity. However, in the process of conversion to pay attention to a few questions:  
         1, the query returns a table of all data columns  
          2, the data table has a corresponding persistent class map  
          The query results can then be converted to entities by using the Addentity () method.  

Session.createsqlquery ("fromperons_inf"). Addentity (person.class). List;    Session.createsqlquery ("Fromperson_inf"). Addentity (Person.class). List ();   

This query specifies:
1. SQL query string
2. Entities to return
Assuming that the person is mapped to a class that has id,name and age three fields, the two queries above return a list, each of which is a person entity.
If an entity has a many-to-one association to another entity at the time of the mapping, it must also return that entity at query time (get the mapped foreign key column), or it will cause a "column not found" database error to occur. These additional fields can be returned automatically using the * callout, but we would like to make it clear that the following is an example of a many-to-one with points to teacher:

Sess.createsqlquery ("fromperson_inf"). Addentity (Person.class). List (); 

This allows the teacher to be obtained through Person.getteacher ().
Instance:

public void entityquery () {Session      Session = Hibernateutil.getsession ();      Transaction tx = Session.begintransaction ();      String sql =  "SELECT * from Person_inf";      List list = session.createsqlquery (sql). Addentity (person.    Class). //specifies that the row of records for the query be converted into a person entity list (); for (Iterator Iterator = list.iterator (); Iterator.hasnext ();) {Person person = (person) iterator.next (); Each element of the //collection is a Person object System.out.println ( "name=" +person.getname ()); System.out.println ( "age=" +person.getage ());} tx.commit (); Session.close (); }

All of these are single-table queries, and if we use multiple table joins in SQL statements, the SQL statement can select data from multiple data tables. Hibernate 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 () method to convert the different data tables to different entities. As follows

Publicvoid Multientityquery () {Session session = Hibernateutil.getsession ();      Transaction tx = Session.begintransaction (); String sql ="Select p.*,e.* from Person_inf as P inner join Event_inf as E" + "on p.person_id=e.person_id"; List List = Session.createsqlquery (sql). Addentity ( Class). addentity ( Class). list (); for (Iterator Iterator = List.iterator (); Iterator.hasnext ();) {// Each collection element is an array of person, MyEvent, object[] objects = (object[]) iterator.next (); Person person = (person) objects[0]; MyEvent event = (myevent) objects[1]; System. out.println ( "person_id=" +person.getid () + " Person_name= "+person.getname () +event.gettitle ());}}   

Iii. dealing with associations and inheritance
It is possible to get the event connection by early fetching, and avoid the additional overhead associated with initializing the proxy. This is done through the Addjoin () method, through which the entity's associated entity can be converted into a query object. As follows:

Publicvoid Joinquery () {Session session = Hibernateutil.getsession ();      Transaction tx = Session.begintransaction (); String sql = "Select p.*,e.* from Person_inf as p,event_inf as E where e.person_id=p.person_id"; List List = Session.createsqlquery (sql). Addentity ( Class). AddJoin (for (Iterator Iterator = List.iterator (); Iterator.hasnext ();) {//each collection element is a person, MyEvent array object[] objects = (object[]) iterator.next (); Person person = (person) objects[0]; MyEvent event = (myevent) objects[1]; System. out.println ( "person_id=" +person.getid () + " Person_name= "+person.getname () +event.gettitle ());}}   

The above program returns the Person object whose property MyEvent property has been completely initialized, no longer requires additional operations from the database, and converts the property to an entity with an alias of E. This means that the returned result is a list of the person, an array of event objects.

Four, named query
Instead of putting SQL statements in a program, we can put them in a configuration file. This can improve program decoupling better.
Hibernate uses the <sql-query.../> element to configure a named SQL query, and the configuration <sql-query.../> element has a required Name property that specifies the name of the named SQL query.
When you use the <sql-query.../> element to define a named query, you can include several elements:
&LT;RETURN.../&GT;: Converting query results to persisted entities
&LT;RETURN-JOIN.../&GT: Pre-load an associated entity for a persisted entity
&LT;RETURN-SCALAR.../&GT: Converting a query's data column to a scalar value
When you use a named SQL query, you do not need to call addentity (), Addscalar (), and so on. Because when you configure a named SQL query, you have specified the result information returned by the query.
[HTML] View Plaincopyprint?
<!--named SQL query-

<Sql-queryName="SQLQuery" ><!--convert P aliases to person entities--<Returnalias="P"class="Person"/><!--convert E aliases to event entities--<Returnalias=Eclass="MyEvent"/><!--specifies that the Name property column of the Person_inf table be returned as a scalar value-<Return-scalarcolumn="P.name"Type="String"/> Select p.*,e.* from Person_inf as p,event_inf as e where p.person_id = e.person_id and P.age=:age</Sql-query> uses the session's getnamedquery to get the specified named SQL query. [Java] View plaincopyprint? public void Query () {Session session = Hibernateutil.getsession ();//Call the named query and return the result directly list = Session.getnamedquery ("sql Query "). Setinteger (" age ", +). List (); for (Iterator Iterator = List.iterator (); Iterator.hasnext ();) {//each collection element is a person, myevent an array of object[] objects = ( Object[]) Iterator.next (); Person person = (person) objects[0]; MyEvent event = (myevent) objects[1]; System.out.println ("person_id=" +person.getid () + "Person_name=" +person.getname () + "title=" +event.gettitle ()); } session.close (); Hibernate allows you to place the mapping information for the result set<The resultset.../> element definition, which allows multiple named queries to share the result set mappings. [HTML] View Plaincopyprint?<ResultSetName="Person_resultset" ><return alias= "P" class= "person"/> < Return-scalar column= "p.age" type= "int"/> </resultset> by <sql-query.../> element specifies the Resultset-ref attribute, you can have the named SQL query use an existing result set mapping. [HTML] View Plaincopyprint? <sql-query name= "SQLQuery" resultset-ref= "Person_resultset" > select p.* from Person as P </SQL-QUERY>         

V. Calling a stored procedure
Hibernate can call a stored procedure or function by naming a SQL query. For a function, the function must return a result set, and for a stored procedure, the first parameter of the stored procedure must be outgoing, and the data type is the result set.
The following is a simple stored procedure:

Procedure Select_person () from    Person_inf;

If you need to use this stored procedure, you can define it as a named SQL query and then use it in your program.
When using native SQL to invoke a stored procedure, you should specify callable= "true" for the <sql-query.../> element.

<Sql-queryName="CallProcedure"callable="True" ><return class= "person" > <!--convert the queried data column to an entity's properties-<return-property span class= "attribute" >name= "name" column= "name" /> <return-property name=  "age" column= "age"/> << Span class= "title" >return-property name= "person_id" column=</return > </SQL-QUERY>         

The program is the same as above.
There are several issues to be aware of when calling stored procedures:
In order to use stored procedures in Hibernate, you must follow some rules. Stored procedures that do not follow these rules will not be available. If you still want to use them, you have to execute them through session.connection (). These rules are for different databases. Because the database Providers have a variety of different syntax and semantics for stored procedures.
Queries against stored procedures cannot be paged using Setfirstresult ()/setmaxresults ().
The recommended method of invocation is standard SQL92: {? = call functionname (<parameters>)} or {? = call procedurename (&LT;PARAMETERS&GT;}. Native invocation syntax is not supported Hold.
The following rules are available for Oracle:
The function must return a result set. The first parameter of a stored procedure must be out, which returns a result set. This is done through the Sys_refcursor type of Oracle 9 or 10. In Oracle you need to define a REF cursor type, see the Manual for Oracle.
The following rules are available for Sybase or MS SQL Server:
The stored procedure must return a result set: Note that these servers may return multiple result sets and the number of updates. Hibernate will take out the first result set as its return value, and the others will be discarded.
This may be more efficient if you can set the set NOCOUNT on in the stored procedure, but this is not required.

Hibernate SQL query Addscalar () or addentity ()