Hibernate about executing SQL query statements

Original http://www.yshjava.cn/post/543.html ThemeSQLHibernate

Hibernate support and control of native SQL queries is implemented through the SQLQuery interface. Through the session interface, we can easily create a sqlquery (SQLQuery is an interface, before Hibernate4.2.2, the default is to return the SQLQuery implementation class--sqlqueryimpl object, The SQLQuery, which appears in the following section, refers to this subclass as a non-annotated object for native SQL queries:

Session. Createsqlquery (String sql);

SQLQuery implements the query interface, so you can use the APIs provided in the query interface to get the data.

The simplest example

Get all query Results session. Createsqlquery ("SELECT * from note"). List ();  Gets the first result only session. Createsqlquery ("SELECT * from note where id = 1"). Uniqueresult ();    

Using preprocessed SQL

The benefits of preprocessing SQL are naturally needless to say, in addition to the well-known ability to prevent SQL injection attacks, it can improve the efficiency of SQL query to some extent. SQLQuery provides a number of interfaces to set different types of parameters, such as Setbigdecimal, Setbinary, setdouble, and so on, the SQLQuery Javadoc, which is not mentioned here. This is just about the general SQL parameter setting interface Setparameter.

The following code demonstrates how to use SQLQuery to perform preprocessing sql:

SQLQuery query = session.createsqlquery ("SELECT * from note where id =?"); //Set the value of the first parameter to 12, that is, query id=12 's notequery.setparameter (three);  List = query. list ();     ..

It is important to note that you can set the SQL parameters by setting the interface for different types of parameters, or by Setparameter to set the parameters, the subscript is starting from 0, not starting from 1!

Using a custom result converter to process query results

The SQLQuery interface reserves the Setresulttransformer interface for processing query results using a user-defined Resulttransformer result set converter. The Resulttransformer interface is very simple, with only two methods for converting single-line data and all result data. Entities that have been generated by custom Resulttransformer are not joined to the session and are therefore non-managed entities.

The following code demonstrates how to load a single row of data into a Linkedhashmap object:

Query.setresulttransformer (New Resulttransformer () { @Override public object transformtuple ( object[] values, string[] columns) {map<string, object> Map = new linkedhashmap<string,  Object> (1); int i = 0; for (string column:columns) {map.put (column, values[i++]);} return map;}  @Override public list transformlist ( list List) {return list;});           

If you do not set a custom Resulttransformer converter, hibernate loads the data that returns results for each row into the object array in the order of the resulting columns.

Here is a tool class: Transformers , which provides a number of common converters that can help us quickly convert result sets such as Transformers.aliastobean (Note.class) The ability to inject query results into the note entity by alias.

Using scalar

When you execute native SQL using SQLQuery, Hibernate uses ResultSetMetaData to determine the actual order and type of scalar values that are returned. You can use Addscalar () if you want to avoid excessive use of resultsetmetadata, or if you are simply naming the return value for more explicit purposes.

Session.createsqlquery ("1"). Addscalar ("ID", longtype.instance). Addscalar ("  Name", stringtype.instance). Addscalar (" Createtime", datetype.instance);      

This query specifies the SQL query string, the fields and types to return. It still returns an object array, but instead of using resultsetmetdata at this point, it is clear that Id,name and createtime follow long, The string and date types are removed from the resultset. 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.

It is also possible to not set the type information for all or part of a scalar value:

Session.createsqlquery ("1"). Addscalar ("ID"). Addscalar ("name"). Addscalar ("Createtime", datetype.instance);      

Fields that are not of the specified type will still use Resultsetmetdata to get their type. Note that fields are not case sensitive and cannot specify fields that do not exist!

about how the Java.sql.Types returned from ResultSetMetaData is mapped to the Hibernate type, which is controlled by the dialect (dialect). If a specified type is not mapped, or if it is not the type you expect, you can define it yourself by Dialet's Registerhibernatetype call.

If only one scalar is specified, then ...

Date Createtime = (date) session.createsqlquery ("1"). Addscalar ("Createtime", datetype.instance). Uniqueresult ();  

If our SQL statements use aggregate functions such as Count, Max, Min, Avg, and so on, and return only one field, Hibernate provides a very handy way to extract scalar results.

Entity Query

The above query returns a scalar value, which is the "bare" data returned from resultset. Here's how to get a native query to return an entity object with Addentity ().

Session.createsqlquery ("1"). Addentity (Note.class); Session.createsqlquery ("ID,1 "). Addentity (Note.class);    

This query specifies the SQL query string to return the entity to. Assuming that the note is mapped to a class that has three fields of Id,name and Createtime, the above two queries return a list, each of which is a note entity.

If an entity has a many-to-one association to another entity at the time of mapping, it must also be returned at query time, otherwise a "column not found" database error will 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 that has a point to dog:

Session.createsqlquery ("id =?"). Addentity (Note.class); 

The author field is the associated field of the note entity and the author entity, and you only get the value of the field at query time, and hibernate can use that value to find the corresponding associated entity. As in the previous example, Note.getauthor () returns the author object to which the current note belongs.

Working with associations and collection classes

It is possible to get the author connection by early fetching, and to avoid the additional overhead associated with initializing the proxy. This is done through the Addjoin () method, which allows you to connect the associations or collections in.

Session. Createsqlquery (. addentity (.AddJoin ("Note.author");   

The above example is a many-to-one correlation query, which in turn makes a pair of more related queries is also possible. In the example below, Author.notes represents all the journal (Note) that the user publishes, set collection type:

Session.createsqlquery ("and note.author = Author.id"). Addentity ("author", User.class). AddJoin ("Note", "Author.notes");    

Note : The join query returns multiple entity objects per row, which you need to be aware of when processing.

Aliases and attribute references

If the SQL query joins multiple tables, the same field name may appear multiple times in more than one table, which results in a SQL error. However, we can solve this problem perfectly by using placeholders.

In fact, the placeholder has been used in the example above:

Session. Createsqlquery (. addentity (.AddJoin ("Note.author");   

This query indicates the SQL query statement that contains the entity that the placeholder is attached to to allow Hibernate to inject field aliases, queries, and returns.

The {note.*} and {author.*} tags used above appear as shorthand for all properties, and you can also explicitly list the field names. But in the example code below we let hibernate inject a SQL field alias for each property, and the placeholder for the field alias is table Alias +. + Property name.

Note : Property names are case-sensitive and cannot be used with placeholders in the WHERE clause.

SQLQuery query = Session.createsqlquery ("andnote.author = author.id"); Query.addentity ("Note", Note.class), Query.addjoin ("Author", "Note.author");    

In most cases, the above method of alias injection is sufficient, but in situations where more complex mappings such as composite attributes, inheritance trees constructed through identifiers, and collection classes are required, more complex alias injection methods are needed.

The following table lists the different ways to use an alias injection parameter:

Nickname Injection (alias injection names) description	Grammar	Example
Simple properties	{[aliasname]. [PropertyName]	A_name as {item.name}
Composite properties	{[aliasname]. [ComponentName]. [PropertyName]}	CURRENCY as {item.amount.currency}, VALUE as {item.amount.value}
Entity-Discerning device	{[Aliasname].class}	DISC as {Item.class}
All properties of an entity	{[aliasname].*}	{item.*}
Collection key (Collection key)	{[Aliasname].key}	ORGID as {Coll.key}
Collection ID	{[Aliasname].id}	EMPID as {coll.id}
Collection elements	{[Aliasname].element}	XID as {coll.element}
Properties of the collection element	{[Aliasname].element. [PropertyName]}	NAME as {coll.element.name}
All properties of the collection element	{[aliasname].element.*}	{coll.element.*}
All properties of the collection	{[aliasname].*}	{coll.*}

Describe the result set mapping information in the HBM file and use it in the query

For some complex result set mappings, it is often necessary to manually configure the files as MyBatis and then use them in the program. Fortunately hibernate also provides similar functionality, and you can use your own configured result set mappings to process the returned result set data:

SQLQuery query = Session.createsqlquery ("andnote.author = author.id");// Use the custom result set mapping Query.setresultsetmapping ("Noteanduthor") configured in HBM files; query.list ();  

Perform an update operation

It is easier to perform a database update operation with sqlquery, except that you need to specify the SQL statement (if necessary and set the SQL parameter) just like the query, and simply call the Executeupdate () method to commit the update operation. The code looks like this:

Session. Createsqlquery ("Update createtime =? From note where note.id =? "); query. SetDate (0, New Date ());  Query. Setlong (1L);  Query. executeupdate ();         

The return result of the Executeupdate method is the number of rows in the changed database record.

Hibernate about executing SQL query statements (GO)