Step by Step: Java + gradle + mybatis + postgresql + log4j

# Step by Step: Java + gradle + mybatis + postgresql + log4j

## Objectives

- 個人的メモ
- 素の OS 状態から簡単なテスト実施までを一連にまとめる
- 初学者にも再現可能なように

## References

以下のサイトを参考にさせていただきました。ありがとうございます。

- https://qiita.com/navekazu/items/af886fa200be8a683132
- https://qiita.com/tatesuke/items/6e30dc8891d0857f240e
- https://qiita.com/opengl-8080/items/a9b5d4038f19d4813ee6

## What I want to do?

1. 必要なパッケージをインストール
2. Java でコードを書く
3. gradle でビルド
4. mybatis で DB に接続して
5. Debug ログで何やってるか SQL レベルで確認する

## System

|  | Version | Note |
| --- | --- | --- | 
| OS | CentOS 7.4.1708 | AWS AMI: CentOS 7 (x86_64) - with Updates HVM (t2.micro) |
| Java | OpenJDK1.8 | base & devel |
| gradle | 4.6 | ビルドツール https://ja.wikipedia.org/wiki/Gradle https://gradle.org/releases/ |
| mybatis | 3.4.6 | DBアクセス https://ja.wikipedia.org/wiki/MyBatis |
| log4j | 1.2.17-16 | ロガー https://ja.wikipedia.org/wiki/Log4j |
| PostgreSQL | 10.2 | DB Server |

## Direction

### 1. OS Login & Create User for operation, etc

```shell-session:package_udpate
$ sudo yum udpate
$ cat /etc/redhat-release
```
```shell-session:add_user_etc
$ sudo useradd -m hirofumi
$ sudo passwd hirofumi
$ sudo visudo
$ su - hirofumi
$ sudo yum -y install vim git wget unzip
```

### 2. Install Java

```shell-session
$ sudo yum -y install \
java-1.8.0-openjdk.x86_64 \
java-1.8.0-openjdk-devel.x86_64
```
### 3. Install Log4j & junit

```shell-session
$ sudo yum -y install log4j junit

$ rpm -qa | grep -Ei "log4j|junit"
junit-4.11-8.el7.noarch
log4j-1.2.17-16.el7_4.noarch
```

### 4. Install PostgreSQL & Create DB, etc

- https://wiki.postgresql.org/wiki/YUM_Installation

```shell-session
$ sudo vim /etc/yum.repos.d/CentOS-Base.repo
```
```:append_2_lines_into_base_and_updates
exclude=postgresql*
```
```shell-session
$ yum clean all

$ sudo yum -y install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

$ yum list postgresql10*

$ sudo yum -y install postgresql10-server
```
```shell-session:check_data_dir
$ sudo ls /var/lib/pgsql/10/data
```
```shell-session:initialize_db
$ sudo /usr/pgsql-10/bin/postgresql-10-setup initdb
```
```shell-session:edit_pg_hba.conf
$ sudo vim /var/lib/pgsql/10/data/pg_hba.conf

## all comment out and add following lines

local all postgres peer
host all postgres 127.0.0.1/32 ident

local testdb dbuser password
host testdb dbuser 127.0.0.1/32 password
```
```shell-session:enable_and_start_service
$ sudo systemctl enable postgresql-10.service
$ sudo systemctl start postgresql-10.service
$ sudo systemctl status postgresql-10.service
```
```shell-session:change_user
$ sudo su -
# su - postgres
-bash-4.2$ /usr/pgsql-10/bin/pg_config --version
PostgreSQL 10.2

-bash-4.2$ vim ~/.bash_profile

export PATH=$PATH:/usr/pgsql-10/bin

-bash-4.2$ . ~/.bash_profile
-bash-4.2$ which pg_config
/usr/pgsql-10/bin/pg_config
```
```shell-session:create_db
-bash-4.2$ /usr/pgsql-10/bin/createdb testdb

if you want to delete db.

-bash-4.2$ /usr/pgsql-10/bin/dropdb testdb
```
```sql:connect_db
-bash-4.2$ /usr/pgsql-10/bin/psql testdb
psql (10.2)
Type "help" for help.

testdb=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

testdb=#
```
```sql:SQL_for_create_table
CREATE TABLE customers (
  customer_id SERIAL PRIMARY KEY, 
  first_name VARCHAR(100) NOT NULL, 
  last_name VARCHAR(100) NOT NULL
);

CREATE SEQUENCE customers_sequence
  start 1
  increment 1;

INSERT INTO customers
  (customer_id, first_name, last_name)
VALUES
  (nextval('customers_sequence'), 'Hirofumi', 'Hida');

SELECT * FROM customers;
```
```sql:create_table
testdb=# CREATE TABLE customers (
testdb(#   customer_id SERIAL PRIMARY KEY,
testdb(#   first_name VARCHAR(100) NOT NULL,
testdb(#   last_name VARCHAR(100) NOT NULL
testdb(# );
CREATE TABLE
testdb=#
testdb=#
testdb=# CREATE SEQUENCE customers_sequence
testdb-#   start 1
testdb-#   increment 1;
CREATE SEQUENCE
testdb=#
testdb=#
testdb=# INSERT INTO customers
testdb-#   (customer_id, first_name, last_name)
testdb-# VALUES
testdb-#   (nextval('customers_sequence'), 'Hirofumi', 'Hida');
INSERT 0 1
testdb=#
testdb=#
testdb=# SELECT * FROM customers;
 customer_id | first_name | last_name
-------------+------------+-----------
           1 | Hirofumi   | Hida
(1 row)

testdb=#
testdb-# \q
```
```sql:add_dbuser
-bash-4.2$ /usr/pgsql-10/bin/psql
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# CREATE ROLE dbuser WITH LOGIN PASSWORD 'dbuser';
CREATE ROLE

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 dbuser    |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dbuser;
GRANT
postgres=# \q

-bash-4.2$ exit
logout
```

