# How to Use Relational Database in App

## How to Connect Database
1. Contact SiteOps to get the JDBC info for environment.  
2. Edit `jdbc.url`, `jdbc.user`, `jdbc.password` in `configCenter.json`;
3. Import `configCenter.json` to insureMO portal Config Center and deploy the config center data.
4. Configure environment variables and run `App.java`.

## How to Use Spring Data JPA  
Spring Data JPA is a framework for simplifying JPA development, aiming to improve the implementation of Data Access Layer to increase development efficiency. It provides:

* Normalized DAO interface method naming
* Database access and operation without DAO implementation
* Basic CRUD function and complex criteria query, such as pagination and sorting
### Entity  
It is designed to establish a mapping between Java Entity and database table.
```java
@Entity
@Table(name = "t_mail_history")
public class TMailHistoryEntity implements Serializable{

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

    @Column(name = "account")
    private String account;

    @Column(name = "consignee")
    private String consignee;

    @Column(name = "subject")
    private String subject;

    @Column(name = "templateCode")
    private String templateCode;

    //getter and setter...
}
```
### Repository  
The capability of Data Access Layer is implemented through Repository interface and its sub-interfaces.
Repository provides data access capabilities, and on this basis, JPARepository extends basic CRUD and pagination capabilities.
```java
@NoRepositoryBean
public interface JpaRepository<T, ID> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {
    ......
}
```
```java
@NoRepositoryBean
public interface PagingAndSortingRepository<T, ID> extends CrudRepository<T, ID> {
    Iterable<T> findAll(Sort var1);
    Page<T> findAll(Pageable var1);
}
```
```java
@NoRepositoryBean
public interface CrudRepository<T, ID> extends Repository<T, ID> {
    <S extends T> S save(S var1);
    <S extends T> Iterable<S> saveAll(Iterable<S> var1);
    Optional<T> findById(ID var1);
    boolean existsById(ID var1);
    Iterable<T> findAll();
    Iterable<T> findAllById(Iterable<ID> var1);
    long count();
    void deleteById(ID var1);
    void delete(T var1);
    void deleteAll(Iterable<? extends T> var1);
    void deleteAll();
}
```
When defining your own Repository interface, you can directly inherit JpaRepository to obtain various basic data operation methods built in by SpringBoot Data JPA. This establishes the relationship between Repository and Entity. According to the naming specification of Spring Data JPA, you can also extend new methods.
```java
@Repository
public interface MailHistoryRepository extends JpaRepository<TMailHistoryEntity, Long> {
    //e.g.
    List<TMailHistoryEntity> findBySubject(String subject);
    ......
}
```
### Service
In the service layer, you can call methods defined by your Repository interface for data operations. Here is an example of saving(inserting or updating).
```java 
@Autowired
MailHistoryRepository mailHistoryRepository;

public void logMailRecord(Map mailMap) {
    TMailHistoryEntity mailHistoryEntity = new TMailHistoryEntity();
    mailHistoryEntity.setAccount(mailMap.get("account").toString());
    mailHistoryEntity.setConsignee(mailMap.get("consignee").toString());
    mailHistoryEntity.setSubject(mailMap.get("subject").toString());
    mailHistoryEntity.setTemplateCode(mailMap.get("templateCode").toString());
    mailHistoryRepository.save(mailHistoryEntity);
}
```

## How to Use Liquibase
Here is a quick guide to help you get familiar with the liquibase. 

Liquibase facilities database schema change management by enabling you to revise and release database changes from development to production in a faster and safer manner.

### Define Directory Structure
The most common way to organize your change logs is by major release. Make certain directories to store change logs in your source control.
The following example specifies the directory resources/dbscript to maintain all the database scripts, with each subdirectory corresponding to a major release version.

```
dbscript
  db.changelog-master.xml
  1.0.0
    index.xml
    20220701_permission_ddl.xml
    20220702_permission_dml.xml
    20220702_permission_data.csv
  1.0.1
    index.xml
    20220720_user_dml.xml
```

### Set up the Root Changelog and Included Changelog Files
The root changelog file db.changelog-master.xml is passed to all Liquibase calls.
When new version files need to be created:
- Create a directory with a version name to store scripts of that version;
- Create an index.xml to include scripts properly;
- Update db.changelog-master.xml with the new included files.

> Use relativeToChangelogFile="true" in &lt;include&gt; and &lt;loadUpdateData&gt; 
```xml
<include relativeToChangelogFile="true" file="dbscript/1.0.0/index.xml" />
<include relativeToChangelogFile="true" file="20220701_permission_ddl.xml" />
<loadUpdateData encoding="UTF-8" file="20220702_permission_data.csv" relativeToChangelogFile="true" primaryKey="id" quotchar="&quot;" separator="," tableName="T_PERMISSION" />
```

### Prefer XML Rather Than Pure SQL
Liquibase supports the inclusion of the change logs in the following formats:
- SQL – Use SQL if you are familiar with it and you'd better not use a new way to specify database changes.
- Markup – Use a markup language like XML, JSON, or YAML to accomplish any of the following tasks.
    - Automate the rollback of new database objects.
    - Simplify the deployment a single set of changes to different types of databases.
    - Improve Hub and DB-doc reporting.

> It is recommended to use XML rather SQL to support multi-types of database and rollback, e.g. Oracle, MySQL and SQLServer.

