快捷導(dǎo)航

springboot 2.x整合mybatis實(shí)現(xiàn)增刪查和批量處理方式

更新時間：2021年09月09日 15:14:42 作者：M_Blood

這篇文章主要介紹了springboot 2.x整合mybatis實(shí)現(xiàn)增刪查和批量處理方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

springboot 2.x整合mybatis實(shí)現(xiàn)增刪查和批量處理

話不多說，直接上代碼：

1.添加依賴

       <!--mybatis數(shù)據(jù)庫整合-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <!-- MySQL的JDBC驅(qū)動包-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!--引入第三方數(shù)據(jù)源-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.6</version>
        </dependency>

2.添加配置文件

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

#設(shè)置數(shù)據(jù)源（默認(rèn)數(shù)據(jù)源是com.zaxxer.hikari.HikariDataSource）
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
#數(shù)據(jù)庫登錄賬號
spring.datasource.username=root
#數(shù)據(jù)庫登錄密碼
spring.datasource.password=123456
#數(shù)據(jù)庫連接
spring.datasource.url=jdbc:mysql://localhost:3306/customer?useUnicode=true&characterEncoding=utf-8
#驅(qū)動(會自動檢測配置，可以注釋掉)
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#設(shè)置需要被掃描的包
#mybatis.type-aliases-package=java.com.example.demo
#打印sql語句(一般用于本地開發(fā)測試)
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

3.Application.class添加掃描

（路徑為自己項(xiàng)目package的路徑）

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

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

4.創(chuàng)建Mapper

@Mapper
public interface DemoMapper {

    //增
    @Insert("INSERT INTO demo(name,age)VALUES(#{name},#{age})")
    @Options(useGeneratedKeys=true, keyProperty="id", keyColumn="id") //獲取插入后自動生成的主鍵,keyProperty對應(yīng)類屬性名，keyColumn對應(yīng)數(shù)據(jù)庫字段名
    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);

    //改，注意：需要指定參數(shù)映射@Param，如不指定，可按下面的方法執(zhí)行
    @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.創(chuàng)建provider實(shí)現(xiàn)類

為注解@UpdateProvider、@InsertProvider、@DeleteProvider、@SelectProvider返回可執(zhí)行SQL語句，需注意：要添加@Param注解，指定映射參數(shù)

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();
    }
}