2014/11 Travel

石門水庫

光之穹頂 Dome of Light

駁二藝術特區

E-DA ROYAL HOTEL

DBM專案環境建置

DBM專案環境建置 from Guo Albert

[Oracle] Customize ADD_DAYS function in ROC era system

Requirement
I am using ROC era system as my date format (ex. 1031120 represents 20141120).
If I would like to create an Oracle function to do add days just like the Oracle build-in function ADD_MONTHS.

Solution
Here is my tailor-made Oracle function.
ADD_DAYS returns the date date plus integer days. 

• The MINGUO_YYYMMDD argument only accept ROC era system as  date format, ex. 1031120.
• The integer argument, DAYS, can be an integer or any value that can be implicitly converted to an integer. 
• The return type is always VARCHAR2 with  ROC era system as  date format.
• If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

create or replace FUNCTION ADD_DAYS ( MINGUO_YYYMMDD IN VARCHAR2 --CHINESE YEAR , DAYS IN NUMBER --5 MEANS ADD 5 DAYS, -3 MEANS SUBSTRACT 3 DAYS ) RETURN VARCHAR2 IS NEW_MINGUO_YYY_MM_DD VARCHAR2(7); --RETURN RESULT BEGIN SELECT NEW_MINGUO_YYY_MM_DD INTO NEW_MINGUO_YYY_MM_DD FROM( SELECT SUBSTR(TO_CHAR(TO_DATE(SUBSTR(MINGUO_YYYMMDD, 0, 3)+1911 || SUBSTR(MINGUO_YYYMMDD, 4, 4), 'YYYYMMDD')+DAYS, 'YYYYMMDD'), 0, 4)-1911 || SUBSTR(TO_CHAR(TO_DATE(SUBSTR(MINGUO_YYYMMDD, 0, 3)+1911 || SUBSTR(MINGUO_YYYMMDD, 4, 4), 'YYYYMMDD')+DAYS, 'YYYYMMDD'), 5, 4) AS NEW_MINGUO_YYY_MM_DD FROM DUAL ); RETURN NEW_MINGUO_YYY_MM_DD; END ADD_DAYS;

Test
Add 5 days

1	SELECT ADD_DAYS('1031120', 5) FROM DUAL;

Subtract 3 days

1	SELECT ADD_DAYS('1030803', -3) FROM DUAL;

[Oracle] How to add serial number for each record

Requirement

Here is my SQL statement to do query:

1 2 3 4 5 6 7 8 9 10

SELECT YYY_MM, ASP, ITEM_CD, ITEM_NM, AMT_A, AMT_B, AMT_C FROM FMS420FA WHERE YYY_MM='10210' ORDER BY ASP

Query result:

If I would like to add serial number for each record as bellowing, how to do it?

Solution
We can make good use of ROW_NUMBER function. ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1.

The SQL statement had been revised as following:

1 2 3 4 5 6 7 8 9 10 11

SELECT ROW_NUMBER() OVER (PARTITION BY YYY_MM ORDER BY ASP) SEQ, YYY_MM, ASP, ITEM_CD, ITEM_NM, AMT_A, AMT_B, AMT_C FROM FMS420FA WHERE YYY_MM='10210'

Reference
[1] https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm

An internal error occurred during: "Initializing Java Tooling". Duplicate field name&signature in class file org/eclipse/jdt/internal/compiler/ast/ASTNode

Problem

I change to a brand new workspace to import project from gitlab.

After imported and try to do maven => update projects, it show "An internal error occurred during: "Initializing Java Tooling". Duplicate field name&signature in class file org/eclipse/jdt/internal/compiler/ast/ASTNode". 

Here has its screenshot:

Root Cause

This error results from lombok's bug. 

Solution

Step1. Shut down your eclipse

Step2. Go to here to download the jar file : http://projectlombok.org/download-edge.html

Step3. follow the instructions in  http://projectlombok.org/download-edge.html

Step4. Startup your eclipse and do again.

