1 环境
- pom.xml
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.10</version>
</dependency>
<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>
<scope>runtime</scope>
</dependency>
- application.yaml
spring:
datasource:
username: root
password: 123456
url: jdbc:mysql://192.168.137.140:3306/test
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
# filters: stat,wall,log4j
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
# 如果你配置文件没有指定执行文件的名称而是使用默认的schema.sql或者schema-all.sql的话就在配置文件中加上spring.datasource.initialization-mode=always
initialization-mode: always
schema:
- classpath:sql/*
mybatis:
# 指定全局配置文件位置
config-location: classpath:mybatis/mybatis-config.xml
# 指定sql映射文件位置
mapper-locations: classpath:mybatis/mapper/*.xml
2 编写bean
- Department
package cn.zhangyu.bean;
import java.io.Serializable;
public class Department implements Serializable{
private Integer id;
private String departmentName;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getDepartmentName() {
return departmentName;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
}
- Employee
package cn.zhangyu.bean;
import java.io.Serializable;
public class Employee implements Serializable {
private Integer id;
private String lastName;
private Integer gender;
private String email;
private Integer dId;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public Integer getGender() {
return gender;
}
public void setGender(Integer gender) {
this.gender = gender;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Integer getdId() {
return dId;
}
public void setdId(Integer dId) {
this.dId = dId;
}
}
3 注解版
- DepartmentMapper
package cn.zhangyu.mapper;
import cn.zhangyu.bean.Department;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;
/**
* 指定操作数据库表department
*/
//@Mapper
@Repository
public interface DepartmentMapper {
@Select("select * from department where id=#{id}")
public Department getDepartmentById(Integer id);
@Select("select * from department where department_name=#{departmentName}")
public Department getDepartmentByName(String name);
@Delete("delete from department where id=#{id}")
public int deleteDepartment(Integer id);
//@Options 确认使用自增主键 、返回主键id 不使用@Options返回的id=null
@Options(useGeneratedKeys = true,keyProperty = "id")
@Insert("insert into department(departmentName) values(#{departmentName})")
public int insertDept(String departmentName);
@Update("update department set department_name=#{departmentName} where id=#{id}")
public int updateDept(Department department);
}
- DepartmentController
package cn.zhangyu.controller;
import cn.zhangyu.bean.Department;
import cn.zhangyu.mapper.DepartmentMapper;
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.RequestParam;
import org.springframework.web.bind.annotation.RestController;
@RestController
public class DepartmentController {
@Autowired
DepartmentMapper departmentMapper;
//查询department
@GetMapping("/dept/{id}")
public Department getDepartment(@PathVariable("id") Integer id){
return departmentMapper.getDepartmentById(id);
}
//插入数据
@GetMapping("/dept")
public Department insertDepartment(@RequestParam("departmentName") String departmentName){
departmentMapper.insertDept(departmentName);
Department department = departmentMapper.getDepartmentByName(departmentName);
return department;
}
}
我们可以发送请求http://localhost:8080/dept/?departmentName=aaa
测试结果:{"id":1,"departmentName":"aaa"}
注意: 这里我们可能存在多个mapper 那么我们就要为每个类加上@mapper
注解,就比较麻烦,我们可以在application中加入@MapperScan(value = "cn.zhangyu.mapper")
注解。
4 配置版
- EmployeeMapper
package cn.zhangyu.mapper;
import cn.zhangyu.bean.Employee;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
//@Mapper或者@MapperScan将接口扫描装配到容器中
@Mapper
@Repository
public interface EmployeeMapper {
//@Select("select * from employee where id=#{id}")
public Employee getEmpById(Integer id);
// @Select("insert into employee(lastName) values(#{lastName})")
public void insertEmp(Employee employee);
}
- resources目录下加入如下配置
- mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<!--适应驼峰-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
</configuration>
- EmployeeMapper.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="cn.zhangyu.mapper.EmployeeMapper">
<!-- public Employee getEmpById(Integer id);
public void insertEmp(Employee employee);-->
<select id="getEmpById" resultType="cn.zhangyu.bean.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>
加上如上配置我们启动项目就可以发送请求了,我们事先可以先插入一条数据:insert into employee(lastName,email,gender,d_id) values('aa','123@qq.com',1,1);
我们来查询看看:http://localhost:8080/emp/1
结果:{"id":1,"lastName":"aa","gender":1,"email":"123@qq.com","dId":1}