albert's blog

2018/06/30

2018/06/16

[Java 8] Examples for LocalDate

package test.albert.datetime;

import java.time.LocalDate;
import java.time.Month;
import java.time.format.DateTimeFormatter;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;

@Slf4j
public class TestDateTime {

    private static final DateTimeFormatter FROMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd");

    public static void main(String[] args) {
        TestDateTime test = new TestDateTime();
        DateRange today = test.getToday();
        log.debug("Get today: {} ", today.toString());

        DateRange yesterday = test.getYesterday();
        log.debug("Get yesterday: {} ", yesterday.toString());

        DateRange lastWeek = test.getLastWeek();
        log.debug("Get last week: {} ", lastWeek.toString());

        DateRange withinOneMonth = test.getWithinOneMonth();
        log.debug("Get within one month: {} ", withinOneMonth.toString());

        DateRange lastMonth = test.getLastMonth();
        log.debug("Get last month: {} ", lastMonth.toString());

        DateRange thisYear = test.getThisYear();
        log.debug("Get this year: {} ", thisYear.toString());

        DateRange lastYear = test.getLastYear();
        log.debug("Get last year: {} ", lastYear.toString());
    }

    /**
     * 計算起訖日期: 今天
     * 
     * @return start/end date
     */
    public DateRange getToday() {
        LocalDate now = LocalDate.now();
        DateRange dateRange = new DateRange(formatDate(now), formatDate(now));
        return dateRange;
    }

    /**
     * 計算起訖日期: 今天減去一天
     * 
     * @return start/end date
     */
    public DateRange getYesterday() {
        LocalDate now = LocalDate.now();
        LocalDate yesterday = now.minusDays(1);

        DateRange dateRange = new DateRange(formatDate(yesterday), formatDate(yesterday));
        return dateRange;
    }

    /**
     * 計算起訖日期: 今天減去六天到今天
     * 
     * @return start/end date
     */
    public DateRange getLastWeek() {
        LocalDate now = LocalDate.now();
        LocalDate lastWeek = now.minusDays(6);

        DateRange dateRange = new DateRange(formatDate(lastWeek), formatDate(now));
        return dateRange;
    }

    /**
     * 計算起訖日期: 今天減去一個月到今天
     * 
     * @return start/end date
     */
    public DateRange getWithinOneMonth() {
        LocalDate now = LocalDate.now();
        LocalDate lastMonth = now.minusMonths(1);

        DateRange dateRange = new DateRange(formatDate(lastMonth), formatDate(now));
        return dateRange;
    }

    /**
     * 計算起訖日期: 上個月的第一天到上個月的最後一天
     * 
     * @return start/end date
     */
    public DateRange getLastMonth() {
        LocalDate now = LocalDate.now();
        LocalDate lastMonth = now.minusMonths(1);

        DateRange dateRange = new DateRange(formatDate(lastMonth.withDayOfMonth(1)),
                formatDate(lastMonth.withDayOfMonth(lastMonth.lengthOfMonth())));
        return dateRange;
    }

    /**
     * 計算起訖日期: 今年度的第一天到今天
     * 
     * @return start/end date
     */
    public DateRange getThisYear() {
        LocalDate now = LocalDate.now();
        LocalDate from = LocalDate.of(now.getYear(), Month.JANUARY, 1);

        DateRange dateRange = new DateRange(formatDate(from), formatDate(now));
        return dateRange;
    }

    /**
     * 計算起訖日期: 去年度的第一天到去年度的最後一天
     * 
     * @return start/end date
     */
    public DateRange getLastYear() {
        LocalDate now = LocalDate.now();

        LocalDate from = LocalDate.of(now.getYear() - 1, Month.JANUARY, 1);
        LocalDate to = LocalDate.of(now.getYear() - 1, Month.DECEMBER, 31);

        DateRange dateRange = new DateRange(formatDate(from), formatDate(to));
        return dateRange;
    }

    private String formatDate(LocalDate date) {
        return date.format(FROMATTER);
    }

    @Data
    @AllArgsConstructor
    private static class DateRange {
        private String from;
        private String to;
    }

}

Console

