springboot 2.x整合mybatis实现增删查和批量处理方式

springboot 2.x整合mybatis实现增删查和批量处理

话不多说，直接上代码：

1.添加依赖

     <!--mybatis数据库整合-->
      <dependency>
          <groupId>org.mybatis.spring.boot</groupId>
          <artifactId>mybatis-spring-boot-starter</artifactId>
          <version>1.3.2</version>
      </dependency>
      <!-- MySQL的JDBC驱动包-->
      <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <scope>runtime</scope>
      </dependency>
      <!--引入第三方数据源-->
      <dependency>
          <groupId>com.alibaba</groupId>
          <artifactId>druid</artifactId>
          <version>1.1.6</version>
      </dependency>

2.添加配置文件

#---------------------
# mybatis配置
#---------------------

#设置数据源（默认数据源是com.zaxxer.hikari.HikariDataSource）
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
#数据库登录账号
spring.datasource.username=root
#数据库登录密码
spring.datasource.password=123456
#数据库连接
spring.datasource.url=jdbc:mysql://localhost:3306/customer?useUnicode=true&characterEncoding=utf-8
#驱动(会自动检测配置，可以注释掉)
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#设置需要被扫描的包
#mybatis.type-aliases-package=java.com.example.demo
#打印sql语句(一般用于本地开发测试)
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

3.Application.class添加扫描

（路径为自己项目package的路径）

@SpringBootApplication
@ServletComponentScan
@MapperScan("com.example.mapper") //mybatis包扫描
public class DemoApplication {

  public static void main(String[] args) {
      SpringApplication.run(DemoApplication.class, args);
  }
}

4.创建Mapper

@Mapper
public interface DemoMapper {

  //增
  @Insert("INSERT INTO demo(name,age)VALUES(#{name},#{age})")
  @Options(useGeneratedKeys=true, keyProperty="id", keyColumn="id") //获取插入后自动生成的主键,keyProperty对应类属性名，keyColumn对应数据库字段名
  int add(Demo demo);

  //删
  @Delete("DELETE FROM demo WHERE id=#{id}")
  boolean deleteById(int id);

  //查
  @Select("SELECT * FROM demo WHERE id=#{id}")
  Demo findById(int id);
  @Select("SELECT * FROM demo WHERE login=#{login} AND password=#{password}")
  Demo findByObject(@Param("login") String login,@Param("password") String password);

  //改，注意：需要指定参数映射@Param，如不指定，可按下面的方法执行
  @Update("UPDATE demo SET name=#{name} WHERE id =#{id}")
  boolean updateById(@Param("name") String name,@Param("id") int id);
  @Update("UPDATE demo SET name=#{name} WHERE id =#{id}")
  boolean update_2ById(Demo demo);


  //批量增
  @InsertProvider(type = LoginProvider.class,method = "insert")
  int insert(@Param("demoList")List<Demo> demoList);

  //批量删
  @DeleteProvider(type = LoginProvider.class,method = "delete")
  boolean delete(@Param("demoList")List<Demo> demoList);

  //批量查
  @Select("SELECT * FROM demo")
  List<Demo> find();
  @SelectProvider(type = LoginProvider.class,method = "find2")
  List<Demo>find2(@Param("demoList")List<Demo> demoList);


  //批量改
  @UpdateProvider(type = LoginProvider.class,method = "update")
  boolean update(@Param("demoList")List<Demo> demoList);


}

5.创建provider实现类

为注解@UpdateProvider、@InsertProvider、@DeleteProvider、@SelectProvider返回可执行SQL语句，需注意：要添加@Param注解，指定映射参数

public class LoginProvider {

  public String insert(@Param("demoList")List<Demo> demoList){
      StringBuilder builder=new StringBuilder();
      builder.append("INSERT INTO demo(name,age)VALUES");
      String message="(''{0}'',{1})";
      int i=1;
      for (Demo demo : demoList) {
          String s = MessageFormat.format(message, demo.getName(), demo.getAge());
          builder.append(s);
          if (i==demoList.size()){break;}
          builder.append(",");
          i++;
      }
      return builder.toString();
  }

  public String delete(@Param("demoList")List<Demo> demoList){
      StringBuilder builder=new StringBuilder();
      builder.append("DELETE FROM demo WHERE id IN (");
      int i=1;
      for (Demo demo : demoList) {
          builder.append(demo.getId());
          if (i==demoList.size()){break;}
          builder.append(",");
          i++;
      }
      builder.append(")");
      return builder.toString();
  }

  public String find2(@Param("demoList")List<Demo> demoList){
      StringBuilder builder=new StringBuilder();
      builder.append("SELECT * FROM demo WHERE id IN (");
      int i=1;
      for (Demo demo : demoList) {
          builder.append(demo.getId());
          if (i==demoList.size()){break;}
          builder.append(",");
          i++;
      }
      builder.append(")");
      return builder.toString();
  }

  public String update(@Param("demoList")List<Demo> demoList){
      StringBuilder builder=new StringBuilder();
      builder.append("INSERT INTO demo(id,name,age)VALUES");
      String message="({0},''{1}'',{2})";
      int i=1;
      for (Demo demo : demoList) {
          String s = MessageFormat.format(message, demo.getId(), demo.getName(), demo.getAge());
          builder.append(s);
          if (i==demoList.size()){break;}
          builder.append(",");
          i++;
      }
      builder.append("on duplicate key update id=VALUES(id),age=values(age),name=VALUES(name)");
      return builder.toString();
  }
}