Introduction to the EBean ORM Framework -2. Field encryption, update logging, and history

This article continues with the advanced features of annotations introduced in the previous article, Introduction to the Ebean ORM Framework -1. Enhanced Annotations

I. @encrypted field encryption

The @encrypted annotation is used to encrypt and decrypt database fields to protect important data, as shown in the phone field

1. Use database encryption

public class User extends BaseModel {

    @DbComment("the name")
    private String name;

    @Encrypted
    private String phone;

    private Integer loginCount = 0;
}

Copy the code

Simply set @encrypted to encrypt and decrypt specified fields, which is completely transparent to the application

2. Use application encryption

public class User extends BaseModel {

    @DbComment("the name")
    private String name;

    @Encrypted
    private String phone;

    private Integer loginCount = 0;

    @Encrypted(dbEncryption=false)
    String description;
}

Copy the code

(1) Annotation setting

@Encrypted(dbEncryption=false)

(2) custom encryption program

public class BasicEncryptKeyManager implements EncryptKeyManager {

    @Override
    public EncryptKey getEncryptKey(String tableName, String columnName) {
        return newBasicEncryptKey(tableName, columnName); }}public class BasicEncryptKey implements EncryptKey {

    private String tableName;
    private String columnName;
    private String key = "0123456";

    public BasicEncryptKey(String tableName, String columnName){
        this.tableName = tableName;
        this.columnName = columnName;
    }

    @Override
    public String getStringValue(a) {
        returntableName.concat(columnName).concat(key); }}Copy the code

(3) Configure annotations

ebean:
  encryptKeyManager: fun.barryhome.ebean.encrypt.BasicEncryptKeyManager

Copy the code

Set in application.yaml

3. Performance analysis

10:29:06.167 [main] DEBUG io.ebean.sql -txn [1001] select t0. Id, t0. CONVERT(AES_DECRYPT(t0.phone,?) USING UTF8) _e_t0_phone, t0.login_count, t0.description, t0.version, t0.when_created, t0.when_modified from user t0 where CONVERT(AES_DECRYPT(t0.phone,?) USING UTF8) = ? and t0.description = ?; --bind(****,130000000000,****)Copy the code

As can be seen from the SQL execution statements in the logs, the SQL function conversion is performed on the encrypted fields. Therefore, use the fields with caution if they are used as query conditions, which may have a large performance cost

2. @Changelog Update logs

The @Changelog annotation is used to record data update logs in logs for log query

1. Set annotations

@ChangeLog
public class User extends BaseModel {... }Copy the code

2. Log configuration

This must be logback.xml

      
<configuration scan="true">

    <appender name="CHANGE_LOG" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <File>log/changeLog.log</File>
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <FileNamePattern>log/changeLog.log.%d{yyyy-MM-dd}</FileNamePattern>
            <MaxHistory>90</MaxHistory>
        </rollingPolicy>
        <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
            <pattern>%d{HH:mm:ss.SSS} %msg%n</pattern>
        </encoder>
    </appender>

    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <pattern>%d{HH:mm:ss.SSS} [%thread] %highlight(%-5level) %cyan(%logger{80}) - %msg%n</pattern>
        </encoder>
    </appender>

    <logger name="io.ebean.ChangeLog" level="INFO" additivity="false">
        <appender-ref ref="CHANGE_LOG"/>
        <appender-ref ref="STDOUT"/>
    </logger>

    <! -- Default console and file-->
    <root level="INFO" >
        <appender-ref ref="CHANGE_LOG" />
        <appender-ref ref="STDOUT" />
    </root>
</configuration>
Copy the code

3. Log result

15:01:07.975 [ebean-db1] INFO io.ebean.changelog - {" ts ": 1622962867966," change ":" U ", "type" : "User", "id" : "1", "data" : {" version ": 24," whenModified ":" the 2021-06-06 T07:01:07. 936 z "," description":"Sun Jun 06 15:01:07 CST 2021-06-06t06:02:49.253z ","description":"Sun Jun 06 14:02:49CST 2021"}Copy the code

@History Historical records

Using the @history annotation, you can take before and after snapshots of update and DEL operations that record all data in a database table

Set 1.

@History
@Table(name = "customer")
public class Customer extends BaseModel {

    public static final CustomerFinder find = new CustomerFinder();

    private String name;

    @HistoryExclude
    private Integer age;

}
Copy the code

Set Entity to ** @history ** and exclude ** for fields that do not need to be recorded

2. Data structure changes

The database structure changes a bit after you set the annotations

• The CUSTOMER table adds two fields, “sys_period_start” and “sys_period_end”, and two triggers, “customer_HISTORy_del” and “customer_HISTORy_upd”
• Add “customer_History” historical data table
• Add the “customer_WITH_history” view

3. Data changes

1) New record changes

