Chung Cheuk Hang MichaelJava Web Developer

JPA／Hibernate 使用方式（二）

Continued from previous post:

JPA／Hibernate 使用方式（一）

1 Maven dependencies
2 Java code
3 Application 配置
4 初始化 DB
- 4.1 初始化 tables
- 4.2 初始化數據
5 測試
6 筆記
7 參考資料

1 Maven dependencies

1<parent>
2	<groupId>org.springframework.boot</groupId>
3	<artifactId>spring-boot-starter-parent</artifactId>
4	<version>3.4.0</version>
5</parent>
6
7<dependencies>
8	<dependency>
9		<groupId>org.springframework.boot</groupId>
10		<artifactId>spring-boot-starter-web</artifactId>
11	</dependency>
12	<dependency>
13		<groupId>org.springframework.boot</groupId>
14		<artifactId>spring-boot-starter-security</artifactId>
15	</dependency>
16
17	<dependency>
18		<groupId>org.springframework.boot</groupId>
19		<artifactId>spring-boot-starter-data-jpa</artifactId>
20	</dependency>
21
22	<!-- for multiple Spring profiles -->
23	<dependency>
24		<groupId>com.h2database</groupId>
25		<artifactId>h2</artifactId>
26	</dependency>
27	<dependency>
28		<groupId>com.microsoft.sqlserver</groupId>
29		<artifactId>mssql-jdbc</artifactId>
30	</dependency>
31	<dependency>
32		<groupId>com.oracle.database.jdbc</groupId>
33		<artifactId>ojdbc11</artifactId>
34	</dependency>
35	<dependency>
36		<groupId>org.postgresql</groupId>
37		<artifactId>postgresql</artifactId>
38	</dependency>
39
40	<dependency>
41		<groupId>org.projectlombok</groupId>
42		<artifactId>lombok</artifactId>
43		<scope>provided</scope>
44	</dependency>
45</dependencies>

2 Java code

2.1 Controller

OrderController.java：

1@Slf4j
2@RestController
3@RequestMapping("/orders")
4public class OrderController {
5
6    OrderService service;
7
8    public OrderController(OrderService service) {
9        this.service = service;
10    }
11
12    @GetMapping
13    public List<OrderEntity> getAllWithSearch(
14            @RequestParam(name = "remarks" , required = false) String remarks) {
15        log.info("GET /orders{}", remarks == null ? "" : ("?remarks=" + remarks));
16        return service.getAllWithSearch(remarks);
17    }
18
19    @GetMapping("/{id}")
20    public OrderEntity getById(@PathVariable(name = "id", required = true) Long id) {
21        log.info("GET /orders/{}", id);
22        return service.getById(id);
23    }
24
25    @PostMapping
26    public OrderEntity create(@RequestBody OrderEntity order) {
27        log.info("POST /orders");
28        return service.create(order);
29    }
30
31    @PutMapping
32    public OrderEntity update(@RequestBody OrderEntity order) {
33        log.info("PUT /orders");
34        return service.update(order);
35    }
36
37    @DeleteMapping("/{id}")
38    public OrderEntity delete(@PathVariable(name = "id", required = true) Long id) {
39        log.info("DELETE /orders/{}", id);
40        return service.delete(id);
41    }
42}

2.2 Service

OrderService.java：

