SpringBoot(十一) ----SpringBoot结合mybatis实现增删改查
我们可以使用SpringBoot，将SpringBoot与mybatis结合起来，实现对数据库的增删改查的操作。
接下来举个例子：
第一步，首先创建一个数据库test，并创建表tb_user，向tb_user中插入几条数据。
create database test;
-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
use test;
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
`id` int(11) NOT NULL,
`username` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tb_user
-- ----------------------------
INSERT INTO `tb_user` VALUES ('1', 'laowang', '112233');
INSERT INTO `tb_user` VALUES ('2', 'laoli', '123456');
第二步，构建我们的springboot项目，项目结构如下，
从项目的结构中，我们可以看到我们需要构建的项目文件，springboot启动类DemoApplication.java、控制层类UserController.java、实体类User.java、DAO层UserMapper.java接口文件、Service层UserService.java、mapping文件夹下UserMapper.xml映射文件、静态资源文件success.html和我们SpringBoot的application.yml配置文件、Maven配置文件pom.xml。
第三步，构建pom.xml文件,文件内容如下：
pom.xml
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.example</groupId> <artifactId>demo</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>demo</name> <description>Demo project for Spring Boot</description> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.0.5.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <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>1.3.2</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.15</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>
这里核心引入与mybatis和mysql数据库相关的jar
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.15</version> </dependency>
第四步，配置application.yml数据库文件
server:
port: 8080
spring:
datasource:
username: root
password: 123456
url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
driver-class-name: com.mysql.jdbc.Driver
mybatis:
mapper-locations: classpath:mapping/*Mapper.xml
type-aliases-package: com.example.entity
#showSql
logging:
level:
com:
example:
mapper : debug
第五步，构建controller、service、Dao、Data层文件内容
UserController.java
package com.example.controller;
import com.example.entity.User;
import com.example.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
/**
* @Author:zk
* @Date: 2020/4/20 0026
* @Time: 14:42
*/
@RestController
@RequestMapping("/testBoot")
public class UserController {
@Autowired
private UserService userService;
@RequestMapping("getUser/{id}")
public String GetUser(@PathVariable int id){
return userService.Sel(id).toString();
}
@RequestMapping("findAll")
public String findAll(){
return userService.SelAll().toString();
}
@RequestMapping("insert/id={id}&username={username}&password={password}")
public String insert(User user){
userService.insert(user);
return "success";
}
@RequestMapping("delete/id={id}")
public String delete(User user){
userService.delete(user);
return "success";
}
@RequestMapping("update/id={id}&username={username}&password={password}")
public String update(User user){
userService.update(user);
return "success";
}
}
User.java
package com.example.entity;
/**
* @Author:zk
* @Date: 2020/4/20 0026
* @Time: 14:42
*/
public class User {
private Integer id;
private String username;
private String password;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", password=" + password + "]";
}
}
UserMapper.java
package com.example.mapper;
import com.example.entity.User;
import java.util.List;
import org.springframework.stereotype.Repository;
/**
* @Author:zk
* @Date: 2020/4/20 0026
* @Time: 14:42
*/
@Repository
public interface UserMapper {
User Sel(int id);
List<User> SelAll();
int insert(User user);
int deleteById(User user);
int updateById(User user);
}
UserMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mapper.UserMapper">
<resultMap id="BaseResultMap" type="com.example.entity.User">
<result column="id" jdbcType="INTEGER" property="id" />
<result column="userName" jdbcType="VARCHAR" property="userName" />
<result column="passWord" jdbcType="VARCHAR" property="passWord" />
</resultMap>
<select id="Sel" resultType="com.example.entity.User">
select * from tb_user where id = #{id}
</select>
<select id="SelAll" resultType="com.example.entity.User">
select * from tb_user
</select>
<insert id="insert" parameterType="com.example.entity.User">
INSERT INTO tb_user
(
id,username,password
)
VALUES (
#{id},
#{username, jdbcType=VARCHAR},
#{password, jdbcType=VARCHAR}
)
</insert>
<delete id="deleteById">
Delete from tb_user
where id=#{id}
</delete>
<update id="updateById" parameterType="com.example.entity.User">
Update tb_user
SET
id = #{id},
username = #{username},
password = #{password}
where id = #{id}
</update>
</mapper>
UserService.java
package com.example.service;
import com.example.entity.User;
import com.example.mapper.UserMapper;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
/**
* @Author:zk
* @Date: 2020/4/20 0026
* @Time: 14:42
*/
@Service
public class UserService {
@Autowired
UserMapper userMapper;
public User Sel(int id){
return userMapper.Sel(id);
}
public List<User> SelAll() {
// TODO Auto-generated method stub
return userMapper.SelAll();
}
public int insert(User user) {
// TODO Auto-generated method stub
return userMapper.insert(user);
}
public int delete(User user) {
// TODO Auto-generated method stub
return userMapper.deleteById(user);
}
public int update(User user) {
// TODO Auto-generated method stub
return userMapper.updateById(user);
}
}
构建success.html静态资源文件
success.html
<!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Insert title here</title> </head> <body> success </body> </html>
最后一步，设置启动类
DemoApplication.java
package com.example;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@MapperScan("com.example.mapper") //扫描的mapper
@SpringBootApplication
public class DemoApplication {
public static void main(String[] args) {
SpringApplication.run(DemoApplication.class, args);
}
}
然后我们开始启动程序：
测试增加操作：
测试删除操作：
刚才加的数据删掉了
测试修改操作：
可以看到id=2的数据已经被修改掉了
测试查询操作：
首先查全部数据
根据id查数据
至此我们的增删改查操作完成。
