67,515
社区成员
发帖
与我相关
我的任务
分享
<select id="selectWorksForUser" parameterType="com.zhuoxun.entity.Work" resultMap="workResultMap">
SELECT
c.work_id,
c.work_content,
c.work_demand,
c.work_create_time,
c.work_last_edit_time,
c.work_comp_cycle,
c.deadline,
c.work_leader_user_id,
c.work_join_emp,
c.work_progress,
c.work_percent,
c.notes,
c.user_id,
c.state,
b.user_id,
b.user_name,
b.role_id,
b.dept_id,
b.reg_time,
b.state,
a.user_id,
a.user_name as leader,
a.role_id,
a.dept_id,
a.reg_time,
a.state,
dept.dept_id,
dept.dept_name,
dept.dept_leder_name,
b.user_passwd,
role.role_id,
role.role_name
FROM
work c
INNER JOIN user_info a ON c.work_leader_user_id = a.user_id
INNER JOIN user_info b ON c.user_id = b.user_id
INNER JOIN dept ON b.dept_id = dept.dept_id
INNER JOIN role ON b.role_id = role.role_id
WHERE c.user_id >= #{user.user_id} OR c.work_leader_user_id = #{user_work_leader.user_id} AND c.state = #{state}
ORDER BY c.work_id
</select>
public class Work {
private Integer work_id;
private String work_order;
private String work_content;
private String work_demand;
private Timestamp work_create_time;
private Timestamp work_last_edit_time;
private Integer work_comp_cycle;
private Timestamp deadline;
private User user_work_leader = new User(); //任务负责人
private String work_join_emp;
private String work_progress;
private Integer work_percent;
private String notes;
private User user = new User(); //任务发布者
private String image;
private Integer state;
Work work = new Work();
//0为未完成的任务
work.setState(0);
work.getUser_work_leader().setUser_id(5);
work.setUser(user);
PageHelper.offsetPage(startPage, pageSize);
works = workService.selectWorksForUser(work);
<resultMap id="workResultMap" type="com.zhuoxun.entity.Work">
<id column="work_id" property="work_id"/>
<result column="work_order" property="work_order"/>
<result column="work_content" property="work_content"/>
<result column="work_demand" property="work_demand"/>
<result column="work_create_time" property="work_create_time"/>
<result column="work_last_edit_time" property="work_last_edit_time"/>
<result column="work_comp_cycle" property="work_comp_cycle"/>
<result column="deadline" property="deadline"/>
<result column="work_join_emp" property="work_join_emp"/>
<result column="work_progress" property="work_progress"/>
<result column="work_percent" property="work_percent"/>
<result column="notes" property="notes"/>
<result column="image" property="image"/>
<result column="state" property="state"/>
<association property="user_work_leader" javaType="com.zhuoxun.entity.User">
<id column="a_user_id" property="user_id"/>
<result column="a_user_name" property="user_name"/>
<result column="a_user_passwd" property="user_passwd"/>
<result column="a_reg_time" property="user_reg_time"/>
<result column="a_state" property="user_state"/>
<association property="role" javaType="com.zhuoxun.entity.Role">
<id column="a_role_id" property="role_id"/>
<result column="a_role_name" property="role_name"/>
</association>
<association property="dept" javaType="com.zhuoxun.entity.Dept">
<id column="a_dept_id" property="dept_id"/>
<result column="a_dept_name" property="dept_name"/>
<result column="a_dept_leder_name" property="dept_leder_name"/>
</association>
</association>
<association property="user" javaType="com.zhuoxun.entity.User">
<id column="b_user_id" property="user_id"/>
<result column="b_user_name" property="user_name"/>
<result column="b_user_passwd" property="user_passwd"/>
<result column="b_reg_time" property="user_reg_time"/>
<result column="b_state" property="user_state"/>
<association property="role" javaType="com.zhuoxun.entity.Role">
<id column="b_role_id" property="role_id"/>
<result column="b_role_name" property="role_name"/>
</association>
<association property="dept" javaType="com.zhuoxun.entity.Dept">
<id column="b_dept_id" property="dept_id"/>
<result column="b_dept_name" property="dept_name"/>
<result column="b_dept_leder_name" property="dept_leder_name"/>
</association>
</association>
</resultMap>
这是SQL语句
<select id="selectWorksForUser" parameterType="com.zhuoxun.entity.Work" resultMap="workResultMap">
SELECT
c.work_id,
c.work_content,
c.work_demand,
c.work_create_time,
c.work_last_edit_time,
c.work_comp_cycle,
c.deadline,
c.work_leader_user_id,
c.work_join_emp,
c.work_progress,
c.work_percent,
c.notes,
c.user_id,
c.state,
b.user_id b_user_id,
b.user_name b_user_name,
b.role_id b_role_id,
b.dept_id b_dept_id,
b.reg_time b_reg_time,
b.state b_state,
a.user_id a_user_id,
a.user_name a_user_name,
a.role_id a_role_id,
a.dept_id a_dept_id,
a.reg_time a_reg_time,
a.state a_state,
dept.dept_id,
dept.dept_name,
dept.dept_leder_name,
b.user_passwd,
role.role_id,
role.role_name
FROM
work c
INNER JOIN user_info a ON c.work_leader_user_id = a.user_id
INNER JOIN user_info b ON c.user_id = b.user_id
INNER JOIN dept ON b.dept_id = dept.dept_id
INNER JOIN role ON b.role_id = role.role_id
WHERE c.user_id >= #{user.user_id} OR c.work_leader_user_id = #{user_work_leader.user_id} AND c.state = #{state}
ORDER BY c.work_id
</select>
<resultMap id="workResultMap" type="com.zhuoxun.entity.Work">
<id column="work_id" property="work_id"/>
<result column="work_order" property="work_order"/>
<result column="work_content" property="work_content"/>
<result column="work_demand" property="work_demand"/>
<result column="work_create_time" property="work_create_time"/>
<result column="work_last_edit_time" property="work_last_edit_time"/>
<result column="work_comp_cycle" property="work_comp_cycle"/>
<result column="deadline" property="deadline"/>
<result column="work_join_emp" property="work_join_emp"/>
<result column="work_progress" property="work_progress"/>
<result column="work_percent" property="work_percent"/>
<result column="notes" property="notes"/>
<result column="image" property="image"/>
<result column="state" property="state"/>
<association property="user_work_leader" javaType="com.zhuoxun.entity.User">
<id column="user_id" property="user_id"/>
<result column="user_name" property="user_name"/>
<result column="user_passwd" property="user_passwd"/>
<association property="role" javaType="com.zhuoxun.entity.Role">
<id column="role_id" property="role_id"/>
<result column="role_name" property="role_name"/>
</association>
<association property="dept" javaType="com.zhuoxun.entity.Dept">
<id column="dept_id" property="dept_id"/>
<result column="dept_name" property="dept_name"/>
<result column="dept_leder_name" property="dept_leder_name"/>
</association>
</association>
<association property="user" javaType="com.zhuoxun.entity.User">
<id column="user_id" property="user_id"/>
<result column="user_name" property="user_name"/>
<result column="user_passwd" property="user_passwd"/>
<association property="role" javaType="com.zhuoxun.entity.Role">
<id column="role_id" property="role_id"/>
<result column="role_name" property="role_name"/>
</association>
<association property="dept" javaType="com.zhuoxun.entity.Dept">
<id column="dept_id" property="dept_id"/>
<result column="dept_name" property="dept_name"/>
<result column="dept_leder_name" property="dept_leder_name"/>
</association>
</association>
</resultMap>
<resultMap id="workResultMap" type="com.zhuoxun.entity.Work">
<id column="work_id" property="work_id"/>
<result column="work_order" property="work_order"/>
<result column="work_content" property="work_content"/>
<result column="work_demand" property="work_demand"/>
<result column="work_create_time" property="work_create_time"/>
<result column="work_last_edit_time" property="work_last_edit_time"/>
<result column="work_comp_cycle" property="work_comp_cycle"/>
<result column="deadline" property="deadline"/>
<result column="work_join_emp" property="work_join_emp"/>
<result column="work_progress" property="work_progress"/>
<result column="work_percent" property="work_percent"/>
<result column="notes" property="notes"/>
<result column="image" property="image"/>
<result column="state" property="state"/>
<association property="user_work_leader" javaType="com.zhuoxun.entity.User">
<id column="user_id" property="user_id"/>
<result column="user_name" property="user_name"/>
<result column="user_passwd" property="user_passwd"/>
<association property="role" javaType="com.zhuoxun.entity.Role">
<id column="role_id" property="role_id"/>
<result column="role_name" property="role_name"/>
</association>
<association property="dept" javaType="com.zhuoxun.entity.Dept">
<id column="dept_id" property="dept_id"/>
<result column="dept_name" property="dept_name"/>
<result column="dept_leder_name" property="dept_leder_name"/>
</association>
</association>
<association property="user" javaType="com.zhuoxun.entity.User">
<id column="user_id" property="user_id"/>
<result column="user_name" property="user_name"/>
<result column="user_passwd" property="user_passwd"/>
<association property="role" javaType="com.zhuoxun.entity.Role">
<id column="role_id" property="role_id"/>
<result column="role_name" property="role_name"/>
</association>
<association property="dept" javaType="com.zhuoxun.entity.Dept">
<id column="dept_id" property="dept_id"/>
<result column="dept_name" property="dept_name"/>
<result column="dept_leder_name" property="dept_leder_name"/>
</association>
</association>
</resultMap>