Springboot 技术整合--笔记2-2--mybatis注
2019-06-05 本文已影响0人
牵手生活
关键步骤
- 数据库脚本
- 表或查询对应的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分页插件，你准备怎么实现
