springboot整合mybatis增删查改操作

springboot整合mybatis增删查改操作

1.创建项目

开发工具： eclipse(STS)

1.File—>New—>Project—>Spring Boot—>Spring Stater Project

2.选择mysql，web， mybatis

3.查看pom.xml文件依赖

<dependencies>
    <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.4</version>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

2.修改配置文件

修改application.properties文件。本文采用application.yml文件

将resource文件夹下的application.properties文件修改为application.yml文件，并新建application-dev.yml文件。

application.yml

spring:
  profiles:
    active: dev

application-dev.yml

server:
  port: 8080
  
spring:
  datasource:
    username: root
    password: root123456
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
    driver-class-name: com.mysql.cj.jdbc.Driver
    
mybatis:
  mapper-locations: classpath:mapping/*.xml
  type-aliases-package: com.example.demo.vo

两个文件的作用：

可满足多环境的参数配置：

application-dev.yml：开发环境
application-test.yml：测试环境
application-prod.yml：生产环境
至于哪个具体的配置文件会被加载，需要在application.yml文件中通过spring.profiles.active属性来设置，其值对应{profile}值。

注意：spring.datasource.url 后面为什么要添加&serverTimezone=UTC ?

可能会报如下错误，原因是新版本的mysql时区与本地不一致。

3.CRUD逻辑实现

1.实体类UserMailVo.java

package com.example.demo.vo;

public class UserMailVo {

	private Integer id;
	private String userid;
	private String username;
	private String mail;
	private String sign_flag;
	private String plant;
	private String role;

	public Integer getId() {
		return id;
	}

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

	public String getUserid() {
		return userid;
	}

	public void setUserid(String userid) {
		this.userid = userid;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getMail() {
		return mail;
	}

	public void setMail(String mail) {
		this.mail = mail;
	}

	public String getSign_flag() {
		return sign_flag;
	}

	public void setSign_flag(String sign_flag) {
		this.sign_flag = sign_flag;
	}

	public String getPlant() {
		return plant;
	}

	public void setPlant(String plant) {
		this.plant = plant;
	}

	public String getRole() {
		return role;
	}

	public void setRole(String role) {
		this.role = role;
	}

	@Override
	public String toString() {
		return "UserMailVo [id=" + id + ", userid=" + userid + ", username=" + username + ", mail=" + mail
				+ ", sign_flag=" + sign_flag + ", plant=" + plant + ", role=" + role + "]";
	}

}

2.Dao层UserMailDao.java

package com.example.demo.dao;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;

import com.example.demo.vo.UserMailVo;

/**
 * @Mapper 和 @Repository 注解的对比
 * 相同点：都使用在dao层
 * 不同点：
 * 	使用@mapper后，通过mapper.xml里面的namespace属性对应相关的mapper类，spring将动态的生成Bean后注入到Service中
 * 	使用@repository则需要在Spring中配置扫描包地址，然后生成dao层的bean，之后被注入到Service中
 *
 */
@Mapper
public interface UserMailDao {

	/**
	 * insert, update, delete 操作的返回值是影响的记录数，可以用int类型接
	 */

	public UserMailVo getUserById(int id);

	public List<UserMailVo> findAll();

	public int insert(UserMailVo vo);

	public int update(UserMailVo vo);

	public int deleteById(int id);
}