1import org.springframework.transaction.annotation.Transactional;
2
3@Slf4j
4@Service
5@Transactional(rollbackFor = Throwable.class)
6public class OrderService {
7
8    OrderRepo repo;
9    EntityManager em;
10
11    public OrderService(OrderRepo repo, EntityManager em) {
12        this.repo = repo;
13        this.em = em;
14    }
15
16
17
18//    public List<OrderEntity> getAll() {
19//        log.info("getAll");
20//        return repo.findAll();
21//    }
22
23    public List<OrderEntity> getAllWithSearch(String remarks) {
24        log.info("getAllWithSearch (remarks={})", remarks);
25
26        final Specification<OrderEntity> spec = StringUtils.isBlank(remarks) ? null
27            : (root, query, criteriaBuilder) -> {
28                return criteriaBuilder.like(root.get("remarks"), "%" + remarks + "%");
29            };
30
31        return repo.findAll(spec);
32    }
33
34    public OrderEntity getById(Long id) {
35        log.info("getById (id={})", id);
36        return repo.findById(id).orElseThrow();
37    }
38
39    public OrderEntity create(OrderEntity order) {
40        log.info("create (order={})", order);
41        return repo.save(order);
42    }
43
44    public OrderEntity update(OrderEntity order) {
45
46        // 而家 order 係 detached entity
47
48        log.info("update (order={})", order);
49        order = repo.saveAndFlush(order);
50
51        // repo.saveAndFlush 會即時更新 DB 紀錄，然後返回一個 managed entity
52        // 所以而家 order 係 managed entity
53
54        em.refresh(order); // 用 EntityManager 喺 DB 重新拎過呢個 order 紀錄，放落 first level cache
55
56        return order;
57    }
58
59    public OrderEntity delete(Long id) {
60        log.info("delete (id={})", id);
61        final OrderEntity order = repo.findById(id).orElseThrow();
62        repo.deleteById(id);
63        return order;
64    }
65}

DatabaseUserDetailsService.java：

1@Slf4j
2@Service
3public class DatabaseUserDetailsService implements UserDetailsService {
4
5    UserRepo userRepo;
6
7    public DatabaseUserDetailsService(UserRepo userRepo) {
8        this.userRepo = userRepo;
9    }
10
11    @Override
12    public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
13
14        log.info("loadUserByUsername - username: {}", username);
15
16        final UserEntity userEntity = userRepo.findByUsername(username);
17
18        if (userEntity == null) {
19            log.warn("User [{}] cannot be found.", username);
20            throw new UsernameNotFoundException("User [" + username + "] cannot be found.");
21        }
22
23        log.info("User [{}] is found.", username);
24
25        return User.builder()
26                .username(userEntity.getUsername())
27                .password(userEntity.getPassword())
28                .disabled(!userEntity.isEnabled())
29                .roles("USER")
30                .build();
31    }
32}

2.3 Repository

OrderRepo.java：

@Repository
public interface OrderRepo extends JpaRepository<OrderEntity, Long>,
                                    JpaSpecificationExecutor<OrderEntity> {
}

UserRepo.java：

1@Repository
2public interface UserRepo extends JpaRepository<UserEntity, Long>,
3                                    JpaSpecificationExecutor<UserEntity> {
4
5    UserEntity findByUsername(@Param(value = "username") String username);
6}

2.4 Entity

BaseEntity.java：

1@Data
2@FieldDefaults(level = PROTECTED)
3@MappedSuperclass
4@EntityListeners(AuditingEntityListener.class)
5public abstract class BaseEntity {
6
7    @Id
8    @GeneratedValue(strategy = GenerationType.IDENTITY)
9    Long id;
10
11    @CreatedBy
12    @Column(name = "created_user", updatable = false)
13    String createdUser;
14
15    @CreatedDate
16    @Column(name = "created_datetime", updatable = false)
17    LocalDateTime createdDatetime;
18
19    @LastModifiedBy
20    @Column(name = "last_modified_user")
21    String lastModifiedUser;
22
23    @LastModifiedDate
24    @Column(name = "last_modified_datetime")
25    LocalDateTime lastModifiedDatetime;
26
27    @Version
28    @Column(name = "version")
29    Long version;
30}

OrderEntity.java：

1@Data
2@ToString(callSuper = true)
3@EqualsAndHashCode(callSuper = true)
4@FieldDefaults(level = PRIVATE)
5@Entity
6@Table(name = "tbl_order")
7public class OrderEntity extends BaseEntity {
8
9    @Column(name = "remarks")
10    String remarks;
11
12    @Column(name = "order_datetime")
13    LocalDateTime orderDatetime;
14}

UserEntity.java：

