albert's blog: August 2018

2018/08/31

[Travel] 201808 貓空 (Maokong)

2018/08/15

[Neo4j] Neo4j offline backup and import database

Scenario

Screenshot for 1.1 and 1.2

Screenshot for 1.3

Screenshot for 2.1, 2.2, and 2.3

Screenshot for 2.4

Reference
[1] https://neo4j.com/docs/operations-manual/current/tools/dump-load/

2018/08/14

[Neo4j] Import data using Neo4j-shell-tools

假設我想要在 neo4j 建立起下圖的關係

由關係圖可以看出會有七個節點，包含

哆啦A夢
大雄
靜香
小夫
胖虎
小叮鈴
玉子 (大雄的媽媽)
伸助 (大雄的爸爸)

這七個人 (nodes) 之間的有九個關係：

大雄的寵物是哆啦A夢
大雄的配偶是靜香
大雄的朋友是小夫
大雄的朋友是胖虎
小夫的朋友是胖虎
哆啦A夢的妹妹是小叮鈴
大雄的爸爸是伸助
大雄的媽媽是玉子
伸助的配偶是玉子

分析與執行步驟如下：

步驟如下：
(1) Download neo4j-shell-tools_3.0.1.zip from https://github.com/jexp/neo4j-shell-tools

(2) Unzip neo4j-shell-tools_3.0.1.zip and copy jar files into neo4j-community-3.3.3\lib

(3) Prepare nodes csv files with UTF-8 encoding in neo4j-community-3.3.3\import directory

(4) Prepare relationship csv files with UTF-8 encoding in neo4j-community-3.3.3\import directory

(5) Execute import-cypher command via neo4j-shell to create nodes