Reference
[1] http://stackoverflow.com/questions/21931107/eclipse-wont-switch-workspace

How to Convert Oracle rows to columns

Requirement
Here has my query SQL statement.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SELECT SUBSTR(PDATE, 0, 5) AS YYY_MM, ROUND(SUM(NVL(PAY_T_BILL, 0))/100000000, 0) AS PAY_T_BILL FROM FMS406VD WHERE SUBSTR(PDATE, 0, 5) IN ('10201', '10202', '10203', '10204', '10205', '10206', '10207', '10208', '10209', '10210', '10211', '10212') AND BL_ROC_YM = SUBSTR(PDATE, 0, 5) GROUP BY SUBSTR(PDATE, 0, 5) ORDER BY SUBSTR(PDATE, 0, 5)

Here is the query result

If I would like to convert row into column, how to do it?

Solution

You can make good use of PIVOT to fulfill this requirement. The SQL statement can be re-written into this way

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

SELECT AMT1, AMT2, AMT3, AMT4, AMT5, AMT6, AMT7, AMT8, AMT9, AMT10, AMT11, AMT12 FROM( SELECT SUBSTR(PDATE, 0, 5) AS YYY_MM, ROUND(SUM(NVL(PAY_T_BILL, 0))/100000000, 0) AS PAY_T_BILL FROM FMS406VD WHERE SUBSTR(PDATE, 0, 5) IN ('10201', '10202', '10203', '10204', '10205', '10206', '10207', '10208', '10209', '10210', '10211', '10212' ) AND BL_ROC_YM = SUBSTR(PDATE, 0, 5) GROUP BY SUBSTR(PDATE, 0, 5) ORDER BY SUBSTR(PDATE, 0, 5) ) PIVOT(SUM(PAY_T_BILL) FOR YYY_MM IN ( '10201' AS AMT1, '10202' AS AMT2, '10203' AS AMT3, '10204' AS AMT4, '10205' AS AMT5, '10206' AS AMT6, '10207' AS AMT7, '10208' AS AMT8, '10209' AS AMT9, '10210' AS AMT10, '10211' AS AMT11, '10212' AS AMT12))

The query result is as bellowing:

Reference
[1] http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html

What's the difference between 'commit' and 'commit and push' in Git?

When I want to commit changes into git repository, I see two options to commit. One is "Commit", another one is "Commit and Push".

Here has a clear picture

If you do git commit, it means you only commit changes into your local repository.

If you do git push, it means you will commit changes to remote repository.

Demo

If I click Commit

Remote repository does not know what happened.

 If I click "Commit and Push"

Remote repository received these changes.

Reference
[1] http://stackoverflow.com/questions/2745076/what-are-the-differences-between-git-commit-and-git-push

How to create project in GitLab and import project into Eclipse

Go to GitLab to create my project

1. Click New Project

 2. Fill in your project name, namespace => Create project

Open Git Bash
1. Change directory to my specific dirctory => make dbm directory => Initialized empty Git repository

1:  albert@ALBERT-PC ~  
2:  $ cd d:  
3:  albert@ALBERT-PC /d  
4:  $ cd git  
5:  albert@ALBERT-PC /d/git  
6:  $ mkdir dbm  
7:  albert@ALBERT-PC /d/git  
8:  $ cd dbm  
9:  albert@ALBERT-PC /d/git/dbm  
10:  $ git init  
11:  Initialized empty Git repository in d:/git/dbm/.git/  

2. create README file => add README to track => commit

1:  albert@ALBERT-PC /d/git/dbm (master)  
2:  $ touch README  
3:  albert@ALBERT-PC /d/git/dbm (master)  
4:  $ git add README  
5:  albert@ALBERT-PC /d/git/dbm (master)  
6:  $ git commit -m "first commit"  
7:  [master (root-commit) b45a8a4] first commit  
8:   1 file changed, 0 insertions(+), 0 deletions(-)  
9:   create mode 100644 README  

3. add git remote repository => push to remote

