雇員基本信息表

雇員薪水信息表

兩張表的聯(lián)立視圖查詢：

創(chuàng)建視圖的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 

查詢效果圖

創(chuàng)建存儲(chǔ)過程的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

存儲(chǔ)過程employee_salary_function的創(chuàng)建效果圖

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>
    <!-- 調(diào)用存儲(chǔ)過程查詢 -->
    <select id="loadByEmployeeId" parameterType="Long" statementType="CALLABLE" resultMap="BaseResultMap">
        {call employee_salary_function(#{employeeId,jdbcType=BIGINT,mode=IN})}
    </select>
    
    <!-- 調(diào)用視圖查詢 -->
    <select id="loadByEmployeeIdView" resultMap="BaseResultMap">
        select * from v_employee_salary where employee_id=#{employeeId,jdbcType=BIGINT}
    </select>
</mapper>

實(shí)體類：

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:根據(jù)雇員的id獲取他的工資情況,存儲(chǔ)過程的調(diào)用
     * @author JM 
     * 2016-12-14 下午11:17:54
     * @param id
     * @return   
     * List<TEmployeeSalary>
     */
    public List<TEmployeeSalary> loadByEmployeeId(@Param("employeeId")Long id);
    
    /**
     * loadByEmployeeIdView:根據(jù)雇員的id獲取他的工資情況,視圖的調(diào)用
     * @author JM 
     * 2016-12-14 下午11:40:36
     * @param id
     * @return   
     * List<TEmployeeSalary>
     */
    public List<TEmployeeSalary> loadByEmployeeIdView(@Param("employeeId")Long id); 
}

以上就是使用mybatis，調(diào)用視圖查詢以及存儲(chǔ)過程的實(shí)現(xiàn)代碼。
許多情況下，對(duì)數(shù)據(jù)庫進(jìn)行多表查詢可以使用視圖進(jìn)行聯(lián)系多表，但是不建議使用視圖進(jìn)行查詢（因?yàn)椴豢梢暂斎雲(yún)?shù)，每次查詢都需要全部查出來然后在挑選出符合條件的數(shù)據(jù)，這樣大大拖慢了查詢的速度），調(diào)用存儲(chǔ)過程進(jìn)行多表查詢的好處就是先進(jìn)性篩選然后在返回?cái)?shù)據(jù)，這樣的查詢速度在數(shù)據(jù)量很大的情況下快的不是一星半點(diǎn)。
雖然存儲(chǔ)過程的使用可以大大提高查詢速率，但是對(duì)于頻繁操作的表本人強(qiáng)烈建議添加索引，這樣效率更高。