3.mapping xml文件UserMail.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">
<!-- namespace 对应dao层@Mapper注解的接口 -->
<mapper namespace = "com.example.demo.dao.UserMailDao">

	<!-- 使用resultMap可以处理属性名称和数据库列名不一致的问题 -->
	<!--  
	<resultMap type="UserMailVo" id="result">
		<result property = "userId" column = "userid" />
		<result property = "userName" column = "username" />
		<result property = "signFlag" column = "sign_flag" />
	</resultMap>
	-->

	<select id = "getUserById" resultType = "UserMailVo">
		select * from user_mail where id = #{id}
	</select>
	
	<select id = "findAll" resultType = "UserMailVo">
		select * from user_mail
	</select>
	
	<insert id = "insert" parameterType = "UserMailVo" keyProperty="id" useGeneratedKeys="true">
		insert into user_mail(userid, username, mail, sign_flag, plant, role) 
			values(#{userid}, #{username}, #{mail}, #{sign_flag}, #{plant}, #{role})
	</insert>
	
	<update id = "update" parameterType = "UserMailVo">
		update user_mail set username = #{username}, mail = #{mail}, sign_flag = #{sign_flag} 
			where userid = #{userid}
	</update>
	
	<delete id = "deleteById" parameterType = "int">
		delete from user_mail where id = #{id}
	</delete>
</mapper>

4.Service层interface UserMailService.java

package com.example.demo.service;

import java.util.List;

import com.example.demo.vo.UserMailVo;

public interface UserMailService {

	public UserMailVo getUserById(int id);

	public List<UserMailVo> findAll();

	public int insert(UserMailVo vo);

	public int update(UserMailVo vo);

	public int deleteById(int id);

}

5.Service层实现类

package com.example.demo.service.impl;

import java.util.List;

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

import com.example.demo.dao.UserMailDao;
import com.example.demo.service.UserMailService;
import com.example.demo.vo.UserMailVo;

@Service
public class UserMailServiceImpl implements UserMailService {

	@Autowired
	private UserMailDao dao;

	@Override
	public UserMailVo getUserById(int id) {
		return dao.getUserById(id);
	}

	@Override
	public List<UserMailVo> findAll() {
		return dao.findAll();
	}

	@Override
	public int insert(UserMailVo vo) {
		return dao.insert(vo);
	}

	@Override
	public int update(UserMailVo vo) {
		return dao.update(vo);
	}

	@Override
	public int deleteById(int id) {
		return dao.deleteById(id);
	}

}

6.Controller层

package com.example.demo.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import com.example.demo.service.UserMailService;
import com.example.demo.vo.UserMailVo;
import com.fasterxml.jackson.databind.ObjectMapper;

@RestController
@RequestMapping("/user")
public class UserMailController {

	@Autowired
	private UserMailService service;

	@RequestMapping(path = "/{id}", method = RequestMethod.GET)
	public UserMailVo getUserById(@PathVariable int id) {
		return service.getUserById(id);
	}

	@RequestMapping(path = "/findAll", method = RequestMethod.GET)
	public List<UserMailVo> findAll() {
		return service.findAll();
	}

	@RequestMapping(path = "/insert", method = RequestMethod.POST)
	public String insert(@RequestBody Object input) {

		try {
			// 将input参数转换成VO
			ObjectMapper objMapper = new ObjectMapper();
			UserMailVo vo = objMapper.convertValue(input, UserMailVo.class);
			int count = service.insert(vo);

			if (count == 1) {
				return "Successed!";
			} else {
				return "Failed!";
			}
		} catch (Exception e) {
			return "Failed!";
		}

	}

	@RequestMapping(path = "/update", method = RequestMethod.POST)
	public String update(@RequestBody Object input) {

		try {
			ObjectMapper objMapper = new ObjectMapper();
			UserMailVo vo = objMapper.convertValue(input, UserMailVo.class);
			int count = service.update(vo);

			if (count == 1) {
				return "Successed!";
			} else {
				return "Failed!";
			}
		} catch (Exception e) {
			return "Failed!";
		}
	}

	@RequestMapping(path = "delete/{id}", method = RequestMethod.GET)
	public String deleteById(@PathVariable int id) {
		int count = service.deleteById(id);

		if (count == 1) {
			return "Successed!";
		} else {
			return "Failed!";
		}
	}
}

7.在主启动类增加@MapperScan注解，用于扫描dao层接口，使之在编译后生成相应的实现类

package com.example.demo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.example.demo.dao")
public class MybatisApplication {

	public static void main(String[] args) {
		SpringApplication.run(MybatisApplication.class, args);
	}

}

4.最终代码结构