1@Data
2@ToString(callSuper = true)
3@EqualsAndHashCode(callSuper = true)
4@FieldDefaults(level = PRIVATE)
5@Entity
6@Table(name = "tbl_user")
7public class UserEntity extends BaseEntity {
8
9    @Column(name = "username")
10    String username;
11
12    @Column(name = "password")
13    String password;
14
15    @Column(name = "enabled")
16    boolean enabled;
17}

2.5 Spring Security config

SecurityConfig.java：

1@Configuration
2public class SecurityConfig {
3
4    @Bean
5    public SecurityFilterChain securityFilterChain(HttpSecurity http) throws Exception {
6        return http
7            .authorizeHttpRequests(authz -> authz
8                .requestMatchers("/orders/**").hasRole("USER")
9                .anyRequest().permitAll())
10            .httpBasic(Customizer.withDefaults())
11            .formLogin(e -> e.disable())
12            .csrf(e -> e.disable())
13            .headers(e -> e.frameOptions(o -> o.disable())) // for H2 console
14            .build();
15    }
16
17    @Bean
18    public BCryptPasswordEncoder passwordEncoder() {
19        return new BCryptPasswordEncoder();
20    }
21}

2.6 Auditing implementation

AuditorAwareImpl.java：

1@Component
2public class AuditorAwareImpl implements AuditorAware<String> {
3
4    @Override
5    public Optional<String> getCurrentAuditor() {
6
7        final Authentication authentication = SecurityContextHolder.getContext().getAuthentication();
8
9        if (authentication == null || !authentication.isAuthenticated()) {
10            return Optional.empty();
11        }
12
13        return Optional.of(authentication.getName());
14    }
15}

2.7 Jackson config

JacksonConfig.java：

1@Configuration
2public class JacksonConfig {
3
4    @Bean
5    public ObjectMapper objectMapper() {
6
7        final SimpleModule module = new SimpleModule();
8        module.addSerializer(LocalDateTime.class, new LocalDateTimeToEpochSerializer());
9        module.addDeserializer(LocalDateTime.class, new LocalDateTimeToEpochDeserializer());
10
11        final ObjectMapper mapper = new ObjectMapper();
12        mapper.registerModule(module);
13
14        return mapper;
15    }
16}
17
18class LocalDateTimeToEpochSerializer extends JsonSerializer<LocalDateTime> {
19
20    @Override
21    public void serialize(LocalDateTime value, JsonGenerator gen, SerializerProvider serializers)
22                    throws IOException {
23        gen.writeNumber(value.toInstant(ZoneOffset.UTC).toEpochMilli());
24    }
25}
26
27class LocalDateTimeToEpochDeserializer extends JsonDeserializer<LocalDateTime> {
28
29    @Override
30    public LocalDateTime deserialize(JsonParser p, DeserializationContext ctxt)
31                            throws IOException, JacksonException {
32        return LocalDateTime.ofInstant(Instant.ofEpochMilli(p.getLongValue()), ZoneOffset.UTC);
33    }
34}

3 Application 配置

application.yml：

1spring:
2  jpa:
3    show-sql: true
4    hibernate:
5      ddl-auto: update # 自動創建 tables
6
7---
8
9logging.level:
10  org.springframework.security: DEBUG
11  org.hibernate.SQL: DEBUG # show SQL statements
12  org.hibernate.orm.jdbc.bind: TRACE # show parameters

application-h2.yml（Spring profile h2）：

1# H2 (SQL Server mode)
2spring:
3  datasource:
4    url: jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;TRACE_LEVEL_FILE=4;MODE=MSSQLServer;DATABASE_TO_UPPER=FALSE;CASE_INSENSITIVE_IDENTIFIERS=TRUE
5    username: sa
6    password: sa
7  h2:
8    console:
9      enabled: true
10      path: /h2-console

application-mssql.yml（Spring profile mssql）：

spring:
  datasource:
    url: jdbc:sqlserver://localhost:1433;databaseName=dev;encrypt=false
    username: sa
    password: StrongPassword123

