Native SQL query of Hibernate5.2

Native SQL query of Hibernate5.2

I. INTRODUCTION

in the previous blog, the author through the form of code to the readers of hibernate in the most important search method--HQL query. In this blog post I will introduce to you the native SQL query in Hibernate, although the native SQL query, but I think hibernate in the different database in the paging statement processing is very good, we do not have to care about the use of what kind of database. This blog post will follow the Pojo class and configuration file in Hibernate5.2 hql query, please read this article before reading this blog post, this article will be related to the hql of the relevant operations are changed to SQL query, so in the code of the high degree of repetition, Please do not spray the reader, then we will go directly to the unit test.

Two. Unit Testing

A. Get all order objects and get a list collection

@Test  Public void list () {    = "SELECT * from Orders";    Nativequery<Order> query = session.createnativequery (sql, Order.  Class);    List<Order> list = query.getresultlist ();      for (Order o:list) {         + "::" + O.getorderid ())}    }

B. Get the paging data for order and get a list collection

 /**   * Although it is a native SQL query, you can still use Setfirstresult () and Setmaxresults () methods. Thus shielding the difference of the underlying database.  */  @Test   void   PageList () {String sql     = "SELECT * FROM Orders" ;  // setfirstresult () starting from 0  Query <Order> query = session.createnativequery (sql, Order.    Class ). Setfirstresult (1). Setmaxresults (4 <Order> list = Query.getresultlist ();  for   (Order o:list) {System.out.println (    O.getid ()); }}

C. Multi-conditional query, returns the list collection (first form: Index placeholder)

@Test Public voidMulticretiera () {String SQL= "SELECT * from Orders where create_time between?" and? And order_id like? "; Query<Order> query = session.createnativequery (sql, Order.class); String Begindatestr= "2016-07-26 00:00:00"; String Enddatestr= "2016-07-28 23:59:59"; SimpleDateFormat SDF=NewSimpleDateFormat ("Yyyy-mm-dd hh:mm:ss"); Date begindate=NULL; Date endDate=NULL; Try{begindate=Sdf.parse (BEGINDATESTR); EndDate=Sdf.parse (ENDDATESTR); } Catch(ParseException e) {e.printstacktrace (); }    //pagination starting from 0Query.setparameter (0, Begindate). Setparameter (1, endDate). Setparameter (2, "%d%"). Setfirstresult (0). Setmaxresults ( 1); List<Order> list =query.getresultlist ();  for(Order o:list) {System.out.println (O.getorderid ()+ "::" +o.getcreatetime ()); }}

D. Multi-criteria query, return list collection (second form: named Placeholder)

@Test Public voidmultiCretiera1 () {String SQL= "SELECT * from Orders where order_id Like:orderid and create_time between:begindate and:enddate"; Query<Order> query = session.createnativequery (sql, Order.class); String Begindatestr= "2016-07-26 00:00:00"; String Enddatestr= "2016-07-28 23:59:59"; SimpleDateFormat SDF=NewSimpleDateFormat ("Yyyy-mm-dd hh:mm:ss"); Date begindate=NULL; Date endDate=NULL; Try{begindate=Sdf.parse (BEGINDATESTR); EndDate=Sdf.parse (ENDDATESTR); } Catch(ParseException e) {e.printstacktrace (); } query.setparameter ("OrderId", "%d%"). Setparameter ("Begindate", Begindate). Setparameter ("EndDate", endDate); List<Order> list =query.getresultlist ();  for(Order o:list) {System.out.println (O.getid ()+ "::" +O.getorderid ()); }
}

E. Queries that are larger than the criteria, using index placeholders

@Test  Public void gt () {    = "SELECT * from orders where ID >?" ;    Query<Order> query = session.createnativequery (sql, Order.  Class). Setparameter (0, 3);    List<Order> list = query.getresultlist ();      for (Order o:list) {        + "::" + O.getorderid ())}    }

F. Delete operation

@Test  Public void Delete () {    = "Delete from the orders where ID in (: idlist)"     ; = session.begintransaction ();    ListNew arraylist<integer>();    List.add (1);    List.add (2);    Query<?> query = session.createnativequery (SQL). Setparameter ("idlist", list);     int i = query.executeupdate ();    System.out.println (i);    Tx.commit ();    Session.close ();}

G. Getting the value of a column

@Test  Public void Singlevalue () {    = "Select order_id from Orders";    Query<String> query = session.createnativequery (sql);    List<String> list = query.getresultlist ();      for (String str:list) {        System.out.println (str);}    }

H. Getting the result set of an associated object

@Test  Public void GetCustomer () {    = "Select c.* from Orders O joins customer C on o.customer_id = c.id where c.id = 8";    Query<Customer> query = session.createnativequery (sql, Customer.  Class);    List<Customer> list = query.getresultlist ();      for (Customer o:list) {        + ";;" );    }}

I. Querying multiple columns of data

@Test  Public void Getobjectarray () {    = "Select C.name, C.phone_number, o.order_id, o.create_time from orders o join customer C On o.customer_id = C.id ";    Query<Object[]> query = session.createnativequery (sql);    List<Object[]> list = query.getresultlist ();      for (object[] o:list) {         System.out.println (o[0] + ";;" + o[1] + ";;" + o[2]);    }}

J. Function query

@Test  Public void Functionquery () {    = "SELECT Max (ID), COUNT (*) from Orders";    Query<Object[]> query = session.createnativequery (sql);     = Query.getsingleresult ();    System.out.println (obj[0] + "::" + obj[1]);}

K. Sorting

@Test  Public void Descquery () {    = "SELECT * FROM Orders ' ORDER by id desc";    Query<Order> query = session.createnativequery (sql, Order.  Class);    List<Order> list = query.getresultlist ();      for (Order o:list) {        + "::" + O.getorderid ())}    }

L. Right connection

@Test  Public void Rightjoin () {    = "Select c.* from Orders o right joins customer C on o.customer_id = C.id";    Query<Customer> query = session.createnativequery (sql, Customer.  Class);    List<Customer> list = query.getresultlist ();      for (Customer c:list) {        System.out.println (C.getid ());}    }

Previous: Hibernate5.2 's hql query

Native SQL query of Hibernate5.2