1:  albert@ALBERT-PC /d/git/dbm (master)  
2:  $ git remote add origin http://192.168.31.166/ifmis/dbm.git  
3:  albert@ALBERT-PC /d/git/dbm (master)  
4:  $ git push -u origin master  
5:  Username for 'http://192.168.31.166': albert_kuo  
6:  Password for 'http://albert_kuo@192.168.31.166':  
7:  Counting objects: 3, done.  
8:  Writing objects: 100% (3/3), 206 bytes | 0 bytes/s, done.  
9:  Total 3 (delta 0), reused 0 (delta 0)  
10:  To http://192.168.31.166/ifmis/dbm.git  
11:   * [new branch]   master -> master  
12:  Branch master set up to track remote branch master from origin.  

README file had been commit into git

Assume I create my project skeleton into my git folder

Add all files into track => commit => push to remote

1:  albert@ALBERT-PC /d/git/dbm (master)  
2:  $ git add .  
3:  warning: LF will be replaced by CRLF in dbm-webapp/src/main/jasperreports/README.md.  
4:  The file will have its original line endings in your working directory.  
5:  warning: LF will be replaced by CRLF in dbm-webapp/src/main/webapp/WEB-INF/templates/README.md.  
6:  The file will have its original line endings in your working directory.  
7:  warning: LF will be replaced by CRLF in dbm-webapp/src/main/webapp/scripts/README.md.  
8:  The file will have its original line endings in your working directory.  
9:  warning: LF will be replaced by CRLF in dbm-webapp/src/main/webapp/styles/README.md.  
10:  The file will have its original line endings in your working directory.  
11:  warning: LF will be replaced by CRLF in dbm-webapp/src/test/database/README.md.  
12:  The file will have its original line endings in your working directory.  
13:  warning: LF will be replaced by CRLF in dbm-webapp/src/test/database/test-datasource.xml.template-or  
14:  acle.  
15:  The file will have its original line endings in your working directory.  
16:  warning: LF will be replaced by CRLF in dbm-webapp/src/test/database/test-datasource.xml.template-sq  
17:  lserver.  
18:  The file will have its original line endings in your working directory.  
19:  warning: LF will be replaced by CRLF in pom.xml.  
20:  The file will have its original line endings in your working directory.  
21:  albert@ALBERT-PC /d/git/dbm (master)  
22:  $ git commit -m "create project skeleton"  
23:  [master 4abf50e] create project skeleton  
24:  warning: LF will be replaced by CRLF in dbm-webapp/src/main/jasperreports/README.md.  
25:  The file will have its original line endings in your working directory.  
26:  warning: LF will be replaced by CRLF in dbm-webapp/src/main/webapp/WEB-INF/templates/README.md.  
27:  The file will have its original line endings in your working directory.  
28:  warning: LF will be replaced by CRLF in dbm-webapp/src/main/webapp/scripts/README.md.  
29:  The file will have its original line endings in your working directory.  
30:  warning: LF will be replaced by CRLF in dbm-webapp/src/main/webapp/styles/README.md.  
31:  The file will have its original line endings in your working directory.  
32:  warning: LF will be replaced by CRLF in dbm-webapp/src/test/database/README.md.  
33:  The file will have its original line endings in your working directory.  
34:  warning: LF will be replaced by CRLF in dbm-webapp/src/test/database/test-datasource.xml.template-or  
35:  acle.  
36:  The file will have its original line endings in your working directory.  
37:  warning: LF will be replaced by CRLF in dbm-webapp/src/test/database/test-datasource.xml.template-sq  
38:  lserver.  
39:  The file will have its original line endings in your working directory.  
40:  warning: LF will be replaced by CRLF in pom.xml.  
41:  The file will have its original line endings in your working directory.  
42:   22 files changed, 498 insertions(+)  
43:   create mode 100644 README.md  
44:   create mode 100644 dbm-entity/pom.xml  
45:   create mode 100644 dbm-entity/src/main/java/gov/nta/entity/package-info.java  
46:   create mode 100644 dbm-service/pom.xml  
47:   create mode 100644 dbm-service/src/main/java/gov/nta/dbm/repository/package-info.java  
48:   create mode 100644 dbm-service/src/main/java/gov/nta/dbm/service/package-info.java  
49:   create mode 100644 dbm-webapp/pom.xml  
50:   create mode 100644 dbm-webapp/src/main/jasperreports/README.md  
51:   create mode 100644 dbm-webapp/src/main/java/gov/nta/dbm/web/controller/package-info.java  
52:   create mode 100644 dbm-webapp/src/main/java/gov/nta/dbm/web/dto/package-info.java  
53:   create mode 100644 dbm-webapp/src/main/java/gov/nta/dbm/web/rest/package-info.java  
54:   create mode 100644 dbm-webapp/src/main/resources/gov/nta/dbm/Messages.properties  
55:   create mode 100644 dbm-webapp/src/main/webapp/WEB-INF/jboss-deployment-structure.xml  
56:   create mode 100644 dbm-webapp/src/main/webapp/WEB-INF/jboss-web.xml  
57:   create mode 100644 dbm-webapp/src/main/webapp/WEB-INF/templates/README.md  
58:   create mode 100644 dbm-webapp/src/main/webapp/WEB-INF/web.xml  
59:   create mode 100644 dbm-webapp/src/main/webapp/scripts/README.md  
60:   create mode 100644 dbm-webapp/src/main/webapp/styles/README.md  
61:   create mode 100644 dbm-webapp/src/test/database/README.md  
62:   create mode 100644 dbm-webapp/src/test/database/test-datasource.xml.template-oracle  
63:   create mode 100644 dbm-webapp/src/test/database/test-datasource.xml.template-sqlserver  
64:   create mode 100644 pom.xml  
65:  albert@ALBERT-PC /d/git/dbm (master)  
66:  $ git push -u origin master  
67:  Username for 'http://192.168.31.166': albert_kuo  
68:  Password for 'http://albert_kuo@192.168.31.166':  
69:  Counting objects: 64, done.  
70:  Delta compression using up to 4 threads.  
71:  Compressing objects: 100% (34/34), done.  
72:  Writing objects: 100% (63/63), 10.12 KiB | 0 bytes/s, done.  
73:  Total 63 (delta 2), reused 0 (delta 0)  
74:  To http://192.168.31.166/ifmis/dbm.git  
75:    b45a8a4..4abf50e master -> master  
76:  Branch master set up to track remote branch master from origin.

