引入依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</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>2.1.1</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>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.20</version>
</dependency>
</dependencies>
项目构建
1,在resources下创建department.sql和employee.sql,项目启动时创建表
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`departmentName` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
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;
2,创建两个实体类
- Department
- Employee
3,配置文件 application.yml
spring:
datasource:
username: root
password: 123456
url: jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
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
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
Mybatis增删改查
1,创建mapper接口
@Mapper
public interface DepartmentMapper {
@Select("select * from department")
public List<Department> selectAll();
@Select("select * from department where id=#{id}")
public Department selectById(Integer id);
@Options(useGeneratedKeys = true, keyProperty = "id")
@Insert("insert into department(departmentName) values(#{departmentName})")
public int save(Department department);
@Update("update department set departmentName=#{departmentName}")
public int update(Department department);
@Delete("delete from department where id =#{id}")
public int delete(Integer id);
}
2,创建Controller接口
@RestController
public class DepartmentController {
@Autowired
private DepartmentMapper departmentMapper;
@RequestMapping("/dep/{id}")
public List<Department> getDepById(@PathVariable Integer id) {
return departmentMapper.selectAll();
}
@RequestMapping("/dep")
public Department getDepById(Department department) {
departmentMapper.save(department);
return department;
}
}
3,访问页面
- http://localhost:8080/dep?departmentName=PeppaPig添加一条数据,department名字为PeppaPig
- department名字为PeppaPig,无需设置id,因为id设置为自增长; @Options(useGeneratedKeys = true, keyProperty = "id")
- 访问 http://localhost:8080/dep/1查询数据
Mybatis配置
开启驼峰命名法则
- 最好将实例类和表中的列名一致,省事省心
- 如果列表和属性名字不一致,数据封装会出错,我们可以开启驼峰命名配置
mybatis: configuration: map-underscore-to-camel-case: true
- 配置完项目需要重启
- 也可以通过向spring容器中注入
org.mybatis.spring.boot.autoconfigure.ConfigurationCustomizer
的方法设置mybatis参数@Configuration public class MybatisConfig { @Bean public ConfigurationCustomizer mybatisConfigurationCustomizer() { return new ConfigurationCustomizer() { @Override public void customize(org.apache.ibatis.session.Configuration configuration) { configuration.setMapUnderscoreToCamelCase(true); } }; } }
Mapper扫描
- 使用@mapper的类可以被扫描到容器中,但是需要每一个Mapper类都要添加这个注解,比较繁琐;
- 我们可以在启动类上加上@MapperScan
- @MapperScan("cn.xxxxxx.springbootmybatis.mapper"),将我们的mapper包引入
@MapperScan("cn.xxxxxx.springbootmybatis.mapper")
@SpringBootApplication
public class SpringbootMybatisApplication {
public static void main(String[] args) {
SpringApplication.run(SpringbootMybatisApplication.class, args);
}
}
使用xml配置文件
1,创建mybatis全局配置文件
<?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>
<typeAliases>
<package name="cn.xxxxx.springbootmybatis.model"/>
</typeAliases>
</configuration>
2,创建EmployeeMapper接口
public interface EmployeeMapper {
List<Employee> selectAll();
int save(Employee employee);
}
3,创建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.xxxxxx.springbootmybatis.mapper.EmployeeMapper">
<select id="selectAll" resultType="employee">
SELECT * FROM employee
</select>
<insert id="save" parameterType="employee" useGeneratedKeys="true" keyProperty="id">
INSERT INTO employee(lastName,email,gender,d_id) VALUES (#{lastName},#{email},#{gender},#{d_id})
</insert>
</mapper>
4,配置文件(application.yaml)中,指定配置文件和映射文件的位置
mybatis:
config-location: classpath:mybatis/mybatis-config.xml
mapper-locations: classpath:mybatis/mapper/*.xml
5,在表中插入数据,用于测试
INSERT INTO employee(lastName,email,gender,d_id) VALUES ('张三','123456@qq.com',1,1);
INSERT INTO employee(lastName,email,gender,d_id) VALUES ('lisi','245612@qq.com',1,1);
6,创建EmployeeController
@RestController
public class EmployeeController {
@Autowired
private EmployeeMapper employeeMapper;
@RequestMapping("/emp/list")
public List<Employee> getALl() {
return employeeMapper.selectAll();
}
@RequestMapping("/emp/{id}")
public Employee save(Employee employee) {
employeeMapper.save(employee);
return employee;
}
}
7,查看数据
访问http://localhost:8080/emp/list查看list列表