### 5. Install gradle

- https://gradle.org/install/#manually

```shell-session:install_gradle
# wget https://services.gradle.org/distributions/gradle-4.6-bin.zip
# mkdir /opt/gradle
# unzip -d /opt/gradle gradle-4.6-bin.zip
# ls /opt/gradle/gradle-4.6

# su - hirofumi

$ export GRADLE_HOME=/opt/gradle/gradle-4.6
$ export PATH=$PATH:$GRADLE_HOME/bin

$ gradle -version

------------------------------------------------------------
Gradle 4.6
------------------------------------------------------------

Build time:   2018-02-28 13:36:36 UTC
Revision:     8fa6ce7945b640e6168488e4417f9bb96e4ab46c

Groovy:       2.4.12
Ant:          Apache Ant(TM) version 1.9.9 compiled on February 2 2017
JVM:          1.8.0_161 (Oracle Corporation 25.161-b14)
OS:           Linux 3.10.0-693.11.6.el7.x86_64 amd64
```
```shell-session:init_gradle_project
$ mkdir mybatis_test01
$ cd mybatis_test01
$ gradle init --type java-library
Starting a Gradle Daemon (subsequent builds will be faster)

BUILD SUCCESSFUL in 6s
2 actionable tasks: 2 executed

$ ls -la
total 20
drwxrwxr-x. 5 hirofumi hirofumi  123 Mar 20 11:07 .
drwx------. 5 hirofumi hirofumi  148 Mar 20 11:06 ..
-rw-rw-r--. 1 hirofumi hirofumi 1031 Mar 20 11:07 build.gradle
drwxrwxr-x. 3 hirofumi hirofumi   21 Mar 20 11:07 gradle
drwxrwxr-x. 4 hirofumi hirofumi   43 Mar 20 11:07 .gradle
-rwxrwxr-x. 1 hirofumi hirofumi 5296 Mar 20 11:07 gradlew
-rw-rw-r--. 1 hirofumi hirofumi 2260 Mar 20 11:07 gradlew.bat
-rw-rw-r--. 1 hirofumi hirofumi  361 Mar 20 11:07 settings.gradle
drwxrwxr-x. 4 hirofumi hirofumi   30 Mar 20 11:07 src
```

### 6. Preparations for gradle

```
$ mkdir libs
$ cd libs
$ wget https://jdbc.postgresql.org/download/postgresql-42.2.2.jar
$ cd ../
```

```shell-session
$ cat build.gradle
/*
 * This file was generated by the Gradle 'init' task.
 *
 * This generated file contains a sample Java Library project to get you started.
 * For more details take a look at the Java Libraries chapter in the Gradle
 * user guide available at https://docs.gradle.org/4.6/userguide/java_library_plugin.html
 */

// plugins {
    // Apply the java-library plugin to add support for Java Library
//    id 'java-library'
//}

apply plugin: 'java'
apply plugin: 'application'
mainClassName = 'sample.mybatis.Main'

dependencies {
    // This dependency is exported to consumers, that is to say found on their compile classpath.
    // api 'org.apache.commons:commons-math3:3.6.1'

    // This dependency is used internally, and not exposed to consumers on their own compile classpath.
    // implementation 'com.google.guava:guava:23.0'

    // Use JUnit test framework
    testImplementation 'junit:junit:4.11'
    compile 'org.mybatis:mybatis:3.4.6'
    compile 'log4j:log4j:1.2.17'
    compile name: 'postgresql-42.2.2'
}

// In this section you declare where to find the dependencies of your project
repositories {
    // Use jcenter for resolving your dependencies.
    // You can declare any Maven/Ivy/file repository here.
    jcenter()
    flatDir {
        dirs 'libs'
    }
}

jar {
    manifest {
        attributes 'Main-Class': 'sample.mybatis.Main'
    }
}
```
```java
$ cat src/main/java/Library.java
/*
 * This Java source file was generated by the Gradle 'init' task.
 */
public class Library {
    public boolean someLibraryMethod() {
        return true;
    }
}
```

