Spring Data - powerful and succinct abstraction

Guestbook

Database tier definition

Database tables, indexes and foreign keys defined in Liquibase configuration:

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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
databaseChangeLog:
  - changeSet:
      id: 1
      author: Terrence Miao
      changes:
        - createTable:
            tableName: draft_order
            columns:
              - column:
                  name: id
                  type: int
                  autoIncrement: true
                  constraints:
                    primaryKey: true
                    nullable: false
              - column:
                  name: c_number
                  type: varchar(32)
                  constraints:
                    nullable: false
              - column:
                  name: source_time_in_ms
                  type: bigint
                  constraints:
                    nullable: false
              - column:
                  name: source_item_id
                  type: varchar(255)
                  constraints:
                    nullable: false
              - column:
                  name: shipment
                  type: json
                  constraints:
                    nullable: false
              - column:
                  name: shipment_id
                  type: varchar(255)
                  constraints:
                    nullable: true
              - column:
                  name: quantity
                  type: int
                  constraints:
                    nullable: false
              - column:
                  name: source_system
                  type: varchar(255)
                  constraints:
                    nullable: false
              - column:
                  name: status
                  type: varchar(32)
                  constraints:
                    nullable: false
        - createIndex:
            columns:
            - column:
                name: source_item_id
            indexName: idx_source_item_id
            tableName: draft_order
            unique: false
        - createIndex:
            columns:
            - column:
                name: c_number
            - column:
                name: source_item_id
            indexName: idx_c_number_source_item_id
            tableName: draft_order
            unique: true
        - createTable:
            tableName: draft_order_combined
            columns:
              - column:
                  name: id
                  type: int
                  autoIncrement: true
                  constraints:
                    primaryKey: true
                    nullable: false
              - column:
                  name: combined_id
                  type: varchar(64)
                  constraints:
                    nullable: false
              - column:
                  name: draft_order_id
                  type: int
                  constraints:
                    nullable: false
        - addForeignKeyConstraint:
            baseColumnNames: draft_order_id
            baseTableName: draft_order_combined
            constraintName: fk_draft_order_combined_draft_order
            onDelete: CASCADE
            onUpdate: RESTRICT
            referencedColumnNames: id
            referencedTableName: draft_order
  - changeSet:
      id: 2
      author: Terrence Miao
      changes:
        - addColumn:
            columns:
              - column:
                  # For MySQL 5.7.x above, the first TIMESTAMP column in the table gets current timestamp as the default value, likely. So 
                  # if an INSERT or UPDATE without supplying a value, the column will get the current timestamp.  Any subsequent TIMESTAMP 
                  # columns should have a default value explicitly defined. If you have two TIMESTAMP columns and if you don't specify a 
                  # default value for the second column, you will get this error while trying to create the table: 
                  #   ERROR 1067 (42000): Invalid default value for 'COLUMN_NAME'
                  name: date_created
                  type: timestamp(3)
                  constraints:
                    nullable: false
              - column:
                  name: date_updated
                  type: timestamp(3)
		  defaultValueComputed: LOCALTIMESTAMP(3)
                  constraints:
                    nullable: false
            tableName: draft_order

DAO definition

  • Draft Order
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
@Entity
@Table(name = "draft_order")
public class DraftOrder implements Serializable {

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

    @Column(name = "c_number")
    private String cNumber;

    @Column(name = "source_time_in_ms")
    private Long sourceTimeInMs;

    @Column(name = "source_item_id")
    private String sourceItemId;

    @Column(name = "shipment", columnDefinition = "json")
    private String shipment;

    @Column(name = "shipment_id")
    private String shipmentId;

    @Column(name = "quantity")
    private Integer quantity;

    @Column(name = "source_system")
    private String sourceSystem;

    @Column(name = "status")
    private String status;
}
  • Draft Order Combined
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@Entity
@Table(name = "draft_order_combined")
public class DraftOrderCombined implements Serializable {

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

    @Column(name = "combined_id")
    private String combinedId;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "draft_order_id")
    private DraftOrder draftOrder;
}
  • An middle Aggregation Object
1
2
3
4
5
6
7
8
9
10
11
12
public class CombinedIdSourceTimeInMs {

    private Long counter;
    private String combinedId;
    private Long sourceTimeInMs;

    public CombinedIdSourceTimeInMs(Long counter, String combinedId, Long sourceTimeInMs) {
        this.counter = counter;
        this.combinedId = combinedId;
        this.sourceTimeInMs = sourceTimeInMs;
    }
}

CRUD Repository definition

  • DraftOrderRepository
1
2
3
4
5
6
7
8
9
10
11
12
13
14
public interface DraftOrderRepository extends CrudRepository<DraftOrder, Integer> {

    List<DraftOrder> findByCNumberAndStatusOrderBySourceTimeInMsDesc(String cNumber, String status, Pageable pageable);

    List<DraftOrder> findByCNumberAndSourceItemIdIn(String cNumber, List<String> sourceItemIds);

    DraftOrder findByCNumberAndSourceItemId(String cNumber, String sourceItemId);
    
    List<DraftOrder> findByShipmentIdInAndStatusAndSourceSystem(List<String> shipmentIds, String status, String sourceSystem);

    List<DraftOrder> findByCNumberAndId(String cNumber, Integer id);

    Long countByCNumberAndStatus(String cNumber, String status);
}
  • DraftOrderCombinedRepository
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
public interface DraftOrderCombinedRepository extends CrudRepository<DraftOrderCombined, Integer> {

    String FIND_QUERY =
            "SELECT new org.paradise.data.dao.CombinedIdSourceTimeInMs"
                    + "(count(doc) as counter, doc.combinedId as combinedId, min(doc.draftOrder.sourceTimeInMs) as sourceTimeInMs) "
                    + " FROM DraftOrderCombined doc WHERE doc.draftOrder.cNumber = :cNumber AND doc.draftOrder.status = :status "
                    + " GROUP BY combinedId "
                    + " ORDER BY sourceTimeInMs DESC";

    String COUNT_QUERY = "SELECT count(1) FROM "
            + "(SELECT count(1) FROM DraftOrderCombined doc WHERE doc.draftOrder.cNumber = :cNumber AND doc.draftOrder.status = :status"
            + "  GROUP BY doc.combinedId)";

    @Query(value = FIND_QUERY, countQuery = COUNT_QUERY)
    List<CombinedIdSourceTimeInMs> countPerCombinedIdAndSourceTimeInMs(@Param("cNumber") String cNumber,
                                                                       @Param("status") String status, Pageable pageable);

    List<DraftOrderCombined> findByCombinedIdOrderByDraftOrderDaoSourceTimeInMsDesc(String combinedId);
}

References