目录

springboot与数据访问

简介

对于数据访问层,无论是SQL还是NOSQL,Spring Boot默认采用整合Spring Data的方式进行统一处理,添加大量自动配置,屏蔽了很多设置。引入 各种xxxTemplate,xxxRepository来简化我们对数据访问层的操作。对我们来 说只需要进行简单的设置即可。我们将在数据访问章节测试使用SQL相关、 NOSQL在缓存、消息、检索等章节测试。

  • JDBC

  • MyBatis

  • JPA

http://img.cana.space/picStore/20201024225849.png

整合基本JDBC与数据源

配置JDBC数据访问环境

引入jdbc starter和数据库驱动

1
2
3
4
5
6
7
8
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

配置application.properties

1
2
3
4
spring.datasource.username=root
spring.datasource.password=333
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/db01?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true&allowMultiQueries=true

测试:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
package com.eh.springbootexternal.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;

import javax.sql.DataSource;
import java.sql.SQLException;

@Controller
public class HelloController {

    @Autowired
    private DataSource dataSource;

    @GetMapping("/abc")
    public String hello(Model model) throws SQLException {
        System.out.println(dataSource);
        System.out.println(dataSource.getConnection());
        model.addAttribute("msg","你好");
        return "success";
    }
}

输出:

1
2
3
4
HikariDataSource (null)
2020-10-24 22:08:47.707  INFO 17022 --- [nio-8080-exec-8] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2020-10-24 22:08:48.658  INFO 17022 --- [nio-8080-exec-8] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
HikariProxyConnection@568055802 wrapping com.mysql.cj.jdbc.ConnectionImpl@5bedaa63

springboot2以上使用HikariDataSource数据源,2以前使用tomcat数据源:org.apache.tomcat.jdbc.pool.DataSource

数据源的相关配置都在DataSourceProperties里面

自动配置原理

参考DataSourceConfiguration,根据配置创建数据源,默认使用Hikari连接池;可以使用spring.datasource.type指定自定义的数据源类型;

1
2
3
4
5
6
@Configuration(proxyBeanMethods = false)
@ConditionalOnClass(HikariDataSource.class)
@ConditionalOnMissingBean(DataSource.class)
@ConditionalOnProperty(name = "spring.datasource.type", havingValue = "com.zaxxer.hikari.HikariDataSource",
      matchIfMissing = true)
static class Hikari {

SpringBoot默认可以支持:org.apache.tomcat.jdbc.pool.DataSource、HikariDataSource、BasicDataSource、 Dbcp2

也可以自定义数据源类型

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
@Configuration(proxyBeanMethods = false)
@ConditionalOnMissingBean(DataSource.class)
@ConditionalOnProperty(name = "spring.datasource.type")
static class Generic {

   @Bean
   DataSource dataSource(DataSourceProperties properties) {
            //使用DataSourceBuilder创建数据源,利用反射创建响应type的数据源,并且绑定相关属性
      return properties.initializeDataSourceBuilder().build();
   }

}

DataSourceInitializer初始化sql脚本

DataSourceAutoConfiguration

1
2
3
4
5
6
@Configuration(proxyBeanMethods = false)
@ConditionalOnClass({ DataSource.class, EmbeddedDatabaseType.class })
@ConditionalOnMissingBean(type = "io.r2dbc.spi.ConnectionFactory")
@EnableConfigurationProperties(DataSourceProperties.class)
@Import({ DataSourcePoolMetadataProvidersConfiguration.class, DataSourceInitializationConfiguration.class })
public class DataSourceAutoConfiguration {

DataSourceInitializationConfiguration

1
2
3
@Configuration(proxyBeanMethods = false)
@Import({ DataSourceInitializerInvoker.class, DataSourceInitializationConfiguration.Registrar.class })
class DataSourceInitializationConfiguration {

DataSourceInitializerInvoker

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
class DataSourceInitializerInvoker implements ApplicationListener<DataSourceSchemaCreatedEvent>, InitializingBean {
  
  @Override
	public void afterPropertiesSet() {
		DataSourceInitializer initializer = getDataSourceInitializer();
		if (initializer != null) {
			boolean schemaCreated = this.dataSourceInitializer.createSchema();
			if (schemaCreated) {
				initialize(initializer);
			}
		}
	}

DataSourceInitializer

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
class DataSourceInitializer {
  boolean createSchema() {
		List<Resource> scripts = getScripts("spring.datasource.schema", this.properties.getSchema(), "schema");
		if (!scripts.isEmpty()) {
			if (!isEnabled()) {
				logger.debug("Initialization disabled (not running DDL scripts)");
				return false;
			}
			String username = this.properties.getSchemaUsername();
			String password = this.properties.getSchemaPassword();
			runScripts(scripts, username, password);
		}
		return !scripts.isEmpty();
	}
  