See..my project skeleton had been commit

Import Git project into Eclipse
1. Copy url

2. File => Import => choose SCM type to "git" => paste url => Finish

3. Well done.

Do not have git connector when I want to Checkout Maven project from SCM

Problem

I would like to import Maven project from Git

File => import

Check out Maven Projects from SCM => Next

I do not have "git" option to select !

Solution
Install git connector

Window => Preferences

Maven => Discovery => Open Catalog

Check "m2e-egit" => Finish

After installation, click Yes to restart eclipse

File => import => Check out Maven Projects from SCM => Next
We have "git" option now!

Unable to ignore resources. Attempted to beginRule: .....

Problem
After I imported Maven project from Git, I always get this error when I do maven-->update projects

Solution
This link, http://www.eclipse.org/forums/index.php/t/493493/ , provides some workaround solutions. 

But you can check the post in the bottom, the author say its plug-in's problem. After I updated it, this error message had gone away.

Failed to launch Eclipse after updated Mac OS Yosemite (10.10)

Problem

After I upgraded Mac OS Yosemite, I found out I cannot launch Eclipse correctly.

Solution
Please go to here to download Java for OS X 2014-001
http://support.apple.com/kb/DL1572?viewlocale=en_US&locale=en_US

After installed Java for OS X 2014-001, my Eclipse can startup correctly.

JUnit Quick Start

Assume I has one utility class, which provide some services, including find employee name, find all employees, etc.

 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
