Introduction

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 .... ** Apache Derby ** seems to solve such a problem.

What is Apache Derby?

Apache Derby is a lightweight RDB made entirely of Java. It can be easily executed with only file access or memory access, and is very suitable for education and sample application distribution.

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.4
DBFlute Intro 0.3.3
Apache Derby Database Engine and Embedded JDBC Driver 10.14.2.0 ※
DBFlute 1.1.9
MyBatis 3.5.1 （MyBatis-Spring-Boot-Starter 2.0.1）
Spring JDBC 5.1.6

At the time of writing this article, a newer version 10.15 series has been released for Apache Derby, but the Driver class up to 10.14 series (org.apache.derby.jdbc.EmbeddedDriver) has become unusable. , I will use 10.14 series.

About the source code of this article

You can download it from here ([http://dossaribook.starfree.jp/public/qiita/20190430_sample-dbaccess_light.zip]). It can be executed immediately by importing it into eclipse etc. with DB connection possible.

DBFlute engine is excluded.
Download links are subject to change without notice.

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 from Window or Mac from http://mergedoc.osdn.jp/.

1. Apache Derby jar download

Get version 10.14 series files from Maven repository.

2. 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.

Apache Derby
JDBC
MyBatis

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

3. 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 ├─ .gitignore ├─ dbflute-intro.jar ├─ HELP.md ├─ mvnw ├─ mvnw.cmd ├─ pom.xml ├─.mvn └─src

4. 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.

5. 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: Apache Derby --JDBC Driver: Apache Derby jar file downloaded earlier
URL: jdbc:derby:../_derbydata;create=true
Schema: APP　※1
User: APP --Password: Optional (blank is OK)

1 "APP" is a schema prepared by default, and if you want to use any other schema, you will have to create it separately, so specify "APP".

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 ├─ dbflute-intro.jar ├─ dbflute_sample_dbaccess ├─ mydbflute ├─ HELP.md ├─ mvnw ├─ mvnw.cmd ├─ pom.xml ├─.mvn └─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.

6. DDL creation

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

`dbflute_sample_dbaccess/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)
);

7. 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/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/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

8. 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
manage.bat 0

The number of data files for derby has increased directly under the project. The directory name created here is specified by the connection URL entered in the initial setup of DBFlute Intro. sample-dbaccess ├─ .gitignore ├─ _derbydata ├─ dbflute-intro.jar ├─ dbflute_sample_dbaccess ├─ mydbflute ├─ HELP.md ├─ mvnw ├─ mvnw.cmd ├─ pom.xml ├─.mvn └─src

9. 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 5. DBFlute Engine Initial Setup.

10. 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.1.9</version>
    </dependency>
</dependencies>

`src/main/resources/application.yml`


#Common DB connection information
spring:
  datasource:
    url: jdbc:derby:_derbydata
    username: APP
    driverClassName: org.apache.derby.jdbc.EmbeddedDriver

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

Note 1. About user name

The connection string for derby will be jdbc: 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. If an error occurs on eclipse

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

11. DBFlute trial run

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

`SampleDbaccessDBFluteApplication.java`


@SpringBootApplication
public class SampleDbaccessDBFluteApplication {

    @Autowired
    EmployeeBhv employeeBhv;

    public static void main(String[] args) {
        try (ConfigurableApplicationContext ctx = SpringApplication.run(SampleDbaccessDBFluteApplication.class, args)) {
            SampleDbaccessDBFluteApplication app = ctx.getBean(SampleDbaccessDBFluteApplication.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");
    }
}

12. MyBatis trial run

`SampleDbaccessMyBatisApplication.java`


@SpringBootApplication
public class SampleDbaccessMyBatisApplication {

    @Autowired
    EmployeeMapper employeeMapper;

    public static void main(String[] args) {
        try (ConfigurableApplicationContext ctx = SpringApplication.run(SampleDbaccessMyBatisApplication.class, args)) {
            SampleDbaccessMyBatisApplication app = ctx.getBean(SampleDbaccessMyBatisApplication.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
}

13. Spring JDBC trial execution

`SampleDbaccessJDBCApplication.java`


@SpringBootApplication
public class SampleDbaccessJDBCApplication {

    @Autowired
    JdbcTemplate jdbcTemplate;

    public static void main(String[] args) {
        try (ConfigurableApplicationContext ctx = SpringApplication.run(SampleDbaccessJDBCApplication.class, args)) {
            SampleDbaccessJDBCApplication app = ctx.getBean(SampleDbaccessJDBCApplication.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);
    }
}

Bonus DB Flute 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.

in conclusion

By importing to eclipse etc., Distributed in a form that can be executed immediately. The total of source code + Derby database is only 287KB (after compression). Please try the O / R mapper sample immediately and experience it.

[JAVA] DB construction & distribution with Apache Derby + DBFlute Intro