Call View, stored Procedure, and hibernateprocedure of Hibernate Optimization Technology

Call View, stored Procedure, and hibernateprocedure of Hibernate Optimization Technology

Preface

Reprinted please indicate the source: Small (http://blog.csdn.net/wlwlwlwl015/article/details/42705585), it is mainly in the object navigation for us to optimize HQL. This blog will introduce some universal optimization methods, that is, using views and stored procedures in hibernate. When the data volume is large (millions), HQL is not recommended for hibernate, but native SQL statements are used, database objects such as views, indexes, and stored procedures are also optimized based on the underlying database and native SQL statements, the following code describes how to call view, proc, and some key points to be aware of in hibernate.

View query through hibernate

The concept and advantages of the database View are not mentioned. This book describes a lot on the Internet. The following example shows how to query the View in hibernate, it is still the example in the previous article. A typical one-to-many example is: Class → student. Let's take a look at the data table first:

Write a simple view. For example, you need to query the following fields: stu_id, sname, sex, birthday, And cname. First, create a view as needed,

DELIMITER $$USE `wltestdb`$$DROP VIEW IF EXISTS `v_stuinfo`$$CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_stuinfo` AS (SELECT  `t1`.`stu_id`   AS `stu_id`,  `t1`.`name`     AS `sname`,  `t1`.`sex`      AS `sex`,  `t1`.`birthday` AS `birthday`,  `t2`.`name`     AS `cname`FROM (`t_student` `t1`   JOIN `t_classroom` `t2`     ON ((`t1`.`cid` = `t2`.`cla_id`))))$$DELIMITER ;

You can use the query statement select * from v_stuinfo to query the view,

OK. The view is okay. The next step is how to map and query the view. The first step is to establish the view mapping through hibernate.

1. if the ing view does not know how to map the View by hand, we can use the reverse engineering of hibernate in MyEclipse to generate the ing object and ing configuration of the view and open the DB Browser View, find our view and right-click Hibernate Reverse Engineering to generate an annotation or xml file as needed. After the next step, we can find three more files, two PO classes, and one hbm. xml file,
We can see that a class with an uppercase V header + view name is generated, and a class with an Id added to the previous class. A total of two PO classes and an hbm are generated for the view. xml file, which is the view mapped by hibernate. Because the view does not have a primary key, therefore, if hibernate cannot determine the <id>, it will naturally not be able to map through one entity class. The solution of hibernate is to map views using two pods, one PO encapsulates all columns returned by the query view, and the other PO uses the encapsulated object as the Union primary key, so that the ing can be completed. Let's take a look at the code of these two classes and the code of the configuration file,
VStuinfo:

package com.wl.entity;/** * VStuinfo entity. @author MyEclipse Persistence Tools */public class VStuinfo implements java.io.Serializable {// Fieldsprivate VStuinfoId id;// Constructors/** default constructor */public VStuinfo() {}/** full constructor */public VStuinfo(VStuinfoId id) {this.id = id;}// Property accessorspublic VStuinfoId getId() {return this.id;}public void setId(VStuinfoId id) {this.id = id;}}

VStinfoId:

package com.wl.entity;import java.util.Date;/** * VStuinfoId entity. @author MyEclipse Persistence Tools */public class VStuinfoId implements java.io.Serializable {// Fieldsprivate Integer stuId;private String sname;private String sex;private Date birthday;private String cname;// Constructors/** default constructor */public VStuinfoId() {}/** minimal constructor */public VStuinfoId(Integer stuId) {this.stuId = stuId;}/** full constructor */public VStuinfoId(Integer stuId, String sname, String sex, Date birthday,String cname) {this.stuId = stuId;this.sname = sname;this.sex = sex;this.birthday = birthday;this.cname = cname;}// Property accessorspublic Integer getStuId() {return this.stuId;}public void setStuId(Integer stuId) {this.stuId = stuId;}public String getSname() {return this.sname;}public void setSname(String sname) {this.sname = sname;}public String getSex() {return this.sex;}public void setSex(String sex) {this.sex = sex;}public Date getBirthday() {return this.birthday;}public void setBirthday(Date birthday) {this.birthday = birthday;}public String getCname() {return this.cname;}public void setCname(String cname) {this.cname = cname;}public boolean equals(Object other) {if ((this == other))return true;if ((other == null))return false;if (!(other instanceof VStuinfoId))return false;VStuinfoId castOther = (VStuinfoId) other;return ((this.getStuId() == castOther.getStuId()) || (this.getStuId() != null&& castOther.getStuId() != null && this.getStuId().equals(castOther.getStuId())))&& ((this.getSname() == castOther.getSname()) || (this.getSname() != null && castOther.getSname() != null && this.getSname().equals(castOther.getSname())))&& ((this.getSex() == castOther.getSex()) || (this.getSex() != null&& castOther.getSex() != null && this.getSex().equals(castOther.getSex())))&& ((this.getBirthday() == castOther.getBirthday()) || (this.getBirthday() != null&& castOther.getBirthday() != null && this.getBirthday().equals(castOther.getBirthday())))&& ((this.getCname() == castOther.getCname()) || (this.getCname() != null && castOther.getCname() != null && this.getCname().equals(castOther.getCname())));}public int hashCode() {int result = 17;result = 37 * result+ (getStuId() == null ? 0 : this.getStuId().hashCode());result = 37 * result+ (getSname() == null ? 0 : this.getSname().hashCode());result = 37 * result+ (getSex() == null ? 0 : this.getSex().hashCode());result = 37 * result+ (getBirthday() == null ? 0 : this.getBirthday().hashCode());result = 37 * result+ (getCname() == null ? 0 : this.getCname().hashCode());return result;}}

VStuinfo. hbm. xml:

<?xml version="1.0" encoding="utf-8"?><!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN""http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"><!--     Mapping file autogenerated by MyEclipse Persistence Tools-->

2. the query View query view is very simple. Now that the ing has been completed, you can use HQL to query the view as a common object. Let's take a look at the test code and running results,





No problem. The two data items returned by the view are successfully printed. However, to map the view, you must note that all columns returned by the view cannot have a NULL value. If a NULL value exists, in this case, the List returned by row 18th must be NULL (this problem occurs when the project was previously created. The console sends the correct SQL statement, and the List object that can be returned is always NULL ). The solution is also very simple. You can add a default value to a column that may be empty, or specify a primary key for the view. Of course, if the view does not return a NULL value, you can ignore this problem.



Call a stored procedure through hibernate



The concept and advantages of the same stored procedure are not introduced here. We still use a complete example to demonstrate how to call the stored procedure in hibernate. 1. The storage creation process still uses class and student examples, and queries the above five columns, but is implemented by the storage process. The first step is to create the storage process,
DELIMITER $$USE `wltestdb`$$DROP PROCEDURE IF EXISTS `proc_stuinfo`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_stuinfo`(IN pstuid INT    )BEGINSELECT t1.stu_id AS stuid,t1.name AS sname,t1.sex,t1.birthday,t2.name AS cname FROM t_student t1 INNER JOIN t_classroom t2 ON t1.cid=t2.cla_id WHERE t1.stu_id=pstuid;    END$$DELIMITER ;

You can see that an input parameter is provided for passing stu_id. Call this parameter to see the data,

We can see that data is successfully returned after the stored procedure is called. The following describes how to call the stored procedure through hibernate.

2. in hibernate, how does one call a stored procedure in hibernate? I often ask other people during interviews. What I don't understand is that few people know such common things, I don't know whether the stored procedure is rarely used or the jdbc api is unfamiliar. In fact, calling through hibernate is simpler than calling the stored procedure through JDBC. We don't even need to use the CallableStatement object. We can directly return data through creatSQLQuery ("{call proc_name (param. Next, let's take a look at the test code and running results,

Here, I encapsulated a DTO object to save the data returned by proc. You can see that the console successfully printed the call statements and data results.

I personally recommend that you use the above method to call the stored procedure. Of course, there are some other methods, such as getting the Connection object through session and then calling the stored procedure through CallableStatement, the same is true for the code,

The session can be found here.
Connection ()The method is out of date. This method is not recommended since hibernate3.2.2. Instead, it obtains the Connection object through SessionFactoryUtils in the spring-orm package provided by spring, as shown in the following figure:
try {Connection connection = SessionFactoryUtils.getDataSource(getSessionFactory()).getConnection();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}
The content of the hibernate stored procedure is introduced here for the time being. If you have further study, we will update this blog one after another.





Summary



This blog record another Optimization Method of the persistence layer, that is, reference views and stored procedures. When the data volume is large, for example, after the data volume reaches a million, we may not be able to meet our efficiency requirements when using HQL statements. Therefore, we should use native SQL statements, such as views and stored procedures, to meet our performance requirements. Well, this blog will come to the end here. The summary of hibernate optimization may be put for the moment. I am going to continue to learn about Android and the front-end. The time will never be enough. Come on, raito!