public class MyUtils {  
  /**  
   * Checks for this employee.  
   *   
   * @param name  
   *      the name  
   * @return true if find data; false if find nothing  
   */  
  public static Boolean hasThisEmployee(final String name) {  
     Boolean hasData = Boolean.FALSE;  
     List strs = initData();  
     hasData = findData(name, hasData, strs);  
     return hasData;  
   }  
   /**  
    * Find employee.  
    *   
    * @param name  
    *      the name  
    * @return employee name if find; null if not find  
    */  
   public static String findEmployee(String name) {  
     Boolean hasData = Boolean.FALSE;  
     List strs = initData();  
     hasData = findData(name, hasData, strs);  
     String employee = null;  
     if(hasData) {  
       employee = name;  
     }  
     return employee;  
   }  
   /**  
    * Find all employee.  
    *   
    * @return list of String  
    */  
   public static List findAllEmployee(){  
     return initData();  
   }  
   /**  
    * Find employee.   
    *   
    * @param name  
    * @param hasData  
    * @param strs  
    * @return true if find data; false if find nothing  
    */  
   private static Boolean findData(final String name, Boolean hasData, List strs) {  
     Predicate predicate = new Predicate() {  
       @Override  
       public boolean apply(String input) {  
         return name.equalsIgnoreCase(input);  
       }  
     };  
     Iterator iterator = Collections2.filter(strs, predicate).iterator();  
     while (iterator.hasNext()) {  
       if (!Strings.isNullOrEmpty((String) iterator.next())) {  
         hasData = Boolean.TRUE;  
         break;  
       }  
     }  
     return hasData;  
   }  
   /**  
    *   
    * @return initial data  
    */  
   private static List initData() {  
     List strs = new ArrayList<>();  
     strs.add("Albert");  
     strs.add("Mandy");  
     strs.add("Eric");  
     return strs;  
   }  
 }  

If we would like to do unit test, this unit test class will demonstrate how to use assertSame, assertNotNull, and assertArrayEquals. 
All test methods are annotated with @Test. 

Here is a template for writing JUnit4-style tests:

package com.example.foo;

import static org.junit.Assert.assertEquals;

import org.junit.Test;
import org.junit.Ignore;
import org.junit.runner.RunWith;
import org.junit.runners.JUnit4;

/**
 * Tests for {@link Foo}.
 *
 * @author user@example.com (John Doe)
 */
public class FooTest {

    @Test
    public void thisAlwaysPasses() {

    }

    @Test
    @Ignore
    public void thisIsIgnored() {
    }
}

And we will demonstrate some assert methods, including

• assertArrayEquals(java.lang.Object[] expecteds, java.lang.Object[] actuals) : 
• Asserts that two object arrays are equal.
• assertSame(java.lang.Object expected, java.lang.Object actual) :  
• Asserts that two objects refer to the same object.
• assertNotNull(java.lang.Object object) : 
• Asserts that an object isn't null.
• assertEquals(long expected, long actual) : 
• Asserts that two longs are equal.

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

public class FooTest { /** * Test assert same. * The list includes Albert, Mandy, Eric, and Obama, * it should return True if I use "Albert" to search */ @Test public void testAssertSame() { assertSame(Boolean.TRUE, MyUtils.hasThisEmployee("Albert")); } /** * Test assert not null. * The list includes Albert, Mandy, Eric, and Obama, * it should return not null if I use "Mandy" to search */ @Test public void testAssertNotNull() { assertNotNull(MyUtils.findEmployee("Mandy")); } /** * Test assert array equals. * The list includes Albert, Mandy, Eric, and Obama, * so it should return equal */ @Test public void testAssertArrayEquals() { List strs = new ArrayList<>(); strs.add("Albert"); strs.add("Mandy"); strs.add("Eric"); assertArrayEquals(strs.toArray(), MyUtils.findAllEmployee().toArray()); } }

If all tests are executed as expected, then you will see success result

If some test does not execute as expected, then you will see fail result

Aggregating tests in suites
If you would like to run multiple test classes at the same time, you can do this way