application-oracle.yml（Spring profile oracle）：

spring:
  datasource:
    url: jdbc:oracle:thin:@localhost:1521:xe
    username: system
    password: oracle

application-postgres.yml（Spring profile postgres）：

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/postgres
    username: postgres
    password: postgres

4 初始化 DB

4.1 初始化 tables

如果 spring.jpa.hibernate.ddl-auto 既配置係 none，我地就要自行初始化 DB 裡面既 table schema。而以下既 CREATE SQL 都係由 JPA/Hibernate 生成。

SQL Server、H2（SQL Server mode）：

1create table tbl_order (
2	id bigint identity not null,
3	remarks varchar(255),
4	order_datetime datetime2(6),
5
6	created_user varchar(255),
7	created_datetime datetime2(6),
8	last_modified_user varchar(255),
9	last_modified_datetime datetime2(6),
10	version bigint,
11
12	primary key (id)
13);
14
15create table tbl_user (
16	id bigint identity not null,
17	username varchar(255),
18	password varchar(255),
19	enabled bit,
20
21	created_user varchar(255),
22	created_datetime datetime2(6),
23	last_modified_user varchar(255),
24	last_modified_datetime datetime2(6),
25	version bigint,
26
27	primary key (id)
28);

Oracle：

1create table tbl_order (
2	id number(19,0) generated by default as identity,
3	remarks varchar2(255 char),
4	order_datetime timestamp(6),
5
6	created_user varchar2(255 char),
7	created_datetime timestamp(6),
8	last_modified_user varchar2(255 char),
9	last_modified_datetime timestamp(6),
10	version number(19,0),
11	primary key (id)
12);
13
14create table tbl_user (
15	id number(19,0) generated by default as identity,
16	username varchar2(255 char),
17	password varchar2(255 char),
18	enabled number(1,0) check (enabled in (0,1)),
19
20	created_user varchar2(255 char),
21	created_datetime timestamp(6),
22	last_modified_user varchar2(255 char),
23	last_modified_datetime timestamp(6),
24	version number(19,0),
25
26	primary key (id)
27);

PostgreSQL：

1create table tbl_order (
2	id bigint generated by default as identity,
3	remarks varchar(255),
4	order_datetime timestamp(6),
5
6	created_user varchar(255),
7	created_datetime timestamp(6),
8	last_modified_user varchar(255),
9	last_modified_datetime timestamp(6),
10	version bigint,
11
12	primary key (id)
13);
14
15create table tbl_user (
16	id bigint generated by default as identity,
17	username varchar(255),
18	password varchar(255),
19	enabled boolean,
20
21	created_user varchar(255),
22	created_datetime timestamp(6),
23	last_modified_user varchar(255),
24	last_modified_datetime timestamp(6),
25	version bigint,
26
27	primary key (id)
28);

4.2 初始化數據

SQL Server、Oracle、H2：

insert into tbl_user (username, password, enabled) values ('mick', '$2a$12$589/AIvI/dCgNo/JBs3kSe1nxT29oXmxmYvU7NMcMH1P1B2cNe2Xa', 1);
insert into tbl_user (username, password, enabled) values ('jack', '$2a$12$vxae.8GVw3GhoGUXaLSA3uDyHvm6X246zzuS3Z4TxhbhTKI6tKthW', 1);

PostgreSQL：

insert into tbl_user (username, password, enabled) values ('mick', '$2a$12$589/AIvI/dCgNo/JBs3kSe1nxT29oXmxmYvU7NMcMH1P1B2cNe2Xa', true);
insert into tbl_user (username, password, enabled) values ('jack', '$2a$12$vxae.8GVw3GhoGUXaLSA3uDyHvm6X246zzuS3Z4TxhbhTKI6tKthW', true);

用戶資料：

用戶名	密碼（加密前）
`mick`	`mickpw`
`jack`	`jackpw`

5 測試

5.1 Create API

新增 order 紀錄（ID = 1）：

