dobtink 2020-02-26 22:22 采纳率: 0%
浏览 1736

关于Mybatis子查询嵌套问题

直接上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) 移植到下面去

  • 写回答

1条回答 默认 最新

  • CH3OH_ 2020-02-27 16:40
    关注

    我是这么写的:

    <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);
    }
    
    

    希望能对你有帮助。

    评论

报告相同问题?

悬赏问题

  • ¥15 数学的三元一次方程求解
  • ¥20 iqoo11 如何下载安装工程模式
  • ¥15 本题的答案是不是有问题
  • ¥15 关于#r语言#的问题:(svydesign)为什么在一个大的数据集中抽取了一个小数据集
  • ¥15 C++使用Gunplot
  • ¥15 这个电路是如何实现路灯控制器的,原理是什么,怎么求解灯亮起后熄灭的时间如图?
  • ¥15 matlab数字图像处理频率域滤波
  • ¥15 在abaqus做了二维正交切削模型,给刀具添加了超声振动条件后输出切削力为什么比普通切削增大这么多
  • ¥15 ELGamal和paillier计算效率谁快?
  • ¥15 蓝桥杯单片机第十三届第一场,整点继电器吸合,5s后断开出现了问题