1 2 3 4 5 6 7 8 9 10 11 12

import org.junit.runner.RunWith; import org.junit.runners.Suite; import org.junit.runners.Suite.SuiteClasses; @RunWith(Suite.class) @SuiteClasses({ FooTest.class, FooTest2.class }) public class TestSuite { // the class remains empty, // used only as a holder for the above annotations }

There are two test case class (FooTest.class, FooTest2.class) in the test suite. In the test result, you can see it will run all test cases in FooTest.class and FooTest2.class

Ignore Test
If for some reason, you just want it ignored, you temporarily disable a test. You just need to add @Ignore annotation

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

public class FooTest2 { @Test @Ignore public void testAssertEqual() { assertEquals(36, MySecondUtils.getAge(1978)); } @Test public void testEquals() { assertEquals("Pass", MySecondUtils.getExamResult(88)); } @Test public void testAssertThat() { assertThat(MySecondUtils.getExamResult(88).equals("Pass")); } }

See.... testAssertEqual method had been ignored.

Reference
[1] https://github.com/junit-team/junit/wiki

[閱讀筆記] 理專不想告訴你的穩穩賺投資法

1. 短期股價是無法預測的，只要長期持有一組股票組合，就會獲得該市場的績效。而主動式或被動式基金，就是一個投資組合，績效都會與市場差不多。只要經濟持續成長，績效就會長趨直上
2. 挑選股票型基金時，我只挑經濟持續成長的區域，基金的績效的長期趨勢就會往上；債券型基金的挑選，我則是直接買全球型，避開開收益債，僅此而已
3. 股票型基金的平均報酬較高，波動風險也較高；債券型基金的平均報酬率與波動風險都較低。我的建議是兩種基金都要投資，透過適當的配置，將風險控制在可承擔的範圍
4. 因股票型基金的上漲，是來自於該投資區域的經濟成長，那麼我們投資股票型基金就很簡單了；不需要去判斷個股的基本面、股價會不會上漲，只要選擇經濟確定會成長的區域，長期持有基金就好
5. 如果買的是全球股票型基金，遇到如金融海嘯的大跌，不僅不用停損，反而應該持續定期定額扣款，甚至用多餘資金加碼攤平。不只沒有躺平的風險，等下一波景氣回升，就能開始反彈獲利，時間越長，投資績效也會更穩定
6. 升息時，債券的殖利率會跟著提高，債券價格則會降低；降息時，殖利率會降低，債券價格升高。這種連動關係，就是市場利率會影響債券價格波動的利率風險
7. 複利的精神就是獲利再投入，如果買債券型基金，除非是有固定現金需求的退休人士，最好直接買累計型（不配息），讓基金的配息直接轉入淨值。因為若選擇配息型，投資部位就沒有以複利成長的效果
8. 我建議的投資組合，是採取股票型基金及債券型基金的均衡配置，因為股票型基金屬於攻擊型資產，債券型基金則是防禦型，一攻一守互相搭配
9. 景氣熱絡時，股票型基金淨值上揚，利率也會跟著走高，造成債券型基金下跌。當景氣衰退時，股票型基金淨值下跌，利率也跟著降低，債券型基金就會上漲。所以兩組基金在平均上下的波動，就會自動抵消
10. 股票型基金的平均報酬較高，但是波動大；債券型基金剛好相反，平均報酬較低，但是波動較低。兩者的比重怎麼分配，主要看投資者自己的投資風險程度而定
11. 持有一檔長期趨勢向上的基金，不需要進進出出，徒增成本。只要長期持有不停利，才能達到最高獲利。當然不停利，就要忍受波動的煎熬，但也只有能勇於承擔波動的投資者，才能成為最大的贏家
12. 依據歷史經驗，股票的景氣週期約七年左右，一漲一跌約三年半，而債券的週期通常不超過兩年
13. 當美國公債殖利率位處高點時，代表經濟景氣好，股票型基金通常已經長一段時間，是贖回好時機；同時，高殖利率的時候，則是購買債券型基金的好時機