  private List<Resource> getScripts(String propertyName, List<String> resources, String fallback) {
		if (resources != null) {
			return getResources(propertyName, resources, true);
		}
		String platform = this.properties.getPlatform();
		List<String> fallbackResources = new ArrayList<>();
		fallbackResources.add("classpath*:" + fallback + "-" + platform + ".sql");
		fallbackResources.add("classpath*:" + fallback + ".sql");
		return getResources(propertyName, fallbackResources, false);
	}

所以我们在类路径下建一个schema-all.sql,然后编写sql脚本即可在应用启动的时候自动执行

schema-all.sql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
-- 创建员工表
CREATE TABLE IF NOT EXISTS `tbl_employee` (
   id INT(11) PRIMARY KEY auto_increment,
   last_name VARCHAR(255),
   gender char(1),
   email VARCHAR(255)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 准备数据
insert into tbl_employee(last_name, gender, email) values('tom', 0, 'tom@gmail.com');

注意springboot2以上需要配置

1
2
# 是否执行初始化sql脚本
spring.datasource.initialization-mode=always

当然我们也可以自定义脚本路径

1
2
# 自定义sql脚本
spring.datasource.schema=classpath:employee.sql

jdbcTemplate

springboot自动配置了JdbcTemplate操作数据库

1
2
3
4
5
6
7
8
9
@Configuration(proxyBeanMethods = false)
@ConditionalOnClass({ DataSource.class, JdbcTemplate.class })
@ConditionalOnSingleCandidate(DataSource.class)
@AutoConfigureAfter(DataSourceAutoConfiguration.class)
@EnableConfigurationProperties(JdbcProperties.class)
@Import({ JdbcTemplateConfiguration.class, NamedParameterJdbcTemplateConfiguration.class })
public class JdbcTemplateAutoConfiguration {

}

演示

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
package com.eh.springbootexternal.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import java.util.List;
import java.util.Map;

@Controller
public class HelloController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @GetMapping("/")
    @ResponseBody
    public Map<String, Object> hello() {
        List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from tbl_employee");
        return list.get(0);
    }
}

演示结果

http://img.cana.space/picStore/20201024235336.png

使用druid数据源

导入druid依赖

1
2
3
4
5
6
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.0.18</version>
</dependency>

配置自定义数据源

1
2
# 使用自定义数据源
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

配置druid数据源参数

往配置文件中加入如下参数

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
spring.datasource.maxWait=60000
spring.datasource.timeBetweenEvictionRunsMillis=60000
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
spring.datasource.poolPreparedStatements=true

刚加进去显示warning,是因为没有数据源与之绑定,所以还需要绑定数据源

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
package com.eh.springbootexternal.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
public class DruidConfig {

    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druid() {
        return new DruidDataSource();
    }
}

配置druid数据源监控

配置文件:

1
2
3
4
5
6
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
#spring.datasource.filters=stat,wall,log4j
spring.datasource.filters=stat
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.useGlobalDataSourceStat=true
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

增加管理后台servlet映射以及监控sql的filter

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
package com.eh.springbootexternal.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class DruidConfig {

    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druid() {
        return new DruidDataSource();
    }

    //配置Druid的监控
    //1、配置一个管理后台的Servlet
    @Bean
    public ServletRegistrationBean statViewServlet() {
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        Map<String, String> initParams = new HashMap<>();

        initParams.put("loginUsername", "admin");
        initParams.put("loginPassword", "123");
        initParams.put("allow", "");//默认就是允许所有ip访问
        initParams.put("deny", "192.168.15.21");

        bean.setInitParameters(initParams);
        return bean;
    }


    //2、配置一个web监控的filter
    @Bean
    public FilterRegistrationBean webStatFilter() {
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());

        Map<String, String> initParams = new HashMap<>();
        // 过滤静态资源
        initParams.put("exclusions", "*.js,*.css,/druid/*");
        bean.setInitParameters(initParams);
        bean.setUrlPatterns(Arrays.asList("/*"));
        return bean;
    }
}

运行效果

数据源参数

http://img.cana.space/picStore/20201025003000.png

sql监控

http://img.cana.space/picStore/20201025002937.png

整合Mybatis

创建一个新工程,选择web、mysql、jdbc(mybatis和jdbc的数据源一样,选择jdbc可以帮我们自动配置数据源,当然也可以不勾选,因为mybatis框架自动引入了starter-jdbc)、mybatis

http://img.cana.space/picStore/20201025005728.png

如果在已创建的项目中集成mybatis,则引入mybatis创建的starter即可

1
2
3
4
5
<dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>

将前面章节的配置文件和java类导入到新的工程里, 这里我讲application.properties改成application.yml,使结构更清晰

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
spring:
  datasource:
    #   数据源基本配置
    username: root
    password: 333
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/db01?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true&allowMultiQueries=true
    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
    filters: stat
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
    # 是否执行初始化sql脚本
    initialization-mode: always
    schema:
      - classpath:sql/department.sql
      - classpath:sql/employee.sql
#mybatis:
#  # 指定全局配置文件位置
#  config-location: classpath:mybatis/mybatis-config.xml
#  # 指定sql映射文件位置
#  mapper-locations: classpath:mybatis/mapper/*.xml

创建库表语句:

department.sql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
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;

employee.sql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `last_name` 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;

创建完后将是否执行初始化sql脚本改成initialization-mode: never

注解版

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
package com.eh.springbootmybatis.orm.dao;

import com.eh.springbootmybatis.orm.bean.Department;
import org.apache.ibatis.annotations.*;
// @Mapper, 使用MapperScan批量扫描所有的Mapper接口就不需要加@Mapper注解了
public interface DepartmentMapper {
    @Select("select * from department where id=#{id}")
    public Department getDeptById(Integer id);

