一,Mybatis框架介绍
MyBatis是支持定制化SQL,存储过程以及高级映射的优秀的持久层框架。MyBatis避免了几乎所有的JDBC代码和手动设置参数以及获取结果集。
MyBatis可以对配置和原生Map使用简单的XML或注解,将接口和Java的POJOS(Plian Old Java Objects,普通的Java对象)映射成数据库中的记录。
二,文章介绍
1,这篇文章主要基于MyBatis注解的形式进行单表项目的增删改查操作
2,常用的注解为
@Insert
@Delete
@Update
@Select
3,使用了MyBatis的高级特性Provider进行数据的批量操作,不过这里也只是用了起来,很简单,但是这里建议
使用xml的形式进行操作,Provider形式拼接sql太麻烦了。
三,项目依赖的jar包信息
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.68</version>
</dependency>
四,项目连接数据库的配置信息
server:
port: 8080
spring:
application:
name: mybatis-batch-operation
datasource:
url: jdbc:mysql://localhost:3306/study?serverTimezone=UTC
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
五,涉及的sql代码
create database if not exists study;
use study;
select database();
create table if not exists `user`
(
id int(11) auto_increment primary key comment "主键id",
username varchar(255) comment '姓名',
password varchar(255) comment '密码',
age int(3) comment '年纪'
) engine = InnoDb
charset = utf8;
insert into user(username,password,age) values("zhangsan","123456",1);
insert into user(username,password,age) values("lisi","123456",2);
insert into user(username,password,age) values("wangwu","123456",3);
insert into user(username,password,age) values("zhaoliu","123456",4);
insert into user(username,password,age) values("dengchao","123456",5);
insert into user(username,password,age) values("sunli","123456",6);
insert into user(username,password,age) values("huangxiaoming","123456",7);
insert into user(username,password,age) values("zhaowei","123456",8);
insert into user(username,password,age) values("haungbo","123456",9);
insert into user(username,password,age) values("shenteng","123456",10);
insert into user(username,password,age) values("gaoyuanyuan","123456",11);
insert into user(username,password,age) values("wanghan","123456",12);
六,由于这里都是基于自己的测试操作,写法上不满足正规的流程,比如说这里直接在controller层调用了userMapper的方法操作数据库,正常的应该还有一个userService接口的操作,这里简单了一些。
七,先看下UserController接口的代码
package com.wpw.mybatisbatchoperation.controller;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.wpw.mybatisbatchoperation.entity.User;
import com.wpw.mybatisbatchoperation.mapper.UserMapper;
import org.springframework.web.bind.annotation.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;
/**
* 用户控制器
*
* @author wpw
*/
@RestController
public class UserController {
private final UserMapper userMapper;
public UserController(UserMapper userMapper) {
this.userMapper = userMapper;
}
@GetMapping(value = "/select")
public User selectOne(@RequestParam(value = "userName") String userName, @RequestParam(value = "passWord") String passWord) {
return userMapper.selectOne(userName, passWord);
}
@DeleteMapping(value = "/delete")
public boolean delete(@RequestParam(value = "id") Integer id) {
return userMapper.delete(id);
}
@PutMapping(value = "/update")
public boolean update(@RequestBody User user) {
return userMapper.update(user);
}
@PostMapping(value = "/insert")
public int insert(@RequestBody User user) {
return userMapper.insert(user);
}
@GetMapping(value = "/selectList")
public List<User> selectList(@RequestParam(value = "userName") String userName) {
return userMapper.selectList(userName);
}
@PostMapping(value = "/batchInsert")
public int batchInsert(@RequestBody JSONObject jsonObject) {
return userMapper.batchInsert(jsonObject.getJSONArray("userList").toJavaList(User.class));
}
@DeleteMapping(value = "/batchDelete")
public boolean batchDelete(@RequestBody JSONObject jsonObject) {
return userMapper.batchDelete(jsonObject.getJSONArray("userList").toJavaList(User.class));
}
@PostMapping(value = "/batchList")
public List<User> batchList(@RequestBody JSONObject jsonObject) {
return userMapper.batchList(jsonObject.getJSONArray("userList").toJavaList(User.class));
}
@PutMapping(value = "/batchUpdate")
public boolean batchUpdate(@RequestBody JSONObject jsonObject) {
return userMapper.batchUpdate(jsonObject.getJSONArray("userList").toJavaList(User.class));
}
}
八,UserMapper接口操作数据的代码如下:
package com.wpw.mybatisbatchoperation.mapper;
import com.wpw.mybatisbatchoperation.entity.User;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;
import java.text.MessageFormat;
import java.util.List;
/**
* @author wpw
*/
@Mapper
@Repository
public interface UserMapper {
/**
* 保存用户信息
*
* @param user 用户信息
* @return 自增主键
*/
@Insert(value = "insert into user(username,password,age) values(#{userName},#{passWord},#{age})")
@Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
int insert(User user);
/**
* 根据用户id删除用户信息
*
* @param id 用户id
* @return 是否删除成功
*/
@Delete(value = "delete from user where id=#{id}")
boolean delete(Integer id);
/**
* 根据用户id进行用户信息的更新
*
* @param user 用户信息
* @return 是否更新成功
*/
@Update(value = "update user set username=#{userName},password=#{passWord} where id=#{id}")
boolean update(User user);
/**
* 根据用户名和密码进行查找
*
* @param userName 用户名
* @param passWord 密码
* @return 用户信息
*/
@Results(value = {
@Result(property = "id", column = "id"),
@Result(property = "userName", column = "username"),
@Result(property = "passWord", column = "password"),
@Result(property = "age", column = "age")
})
@Select(value = "select * from user where username=#{userName} and password=#{passWord}")
User selectOne(String userName, String passWord);
/**
* 根据用户名称查询用户列表信息
*
* @param userName 用户名称
* @return 用户列表
*/
@Results(value = {
@Result(property = "id", column = "id"),
@Result(property = "userName", column = "username"),
@Result(property = "passWord", column = "password"),
@Result(property = "age", column = "age")
})
@Select(value = "select * from user where username like #{userName}")
List<User> selectList(String userName);
/**
* 批量增加数据
*
* @param userList 用户列表信息
* @return 是否保存成功
*/
@InsertProvider(type = UserSqlProvider.class, method = "batchInsert")
int batchInsert(@Param(value = "userList") List<User> userList);
/**
* 批量删除用户信息
*
* @param userList 用户列表信息
* @return 是否删除成功
*/
@DeleteProvider(type = UserSqlProvider.class, method = "batchDelete")
boolean batchDelete(@Param(value = "userList") List<User> userList);
/**
* 批量更新
*
* @param userList 用户列表信息
* @return 是否更新成功
*/
@UpdateProvider(type = UserSqlProvider.class, method = "batchUpdate")
boolean batchUpdate(@Param(value = "userList") List<User> userList);
/**
* 批量查询
*
* @param userList 用户列表
* @return 用户列表信息
*/
@Results(value = {
@Result(property = "id", column = "id"),
@Result(property = "userName", column = "username"),
@Result(property = "passWord", column = "password"),
@Result(property = "age", column = "age")
})
@SelectProvider(type = UserSqlProvider.class, method = "batchList")
List<User> batchList(@Param(value="userList") List<User> userList);
class UserSqlProvider {
/**
* 批量增加
*
* @param userList 用户列表
* @return str字符串
*/
public String batchInsert(@Param(value = "userList") List<User> userList) {
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append("insert into user(username,password,age)values");
String message = "(''{0}'',''{1}'',{2})";
int i = 1;
for (User u : userList) {
String format = MessageFormat.format(message, u.getUserName(), u.getPassWord(), u.getAge());
stringBuilder.append(format);
if (i == userList.size()) {
break;
}
stringBuilder.append(",");
i++;
}
return stringBuilder.toString();
}
/**
* 批量删除
*
* @param userList 用户列表信息
* @return str字符串
*/
public String batchDelete(@Param(value = "userList") List<User> userList) {
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append("delete from user where id in(");
int i = 1;
for (User u : userList
) {
stringBuilder.append(u.getId());
if (i == userList.size()) {
break;
}
stringBuilder.append(",");
i++;
}
stringBuilder.append(")");
return stringBuilder.toString();
}
/**
* 批量更新
*
* @param userList 用户列表
* @return str字符串
*/
public String batchUpdate(@Param(value = "userList") List<User> userList) {
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append("insert into user(id,name,password,age) values");
String message = "{0},''{1}'',''{2}'',{3}";
int i = 1;
for (User u : userList
) {
String format = MessageFormat.format(message, u.getId(), u.getUserName(), u.getPassWord(), u.getAge());
stringBuilder.append(format);
if (i == userList.size()) {
break;
}
stringBuilder.append(",");
i++;
stringBuilder.append("on duplicate key update id=values(id),username=values(username),password=values(password),age=values(age)");
}
return stringBuilder.toString();
}
/**
* 批量查询
*
* @param userList 用户列表信息
* @return str字符串
*/
public String batchList(@Param(value = "userList") List<User> userList) {
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append("select * from user where id in(");
int i = 1;
for (User u : userList
) {
stringBuilder.append(u.getId());
if (i == userList.size()) {
break;
}
stringBuilder.append(",");
i++;
}
stringBuilder.append(")");
return stringBuilder.toString();
}
}
}
九,涉及的用户操作类User.class
package com.wpw.mybatisbatchoperation.entity;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
import java.io.Serializable;
/**
* @author wpw
*/
@AllArgsConstructor
@NoArgsConstructor
@Data
@Builder
@Accessors(chain = true)
public class User implements Serializable {
private Integer id;
private String userName;
private String passWord;
private Integer age;
}
十,代码的地址放入到github了,地址如下
github:https://github.com/myownmyway/mybatis-batch-operation.git
十一:项目的结构图