mybatis前因后果(三)mybatis 多表查询

红太狼 2022-11-03 10:53 187阅读 0赞

## 多表查询 ##

mybatis可以说是特别擅长于多表查询和复杂查询，而复杂查询我们既可以用xml配置方式完成，也可以用注解完成。但无论是注解方式还是xml文件配置方式，都需要引进项目依赖。

### 项目依赖 ###

所有项目

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
    
        <groupId>org.example</groupId>
        <artifactId>mutitableopt</artifactId>
        <version>1.0-SNAPSHOT</version>
        <dependencies>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.32</version>
            </dependency>
    
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
                <version>3.4.5</version>
            </dependency>
    
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
            </dependency>
    
            <dependency>
                <groupId>log4j</groupId>
                <artifactId>log4j</artifactId>
                <version>1.2.17</version>
            </dependency>
    
            
            <dependency>
                <groupId>com.github.pagehelper</groupId>
                <artifactId>pagehelper</artifactId>
                <version>4.0.0</version>
            </dependency>
    
            <dependency>
                <groupId>com.github.jsqlparser</groupId>
                <artifactId>jsqlparser</artifactId>
                <version>1.0</version>
            </dependency>
        </dependencies>
        <build>
            <resources>
                <resource>
                    <directory>src/main/java</directory>
                    <includes>
                        <include>**/*.properties</include> <include>**/*.xml</include>
                    </includes>
                    <filtering>true</filtering>
                </resource>
                <resource>
                    <directory>src/main/resources</directory>
                    <includes>
                        <include>**/*.properties</include> <include>**/*.xml</include>
                    </includes>
                    <filtering>true</filtering>
                </resource>
            </resources>
        </build>
    </project>

### xml 配置多表查询 ###

1.  user中含有orderList 和roleList，及用户和角色是一对多的关系，用户和角色是多对多的关系，现根据四张表格查询出用户的所有订单以及所有角色。
2.  order中含有一个用户，order和用户是一对一的关系，将查询到的用户信息封装到user中。

#### 案例依赖 ####

#### 实体类: ####

##### order #####

public class Order { 
        private int id;
        private Date ordertime;
        private double total;
        //当前订单属于哪一个用户
        private User user;
        public int getId() { 
            return id;
        }
    
        public void setId(int id) { 
            this.id = id;
        }
    
        public Date getOrdertime() { 
            return ordertime;
        }
    
        public void setOrdertime(Date ordertime) { 
            this.ordertime = ordertime;
        }
    
        public double getTotal() { 
            return total;
        }
    
        public void setTotal(double total) { 
            this.total = total;
        }
    
        public User getUser() { 
            return user;
        }
    
        public void setUser(User user) { 
            this.user = user;
        }
    
        @Override
        public String toString() { 
            return "Order{" +
                    "id=" + id +
                    ", ordertime=" + ordertime +
                    ", total=" + total +
                    ", user=" + user +
                    '}';
        }
    }

##### role #####

public class Role { 
    
        private int id;
        private String roleName;
        private String roleDesc;
    
        public int getId() { 
            return id;
        }
    
        public void setId(int id) { 
            this.id = id;
        }
    
        public String getRoleName() { 
            return roleName;
        }
    
        public void setRoleName(String roleName) { 
            this.roleName = roleName;
        }
    
        public String getRoleDesc() { 
            return roleDesc;
        }
    
        public void setRoleDesc(String roleDesc) { 
            this.roleDesc = roleDesc;
        }
    
        @Override
        public String toString() { 
            return "Role{" +
                    "id=" + id +
                    ", roleName='" + roleName + '\'' +
                    ", roleDesc='" + roleDesc + '\'' +
                    '}';
        }
    }

##### user #####

public class User { 
    
        private int id;
        private String username;
        private String password;
        private Date birthday;
    
        //描述的是当前用户存在哪些订单
        private List<Order> orderList;
        //描述的是当前用户具备哪些角色
        private List<Role> roleList;
        public List<Role> getRoleList() { 
            return roleList;
        }
    
        public void setRoleList(List<Role> roleList) { 
            this.roleList = roleList;
        }
    
        public List<Order> getOrderList() { 
            return orderList;
        }
    
        public void setOrderList(List<Order> orderList) { 
            this.orderList = orderList;
        }
    
        public Date getBirthday() { 
            return birthday;
        }
    
        public void setBirthday(Date birthday) { 
            this.birthday = birthday;
        }
    
        public int getId() { 
            return id;
        }
    
        public void setId(int id) { 
            this.id = id;
        }
    
        public String getUsername() { 
            return username;
        }
    
        public void setUsername(String username) { 
            this.username = username;
        }
    
        public String getPassword() { 
            return password;
        }
    
        public void setPassword(String password) { 
            this.password = password;
        }
    
