Step by Step: Java + gradle + mybatis + postgresql + log4j
Objectives
--Personal notes --A series from the basic OS state to the simple test execution --To be reproducible even for beginners
References
I referred to the following site. Thank you very much.
What I want to do?
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 | Build tool https://ja.wikipedia.org/wiki/Gradle https://gradle.org/releases/ |
| mybatis | 3.4.6 | DB access https://ja.wikipedia.org/wiki/MyBatis |
| log4j | 1.2.17-16 | Logger https://ja.wikipedia.org/wiki/Log4j |
| PostgreSQL | 10.2 | DB Server |
Direction
package_udpate
$ sudo yum udpate
$ cat /etc/redhat-release
add_user_etc
$ sudo useradd -m hirofumi
$ sudo passwd hirofumi
$ sudo visudo
$ su - hirofumi
$ sudo yum -y install vim git wget unzip
$ sudo yum -y install \
java-1.8.0-openjdk.x86_64 \
java-1.8.0-openjdk-devel.x86_64
$ 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
$ sudo vim /etc/yum.repos.d/CentOS-Base.repo
append_2_lines_into_base_and_updates
exclude=postgresql*
$ 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
check_data_dir
$ sudo ls /var/lib/pgsql/10/data
initialize_db
$ sudo /usr/pgsql-10/bin/postgresql-10-setup initdb
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
enable_and_start_service
$ sudo systemctl enable postgresql-10.service
$ sudo systemctl start postgresql-10.service
$ sudo systemctl status postgresql-10.service
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
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
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_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;
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
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
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
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
$ mkdir libs
$ cd libs
$ wget https://jdbc.postgresql.org/download/postgresql-42.2.2.jar
$ cd ../
$ 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'
}
}
$ 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;
}
}
$ 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
$ 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>
$ 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 test
from customers
-- test ccc
</select>
</mapper>
$ 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
$ gradle build
BUILD SUCCESSFUL in 2s
9 actionable tasks: 9 executed
$ 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 test 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