前言
使用h2和flyway快速搭建开发测试环境，自动建表。
1、环境搭建
1、新建springboot项目
2、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.leahoop.h2-flyway-demo</groupId>
<artifactId>h2-flyway-demo</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
<druid.version>1.1.10</druid.version>
<flyway.version>5.2.4</flyway.version>
</properties>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.3.RELEASE</version>
<relativePath/>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--lombok插件-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!--<!–Mysql依赖包–>-->
<!--<dependency>-->
<!--<groupId>mysql</groupId>-->
<!--<artifactId>mysql-connector-java</artifactId>-->
<!--<scope>runtime</scope>-->
<!--</dependency>-->
<!--H2依赖包(开发用)-->
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
</dependency>
<!-- druid数据源驱动 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>${druid.version}</version>
</dependency>
<!-- flywaydb -->
<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-core</artifactId>
<version>${flyway.version}</version>
</dependency>
</dependencies>
</project>
3、applicaiton.yml 配置文件如下：
server:
port: 8080
spring:
jpa:
generate-ddl: false
# 输出sql语句
show-sql: true
hibernate:
ddl-auto: none
datasource:
url: jdbc:h2:mem:test
username: sa
password:
type: com.alibaba.druid.pool.DruidDataSource # 使用阿里druid连接池
druid:
min-idle: 2
initial-size: 5
max-active: 10
max-wait: 5000
validation-query: select '1' from dual
h2:
console:
enabled: true
# 映射h2
path: /h2
flyway:
clean-on-validation-error: true # 仅限开发环境使用
locations: classpath:/db/migration
4、
- 在resources下新建db.migration目录，新建文件V1.0.1__first.sql;
- migration目录下存放sql文件，命名格式为：V大写+序号，中间是两个下划线（__）+命名
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(255) NOT NULL COMMENT '名称',
`pid` bigint(20) NOT NULL COMMENT '上级部门',
`create_time` datetime DEFAULT NULL,
`enabled` bit(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
INSERT INTO `dept` VALUES ('1', 'ali', '0', '2019-03-25 09:14:05', '1');
2、代码
项目结构
- 新建Dept实体
package com.leahoop.domain;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.CreationTimestamp;
import javax.persistence.*;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
import java.io.Serializable;
import java.sql.Timestamp;
@Entity
@Table(name = "dept")
@Data
@NoArgsConstructor
public class Dept implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private Long id;
@Column(name = "name", nullable = false)
@NotBlank
private String name;
@NotNull
private Boolean enabled;
@Column(name = "pid", nullable = false)
@NotNull
private Long pid;
@Column(name = "create_time")
@CreationTimestamp
private Timestamp createTime;
}
- 新建repository
package com.leahoop.repository;
import com.leahoop.domain.Dept;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import java.util.List;
public interface DeptRepository extends JpaRepository<Dept, Long>, JpaSpecificationExecutor {
List<Dept> findByPid(Long id);
}
- 新建Service层
// 接口
package com.leahoop.service;
import com.leahoop.domain.Dept;
import java.util.Optional;
public interface DeptService {
Optional<Dept> findById(Long id);
void save(Dept dept);
}
// 实现
package com.leahoop.service.impl;
import com.leahoop.domain.Dept;
import com.leahoop.repository.DeptRepository;
import com.leahoop.service.DeptService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.Optional;
@Service
public class DeptServiceImpl implements DeptService {
@Autowired
private DeptRepository deptRepository;
@Override
public Optional<Dept> findById(Long id) {
return deptRepository.findById(id);
}
@Override
public void save(Dept dept) {
deptRepository.save(dept);
}
}
- 新建controller层
package com.leahoop.controller;
import com.leahoop.domain.Dept;
import com.leahoop.service.DeptService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
@RestController
@RequestMapping({"api"})
public class Api {
@Autowired
private DeptService deptService;
@GetMapping({"/dept/find"})
public ResponseEntity getDept(@RequestParam Long id) {
return new ResponseEntity(deptService.findById(id), HttpStatus.OK);
}
@PostMapping({"/dept/save"})
public ResponseEntity saveDept(@RequestBody Dept dept) {
deptService.save(dept);
return new ResponseEntity(HttpStatus.OK);
}
}
3、测试
- 浏览器输入 http://localhost:8080/h2 可以看到h2登录页，/h2是我们在配置文件配置的地址
-
接着用postman测试数据
- 查询数据
- 新增数据
