[JAVA] DB environment construction with DBFlute Intro + H2 Database
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
Recommended Posts