        @Override
        public String toString() { 
            return "User{" +
                    "id=" + id +
                    ", username='" + username + '\'' +
                    ", password='" + password + '\'' +
                    ", birthday=" + birthday +
                    ", orderList=" + orderList +
                    ", roleList=" + roleList +
                    '}';
        }
    }

#### 日期转换 ####

由于案例中可能会用到一些自定义的日期类型，我们需要日期类型的转换器。我们要求将日期格式统一的按照毫秒值进行存储。

public class DateTypeHandler extends BaseTypeHandler<Date> { 
    
        //将java类型转换成sql类型
        public void setNonNullParameter(PreparedStatement ps, int i, Date date, JdbcType jdbcType) throws SQLException, SQLException { 
            long time = date.getTime();
            ps.setLong(i,time);
        }
    
        //获取结果集合中有用的数据
        public Date getNullableResult(ResultSet rs, String columnName) throws SQLException { 
            long time = rs.getLong(columnName);
            Date date = new Date(time);
            return date;
        }
    
        public Date getNullableResult(ResultSet rs, int columnIndex) throws SQLException { 
            long time = rs.getLong(columnIndex);
            Date date = new Date(time);
            return date;
        }
    
        public Date getNullableResult(CallableStatement cs, int columnIndex) throws SQLException { 
            long time = cs.getLong(columnIndex);
            Date date = new Date(time);
            return date;
        }
    }

#### 用户接口和订单接口 ####

public interface OrderDao { 
        public List<Order> findAll();
    }

public interface UserDao { 
        public List<User> findAll();
        public List<User> findUserAll();
    }

#### 映射配置文件 ####

##### UserMapper #####

<?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.feitian.dao.UserDao">
        <resultMap id="userMap" type="user">
            <id column="uid" property="id"/>
            <result column="username" property="username"/>
            <result column="password" property="password"/>
            <result column="birthday" property="birthday"/>
            
            <collection property="orderList" ofType="order">
                <id column="oid" property="id"/>
                <result column="ordertime" property="ordertime"/>
                <result column="total" property="total"/>
            </collection>
        </resultMap>
        <select id="findAll" resultMap="userMap">
            select  *, o.id oid ,o.uid uid from user u, orders o where uid =   u.id;
        </select>
        <resultMap id="userAllMap" type="user">
            <id column="userId" property="id"/>
            <result column="username" property="username"/>
            <result column="password" property="password"/>
            <result column="birthday" property="birthday"/>
    
            <collection property="roleList" ofType="role">
                <id column="roleId" property="id"/>
                <result column="roleName" property="roleName"/>
                <result column="roleDesc" property="roleDesc"/>
            </collection>
            <collection property="orderList" ofType="order">
                <id column="oid" property="id"/>
                <result column="ordertime" property="ordertime"/>
                <result column="total" property="total"/>
            </collection>
        </resultMap>
        <select id="findUserAll" resultMap="userAllMap">
            select * , o.id oid , u.id uid  from user u, sys_user_role ur, orders o, sys_role r where
            o.uid = u.id and u.id = ur.userId  and  ur.roleId = r.id
        </select>
        
    </mapper>

##### OrderMapper #####

#### mybatis 配置文件 ####

<?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"/>
        
        <typeAliases>
            <typeAlias type="com.feitian.domain.Order" alias="order"/>
            <typeAlias type="com.feitian.domain.User" alias="user"/>
            <typeAlias type="com.feitian.domain.Role" alias="role"/>
        </typeAliases>
        
        <typeHandlers>
            <typeHandler handler="com.feitian.datehandler.DateTypeHandler"/>
        </typeHandlers>
        
        <plugins>
            <plugin interceptor="com.github.pagehelper.PageHelper"/>
        </plugins>
    
        
        <environments default="developement">
            <environment id="developement">
                <transactionManager type="JDBC" />
                <dataSource type="POOLED">
                    <property name="driver" value="${jdbc.driver}"/>
                    <property name="url" value="${jdbc.url}"/>
                    <property name="username" value="${jdbc.username}"/>
                    <property name="password" value="${jdbc.password}"/>
                </dataSource>
            </environment>
        </environments>
        
        <mappers>
            <mapper resource="com/feitian/mapper/UserMapper.xml"/>
            <mapper resource="com/feitian/mapper/OrderMapper.xml"/>
        </mappers>
    </configuration>

### 数据库配置文件 ###

jdbc.driver=com.mysql.jdbc.Driver
    jdbc.url=jdbc:mysql://localhost:3306/test
    jdbc.username=root
    jdbc.password=root

### 日志配置文件 ###