    @Delete("delete from department where id=#{id}")
    public int deleteDeptById(Integer 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);
}

注意事项

  • id自动生成回写

    在插入语句上增加注解 @Options(useGeneratedKeys = true,keyProperty = "id")

  • 驼峰命名法,可以使用springboot配置,也可以自己写ConfigurationCustomizer

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    
    package com.eh.springbootmybatis.config;
      
    import org.mybatis.spring.boot.autoconfigure.ConfigurationCustomizer;
    import org.springframework.context.annotation.Bean;
      
    @org.springframework.context.annotation.Configuration
    public class MyBatisConfig {
      
        @Bean
        public ConfigurationCustomizer configurationCustomizer() {
            return configuration -> configuration.setMapUnderscoreToCamelCase(true);
        }
    }
    
  • 使用MapperScan批量扫描所有的Mapper接口

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    
    package com.eh.springbootmybatis;
      
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
      
    @MapperScan(value = "com.eh.springbootmybatis.orm.dao")
    @SpringBootApplication
    public class SpringbootMybatisApplication {
      
        public static void main(String[] args) {
            SpringApplication.run(SpringbootMybatisApplication.class, args);
        }
    }
    

测试

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
package com.eh.springbootmybatis.controller;


import com.eh.springbootmybatis.orm.bean.Department;
import com.eh.springbootmybatis.orm.bean.Employee;
import com.eh.springbootmybatis.orm.dao.DepartmentMapper;
import com.eh.springbootmybatis.orm.dao.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;

@RestController
public class DeptController {

    @Autowired
    DepartmentMapper departmentMapper;

    @Autowired
    EmployeeMapper employeeMapper;


    @GetMapping("/dept/{id}")
    public Department getDepartment(@PathVariable("id") Integer id) {
        return departmentMapper.getDeptById(id);
    }

  // http://localhost:8080/dept?departmentName=BB 插入一条数据
    @GetMapping("/dept")
    public Department insertDept(Department department) {
        departmentMapper.insertDept(department);
        return department;
    }

