【Spring Cloud 基础设施搭建系列】Spring Cloud Demo项目整合Spring Data JPA

本是古典何须时尚 2023-05-29 03:07 12阅读 0赞

### 文章目录 ###

*   *  Docker 安装Mysql
     *  整合Spring Data JPA
     *   *  引入依赖
         *  添加配置
         *  添加实体类
         *   *  JPA @MappedSuperclass 注解
             *  JPA @Column 注解
             *  JPA @Version 注解
             *  JPA @CreatedDate @CreatedBy @LastModifiedDate @LastModifiedBy 注解
             *  JPA @Id和@GeneratedValue 注解
             *  @JsonFormat注解
         *  启动服务
         *  测试
         *  加入事务
     *  源代码

## Docker 安装Mysql ##

首先我们需要安装mysql，这里介绍一下使用docker去安装mysql

1.  使用`docker search` 命令搜索mysql

$ docker search mysql
    NAME                              DESCRIPTION                                     STARS               OFFICIAL            AUTOMATED
    mysql                             MySQL is a widely used, open-source relation…   8719                [OK]
    mariadb                           MariaDB is a community-developed fork of MyS…   3049                [OK]
    mysql/mysql-server                Optimized MySQL Server Docker images. Create…   645                                     [OK]
    centos/mysql-57-centos7           MySQL 5.7 SQL database server                   63
    centurylink/mysql                 Image containing mysql. Optimized to be link…   61                                      [OK]
    mysql/mysql-cluster               Experimental MySQL Cluster Docker images. Cr…   54
    deitch/mysql-backup               REPLACED! Please use http://hub.docker.com/r…   41                                      [OK]
    bitnami/mysql                     Bitnami MySQL Docker Image                      36                                      [OK]
    tutum/mysql                       Base docker image to run a MySQL database se…   34
    schickling/mysql-backup-s3        Backup MySQL to S3 (supports periodic backup…   28                                      [OK]
    prom/mysqld-exporter                                                              23                                      [OK]
    linuxserver/mysql                 A Mysql container, brought to you by LinuxSe…   22
    centos/mysql-56-centos7           MySQL 5.6 SQL database server                   16
    circleci/mysql                    MySQL is a widely used, open-source relation…   15
    mysql/mysql-router                MySQL Router provides transparent routing be…   13
    arey/mysql-client                 Run a MySQL client from a docker container      11                                      [OK]
    imega/mysql-client                Size: 36 MB, alpine:3.5, Mysql client: 10.1.…   8                                       [OK]
    openshift/mysql-55-centos7        DEPRECATED: A Centos7 based MySQL v5.5 image…   6
    yloeffler/mysql-backup            This image runs mysqldump to backup data usi…   6                                       [OK]
    fradelg/mysql-cron-backup         MySQL/MariaDB database backup using cron tas…   4                                       [OK]
    ansibleplaybookbundle/mysql-apb   An APB which deploys RHSCL MySQL                2                                       [OK]
    genschsa/mysql-employees          MySQL Employee Sample Database                  2                                       [OK]
    jelastic/mysql                    An image of the MySQL database server mainta…   1
    monasca/mysql-init                A minimal decoupled init container for mysql    0
    widdpim/mysql-client              Dockerized MySQL Client (5.7) including Curl…   0                                       [OK]

1.  使用`docker pull`命令拉取mysql镜像

$ docker pull mysql
    Using default tag: latest
    latest: Pulling from library/mysql
    80369df48736: Pull complete
    e8f52315cb10: Pull complete
    cf2189b391fc: Pull complete
    cc98f645c682: Pull complete
    27a27ac83f74: Pull complete
    fa1f04453414: Pull complete
    d45bf7d22d33: Pull complete
    3dbac26e409c: Pull complete
    9017140fb8c1: Pull complete
    b76dda2673ae: Pull complete
    bea9eb46d12a: Pull complete
    e1f050a38d0f: Pull complete
    Digest: sha256:7345ce4ce6f0c1771d01fa333b8edb2c606ca59d385f69575f8e3e2ec6695eee
    Status: Downloaded newer image for mysql:latest
    docker.io/library/mysql:latest

1.  启动mysql

docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql

-p 3306:3306：将容器的3306端口映射到主机的3306端口
    -v /opt/docker_v/mysql/conf:/etc/mysql/conf.d：将主机/opt/docker_v/mysql/conf目录挂载到容器的/etc/mysql/conf.d
    -e MYSQL_ROOT_PASSWORD=123456：初始化root用户的密码
    -d: 后台运行容器，并返回容器ID

1.  进入容器

docker exec -it mysql bash

1.  连接mysql

#登录mysql
    mysql -u root -p

参考：[Docker 安装 MySQL][Docker _ MySQL]

## 整合Spring Data JPA ##

在开始之前，我先说明一下，这个项目只是拿来练手和学习的，并不是实际的项目，真实的项目结构和模块可能是很不一样的。

### 引入依赖 ###

首先我这里抽取了一个common的module，用来存放跟jpa有关的common类和方法。命名为cloud-jpa-common。

首先我们需要在cloud-jpa-common中引入Spring Data JPA的依赖还有mysql连接的依赖。

<dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>

然后我这里只用一个微服务cloud-service-member来做简单的改造。

我们需要在cloud-service-member中引入cloud-jpa-common

<dependency>
        <groupId>com.cc.cloud</groupId>
        <artifactId>cloud-jpa-common</artifactId>
    </dependency>

### 添加配置 ###

下面我们需要添加连接mysql的配置还有jpa的配置。

spring:
      application:
        #对应config server所获取的配置文件的{application}
        name: cloud-service-member
      datasource:
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.99.100:3306/spring_cloud_demo?useUnicode=true&character_set_server=utf8mb4&useSSL=false&allowPublicKeyRetrieval=true
        username: root
        password: root
      jpa:
        #Hibernate相关配置
        hibernate:
          # create每次运行都删除原有表创建新表,update不用每次创建新表
          ddl-auto: create
        database: mysql
        # 打印SQL语句
        show-sql: true
        properties:
          hibernate:
            format_sql: true
            jdbc:
              time_zone: UTC

关于sring.datasource.url的配置可以参考如下：

[JDBC 连接 MySQL 时碰到的小坑][JDBC _ MySQL]

[MySQL8版本数据库连接URL注意事项][MySQL8_URL]

[关于JDBC连接数据库时出现的Public Key Retrieval is not allowed错误][JDBC_Public Key Retrieval is not allowed]

[永远不要在 MySQL 中使用「utf8」][MySQL _utf8]

[mysql使用utf8mb4经验吐血总结][mysql_utf8mb4]

[全面了解mysql中utf8和utf8mb4的区别][mysql_utf8_utf8mb4]

[springboot连接JDBC及application.yml的注意事项][springboot_JDBC_application.yml]

还有关于时区的设置，Hibernate支持设置时区，在Springboot中增加配置如下`spring.jpa.properties.hibernate.jdbc.time_zone`设置为UTC，当然还可以把`spring.jpa.properties.hibernate.jdbc.time_zone`设置为`GMT+8`。  
如果是MySQL数据库，也可以在连接池链接后面增加配置如下：`?serverTimezone=TimeZone&useLegacyDatetimeCode=false`

参考：

[UTC时间、GMT时间、本地时间、Unix时间戳][UTC_GMT_Unix]

[UTC和GMT时间][UTC_GMT]

[Java中的Date和时区转换][Java_Date]

[SpringBoot 统一时区的方案][SpringBoot]

[spring boot 设置时区][spring boot]

### 添加实体类 ###

package com.cc.cloud.member.domain;
    
    import com.cc.cloud.domain.common.VersionedEntityWithID;
    import lombok.Data;
    import lombok.EqualsAndHashCode;
    import lombok.NoArgsConstructor;
    import lombok.ToString;
    
    import javax.persistence.Column;
    import javax.persistence.Entity;
    import javax.persistence.Table;
    import java.io.Serializable;
    
    @Data
    @EqualsAndHashCode(callSuper = true)
    @ToString(callSuper = true)
    @NoArgsConstructor
    @Table(name = "MEMBER")
    @Entity
    public class Member extends VersionedEntityWithID implements Serializable { 
        private static final long serialVersionUID = 1L;
    
        @Column(name = "MEMBER_NAME",length = 20,nullable = false)
        private String memberName;
    }

参考：

[Java 之 Serializable 序列化和反序列化的概念,作用的通俗易懂的解释][Java _ Serializable]

[Entity实体类为什么要实现Serializable接口才能被序列化？][Entity_Serializable]

[实体类为什么要实现Serializable序列化的作用][Serializable]

这里我使用了lombok，所以还需要引入依赖。

<dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <scope>provided</scope>
    </dependency>

当然lombok的使用也是存在一些坑的，可以参考如下：

[lombok @EqualsAndHashCode 注解的影响][lombok _EqualsAndHashCode]

[Lombok介绍和使用][Lombok]

[Lombok 看这篇就够了][Lombok 1]

[180. Spring Boot lombok：@EqualsAndHashCode][180. Spring Boot lombok_EqualsAndHashCode]

[使用Hibernate、JPA、Lombok遇到的有趣问题][Hibernate_JPA_Lombok]

然后在我们的Entity中还继承了VersionedEntityWithID，VersionedEntityWithID是一个Entity中common的属性，我把他抽取到了cloud-jpa-common模块中去了。

package com.cc.cloud.domain.common;
    
    import lombok.Data;
    import lombok.EqualsAndHashCode;
    import lombok.ToString;
    
    import javax.persistence.GeneratedValue;
    import javax.persistence.GenerationType;
    import javax.persistence.Id;
    import javax.persistence.MappedSuperclass;
    
    @Data
    @EqualsAndHashCode(callSuper = true)
    @ToString(callSuper = true)
    @MappedSuperclass
    public class VersionedEntityWithID extends VersionedEntity { 
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Integer id;
    }

VersionedEntity也是common的属性。

package com.cc.cloud.domain.common;
    
    import com.cc.cloud.constant.EntityConstant;
    import com.fasterxml.jackson.annotation.JsonFormat;
    import lombok.Data;
    import org.springframework.data.annotation.CreatedBy;
    import org.springframework.data.annotation.CreatedDate;
    import org.springframework.data.annotation.LastModifiedBy;
    import org.springframework.data.annotation.LastModifiedDate;
    import org.springframework.data.jpa.domain.support.AuditingEntityListener;
    
    import javax.persistence.Column;
    import javax.persistence.EntityListeners;
    import javax.persistence.MappedSuperclass;
    import javax.persistence.Version;
    import java.util.Date;
    
    @Data
    @MappedSuperclass
    @EntityListeners(AuditingEntityListener.class)
    class VersionedEntity { 
    
        @Column(name = "JPA_VERSION")
        @Version
        private Long version;
    
        @Column(name = "CREATE_DATE")
        @JsonFormat(
                shape = JsonFormat.Shape.STRING,
                pattern = EntityConstant.JSON_DATE_FORMAT,
                timezone = EntityConstant.JSON_DATE_TIME_ZONE)
        @CreatedDate
        private Date createDate;
    
        @Column(name = "CREATE_BY")
        @CreatedBy
        private String createBy;
    
        @Column(name = "UPDATE_DATE")
        @JsonFormat(
                shape = JsonFormat.Shape.STRING,
                pattern = EntityConstant.JSON_DATE_FORMAT,
                timezone = EntityConstant.JSON_DATE_TIME_ZONE)
        @LastModifiedDate
        private Date updateDate;
    
        @Column(name = "UPDATE_BY")
        @LastModifiedBy
        private String updateBy;
    }

#### JPA @MappedSuperclass 注解 ####

我们可以注意到上面的 `@MappedSuperclass` 注解

在Jpa里, 当我们在定义多个实体类时, 可能会遇到这几个实体类都有几个共同的属性, 这时就会出现很多重复代码.  
这时我们可以选择编写一个父类,将这些共同属性放到这个父类中, 并且在父类上加上`@MappedSuperclass`注解.注意:标注为`@MappedSuperclass`的类将不是一个完整的实体类，他将不会映射到数据库表，但是他的属性都将映射到其子类的数据库字段中。

标注为`@MappedSuperclass`的类不能再标注@Entity或@Table注解，也无需实现序列化接口.

参考：

[springboot之jpa开发@MappedSuperclass 注解说明][springboot_jpa_MappedSuperclass]

[@MappedSuperclass的作用][MappedSuperclass]

[@MappedSuperclass的用法][MappedSuperclass 1]

[SpringData JPA 中 @MappedSuperclass 注解的使用][SpringData JPA _ _MappedSuperclass]

#### JPA @Column 注解 ####

@Column注解是用来标识实体类中属性与数据表中字段的对应关系。

参考：

[JPA @Column 注解][JPA _Column]

[Spring Date JPA的@Column注解详解][Spring Date JPA_Column]

#### JPA @Version 注解 ####

JPA默认提供了一个注解@Version来实现乐观锁。简单来说就是用一个version字段来充当乐观锁的作用。

参考：

[【Spring】27、JPA 实现乐观锁@Version注解的使用][Spring_27_JPA _Version]

[JPA之@Version进行乐观锁并发更新][JPA_Version]

#### JPA @CreatedDate @CreatedBy @LastModifiedDate @LastModifiedBy 注解 ####

*  @CreatedDate  
    表示该字段为创建时间时间字段，在这个实体被insert的时候，会设置值
 *  @CreatedBy  
    表示该字段为创建人，在这个实体被insert的时候，会设置值
 *  @LastModifiedDate、@LastModifiedBy同理。

当然想要@CreatedDate @CreatedBy @LastModifiedDate @LastModifiedBy注解起作用，还需要做多几个步骤。

1.  实体类上添加 `@EntityListeners(AuditingEntityListener.class)`，我们在VersionedEntity中已经添加了`@EntityListeners(AuditingEntityListener.class)`
2.  在启动类上添加 @EnableJpaAuditing

package com.cc.cloud.member;
    
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    import org.springframework.cloud.client.circuitbreaker.EnableCircuitBreaker;
    import org.springframework.cloud.netflix.eureka.EnableEurekaClient;
    import org.springframework.cloud.openfeign.EnableFeignClients;
    import org.springframework.data.jpa.repository.config.EnableJpaAuditing;
    import org.springframework.transaction.annotation.EnableTransactionManagement;
    
    @EnableEurekaClient
    @EnableFeignClients
    @EnableCircuitBreaker
    @EnableJpaAuditing
    public class MemberApp { 
        public static void main(String[] args) { 
            SpringApplication.run(MemberApp.class, args);
        }
    }

这个时候@CreatedDate和@LastModifiedDate都已经起作用了。但是@CreatedBy，@LastModifiedBy还需要做额外的配置。

下面这两个配置类，我都是抽取到了cloud-jpa-common模块中了。

package com.cc.cloud.configuration;
    
    import org.springframework.data.domain.AuditorAware;
    
    import java.util.Optional;
    
    public class AuditorProvider implements AuditorAware<String> { 
        @Override
        public Optional<String> getCurrentAuditor() { 
            return Optional.of("system");
        }
    }

package com.cc.cloud.configuration;
    
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.data.domain.AuditorAware;
    
    @Configuration
    public class AuditorConfig { 
        @Bean
        public AuditorAware<String> auditorProvider() { 
            return new AuditorProvider();
        }
    }

参考：

[Spring JPA 使用@CreatedDate、@CreatedBy、@LastModifiedDate、@LastModifiedBy 自动生成时间和修改者][Spring JPA _CreatedDate_CreatedBy_LastModifiedDate_LastModifiedBy]

[Spring Data JPA 的时间注解：@CreatedDate 和 @LastModifiedDate][Spring Data JPA _CreatedDate _ _LastModifiedDate]

[JPA EnableJpaAuditing 审计功能][JPA EnableJpaAuditing]

[JPA @CreatedBy@CreatedDate@LastModifiedBy@LastModifiedDate][JPA _CreatedBy_CreatedDate_LastModifiedBy_LastModifiedDate]

[Spring Data审计功能@CreatedDate、@CreatedBy、@LastModifiedDate、@LastModifiedBy的使用][Spring Data_CreatedDate_CreatedBy_LastModifiedDate_LastModifiedBy]

#### JPA @Id和@GeneratedValue 注解 ####

@Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Integer id;

参考：

[理解JPA注解@GeneratedValue][JPA_GeneratedValue]

[ID生成策略一 generator @GeneratedValue][ID_ generator _GeneratedValue]

#### @JsonFormat注解 ####

@Column(name = "CREATE_DATE")
        @JsonFormat(
                shape = JsonFormat.Shape.STRING,
                pattern = EntityConstant.JSON_DATE_FORMAT,
                timezone = EntityConstant.JSON_DATE_TIME_ZONE)
        @CreatedDate
        private Date createDate;

package com.cc.cloud.constant;
    
    public class EntityConstant { 
    
        public static final String JSON_DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
    
        public static final String JSON_DATE_TIME_ZONE = "GMT+8";
    
    }

参考：

[yyyy-MM-dd’T’HH:mm:ss.SSS’Z’即UTC时间，与String日期转换][yyyy-MM-dd_T_HH_mm_ss.SSS_Z_UTC_String]

[@JsonFormat与@DateTimeFormat注解的使用][JsonFormat_DateTimeFormat]

[使用@JsonFormat引起的时间比正常时间慢8小时解决方法][yyyy-MM-dd_T_HH_mm_ss.SSS_Z_UTC_String]

### 启动服务 ###

现在我们可以启动我们的服务了，启动cloud-eureka，cloud-config-server,还有cloud-service-member。

启动cloud-service-member时候，发现在控制台打印如下：

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

所以我们需要使用`driver-class-name: com.mysql.cj.jdbc.Driver`

我们更改一下我们的配置：

spring:
      application:
        #对应config server所获取的配置文件的{application}
        name: cloud-service-member
      datasource:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.99.100:3306/spring_cloud_demo?useUnicode=true&character_set_server=utf8mb4&useSSL=false&allowPublicKeyRetrieval=true
        username: root
        password: root
      jpa:
        #Hibernate相关配置
        hibernate:
          # create每次运行都删除原有表创建新表,update不用每次创建新表
          ddl-auto: create
        database: mysql
        # 打印SQL语句
        show-sql: true
        properties:
          hibernate:
            format_sql: true
            jdbc:
              time_zone: UTC

然后我们重新启动服务，结果在控制台中又报了如下的错误。

Hibernate: 
        
        drop table if exists member
    2019-10-29 23:49:55.725  WARN [cloud-service-member,,,] 13364 --- [           main] o.h.t.s.i.ExceptionHandlerLoggedImpl     : GenerationTarget encountered exception accepting command : Error executing DDL " drop table if exists member" via JDBC Statement
    
    org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL " drop table if exists member" via JDBC Statement
    	at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    	at org.hibernate.tool.schema.internal.SchemaDropperImpl.applySqlString(SchemaDropperImpl.java:375) [hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    	at org.hibernate.tool.schema.internal.SchemaDropperImpl.applySqlStrings(SchemaDropperImpl.java:359) [hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    	at org.hibernate.tool.schema.internal.SchemaDropperImpl.dropFromMetadata(SchemaDropperImpl.java:241) [hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    	at org.hibernate.tool.schema.internal.SchemaDropperImpl.performDrop(SchemaDropperImpl.java:154) [hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    	at org.hibernate.tool.schema.internal.SchemaDropperImpl.doDrop(SchemaDropperImpl.java:126) [hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    	at org.hibernate.tool.schema.internal.SchemaDropperImpl.doDrop(SchemaDropperImpl.java:112) [hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    	at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:144) [hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    	at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72) [hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    	at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:310) [hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    	at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:467) [hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    	at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:939) [hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    	at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:57) [spring-orm-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    	at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) [spring-orm-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:390) [spring-orm-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:377) [spring-orm-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    	at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) [spring-orm-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1804) [spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1741) [spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:576) [spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:498) [spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    	at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:320) [spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) ~[spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:318) [spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199) [spring-beans-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    	at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1083) ~[spring-context-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:853) ~[spring-context-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:546) ~[spring-context-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    	at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:142) ~[spring-boot-2.1.1.RELEASE.jar:2.1.1.RELEASE]
    	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:775) ~[spring-boot-2.1.1.RELEASE.jar:2.1.1.RELEASE]
    	at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397) ~[spring-boot-2.1.1.RELEASE.jar:2.1.1.RELEASE]
    	at org.springframework.boot.SpringApplication.run(SpringApplication.java:316) ~[spring-boot-2.1.1.RELEASE.jar:2.1.1.RELEASE]
    	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1260) ~[spring-boot-2.1.1.RELEASE.jar:2.1.1.RELEASE]
    	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1248) ~[spring-boot-2.1.1.RELEASE.jar:2.1.1.RELEASE]
    	at com.cc.cloud.member.MemberApp.main(MemberApp.java:17) ~[classes/:na]
    Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'member' at line 1
    	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:782) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    	at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:666) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    	at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95) ~[HikariCP-3.2.0.jar:na]
    	at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-3.2.0.jar:na]
    	at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    	... 34 common frames omitted
    
    Hibernate: 
        
        create table member (
           id integer not null,
            create_by varchar(255),
            create_date_gmt datetime,
            update_by varchar(255),
            update_date_gmt datetime,
            jpa_version bigint,
            member_name varchar(20) not null,
            primary key (id)
        ) engine=MyISAM
    2019-10-29 23:49:55.732  WARN [cloud-service-member,,,] 13364 --- [           main] o.h.t.s.i.ExceptionHandlerLoggedImpl     : GenerationTarget encountered exception accepting command : Error executing DDL " create table member ( id integer not null, create_by varchar(255), create_date_gmt datetime, update_by varchar(255), update_date_gmt datetime, jpa_version bigint, member_name varchar(20) not null, primary key (id) ) engine=MyISAM" via JDBC Statement
    
    org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL " create table member ( id integer not null, create_by varchar(255), create_date_gmt datetime, update_by varchar(255), update_date_gmt datetime, jpa_version bigint, member_name varchar(20) not null, primary key (id) ) engine=MyISAM" via JDBC Statement