### direct log messages to stdout ###
    log4j.appender.stdout=org.apache.log4j.ConsoleAppender
    log4j.appender.stdout.Target=System.err
    log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
    log4j.appender.stdout.layout.ConversionPattern=%d{ ABSOLUTE} %5p %c{ 1}:%L - %m%n
    
    ### direct messages to file mylog.log ###
    log4j.appender.file=org.apache.log4j.FileAppender
    log4j.appender.file.File=c:/mylog.log
    log4j.appender.file.layout=org.apache.log4j.PatternLayout
    log4j.appender.file.layout.ConversionPattern=%d{ ABSOLUTE} %5p %c{ 1}:%L - %m%n
    
    ### set log levels - for more verbose logging change 'info' to 'debug' ###
    
    log4j.rootLogger=debug, stdout

### 测试文件 ###

public class ServiceImpl { 
        @Test
        public void  test01() throws IOException { 
            InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            OrderDao orderDao = sqlSession.getMapper(OrderDao.class);
            List<Order> orderList = orderDao.findAll();
            for (Order order :
                    orderList) { 
                System.out.println(order);
            }
            sqlSession.close();
        }
        @Test
        public void  test02() throws IOException { 
            InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            List<User> userList = userDao.findAll();
            for (User user :
                    userList) { 
                System.out.println(user);
            }
            sqlSession.close();
        }
        @Test
        public void  test03() throws IOException { 
            InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            List<User> userList = userDao.findUserAll();
            for (User user :
                    userList) { 
                System.out.println(user);
            }
            sqlSession.close();
        }
    }

通过上述测试案例我们即可使用xml配置文件的方式实现mybatis多表查询，但此处值得我们注意的一个问题是关于sql映射文件的文件名以及目录接口，本案例的目录结构如下所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70]  
上图为本案例文件目录结构问起，上图中提出了两个问题是我们值得注意的，如上图所配置的这种方式会给我们带较多的隐患，如采用xml全局配置时会出现找不到文件的异常，所以要进行修改，注意以下两点：

1.  dao层包名与sql映射文件包名需要一直
2.  dao层接口名需要与sql映射文件名保持一致

### 注解方式实现多表查询 ###

同样是上面案例的要求，接下来就需要用注解实现，其实mybatis的注解相当于采用注解代替了原来mybatis的sql映射文件，下面就来实现这一过程：

#### 实体类和日期转换接口 ####

user ，order ，role 等实体类与上述案例保持一致即可。

#### 数据库 、日志配置文件 ####

数据库和日志配置文件都不变

##### SqlMapper文件 #####

sqlmapper文件已经完没有必要了，及UserMapper 和OrderMapper已完全省却。

#### sqlMapperConfig ####

由于sqlmapper文件不存在了，所以这项配置文件需要发生变化。

<?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"/>
        
        <typeAliases>
            <typeAlias type="com.feitian.domain.Order" alias="order"/>
            <typeAlias type="com.feitian.domain.User" alias="user"/>
            <typeAlias type="com.feitian.domain.Role" alias="role"/>
        </typeAliases>
        
        
    
        
        
    
        
        <environments default="developement">
            <environment id="developement">
                <transactionManager type="JDBC" />
                <dataSource type="POOLED">
                    <property name="driver" value="${jdbc.driver}"/>
                    <property name="url" value="${jdbc.url}"/>
                    <property name="username" value="${jdbc.username}"/>
                    <property name="password" value="${jdbc.password}"/>
                </dataSource>
            </environment>
        </environments>
        
        
        
        <mappers>
            <package name="com.feitian.dao"/>
        </mappers>
    </configuration>

此项文件中**取消了加载sqlmapper文件，其他配置可变可不变。**

#### 用户接口和订单接口 ####

重点变化在dao接口层，在这里我们需要加入大量的注解。

##### OrderDao #####

public interface OrderDao { 
    
    
        @Select("select *, o.id oid from orders o ,user u where o.uid = u.id")
        @Results({ 
                @Result(column ="oid", property = "id"),
                @Result(column ="ordertime", property = "ordertime"),
                @Result(column ="total", property = "total"),
                @Result(
                        property = "user",     //要封装的属性名称
                        column = "uid" ,       //根据那个字段去查询user表
                        javaType = User.class, //要封装的实体类型
                        one=@One(select = "com.feitian.dao.UserDao.findById")   // //select属性 代表查询那个接口的方法获得数据
    
                )
        })
        public List<Order> findAll();
        /*@Select("select *,o.id oid from orders o,user u where o.uid=u.id") @Results({ @Result(column = "oid",property = "id"), @Result(column = "ordertime",property = "ordertime"), @Result(column = "total",property = "total"), @Result(column = "uid",property = "user.id"), @Result(column = "username",property = "user.username"), @Result(column = "password",property = "user.password") }) public List<Order> findAll();*/
    
