关键步骤

• 数据库脚本
• 表或查询对应的pojo实现
• 表或查询对应的Mapper
• 单元测试&运行
• 表或查询的Mapper对应的Service(@Service)
• web api对应的Controller

数据库准备

用powerDesigner设计表nw_robotInfo

powerDesigner设计表

运行脚本

drop table if exists robotInfo;

/*==============================================================*/
/* Table: robotInfo                                             */
/*==============================================================*/
create table robotInfo
(
   pluginisActvie       int comment '插件是否激活 1:激活；0：未激活',
   pluginVersionCode    varchar(32) comment '插件版本号，如：1905161446 ',
   systemVersion        varchar(32) binary comment '系统版本号Android或ios的系统版本',
   imei                 varchar(255) not null comment '手机imei，如果没用授权则为：imei88888888',
   xposedIsInstall      int comment 'xposed框架是否安装 1:已安装、0 未安装',
   wechatIsInstall      int comment '手机是否安装微信 ',
   wechatVersionCode    varchar(50) binary comment '微信的版本号 如：6.7.3',
   wechatStatus         int comment '微信登录状态  :0：未登录；1：登录；2：封号或异常',
   supportCurWechat     int comment '是否支持当前微信版本',
   nickName             varchar(255) binary comment '微信昵称',
   wechatId             varchar(255) binary not null comment '微信原始id',
   primary key (imei, wechatId)
);

alter table robotInfo comment '微信机器人信息（对应心跳）';

INSERT INTO `nw_robotInfo` VALUES (0, '1905161446', '4.4', '', 1, 1, '6.6.0', 0, 1, '喔喔我', 'wxid_on8oksh88zo22');

image.png

写数据库表对应的pojo实体类nw_robotInfo
采用idea的mybatis插件生成，后来发现根本没用必要

/**
 * 机器人
 * 特别说明：之前采用mybatis的逆向生成对象，发现字段明会与数据库不一致，估计是驼峰转换问题，会造成get 和set方法找不到的问题
 * 设为true表示开启驼峰转换：mybatis.configuration.mapUnderscoreToCamelCase=true
 * 如果用使用@Data 注解需要lombok插件支持，@Data =@Getter +@Setter*
 */
public class Robotinfo {
    /**
     * 手机imei，如果没用授权则为：imei88888888
     */

    private String imei;

    /**
     * 微信原始id
     */

    private String wechatId;

    /**
     * 插件是否激活 1:激活；0：未激活
     */
    private Integer pluginisActvie;

    /**
     * 插件版本号，如：1905161446 
     */
    private String pluginVersionCode;

    /**
     * 系统版本号Android或ios的系统版本
     */
    private String systemVersion;

    /**
     * xposed框架是否安装 1:已安装、0 未安装
     */
    private Integer xposedIsInstall;

    /**
     * 手机是否安装微信 
     */
    private Integer wechatIsInstall;

    /**
     * 微信的版本号 如：6.7.3
     */
    private String wechatVersionCode;

    /**
     * 微信登录状态  :0：未登录；1：登录；2：封号或异常
     */
    private Integer wechatStatus;

    /**
     * 是否支持当前微信版本
     */
    private Integer supportCurWechat;

    /**
     * 微信昵称
     */
    private String nickName;

    public Robotinfo() {
        pluginisActvie =0;
        xposedIsInstall =0;
        wechatIsInstall =0;
        wechatStatus =0;
        supportCurWechat =0;
    }

    public String getImei() {
        return imei;
    }

    public void setImei(String imei) {
        this.imei = imei;
    }

    public String getWechatId() {
        return wechatId;
    }

    public void setWechatId(String wechatId) {
        this.wechatId = wechatId;
    }

    public Integer getPluginisActvie() {
        return pluginisActvie;
    }

    public void setPluginisActvie(Integer pluginisActvie) {
        this.pluginisActvie = pluginisActvie;
    }

    public String getPluginVersionCode() {
        return pluginVersionCode;
    }

    public void setPluginVersionCode(String pluginVersionCode) {
        this.pluginVersionCode = pluginVersionCode;
    }

    public String getSystemVersion() {
        return systemVersion;
    }

    public void setSystemVersion(String systemVersion) {
        this.systemVersion = systemVersion;
    }

    public Integer getXposedIsInstall() {
        return xposedIsInstall;
    }

    public void setXposedIsInstall(Integer xposedIsInstall) {
        this.xposedIsInstall = xposedIsInstall;
    }