### 7. Configure Log4j

```
$ mkdir src/main/resources/
```
```
$ cat src/main/resources/log4j.properties
# Global logging configuration
log4j.rootLogger=debug, stdout
# MyBatis logging configuration...
log4j.logger.org.mybatis.example.BlogMapper=TRACE
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=[%p] %d{yyyy/MM/dd HH:mm:ss} %m%n
```

### 8. Configure Mybatis

```xml
$ cat src/main/resources/mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
  <settings>
    <setting name="logImpl" value="LOG4J"/>
  </settings>
  <environments default="sample_id">
    <environment id="sample_id">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="org.postgresql.Driver"/>
        <property name="url" value="jdbc:postgresql://localhost/testdb"/>
        <property name="username" value="dbuser"/>
        <property name="password" value="dbuser"/>
      </dataSource>
    </environment>
  </environments>
  <mappers>
    <mapper resource="sample_mapper.xml"/>
  </mappers>
</configuration>
```
```xml
$ cat src/main/resources/sample_mapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="sample.mybatis">
  <select id="selectTest" resultType="map">
    -- test aaa
    select last_name,first_name
    -- test bbb テスト
    from customers
    -- test ccc
  </select>
</mapper>
```

### 9. Write a code

```java
$ cat src/main/java/Main.java
package sample.mybatis;
import java.io.InputStream;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.log4j.Logger;
import org.apache.ibatis.logging.LogFactory;
public class Main {
    static Logger log = Logger.getLogger(Main.class.getName());
    public static void main(String[] args) throws Exception {
        log.info("Start");
        log.debug("Debug info");
        try (InputStream in = Main.class.getResourceAsStream("/mybatis-config.xml")) {
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
            try (SqlSession session = factory.openSession()) {
                List<Map<String, Object>> result = session.selectList("sample.mybatis.selectTest");
                result.forEach(row -> {
                    System.out.println("---------------");
                    row.forEach((columnName, value) -> {
                        System.out.printf("columnName=%s, value=%s%n", columnName, value);
                    });
                });
            }
        }
    }
}
```
```
$ cp -irpv src/main src/test
```

### 10. Build

```shell-session
$ gradle build

BUILD SUCCESSFUL in 2s
9 actionable tasks: 9 executed
```

### 11. Run

```shell-session
$ gradle run

> Task :run
[INFO] 2018/03/21 19:58:44 Start
[DEBUG] 2018/03/21 19:58:44 Debug info
[DEBUG] 2018/03/21 19:58:44 Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
[DEBUG] 2018/03/21 19:58:44 Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
[DEBUG] 2018/03/21 19:58:44 PooledDataSource forcefully closed/removed all connections.
[DEBUG] 2018/03/21 19:58:44 PooledDataSource forcefully closed/removed all connections.
[DEBUG] 2018/03/21 19:58:44 PooledDataSource forcefully closed/removed all connections.
[DEBUG] 2018/03/21 19:58:44 PooledDataSource forcefully closed/removed all connections.
[DEBUG] 2018/03/21 19:58:44 Opening JDBC Connection
[DEBUG] 2018/03/21 19:58:45 Created connection 267760927.
[DEBUG] 2018/03/21 19:58:45 Setting autocommit to false on JDBC Connection [org.postgresql.jdbc.PgConnection@ff5b51f]
[DEBUG] 2018/03/21 19:58:45 ==>  Preparing: -- test aaa select last_name,first_name -- test bbb テスト from customers -- test ccc
[DEBUG] 2018/03/21 19:58:45 ==> Parameters:
[DEBUG] 2018/03/21 19:58:45 <==      Total: 1
---------------
columnName=last_name, value=Hida
columnName=first_name, value=Hirofumi
[DEBUG] 2018/03/21 19:58:45 Resetting autocommit to true on JDBC Connection [org.postgresql.jdbc.PgConnection@ff5b51f]
[DEBUG] 2018/03/21 19:58:45 Closing JDBC Connection [org.postgresql.jdbc.PgConnection@ff5b51f]
[DEBUG] 2018/03/21 19:58:45 Returned connection 267760927 to pool.


BUILD SUCCESSFUL in 2s
3 actionable tasks: 1 executed, 2 up-to-date
```