        @Insert("insert into user values(#{id},#{username},#{password},#{birthday})")
        public void save(User user);
        @Update("update user set username=#{username},password=#{password} " +
                ",birthday=#{birthday} where id=#{id}")
        public void update(User user);
    
        @Delete("delete from user where id=#{id}")
        public void delete(Integer id);
    
        @Select("select * from orders where uid=#{uid}")
        public List<Order> findByUid(int uid);
    
    }

##### UserDao #####

public interface UserDao { 
    
        @Select("select * from user")
        public List<User> findAll();
        @Select("select * from user")
        @Results({ 
                @Result (column = "id" , property ="id"),
                @Result (column = "username" , property = "username"),
                @Result (column = "password" , property = "password"),
                @Result (column = "birthday" , property = "birthday"),
                @Result(
                        property = "orderList" ,
                        column = "id",
                        javaType = List.class,
                        many = @Many(select = "com.feitian.dao.OrderDao.findByUid")
                )
        })
        public  List<User> findUserAll();
        @Select("select * from user")
        @Results({ 
                @Result (column = "id" , property ="id"),
                @Result (column = "username" , property = "username"),
                @Result (column = "password" , property = "password"),
                @Result (column = "birthday" , property = "birthday"),
                @Result(
                        property = "orderList" ,
                        column = "id",
                        javaType = List.class,
                        many = @Many(select = "com.feitian.dao.OrderDao.findByUid")
                ),
                @Result(
                        property = "roleList" ,
                        column = "id",
                        javaType = List.class,
                        many = @Many(select = "com.feitian.dao.RoleDao.findByUid" )
                )
        })
        public  List<User> findUserRoleAll();
        @Insert("insert into user values(#{id},#{username},#{password},#{birthday})")
        public void save(User user);
        @Update("update user set username=#{username},password=#{password} " +
                ",birthday=#{birthday} where id=#{id}")
        public void update(User user);
    
        @Delete("delete from user where id=#{id}")
        public void delete(Integer id);
    
        @Select("select * from user where id=#{id}")
        public User findById(int i);
    }

##### RoleDao #####

public interface RoleDao { 
    
    
        @Select("select * from sys_user_role ur, sys_role r " +
                "where ur.roleId = r.id AND ur.userId = #{uid}")
        public List<Role> findByUid(int uid);
    }

#### 测试类 ####

##### order测试类 #####

public class OrderServiceImpl { 
        private SqlSession sqlSession = null;
        @Before
        public void  getSqlSession() throws IOException { 
            InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
            sqlSession = sqlSessionFactory.openSession();
    
        }
        @Test
        public void  test01() throws IOException { 
            OrderDao orderDao = sqlSession.getMapper(OrderDao.class);
            List<Order> orderList = orderDao.findAll();
            for (Order order :
                    orderList) { 
                System.out.println(order);
            }
        }
        @After
        public void closeSqlSession(){ 
            sqlSession.commit();
            sqlSession.close();
        }
    }

##### User测试类 #####

public class UserServiceImpl { 
        private SqlSession sqlSession = null;
    
        @Before
        public void  getSqlSession() throws IOException { 
            InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
            sqlSession = sqlSessionFactory.openSession();
    
        }
    
        @Test
        public void  test01() throws IOException { 
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            List<User> userList = userDao.findAll();
            for (User user :
                    userList) { 
                System.out.println(user);
            }
        }
    
        @Test
        public void  test02() throws IOException { 
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            User user = new User();
            user.setId(4);
            user.setUsername("tom");
            user.setPassword("123");
            user.setBirthday(new Date());
            userDao.save(user);
        }
    
        @Test
        public void  test03() throws IOException { 
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            User user = new User();
            user.setId(3);
            user.setUsername("java");
            user.setPassword("1234");
           /* SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");*/
            Date date = new Date("1992/08/20");
            user.setBirthday(date);
            userDao.update(user);
        }
    
        @Test
        public void  test04() throws IOException { 
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            userDao.delete(4);
        }
    
        @Test
        public void  test05() throws IOException { 
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            User user = userDao.findById(4);
            System.out.println(user);
        }
    
    
        @Test
        public void test06(){ 
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            List<User> userList = userDao.findUserAll();
            for (User user :
                    userList) { 
                System.out.println(user);
            }
        }
    
    
    
        @Test
        public void test07(){ 
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            List<User> userList = userDao.findUserRoleAll();
            for (User user :
                    userList) { 
                System.out.println(user);
            }
        }
    
        @After
        public void closeSqlSession(){ 
            sqlSession.commit();
            sqlSession.close();
        }
    }

此处的不同点是我们把每次运行的固定初始化代码和后期关闭的代码抽取出来，用@before和@after来实现代码简化。  
支持mybatis两种方式实现多表查询的案例已经结束，期待下节的ssm整合。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70]: /images/20221024/32adbbe1c4144201bcfab4adace8c4a1.png