Springboot整合持久层技术笔记

一、整合SpringData JPA

1.MySQL(8.0.13) serverTimezone，参考文章1，文章2

• 使用MySQL数据库报错

Caused by: java.sql.SQLException: The server time zone value '�й���׼ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

url=jdbc:mysql://localhost:3306/test
url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC

• 问题：serverTimezone=UTC导致存入数据库的时间比实际时间少8个小时（MySQL jdbc 6.0 版本以上必须配置此参数）
• 原因：UTC代表的是全球标准时间 ，北京时区也就是东八区，领先UTC八个小时
• 解决方法一：将serverTimezone的UTC改为Asia/Shanghai
• 解决方法二：url中不用指定serverTimezone，直接修改数据库的默认时区:

show variables like '%time_zone%';
set global time_zone='+8:00';

2.spring.jpa.hibernate.ddl-auto，参考文章

create 启动时删数据库中的表，然后创建，退出时不删除数据表
create-drop 启动时删数据库中的表，然后创建，退出时删除数据表 如果表不存在报错
update 如果启动时表格式不一致则更新表，原有数据保留
validate 项目启动表结构进行校验 如果不一致则报错

• 使用建议：开发调试用update，生产环境用validate

3.MySQL创建时间更新时间自动赋值，参考文章

• 方法一：MySQL语句中定义默认值，实体类上加注解@DynamicUpdate，参考文章

'create_time' timestamp not null default current_timestamp comment '创建时间',
'update_time' timestamp not null default current_timestamp on update_time current_timestamp comment '更新时间'

• 方法二：属性上加@CreationTimestamp @UpdateTimestamp，参考文章

@CreationTimestamp
@Column(nullable = false, updatable = false)
private Timestamp createTime;

@UpdateTimestamp
Column(nullable = false)
private Timestamp updateTime;

• 方法三：属性上加@CreatedDate、@LastModifiedDate，实体类上加
  @EntityListeners(AuditingEntityListener.class)，启动类加上@EnableJpaAuditing，参考文章
• 支持的Java日期字段类型如下

java.sql.Timestamp, org.joda.time.DateTime, org.joda.time.LocalDateTime, java.util.Date, java.lang.Long, long

• 日期类型如果是Long，则数据库表对应字段为Number类型。（不推荐用，因为在数据库中的时间表示用整数表示，可视化软件不会将其转换为正常的日期表达形式，而时间戳可以）
• java.util.Date（注意java.sql.Date不行），则数据库表对应字段为Date类型
• java.sql.Timestamp，则数据库表对应字段为Timestamp类型
• 下面用Timestamp做测试

@CreatedDate
private Timestamp createTime;
 
@LastModifiedDate
private Timestamp updateTime;

@Data
@Entity
@EntityListeners(AuditingEntityListener.class)
public class Person{
    @Id
    @GenericGenerator(name = "idGenerator1", strategy = "uuid")
    @GeneratedValue(generator = "idGenerator1")
    @Column(length = 32)
    private String id;
    private String name;
    private int age;
    @CreatedDate
    private Timestamp createTime;
}

@Data
public class PersonDTO {
    private String name;
    private int age;
}

public interface PersonRepository extends JpaRepository<Person, String> {
}

@Slf4j
@RestController
public class ControllerTest {
    private final PersonRepository personRepository;

    public ControllerTest(PersonRepository personRepository) {
        this.personRepository = personRepository;
    }

    @PostMapping("/person")
    public Person addPerson(@RequestBody PersonDTO personDTO) {
        log.info(personDTO.toString());
        Person person = new Person();
        person.setName(personDTO.getName());
        person.setAge(personDTO.getAge());
        log.info(person.toString());
        personRepository.save(person);
        // save 之后 person 增加了 id 和 createTime 字段
        log.info(person.toString());
        return person;
    }
}

@SpringBootApplication
@EnableJpaAuditing
public class DemoApplication {
    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }
}

 

4.Spring Data JPA 设置字段默认值

• 问题：MySQL语句中定义了默认值，但是由于save的对象中的player_count属性被初始化为0，保存到数据库中的记录的player_count字段值也被设为0，无法指定为默认值。

 `player_count` int not null default 1 comment '玩家的人数，默认1',

• 方法一：使用@Column注解，缺点：默认值无法更新，若想在代码里修改字段默认值，需要重新建表，参考文章

@Column(insertable = false, nullable = false, columnDefinition = "int default 1")
private int playCount;

• 方法二：直接在属性定义时给出默认值（推荐）