    public Integer getWechatIsInstall() {
        return wechatIsInstall;
    }

    public void setWechatIsInstall(Integer wechatIsInstall) {
        this.wechatIsInstall = wechatIsInstall;
    }

    public String getWechatVersionCode() {
        return wechatVersionCode;
    }

    public void setWechatVersionCode(String wechatVersionCode) {
        this.wechatVersionCode = wechatVersionCode;
    }

    public Integer getWechatStatus() {
        return wechatStatus;
    }

    public void setWechatStatus(Integer wechatStatus) {
        this.wechatStatus = wechatStatus;
    }

    public Integer getSupportCurWechat() {
        return supportCurWechat;
    }

    public void setSupportCurWechat(Integer supportCurWechat) {
        this.supportCurWechat = supportCurWechat;
    }

    public String getNickName() {
        return nickName;
    }

    public void setNickName(String nickName) {
        this.nickName = nickName;
    }
}

写nw_robotInfo的Mapper RobotinfoMapper

/**
 * Robotinfo采用mybatis的注解方式
 * Mybatis注解的方式比较简单，只要定义一个dao接口，然后sql语句通过注解写在接口方法上。最后给这个接口添加@Mapper注解或者在启动类上添加@MapperScan(“com.springboot.dao”)注解都行。
 */
@Mapper
public interface RobotinfoMapper {
    @Select("SELECT * FROM nw_robotInfo WHERE wechatId = #{wechatId}")
    Robotinfo findByWechatId(@Param("wechatId") String wechatId);

    @Select("SELECT * FROM nw_robotInfo ")
    List<Robotinfo> findAll();

   //错误提示：org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'pluginisActvie' in 'class com.younghare.pojo.Robotinfo'
   //原因是需要getPluginisActvie，结果idea自动生成的方法是getPluginisactvie

    @Insert("insert into nw_robotInfo(pluginisActvie,pluginVersionCode,systemVersion,imei,xposedIsInstall" +
            ",wechatIsInstall,wechatVersionCode,wechatStatus,supportCurWechat,nickName" +
            ",wechatId) " +
            "values(#{pluginisActvie},#{pluginVersionCode},#{systemVersion},#{imei},#{xposedIsInstall}" +
            ",#{wechatIsInstall},#{wechatVersionCode},#{wechatStatus},#{supportCurWechat},#{nickName}" +
            ",#{wechatId})")
    int insertByRobotinfo(Robotinfo robotinfo);


    @Update("UPDATE nw_robotInfo SET pluginisActvie=#{pluginisActvie},pluginVersionCode=#{pluginVersionCode},systemVersion=#{systemVersion},imei=#{imei},xposedIsInstall=#{xposedIsInstall}" +
            ",wechatIsInstall=#{wechatIsInstall},wechatVersionCode=#{wechatVersionCode},wechatStatus=#{wechatStatus},supportCurWechat=#{supportCurWechat},nickName=#{nickName}" +
            " WHERE wechatId =#{wechatId}")
    void update(Robotinfo robotinfo);


    @Delete("DELETE FROM nw_robotInfo WHERE wechatId =#{wechatId}")
    void delete(String wechatId);




}

写测试用例进行测试

@SpringBootTest(classes= WechatTaskApplication.class)
@RunWith(SpringJUnit4ClassRunner.class)
public class RobotinfoMapperTest {
    @Autowired
    RobotinfoMapper robotinfoMapper;
    @Test
    public void findRobotInfoByWechatIdTest(){
        Robotinfo robotinfo = robotinfoMapper.findByWechatId("wxid_on8oksh88zo22");
        if (null != robotinfo){
            System.out.println("=========**"+robotinfo.getNickName());
        }else {
            System.out.println("没用找到记录");
        }

    }

    //private String wechatId_new = "wxid_on8oksh88zo33";

    public int InsertRobotInfo(String wechatId){
        Robotinfo robotinfo = new Robotinfo();
        robotinfo.setPluginVersionCode("1905071513");


        robotinfo.setImei(wechatId+"IMEI");
        robotinfo.setNickName(wechatId+"测试昵称");

        robotinfo.setXposedIsInstall(1);

        robotinfo.setWechatId(wechatId);
        int ret = robotinfoMapper.insertByRobotinfo(robotinfo);

        System.out.println("插入结果"+ ret);
        return ret;


    }

