NOTES: Hibernate SQL Query

Hibernate supports native SQL queries that use native SQL queries to take advantage of some database features, and native SQL queries support the placement of SQL statements in configuration files to improve program decoupling, and named SQL queries can also be used to invoke stored procedures.

SQL query is represented by the SQLQuery interface, the SQLQuery interface is a sub-interface of the query interface, can fully use the query interface method, SQLQuery added two overloaded methods

• Addentity (): Associating a queried record with a specific entity
• Addscalar (): Jiang Query the record associated scalar value

The steps to execute SQL are as follows:

• Get Hibernate Session object
• Write SQL statement
• Create SQLQuery object, Call the session's Createsqlquery method to create the
• Call SQLQuery object Addscalar Or the Addentity method associates the selected result with a variable value or entity
• If the SQL statement contains parameters, Call query's Setxxx method to assign a value to the parameter
• Call the list of query, The Uniqueresult method returns the result set of the query

  ??

2. scalar query

   If you want Hibernate to select only the value of a field, you can use a scalar query to specify the value of the field returned by the Addscalar method, as shown in the following example:

   list List = Cursession.createsqlquery (" SELECT * from Onetomany_one ")

   Addscalar (" Total_trade_ Amt ", Standardbasictypes.long)

4. Entity Query

   If you query all the data columns of a data table and the data table has a corresponding persisted class map, you can convert the query result to an entity by addentity specifying the persisted type, as in the following example:

   List List = Cursession.createsqlquery ("SELECT * from Onetomany_one")

   . Addentity (Onetomanyforonemodel.class)

   . List ();

6. SQL parameters

   SQL statements can use two parameter definition methods, parameter key and parameter index

• parameter key method: Define the parameter in the SQL statement using ": Parameter name" and use the Setparameter method's parameter name to assign a value to the parameter, and set the type of the parameter, examples are as follows:

  list List = Cursession.createsqlquery ("select * from Onetomany_one WHERE record_id =: Reocrdid")

  addentity (onetomanyforonemodel.class)

  Setparameter (" Reocrdid ", 1234, Standardbasictypes.integer)

  . List ();

• Parameter indexing method: Use "?" in SQL statements To define the parameters and use the index overloads of the Setparameter method to assign values to the parameters and to set the type of the parameters, as an example:

  List List = Cursession.createsqlquery ("select * from Onetomany_one WHERE record_id =?")

  . Addentity (Onetomanyforonemodel.class)

  . setparameter (0, 1234, Standardbasictypes.integer)

  . List ();

1. Stored Procedures

   Hibernate currently supports only stored procedures that return scalars and entities, and calls to stored procedures have the following requirements to note:

• It is recommended to use the standard SQL92 syntax, such as {? =call functionname (<parameters>)} or {call ProcedureName (<parameters>)}, which does not support native invocation syntax.
• Queries that call stored procedure feed cannot be paged using Setfirstresult (), Setmaxresults ()

1. Custom Sql,hibernate When you need to save, update, and delete persisted entities, these functions are done by default through a fixed set of SQL statements, and if the program needs to change the default SQL statement, you can use the custom SQL features provided by Hibernate .
   1. Annotations @SQLInsert Custom SQL statements for inserting records;
   2. Note @SQLUpdate: A SQL statement that customizes updated records;
   3. Annotation @SQLDelete: SQL statement for custom delete records;
   4. Annotation @SQLDeleteAll: Customizing SQL statements to delete all records;
   5. Annotation @loader: SQL statement for custom queries

   They all have two parameters, whose parameter SQL represents the executed custom SQL statement (stored procedure), and the parameter callable indicates whether the statement is a stored procedure. If the order of the parameters is important when using stored procedures, you can set the Org.hibernate.persister.entity log level to the debug level, allowing you to see the order that hibernate expects, and hibernate will output the entire level Static SQL for Create, update, and delete entities. Because hibernate checks the SQL statement for success, it should allow the stored procedure to return the number of rows affected by the stored procedure, and hibernate usually registers the first parameter of the CUD action statement as a numeric output parameter. Therefore, you should let the first parameter of the stored procedure record the number of record bars affected by the stored procedure (the stored procedure is not tested successfully)

NOTES: Hibernate SQL Query