2018-05-09 11:27:31.661 - DEBUG- t.a.datetime.TestDateTime - Get today: TestDateTime.DateRange(from=2018-05-09, to=2018-05-09)  
2018-05-09 11:27:31.664 - DEBUG- t.a.datetime.TestDateTime - Get yesterday: TestDateTime.DateRange(from=2018-05-08, to=2018-05-08)  
2018-05-09 11:27:31.664 - DEBUG- t.a.datetime.TestDateTime - Get last week: TestDateTime.DateRange(from=2018-05-03, to=2018-05-09)  
2018-05-09 11:27:31.664 - DEBUG- t.a.datetime.TestDateTime - Get within one month: TestDateTime.DateRange(from=2018-04-09, to=2018-05-09)  
2018-05-09 11:27:31.665 - DEBUG- t.a.datetime.TestDateTime - Get last month: TestDateTime.DateRange(from=2018-04-01, to=2018-04-30)  
2018-05-09 11:27:31.665 - DEBUG- t.a.datetime.TestDateTime - Get this year: TestDateTime.DateRange(from=2018-01-01, to=2018-05-09)  
2018-05-09 11:27:31.665 - DEBUG- t.a.datetime.TestDateTime - Get last year: TestDateTime.DateRange(from=2017-01-01, to=2017-12-31)

2018/06/15

[Neo4j] Cypher Example

在 graph database 中，擁有以下 User 與 Movie 的關係：

以下有數個查詢的例子：

// 找出 John Johnson 與其朋友共同看過的電影
match (u:User)-[:IS_FRIEND_OF]->()-[:HAS_SEEN]->(m:Movie)
where u.name="John Johnson" and not((u)-[:HAS_SEEN]->(m))
return m as movie;

// 找出 John Johnson 的朋友看過的，但是 John Johnson 沒看過的電影
match (u:User)-[:IS_FRIEND_OF]->()-[:HAS_SEEN]->(m:Movie)
where u.name="John Johnson" and (u)-[:HAS_SEEN]->(m)
return m as movie;

// 找出大家看過的電影，剔除重複的電影
match (u:User)-[:HAS_SEEN]->(m:Movie)
return distinct m as movie

// 找出 yearOfBirth 比 John Johnson 大的朋友
match (u1:User)-[:IS_FRIEND_OF]-(friend:User)
where u1.name="John Johnson" and  friend.yearOfBirth > u1.yearOfBirth
return friend

// 找出 John Johnson 的朋友中，使用 gmail mailbox 的人
match (u1:User)-[:IS_FRIEND_OF]-(friend:User)
where u1.name="John Johnson" and friend.email =~ ".*@gmail.com"
return friend

// 找出每個人有幾個朋友 (數量以降冪排序)
match (u:User)-[:IS_FRIEND_OF]-()
return u as user, count(*) 
order by count(*) desc

// 找出 John Johnson 與其他節點的關係，並統計數量
match (u:User)-[rel]-()
where u.name="John Johnson"
with u.name as name, type(rel) as rel, count(*) as count
return name, rel, count

2018/06/14

[Neo4j] Importing data using spreadsheets

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

分析與執行步驟如下：

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

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

這七個人，就分別是 graph database 中的七個節點，分別給予 id 與 label ，Excel 內容如下：

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

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

在 Excel 中，關係表示如下：

透過 Excel 內建的 CONCATENATE 語法，產生 create nodes / relationship 的 Cypher 語法

建立 nodes 的 Excel formula：

=CONCATENATE("create(n",A2, ":", ,C2, " {id:", A2, ", name:'",B2, "'}", ")")

建立 relationships 的 Excel formula：

=CONCATENATE("create(n",E2,")-[:",G2,"]->(n",H2,")")

依據上述 nodes 與 relationship ，產生的 Cypher 語法如下：

create(n1:男生 {id:1, name:'哆啦A夢'})
create(n2:男生 {id:2, name:'大雄'})
create(n3:女生 {id:3, name:'靜香'})
create(n4:男生 {id:4, name:'小夫'})
create(n5:男生 {id:5, name:'胖虎'})
create(n6:女生 {id:6, name:'小叮鈴'})
create(n7:女生 {id:7, name:'玉子'})
create(n8:男生 {id:8, name:'伸助'})

create(n2)-[:PET_OF]->(n1)
create(n2)-[:SPOUSE_OF]->(n3)
create(n2)-[:FRIEND_OF]->(n4)
create(n2)-[:FRIEND_OF]->(n5)
create(n4)-[:FRIEND_OF]->(n5)
create(n1)-[:SISTER_OF]->(n6)
create(n2)-[:MOTHER_OF]->(n7)
create(n2)-[:FATHER_OF]->(n8)
create(n8)-[:SPOUSE_OF]->(n7)

將上述語法貼到 Neo4j Browser 並執行

產生的 graph 如下：

2018/06/13

[PostgreSQL] How to update data from a sub-query

Problem
I have two tables, project and intent, in my database.

I import data into intent table, but found out I mistype some data in intent.value column. All data in intent.value should start with qa_.