    @Test
    public void InsertRobotInfoTest(){
        String wechatId = "wxid_on8oksh88zo44";

        InsertRobotInfo("wxid_on8oksh88zo44");

        Robotinfo robotinfo = robotinfoMapper.findByWechatId(wechatId);

        if (null != robotinfo){
            System.out.println("=====robotinfo2 插入成功====**"+robotinfo.getNickName());
        }else {
            System.out.println("robotinfo2插入失败没用找到记录");
        }


    }

    /**
     * 插入多条记录用与当测试数据
     */

    @Test
    public void InsertRobotInfoTest2(){

        Robotinfo robotinfo = robotinfoMapper.findByWechatId("wxid_ceshi0019");
        if (robotinfo !=null){
            return;
        }

        for (int index =0;index<20;index++){
            String strIndex = String.format("%04d", index);  //数字不足4位前面补0
            String wechatId = "wxid_ceshi"+strIndex;
            InsertRobotInfo(wechatId);
        }

    }



    @Test
    public void updateRobotInfoTest(){
        String wechatId = "wxid_on8oksh88zo44";

        //先查找看记录是
        Robotinfo robotinfo = robotinfoMapper.findByWechatId(wechatId);
        if (null != robotinfo){

            robotinfo.setNickName(robotinfo.getNickName()+"update");
            robotinfoMapper.update(robotinfo);
        }

    }

    @Test
    public void delRobotInfoTest(){
        String wechatId = "wxid_on8oksh88zo55";

        //先查找看记录是
        Robotinfo robotinfo = robotinfoMapper.findByWechatId(wechatId);
        if (null == robotinfo){
            int ret =InsertRobotInfo(wechatId);

            Robotinfo robotinfo2 = robotinfoMapper.findByWechatId(wechatId);
            System.out.println("临时插入的wechatId:"+robotinfo2.getWechatId());
            Assert.assertEquals(wechatId, robotinfo2.getWechatId());
        }

        robotinfoMapper.delete(wechatId);
        Robotinfo robotinfo3 = robotinfoMapper.findByWechatId(wechatId);
        boolean isDel = robotinfo3!=null? false:true;
        System.out.println("删除后的情况记录存在？isDel :"+isDel);
        Assert.assertNull(robotinfo3);


    }
    @Test
    public void findAllTest(){
        int page =2;
        int pageSize =10;

        PageInfo<Robotinfo> info = findAll(page,pageSize);

        System.out.println("打印其实是用于断点查看："+ JSONObject.toJSONString(info));




    }
    /**
     * 分页查询
     * @param page 当前页数
     * @param pageSize 每页个数
     * @return
     */
    public PageInfo<Robotinfo> findAll(int page ,int pageSize){
        PageHelper.startPage(page, pageSize);//改写语句实现分页查询
        List<Robotinfo> all = robotinfoMapper.findAll();
        PageInfo<Robotinfo> info = new PageInfo<>(all);
        return info;


    }



}

运行删除记录的测试用例结果

image.png

在application.properties添加mybatis的sql打印

#将mapper接口所在包的日志级别改成debug，可以在控制台打印sql
logging.level.com.younghare.mapper:debug

mybatis sql 日志打印

运行findAllTest单元测试

运行findAllTest单元测试

输出json字符串格式化

findAllTest输出

看pagehelper是如何查询数据库--分步骤查询

pagehelper其实是分次查询

编写RoboinfoService (注意注解@Service)

@Service
public class RobotinfoService {
    @Autowired
    private RobotinfoMapper robotinfoMapper;

    /**
     * 查询机器人信息
     * @param wechatId
     * @return
     */
    public Robotinfo findByWechatId(String wechatId){
        return robotinfoMapper.findByWechatId(wechatId);
    }

    /**
     * 分页查询
     * @param page
     * @param pageSize
     * @return
     */
    public PageInfo<Robotinfo> findAll(int page,int pageSize){
        PageHelper.startPage(page, pageSize);//改写语句实现分页查询
        List<Robotinfo> all = robotinfoMapper.findAll();
        PageInfo<Robotinfo> info = new PageInfo<>(all);
        return info;
    }

    /**
     * 插入
     * @param robotinfo
     * @return
     */
    public int insert(Robotinfo robotinfo ){
        return robotinfoMapper.insertByRobotinfo(robotinfo);
    }

    /**
     * 更新记录
     * @param robotinfo
     */
    public void update(Robotinfo robotinfo){
        robotinfoMapper.update(robotinfo);
    }

