mysql数据库详解（二）：mybatis与springboot整合

作为一个稀有的Java妹子，所写的所有博客都只是当作自己的笔记，留下证据自己之前是有用心学习的～哈哈哈哈（如果有不对的地方，也请大家指出，不要悄悄咪咪的不告诉我）

一、引入相关的jar包

 <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-jdbc</artifactId>
 </dependency>
 <dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>1.3.2</version>
 </dependency>
 <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid-spring-boot-starter</artifactId>
      <version>1.1.21</version>
 </dependency>
 <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.47</version>
 </dependency>

 <dependency>
       <groupId>tk.mybatis</groupId>
       <artifactId>mapper</artifactId>
       <version>4.1.5</version>
 </dependency>

二、配置文件

server.port=8888
spring.datasource.druid.url=jdbc:mysql://192.168.7.104:3306/qqsk?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.datasource.druid.username=jiebai
spring.datasource.druid.password=123456
spring.datasource.druid.driver-class-name=com.mysql.jdbc.Driver

三、sqlSessionFactory配置

@Configuration
public class MybatisConfigurer {
    /**
     * 项目基础包名称，根据公司的项目修改
     */
    public static final String BASE_PACKAGE = "com.mybatis.demo";

    /**
     * Model所在包
     */
    public static final String MODEL_PACKAGE = BASE_PACKAGE + ".domain";


    /**
     * Mapper所在包
     */
    public static final String MAPPER_PACKAGE = BASE_PACKAGE + ".dao";


    /**
     * Mapper插件基础接口的完全限定名
     */
    public static final String MAPPER_INTERFACE_REFERENCE = "com.mybatis.demo.mapper.Mapper";

    @Bean
    public SqlSessionFactory sqlSessionFactoryBean(DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
        factory.setDataSource(dataSource);
        factory.setTypeAliasesPackage(MODEL_PACKAGE);
        // 添加XML目录
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
		//设置mapper文件的获取位置
        factory.setMapperLocations(resolver.getResources("classpath:mapper/*.xml"));
        return factory.getObject();
    }

    @Bean
    public MapperScannerConfigurer mapperScannerConfigurer() {
        MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactoryBean");
        mapperScannerConfigurer.setBasePackage(MAPPER_PACKAGE);

        // 配置通用Mapper，详情请查阅官方文档
        Properties properties = new Properties();
        properties.setProperty("mappers", MAPPER_INTERFACE_REFERENCE);
        properties.setProperty("notEmpty", "false");
        properties.setProperty("IDENTITY", "MYSQL");
        mapperScannerConfigurer.setProperties(properties);

        return mapperScannerConfigurer;
    }

}

四、通用mapper接口

主要是继承已有的mapper方法，自己也可以封装一些常用的方法。

public interface Mapper<T> extends BaseMapper<T>, ConditionMapper<T>, IdsMapper<T>, InsertListMapper<T> {
}

这里我继承了tkmapper包里的接口，可以看一下继承的这几个接口有哪些通用方法

1.BaseMapper

//BaseMapper继承了四个mapper，从命名上可以看出是增删改查相关的mapper
@tk.mybatis.mapper.annotation.RegisterMapper
public interface BaseMapper<T> extends
        BaseSelectMapper<T>,
        BaseInsertMapper<T>,
        BaseUpdateMapper<T>,
        BaseDeleteMapper<T> {

}
//查询mapper
@RegisterMapper
public interface BaseSelectMapper<T> extends
		//根据实体类中的属性值查询出一个结果，如果有多个则会抛出异常
        SelectOneMapper<T>,
        //根据实体类的属性查询出一个集合
        SelectMapper<T>,
        //查询出所有的结果
        SelectAllMapper<T>,
        //根据实体中的属性查询总数
        SelectCountMapper<T>,
        //根据主键查询结果
        SelectByPrimaryKeyMapper<T>,
        ExistsWithPrimaryKeyMapper<T> {

}
//添加mapper
@RegisterMapper
public interface BaseInsertMapper<T> extends
		//保存一个实体，null的属性也会保存，不会使用数据库默认值
        InsertMapper<T>,
        //保存一个实体，null的属性不会保存，会使用数据库默认值
        InsertSelectiveMapper<T> {

}
//修改mapper
@RegisterMapper
public interface BaseUpdateMapper<T> extends
		//根据主键更新实体全部字段，null值会被更新
        UpdateByPrimaryKeyMapper<T>,
        //根据主键更新属性不为null的值
        UpdateByPrimaryKeySelectiveMapper<T> {

}
//删除mapper
@RegisterMapper
public interface BaseDeleteMapper<T> extends
		//根据实体属性作为条件进行删除
        DeleteMapper<T>,
        //根据主键字段进行删除
        DeleteByPrimaryKeyMapper<T> {
}

2.IdsMapper

@tk.mybatis.mapper.annotation.RegisterMapper
public interface IdsMapper<T> extends SelectByIdsMapper<T>, DeleteByIdsMapper<T> {

}
public interface SelectByIdsMapper<T> {

    /**
     * 根据主键字符串进行查询，类中只有存在一个带有@Id注解的字段
     *
     * @param ids 如 "1,2,3,4"
     * @return
     */
    @SelectProvider(type = IdsProvider.class, method = "dynamicSQL")
    List<T> selectByIds(String ids);

}
public interface DeleteByIdsMapper<T> {

    /**
     * 根据主键字符串进行删除，类中只有存在一个带有@Id注解的字段
     *
     * @param ids 如 "1,2,3,4"
     * @return
     */
    @DeleteProvider(type = IdsProvider.class, method = "dynamicSQL")
    int deleteByIds(String ids);

}

3.InsertListMapper

public interface InsertListMapper<T> {

    /**
     * 批量插入，支持批量插入的数据库可以使用，例如MySQL,H2等，另外该接口限制实体包含`id`属性并且必须为自增列
     *
     * @param recordList
     * @return
     */
    @Options(useGeneratedKeys = true)
    @InsertProvider(type = SpecialProvider.class, method = "dynamicSQL")
    int insertList(List<? extends T> recordList);

}

4.ConditionMapper

public interface ConditionMapper<T> extends
        SelectByConditionMapper<T>,
        SelectCountByConditionMapper<T>,
        DeleteByConditionMapper<T>,
        UpdateByConditionMapper<T>,
        UpdateByConditionSelectiveMapper<T> {

}

五、mapper.xml

1、mapper文件里使用if判断

//判断集合不等于空，不能写为list！=“”
<if test="list!=null and list.size>0”> 
    And ...
</if>
//判断字符串是否等于某个值，需要将值用双引号括起来，如果使用单引号需要使用tostring方法
<if test=‘name==“Tag"'> 
    And ...
</if>

2、#和$的区别
#是会默认给参数的左右加上双引号，避免sql注入攻击
$是直接把参数传入，在使用模糊查询的时候使用

<select id="queryByGoldDesc" resultType="string">
    select transaction_no from tb_gold_account where gold_desc like "%${goldDesc}"
  </select>