前言
因为懒惰,在一个查询中,一个表被联查了两次,提供一种不需要再业务层进行二次调用的方式。
提示:以下是本篇文章正文内容,下面案例可供参考
一、Mybatis是什么?
各位"看官"移步官网啦!! https://mybatis.org/mybatis-3/zh/index.html。
二、使用步骤
1.方式一代码如下
1.1Java类 1->n(1对多,1对1)
public class Project extends BaseEntity{
private static final long serialVersionUID = 1L;
private Long projectId;
/** 项目名称 */
@Excel(name = "项目名称")
private String projectName;
/** 所属部门id */
@Excel(name = "所属部门id")
private Long deptId;
private Long constrDeptId;
/** 所属部门*/
private Dept dept;
private Dept constructionTeam;
/**项目阶段*/
private List<ProjectStage> projectStages = new ArrayList<>();
/** -1:未开始,0:已开始,1:已结束 */
@Excel(name = "-1:未开始,0:已开始,1:已结束")
private String status;
/** 计划开始时间 */
@JsonFormat(pattern = "yyyy-MM-dd")
@Excel(name = "计划开始时间", width = 30, dateFormat = "yyyy-MM-dd")
private Date planStartTime;
/** 实际开始时间 */
@JsonFormat(pattern = "yyyy-MM-dd")
@Excel(name = "实际开始时间", width = 30, dateFormat = "yyyy-MM-dd")
private Date actualStartTime;
/** 计划结束时间 */
@JsonFormat(pattern = "yyyy-MM-dd")
@Excel(name = "计划结束时间", width = 30, dateFormat = "yyyy-MM-dd")
private Date planEndTime;
/** 实际结束时间 */
@JsonFormat(pattern = "yyyy-MM-dd")
@Excel(name = "实际结束时间", width = 30, dateFormat = "yyyy-MM-dd")
private Date actualEndTime;
}
1.1.1重点标注
1.2 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.project.mapper.ProjectMapper">
<resultMap type="com.project.model.Project" id="ProjectResult">
<result property="projectId" column="project_id" />
<result property="projectName" column="project_name" />
<result property="deptId" column="dept_id" />
<result property="constrDeptId" column="constr_dept_id" />
<result property="status" column="status" />
<result property="planStartTime" column="plan_start_time" />
<result property="actualStartTime" column="actual_start_time" />
<result property="planEndTime" column="plan_end_time" />
<result property="actualEndTime" column="actual_end_time" />
<result property="delFlag" column="del_flag" />
<result property="createBy" column="create_by" />
<result property="createTime" column="create_time" />
<result property="updateBy" column="update_by" />
<result property="updateTime" column="update_time" />
<result property="remark" column="remark" />
<association property="dept" column="dept_id" javaType="com.project.model.Project" resultMap="DeptResult1"/>
<association property="constructionTeam" column="constr_dept_id" javaType="com.project.model.Project" resultMap="DeptResult2"/>
<collection property="projectStages" javaType="java.util.List" resultMap="ProjectStageResult"/>
</resultMap>
<resultMap type="com.soldier.model.Dept" id="DeptResult1">
<result property="deptId" column="dept_id" />
<result property="parentId" column="parent_id" />
<result property="ancestors" column="ancestors" />
<result property="deptName" column="dept_name" />
<result property="orderNum" column="order_num" />
<result property="leader" column="leader" />
<result property="phone" column="phone" />
<result property="email" column="email" />
<result property="status" column="status" />
</resultMap>
<resultMap type="com.soldier.model.Dept" id="DeptResult2">
<result property="deptId" column="team_dept_id" />
<result property="parentId" column="team_parent_id" />
<result property="ancestors" column="team_ancestors" />
<result property="deptName" column="team_dept_name" />
<result property="orderNum" column="team_order_num" />
<result property="leader" column="team_leader" />
<result property="phone" column="team_phone" />
<result property="email" column="team_email" />
<result property="status" column="team_status" />
</resultMap>
<resultMap type="com.project.model.ProjectStage" id="ProjectStageResult">
<result property="proStageId" column="pro_stage_id" />
<result property="proStageName" column="pro_stage_name" />
<result property="projectId" column="project_id" />
<result property="constrDeptId" column="constr_dept_id" />
<result property="status" column="status" />
<!-- <result property="planStartTime" column="plan_start_time" />
<result property="actualStartTime" column="actual_start_time" />
<result property="planEndTime" column="plan_end_time" />
<result property="actualEndTime" column="actual_end_time" />-->
</resultMap>
<sql id="selectProjectVo">
select project_id, project_name, dept_id, constr_dept_id, status, plan_start_time, actual_start_time, plan_end_time, actual_end_time, del_flag, create_by, create_time, update_by, update_time, remark from _project
</sql>
<sql id="selectProjectVo2">
select project_id, project_name, dept_id, constr_dept_id, status, plan_start_time,
actual_start_time, plan_end_time, actual_end_time, del_flag, create_by, create_time,
update_by, update_time, remark,
d.dept_name,ur.dept_name as team_dept_name,r.pro_stage_name,r.pro_stage_name,r.constr_dept_id
from _project
left join _dept d on dept_id = d.dept_id
left join _dept ur on constr_dept_id = ur.dept_id
left join _project_stage r on project_id = r.project_id
</sql>
<select id="selectProjectList" parameterType="com.project.model.Project" resultMap="ProjectResult">
<include refid="selectProjectVo2"/>
<where>
<if test="projectName != null and projectName != ''"> and project_name like concat('%', #{projectName}, '%')</if>
<if test="deptId != null "> and dept_id = #{deptId}</if>
<if test="constrDeptId != null "> and constr_dept_id = #{constrDeptId}</if>
<if test="status != null and status != ''"> and status = #{status}</if>
<if test="planStartTime != null "> and plan_start_time = #{planStartTime}</if>
<if test="actualStartTime != null "> and actual_start_time = #{actualStartTime}</if>
<if test="planEndTime != null "> and plan_end_time = #{planEndTime}</if>
<if test="actualEndTime != null "> and actual_end_time = #{actualEndTime}</if>
</where>
</select>
<select id="selectProjectById" parameterType="Long" resultMap="ProjectResult">
<include refid="selectProjectVo"/>
where project_id = #{projectId}
</select>
<insert id="insertProject" parameterType="com.project.model.Project">
insert into _project
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="projectId != null">project_id,</if>
<if test="projectName != null">project_name,</if>
<if test="deptId != null">dept_id,</if>
<if test="constrDeptId != null">constr_dept_id,</if>
<if test="status != null">status,</if>
<if test="planStartTime != null">plan_start_time,</if>
<if test="actualStartTime != null">actual_start_time,</if>
<if test="planEndTime != null">plan_end_time,</if>
<if test="actualEndTime != null">actual_end_time,</if>
<if test="delFlag != null">del_flag,</if>
<if test="createBy != null">create_by,</if>
<if test="createTime != null">create_time,</if>
<if test="updateBy != null">update_by,</if>
<if test="updateTime != null">update_time,</if>
<if test="remark != null">remark,</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="projectId != null">#{projectId},</if>
<if test="projectName != null">#{projectName},</if>
<if test="deptId != null">#{deptId},</if>
<if test="constrDeptId != null">#{constrDeptId},</if>
<if test="status != null">#{status},</if>
<if test="planStartTime != null">#{planStartTime},</if>
<if test="actualStartTime != null">#{actualStartTime},</if>
<if test="planEndTime != null">#{planEndTime},</if>
<if test="actualEndTime != null">#{actualEndTime},</if>
<if test="delFlag != null">#{delFlag},</if>
<if test="createBy != null">#{createBy},</if>
<if test="createTime != null">#{createTime},</if>
<if test="updateBy != null">#{updateBy},</if>
<if test="updateTime != null">#{updateTime},</if>
<if test="remark != null">#{remark},</if>
</trim>
</insert>
<update id="updateProject" parameterType="com.project.model.Project">
update _project
<trim prefix="SET" suffixOverrides=",">
<if test="projectName != null">project_name = #{projectName},</if>
<if test="deptId != null">dept_id = #{deptId},</if>
<if test="constrDeptId != null">constr_dept_id = #{constrDeptId},</if>
<if test="status != null">status = #{status},</if>
<if test="planStartTime != null">plan_start_time = #{planStartTime},</if>
<if test="actualStartTime != null">actual_start_time = #{actualStartTime},</if>
<if test="planEndTime != null">plan_end_time = #{planEndTime},</if>
<if test="actualEndTime != null">actual_end_time = #{actualEndTime},</if>
<if test="delFlag != null">del_flag = #{delFlag},</if>
<if test="createBy != null">create_by = #{createBy},</if>
<if test="createTime != null">create_time = #{createTime},</if>
<if test="updateBy != null">update_by = #{updateBy},</if>
<if test="updateTime != null">update_time = #{updateTime},</if>
<if test="remark != null">remark = #{remark},</if>
</trim>
where project_id = #{projectId}
</update>
<delete id="deleteProjectById" parameterType="Long">
delete from _project where project_id = #{projectId}
</delete>
<delete id="deleteProjectByIds" parameterType="String">
delete from _project where project_id in
<foreach item="projectId" collection="array" open="(" separator="," close=")">
#{projectId}
</foreach>
</delete>
</mapper>
1.2.1重点标注
要将你所需要的字段清楚的写出来,并且名相同&&值不同的字段要起别名;
如果你只想要某个表的name/特殊字段,不想用对象封装,也可以用这种方式:
2.方式二代码如下:
代码如下(示例):
2.1java:
public class Project extends BaseEntity{
private static final long serialVersionUID = 1L;
/** $column.columnComment */
private Long projectId;
/** 项目名称 */
@Excel(name = "项目名称")
private String projectName;
/** 所属部门id */
@Excel(name = "所属部门id")
private Long deptId;
private Long constrDeptId;
private String deptName;
private String teamDeptName;
/** 所属部门*/
private Dept dept;
private Dept constructionTeam;
/**项目阶段*/
private List<ProjectStage> projectStages = new ArrayList<>();
/** -1:未开始,0:已开始,1:已结束 */
@Excel(name = "-1:未开始,0:已开始,1:已结束")
private String status;
/** 计划开始时间 */
@JsonFormat(pattern = "yyyy-MM-dd")
@Excel(name = "计划开始时间", width = 30, dateFormat = "yyyy-MM-dd")
private Date planStartTime;
/** 实际开始时间 */
@JsonFormat(pattern = "yyyy-MM-dd")
@Excel(name = "实际开始时间", width = 30, dateFormat = "yyyy-MM-dd")
private Date actualStartTime;
/** 计划结束时间 */
@JsonFormat(pattern = "yyyy-MM-dd")
@Excel(name = "计划结束时间", width = 30, dateFormat = "yyyy-MM-dd")
private Date planEndTime;
/** 实际结束时间 */
@JsonFormat(pattern = "yyyy-MM-dd")
@Excel(name = "实际结束时间", width = 30, dateFormat = "yyyy-MM-dd")
private Date actualEndTime;
}
2.2 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.project.mapper.ProjectMapper">
<resultMap type="com.project.model.Project" id="ProjectResult">
<result property="projectId" column="project_id" />
<result property="projectName" column="project_name" />
<result property="deptId" column="dept_id" />
<result property="constrDeptId" column="constr_dept_id" />
<result property="status" column="status" />
<result property="planStartTime" column="plan_start_time" />
<result property="actualStartTime" column="actual_start_time" />
<result property="planEndTime" column="plan_end_time" />
<result property="actualEndTime" column="actual_end_time" />
<result property="delFlag" column="del_flag" />
<result property="createBy" column="create_by" />
<result property="createTime" column="create_time" />
<result property="updateBy" column="update_by" />
<result property="updateTime" column="update_time" />
<result property="deptName" column="dept_name" />
<result property="teamDeptName" column="team_dept_name" />
<collection property="projectStages" javaType="java.util.List" resultMap="ProjectStageResult"/>
</resultMap>
<resultMap type="com.project.model.ProjectStage" id="ProjectStageResult">
<result property="proStageId" column="pro_stage_id" />
<result property="proStageName" column="pro_stage_name" />
<result property="projectId" column="project_id" />
<result property="constrDeptId" column="constr_dept_id" />
<result property="status" column="status" />
</resultMap>
<sql id="selectProjectVo">
select project_id, project_name, dept_id, constr_dept_id, status, plan_start_time,
actual_start_time, plan_end_time, actual_end_time, del_flag, create_by, create_time,
update_by, update_time,
d.dept_name, ur.dept_name as team_dept_name
from _project
left join _dept d on dept_id = d.dept_id
left join _dept ur on constr_dept_id = ur.dept_id
left join _project_stage r on project_id = r.project_id
</sql>
</mapper>
总结
- 一对一/一对多可以将对象封装到对象中,直接通过多表联查查询;
- 也可以将需要的属性拿出来,通过< resultMap > 映射出来
- 您也可以构造一个VO对象,然后在业务层多次调用数据库,构造出前端需要的数据;
- 尽量不要懒惰哦!
作者:6Apricity6
链接地址:https://blog.csdn.net/weixin_42096620/article/details/110393971
来源:CSDN