三.Mybatis高级查询

桃扇骨 2022-06-15 23:28 181阅读 0赞

**一关联查询：**  
**表：**  
author: id realName userID IDCard  
jikeUse: id userName password  
visit: visitID userID visitDate visitIp

**联合查询：查询所有作者带出其用户名**

mapping:
        <resultMap id="JiKeAuthorMap" type="Author">
            <id property="id" column="author.id" />
            <result property="realName" column="realName" />
            <result property="IDCard" column="IDCard" />
            <association property="jikeUser" column="userID"
                javaType="JiKeUser">
                <id property="id" column="jikeUser.id" />
                <result property="userName" column="userName" />
                <result property="password" column="password" />
            </association>
        </resultMap>
    
        <select id="selectAuthorJoin" resultMap="JiKeAuthorMap">
            select * from author inner join jikeUser on     jikeuser.id=author.userID
        </select>

**构造查询：**

mapping：
    
    <resultMap id="JiKeAuthorMapByCon" type="Author">
            <id property="id" column="author.id" />
            <result property="realName" column="realName" />
            <result property="IDCard" column="IDCard" />
            <association property="jikeUser" column="userID" javaType="JiKeUser">
                <constructor>
                        <arg column="userName" javaType="String" />
                        <arg column="password" javaType="String" />
                </constructor>
            </association>
        </resultMap>
    
    <select id="selectAuthorJoin" resultMap="JiKeAuthorMapByCon">
            select * from author inner join jikeUser 
                on jikeuser.id=author.userID
        </select>
    
    测试：List<Author> ap=session.selectList("selectAuthorJoin");

构造查询有些参数无值

**子查询：**

mapping:
    <resultMap id="JiKeAuthorSubMap" type="Author">
            <id property="id" column="author.id" />
            <result property="realName" column="realName" />
            <result property="IDCard" column="IDCard" />
            <association property="jikeUser" column="userID"
                javaType="JiKeUser" select="findById">
            </association>
    </resultMap>
    
    <select id="selectAuthor" resultMap="JiKeAuthorSubMap">
            select * from author 
    </select>

**懒加载**可以在基本配置文件开启：

**二.集合查询**  
表：  
jikeUser:id userName password  
visit: visitID userID visitDate vistIP

JikeUser类中添加private List visitList;一对多  
mapping:

<resultMap id="visitMap" type="JiKeUser">
            <id property="id" column="id" />
            <result property="userName" column="userName" />
            <collection property="visitList" javaType="ArrayList"
                column="visitID" ofType="jike.book.pojo.Visit">
                <result property="visitID" column="visitID" />
                <result property="visitIP" column="visitIP" />
                <result property="visitDate" column="visitDate" />
            </collection>
    </resultMap>
    
    <select id="selectVisit" resultMap="visitMap">
            select * from JiKeUser inner join visit on jikeuser.id=visit.userID
    </select>

测试：

List<JiKeUser> ap=session.selectList("selectVisit"); 
    for(JiKeUser temp:ap) {
                    System.out.println("用户名="+temp.getUserName());
                    for(Visit oneVisit:temp.getVisitList())
                    {
                        System.out.println("登录时间="+oneVisit.getVisitDate()+
                                "登录IP="+oneVisit.getVisitIP());
                    }   
                }

**三.鉴别器**

**简单鉴别：**  
Id Gender Husband wife  
主键 性别 丈夫 妻子

为1给丈夫赋值，为2给妻子赋值

**复杂鉴别：**  
会员等级 等级对象  
byte UserClass

为1实例化一个普通会员，为2实例化一个高级会员，为3实例化一个VIP

Class GeneralClass extends UserClass //普通会员，等级为1 Class SeniorClass extends UserClass //高级会员,等级为2 Class VIPClass extends UserClass //VIP会员,等级为3