首先说明一点,这个设计完全没有问题的,一般金融项目经常这样设计,比如合同表中的借款人和担保人都指向用户表。
假如我们现在有两张表:
一个用户表、一个任务表
user:
用户Id:id
用户名:username
task:
id
任务名:taskname
任务创建者:taskCreateId---------->关联用户Id
任务负责人:taskFunctionaryId---------->关联用户Id
首先在task表的实体类中增加两个属性,以便查询用户信息
private User functionary;
private User initiator;
接下来就可以写mybatis映射了:
<resultMap type="user" id="userRusultMap">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="sex" column="sex"/>
<result property="email" column="email"/>
<result property="phone" column="phone"/>
<result property="remark" column="remark"/>
</resultMap>
<resultMap type="ProjectTasks" id="project_Tasks_resultMap">
<id property="id" column="id" />
<result property="project_task_name" column="project_task_name" />
<result property="project_task_type" column="project_task_type" />
<result property="project_task_state" column="project_task_state" />
<result property="project_task_start_date" column="project_task_start_date" />
<result property="project_task_end_date" column="project_task_end_date" />
<result property="ready" column="ready" />
<result property="project_task_result" column="project_task_result" />
<result property="project_task_create_time" column="project_task_create_time" />
<result property="project_task_description" column="project_task_description" />
<association property="functionary" column="project_task_functionaryId" select="getUserById"></association>
<association property="initiator" column="project_task_creatorId" select="getUserById"></association>
</resultMap>
<select id="getUserById" parameterType="int" resultMap="userRusultMap">
select * from justsafe_user where id=#{id}
</select>
mybatis这样写绝壁没问题,接下来在搞sql语句:
这样两个用户名就都查询出来了,为了方便复制粘贴还是放下sql语句吧,方便:
SELECT juser.username,juser1.username FROM (SELECT * FROM justsafe_user) AS juser
RIGHT JOIN justsafe_project_tasks tasks ON juser.id = tasks.project_task_functionaryId LEFT JOIN
justsafe_user juser1 ON juser1.id = tasks.project_task_creatorId
根据自己的需求改成自己的表就可以了!!!
ok打完收工,刚好也该下班了!!!