关于Mybatis子查询嵌套问题

Question

直接上Sql代码

SELECT
    t.*,
    ( SELECT count( s.un_sid ) FROM un_star s WHERE t.un_pid = s.un_pid AND s.un_uid = #{unUid,jdbcType=INTEGER) ) AS 'skey',
    ( SELECT count( l.un_lid ) FROM un_like l WHERE t.un_pid = l.un_pid AND l.un_uid = #{unUid,jdbcType=INTEGER) ) AS 'lkey' 
FROM
    (
SELECT
    a.*,
    b.un_uname,
    b.un_uid,
    b.un_upic 
FROM
    `un_pic` a,
    `un_user` b 
WHERE
    b.un_uid = a.un_plinkid 
    AND a.un_pstatus = 0 
ORDER BY
    a.un_pid DESC 
    ) t

运行的话会报错如下图

我把

( SELECT count( s.un_sid ) FROM un_star s WHERE t.un_pid = s.un_pid AND s.un_uid = 3) AS 'skey',
( SELECT count( l.un_lid ) FROM un_like l WHERE t.un_pid = l.un_pid AND l.un_uid = 3) ) AS 'lkey' 

这两句的 #{}里面 直接换成数字ID就可以正常通过，就很纳闷，怎么才能把UID传递进去呢？

目前的mybatis里是这样写的

      <select id="selectUserHomePic"  resultMap="QueryPicList" parameterType="java.lang.Integer" >
            SELECT
                t.*,
(SELECT count( s.un_sid ) FROM un_star s WHERE t.un_pid = s.un_pid AND s.un_uid = #{unUid,jdbcType=INTEGER))  AS 'skey',
(SELECT count( l.un_lid ) FROM un_like l WHERE t.un_pid = l.un_pid AND l.un_uid = #{unUid,jdbcType=INTEGER))  AS 'lkey'
            FROM
                (
            SELECT
                a.*,
                b.un_uname,
                b.un_uid,
                b.un_upic 
            FROM
                `un_pic` a,
                `un_user` b 
            WHERE
                b.un_uid = a.un_plinkid 
                AND a.un_pstatus = 0 
            ORDER BY
                a.un_pid DESC 
                ) t
        </select>

求助

已经解决了该问题了！请遇到此类 链接多个数据表，并且传递参数的时候，要将参数放在 最外层的where去 传递， 括号()里面的select语句传递参数是获取不到的

如下

      <select id="selectUserHomePic"  resultMap="QueryPicList" parameterType="java.lang.Integer" >
      SELECT
        t.*,
(SELECT count( s.un_sid ) FROM un_star s WHERE t.un_pid = s.un_pid AND s.un_uid = b.un_uid)  AS 'skey',
(SELECT count( l.un_lid ) FROM un_like l WHERE t.un_pid = l.un_pid AND l.un_uid = b.un_uid )  AS 'lkey'
      FROM
        (
      SELECT
        a.*,
        b.un_uname,
        b.un_uid,
        b.un_upic 
      FROM
        `un_pic` a,
        `un_user` b 
      WHERE
        b.un_uid = a.un_plinkid 
        AND a.un_pstatus = 0 
      ORDER BY
        a.un_pid DESC 
        ) t  , `un_user `  b where b.un_uid = #{unUid,jdbcType=INTEGER)
    </select>

将（）内的 #{unUid,jdbcType=INTEGER) 移植到下面去

Answer 1

我是这么写的：

<resultMap type="com.web.domain.UserInfo" id="userInfoMap">
<id column="userName" jdbcType="VARCHAR" property="userName" />
<result column="password" jdbcType="VARCHAR" property="password" />
<result column="email" jdbcType="VARCHAR"  property="email" />
</resultMap>
<select id="getAllUser"     resultMap="userInfoMap">
       select userName, password,email from user_table
</select>
<select id="getUserInfoByUserName"  parameterType="java.lang.String"    resultType="com.web.domain.UserInfo">
       select userName as userName , password as password , email as email from user_table where userName = #{userName}
</select>
<insert id="insertuser">
        insert into user_table(userName,password) values(#{userName},#{password})
</insert>

通过一个接口：

public interface UserDao {
        UserInfo getUserInfoByUserName(String userName);
    List<UserInfo> getAllUser();
    void insertuser(@Param("userName")String userName,@Param("password")String password);
}

希望能对你有帮助。