Mybatis框架（三）

太过爱你忘了你带给我的痛 2022-04-23 09:50 190阅读 0赞

上一篇写到Mybatis动态代理实现Dao和一些Mybatis的一些配置，下来我们将接着上一篇讲到的Mybatis的参数传递和文件配置。

--------------------

##### 包装pojo #####

为什会需要包装pojo呢，包装pojo主要是用于一些复杂查询，它可以将你需要用到的pojo包装进去，可以在查询时从pojo里调属性。下来就用一个包装pojo的实例分析：  
1.配置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>
        
            
            
        
        
        <typeAliases>
            
            
            
            <package name="pojo"/>
        </typeAliases>
        <environments default="development">
            <environment id="development">
                
                <transactionManager type="JDBC"/>
                
                <dataSource type="POOLED">
                    <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                    <property name="url"
                              value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true"/>
                    <property name="username" value="root"/>
                    <property name="password" value="wrial.qq.com"/>
                </dataSource>
            </environment>
        </environments>
        
        <mappers>
            <mapper resource="user.xml"/>
            
            
            
            <package name="DaoProxy"/>
        </mappers>
    </configuration>

2.建立扩展类，扩展实现额外的一些功能（如果没有需求也不用写扩展类）

package pojo;
    
    public class UserAdd extends User {
        //可以添加User之外的东西，作为扩展类来使用
        private String nearPeopel;
    
        public String getNearPeopel() {
            return nearPeopel;
        }
    
        public void setNearPeopel(String nearPeopel) {
            this.nearPeopel = nearPeopel;
        }
    }

3.使用包装类，包装pojo  
（1）没使用扩展类

package pojo;
    
    public class UserQuaryVo {
    
        private User user;
    
        private Order order;
        public User getUser() {
            return user;
        }
    
        public void setUser(User user) {
            this.user = user;
        }
    
        public Order getOrder() {
            return order;
        }
    
        public void setOrder(Order order) {
            this.order = order;
        }
    }

（2）使用扩展类：可以对pojo做一个整合，可以接受复杂的数据进行查询操作

package pojo;
    
    public class UserQuaryVo {
    
       private UserAdd userAdd;
    
        private Order order;
    
        public Order getOrder() {
            return order;
        }
    
        public void setOrder(Order order) {
            this.order = order;
        }
    
        public UserAdd getUserAdd() {
            return userAdd;
        }
    
        public void setUserAdd(UserAdd userAdd) {
            this.userAdd = userAdd;
        }
    }

4.在Mapper中编写sql

<select id="getUserByQuaryVo" parameterType="userquaryvo" resultType="user">
            select *
            from user
            where id = #{user.id}
        </select>

5.编写测试类

@Test
        public void testGetUserById() throws IOException {
            SqlSessionFactory sqlSessionFactory = SqlSessionFactoryUtils.getSqlSessionFactory("SqlMapCofig.xml");
    
            SqlSession sqlSession = sqlSessionFactory.openSession();
            UserQuaryVo quaryVo = new UserQuaryVo();
            UserAdd userAdd = new UserAdd();
            userAdd.setId(1);
            quaryVo.setUserAdd(userAdd);
            User user = sqlSession.selectOne("DaoProxy.UserMapper.getUserByQuaryVo", quaryVo.getUserAdd().getId());
            System.out.println(user.toString());
            sqlSession.close();
        }

![在这里插入图片描述][20190207012841155.png]  
上边就是包装pojo的全过程,在需求复杂的情况下会遇到。

##### ResultMap入门之使用ResultMap解决数据库和pojo数据类型不符合问题 #####

下边用实例来处理这一问题，基于在核心文件配置包扫描  
先不配置类型一致，会出现如下错误（我设置pojo的userId和数据库中user\_id），userId不能匹配，所以无法赋值  
![在这里插入图片描述][20190208230655574.png]

下来在xml配置resultMap  
OrderMapper.xml

<mapper namespace="DaoProxy.OrderMapper">
        <resultMap id="GetOrderList" type="order">
            
            
            
            <result property="userId" column="user_id"/>
        </resultMap>
        <select id="GetOrders" resultMap="GetOrderList">
            SELECT *
            FROM `order`;
        </select>
    </mapper>

