mybatis之多表关联查询

喜欢ヅ旅行 2022-01-07 09:43 321阅读 0赞

###### 多表关联查询 ######

> resultMap通常用于比较复杂的结果集映射(如：多表关联查询)的情况，使用步骤如下：

1.  显示定义标签映射结果集
    
        <resultMap id="userResultMap" type="User">
          <id property="id" column="user_id" />
          <result property="username" column="user_name"/>
          <result property="password" column="hashed_password"/>
        </resultMap>
2.  在查询语句中引用我们定义的resultMap：
    
        <select id="selectUsers" resultMap="userResultMap">
          select user_id, user_name, hashed_password
          from some_table
          where id = #{id}
        </select>

###### 多表关联查询（级联查询）示例 ######

1.  一对一查询  
    jdbc.properties数据源
    
        jdbc.url=jdbc:mysql://localhost:3306/yanfa5
        jdbc.driver=com.mysql.jdbc.Driver
        jdbc.user=root
        jdbc.password=root
        jdbc.characterEncoding=utf8
    
    BookMapper.xml
    
        <?xml version="1.0" encoding="UTF-8" ?>
        <!DOCTYPE mapper
                PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
                "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
        
        
        
        <mapper namespace="com.lanou3g.mybatis.dao.BookDao">
            <resultMap id="book" type="com.lanou3g.mybatis.bean.Book">
                <id property="id" column="id" />
                <result property="bname" column="bname" />
                <result property="author" column="author" />
                <result property="authorGender" column="author_gender" />
                <result property="price" column="price" />
                <result property="description" column="description" />
                <association property="bookType" javaType="com.lanou3g.mybatis.bean.BookType">
                    <id property="id" column="id" />
                    <result property="tname" column="tname" />
                </association>
            </resultMap>
            
            <select id="queryBook" resultMap="book">
                select b.*,bt.*,b.id,bt.id from book b,booktype bt where b.btype = bt.id;
            </select>
            
        </mapper>
    
    mybatis.xml
    
        <?xml version="1.0" encoding="UTF-8" ?>
        <!DOCTYPE configuration
                PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
                "http://mybatis.org/dtd/mybatis-3-config.dtd">
        <configuration>
            <properties resource="jdbc.properties" />
            <environments default="development">
                <environment id="development">
                    <transactionManager type="JDBC"/>
                    <dataSource type="POOLED">
                        <property name="driver" value="${jdbc.driver}"/>
                        <property name="url" value="${jdbc.url}"/>
                        <property name="username" value="${jdbc.user}"/>
                        <property name="password" value="${jdbc.password}"/>
                    </dataSource>
                </environment>
            </environments>
            <mappers>
                <mapper resource="mapper/BookMapper.xml" />
            </mappers>
        </configuration>
    
    对应数据库表的java类  
    注意：此处可以体现出一对一
    
        @Setter
        @Getter
        public class Book {
              
            private Integer id;
            private String bname;
            private BookType bookType;	// ————>一对一体现在此处<————
            private String author;
            private Integer authorGender;
            private Float price;
            private String description;
        
            @Override
            public String toString() {
              
                return "Book{" +
                        "id=" + id +
                        ", bname='" + bname + '\'' +
                        ", bookType=[" + bookType + ']' +
                        ", author='" + author + '\'' +
                        ", authorGender=" + authorGender +
                        ", price=" + price +
                        ", description='" + description + '\'' +
                        "}\n";
            }
        }
        
        
        @Getter
        @Setter
        public class BookType {
              
            private Integer id;
            private  String tname;
        
            @Override
            public String toString() {
              
                return "BookType{" +
                        "id=" + id +
                        ", tname='" + tname + '\'' +
                        "}\n";
            }
        }
    
    dao层接口（操作数据库）
    
        public interface BookDao {
              
            List<Book> queryBook();
        }
    
    程序运行入口
    
        public class App {
              
            public static void main(String[] args) throws IOException {
              
                //1、读入配置文件
                String confPath = "mybatis.xml";
                InputStream is = Resources.getResourceAsStream(confPath);
        
                //2、构建SqlSessionFactory(用于获取sqlSession)
                SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
        
                //3、获取sqlSession对象（用于具体的RUID)
                SqlSession sqlSession = sessionFactory.openSession();
        
                //4、具体的RUID
                BookDao bookDao = sqlSession.getMapper(BookDao.class);
                System.out.println(bookDao.queryBook());
            }
        }