    @GetMapping("/emp/{id}")
    public Employee getEmp(@PathVariable("id") Integer id){
        return employeeMapper.selectByPrimaryKey(id);
    }
}

配置版

使用mybatis逆向工程自动生成相应代码

这里参照:mybatis代码生成器

完整示例:github地址

mybatis全局配置文件

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
<?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>
  <!--使用ResultMap,不需要再使用全局设置-->
    <!--<settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>-->
</configuration>

springboot配置mybatis相关设置

1
2
3
4
5
mybatis:
  # 指定全局配置文件位置
  config-location: classpath:mybatis/mybatis-config.xml
  # 指定sql映射文件位置
  mapper-locations: classpath:mybatis/mapper/*.xml

更多使用参照:官网链接

Spring Data

简介

http://img.cana.space/picStore/20201025025805.png

Spring Data 项目的目的是为了简化构建基于 Spring 框架应用的数据访问技术,包括非关系数据库、 Map-Reduce 框架、云数据服务等等;另外也包含对关系数据库的访问支持。

Spring Data 包含多个子项目:

  • – Spring Data Commons

  • – Spring Data JPA

  • – Spring Data KeyValue

  • – Spring Data LDAP

  • – Spring Data MongoDB

  • – Spring Data Gemfire

  • – Spring Data REST

  • – Spring Data Redis

  • – Spring Data for Apache Cassandra

  • – Spring Data for Apache Solr

  • – Spring Data Couchbase (community module)

  • – Spring Data Elasticsearch (community module)

  • – Spring Data Neo4j (community module)

Spring Data特点

http://img.cana.space/picStore/20201025025436.png

SpringData为我们提供使用统一的API来对数据访问层进行操作;这主要是Spring Data Commons项目来实现的。Spring Data Commons让我们在使用关系型或者非关系型数据访问 技术时都基于Spring提供的统一标准,标准包含了CRUD(创建、获取、更新、删除)、查询、 排序和分页的相关操作。

统一的Repository接口

  • Repository<T, ID extends Serializable>:统一接口
  • RevisionRepository<T, ID extends Serializable, N extends Number & Comparable<N>>:基于乐观 锁机制
  • CrudRepository<T, ID extends Serializable>:基本CRUD操作
  • PagingAndSortingRepository<T, ID extends Serializable>:基本CRUD及分页

http://img.cana.space/picStore/20201025120742.png

提供数据访问模板类xxxTemplate

如:MongoTemplate、RedisTemplate等

JPA与Spring Data

JPA是Java Persistence API的简称,中文名Java持久层API,是JDK 5.0注解或XML描述对象-关系表的映射关系,并将运行期的实体对象持久化到数据库中。

Sun引入新的JPA ORM规范出于两个原因:其一,简化现有Java EE和Java SE应用开发工作;其二,Sun希望整合ORM技术,实现天下归一。

JpaRepository基本功能

编写接口继承JpaRepository既有crud及分页等基本功能

定义符合规范的方法命名

在接口中只需要声明符合规范的方法,即拥有对应的功能

http://img.cana.space/picStore/20201025025934.png

@Query自定义查询,定制查询SQL

Specifications查询(Spring Data JPA支持JPA2.0的Criteria查询)

整合JPA

完整示例参考:github地址

  1. 引入spring-boot-starter-data-jpa和数据库驱动

    1
    2
    3
    4
    5
    6
    7
    8
    9
    
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    
  2. 配置文件打印SQL语句和自动创建更新数据表结构

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    
    spring:
      datasource:
        #   数据源基本配置
        username: root
        password: 333
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/db01?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true&allowMultiQueries=true
      jpa:
        hibernate:
        # 更新或者创建数据表结构
          ddl‐auto: update # 控制台显示SQL
        show‐sql: true
    
  3. 创建Entity标注JPA注解

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    
    package com.eh.springbootjpa.entiry;
       
    import lombok.Data;
    import javax.persistence.*;
       
    //使用JPA注解配置映射关系
    @Entity //告诉JPA这是一个实体类(和数据表映射的类)
    @Table(name = "tbl_user") //@Table来指定和哪个数据表对应;如果省略默认表名就是user;
    @Data
    public class User {
       
        @Id //这是一个主键
        @GeneratedValue(strategy = GenerationType.IDENTITY)//自增主键
        private Integer id;
       
        @Column(name = "last_name", length = 50) //这是和数据表对应的一个列
        private String lastName;
        @Column //省略默认列名就是属性名
        private String email;
    }
    
  4. 创建Repository接口继承JpaRepository

    1
    2
    3
    4
    5
    6
    7
    8
    
    package com.eh.springbootjpa.dao;
       
    import com.eh.springbootjpa.entiry.User;
    import org.springframework.data.jpa.repository.JpaRepository;
       
    //继承JpaRepository来完成对数据库的操作
    public interface UserRepository extends JpaRepository<User, Integer> {
    }
    
  5. 测试方法

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    
    package com.eh.springbootjpa.controller;
       
    import com.eh.springbootjpa.dao.UserRepository;
    import com.eh.springbootjpa.entiry.User;
    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;
       
    @RestController
    public class UserController {
       
        @Autowired
        UserRepository userRepository;
       
        // 测试:http://localhost:8080/user/1
        @GetMapping("/user/{id}")
        public User getUser(@PathVariable("id") Integer id){
            User user = userRepository.findById(id).orElse(null);
            return user;
        }
       
        // 测试:http://localhost:8080/user?lastName=lisi&email=lisi@gmail.com
        @GetMapping("/user")
        public User insertUser(User user){
            User save = userRepository.save(user);
            return save;
        }
    }