Introduction

This article is a reprint of previously written article, and the DB has been changed from Apache Derby to H2DB. Therefore, there are many duplications, but basically we aim for self-contained articles, not DRY.

When trying to create an application that connects to a database, it is often troublesome in terms of environment construction. Even if you try the O / R mapper for a while, you can install the DB server locally, create a table, and input data. Recently, it seems that it is possible to easily build an environment using container technology such as Docker, but if you want to distribute sample applications for education etc., build the environment as easily as possible and execute it as soon as possible. The threshold will be a little higher.

If possible, I want an environment that is easy to distribute in a small capacity and can be executed immediately ... ** H2DB ** seems to solve such a problem.

What is H2DB?

H2DB is a lightweight RDB made of pure Java. It can be easily executed with only file access or memory access, and is very suitable for education and sample application distribution. In addition, since it also has a server function, it is possible to connect from multiple apps (for example, checking the contents of the table from the console while connecting to the DB from the web app).

What is DBFlute?

It is an RDB development support tool and has the following two functions.

--O / R Mapper --DB management support tool

This article mainly deals with the second DB management support tool. By using this, you can create a table and input data in one batch. In addition, I will write a little sample code as an O / R mapper.

What is DBFlute Intro?

DBFlute has various functions, and there is a dedicated engine to carry them out. Therefore, when using DBFlute for the first time, it is necessary to download the engine and set the environment, but it is a tool to easily prepare such an environment. It is distributed in an executable jar format and can be run immediately in any Java environment (https://github.com/dbflute/dbflute-intro/releases).

O / R mapper to try

After preparing the DB access environment, try using the following 3 types of O / R mappers as a trial.

DBFlute
MyBatis
Spring JDBC

About version

This article uses the following version.

Java 11（AdoptOpenJDK）
Spring Boot 2.1.7
DBFlute Intro 0.3.5
H2DB 1.4.199
MyBatis 3.5.2 （MyBatis-Spring-Boot-Starter 2.1.0）
Spring JDBC 5.1.9

procedure

0. Eclipse download

Since it is a big deal, I will start by preparing the coding environment and execution environment. It doesn't have to be Eclipse, but to save you some trouble, I'll rely on Pleiades All in One here. Download Java Full Edition with jkd for Windows or Mac from http://mergedoc.osdn.jp/.

1. Create a project from Spring Initializer

Create a Spring Boot project from https://start.spring.io/. At that time, add the following to the Spring Boot dependency.

H2 Database
JDBC
MyBatis

Unzip the downloaded file to a suitable location (such as under the workspace if you use eclipse). sample-dbaccess-h2 ├─ .gitignore ├─ .mvn ├─ HELP.md ├─ mvnw ├─ mvnw.cmd ├─ pom.xml └─ src

2. DBFlute Intro Download, Deployment

Get the jar file from github. https://github.com/dbflute/dbflute-intro/releases

Place the downloaded file directly under the Spring project you unzipped earlier. sample-dbaccess-h2 ├─ .gitignore ├─ .mvn ├─ dbflute-intro.jar ├─ HELP.md ├─ mvnw ├─ mvnw.cmd ├─ pom.xml └─ src

3. Run DBFlute Intro

Run the dbflute-intro.jar file. For later, if the java path does not pass, pass it.

set PATH=%PATH%;C:\path\to\eclipse_java\11\bin
java -jar dbflute-intro.jar

When executed, a local server will be launched internally and opened in a browser.

4. DBFlute engine initial setup

Enter the required information and perform the initial setup. After completing the input, click the "Create" button on the screen to execute it.

The following points should be noted when inputting.

--Project Name: Optional

DBMS: H2 Database
URL: jdbc:h2:file:../_h2/h2data
Schema: PUBLIC　※1
User: sa　※1 --Password: Optional (blank is OK)

1 If you use the schema "PUBLIC" and the user "sa", you can connect without any special procedure. I think it is enough for local verification.

Also, since DBFlute is used as an O / R mapper in the sample, click "O / R Mapper settings" on the right side of the screen to make it ready for input, and enter the following.

Language: Java
DI Container: Spring Framework --Generation Package: Any package (DBFlute source code will be generated under the package specified here)

If you click the "Create" button and the execution is successful, the following screen will be displayed. !!

There are more files in the project. sample-dbaccess ├─ .gitignore ├─ .mvn ├─ dbflute-intro.jar ├─ dbflute_sample_dbaccess_h2 ├─ mydbflute ├─ HELP.md ├─ mvnw ├─ mvnw.cmd ├─ pom.xml └─ src

--mydbflute: DBFlute engine. Basically you don't edit here. --dbflute _ * xxx *: This is a folder that contains DBFlute settings for each project. "Xxx" is the "Project Name" entered in the initial setup.

5. DDL creation

Enter the DDL such as the table create statement in replace-schema.sql in the project-specific DBFlute folder "dbflute_sample_dbaccess_h2" created earlier.

`dbflute_sample_dbaccess_h2/playsql/replace-schema.sql`


create table DEPARTMENT (
	DEPARTMENT_ID int not null,
	DEPARTMENT_NAME varchar(100) not null,
	constraint PK_DEPARTMENT primary key(DEPARTMENT_ID),
	constraint FK_DEPARTMENT_1 FOREIGN KEY (DEPARTMENT_ID) references DEPARTMENT(DEPARTMENT_ID)
);

create table EMPLOYEE (
	EMPLOYEE_ID int not null,
	EMPLOYEE_NAME varchar(100) not null,
	DEPARTMENT_ID int not null,
	constraint PK_EMPLOYEE primary key(EMPLOYEE_ID),
	constraint FK_EMPLOYEE_1 FOREIGN KEY (DEPARTMENT_ID) references DEPARTMENT(DEPARTMENT_ID)
);

6. Data creation for registration

This time, we will create registration data in tsv format (data format with tabs as delimiters). There are special rules when registering data, and you need to pay particular attention to directory names and file names. For details, refer to "Data Registration (TSV)".

`dbflute_sample_dbaccess_h2/playsql/data/ut/tsv/UTF-8/01-department.tsv`


DEPARTMENT_ID	DEPARTMENT_NAME
1 department 1
2 department 2
3 department 3

`dbflute_sample_dbaccess_h2/playsql/data/ut/tsv/UTF-8/02-employee.tsv`


EMPLOYEE_ID	EMPLOYEE_NAME	DEPARTMENT_ID
11 Employee 11 1
12 employees 12 1
13 Employees 13 1
21 Employees 21 2
22 Employees 22 2
23 Employees 23 2
31 Employee 31 3
32 employees 32 3
33 Employee 33 3

7. Schema creation & data input

On the DBFlute Intro screen, select the created project and press the Replace Schema button to execute schema creation & data entry.

Alternatively, you can run the manage.bat file. Note that the java path is in place.

cd dbflute_sample_dbaccess_h2
manage.bat 0

The data file for h2 has increased directly under the project. The file name (\ * .mv.db) created here is specified by the connection URL entered in the initial setup of DBFlute Intro. sample-dbaccess-h2 ├─ .gitignore ├─ .mvn ├─ _h2 　├─ h2data.mv.db ├─ dbflute-intro.jar ├─ dbflute_sample_dbaccess_h2 ├─ mydbflute ├─ HELP.md ├─ mvnw ├─ mvnw.cmd ├─ pom.xml └─ src

8. Automatic source code generation

Since the source code for DBFlute could not be automatically generated from the DBFlute Intro screen, execute it from the command line. Run manage.bat with the argument "2: regenerate" (or you can run it with no arguments and enter it later). See Manage Tasks for more information.

cd dbflute_sample_dbaccess
manage.bat 2

If successful, the auto-generated code will be output under the package entered in 4. DBFlute Engine Initial Setup.

9. Add Spring settings

Add settings to use O / R mapper on Spring.

First edit pom.xml. Adjust the Java version to add DBFlute dependencies.

`pom.xml`


<properties>
    <java.version>1.11</java.version>
</properties>
<!--Omission-->
<dependencies>
    <!--Add to various existing dependencies-->
    <dependency>
        <groupId>org.dbflute</groupId>
        <artifactId>dbflute-runtime</artifactId>
        <version>1.2.0</version>
    </dependency>
</dependencies>

`src/main/resources/application.yml`


#Common DB connection information
spring:
  datasource:
    url: jdbc:h2:file:./_h2/h2data
    username: sa
    driverClassName: org.h2.Driver

#Settings for MyBatis. lower_Map the column name of case to the property of camelCase
mybatis:
  configuration:
    mapUnderscoreToCamelCase: true

Note 1. About the connection string

The connection string for h2 is h2: file: file path. Here, for the file path, specify the relative path from the base directory when executing java (when executing from eclipse, directly under the project) or the absolute path.

Note 2. About the file path

If you set ʻurl: jdbc: h2: file: _h2 / h2data instead of ʻurl: jdbc: h2: file: ./_h2 / h2data in the file path setting of h2, the following error will occur at runtime.

Database URL with relative file path from implicit current directory("jdbc:h2:file:_h2/h2data")It is not allowed to specify in. Absolute or relative path instead( ~/name, ./name)Or specify baseDir.
A file path that is implicitly relative to the current working directory is not allowed in the database URL "jdbc:h2:file:_h2/h2data". Use an absolute path, ~/name, ./name, or the baseDir setting instead. [90011-199]

If you use a relative path, it seems that you need to explicitly write the path starting with the current ..

Note 3. If an error occurs on eclipse

Select a project and try "Maven> Update Project".

10. DBFlute trial run

Add the following files and easily start Spring from the command line.

`SampleDbaccessH2Application.java`


@SpringBootApplication
public class SampleDbaccessH2DBFluteApplication {

    @Autowired
    EmployeeBhv employeeBhv;

    public static void main(String[] args) {
        try (ConfigurableApplicationContext ctx = SpringApplication.run(SampleDbaccessH2DBFluteApplication.class, args)) {
            SampleDbaccessH2DBFluteApplication app = ctx.getBean(SampleDbaccessH2DBFluteApplication.class);
            app.run(args);
        }
    }

    private void run(String... args) {
        System.out.println("Start processing");
        employeeBhv.selectList(cb -> {
            cb.setupSelect_Department();
            cb.query().setDepartmentId_Equal(2);
        }).forEach(employee -> {
            System.out.println(
                String.format(
                    "employeeName: %s, departmentName: %s",
                    employee.getEmployeeName(),
                    employee.getDepartment().get().getDepartmentName()));
        });
        //App processing
        System.out.println("Processing Exit");
    }
}

11. MyBatis trial run

`SampleDbaccessH2MyBatisApplication.java`


@SpringBootApplication
public class SampleDbaccessH2MyBatisApplication {

    @Autowired
    EmployeeMapper employeeMapper;

    public static void main(String[] args) {
        try (ConfigurableApplicationContext ctx = SpringApplication.run(SampleDbaccessH2MyBatisApplication.class, args)) {
            SampleDbaccessH2MyBatisApplication app = ctx.getBean(SampleDbaccessH2MyBatisApplication.class);
            app.run(args);
        }
    }

    private void run(String... args) {
        System.out.println("Start processing");
        employeeMapper.findEmployeeList(2).forEach(employee -> {
            if (employee != null) {
                System.out.println(
                    String.format(
                        "employeeName: %s, departmentName: %s",
                        employee.getEmployeeName(),
                        employee.getDepartmentName()));
            } else {
                System.out.println("employee is null");
            }
        });
        //App processing
        System.out.println("Processing Exit");
    }
}

`EmployeeMapper.java`


@Mapper
public interface EmployeeMapper {
    @Select({ "select emp.EMPLOYEE_NAME as EMPLOYEENAME, dept.DEPARTMENT_NAME",
        "from EMPLOYEE emp",
        "inner join DEPARTMENT dept on dept.DEPARTMENT_ID = emp.DEPARTMENT_ID",
        "where emp.DEPARTMENT_ID = #{departmentId}", })
    List<Employee> findEmployeeList(int departmentId);
}

`Employee.java`


public class Employee {
    private String employeeName;
    private String departmentName;
    
    // getter, setter
}

12. Spring JDBC trial execution

`SampleDbaccessH2JDBCApplication.java`


@SpringBootApplication
public class SampleDbaccessH2JDBCApplication {

    @Autowired
    JdbcTemplate jdbcTemplate;

    public static void main(String[] args) {
        try (ConfigurableApplicationContext ctx = SpringApplication.run(SampleDbaccessH2JDBCApplication.class, args)) {
            SampleDbaccessH2JDBCApplication app = ctx.getBean(SampleDbaccessH2JDBCApplication.class);
            app.run(args);
        }
    }

    private void run(String... args) {
        System.out.println("Start processing");
        findEmployeeList(2).forEach(map -> {
            System.out.println(
                String.format(
                    "employeeName: %s, departmentName: %s",
                    map.get("EMPLOYEE_NAME"),
                    map.get("DEPARTMENT_NAME")));
        });
        System.out.println("Processing Exit");
    }

    private List<Map<String, Object>> findEmployeeList(int departmentId) {
        return jdbcTemplate.queryForList(String.join("", new String[] {
            "select emp.EMPLOYEE_NAME, dept.DEPARTMENT_NAME",
            " from EMPLOYEE emp",
            " inner join DEPARTMENT dept on dept.DEPARTMENT_ID = emp.DEPARTMENT_ID",
            " where emp.DEPARTMENT_ID = ?"
        }), departmentId);
    }
}

in conclusion

After all I thought that the article created by copy and paste was easy. When writing the source code, you have to copy and paste and aim for DRY ...

Supplement A. DBFlute as a DB management support tool

If there is a table addition change, modify replace-schema.sql and registration data, execute manage.bat, the existing table will be deleted & regenerated, and migration is possible in the development environment. In addition, DBFlute has excellent functions as a migration tool, such as a schema difference comparison tool (Reference: "[20160521 Behind the full renewal of large-scale video distribution service with Java 8]" (https://www.slideshare) .net / SuguruAkiho / 20160521-jjug-cccunext) ". Even if you cannot introduce it as an O / R mapper, please consider introducing it as a DB management support tool.

Supplement B: Start the H2 Database server and enable multiple simultaneous access

If you want to access the DB from multiple applications at the same time, it is not possible to connect with file: ... due to restrictions. Multiple simultaneous access can occur not only in the production environment but also in the local development environment. For example, when you want to update the schema with DBFlute while the Web application is running. To do this, you need to use the server function.

The procedure for starting the server is briefly described below.

Download H2DB

Download All Platforms from Honke Site.

Move required files

Unzip the downloaded zip file and move the required files towards your project. Since only the runtime is required, move the following files from under bin to the directory for h2. --Startup script. Select according to the environment (bin / h2.bat for Windows)

bin/h2.jar

The structure of the project is as follows.

sample-dbaccess-h2 ├─ .gitignore ├─.mvn ├─ _h2 　├─ h2data.mv.db 　├─ h2data.trace.db 　├─ h2.bat 　├─ h2-1.4.199.jar ├─ dbflute-intro.jar ├─ dbflute_sample_dbaccess_h2 ├─ mydbflute ├─ HELP.md ├─ mvnw ├─ mvnw.cmd ├─ pom.xml └─ src

Start script execution

Cd under the same directory as the startup script you moved above and run the startup script. Note that the Java path must be in the same way as running DBFlute Intro. The browser starts up and transitions to the login screen of the h2 console.

Schema & data check on h2 console

You don't need to log in to the console, but here we will try to check the data input from the console. Enter the following information to log in to the console.

When you log in, you can confirm that the table has been created and data has been entered.

Change application settings

Change the connection string as follows: url: jdbc:h2:tcp://localhost/./h2data

`src/main/resources/application.yml`


spring:
  datasource:
    url: jdbc:h2:tcp://localhost/./h2data
#Omission

`dbflute_sample_dbaccess_h2/dfprop/databaseInfoMap.dfprop`


#Omission
    ; url: jdbc:h2:tcp://localhost/./h2data
#Omission

Note 1. About specifying the path

For the same reasons already mentioned, you can't do ʻurl: jdbc: h2: tcp: // localhost / h2data. Must be explicitly relative path ./h2data instead of h2data`.

Note 2. About server settings

Although it has not been verified, it seems that it is possible to set the settings (port etc.) when starting the server. http://www.h2database.com/html/tutorial.html#console_settings

Note 3. About the first schema construction

The file (h2data.mv.db in the above example) is not completed. When I started the server on H2 at the first access and performed Replace Schema from DBFlute, an error occurred.

Database "/path/to/h2/h2data" not found, and IFEXISTS=true, so we cant auto-create it [90146-197]

I got an error message saying that if I add ʻIFEXISTS = true to the connection option, it cannot be created automatically, so I tried setting ʻIFEXISTS = false, but it didn't work.

For the time being, the first time I avoid it by the following method.

--Access with file: instead of tcp:. --Initialize from H2 Management Console

DB environment construction with DBFlute Intro + H2 Database

Introduction

What is H2DB?

What is DBFlute?

What is DBFlute Intro?

O / R mapper to try

About version

procedure

0. Eclipse download

1. Create a project from Spring Initializer

2. DBFlute Intro Download, Deployment

3. Run DBFlute Intro

4. DBFlute engine initial setup

5. DDL creation

dbflute_sample_dbaccess_h2/playsql/replace-schema.sql

6. Data creation for registration

dbflute_sample_dbaccess_h2/playsql/data/ut/tsv/UTF-8/01-department.tsv

dbflute_sample_dbaccess_h2/playsql/data/ut/tsv/UTF-8/02-employee.tsv

7. Schema creation & data input

8. Automatic source code generation

9. Add Spring settings

pom.xml

src/main/resources/application.yml

Note 1. About the connection string

Note 2. About the file path

Note 3. If an error occurs on eclipse

10. DBFlute trial run

SampleDbaccessH2Application.java

11. MyBatis trial run

SampleDbaccessH2MyBatisApplication.java

EmployeeMapper.java

Employee.java

12. Spring JDBC trial execution

SampleDbaccessH2JDBCApplication.java

in conclusion

Supplement A. DBFlute as a DB management support tool

Supplement B: Start the H2 Database server and enable multiple simultaneous access

Download H2DB

Move required files

Start script execution

Schema & data check on h2 console

Change application settings

src/main/resources/application.yml

dbflute_sample_dbaccess_h2/dfprop/databaseInfoMap.dfprop

Note 1. About specifying the path

Note 2. About server settings

Note 3. About the first schema construction

`dbflute_sample_dbaccess_h2/playsql/replace-schema.sql`

`dbflute_sample_dbaccess_h2/playsql/data/ut/tsv/UTF-8/01-department.tsv`

`dbflute_sample_dbaccess_h2/playsql/data/ut/tsv/UTF-8/02-employee.tsv`

`pom.xml`

`src/main/resources/application.yml`

`SampleDbaccessH2Application.java`

`SampleDbaccessH2MyBatisApplication.java`

`EmployeeMapper.java`

`Employee.java`

`SampleDbaccessH2JDBCApplication.java`

`src/main/resources/application.yml`

`dbflute_sample_dbaccess_h2/dfprop/databaseInfoMap.dfprop`