How to iron out this problem using update statement?

How-To
Here has an example to fix this problem:

  update intent
  set value = source.new_value
  from(
      select id as intent_id, value, 'qa_' || value as new_value, project_id as proj_id
      from intent 
      where project_id= (select id from project where identifier='test')
      and substring(value, 1, 3) <> 'qa_' 
  ) as source
  where id = source.intent_id and project_id = source.proj_id

2018/06/12

[jackson] How to define ordering when serializing object properties to JSON ?

Problem
How to define ordering when serializing object properties to JSON ?

Employ class

  package test.albert.jackson;
  
  import lombok.Builder;
  import lombok.Data;
  
  @Data
  @Builder
  public class Employee {
  
      private Integer id;
  
      private String name;
  
      private String email;
  
      private String phone;
  
      private Address address;
  
  }

Address class

  package test.albert.jackson;
  
  import lombok.Builder;
  import lombok.Data;
  
  @Data
  @Builder
  public class Address {
  
      private String streetAddress;
      private String city;
      private String zipCode;
  
  }

Using ObjectMapper to serialize object properties to JSON

  package test.albert.jackson;
  
  import java.io.IOException;
  
  import com.fasterxml.jackson.databind.ObjectMapper;
  
  import lombok.extern.slf4j.Slf4j;
  
  @Slf4j
  public class JacksonTest {
  
      public static void main(String[] args) throws IOException {
          Address address = Address.builder().zipCode("110").city("台北市").streetAddress("信義區信義路五段7號").build();
          Employee albert = Employee.builder().id(1).name("Albert").email("albert@gmail.com").phone("0900123456")
                  .address(address).build();
          
          ObjectMapper mapper = new ObjectMapper();
          
          // convert object to JSON
          String json = mapper.writeValueAsString(albert);
          log.debug(json);
      }
  
  }

How-to
You can make good use of @JsonPropertyOrder to define the ordering, for example

  package test.albert.jackson;
  
  import com.fasterxml.jackson.annotation.JsonPropertyOrder;
  
  import lombok.Builder;
  import lombok.Data;
  
  @Data
  @Builder
  @JsonPropertyOrder({ "id", "name", "address", "phone", "email" })
  public class Employee {
  
      private Integer id;
  
      private String name;
  
      private String email;
  
      private String phone;
  
      private Address address;
  
  }

2018/06/11

[JavaScript] How to get selected value from a drop-down list that allows multiple selections ?

Problem
Assume I have a drop-down list that allows multiple selections.

   <select class="form-control" multiple id="importIntentList" 
           name="importIntentList" style="height: 100%;" size="20">
      <option value="1">A</option>
      <option value="2">B</option>
      <option value="3">C</option>
      <option value="4">D</option>
   </select>

I try to get selected value from the drop-down list, how to do it?

How-To
Here has code snippet to get values from the multi-selected drop-down list and push values into array:

   var intentSelectedOptions = [];
   var items = document.getElementById("importIntentList");
   for (var i = 0; i < items.options.length; i++) {
       if (items.options[i].selected == true) {
           intentSelectedOptions.push(items.options[i].value);
       }
   }

2018/06/10

[Bootbox] How to keep the modal dialog open until the input is valid?

Problem
I utilize Bootbox to open a dialog, it has one dropdown list for end user to select. And click Import button after select dropdown list.

The code snippet looks like:

bootbox.dialog({
    title: '匯入專案',
    buttons: {
        importProject: {
            label: '匯入',
            className: 'btn-primary',
            callback: function() {
                var projectSelectedVal = document.getElementById("projectList").value;
                var importOptionVal = document.querySelector('input[name="importOption"]:checked').value;
                if (projectSelectedVal == '') {
                    bootbox.alert('<font color=red><b>請選擇要匯入的專案</b></font>');
                } else {
                    // do something
                }
            }
        },
        cancel: {
            label: '取消'
        }
    }
});

If end user do not select required attribute, I will show warning message to end user. But the Bootbox dialog will close it automatically, how do I keep the dialog open?

How-to
Here has updated code:

bootbox.dialog({
    title: '匯入專案',
    buttons: {
        importProject: {
            label: '匯入',
            className: 'btn-primary',
            callback: function() {
                var projectSelectedVal = document.getElementById("projectList").value;
                var importOptionVal = document.querySelector('input[name="importOption"]:checked').value;
                if (projectSelectedVal == '') {
                    bootbox.alert('<font color=red><b>請選擇要匯入的專案</b></font>');
                    return false;
                } else {
                    // do something
                }
            }
        },
        cancel: {
            label: '取消'
        }
    }
});