结果排查了很久，发现可能是table的名字member有什么冲突吧。解决方式就是把table名字改为members

package com.cc.cloud.member.domain;
    
    import com.cc.cloud.domain.common.VersionedEntityWithID;
    import lombok.Data;
    import lombok.EqualsAndHashCode;
    import lombok.NoArgsConstructor;
    import lombok.ToString;
    
    import javax.persistence.Column;
    import javax.persistence.Entity;
    import javax.persistence.Table;
    import java.io.Serializable;
    
    @Data
    @EqualsAndHashCode(callSuper = true)
    @ToString(callSuper = true)
    @NoArgsConstructor
    @Table(name = "MEMBERS")
    @Entity
    public class Member extends VersionedEntityWithID implements Serializable { 
        private static final long serialVersionUID = 1L;
    
        @Column(name = "MEMBER_NAME",length = 20,nullable = false)
        private String memberName;
    }

参考：

[springboot Error executing DDL via JDBC Statement][]

[一个hql 关键字member（非mysql）引起的 vo 数据 保存数据库错误][hql _member_mysql_ vo _]

[Springboot+jpa自动生成表,以及可能会遇到的问题][Springboot_jpa]

### 测试 ###

接下来我们在代码中写个测试的方法。

package com.cc.cloud.member.controller;
    
    import com.cc.cloud.member.domain.Member;
    import com.cc.cloud.member.feign.OrderFeign;
    import com.cc.cloud.member.service.MemberService;
    import com.google.common.collect.Lists;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.cloud.context.config.annotation.RefreshScope;
    import org.springframework.http.HttpStatus;
    import org.springframework.web.bind.annotation.*;
    
    import java.util.List;
    
    @RefreshScope
    @RestController
    @RequestMapping("/member")
    public class MemberController { 
    
        private OrderFeign orderFeign;
    
        @Value("${cloud.service.member}")
        private String memberConfig;
    
        @Autowired
        private MemberService memberService;
    
        @Autowired
        public void setOrderFeign(OrderFeign orderFeign) { 
            this.orderFeign = orderFeign;
        }
    
        @RequestMapping("/orders")
        @ResponseStatus(HttpStatus.OK)
        public List<String> getOrderList() { 
            return orderFeign.getAllOrderList();
        }
    
        @RequestMapping("/members")
        @ResponseStatus(HttpStatus.OK)
        public List<String> getMemberList() { 
            List<String> memberList = Lists.newArrayList();
            memberList.add("member 1");
            memberList.add("member 2");
            memberList.add("member 3");
            return memberList;
        }
    
    
        @GetMapping("/config")
        @ResponseStatus(HttpStatus.OK)
        public String getMemberConfig(){ 
            return memberConfig;
        }
    
    
        //加入测试的代码
        @PostMapping
        @ResponseStatus(HttpStatus.OK)
        public String addMember(@RequestBody Member member){ 
            return memberService.addMember(member).toString();
        }
    }

