存储过程与视图的比较--并且在mybatis中的实现过程

雇员基本信息表 雇员薪水信息表

两张表的联立视图查询：

创建视图的sql语句：

SELECT
t_employee_info.employee_name,
t_employee_info.employee_age,
t_employee_info.employee_address,
t_salary_info.salary_id,
t_salary_info.time,
t_salary_info.money,
t_employee_info.employee_id
FROM
t_employee_info
INNER JOIN t_salary_info ON t_salary_info.employee_id = t_employee_info.employee_id
ORDER BY
t_salary_info.money DESC 

查询效果图

创建存储过程的sql语句：

BEGIN
    #Routine body goes here...
select 
t_employee_info.employee_name,
t_employee_info.employee_age,
t_employee_info.employee_address,
t_employee_info.employee_id,
t_salary_info.salary_id,
t_salary_info.time,
t_salary_info.money
FROM t_employee_info INNER JOIN t_salary_info 
ON t_salary_info.employee_id = t_employee_info.employee_id
where t_employee_info.employee_id=employee_id
ORDER BY
t_salary_info.money DESC limit 10;
END

存储过程employee_salary_function的创建效果图

mybatis的mapper.xml代码：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.jm.dao.TEmployeeSalaryMapper">
    <resultMap id="BaseResultMap" type="com.jm.model.TEmployeeSalary">
        <result column="employee_id" property="employeeId" jdbcType="BIGINT" />
        <result column="employee_name" property="employeeName" jdbcType="VARCHAR" />
        <result column="employee_age" property="employeeAge" jdbcType="INTEGER" />
        <result column="employee_address" property="employeeAddress" jdbcType="VARCHAR" />
        <result column="salary_id" property="salaryId" jdbcType="BIGINT" />
        <result column="time" property="time" jdbcType="BIGINT" />
        <result column="money" property="money" jdbcType="BIGINT" />    
    </resultMap>
    <!-- 调用存储过程查询 -->
    <select id="loadByEmployeeId" parameterType="Long" statementType="CALLABLE" resultMap="BaseResultMap">
        {call employee_salary_function(#{employeeId,jdbcType=BIGINT,mode=IN})}
    </select>
    
    <!-- 调用视图查询 -->
    <select id="loadByEmployeeIdView" resultMap="BaseResultMap">
        select * from v_employee_salary where employee_id=#{employeeId,jdbcType=BIGINT}
    </select>
</mapper>

实体类：

public class TEmployeeSalary {
    
    private Long employeeId;
    private String employeeName;
    private Integer employeeAge;
    private String employeeAddress;
    private Long salaryId;
    private Long time;
    private Long money;
    
    public String toString(){
        return "employeeId:"+employeeId+", "+"employeeName:"+employeeName+", "+"employeeAge:"+employeeAge+", "+"employeeAddress:"+employeeAddress+", "+
                "salaryId:"+salaryId+", "+"time:"+time+", "+"money:"+money+"-----------";
    }
    
    public Long getEmployeeId() {
        return employeeId;
    }

    public void setEmployeeId(Long employeeId) {
        this.employeeId = employeeId;
    }

    public String getEmployeeName() {
        return employeeName;
    }

    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }

    public Integer getEmployeeAge() {
        return employeeAge;
    }

    public void setEmployeeAge(Integer employeeAge) {
        this.employeeAge = employeeAge;
    }

    public String getEmployeeAddress() {
        return employeeAddress;
    }

    public void setEmployeeAddress(String employeeAddress) {
        this.employeeAddress = employeeAddress;
    }

    public Long getSalaryId() {
        return salaryId;
    }

    public void setSalaryId(Long salaryId) {
        this.salaryId = salaryId;
    }

    public Long getTime() {
        return time;
    }

    public void setTime(Long time) {
        this.time = time;
    }

    public Long getMoney() {
        return money;
    }

    public void setMoney(Long money) {
        this.money = money;
    }
    
}

dao:

import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.jm.model.TEmployeeSalary;

public interface TEmployeeSalaryMapper {
    /**
     * loadByEmployeeId:根据雇员的id获取他的工资情况,存储过程的调用
     * @author JM 
     * 2016-12-14 下午11:17:54
     * @param id
     * @return   
     * List<TEmployeeSalary>
     */
    public List<TEmployeeSalary> loadByEmployeeId(@Param("employeeId")Long id);
    
    /**
     * loadByEmployeeIdView:根据雇员的id获取他的工资情况,视图的调用
     * @author JM 
     * 2016-12-14 下午11:40:36
     * @param id
     * @return   
     * List<TEmployeeSalary>
     */
    public List<TEmployeeSalary> loadByEmployeeIdView(@Param("employeeId")Long id); 
}

以上就是使用mybatis，调用视图查询以及存储过程的实现代码。
许多情况下，对数据库进行多表查询可以使用视图进行联系多表，但是不建议使用视图进行查询（因为不可以输入参数，每次查询都需要全部查出来然后在挑选出符合条件的数据，这样大大拖慢了查询的速度），调用存储过程进行多表查询的好处就是先进性筛选然后在返回数据，这样的查询速度在数据量很大的情况下快的不是一星半点。
虽然存储过程的使用可以大大提高查询速率，但是对于频繁操作的表本人强烈建议添加索引，这样效率更高。