### Specify One Change Per Changeset
Unless you are grouping several changes as a single transaction, we strongly recommend you specify only one change per changeset. This approach makes each change automatic within a single transaction.  
Each changeset either succeeds or fails. If it fails, you can correct and redeploy it until it succeeds.  
Multiple independent changes in a single changeset might introduce the risk that some changes are deployed while the later changes fail, leaving the database in a partially deployed state.

### Support Rollback
Always have &lt;preConditions&gt; in DDL changeset. If your change don't support auto rollback, please use &lt;rollback&gt; tag in changeset.

### Define Team's Changeset ID Format
Choose a changeset ID that is unique within its changelog. We recommend that you use a pattern to generate ID, e.g. combination rule:
- Primary object name and table name;
- Changeset headline;
- Date and sequence.

example:
```
id="T_PUB_CONTEXT_TYPE.rename_column_productId.20220701001"

id="T_PUB_CONTEXT_TYPE-rename_column_productId-20220701001"
```

### Database Object Naming Case Sensitivity
SQL keyword is case-insensitive, but database object name in some types of database is case-sensitive, e.g. SQLServer.  
Always keep naming consistent (using uppercase name is recommended) in all liquibase scripts, e.g. table name, column name, index name and constraint names. 

### Data Types
Some recommendations for data types:
- DECIMAL for numeric column, e.g. DECIMAL(15) for primary/foreign key column
- VARCHAR/CHAR for string column
- DATETIME for date/datetime
- Don't use boolean column; use CHAR(1) to store Y/N.

### Date/Time Format
Liquibase initially sets the date format to yyyy-MM-dd'T'HH:mm:ss and then it checks for two special cases which will override the data format string:

If the string representing the date/time includes a period (.), then the date format is changed to yyyy-MM-dd'T'HH:mm:ss.SSS.
If the string representing the date/time includes a space, then the date format is changed to yyyy-MM-dd HH:mm:ss.
Once the date format string is set, Liquibase will then call the SimpleDateFormat.parse() method attempting to parse the input string so that it can return a date/time.    

### Use [&lt;loadUpdateData&gt;](https://docs.liquibase.com/change-types/load-update-data.html) for DML Data
Use &lt;loadUpdateData&gt; for file include, e.g. 
```
<changeSet author="generated" id="t_pub_module.DATA.20210603">
   <preConditions onFail="MARK_RAN">
       <sqlCheck expectedResult="0">select count(*) from T_PUB_MODULE where CODE = 'EASY_CLAIM'</sqlCheck>
   </preConditions>
   <loadUpdateData encoding="UTF-8" file="T_PUB_MODULE.csv" primaryKey="MODULE_ID" quotchar="&quot;" separator="," relativeToChangelogFile="true" tableName="T_PUB_MODULE">
       <column header="MODULE_ID" name="MODULE_ID" type="NUMERIC"/>
       <column header="CODE" name="CODE" type="STRING"/>
       <column header="NAME" name="NAME" type="STRING"/>
       <column header="DESCRIPTION" name="DESCRIPTION" type="STRING"/>
       <column header="INSERT_BY" name="INSERT_BY" type="NUMERIC"/>
       <column header="UPDATE_BY" name="UPDATE_BY" type="NUMERIC"/>
       <column header="RECORD_USAGE" name="RECORD_USAGE" type="NUMERIC"/>
       <column header="INSERT_TIME" name="INSERT_TIME" type="DATE"/>
       <column header="UPDATE_TIME" name="UPDATE_TIME" type="DATE"/>
       <column header="DYNAMIC_FIELDS" name="DYNAMIC_FIELDS" type="STRING"/>
   </loadUpdateData>
</changeSet>

```

### Use [&lt;logicalFilePath&gt;](https://docs.liquibase.com/concepts/changelogs/attributes/logicalfilepath.html) If You Need Move Scripts
If you're adapting appframework in an existing project, and some liquibase scripts have been executed in the runtime environment, in order to avoid errors in the case of filename check in liquibase, please use &lt;logicalFilePath&gt; attribute to specify the old file path in databaseChangeLog xml. 
Check the filename in existing runtime database table databasechangelog, e.g. `freshman-bff-ap-db/initdata/1.0.0/init_db.xml`, and it is now `resource/dbscript/1.0.0/init_db.xml`.
```
<databaseChangeLog logicalFilePath="freshman-bff-ap-db/initdata/1.0.0/init_db.xml"
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd"/>
```
Through the above setting, liquibase knows that the filename of all the changesets in this script is `freshman-bff-ap-db/initdata/1.0.0/init_db.xml`.

## Links
- Know more about Spring Data JPA: [Spring Data JPA - Reference Documentation](https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#reference).
- Know more about Liquibase: [Getting Started with Liquibase](https://www.liquibase.org/get-started#how).
- About more change types: [Liquibase Change Types](https://docs.liquibase.com/change-types/home.html?__hstc=128893969.b4519e28db07c3ae59a83151a8579c37.1655690029739.1656299452730.1657677847687.14&__hssc=128893969.3.1657677847687&__hsfp=604157576&_ga=2.254632202.368072700.1657677834-1797974098.1655348598).
- About best practice: [Liquibase Best Practices](https://docs.liquibase.com/concepts/bestpractices.html).