接下来的是service层的代码。

package com.cc.cloud.member.service.impl;
    
    import com.cc.cloud.member.domain.Member;
    import com.cc.cloud.member.repository.MemberRepository;
    import com.cc.cloud.member.service.MemberService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    
    @Service
    public class MemberServiceImpl implements MemberService { 
    
        @Autowired
        private MemberRepository memberRepository;
    
        @Override
        public Member addMember(Member member) { 
            return memberRepository.save(member);
        }
    }

repository层的代码。

package com.cc.cloud.member.repository;
    
    import com.cc.cloud.member.domain.Member;
    import org.springframework.data.jpa.repository.JpaRepository;
    
    public interface MemberRepository extends JpaRepository<Member, Integer> { 
    
    }

启动完毕之后，我们测试我们的API接口。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Nja2V2aW5jeWg_size_16_color_FFFFFF_t_70]

结果发现@CreateBy是null，这是为什么。

经过排查了很久，原因是因为启动类中@SpringBootApplication注解是扫描当前包和子包，而当前包是`com.cc.cloud.member`，而AuditorConfig类，所在的包在`com.cc.cloud.configuration`,所以根本扫不到配置。解决方法也很简单，把包扫描设置为`com.cc.cloud`

package com.cc.cloud.member;
    
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    import org.springframework.cloud.client.circuitbreaker.EnableCircuitBreaker;
    import org.springframework.cloud.netflix.eureka.EnableEurekaClient;
    import org.springframework.cloud.openfeign.EnableFeignClients;
    import org.springframework.data.jpa.repository.config.EnableJpaAuditing;
    import org.springframework.transaction.annotation.EnableTransactionManagement;
    
    @SpringBootApplication(scanBasePackages = "com.cc.cloud")
    @EnableEurekaClient
    @EnableFeignClients
    @EnableCircuitBreaker
    @EnableJpaAuditing
    public class MemberApp { 
        public static void main(String[] args) { 
            SpringApplication.run(MemberApp.class, args);
        }
    }

