学习Spring(6)Spring整合MyBatis简单进阶

太过爱你忘了你带给我的痛 2022-09-04 00:51 188阅读 0赞

# Spring整合MyBatis简单进阶 #

### mybatis动态sql ###

#### 多对一 ####

maven：

<dependencies>
            
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.11</version>
                <scope>test</scope>
            </dependency>
            
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-webmvc</artifactId>
                <version>5.2.15.RELEASE</version>
            </dependency>
            
            <dependency>
                <groupId>org.aspectj</groupId>
                <artifactId>aspectjweaver</artifactId>
                <version>1.9.7</version>
            </dependency>
            
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
                <version>3.5.6</version>
            </dependency>
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.16</version>
            </dependency>
            
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-jdbc</artifactId>
                <version>5.3.4</version>
            </dependency>
            
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis-spring</artifactId>
                <version>2.0.1</version>
            </dependency>
            
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>1.18.6</version>
            </dependency>
        </dependencies>
    
        
        <build>
            <resources>
                <resource>
                    <directory>src/main/resources</directory>
                    <includes>
                        <include>**/*.*</include>
                    </includes>
                    
                    <filtering>true</filtering>
                </resource>
            </resources>
        </build>

流程：

1.  编写pojo实体类(表的对应类)
2.  实现mybatis的配置文件(表连接)，配置文件
3.  接口编写(对表的操作接口，增删改查)
4.  接口对应的Mapper映射文件(接口和sql的映射xml)
5.  接口实现类
6.  测试类  
    新建一个项目  
    目录结构：

*  java
    
     *  com.test
        
         *  dao
            
             *  PlayerMapper.java(第三步的表操作接口)
             *  PlayerMapperImpl.java(第五步接口实现类)
         *  pojo
            
             *  Player.java(第一步的表对应类)
             *  Team.java
 *  resources
    
     *  com.test.mapper
        
         *  PlayerMapper.xml(第四步的映射文件配置)
     *  MyBatisConfig.xml(第二步的mybatis配置)
     *  applicationContext.xml(spring配置)
     *  SpringMyBatis.xml(spring-mybatis配置)
 *  test
    
     *  MyBatisTest.java(测试)

前提maven配置好了  
第一步：把表的类写好  
先写球队类Team.java：

再写球员类Player.java：

package com.test.pojo;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    public class Player { 
        private int player_id;
        private int team_id;
        private String player_name;
        private float height;
        private Team team;
        public Player(int team_id, String player_name, float height){ 
            this.team_id = team_id;
            this.player_name = player_name;
            this.height = height;
        }
    }

第二步配置文件：  
先配置spring的配置，applicationContext.xml：

再配置mybatis配置，MyBatisConfig.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 >
        
        <settings>
            <setting name="logImpl" value="STDOUT_LOGGING" />
        </settings>
        
        <typeAliases>
            <package name="com.test.pojo"/>
        </typeAliases>
    </configuration>

最后配置spring-mybatis的配置，SpringMyBatis.xml（这里要注意一点，最后有一个实现类的注册，其实实现类还没有写，这里我直接写上去了）

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd http://www.springframework.org/schema/aop https://www.springframework.org/schema/aop/spring-aop.xsd">
        
        <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
            <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
            <property name="url" value="jdbc:mysql://127.0.0.1:3306/test_crm?characterEncoding=utf-8" />
            <property name="username" value="root" />
            <property name="password" value="root123456" />
        </bean>
        
        
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
            <property name="dataSource" ref="dataSource" />
            
            <property name="mapperLocations" value="classpath:com/test/mapper/*.xml" />
            
            
            
            <property name="configLocation" value="MyBatisConfig.xml" />
        </bean>
        
        <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <constructor-arg ref="dataSource" />
        </bean>
        
        
        
        <tx:advice id="txAdvice" transaction-manager="transactionManager">
            <tx:attributes>
                
                
                <tx:method name="add*" propagation="REQUIRED"/>
                <tx:method name="delete*" propagation="REQUIRED"/>
                <tx:method name="update*" propagation="REQUIRED"/>
                <tx:method name="select*" propagation="REQUIRED"/>
                
                
                
                 <tx:method name="*" propagation="REQUIRED"/>
            </tx:attributes>
        </tx:advice>
        
        <aop:config>
            
            <aop:pointcut id="txPointcut" expression="execution(* com.test.dao.*.*(..))"/>
            
            <aop:advisor advice-ref="txAdvice" pointcut-ref="txPointcut"/>
        </aop:config>
        
        <bean id="playerMapper" class="com.test.dao.PlayerMapperImpl">
            
            <property name="sqlSessionFactory" ref="sqlSessionFactory" />
        </bean>
    </beans>

第三步编写表操作的接口，PlayerMapper.java（这里使用两种方式实现查询）：

package com.test.dao;
    import com.test.pojo.Player;
    public interface PlayerMapper { 
        // 按照查询嵌套处理
        Player selectPlayer(int ids);
        // 按照结果嵌套处理
        Player selectPlayer2(int ids);
    }

第四步，接口对应的Mapper映射文件，PlayerMapper.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.test.dao.PlayerMapper">
        
        
        
        
        <select id="selectPlayer" resultMap="getPlayer" parameterType="int">
            select * from player where player_id = #{player_id};
        </select>
        <resultMap id="getPlayer" type="Player">
            <result property="player_id" column="player_id" />
            <result property="team_id" column="team_id" />
            <result property="player_name" column="player_name" />
            <result property="height" column="height" />
            
            
            <association property="team" column="team_id" javaType="Team" select="getTeam"/>
        </resultMap>
        <select id="getTeam" resultType="Team">
            select * from team where team_id = #{team_id};
        </select>
    
        
        
        <select id="selectPlayer2" resultMap="getPlayer2">
            select * from player, team
            where player.player_id = #{player_id} and team.team_id=player.team_id;
        </select>
        
        <resultMap id="getPlayer2" type="Player">
            <result property="player_id" column="player_id" />
            <result property="team_id" column="team_id" />
            <result property="player_name" column="player_name" />
            <result property="height" column="height" />
            
            <association property="team" javaType="Team">
                <result property="team_id" column="team_id" />
                <result property="team_name" column="team_name" />
            </association>
        </resultMap>
    </mapper>

第五步：实现接口，PlayerMapperImpl.java（这里注意，原本应该写好实现类以后去SpringMyBatis.xml中注册这个类）：

package com.test.dao;
    import com.test.pojo.Player;
    import org.mybatis.spring.support.SqlSessionDaoSupport;
    public class PlayerMapperImpl extends SqlSessionDaoSupport implements PlayerMapper { 
        // 映射接口
        public PlayerMapper getPlayerMapper() { 
            return getSqlSession().getMapper(PlayerMapper.class);
        }
        // 根据条件查询嵌套处理
        @Override
        // @Transactional(rollbackFor = Throwable.class) 这是注解形式的事务管理
        public Player selectPlayer(int ids) { 
            return getPlayerMapper().selectPlayer(ids);
        }
        // 根据结果查询嵌套处理
        @Override
        // @Transactional(rollbackFor = Throwable.class) 这是注解形式的事务管理
        public Player selectPlayer2(int ids) { 
            return getPlayerMapper().selectPlayer2(ids);
        }
    }

第六步测试，MyBatisTest.java：

import com.test.dao.PlayerMapper;
    import com.test.pojo.Player;
    import org.junit.Test;
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.support.ClassPathXmlApplicationContext;
    public class MyBatisTest { 
        @Test
        public void  test(){ 
            // spring配置文件
            ApplicationContext application = new ClassPathXmlApplicationContext("applicationContext.xml");
            PlayerMapper mapper = application.getBean("playerMapper", PlayerMapper.class);
            // 按条件查询嵌套处理
            Player playerList = mapper.selectPlayer(20036);
            System.out.println(playerList);
            // 按结果查询嵌套处理
            Player playerList2 = mapper.selectPlayer2(20036);
            System.out.println(playerList2);
        }
    }

#### 一对多 ####

这次是在上面的项目上加一些东西，所以结构目录和上面一样，不过要加几个文件：

*  java
    
     *  com.test.dao
        
         *  TeamMapper.java(接口)
         *  TeamMapperImpl.java(实现类)
 *  resources
    
     *  com.teat.mapper
        
         *  TeamMapper.xml(映射文件)

编写流程：  
第一步先修改Team.java，因为一对多，这个多就是一个List：

package com.test.pojo;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    import java.util.ArrayList;
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    public class Team { 
        private int team_id;
        private String team_name;
        // 多个球员
        ArrayList<Player> players;
    }

第二步，编写表操作接口，TeamMapper.java

package com.test.dao;
    import com.test.pojo.Team;
    public interface TeamMapper { 
        // 查询
        Team selectTeam(int id);
    }

第三步写接口对应的Mapper映射文件，TeamMapper.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.test.dao.TeamMapper">
        
        
        
        <select id="selectTeam" parameterType="int" resultMap="getTeam">
            select * from team, player
            where team.team_id = #{team_id} and player.team_id=team.team_id;
        </select>
        
        <resultMap id="getTeam" type="Team">
            <result property="team_id" column="team_id" />
            <result property="team_name" column="team_name" />
            
            
            <collection property="players" javaType="ArrayList" ofType="Player">
                <result property="player_id" column="player_id" />
                <result property="player_name" column="player_name" />
                <result property="height" column="height" />
            </collection>
        </resultMap>
    </mapper>

第四步编写接口实现类，TeamMapperImpl.java

package com.test.dao;
    import com.test.pojo.Team;
    import org.mybatis.spring.support.SqlSessionDaoSupport;
    public class TeamMapperImpl extends SqlSessionDaoSupport implements TeamMapper { 
        // 映射接口
        public TeamMapper getTeamMapper() { 
            return getSqlSession().getMapper(TeamMapper.class);
        }
        // 根据条件查询嵌套处理
        @Override
        // @Transactional(rollbackFor = Throwable.class) 这是注解形式的事务管理
        public Team selectTeam(int ids) { 
            return getTeamMapper().selectTeam(ids);
        }
    }

第五步注册类，在SpringMyBatis.xml的最下面注册类：

第六步测试，MyBatisTest.java

import com.test.dao.PlayerMapper;
    import com.test.dao.TeamMapper;
    import com.test.pojo.Player;
    import com.test.pojo.Team;
    import org.junit.Test;
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.support.ClassPathXmlApplicationContext;
    public class MyBatisTest { 
        @Test
        public void  test(){ 
            // spring配置文件
            ApplicationContext application = new ClassPathXmlApplicationContext("applicationContext.xml");
            PlayerMapper mapper = application.getBean("playerMapper", PlayerMapper.class);
            // 按条件查询嵌套处理
            Player playerList = mapper.selectPlayer(20036);
            System.out.println(playerList);
            // 按结果查询嵌套处理
            Player playerList2 = mapper.selectPlayer2(20036);
            System.out.println(playerList2);
    
            TeamMapper mapper2 = application.getBean("teamMapper", TeamMapper.class);
            Team teamList = mapper2.selectTeam(1001);
            System.out.println(teamList);
        }
    }

数据库字段形式xx\_yy，类用属性使用xxYy驼峰命名方式，需要在MyBatisConfig.xml中的  
settings标签下添加

<setting name="mapUnderscoreToCamelCase" value="true"/>

#### 动态sql ####

##### if语句，只要满足条件就拼接 #####

如果一个查询需求，可以查询全部，又可以根据不同条件查询，怎么办？

<select id="selectDemo" parameterType="map" resultType="Demo">
        select * from demo where 1=1
        <if test="name!=null">
            and name = #{name}
        </if>
        
        <if test="title!=null and author !=null">
            and title = #{title}
        </if>
    </select>

先说一个上面sql拼接的问题，第一是1=1，这个是为了查出所有数据添加，还有一个问题就是where后面如果没有1=1，直接拼接，如果第一if没有符合要求，第二个语句带有and，那sql就成了 where and xx=xx，显然是sql语法错误，为了解决这个问题，出现了一个where标签

where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除，就是where后面没条件就把where去掉，如果有条件但是是以or，and开头的会自动去掉or，and

所以改一下上面的sql

<select id="selectDemo" parameterType="map" resultType="Demo">
        select * from demo
        
        <where>
            <if test="name!=null">
                name = #{name}
            </if>
            
            <if test="title!=null and author !=null">
                and title = #{title}
            </if>
        </where>
    </select>

##### choose、when、otherwise，从多个条件中选择一个拼接 #####

<select id="selectDemo" parameterType="map" resultType="Demo">
        select * from demo
        <where>
            <choose>
                
                <when test="name!=null">
                    name = #{name}
                </when>
                <when test="title!=null and author !=null">
                    and title = #{title}
                </when>
                
                <otherwise>
                    and status = 1
                </otherwise>
            </choose>
        </where>
    </select>

##### trim、where、set #####

where上面讲过了

set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）

<update id="updateAuthorIfNecessary">
      update Author
        <set>
          <if test="username != null">username=#{username},</if>
          <if test="password != null">password=#{password},</if>
          <if test="email != null">email=#{email},</if>
          <if test="bio != null">bio=#{bio}</if>
        </set>
      where id=#{id}
    </update>

trim有四个属性：

*  prefix：给sql语句拼接的前缀
 *  suffix：给sql语句拼接的后缀
 *  prefixOverrides：去除sql语句前面的关键字或者字符，该关键字或者字符由prefixOverrides属性指定，假设该属性指定为"AND"，当sql语句的开头为"AND"，trim标签将会去除该"AND"
 *  suffixOverrides：去除sql语句后面的关键字或者字符，该关键字或者字符由suffixOverrides属性指定

例子：

<trim prefix="WHERE" prefixOverrides="AND|OR"></trim>
    
    
    <trim prefix="SET" suffixOverrides=","></trim>

##### sql片段 #####

提取复用性高的sql片段，可以重复利用

<sql id="conditions">
        <if test="name!=null">
            name = #{name}
        </if>
        
        <if test="title!=null and author !=null">
            and title = #{title}
        </if>
    </sql>
    <select id="selectDemo" parameterType="map" resultType="Demo">
        select * from demo
        <where>
            
            <include refid="conditions"></include>
        </where>
    </select>

##### foreach #####

<select id="selectPostIn" resultType="domain.blog.Post">
      SELECT *
      FROM POST P
      WHERE ID in
      <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
            #{item}
      </foreach>
    </select>

### mybatis缓存 ###

#### 一级缓存 ####

一级缓存是默认开启的，SqlSession级别的，生命周期在SqlSession的创建和关闭之间，如果两次查询之间存在新增更新删除操作缓存会失效，也可以手动清理缓存

#### 二级缓存 ####

*  一个会话查询会将数据放到一级缓存中
 *  会话关闭后一级缓存的数据会保存在二级缓存中
 *  新的会话可以从二级缓存中取数据
 *  不同的mapper查出的数据会放在自己对应的缓存中

步骤：

1.  开启全局缓存（默认是开启的）
2.  在mapper.xml中使用< cache/>标签

缓存查询顺序:

1.  先在二级缓存找
2.  再去一级缓存找
3.  最后数据库里找

#### 自定义缓存-ehcache ####

导入第三方缓存的包，然后< cache type=“xxxx.xxx.xxx”/>