@Test
public void create(a) {
  Customer customer = Customer.builder()
    .name("abc")
    .age(0)
    .build();
  customer.save();
}
Copy the code

The sys_period_start field in the source table is updated to the last updated time, which is 8 hours less than the current time due to time zone

2) Modify record changes

@Test
public void update(a) {
  Customer customer = DB.find(Customer.class, 1L);
  customer.setName(UUID.randomUUID().toString());
  customer.setAge(customer.getAge() + 1);
  customer.update();
}
Copy the code

The original field in the source table is updated, and the sys_PERIOd_start field is updated to the last updated time

A row is added to the history table to record the original data

• Sys_period_start indicates the new time
• Sys_period_end indicates the time after the update
• Two times form the retention time of this record

The start time of the last record is the end time of the previous record, which is also the retention time of the current record data

4. Query historical records

@Test
public void query(a) {
  Timestamp date = Timestamp.valueOf("The 2021-06-07 04:20:00");
  Customer customer = Customer.find.query().asOf(date).findOne();
  System.err.println(customer);
}
Copy the code

13:58:44.485 [main] DEBUG io.ebean.SQL - txn[1001] select t0.id, t0.name, t0.age, t0.version, t0.when_created, t0.when_modified from customer_with_history t0 where (t0.sys_period_start < = ? and (t0.sys_period_end is null or t0.sys_period_end >?). );- the bind (asOf the 2021-06-07 06:20:00. 0,)
Copy the code

• The value of date can be any time in history. A unique record can be queried based on the previous time rule. If the date is earlier than the new time, nothing is displayed
• Customer_with_history is an automatically created view

5. Compare historical data versions

@Test
public void queryList(a) {
    List<Version<Customer>> customerVersions = Customer.find.query()
            .where().idEq(1L)
            .findVersions();

    for(Version<Customer> customerVersion : customerVersions) { Customer bean = customerVersion.getBean(); Map<String, ValuePair> diff = customerVersion.getDiff(); Timestamp effectiveStart = customerVersion.getStart(); Timestamp effectiveEnd = customerVersion.getEnd(); System.err.println(diff); }}Copy the code

{name = a6d6ca0e - c5 efd c266-4-95-9 a47e94706d2, 711 d365c - 9 AD - be6a a40-44-535879 c81c12, age = 4, null, version = 5, 4, WhenModified = 2021-06-07 T05:49:06. 570 z, the 2021-06-07 T04:29:48. 570 z} {name = 711 d365c - 9 a40-44 AD - c81c12 be6a - 535879, 3 b1da201 - edfde4e d94 - ba86 e600-4-89679, version = 4, 3, WhenModified = 2021-06-07 T04:29:48. 570 z, the 2021-06-07 T04: throne. 179 z} {name = 3 b1da201 - edfde4e d94 - ba86 e600-4-89679, 520874 da - 816 - c - 482 - f - a3b9 - d94be47477ba, version = 3, 2, WhenModified = 2021-06-07 T04: throne. 179 z, the 2021-06-07 T04:19:45. 068 z} {name = 520874 da - 816 - c - 482 - f - a3b9 - d94be47477ba, ABC, Version = 2, 1, whenModified = 2021-06-07 T04:19:45. 068 z, the 2021-06-07 T04:18:58. 203 z}Copy the code

Each output shows how the two values change

6. Precautions

• Transactionality ensures that updates are consistent
• History is implemented through database triggers, so modifying the database directly can also produce History data
• Compared with @Changelog, @History brings extra storage costs and performance costs to the database
• For table structure changes, the existing triggers and history tables are not automatically updated, and structural synchronization can cause some trouble, which can be resolved using db migration provided by eBeans

Fourth, review

Ebeans also have many advanced features that JPA does not have, such as drafts, composite queries, multi-data support, multi-tenancy, and more, which are expected to be updated.

The code in this article due to the length of reasons have some omission is not complete logic, if interested please Fork source code gitee.com/hypier/barr…