首页 > 数据库 > 其他综合 >

MyBatis学习总结(二)表的关联以及动态SQL

2016-09-06

实际项目中我们经常需要进行多表的联查。比较常见的有一对多,多对一以及最复杂的多对多,下面我们依次讨论。

表的关联

实际项目中我们经常需要进行多表的联查。比较常见的有一对多,多对一以及最复杂的多对多,下面我们依次讨论。

1. 一对多

以“用户”和“机构”为例:
一个“机构”中含有多个“用户”。我们想做的是:获取一个机构,然后得到这个机构下面的所有用户信息。

创建对应的表
DROP TABLE IF EXISTS `org`;
CREATE TABLE `org` (
  `orgid` varchar(255) CHARACTER SET utf8 NOT NULL,
  `orgname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`orgid`)
)

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `username` varchar(255) CHARACTER SET utf8 NOT NULL,
  `userpass` varchar(255) DEFAULT NULL,
  `orgid` varchar(255) DEFAULT NULL,
  `fullname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`username`)
) 
对应的实体类:
/**
 * user表的实体类,用户
 */
public class o2mUser {
    private String username;
    private String userpass;
    private String fullname;
    private o2mOrg org;

    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getUserpass() {
        return userpass;
    }
    public void setUserpass(String userpass) {
        this.userpass = userpass;
    }
    public String getFullname() {
        return fullname;
    }
    public void setFullname(String fullname) {
        this.fullname = fullname;
    }
    public o2mOrg getOrg() {
        return org;
    }
    public void setOrg(o2mOrg org) {
        this.org = org;
    }

}
/**
 * org表的实体类,机构
 */
public class o2mOrg {
    private String orgId;
    private String orgName;
    private List users;

    public o2mOrg() {
        super();
    }

    public o2mOrg(String orgId, String orgName) {
        super();
        this.orgId = orgId;
        this.orgName = orgName;
    }

    public String getOrgId() {
        return orgId;
    }

    public void setOrgId(String orgId) {
        this.orgId = orgId;
    }

    public String getOrgName() {
        return orgName;
    }

    public void setOrgName(String orgName) {
        this.orgName = orgName;
    }

    public List getUsers() {
        return users;
    }

    public void setUsers(List users) {
        this.users = users;
    }
}
mapper.xml文件
org里面有user的List集合,在mapper.xml文件中,用来表示。column指明外键。
这样,resultMapOrg中就既包含了org的属性,也包含了一个user集合的属性。通过执行“多表联查sql“查询后获取的字段就能一一对应的写入其中。



    
        
        

        
                
            
             
            
            
        
    

    

下面是测试代码:获取一个机构,然后获取该机构下的所有用户信息

public class o2mMain {

    private static SqlSessionFactory sqlSessionFactory;
    private static Reader reader;