    /**
     * 插入或更新记录
     * @param robotinfo
     */
    public void insertOrUpdate(Robotinfo robotinfo){
        Robotinfo robotinfo_tmp = robotinfoMapper.findByWechatId(robotinfo.getWechatId());
        if (null == robotinfo_tmp){
            robotinfoMapper.insertByRobotinfo(robotinfo);
        }else {
            robotinfoMapper.update(robotinfo);
        }

    }

    /**
     * 删除记录
     * @param wechatId
     */
    public void delete(String wechatId){
        robotinfoMapper.delete(wechatId);
    }





}

RobotinfoController

@RestController
@RequestMapping("wechatTask")
public class RobotinfoController {
    @Autowired
    private RobotinfoService robotinfoService;

    @RequestMapping("/robot/save")
    public IJSONResult saveRobotInfo(String wechatId,String imei,String robotJson){

        Robotinfo robotinfo = null;
        try {
            RobotApp robotApp =JSONObject.parseObject(robotJson,new TypeReference<RobotApp>(){});
            //robotinfo = JSONObject.parseObject(robotJson,new TypeReference<Robotinfo>(){});
            if (null != robotApp){
                robotinfo = RobotApp2Robotinfo(wechatId,robotApp);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        if (null == robotinfo){
            return IJSONResult.errorMsg("robotJson非法");
        }

        robotinfoService.insertOrUpdate(robotinfo);
        return IJSONResult.ok("保存成功");

    }



    @RequestMapping("/robot/delete")
    public IJSONResult deleteUser(String wechatId) {
        if (null ==robotinfoService.findByWechatId(wechatId)){
            return IJSONResult.errorMsg("该robot不存在！");
        }

        robotinfoService.delete(wechatId);
        return IJSONResult.ok("删除成功");
    }
    @RequestMapping("/robot/queryByWechatId")
    public IJSONResult queryByWechatId(String wechatId) {

        return IJSONResult.ok(robotinfoService.findByWechatId(wechatId));
    }


    @RequestMapping("/robot/queryPaged")  //查询分页
    public IJSONResult queryPaged(Integer page,Integer pageSize) {

        if (page == null) {
            page = 1;
        }
        if (pageSize == null){
            pageSize =10;
        }

        if (pageSize >50){
            return IJSONResult.errorMsg("每次请求记录数不允许超过50条记录！");
        }
        if (pageSize<1){
            return IJSONResult.errorMsg("每页请求记录数不合法！");
        }

        PageInfo<Robotinfo> pageInfo = robotinfoService.findAll(page,pageSize);

        return IJSONResult.ok(pageInfo);
    }


    /**
     * App发送过来json对象转化为数据库对应pojo对象
     * @param robotApp
     * @return
     */
    private Robotinfo RobotApp2Robotinfo(String wechatId,RobotApp robotApp){
        Robotinfo robotinfo = new Robotinfo();
        robotinfo.setPluginisActvie(robotApp.isPluginisActive()? 1:0);
        robotinfo.setPluginVersionCode(robotApp.getPluginVersionCode());

        robotinfo.setImei(robotApp.getImei());
        robotinfo.setXposedIsInstall(robotApp.isWechatIsInstall()? 1:0);
        robotinfo.setWechatIsInstall(robotApp.isXposedIsInstall()? 1:0);
        robotinfo.setWechatStatus(robotApp.getWechatStatus());
        robotinfo.setSupportCurWechat(robotApp.isSupportCurWechat()? 1:0);
        robotinfo.setNickName(robotApp.getNickName());

        robotinfo.setWechatId(wechatId);

        return robotinfo;
    }


}

运行效果

Mybatis注解方式insert时获取返回的自增主键

    //Mybatis注解方式insert时获取返回的自增主键
    //@SelectKey(statement="select last_insert_id()",before=false,keyProperty="snsItemInfo.db_id",resultType=Integer.class,keyColumn="id")
    //或
    @Options(useGeneratedKeys = true, keyProperty = "snsItemInfo.db_id")

    int insertBySnsItemInfo(SnsItemInfo snsItemInfo,String updateWechatId);

关于rabbitmq的文章收录

Spring Boot中使用MyBatis注解配置详解-今日头条
Spring Boot中使用MyBatis注解配置详解--推荐itcast
mybatis使用注解，多参数增删改查-含测试用例
Springboot集成Mybatis+PageHelper
SpringBoot-15-之整合MyBatis-注解篇+分页
如果让你写一个MyBatis分页插件，你准备怎么实现