然后重新启动，重新测试我们的API接口。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Nja2V2aW5jeWg_size_16_color_FFFFFF_t_70 1]

现在发现已经能看到createBy的值。

### 加入事务 ###

我们只需要在我们的service层中加入注解@Transactional

@Transactional
        @Override
        public Member addMember(Member member) { 
            return memberRepository.save(member);
        }

然后在启动类中开启注解事务管理。

package com.cc.cloud.member;
    
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    import org.springframework.cloud.client.circuitbreaker.EnableCircuitBreaker;
    import org.springframework.cloud.netflix.eureka.EnableEurekaClient;
    import org.springframework.cloud.openfeign.EnableFeignClients;
    import org.springframework.data.jpa.repository.config.EnableJpaAuditing;
    import org.springframework.transaction.annotation.EnableTransactionManagement;
    
    @SpringBootApplication(scanBasePackages = "com.cc.cloud")
    @EnableEurekaClient
    @EnableFeignClients
    @EnableCircuitBreaker
    @EnableJpaAuditing
    @EnableTransactionManagement // 开启注解事务管理
    public class MemberApp { 
        public static void main(String[] args) { 
            SpringApplication.run(MemberApp.class, args);
        }
    }

但是经过测试，当我在service中加入错误的代码，结果发现最终并没有回滚，依然插入了数据。