curl -X POST -u mick:mickpw localhost:8080/orders -H "Content-Type: application/json" -d "{ \"remarks\": \"foo\", \"orderDatetime\": 1730000000000 }"

結果：

1{
2	"id": 1,
3	"createdUser": "mick",
4	"createdDatetime": 1742149186385,
5	"lastModifiedUser": "mick",
6	"lastModifiedDatetime": 1742149186385,
7	"version": 0,
8	"remarks": "foo",
9	"orderDatetime": 1730000000000
10}

新增 order 紀錄（ID = 2）：

curl -X POST -u mick:mickpw localhost:8080/orders -H "Content-Type: application/json" -d "{ \"remarks\": \"foobar\", \"orderDatetime\": 1740000000000 }"

結果：

1{
2	"id": 2,
3	"createdUser": "mick",
4	"createdDatetime": 1742149186702,
5	"lastModifiedUser": "mick",
6	"lastModifiedDatetime": 1742149186702,
7	"version": 0,
8	"remarks": "foobar",
9	"orderDatetime": 1740000000000
10}

5.2 Read API

查詢所有 order 紀錄：

curl -X GET -u mick:mickpw localhost:8080/orders

結果：

1[
2	{
3		"id": 1,
4		"createdUser": "mick",
5		"createdDatetime": 1742149186385,
6		"lastModifiedUser": "mick",
7		"lastModifiedDatetime": 1742149186385,
8		"version": 0,
9		"remarks": "foo",
10		"orderDatetime": 1730000000000
11	},
12	{
13		"id": 2,
14		"createdUser": "mick",
15		"createdDatetime": 1742149186702,
16		"lastModifiedUser": "mick",
17		"lastModifiedDatetime": 1742149186702,
18		"version": 0,
19		"remarks": "foobar",
20		"orderDatetime": 1740000000000
21	}
22]

搜尋所有 order 紀錄（remarks contains bar）：

curl -X GET -u mick:mickpw localhost:8080/orders?remarks=bar

結果：

1[
2	{
3		"id": 2,
4		"createdUser": "mick",
5		"createdDatetime": 1742149186702,
6		"lastModifiedUser": "mick",
7		"lastModifiedDatetime": 1742149186702,
8		"version": 0,
9		"remarks": "foobar",
10		"orderDatetime": 1740000000000
11	}
12]

5.3 Update API

修改 order 紀錄（ID = 2）：

curl -X PUT -u jack:jackpw localhost:8080/orders -H "Content-Type: application/json" -d "{\"id\": 2, \"createdUser\": \"fake\", \"createdDatetime\": 111111, \"lastModifiedUser\": \"fake\", \"lastModifiedDatetime\": 111111, \"remarks\": \"bar\", \"orderDatetime\": 1740000000005, \"version\": 0}"

結果：

1{
2	"id": 2,
3	"createdUser": "mick",
4	"createdDatetime": 1742149186702,
5	"lastModifiedUser": "jack",
6	"lastModifiedDatetime": 1742149191350,
7	"version": 1,
8	"remarks": "bar",
9	"orderDatetime": 1740000000005
10}

查詢所有 order 紀錄：

curl -X GET -u mick:mickpw localhost:8080/orders

結果：

1[
2	{
3		"id": 1,
4		"createdUser": "mick",
5		"createdDatetime": 1742149186385,
6		"lastModifiedUser": "mick",
7		"lastModifiedDatetime": 1742149186385,
8		"version": 0,
9		"remarks": "foo",
10		"orderDatetime": 1730000000000
11	},
12	{
13		"id": 2,
14		"createdUser": "mick",
15		"createdDatetime": 1742149186702,
16		"lastModifiedUser": "jack",
17		"lastModifiedDatetime": 1742149191350,
18		"version": 1,
19		"remarks": "bar",
20		"orderDatetime": 1740000000005
21	}
22]

用同之前一樣既 version value（即係 0）去修改 order 紀錄（ID = 2）：