    static{
        try {
            reader=Resources.getResourceAsReader("config/mybatis-config.xml");
            sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSessionFactory getSqlSessionFactory() {
        return sqlSessionFactory;
    }

    public static void main(String[] args) {
        SqlSession session=getSqlSessionFactory().openSession();
        //一对多测试
        o2mOrg org=session.selectOne("a.getOrg", "1");
        System.out.println(org.getOrgName()+":");

        List users=org.getUsers();
        for (o2mUser o2mUser : users) {
            System.out.println(o2mUser.getUsername());
        }
}

2. 多对一

仍然采用“用户”和“机构”为例,我们想要:
获取一个用户,想要知道对应的机构的信息。

mapper.xml文件
resultMap中采用来加入其他表的属性。其实从”association”的中文意思“联合”也可以知道这个标签是什么意思。
这样,resultMap便可以一一对应执行“联查sql“后获取的表的字段。



    
        
        
        
        
          
            
               
         
    

    

测试代码:获取一个用户,再获取其机构信息
//多对一测试
        o2mUser user=session.selectOne("b.getUser", "test");
        System.out.println(user.getOrg().getOrgName());

3. 多对多

一对多和多对一已经能满足我们大部分的需求了,但某些时候,我们仍然需要配置多对多。

以“学生”和“课程”为例。现在我们要获取一个学生,然后知道他选了哪些课程,然后需要知道其中一门课程有那些学生选了。有点绕……

创建对应的表:学生表,课程表以及中间表
DROP TABLE IF EXISTS `choose_course`;
CREATE TABLE `choose_course` (
  `sid` varchar(255) NOT NULL,
  `cid` varchar(255) NOT NULL,
  PRIMARY KEY (`sid`,`cid`)
) 

DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `id` int(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) 
对应的实体类
public class m2mStudent {
    private String id;
    private String name;
    private List courses;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List getCourses() {
        return courses;
    }

    public void setCourses(List courses) {
        this.courses = courses;
    }

}

public class m2mCourse {
    private int id;
    private String name;
    private List students;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List getStudents() {
        return students;
    }

    public void setStudents(List students) {
        this.students = students;
    }

}

public class m2mChooseCourse {
    private String sid;
    private String cid;

    public String getSid() {
        return sid;
    }

    public void setSid(String sid) {
        this.sid = sid;
    }

    public String getCid() {
        return cid;
    }

    public void setCid(String cid) {
        this.cid = cid;
    }

}
mapper.xml文件
多对多的配置比较复杂,三个实体类都需要配置mapper文件。

1.中间表的mapper:




    
        
        
    

    
        INSERT INTO choose_course(sid, cid)
        VALUES(#{sid},#{cid})
    

    
        
        
    

    
    

    
        
        
     

    
    

这个映射xml文件中,配置了一个用于中间表插入数据。两个将在“学生”和“课程”的mapper文件中被用到,后面详讲。

2.course的mapper:




    
        
        
    

        
        INSERT INTO course(name)
        VALUES(#{name});
    

    
        
        
        
        
    
    
        

这个映射xml文件中,配置了一个一对多的 SELECT * FROM student WHERE id=#{id}

同上,通过引用中间表mapper文件中的查询实现了一对多查询,获取了指定id的学生以及他选的课程。

测试代码:
SqlSession session=getSqlSessionFactory().openSession();
        //获取指定的学生以及其选的课程
        m2mStudent student=session.selectOne("m2mStudent.getStudent","1");
        System.out.println(student.getName()+":");
        List courses=student.getCourses();
        for (m2mCourse m2mCourse : courses) {
            System.out.println(m2mCourse.getName());
        }

        //获取该学生选中的一门课程,该课程被哪些学生选了
        m2mCourse course=session.selectOne("m2mCourse.getCourse",courses.get(0).getId());
        List students=course.getStudents();
        System.out.println(course.getName()+":");
        for (m2mStudent m2mStudent : students) {
            System.out.println(m2mStudent.getName());
        }

动态SQL

这里给出一个动态sql的模板





    
        
        
        
        
        
        
        
        
        
        
        
        
    

    Z_ROL
    ZROL_ID,ZORG_ID,ZROL_CODE,ZROL_NAME,ZROL_WARN_VALUE,ZROL_IS_DELETED,ZROL_CREATE_AT,ZROL_UPDATE_AT,ZROL_SYNC_IS_CREATED,ZROL_SYNC_LAST_STATUS,ZROL_SYNC_LAST_TIME,ZROL_SYNC_LAST_ERRCODE
    
        
            ZORG_ID = null#{organId},
            ZROL_CODE = null#{code},
            ZROL_NAME = null#{name},
            ZROL_WARN_VALUE = #{warnValue},
            ZROL_IS_DELETED = #{isDeleted},
            ZROL_UPDATE_AT =
                
                    
                        TO_TIMESTAMP(#{updateAt,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}, 'yyyy-mm-dd hh24:mi:ss.ff'),
                    
                    mysql'">
                        STR_TO_DATE(#{updateAt,typeHandler=com.bamboocloud.fw.mybatis.type.SqlDatetimeTypeHandler}, '%Y-%m-%d %H:%i:%s'),
                    
                    
                        #{updateAt,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler},
                    
                
            
            ZROL_SYNC_IS_CREATED = #{syncIsCreated},
            ZROL_SYNC_LAST_STATUS = #{syncLastStatus},
            ZROL_SYNC_LAST_TIME =
                
                    
                        TO_TIMESTAMP(#{syncLastTime,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}, 'yyyy-mm-dd hh24:mi:ss.ff'),
                    
                    
                        STR_TO_DATE(#{syncLastTime,typeHandler=com.bamboocloud.fw.mybatis.type.SqlDatetimeTypeHandler}, '%Y-%m-%d %H:%i:%s'),
                    
                    
                        #{syncLastTime,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler},
                    
                
            
            ZROL_SYNC_LAST_ERRCODE = null#{syncLastErrcode},
        
    
    
        
            AND ZROL_ID = #{id}
            AND ZORG_ID = #{organId}
            AND ZROL_CODE = #{code}
            AND ZROL_NAME = #{name}
        
    
    
        
            
                AND ZROL_ID = #{filter.id_eq}
                AND ZROL_ID != #{filter.id_ne}
                AND ZROL_ID in #{i}
                AND ZROL_ID not in #{i}
                AND ZORG_ID = #{filter.organId_eq}
                AND ZORG_ID != #{filter.organId_ne}
                AND ZORG_ID in #{i}
                AND ZORG_ID not in #{i}
                AND ZROL_CODE = #{filter.code_eq}
                AND lower(ZROL_CODE) LIKE lower(#{filter.code_like})
                AND ZROL_CODE in #{i}
                AND (ZROL_CODE IS NULL OR ZROL_CODE not in #{i})
                AND ZROL_NAME = #{filter.name_eq}
                AND lower(ZROL_NAME) LIKE lower(#{filter.name_like})
                AND ZROL_WARN_VALUE = #{filter.warnValue_eq}
                AND ZROL_IS_DELETED = #{filter.isDeleted_eq}
                AND ZROL_CREATE_AT >=
                    
                        
                            TO_TIMESTAMP(#{filter.createAt_gte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}, 'yyyy-mm-dd hh24:mi:ss.ff')
                        
                        
                            STR_TO_DATE(#{filter.createAt_gte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlDatetimeTypeHandler}, '%Y-%m-%d %H:%i:%s')
                        
                        
                            #{filter.createAt_gte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}
                        
                    
                
                AND ZROL_CREATE_AT <= 
                    
                        
                            TO_TIMESTAMP(#{filter.createAt_lte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}, &#39;yyyy-mm-dd hh24:mi:ss.ff&#39;)
                        
                        
                            STR_TO_DATE(#{filter.createAt_lte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlDatetimeTypeHandler}, &#39;%Y-%m-%d %H:%i:%s&#39;)
                        
                        
                            #{filter.createAt_lte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}
                        
                    
                
                AND ZROL_UPDATE_AT >= 
                    
                        
                            TO_TIMESTAMP(#{filter.updateAt_gte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}, &#39;yyyy-mm-dd hh24:mi:ss.ff&#39;)
                        
                        
                            STR_TO_DATE(#{filter.updateAt_gte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlDatetimeTypeHandler}, &#39;%Y-%m-%d %H:%i:%s&#39;)
                        
                        
                            #{filter.updateAt_gte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}
                        
                    
                
                AND ZROL_UPDATE_AT <= 
                    
                        
                            TO_TIMESTAMP(#{filter.updateAt_lte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}, &#39;yyyy-mm-dd hh24:mi:ss.ff&#39;)
                        
                        
                            STR_TO_DATE(#{filter.updateAt_lte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlDatetimeTypeHandler}, &#39;%Y-%m-%d %H:%i:%s&#39;)
                        
                        
                            #{filter.updateAt_lte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}
                        
                    
                
                AND ZROL_SYNC_LAST_STATUS = #{filter.syncLastStatus_eq}
                AND (${filter.WHERE_SQL_custom})
                AND ZROL_ID in (select ZROL_ID from Z_GRP_ROL where ZGRP_ID =#{filter._groupId_eq} and ZROL_TYPE=#{filter._roleIdType_eq} and ZROL_IS_DELETED = 0)
                AND ZROL_ID in (select ZROL_ID from Z_GRP_ROL where ZGRP_ID =#{filter._groupId_eq})
            
        
    
    
        
            
                
            
            
                
            
        
    
    
        
            ORDER BY ZROL_NAME ASC
        
        
            
                
                    ZROL_ID ${val},
                    ZROL_CODE ${val},
                    ZROL_NAME ${val},
                    ZROL_WARN_VALUE ${val},
                    ZROL_IS_DELETED ${val},
                    ZROL_CREATE_AT ${val},
                    ZROL_UPDATE_AT ${val},
                    ZROL_SYNC_IS_CREATED ${val},
                    ZROL_SYNC_LAST_STATUS ${val},
                    ZROL_SYNC_LAST_TIME ${val},
                
            
        
    

    
        INSERT INTO  (
            
        ) VALUES (
            
                null#{id}, 
                null#{organId}, 
                null#{code}, 
                null#{name}, 
                #{warnValue}, 
                #{isDeleted},
                
                    
                        TO_TIMESTAMP(#{createAt,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}, &#39;yyyy-mm-dd hh24:mi:ss.ff&#39;),
                    
                    
                        STR_TO_DATE(#{createAt,typeHandler=com.bamboocloud.fw.mybatis.type.SqlDatetimeTypeHandler}, &#39;%Y-%m-%d %H:%i:%s&#39;),
                    
                    
                        #{createAt,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler},
                    
                
                
                    
                        TO_TIMESTAMP(#{updateAt,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}, &#39;yyyy-mm-dd hh24:mi:ss.ff&#39;),
                    
                    
                        STR_TO_DATE(#{updateAt,typeHandler=com.bamboocloud.fw.mybatis.type.SqlDatetimeTypeHandler}, &#39;%Y-%m-%d %H:%i:%s&#39;),
                    
                    
                        #{updateAt,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler},
                    
                
                #{syncIsCreated},
                #{syncLastStatus},
                #{syncLastTime},
                #{syncLastErrcode}
            
        )
    

    
        UPDATE  SET 
        WHERE ZROL_ID = #{id}
    

    
        DELETE FROM  
        WHERE ZROL_ID = #{id}
    

    

    

    

    

    
        UPDATE  SET 
        
    

    
        DELETE FROM  
        
    


相关文章
最新文章
热点推荐