@Transactional
        @Override
        public Member addMember(Member member) { 
            Member m = memberRepository.save(member);
            int i = 1 / 0;
            return m;
        }

这是为什么，原因是因为mysql中engine默认是myisam，它是事务不安全的。解决方法就是修改我们的配置。

加入配置`database-platform: org.hibernate.dialect.MySQL5InnoDBDialect`,设置为engine=innodb

配置如下：

spring:
      application:
        #对应config server所获取的配置文件的{application}
        name: cloud-service-member
      datasource:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.99.100:3306/spring_cloud_demo?useUnicode=true&character_set_server=utf8mb4&useSSL=false&allowPublicKeyRetrieval=true
        username: root
        password: root
      jpa:
        #Hibernate相关配置
        hibernate:
          # create每次运行都删除原有表创建新表,update不用每次创建新表
          ddl-auto: create
        database: mysql
        # 打印SQL语句
        show-sql: true
        database-platform: org.hibernate.dialect.MySQL5InnoDBDialect #设置使用的engine=InnoDB，默认MyISAM 不是事务安全的
        properties:
          hibernate:
            format_sql: true
            jdbc:
              time_zone: UTC

参考：

[Spring Boot 事务的使用][Spring Boot]

[SpringBoot 实战 (十) | 声明式事务][SpringBoot _ _ _]

