Hibernate returns the processing of result sets using native SQL queries

Hibernate returns the processing of result sets using native SQL queries

Today is OK, see the company framework there is a native SQL write function, said really before I did this thing, but for a long time no use, have forgotten almost, and now basically use the HQL statement to query the results. Hibernate uses Createsqlquery to implement queries for SQL statements, using: Session.createsqlquery (SQL), which has no memory for the result set returned with SQL. I checked the next, only to know that the original is to return an array of type object, and the value in the array of type object corresponds to the query item in the SQL statement, if it is a select * from XXX, the array value is according to all fields in the XXX table. Other not to say, see an article on the internet said very thin, directly turned around:
In migrating systems originally implemented with Jdbc/sql, Hibernat native SQL support is inevitably required.
1. Using SQLQuery
Hibernate's control of native SQL query execution is done through the SQLQuery interface.
1session.createsqlquery ();
1.1 Scalar queries
The most basic SQL query is to obtain a scalar (numeric) list.
1sess.createsqlquery ("SELECT * from CATS"). List ();
2sess.createsqlquery ("Select ID, NAME, BIRTHDATE from CATS"). List ();
Returns a list of Object arrays (object[]), each of which is a field value of the Cats table. 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.
1sess.createsqlquery ("SELECT * from CATS")
2. Addscalar ("ID", Hibernate.long)
3. Addscalar ("NAME", hibernate.string)
4. Addscalar ("BIRTHDATE", Hibernate.date)

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 birthdate follow long, The string and short 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.
1sess.createsqlquery ("SELECT * from CATS")
2. Addscalar ("ID", Hibernate.long)
3. Addscalar ("NAME")
4. Addscalar ("BIRTHDATE")

This is basically the same as the previous query, except that the ResultSetMetaData is used to determine the type of name and birthdate, and the type of the ID is explicitly stated.

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.
1.2 Entity queries
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 ().
1sess.createsqlquery ("SELECT * from CATS"). Addentity (Cat.class);
2sess.createsqlquery ("Select ID, NAME, BIRTHDATE from CATS"). Addentity (Cat.class);

This query specifies the SQL query string that is to be returned by the entity. Assuming that cat is mapped to a class that has three fields of id,name and birthdate, the above two queries return a list, each of which is a cat 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:
1sess.createsqlquery ("Select ID, NAME, BIRTHDATE, dog_id from CATS"). Addentity (Cat.class);
This cat.getdog () will work.
1.3 Handling associations and collection classes
It is possible to get the dog connection by early fetching and avoid the additional overhead of initializing the proxy. This is done through the Addjoin () method, which allows you to connect the associations or collections in.
1sess.createsqlquery ("Select C.id, NAME, BIRTHDATE, dog_id, d_id, d_name from CATS C, DOGS D WHERE c.dog_id = d.d_id")
2. Addentity ("Cat", Cat.class)
3. AddJoin ("Cat.dog");

In this example, the returned Cat object, whose dog attribute is fully initialized, no longer requires additional operations from the database. Notice that we added an alias ("Cat") to indicate the target property path of the join. The same advance connection can also be used for collection classes, for example, if Cat has a one-to-many association to dog.
1sess.createsqlquery ("Select ID, NAME, BIRTHDATE, d_id, D_name, cat_id from CATS C, DOGS D WHERE c.id = d.cat_id")
2. Addentity ("Cat", Cat.class)
3. AddJoin ("Cat.dogs");

1.4 Returning multiple entities

So far, the result set field names are assumed to be consistent with the field names specified in the mapping file. If a SQL query joins multiple tables, the same field name may appear multiple times in more than one table, which can cause problems.

The following query requires the use of a field alias injection (This example itself will fail):
1sess.createsqlquery ("Select C.*, m.* from CATS C, CATS m WHERE c.mother_id = c.id")
2. Addentity ("Cat", Cat.class)
3. Addentity ("Mother", Cat.class)

This query is intended to return two cat instances per row, one for cat and the other for its mother. However, because their field names are mapped to the same, and in some databases, the returned field aliases are in the form of "C.id", "C.name", and they do not match the names in the mapping file ("ID" and "name"), which can cause a failure.

Duplicate field names can be resolved in the following form:
1sess.createsqlquery ("Select {cat.*}, {mother.*} from CATS C, CATS m WHERE c.mother_id = c.id")
2. Addentity ("Cat", Cat.class)
3. Addentity ("Mother", Cat.class)

This query indicates that the SQL query statement, which contains placeholders attached to hibernate injection field aliases, queries the returned entity

The {cat.*} and {mother.*} tags used above appear as shorthand for all properties. Of course you can also explicitly list the field names, but in this example we let Hibernate inject SQL field aliases for each attribute. The placeholder for the field alias is the prefix of the property name plus the table alias. In the following example, we get the cat and its mother from another table (Cat_log) by mapping the specified in the metadata. Note that we can even use property aliases in the WHERE clause if we want to.
1String sql = "Select ID as {c.id}, NAME as {c.name}," +
2 "BIRTHDATE as {c.birthdate}, mother_id as {c.mother}, {mother.*}" +
3 "from Cat_log C, Cat_log m WHERE {c.mother} = c.id";
4
5List loggedcats = sess.createsqlquery (SQL)
6. Addentity ("Cat", Cat.class)
7. Addentity ("Mother", Cat.class). List ();
1.4.1 Aliases and attribute references

In most cases, the above attribute injections are required, but in the case of more complex mappings such as composite attributes, inheritance trees through identifiers, and collection classes, there are also special aliases that allow hibernate to inject appropriate aliases.

The following table lists the different possibilities for using an alias injection parameter. Note: Aliases in the following results are examples only, and each alias requires a unique and different name when it is useful.
Aliases (alias injection names)
Description Syntax Example
Simple property {[aliasname].[ PropertyName] A_name as {item.name}
Composite property {[aliasname].[ ComponentName].     [PropertyName]} CURRENCY as {item.amount.currency}, VALUE as {item.amount.value}
Entity Detector (Discriminator of an entity) {[Aliasname].class} DISC as {Item.class}
All properties of entity {[aliasname].*} {item.*}
Collection key (collection key) {[Aliasname].key} ORGID as {Coll.key}
Collection ID {[Aliasname].id} EMPID as {coll.id}
Collection element {[Aliasname].element} XID as {coll.element}
property 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.*}
1.5. Return to unmanaged entities
You can use Resulttransformer for native SQL queries. This returns entities that are not managed by hibernate.
1sess.createsqlquery ("Select NAME, BIRTHDATE from CATS")
2. Setresulttransformer (Transformers.aliastobean (Catdto.class))

This query specifies: SQL query string, resulting converter (result transformer)

The above query will return a list of catdto, which will be instantiated and inject the value of name and birthday into the corresponding attribute or field.

Transferred from: http://blog.csdn.net/yangqicong/article/details/6910740

Hibernate returns the processing of result sets using native SQL queries