2.  一对多查询  
    jdbc.properties数据源
    
        jdbc.url=jdbc:mysql://localhost:3306/yanfa5
        jdbc.driver=com.mysql.jdbc.Driver
        jdbc.user=root
        jdbc.password=root
        jdbc.characterEncoding=utf8
    
    ProvinceMapper.xml
    
        <?xml version="1.0" encoding="UTF-8" ?>
        <!DOCTYPE mapper
                PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
                "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
        
        
        
        <mapper namespace="com.lanou3g.mybatis.dao.ProvinceDao">
            <resultMap id="province" type="com.lanou3g.mybatis.bean.Province">
                <id property="id" column="p_id" />
                <result property="pname" column="pname" />
                <collection property="cityList" ofType="com.lanou3g.mybatis.bean.City">
                    <id property="id" column="c_id" />
                    <result property="cname" column="cname" />
                    <result property="pid" column="pid" />
                </collection>
            </resultMap>
        
            <select id="queryProvince" resultMap="province">
                select p.*,c.*,p.id p_id,c.id c_id from province p,city c where p.id = c.pid
            </select>
        
        </mapper>
    
    mybatis.xml
    
        <?xml version="1.0" encoding="UTF-8" ?>
        <!DOCTYPE configuration
                PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
                "http://mybatis.org/dtd/mybatis-3-config.dtd">
        <configuration>
            <properties resource="jdbc.properties" />
            <environments default="development">
                <environment id="development">
                    <transactionManager type="JDBC"/>
                    <dataSource type="POOLED">
                        <property name="driver" value="${jdbc.driver}"/>
                        <property name="url" value="${jdbc.url}"/>
                        <property name="username" value="${jdbc.user}"/>
                        <property name="password" value="${jdbc.password}"/>
                    </dataSource>
                </environment>
            </environments>
            <mappers>
                <mapper resource="mapper/ProvinceMapper.xml" />
            </mappers>
        </configuration>
    
    对应数据库表的java类  
    注意：此处可以体现出一对多
    
        @Setter
        @Getter
        public class Province {
              
            private Integer id;
            private String pname;
            private List<City> cityList;	// ————>一对多体现在此处<————
        
            @Override
            public String toString() {
              
                return "Province{" +
                        "id=" + id +
                        ", pname='" + pname + '\'' +
                        ", cityList=" + cityList +
                        "}\n";
            }
        }
        
        
        @Getter
        @Setter
        public class City {
              
            private Integer id;
            private String cname;
            private Integer pid;
        
            @Override
            public String toString() {
              
                return "City{" +
                        "id=" + id +
                        ", cname='" + cname + '\'' +
                        ", pid=" + pid +
                        "}\n";
            }
        }
    
    dao层接口（操作数据库）
    
        public interface ProvinceDao {
              
            List<Province> queryProvince();
        }
    
    程序运行入口
    
        public class App {
              
            public static void main(String[] args) throws IOException {
              
                //1、读入配置文件
                String confPath = "mybatis.xml";
                InputStream is = Resources.getResourceAsStream(confPath);
        
                //2、构建SqlSessionFactory(用于获取sqlSession)
                SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
        
                //3、获取sqlSession对象（用于具体的RUID)
                SqlSession sqlSession = sessionFactory.openSession();
        
                //4、具体的RUID
                ProvinceDao provinceDao = sqlSession.getMapper(ProvinceDao.class);
                System.out.println(provinceDao.queryProvince());
            }
        }