[Spring Boot中的事务管理][Spring Boot 1]

[mysql中engine=innodb和engine=myisam的区别][mysql_engine_innodb_engine_myisam]

[MySql – 创建表时 engine=innodb和engine=myisam的区别][MySql _ _ engine_innodb_engine_myisam]

## 源代码 ##

[https://gitee.com/cckevincyh/spring-cloud-demo/tree/spring-data-jpa/][https_gitee.com_cckevincyh_spring-cloud-demo_tree_spring-data-jpa]

[Docker _ MySQL]: https://www.runoob.com/docker/docker-install-mysql.html
[JDBC _ MySQL]: https://www.cnblogs.com/Leroscox/p/9011003.html
[MySQL8_URL]: https://gitee.com/MrXionGe/codes/rsaqvtj7ekhy2bi8xc6o468
[JDBC_Public Key Retrieval is not allowed]: https://blog.csdn.net/Yuriey/article/details/80423504
[MySQL _utf8]: https://mp.weixin.qq.com/s/Z3uG3r2Yb-Wq_KL3VQ90rQ
[mysql_utf8mb4]: http://seanlook.com/2016/10/23/mysql-utf8mb4/
[mysql_utf8_utf8mb4]: https://my.oschina.net/xsh1208/blog/1052781
[springboot_JDBC_application.yml]: https://blog.csdn.net/Emperor_Hd/article/details/86626298
[UTC_GMT_Unix]: https://www.cnblogs.com/xwdreamer/p/8761825.html
[UTC_GMT]: https://www.cnblogs.com/qiuyi21/archive/2008/03/04/1089456.html
[Java_Date]: https://blog.csdn.net/halfclear/article/details/77573956
[SpringBoot]: https://www.jianshu.com/p/504c17b35e17
[spring boot]: https://blog.csdn.net/CookingChickenBoy/article/details/84939127
[Java _ Serializable]: https://blog.csdn.net/u013870094/article/details/82765907
[Entity_Serializable]: https://blog.csdn.net/weixin_39973810/article/details/92839741
[Serializable]: https://blog.csdn.net/qq_18298439/article/details/80607057
[lombok _EqualsAndHashCode]: https://blog.csdn.net/zhanlanmg/article/details/50392266
[Lombok]: https://www.jianshu.com/p/a1c95b35dc88
[Lombok 1]: https://zhuanlan.zhihu.com/p/32779910
[180. Spring Boot lombok_EqualsAndHashCode]: https://zhuanlan.zhihu.com/p/33157558
[Hibernate_JPA_Lombok]: https://www.jianshu.com/p/61d4e28ee254
[springboot_jpa_MappedSuperclass]: https://www.cnblogs.com/soul-wonder/p/9103444.html
[MappedSuperclass]: https://www.cnblogs.com/shenhanboBlog/p/9922244.html
[MappedSuperclass 1]: https://www.cnblogs.com/zqyanywn/p/7753596.html
[SpringData JPA _ _MappedSuperclass]: https://blog.csdn.net/weixin_41404773/article/details/80661365
[JPA _Column]: https://fanlychie.github.io/post/jpa-column-annotation.html
[Spring Date JPA_Column]: https://www.jianshu.com/p/37a8573dc586
[Spring_27_JPA _Version]: https://www.cnblogs.com/wangzhongqiu/p/7550985.html
[JPA_Version]: https://www.jianshu.com/p/58f477f6423b
[Spring JPA _CreatedDate_CreatedBy_LastModifiedDate_LastModifiedBy]: https://www.jianshu.com/p/14cb69646195
[Spring Data JPA _CreatedDate _ _LastModifiedDate]: https://cloud.tencent.com/developer/article/1376563
[JPA EnableJpaAuditing]: https://www.cnblogs.com/niceyoo/p/10908647.html
[JPA _CreatedBy_CreatedDate_LastModifiedBy_LastModifiedDate]: https://blog.csdn.net/onepiecekapu/article/details/77840942
[Spring Data_CreatedDate_CreatedBy_LastModifiedDate_LastModifiedBy]: https://blog.csdn.net/Tracycater/article/details/74060721
[JPA_GeneratedValue]: https://blog.csdn.net/canot/article/details/51455967
[ID_ generator _GeneratedValue]: https://blog.csdn.net/weixin_40512519/article/details/80876286
[yyyy-MM-dd_T_HH_mm_ss.SSS_Z_UTC_String]: https://blog.csdn.net/swjtu_yhz/article/details/53300757
[JsonFormat_DateTimeFormat]: https://www.cnblogs.com/mracale/p/9828346.html
[springboot Error executing DDL via JDBC Statement]: https://blog.csdn.net/weixin_37683300/article/details/80025922
[hql _member_mysql_ vo _]: https://www.cnblogs.com/wzhanke/p/4350323.html
[Springboot_jpa]: https://blog.csdn.net/weixin_42379291/article/details/86535309
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Nja2V2aW5jeWg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191102105435217.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Nja2V2aW5jeWg=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Nja2V2aW5jeWg_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191102105445954.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Nja2V2aW5jeWg=,size_16,color_FFFFFF,t_70
[Spring Boot]: https://blog.csdn.net/catoop/article/details/50595702
[SpringBoot _ _ _]: https://juejin.im/post/5c6ea806e51d45012d06932b
[Spring Boot 1]: http://blog.didispace.com/springboottransactional/
[mysql_engine_innodb_engine_myisam]: http://www.360doc.com/content/14/0702/11/13883922_391427906.shtml
[MySql _ _ engine_innodb_engine_myisam]: https://blog.csdn.net/u010286334/article/details/46500225
[https_gitee.com_cckevincyh_spring-cloud-demo_tree_spring-data-jpa]: https://gitee.com/cckevincyh/spring-cloud-demo/tree/spring-data-jpa/