博客

Personalreport 实体中不用写外键，直接把整个实体注入如下Userinfo，Department ，

private Userinfo userinfo;

private Department department;

package com.jykj.dmms.entity;

import java.util.Date;

public class Personalreport {
    private Integer id;

    private String content;

    private Date createtime;

    private String effectiveduration;

    private String duration;

    private String verification;

    private String workproduct;

    private String workappraise;

    private String expertappraise;

    private Integer userid;

    private Integer departid;

    private String ex3;

    private String username;
    
    
    private Userinfo userinfo;
    
    private Department department;
    

	public Userinfo getUserinfo() {
		return userinfo;
	}

	public void setUserinfo(Userinfo userinfo) {
		this.userinfo = userinfo;
	}

	public Department getDepartment() {
		return department;
	}

	public void setDepartment(Department department) {
		this.department = department;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content == null ? null : content.trim();
    }

    public Date getCreatetime() {
        return createtime;
    }

    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }

    public String getEffectiveduration() {
        return effectiveduration;
    }

    public void setEffectiveduration(String effectiveduration) {
        this.effectiveduration = effectiveduration == null ? null : effectiveduration.trim();
    }

    public String getDuration() {
        return duration;
    }

    public void setDuration(String duration) {
        this.duration = duration == null ? null : duration.trim();
    }

    public String getVerification() {
        return verification;
    }

    public void setVerification(String verification) {
        this.verification = verification == null ? null : verification.trim();
    }

    public String getWorkproduct() {
        return workproduct;
    }

    public void setWorkproduct(String workproduct) {
        this.workproduct = workproduct == null ? null : workproduct.trim();
    }

    public String getWorkappraise() {
        return workappraise;
    }

    public void setWorkappraise(String workappraise) {
        this.workappraise = workappraise == null ? null : workappraise.trim();
    }

    public String getExpertappraise() {
        return expertappraise;
    }

    public void setExpertappraise(String expertappraise) {
        this.expertappraise = expertappraise == null ? null : expertappraise.trim();
    }

    public Integer getUserid() {
        return userid;
    }

    public void setUserid(Integer userid) {
        this.userid = userid;
    }

    public Integer getDepartid() {
        return departid;
    }

    public void setDepartid(Integer departid) {
        this.departid = departid;
    }

    public String getEx3() {
        return ex3;
    }

    public void setEx3(String ex3) {
        this.ex3 = ex3 == null ? null : ex3.trim();
    }
}

PersonReportDao 层@Param注解使用情况：

1.当xml查询方法没有可以不添加注解

2.当如果有多个参数是必须加@Param

3.单个参数可以不用@Param注解

List<Personalreport> queryPersonReport(@Param("personCondition") Personalreport personReportEntity,@Param("beginTime") String beginTime,@Param("endTime") String endTime);

PersonReportDao.xml

depart_id，person_id是查询询语句出来的列的别名 （真实dapart的id）,这里取别名就是在SQL中取别名一个意思

association 是针对一对一的表

conllection是针对一对多或多对多表

sql DATE_FORMAT方法用于字符串转指定时间格式进行比较（本来可以用between and 但是考虑到有可能开始结束时间直选一个所以写了两,用于判断开始结束时间）

<?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.jykj.dmms.dao.PersonReportDao">
	    <resultMap id="personReportMap" type="com.jykj.dmms.entity.Personalreport">
	        <id column="id" property="id"/>
	        <result column="content" property="content"/>
	        <result column="createtime" property="createtime"/>
	        <result column="effectiveduration" property="effectiveduration"/>
	        <result column="duration" property="duration"/>
	        <result column="verification" property="verification"/>
	        <result column="workproduct" property="workproduct"/>
	        <result column="workappraise" property="workappraise"/>
	        <result column="expertappraise" property="expertappraise"/>
	        <association property="department" 
	        javaType="com.jykj.dmms.entity.Department">
	        <!-- depart_id是查询询语句中取的别名 （真实为id） -->
	          <id column="depart_id" property="id"/>
	          <result column="name" property="name"/>
	          <result column="leader" property="leader"/>
	          <result column="ex" property="ex"/>
	        </association>
	        <association property="userinfo" 
	        javaType="com.jykj.dmms.entity.Userinfo">
	        <!-- person_id是查询询语句中取的别名 （真实为id） -->
	          <id column="person_id" property="id"/>
	          <result column="username" property="username"/>
	          <result column="password" property="password"/>
	          <result column="realname" property="realname"/>
	          <result column="roleid" property="roleid"/>
	          <result column="delflag" property="delflag"/>
	        </association>
	    </resultMap>
	    <select id="queryPersonReport" resultMap="personReportMap">
	        SELECT 
	        r.id,
	        r.content,
	        r.createtime,
	        r.effectiveduration,
	        r.duration,
	        r.verification,
	        r.workproduct,
	        r.workappraise,
	        r.expertappraise,
	        d.name,
	        d.id depart_id,
	        u.realname,
	        u.id person_id
	        FROM
	        personalreport r
	        INNER JOIN department d
	        ON r.departid = d.id
	        INNER JOIN userinfo u
	        ON r.userid = u.id
	        <where>
	         <if test="personCondition != null and personCondition.userid !=null 
              and personCondition.userid > 0">
              and r.userid = #{personCondition.userid}
              </if>
              <if test="personCondition != null and personCondition.verification !=null 
              and personCondition.verification != ''">
              and r.verification = #{personCondition.verification}
              </if>
              <if test="beginTime != null">
              <!-- and r.createtime &gt;= #{beginTime} -->
              and DATE_FORMAT(r.createtime,'%Y-%m-%d') &gt;= #{beginTime}
              </if>
              <if test="endTime != null">
              <!-- sql中查询Date的时间范围语句 -->
              and DATE_FORMAT(r.createtime,'%Y-%m-%d') &lt;= #{endTime}
              </if>
              ORDER BY r.createtime DESC	    
	        </where>	
	    </select>
	    <select id="queryPersonReportCount" resultType="int">
	         SELECT
		     count(1)
		     FROM
		        personalreport r
		        INNER JOIN department d
		        ON r.departid = d.id
		        INNER JOIN userinfo u
		        ON r.userid = u.id
		        <where>
		         <if test="personCondition != null and personCondition.userid !=null 
	              and personCondition.userid > 0">
	              and r.userid = #{personCondition.userid}
	              </if>
	              <if test="personCondition != null and personCondition.verification !=null 
	              and personCondition.verification != ''">
	              and r.verification = #{personCondition.verification}
	              </if>
	              <if test="beginTime != null">
	              <!-- and r.createtime &gt;= #{beginTime} -->
	              and DATE_FORMAT(r.createtime,'%Y-%m-%d') &gt;= #{beginTime}
	              </if>
	              <if test="endTime != null">
	              <!-- and r.createtime &lt;= #{endTime} -->
	              and DATE_FORMAT(r.createtime,'%Y-%m-%d') &lt;= #{endTime}
	              </if>	    
		        </where>	
	    </select>
    </mapper>