curl -X PUT -u jack:jackpw localhost:8080/orders -H "Content-Type: application/json" -d "{\"id\": 2, \"createdUser\": \"fake\", \"createdDatetime\": 111111, \"lastModifiedUser\": \"fake\", \"lastModifiedDatetime\": 111111, \"remarks\": \"bar2\", \"orderDatetime\": 1740000000007, \"version\": 0}"

結果：

1{
2	"timestamp": 1742114659243,
3	"status": 500,
4	"error": "Internal Server Error",
5	"path": "/orders"
6}

Console log：

Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.orm.ObjectOptimisticLockingFailureException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect) : [code.entity.OrderEntity#2]] with root cause
org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect) : [code.entity.OrderEntity#2]

查詢所有 order 紀錄：

curl -X GET -u mick:mickpw localhost:8080/orders

結果：

1[
2	{
3		"id": 1,
4		"createdUser": "mick",
5		"createdDatetime": 1742149186385,
6		"lastModifiedUser": "mick",
7		"lastModifiedDatetime": 1742149186385,
8		"version": 0,
9		"remarks": "foo",
10		"orderDatetime": 1730000000000
11	},
12	{
13		"id": 2,
14		"createdUser": "mick",
15		"createdDatetime": 1742149186702,
16		"lastModifiedUser": "jack",
17		"lastModifiedDatetime": 1742149191350,
18		"version": 1,
19		"remarks": "bar",
20		"orderDatetime": 1740000000005
21	}
22]

5.4 Delete API

刪除 order 紀錄（ID = 2）：

curl -X DELETE -u mick:mickpw localhost:8080/orders/2

結果：

1{
2	"id": 2,
3	"createdUser": "mick",
4	"createdDatetime": 1742149186702,
5	"lastModifiedUser": "jack",
6	"lastModifiedDatetime": 1742149191350,
7	"version": 1,
8	"remarks": "bar",
9	"orderDatetime": 1740000000005
10}

查詢所有 order 紀錄：

curl -X GET -u mick:mickpw localhost:8080/orders

結果：

1[
2	{
3		"id": 1,
4		"createdUser": "mick",
5		"createdDatetime": 1742149186385,
6		"lastModifiedUser": "mick",
7		"lastModifiedDatetime": 1742149186385,
8		"version": 0,
9		"remarks": "foo",
10		"orderDatetime": 1730000000000
11	}
12]

6 筆記

6.1 JPA/Hibernate 既 first level cache

當我地執行 JpaRepository 既 save()、delete() methods 既時候，JPA/Hibernate 會執行 SELECT SQL 用 ID 獲取 DB 紀錄，然後放喺 JPA/Hibernate 既 session 既 first level cache。
如果 JPA/Hibernate 既 session 既 first level cache 裡面有紀錄，JPA/Hibernate 就唔會再訪問 DB 獲取紀錄。
JPA/Hibernate 既 session 會隨住 @Transactional 創建既 DB transaction 而完結。
- 通常一個 HTTP request 係一個 single unit of work，負責執行一個完整既業務操作，所以通常只會用一個 DB transaction 做曬所有 DB 改動。
- Spring 既 @Transactional annotation 既 propagation attribute 既默認值係 Propagation.REQUIRED。
  - 無論一個 HTTP request 經過幾多個 @Service classes 以及 @Repository interfaces，就算佢地都有 annotate 到 @Transactional，佢地都只會 share 同一個 DB transaction。

6.2 用 `@Version` 實現 optimistic locking

OrderEntity 裡面既 version field annotate 左 @Version，JPA/Hibernate 會幫我地管理呢個 field/column 既 value。

6.2.1 新增紀錄

喺用 repo.save(order); 新增紀錄既時候：
- Annotate 左 @Version 既 version field 既 column value 會係 0。

6.2.2 更新紀錄

JPA/Hibernate 會用到 optimistic locking 既方式去更新紀錄。
喺 repo.saveAndFlush(order); 既時候：
- JPA/Hibernate 生成既 UPDATE SQL 既 WHERE clause 會多左 version = ?。
- 如果 prepared statement 既 version parameter 同 DB 紀錄既 version column 一樣，就會更新紀錄，而更新後既紀錄既 version column 會係本來既 +1。
- 如果 prepared statement 既 version parameter 同 DB 紀錄既 version column 唔一樣，就會更新唔到紀錄，JPA/Hibernate 會 throw StaleObjectStateException。
- 如果 DB 紀錄既 version column 係 null，JPA/Hibernate 就唔會執行 UPDATE SQL，但係會 throw StaleObjectStateException。
- 如果 HTTP request body 既 version field 係 null，JPA/Hibernate 就會 throw Detached entity with generated id '1' has an uninitialized version value 'null': code.entity.OrderEntity.version。
必需要喺 transaction 裡面進行。

JPA/Hibernate 生成既 UPDATE SQL：

update tbl_order set last_modified_datetime=?,last_modified_user=?,order_datetime=?,remarks=?,version=? where id=? and version=?

6.2.3 刪除紀錄

喺 repo.delete(id); 既時候：
- JPA/Hibernate 生成既 DELETE SQL 既 WHERE clause 會多左 version = ?。
- 如果 prepared statement 既 version parameter 同 DB 紀錄既 version column 唔一樣，就會更新唔到紀錄，JPA/Hibernate 會 throw StaleObjectStateException。
- 如果 DB 紀錄既 version column 係 null，JPA/Hibernate 就唔會執行 DELETE SQL，所以就唔會刪除到呢個紀錄，亦唔會 throw 任何 exception。

JPA/Hibernate 生成既 DELETE SQL：

delete from tbl_order where id=? and version=?

6.3 Auditing

因為我地有 AuditorAwareImpl，所以 annotate 左 @CreatedBy、@LastModifiedBy 既 fields 會用左個 HTTP request 所屬既 thread 裡面既 ThreadLocal 既 SecurityContext 既資料。
- 即係通過左 Spring Security authentication 既 Authentication object 既 name field。
我地可以用 @Column 既 attribute updatable = false 黎避免 @CreatedBy 以及 @CreatedDate 既 fields 喺日後被更新。
依測試所見，就算 HTTP request body 裡面有 lastModifiedUser 以及 lastModifiedDatetime fields，喺保存紀錄之前，Spring 都會覆寫佢地既 values。

6.4 Criteria API

criteriaBuilder.like(root.get("remarks"), "%" + remarks + "%") 會生成以下既 SELECT SQL：

select oe1_0.id,oe1_0.created_datetime,oe1_0.created_user,oe1_0.last_modified_datetime,oe1_0.last_modified_user,oe1_0.order_datetime,oe1_0.remarks,oe1_0.version from tbl_order oe1_0 where oe1_0.remarks like ? escape ''

JPA/Hibernate 生成左 like ? escape '' 出黎：

因為係 prepared statement，所以唔會有 SQL injection 既問題。
就算我地冇提供到 escape character，佢都會自動加上 escape ''。

7 參考資料

6330 1333

michaelboyboy@gmail.com

www.linkedin.com/in/mickchung

Developed with React, Bootstrap and Next.js.

JPA／Hibernate 使用方式（二）

Table of contents

1 Maven dependencies

2 Java code

2.1 Controller

2.2 Service

2.3 Repository

2.4 Entity

2.5 Spring Security config

2.6 Auditing implementation

2.7 Jackson config

3 Application 配置

4 初始化 DB

4.1 初始化 tables

4.2 初始化數據

5 測試

5.1 Create API

5.2 Read API

5.3 Update API

5.4 Delete API

6 筆記

6.1 JPA/Hibernate 既 first level cache

6.2 用 @Version 實現 optimistic locking

6.2.1 新增紀錄

6.2.2 更新紀錄

6.2.3 刪除紀錄

6.3 Auditing

6.4 Criteria API

7 參考資料

6.2 用 `@Version` 實現 optimistic locking