Hibernate SQL query

If you're as familiar with SQL as I am, don't want to learn a new language. It is also a good way to use native SQL queries in hibernate.

• On the one hand, Native SQL has a natural advantage in terms of efficiency;
• On the other hand, SQL is a standard for database operations, and we have very little association with programs and databases. If you don't have hibernate in the future, use other orm,sql as well.

But try to use standard SQL instead of too much dialect SQL. Scalar queries

The most basic SQL query is to obtain a scalar (numeric) list.

Sess.createsqlquery ("SELECT * from User"). List (); Sess.createsqlquery ("Select ID, NAME, BIRTHDATE from User"). List ();

They all return a List of an Object array (object[]), each element of which is a field value of the USER table. Hibernate uses ResultSetMetaData to determine the actual order and type of scalar values that are returned.

If you do not need to perform complex processing in the background, you can convert the list<object[]> directly to JSON and hand it over to the foreground. When working with JSON, the property of an item is traversed as an array, and the property value null corresponds to NULL.

For example, our front desk needs a JSON-formatted list of userinfo that contains username,address,phone, and does not require private information such as account, password, and so on.

    Public list<object> Finduserinfo () {        Session session=hibernateutil.currentsession ();        Transaction tx = NULL;        List<object> Infolist=null;        try {            tx=session.begintransaction ();            Query query=session.createsqlquery ("Select Username,address,phone from User");            Infolist=query.list ();            Tx.commit ();        } catch (Hibernateexception e) {            throw e;        }        return infolist;    }

Entity Query

The above query returns a scalar value, which is the "bare" data returned from resultset. The following shows how to pass

Addentity () lets the native query return the entity object.

Sess.createsqlquery ("SELECT * from USER"). Addentity (User.class); Sess.createsqlquery ("Select ID, NAME, BIRTHDATE from USER "). Addentity (User.class);

This query specifies:

• SQL query string
• The entity to return

Return a single entity

    Public User getById (Integer id) {        Session session=hibernateutil.currentsession ();        Transaction tx = NULL;        User User=null;        try {            tx=session.begintransaction ();            User= (User) session.get (User.class, id);            Tx.commit ();        } catch (Hibernateexception e) {            tx.rollback ();            throw e;        }        return user;    }

Returns a collection

    Public list<user> FindAll () {        Session session=hibernateutil.currentsession ();        Transaction tx = NULL;        List<user> Userlist=null;        try {            tx=session.begintransaction ();            Query query=session.createsqlquery ("SELECT * from User"). Addentity (User.class);            Userlist=query.list ();            Tx.commit ();        } catch (Hibernateexception e) {            tx.rollback ();            throw e;        }        return userlist;    }

Paging Query

If you need to specify the range of the result set (the maximum number of rows/or rows you want to return), you should use the method provided by the Query interface, respectively: setfirstresult (int firstresult), setmaxresults (int maxResults).

A paged query is a special case of this kind of application where a page represents a result set that starts from a row of a database to the end of a row.

Query query=session.createsqlquery ("SELECT * from USER"); Query.setfirstresult ((pageIndex-1) * pageSize);// The starting line of the result set query.setmaxresults (pageSize); Maximum number of records in the result set, that is, the number of records on a page

Here is an example:

    Public list<logdetail> pagequery (int pageIndex, int pageSize, int adminid) {        session session = HIBERNATEUTIL.CU Rrentsession ();        Transaction tx = NULL;        List<logdetail> logdetaillist = null;        try {            tx = Session.begintransaction ();            Query query = Session                    . Createsqlquery (                            "Select logdetail.* from Logdetail INNER JOIN useradmin on Logdetail.useri D=useradmin.userid "                                    +" where adminid= "+ Adminid +" ORDER by logdate Desc ")                                    . Addentity (logdetail.class);            Query.setfirstresult ((pageIndex-1) * pageSize);            Query.setmaxresults (pageSize);            Logdetaillist = Query.list ();            Tx.commit ();        } catch (Hibernateexception e) {            tx.rollback ();            throw e;        }        return logdetaillist;    }

Hibernate SQL query