springboot整合mybatis注解版和xml版
1、新建springboot项目
- 注:mybatis中引入了spring_data_jdbc
sql
/*
Navicat MySQL Data Transfer
Source Server : 本地
Source Server Version : 50528
Source Host : 127.0.0.1:3306
Source Database : restful_crud
Target Server Type : MYSQL
Target Server Version : 50528
File Encoding : 65001
Date: 2020-12-22 20:41:40
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`department_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*
Navicat MySQL Data Transfer
Source Server : 本地
Source Server Version : 50528
Source Host : 127.0.0.1:3306
Source Database : restful_crud
Target Server Type : MYSQL
Target Server Version : 50528
File Encoding : 65001
Date: 2020-12-22 20:41:40
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`lastName` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`gender` int(2) DEFAULT NULL,
`d_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
- 完整
pom
文件
<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.4.1</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.cls</groupId>
<artifactId>springboot_mybatis_demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>springboot_mybatis_demo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<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>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
yml
配置文件
# 数据源
spring:
datasource:
username: root
password: root
url: jdbc:mysql://127.0.0.1:3306/mybatis?serverTimezone=Asia/Shanghai
driver-class-name: com.mysql.cj.jdbc.Driver
# mybatis相关配置
mybatis:
#指定sql映射文件的位置
mapper-locations: classpath:mapper/*.xml
# 实体类别名
type-aliases-package: com.cls.springboot_mybatis_demo.entity
configuration:
#开启驼峰命名映射
map-underscore-to-camel-case: true
# 日志记录级别
logging:
level:
com.cls.springboot_mybatis_demo.controller: debug
org.springframeworker: info
- 实体类(实体类中是
departmentName
和dId
,而数据库中是department_name
和d_id
)
package com.cls.springboot_mybatis_demo.entity;
public class Department {
private Integer id;
private String departmentName;
public void setId(Integer id) {
this.id = id;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
public Integer getId() {
return id;
}
public String getDepartmentName() {
return departmentName;
}
}
package com.cls.springboot_mybatis_demo.entity;
public class Employee {
private Integer id;
private String lastName;
private Integer gender;
private String email;
private Integer dId;
public void setId(Integer id) {
this.id = id;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public void setGender(Integer gender) {
this.gender = gender;
}
public void setEmail(String email) {
this.email = email;
}
public void setdId(Integer dId) {
this.dId = dId;
}
public Integer getId() {
return id;
}
public String getLastName() {
return lastName;
}
public Integer getGender() {
return gender;
}
public String getEmail() {
return email;
}
public Integer getdId() {
return dId;
}
}
- 启动器
package com.cls.springboot_mybatis_demo;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.cls.springboot_mybatis_demo.mapper")
public class SpringbootMybatisDemoApplication {
public static void main(String[] args) {
SpringApplication.run(SpringbootMybatisDemoApplication.class, args);
}
}
- controller
package com.cls.springboot_mybatis_demo.controller;
import com.cls.springboot_mybatis_demo.entity.Department;
import com.cls.springboot_mybatis_demo.entity.Employee;
import com.cls.springboot_mybatis_demo.mapper.DepartmentMapper;
import com.cls.springboot_mybatis_demo.mapper.EmployeeMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;
/**
* @author cls
* @version 1.0
* @date 2020/12/22/0022
*/
@RestController
public class ControllerDemo {
@Autowired
public DepartmentMapper departmentMapper;
@Autowired
public EmployeeMapper employeeMapper;
@GetMapping("/getDept/{id}")
public Department getDeptById(@PathVariable("id") Integer id){
return departmentMapper.getDeptById(id);
}
@GetMapping("/dept")
public Department insertDept(Department department){
departmentMapper.insertDept(department);
return department;
}
@GetMapping("/getEmp/{id}")
public Employee getEmp(@PathVariable("id") Integer id) {
return employeeMapper.getEmpById(id);
}
@GetMapping("/emp")
public void insertEmp(Employee employee) {
employeeMapper.insertEmp(employee);
}
}
2、注解版
package com.cls.springboot_mybatis_demo.mapper;
import com.cls.springboot_mybatis_demo.entity.Department;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
/**
* @author cls
* @version 1.0
* @date 2020/12/21/0021
*/
//@Mapper注解,指定这是一个操作数据库的mapper
//@Mapper
@Repository
public interface DepartmentMapper {
@Select("select * from department where id = #{id}")
public Department getDeptById(@Param("id") Integer id);
@Delete("delete from department where id = #{id}")
public int delDeptById(Integer id);
//useGeneratedKeys = true:使用自动生成的主键;keyProperty = "id":Department对象中的id属性是用来封装主键的,现在就是插入对象,主键会自动封装进来
@Options(useGeneratedKeys = true, keyProperty = "id")
@Insert("insert into department(department_name) values (#{departmentName})")
public int insertDept(Department department);
@Update("update department set department_name = #{departmentName} where id = #{id}")
public int updateDept(Department department);
}
3、xml版
package com.cls.springboot_mybatis_demo.mapper;
import com.cls.springboot_mybatis_demo.entity.Employee;
import org.springframework.stereotype.Repository;
/**
* @author cls
* @version 1.0
* @date 2020/12/21/0021
*/
//@Mapper或者启动类写了@MapperScan 将接口扫描到容器中
@Repository
public interface EmployeeMapper {
public Employee getEmpById(Integer id);
public void insertEmp(Employee employee);
}
<?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.cls.springboot_mybatis_demo.mapper.EmployeeMapper">
<!-- <select id="getEmpById" resultType="com.cls.springboot_mybatis_demo.entity.Employee">-->
<select id="getEmpById" resultType="Employee">
select * from employee where id = #{id}
</select>
<insert id="insertEmp">
insert into employee(lastName, email, gender, d_id) VALUES (#{lastName},#{email},#{gender},#{dId})
</insert>
</mapper>
-
测试链接
http://localhost:8080/dept?departmentName=ee
http://localhost:8080/getDept/1
http://localhost:8080/emp?lastName=ggg&email=111@qq.com&gender=1&dId=3
http://localhost:8080/getEmp/1 -
整体项目的目录结构
-
小技巧:可以在idea中配置数据库连接,这样在
xml
中写sql
的时候就会提示表名,列名了