测试类

@Test
        public void testGetOrders() throws IOException {
            SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory("SqlMapCofig.xml").openSession();
            List<Order> orders = sqlSession.selectList("DaoProxy.OrderMapper.GetOrders");
            for (Order order1 : orders
            ) {
                System.out.println(order1.toString());
    
            }
        }

解决后结果如下：  
![在这里插入图片描述][20190208230956968.png]  
注：在单表查询可以只写需要改的属性，因为在`<resultMap id="GetOrderList" type="order">定义了类型，多表就不能这样了`

--------------------

### 动态sql语句 ###

为什么说mybatis用起来好用，其中有很大一部分原因是它可以简单的使用动态sql语句，下来我们就来学习一下动态sql。

##### if标签（用法和基础里if大同小异） #####

映射文件（实现多条件查询）：注意要在where后加1=1，因为如果没有筛选到就查询全部。

<select id="getUserByIfPojo" parameterType="user" resultType="User">
            select *
            from user
            where 1=1
            <if test="username!=null and username!=''">
                and username like '%${username}%'
            </if>
            <if test="sex!=null and sex!=''">
                and sex=#{sex}
            </if>
        </select>

测试类

@Test
        public void testGet() throws IOException {
            SqlSession sqlSession = SqlSessionUtils.getSqlSession("SqlMapCofig.xml");
            User user = new User();
            user.setUsername("张");
            user.setSex("w");
           List<User> users = sqlSession.selectList("DaoProxy.UserMapper.getUserByIfPojo",user);
            for (User u :users) {
                System.out.println(u.toString());
            }
           sqlSession.close();
        }

![在这里插入图片描述][20190208235809296.png]  
如果不设置性别测试如下  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjA1OTY4_size_16_color_FFFFFF_t_70]

##### sql - where #####

where标签自动补where关键字（和手动写会起冲突）  
映射文件

<select id="getUserByIfPojo" parameterType="user" resultType="User">
            select *
            from user
            <where>
                <if test="username!=null and username!=''">
                    and username like '%${username}%'
                </if>
                <if test="sex!=null and sex!=''">
                    and sex=#{sex}
                </if>
            </where>
        </select>

![在这里插入图片描述][20190209000916514.png]

##### sql - sql #####

**sql片段拼接用include关键字**

<sql id="user_sql">
            'username','sex'
        </sql>
        <select id="getUserByIfPojo" parameterType="user" resultType="User">
            select
            <include refid="user_sql"/>
            from user

##### foreach #####

循环标签，一般用于集合查询  
映射文件

<select id="getUserFromList" parameterType="UserQuaryVo" resultType="user">
            select * from user
            <where>
               <foreach collection="ids" open="id IN(" item="id" separator="," close=")">
                   #{id}
               </foreach>
            </where>
        </select>

测试类

@Test
        public void testList() throws IOException {
            SqlSession sqlSession = SqlSessionUtils.getSqlSession("SqlMapCofig.xml");
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            UserQuaryVo vo = new UserQuaryVo();
            vo.setIds(Arrays.asList(1,29,31));
            List<User> list = userMapper.getUserFromList(vo);
            for (User user:list
                 ) {
                System.out.println(user.toString());
            }
    
        }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjA1OTY4_size_16_color_FFFFFF_t_70 1]  
这一次就到这结束了，下一次写关联查询还有和Spring进行整合！

--------------------

[20190207012841155.png]: /images/20220321/76af0b27d07b4b2ca03a2943357a502f.png
[20190208230655574.png]: /images/20220321/bb9b48101ded467ba35cd1553e28eb29.png
[20190208230956968.png]: /images/20220321/07f75fa657b341d3b60a8671050e8f53.png
[20190208235809296.png]: /images/20220321/7f14009ff07843e8a2e36baef3459ea5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjA1OTY4_size_16_color_FFFFFF_t_70]: /images/20220321/7fdfb6ad7a5b4b89821738ce88f89302.png
[20190209000916514.png]: /images/20220321/5721f37e8dc94581acb9d4397742809e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjA1OTY4_size_16_color_FFFFFF_t_70 1]: /images/20220321/6acc73e9aa1f4b828d7e0e1562941956.png