Springboot集成Mybatis使用注解SQL

⼀、建⽴表sys_user_tab

CREATE TABLE `suphowe`.`Untitled` ( `id` int(11) NOT NULL,

`user` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `tel` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `addr` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `duty` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

⼆、添加依赖

org.mybatis.spring.boot

mybatis-spring-boot-starter 1.3.0

三、配置properties

# ===================mybatis========================mybatis.mapper-locations=classpath:mapper/*.xmlmybatis.type-aliases-package=com.soft.entity

四、编写Java

User.java

package com.soft.entity;public class User { private Integer id; private String user; private String name; private String tel; private String addr; private String duty; public Integer getId() { return id; }

public void setId(Integer id) { this.id = id; }

public String getUser() { return user; }

public void setUser(String user) { this.user = user; }

public String getName() { return name; }

public void setName(String name) { this.name = name; }

public String getTel() { return tel; }

public void setTel(String tel) { this.tel = tel; }

public String getAddr() { return addr; }

public void setAddr(String addr) { this.addr = addr; }

public String getDuty() { return duty; }

public void setDuty(String duty) { this.duty = duty; }

@Override

public String toString() { return \"User{\" + \"id=\" + id +

\ \ \ \ \ '}'; }}

UserController.java

package com.soft.controller;

import com.google.gson.Gson;import com.soft.entity.User;

import com.soft.service.IUserService;import com.soft.util.BsUtil;

import io.swagger.annotations.Api;

import io.swagger.annotations.ApiImplicitParam;import io.swagger.annotations.ApiImplicitParams;import io.swagger.annotations.ApiOperation;

import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.*;

import org.springframework.web.multipart.MultipartFile;

import org.springframework.web.multipart.commons.CommonsMultipartFile;import javax.servlet.http.HttpServletRequest;import java.io.File;

import java.util.HashMap;import java.util.List;

@RestController

@Api(value = \"Mybatis测试,注解\")@RequestMapping(value = \"/user\")public class UserController { @Autowired

public IUserService userService; @Autowired

public BsUtil bsUtil;

@RequestMapping(value = \"/findUser\ @ApiOperation(value = \"Mybatis查询测试\ @ApiImplicitParams({

@ApiImplicitParam(paramType = \"query\ })

public String findUser(Integer id) {

List list = userService.findUser(id);

HashMap result = new HashMap(); bsUtil.createReturnMsg(result, \"0\ return new Gson().toJson(result); }

@RequestMapping(value = \"/login\ @ApiOperation(value = \"User登陆测试\ @ApiImplicitParams({

@ApiImplicitParam(paramType = \"query\

@ApiImplicitParam(paramType = \"query\ })

public String login(String user, String passwd) { int code = 0;

String message = \"success\";

if(!user.equals(\"admin\") || !passwd.equals(\"admin123\")){ code = 1;

message = \"fail to login\"; }

HashMap result = new HashMap(); HashMap meta = new HashMap(); meta.put(\"code\

meta.put(\"message\ result.put(\"meta\

return new Gson().toJson(result); }

@RequestMapping(value = \"/findUserByName\ @ApiOperation(value = \"Mybatis查询测试LIKE\ @ApiImplicitParams({

@ApiImplicitParam(paramType = \"query\ @ApiImplicitParam(paramType = \"query\ })

public String findUserByName(String name, int beginRow) {

List list = userService.findUserByName(name, beginRow); HashMap result = new HashMap(); bsUtil.createReturnMsg(result, \"0\ return new Gson().toJson(result); }

@RequestMapping(value = \"/findUserChoose\ @ApiOperation(value = \"Mybatis查询测试,注解choose\ @ApiImplicitParams({

@ApiImplicitParam(paramType = \"query\

@ApiImplicitParam(paramType = \"query\ })

public String findUserChoose(Integer id, String name) {

List list = userService.findUserChoose(id, name);

HashMap result = new HashMap(); bsUtil.createReturnMsg(result, \"0\ return new Gson().toJson(result); }

@RequestMapping(value = \"/insertUser\ @ApiOperation(value = \"Mybatis新增测试\ @ApiImplicitParams({

@ApiImplicitParam(paramType = \"query\ @ApiImplicitParam(paramType = \"query\ @ApiImplicitParam(paramType = \"query\ @ApiImplicitParam(paramType = \"query\ @ApiImplicitParam(paramType = \"query\ @ApiImplicitParam(paramType = \"query\ })

public String insertUser(Integer id, String user, String name, String tel, String addr, String duty) { userService.insertUser(id, user, name, tel, addr, duty);

HashMap result = new HashMap(); bsUtil.createReturnMsg(result, \"0\ return new Gson().toJson(result); }

@RequestMapping(value = \"/updateUser\ @ApiOperation(value = \"Mybatis修改测试\ @ApiImplicitParams({

@ApiImplicitParam(paramType = \"query\ @ApiImplicitParam(paramType = \"query\ @ApiImplicitParam(paramType = \"query\

@ApiImplicitParam(paramType = \"query\ @ApiImplicitParam(paramType = \"query\ @ApiImplicitParam(paramType = \"query\ })

public String updateUser(Integer id, String user, String name, String tel, String addr, String duty) { userService.updateUser(id, user, name, tel, addr, duty);

HashMap result = new HashMap(); bsUtil.createReturnMsg(result, \"0\ return new Gson().toJson(result); }

@RequestMapping(value = \"/deleteUser\ @ApiOperation(value = \"Mybatis删除测试\ @ApiImplicitParams({

@ApiImplicitParam(paramType = \"query\ })

public String deleteUser(Integer id) { userService.deleteUser(id);

HashMap result = new HashMap(); bsUtil.createReturnMsg(result, \"0\ return new Gson().toJson(result); }

@RequestMapping(value = \"upload\ @ApiOperation(value = \"⽂件上传测试\

public String upload(HttpServletRequest request, MultipartFile file) throws Exception{ //file对象名记得和前端name属性值⼀致

System.out.println(new String (file.getOriginalFilename().getBytes(\"ISO-8859-1\"), \"UTF-8\")); System.out.println(file.getOriginalFilename()); System.out.println(file.getName()); HashMap reshm = new HashMap(); reshm.put(\"code\

reshm.put(\"message\ return new Gson().toJson(reshm); }}

IUserService.javapackage com.soft.service;

import com.soft.entity.User;import java.util.List;

public interface IUserService { List findUser(Integer id);

List findUserByName(String name, int beginRow); List findUserChoose(Integer id, String name);

void insertUser(Integer id, String user, String name, String tel, String addr, String duty); void updateUser(Integer id, String user, String name, String tel, String addr, String duty); void deleteUser(Integer id);}

UserService.java

package com.soft.service.impl;

import com.soft.dao.UserDao;import com.soft.entity.User;

import com.soft.service.IUserService;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.datasource.DataSourceTransactionManager;import org.springframework.stereotype.Service;

import org.springframework.transaction.TransactionDefinition;import org.springframework.transaction.TransactionStatus;

import org.springframework.transaction.annotation.Transactional;import java.util.List;

@Service

public class UserService implements IUserService { /**

* 事务控制,⼿动 */

@Autowired

private DataSourceTransactionManager dataSourceTransactionManager; /**

* 事务控制,⼿动 */

@Autowired

private TransactionDefinition transactionDefinition; @Autowired

UserDao userDao;

@Override

public List findUser(Integer id){ return userDao.findUser(id); }

@Override

public List findUserByName(String name, int beginRow){ return userDao.findUserByName(name, beginRow); }

@Override

public List findUserChoose(Integer id, String name){ return userDao.findUserChoose(id, name); }

@Override

public void deleteUser(Integer id){ userDao.deleteUser(id); }}

UserDao.java

package com.soft.dao;

import com.soft.entity.User;

import org.apache.ibatis.annotations.*;import java.util.List;@Mapper

public interface UserDao {

/**

* if 对内容进⾏判断

* 在注解⽅法中，若要使⽤MyBatis的动态SQL，需要编写在标签内

* 在 内使⽤特殊符号，则使⽤java的转义字符，如 双引号 \"\" 使⽤\"\" 代替 * concat函数：mysql拼接字符串的函数 */

@Select(\"\" )

List findUser(@Param(\"id\") Integer id);

/**

* if 对内容进⾏判断

* 在注解⽅法中，若要使⽤MyBatis的动态SQL，需要编写在标签内

* 在 内使⽤特殊符号，则使⽤java的转义字符，如 双引号 \"\" 使⽤\"\" 代替 * concat函数：mysql拼接字符串的函数 */

@Select(\"\" )

List findUserByName(@Param(\"name\") String name, @Param(\"beginRow\") int beginRow); /**

* choose when otherwise 类似Java的Switch，选择某⼀项 * when...when...otherwise... == if... if...else...

* choose标签是按顺序判断其内部when标签中的test条件出否成⽴，如果有⼀个成⽴，则 choose 结束 * 当 choose 中所有 when 的条件都不满则时，则执⾏ otherwise 中的sql */

@Select(\"\" )

List findUserChoose(@Param(\"id\") Integer id, @Param(\"name\") String name); @Insert(\"insert into sys_user_tab (id, user, name, tel, addr, duty)\" + \" values (#{id},#{user},#{name},#{tel},#{addr},#{duty})\") void insertUser(User user);

/**

* set 动态更新语句，类似 */

@Update(\"\")

void updateUser(User user);

/**

* foreach 遍历⼀个集合，常⽤于批量更新和条件语句中的 IN * foreach 批量更新

* (1) item：集合的元素，访问元素的Filed 使⽤ #{item.Filed} * (2) index: 下标，从0开始计数 * (3) collection：传⼊的集合参数 * (4) open：以什么开始

* (5) separator：以什么作为分隔符 * (6) close：以什么结束 */

@Insert(\" \")

int insertUserListWithForeach(List list); @Delete(\"\" )

void deleteUser(@Param(\"id\") Integer id);}

　五、测试

配置成功！