@NotNull
private int playerCount = 1;    // 玩家的人数，默认1

5.@Column注解及属性详解，参考文章

6.@Table(name="表名")

• 表名和实体类名不一致时才要用到（忽略大小写）

7.@Transient 

• @Transient表示在生成数据库中的表时，该属性被忽略，即不生成对应的字段

8.@Transactional

• 加在测试方法上：测试完成后回滚

@Test
@Transactional
public void deleteById(){
    String id = "123";
    playerRepository.deleteById(id);
}

• 加在业务方法上：xxxRepository中自定义了更新数据表记录的SQL或JPQL语句时，需要在该方法上加@Modifying注解，同时在调用该方法的业务方式上添加@Transactional注解，参考文章

@Modifying
@Query(value = "delete from book where id=?1",nativeQuery = true)
void deleteById(String id);

@Transactional
public void deleteById(String id) {
    bookRepository.deleteById(id);
}

9.MySQL数据类型，参考文章

日期用timestamp，对应实体类的java.util.Date类型
价格用decimal(m,n)，对应实体类的BigDecimal类型

10.SpringData JPA 关系映射，参考文章1，文章2

@OneToOne @OneToMany @ManyToOne

11.SpringData JPA的整型主键生成策略，参考文章

1）MySQL

@Entity
@Data
public class Test {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Long id;
}

2）Oracle

@Entity
@Data
public class Test {
    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="TestSequence")
    @SequenceGenerator(name = "TestSequence", sequenceName = "SEQ_Test", allocationSize=1)
    private Long id;
}

12. Spring Data JPA的UUID主键生成策略，参考文章

@Entity
public class User implements Serializable {
    @Id
    @GenericGenerator(name = "jpa-uuid", strategy = "uuid")
    @GeneratedValue(generator = "jpa-uuid")
    @Column(length = 32)
    private String userId;
    ...
}

13.@JsonIgnoreProperties({"handler", "hibernateLazyInitializer"})，参考文章

• 目的：解决jackson 序列化Hibernate entity类无限递归的问题

14.@JsonProperty

// 序列化为json对象时，改用其他变量名
@JsonProperty("id")   
private String productId;

15.Hibernate中的数据库方言（Dialect），参考文章

16.Spring Data JPA 删除记录

• 自定义SQL删除语句更快

@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
public class Person {
    @Id
    private String id;
    private String name;
}

public interface PersonRepository extends JpaRepository<Person, String> {
    // 方式一：底层ORM框架自动生成SQL
    // Hibernate: select person0_.id as id1_0_, person0_.name as name2_0_ from person person0_ where person0_.name=?
    // Hibernate: delete from person where id=?
    void deleteByName(String name);
}

public interface PersonRepository extends JpaRepository<Person, String> {
    // 方式二：自定义SQL语句，直接删除
    // Hibernate: delete from person where name=?
    @Modifying
    @Query(value = "delete from person where name=?1", nativeQuery = true)
    void deleteByName(String name);
}

@RestController
public class HelloController {
    @Autowired
    private PersonRepository personRepository;

    @GetMapping("/test")
    @Transactional
    public String test() {
        personRepository.save(new Person("123","zhangsan"));
        personRepository.deleteByName("zhangsan");
        return "删除成功";
    }
}

17.jpa查询规范

Keyword	Sample	JPQL snippet
IsNotNull	findByAgeNotNull	... where x.age not null
Like	findByNameLike	... where x.name like ?1
NotLike	findByNameNotLike	... where x.name not like ?1
StartingWith	findByNameStartingWith	... where x.name like ?1(parameter bound with appended %)
EndingWith	findByNameEndingWith	... where x.name like ?1(parameter bound with prepended %)
Containing	findByNameContaining	... where x.name like ?1(parameter bound wrapped in %)
OrderBy	findByAgeOrderByName	... where x.age = ?1 order by x.name desc
Not	findByNameNot	... where x.name <> ?1
In	findByAgeIn	... where x.age in ?1
NotIn	findByAgeNotIn	... where x.age not in ?1
True	findByActiveTrue	... where x.avtive = true
Flase	findByActiveFalse	... where x.active = false
And	findByNameAndAge	... where x.name = ?1 and x.age = ?2
Or	findByNameOrAge	... where x.name = ?1 or x.age = ?2
Between	findBtAgeBetween	... where x.age between ?1 and ?2
LessThan	findByAgeLessThan	... where x.age < ?1
GreaterThan	findByAgeGreaterThan	... where x.age > ?1
After/Before	...	...
IsNull	findByAgeIsNull	... where x.age is null