SpringBoot 使用mybatil+Provider进行MYSQL的增删改查

随便简单的找了个hd_tag表测试

 

1、先添加spring依赖  pom.xml添加

        <!-- 连接数据库依赖  -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <!--mysql数据库驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.0</version>
        </dependency>

2、新建tag实体类

package com.projuct.junlaishun.entity;

import lombok.Data;

@Data
public class Tage {
    private Integer tid;
    private String tag;
    private Integer total;
}

3、创建Provider类

package com.projuct.junlaishun.mapper;


import com.projuct.junlaishun.entity.Tage;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.stereotype.Component;


@Slf4j
@Component
public class TagProvider {
    private String prefix = "hd_";  //数据库表前缀
    private Integer begin = 0;
    private Integer end = 10;
    /**
     * 查询信息详情
     *
     * @param tid 信息id
     * @return sql
     */
    public String getTagInfo(Integer tid) {
        log.info("prefix:{}",prefix);
        return "SELECT * FROM "+prefix+"tag WHERE tid = #{tid}";
    }

    /**
     * 添加数据
     * @param tag
     * @return
     */
    public String add(Tage tag){
        return new SQL(){
            {
                INSERT_INTO(prefix+"tag");
                if(tag.getTag() != null){
                    VALUES("tag","#{tag}");
                }
                if(tag.getTotal() != null){
                    VALUES("total","#{total}");
                }
            }
        }.toString();
    }

    /**
     * 更新数据
     * @param tag
     * @return
     */
    public String update(Tage tag){
        return new SQL(){
            {
                UPDATE(prefix+"tag");
                if(tag.getTag() != null){
                    SET("tag = #{tag}");
                }
                if(tag.getTotal() != null){
                    SET("total = #{total}");
                }
                WHERE("tid = #{tid}");
            }
        }.toString();
    }

    /**
     * 根据ID删除信息
     * @param tid
     * @return
     */
    public String del(Integer tid){
        return new SQL(){
            {
                DELETE_FROM(prefix+"tag");
                WHERE("tid=#{tid}");
            }
        }.toString();
    }

    /**
     * 分页查询
     * 还可以不使用SQL类  直接使用以下方法查询
     * @param tage 信息
     * @return 列表信息
     */
    public String listPag(Tage tage,Integer page,Integer limit) {
        if(page != null && limit != null){
            begin = (page - 1) * limit;
        }
        String sql = "SELECT * FROM `hd_tag`";
        if(tage.getTag() != null){
            sql = sql+" WHERE `tag` LIKE '%"+tage.getTag()+"%'";
        }
        sql = sql+" LIMIT "+begin+","+limit;
        log.info("sql:{}",sql);
        return sql;
    }

}

4、创建表mapper类

package com.projuct.junlaishun.mapper;

import com.projuct.junlaishun.entity.Tage;
import lombok.Value;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface TagMapper {

    /**
     * 根据id查询信息
     * @param tid
     * @return
     */
    @Select("SELECT * FROM hd_tag WHERE tid = #{tid}")
    Tage getTagInfo(@Param("tid") Integer tid);
    /*使用Provider方法查询
    @SelectProvider(type = TagProvider.class, method = "getTagInfo")
    Tage getTagInfo(@Param("tid") Integer tid);*/


    /**
     * 添加数据
     * @param tag
     * @return
     */
    @Insert("INSERT INTO hd_tag(tag,total) VALUE (#{tag},#{total})")
    @SelectKey(before = false, keyColumn = "tid", keyProperty = "tid", statement = "select last_insert_id()", resultType = Integer.class)   //返回自增主键tid
    Integer add(Tage tag);

    /* 使用Provider方法添加   @InsertProvider(type = TagProvider.class,method = "add")
    @SelectKey(before = false, keyColumn = "tid", keyProperty = "tid", statement = "select last_insert_id()", resultType = Integer.class)   //返回自增主键tid   返回的自增tid 可以使用  tag.getTid()获得
    Integer add(Tage tag);*/


    /**
     * 更新数据
     * @param tag
     * @return
     */
    /*直接使用方法    @Update("UPDATE `text`.`hd_tag` SET `tag` = '#{tag}', `total` = #{total} WHERE `tid` = #{tid}")
    Integer update(Tage tag);*/
    /*使用Provider方法*/
    @UpdateProvider(type = TagProvider.class,method = "update")
    Integer update(Tage tag);
    /**
     *通过tid删除信息
     * @param tid
     * @return
     */
/*    @Delete("DELETE FROM `text`.`hd_tag` WHERE `tid` = #{id}")
    Integer del(Integer id);*/
    @DeleteProvider(type = TagProvider.class,method = "del")
    Integer del(Integer tid);

    /**
     * 分页查询
     * @param tage
     * @return
     */
    @SelectProvider(type = TagProvider.class,method = "listPag")
    List<Tage> listPag(Tage tage,Integer page,Integer limit);
}

5、创建控制器文件

package com.projuct.junlaishun.controller.model;
 
 
import com.projuct.junlaishun.entity.Tage;
import com.projuct.junlaishun.mapper.TagMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
 
import javax.websocket.server.PathParam;
import java.util.List;
 
@Slf4j
@RestController
@ResponseBody
@RequestMapping("/tag")
public class Tag {
    private static TapMapper tapMapper;
    @Autowired
    public void setMapper(TapMapper tapMapper) {
        Tag.tapMapper = tapMapper;
    }   
    @RequestMapping("abc11")
    public String index(){
        return "prefix";
    }
 
    /**
     * 通过用户id获取信息
     * @param tid
     * @return
     */
    @RequestMapping(value = "/getUser/{tid}")
    public String GetTag(@PathVariable Integer tid) {
        System.out.print("tid:"+tid);
        System.out.print(tagMapper.getTagInfo(tid).toString());
        return tagMapper.getTagInfo(tid).getTag();
    }
 
    /**
     * 添加数据
     * @return
     */
    @RequestMapping(value = "/add",method = RequestMethod.POST)
    public Integer addData(Tage tags) {
       tagMapper.add(tags);
       return tags.getTid();
    }
 
    /**
     * 更新数据
     * @return
     */
    @RequestMapping(value = "/update",method = RequestMethod.POST)
    public Integer update(Tage tags) {
        return tagMapper.update(tags);
    }
 
    /**
     * 通过tid删除信息
     * @param tid
     * @return
     */
    @RequestMapping(value = "/del/{tid}",method = RequestMethod.GET)
    public Integer del(@PathVariable("tid") Integer tid){
        log.info("tid:{}",tid);
        return tagMapper.del(tid);
    }
 
    @RequestMapping(value = "/listPage")
    public List<Tage> listPage(Tage tag, @PathParam("page") Integer page,@PathParam("limit") Integer limit){
        //tag.setTag("%"+tag.getTag()+"%");   //模糊搜索
        return tagMapper.listPag(tag,page,limit);
    }
 
 
}

　　

6、项目运行文件增加mapper扫描

package com.projuct.junlaishun;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication

@MapperScan("com.projuct.junlaishun.mapper") //扫描的mapper，这样不必在每个Mapper上加上Mapper注解
public class JunlaishunApplication {
    public static void main(String[] args) {
        SpringApplication.run(JunlaishunApplication.class, args);
    }
}

　　

到此 完成表hd_tag的增删改查

以分页查询为例：