import-cypher -d , -i ./import/nodes.csv -o ./import/nodes_output.txt create(p#{node}:Person {id:{node}, name:{name}, gender:{gender}})

(6) Execute import-cypher command via neo4j-shell to create relationships

import-cypher -d , -i ./import/relationships.csv -o ./import/relationships_output.txt match (from:Person), (to:Person) where from.id={From} and to.id={To} create (from)-[:#{Relation Type}]->(to)

產生的關係圖如下：

2018/08/13

[JSON] How to ignore Inheritance properties with Jackson

How-To
AbstractCommonEntity Superclass:

  package com.xxx.entity;
  
  
  import java.io.Serializable;
  import java.util.Date;
  
  import javax.persistence.Column;
  import javax.persistence.MappedSuperclass;
  
  import org.codehaus.jackson.annotate.JsonIgnoreProperties;
  import org.hibernate.annotations.GenerationTime;
  import org.hibernate.annotations.GeneratorType;
  import org.springframework.data.annotation.LastModifiedDate;
  
  import com.xxx.LoggedUserGenerator;
  
  import lombok.Data;
  
  @Data
  @MappedSuperclass
  public abstract class AbstractCommonEntity implements Serializable {
  
      private static final long serialVersionUID = 1L;
  
      @GeneratorType(type = LoggedUserGenerator.class, when = GenerationTime.ALWAYS)
      @Column(name = "LAST_MODIFIED_BY")
      private String lastModifiedBy;
  
      @LastModifiedDate
      @Column(name = "LAST_MODIFIED_DATETIME")
      private Date lastModifiedDatetime;
  
  }

Action subclass:

  package com.xxx.entity;
  
  import java.io.Serializable;
  
  import javax.persistence.Column;
  import javax.persistence.Entity;
  import javax.persistence.EntityListeners;
  import javax.persistence.GeneratedValue;
  import javax.persistence.GenerationType;
  import javax.persistence.Id;
  import javax.persistence.SequenceGenerator;
  import javax.persistence.Table;
  
  import org.springframework.data.jpa.domain.support.AuditingEntityListener;
  
  import lombok.Data;
  import lombok.EqualsAndHashCode;
  
  @Entity
  @Table(name = "ACTION")
  @Data
  @EntityListeners(AuditingEntityListener.class)
  @EqualsAndHashCode(of = "id", callSuper = false)
  public class Action extends AbstractCommonEntity implements Serializable {
  
      private static final long serialVersionUID = 1L;
      
      @Id
      @SequenceGenerator(name = "ACTION_ID_SEQ", sequenceName = "ACTION_ID_SEQ", allocationSize = 1)
      @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ACTION_ID_SEQ")
      @Column(name = "ID")
      private Integer id;
      
      @Column(name = "NAME")
      private String name;
      
      @Column(name = "DEPEND_SLOTS")
      private String dependSlots;
  
      @Column(name = "PROJECT_ID")
      private Integer projectId;
  
  }

If I would like to genenrate JSON from Action subclass and ignore properties from its superclass, how to do it?

How-To
Simply add @JsonIgnoreProperties in superclass:

  package com.xxx.entity;
  
  
  import java.io.Serializable;
  import java.util.Date;
  
  import javax.persistence.Column;
  import javax.persistence.MappedSuperclass;
  
  import org.codehaus.jackson.annotate.JsonIgnoreProperties;
  import org.hibernate.annotations.GenerationTime;
  import org.hibernate.annotations.GeneratorType;
  import org.springframework.data.annotation.LastModifiedDate;
  
  import com.xxx.LoggedUserGenerator;
  
  import lombok.Data;
  
  @Data
  @MappedSuperclass
  @JsonIgnoreProperties({ "lastModifiedBy", "lastModifiedDatetime" })
  public abstract class AbstractCommonEntity implements Serializable {
  
      private static final long serialVersionUID = 1L;
  
      @GeneratorType(type = LoggedUserGenerator.class, when = GenerationTime.ALWAYS)
      @Column(name = "LAST_MODIFIED_BY")
      private String lastModifiedBy;
  
      @LastModifiedDate
      @Column(name = "LAST_MODIFIED_DATETIME")
      private Date lastModifiedDatetime;
  
  }

2018/08/12

[PostgreSQL] How to retrieve a value which separated by comma

Problem
I retrieve 3 records via the following SQL statement:

  select id,  domain_projects 
  from project
  where domain_classifier = true

The value in domain_projects column is separated by comma, if I would like to pick up the value which contains 26, how to do it?

How-To
Here has SQL statement:

  select id,  domain_projects 
  from project
  where domain_classifier = true 
        and '26' = any(string_to_array(domain_projects, ','))

Reference
[1] https://www.postgresql.org/docs/9.1/static/functions-array.html
[2] https://www.postgresql.org/docs/9.1/static/functions-comparisons.html

2018/08/11

[JPA] Entity Inheritance in JPA

Problem
I have three tables which has two identical column (i.e. LAST_MODIFIED_BY and LAST_MODIFIED_DATETIME).

How do define entities via entity inheritance in JPA?

How-To
There are two steps to implement entity inheritance:
[Step 1] Extract the two columns into an abstract class with @MappedSuperclass

  package test.entity;
  
  import java.io.Serializable;
  import java.util.Date;
  
  import javax.persistence.Column;
  import javax.persistence.MappedSuperclass;
  
  import org.springframework.data.annotation.LastModifiedBy;
  import org.springframework.data.annotation.LastModifiedDate;
  
  import lombok.Data;
  
  @Data
  @MappedSuperclass
  public abstract class CommonEntity implements Serializable {
  
      private static final long serialVersionUID = 1L;
  
      @LastModifiedBy
      @Column(name = "LAST_MODIFIED_BY")
      private String lastModifiedBy;
  
      @LastModifiedDate
      @Column(name = "LAST_MODIFIED_DATETIME")
      private Date lastModifiedDatetime;
  
  }

[Step 2] Extend the abstract class from the three entities:

  package test.entity;
  
  import java.io.Serializable;
  
  import javax.persistence.Column;
  import javax.persistence.Entity;
  import javax.persistence.EntityListeners;
  import javax.persistence.GeneratedValue;
  import javax.persistence.GenerationType;
  import javax.persistence.Id;
  import javax.persistence.SequenceGenerator;
  import javax.persistence.Table;
  
  import org.springframework.data.jpa.domain.support.AuditingEntityListener;
  
  import lombok.Data;
  import lombok.EqualsAndHashCode;
  
  @Entity
  @Table(name = "PROJECT")
  @Data
  @EqualsAndHashCode(of = "id", callSuper = false)
  public class Project extends CommonEntity implements Serializable {
  
      private static final long serialVersionUID = 1L;
      
      @Id
      @SequenceGenerator(name = "PROJECT_ID_SEQ", sequenceName = "PROJECT_ID_SEQ", allocationSize = 1)
      @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "PROJECT_ID_SEQ")
      @Column(name = "ID")
      private Integer id;
      
      @Column(name = "IDENTIFIER")
      private String identifier;
      
      @Column(name = "NAME")
      private String name;
      
      @Column(name = "STATUS")
      private String status;
  }

  package test.entity;
  
  import java.io.Serializable;
  
  import javax.persistence.Column;
  import javax.persistence.Entity;
  import javax.persistence.EntityListeners;
  import javax.persistence.Id;
  import javax.persistence.Lob;
  import javax.persistence.Table;
  
  import org.springframework.data.jpa.domain.support.AuditingEntityListener;
  
  import lombok.Data;
  import lombok.EqualsAndHashCode;
  
  @Entity
  @Table(name = "MODEL")
  @Data
  @EqualsAndHashCode(of = "id", callSuper = false)
  public class Model extends CommonEntity implements Serializable {
  
      private static final long serialVersionUID = 1L;
      
      @Id
      @Column(name = "ID")
      private String id;
      
      @Column(name = "PROJECT_ID")
      private Integer projectId;
      
      @Lob
      @Column(name = "STATISTICS_JSON")
      private String statisticsJson;
      
  }

  package com.cht.ai.bot.entity;
  
  import java.io.Serializable;
  
  import javax.persistence.Column;
  import javax.persistence.Entity;
  import javax.persistence.EntityListeners;
  import javax.persistence.GeneratedValue;
  import javax.persistence.GenerationType;
  import javax.persistence.Id;
  import javax.persistence.SequenceGenerator;
  import javax.persistence.Table;
  
  import org.springframework.data.jpa.domain.support.AuditingEntityListener;
  
  import lombok.Data;
  import lombok.EqualsAndHashCode;
  
  @Entity
  @Table(name = "SLOT")
  @Data
  @EqualsAndHashCode(of = "id", callSuper = false)
  public class Slot extends CommonEntity implements Serializable {
  
      private static final long serialVersionUID = 1L;
      
      @Id
      @SequenceGenerator(name = "SLOT_ID_SEQ", sequenceName = "SLOT_ID_SEQ", allocationSize = 1)
      @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SLOT_ID_SEQ")
      @Column(name = "ID")
      private Integer id;
      
      @Column(name = "NAME")
      private String name;
      
      @Column(name = "TYPE")
      private String type;
      
  }

2018/08/10

[Win10] 如何取消定期強制更改密碼？

How-To

Snapshot for Step 1

Snapshot for Step 3

2018/08/09

[PostgreSQL] No operator matches the given name and argument type(s). You might need to add explicit type casts.

Problem
I have a table which name project (the data type of Project.id is integer):

the domain_projects column stored values which separated by comma.

As I try to execute the following SQL statement, it will occur this error

SQLState: 42883, No operator matches the given name and argument type(s). You might need to add explicit type casts.

  select *
  from project
  where id in (select unnest(string_to_array( (select domain_projects from project where id = :projectId), ',')))

How-To
Owing to the return type of unnest function is set of any element, the result should be casted to integer to match project.id.

Hence, the updated SQL statement is as bellows:

  select *
  from project
  where id in (select cast(unnest(string_to_array( (select domain_projects from project where id = :projectId), ',')) as int))

2018/08/08

[PostgreSQL] How to split string into multiple rows?

Problem
I have a table which name project:

the domain_projects column stored values which separated by comma.

I attemp to split domain_projects string into multiple rows:

How do to it?

How-To
Here has SQL statement to fulfill this requirement:

  select cast(unnest(string_to_array( (select domain_projects from project where id = :id), ',')) as int) as project_id

Reference
[1] https://www.postgresql.org/message-id/20100121190706.GA12363@tux
[2] https://www.postgresql.org/docs/9.2/static/functions-array.html

2018/08/07

[Java 8] Sorting With JDK 8

Scenario
I have a Song class, and instantiate five instances as bellows:

How-To
Here has an example to do sorting:

  package test.albert.sorting;
  
  import java.util.ArrayList;
  import java.util.Arrays;
  import java.util.Comparator;
  import java.util.List;
  import java.util.stream.Collectors;
  
  import lombok.extern.slf4j.Slf4j;
  
  @Slf4j
  public class SortingExample {
  
      public static void main(String[] args) {
  
          SortingExample sortingExample = new SortingExample();
  
          List<Song> songs = sortingExample.createSongs();
          sortingExample.sortedByYear(songs);
          sortingExample.sortedByArtistAlbum(songs);
          sortingExample.sortedByYearArtistAlbum(songs);
      }
  
      private List<Song> sortedByYear(List<Song> songs) {
          List<Song> sortedSongs = new ArrayList<Song>();
          sortedSongs = songs.stream().sorted(Comparator.comparingInt(Song::getReleaseYear)).collect(Collectors.toList());
  
          log.debug("sorted by rlease year");
          sortedSongs.forEach(s -> log.debug(s.toString()));
          log.debug("\n");
  
          return sortedSongs;
      }
  
      private List<Song> sortedByArtistAlbum(List<Song> songs) {
          List<Song> sortedSongs = new ArrayList<Song>();
          sortedSongs = songs.stream().sorted(Comparator.comparing(Song::getArtist).thenComparing(Song::getAlbum))
                  .collect(Collectors.toList());
          
          log.debug("sorted by artist, album");
          sortedSongs.forEach(s -> log.debug(s.toString()));
          log.debug("\n");
  
          return sortedSongs;
      }
  
      private List<Song> sortedByYearArtistAlbum(List<Song> songs) {
          List<Song> sortedSongs = new ArrayList<Song>();
          sortedSongs = songs.stream().sorted(Comparator.comparingInt(Song::getReleaseYear).thenComparing(Song::getArtist)
                  .thenComparing(Song::getAlbum)).collect(Collectors.toList());
  
          log.debug("sorted by rlease year, artist, album");
          sortedSongs.forEach(s -> log.debug(s.toString()));
          log.debug("\n");
  
          return sortedSongs;
      }
  
      private List<Song> createSongs() {
          Song jay = new Song("告白氣球", "周杰倫的床邊故事", "周杰倫", 2016);
          Song mayday = new Song("任意門", "自傳", "五月天", 2016);
          Song jamHsiao = new Song("阿飛的小蝴蝶", "王妃", "蕭敬騰", 2009);
          Song hebe = new Song("不醉不會", "渺小", "田馥甄", 2013);
          Song lala = new Song("尋人啟事", "尋人啟事", "徐佳瑩", 2015);
          return Arrays.asList(jay, mayday, jamHsiao, hebe, lala);
      }
  
  }

Sorting result:

  sorted by rlease year 
  Song(title=阿飛的小蝴蝶, album=王妃, artist=蕭敬騰, releaseYear=2009) 
  Song(title=不醉不會, album=渺小, artist=田馥甄, releaseYear=2013) 
  Song(title=尋人啟事, album=尋人啟事, artist=徐佳瑩, releaseYear=2015) 
  Song(title=告白氣球, album=周杰倫的床邊故事, artist=周杰倫, releaseYear=2016) 
  Song(title=任意門, album=自傳, artist=五月天, releaseYear=2016) 
  
 
  sorted by artist, album 
  Song(title=任意門, album=自傳, artist=五月天, releaseYear=2016) 
  Song(title=告白氣球, album=周杰倫的床邊故事, artist=周杰倫, releaseYear=2016) 
  Song(title=尋人啟事, album=尋人啟事, artist=徐佳瑩, releaseYear=2015) 
  Song(title=不醉不會, album=渺小, artist=田馥甄, releaseYear=2013) 
  Song(title=阿飛的小蝴蝶, album=王妃, artist=蕭敬騰, releaseYear=2009) 
  
 
  sorted by rlease year, artist, album 
  Song(title=阿飛的小蝴蝶, album=王妃, artist=蕭敬騰, releaseYear=2009) 
  Song(title=不醉不會, album=渺小, artist=田馥甄, releaseYear=2013) 
  Song(title=尋人啟事, album=尋人啟事, artist=徐佳瑩, releaseYear=2015) 
  Song(title=任意門, album=自傳, artist=五月天, releaseYear=2016) 
  Song(title=告白氣球, album=周杰倫的床邊故事, artist=周杰倫, releaseYear=2016)

Total Pageviews