[JAVA] How to join a table without using DBFlute and sql

Introduction

Nice to meet you, everyone. My name is Wu and I am in charge of this series.

There are three typical O / R mapping tools (iBATIS, Torque, Hibernate), This time, I would like to introduce DBFlute as an O / R mapping.

Why do you want to avoid using SQL outside of DBFlute as much as possible? The following two reasons: (1) If SQL statements are written in various places in the source code, you must always be aware of problems such as omissions and mistakes in development. (2) O / R mapping reduces complicated work related to database operation at the time of implementation and solves "mismatch".

Challenge background

There was a DBFlute homepage, but the knowledge is scattered and in order to use the work in the field, it is necessary to organize the configuration file (additionalforeignkey.dfprop) and how to write the Java source.

Table join method explanation

This article uses DBFlute-1.0.5N and JAVA7.

Basic mechanism of DBFlute

Rough mechanism of DBFlute O / R mapping (1) Automatic generation in DBFlute DBFlute is an O / R mapping that only works with auto-generated classes. (2) JAVA implementation Behavior Controls the processing of all DB access ConditionBean Build search conditions Please refer to Implementation Manual for details.

If there is no foreign key linked between two or more tables, and you want to join them for business purposes, use the following method.

Example:

Usage table

TableA TableB TableC
A_column_1 B_column_1 C_column_1
A_column_2 B_column_1 C_column_1

  1. Expected execution result SQL (TableA and TableB join): SELECT * FROM TableA A LEFT JOIN TableB B ON A.A_column_1 = B.B_column_1

setting file: ; FK_TableA_TO_TableB_BY_ID = map:{ ; localTableName = TableA ; foreignTableName = TableB ; localColumnName = A_column_1 ; foreignColumnName = B_column_1 ; fixedSuffix = ByID }

JAVA: //TableA情報の取得

TableACB cb = tableABhv.newMyConditionBean();


cb.setupSelect_TableBById();```  

---------------------------------------

 2, Expected execution result SQL (TableA / TableB and TableB / TableC join):
SELECT * 
FROM TableA A
INNER JOIN TableB B
ON A.A_column_1 = B.B_column_1
INNER JOIN TableC C
ON B.B_column_1 = C.C_column_1

 setting file:
 ; FK_TableB_TO_TableC_BY_ID = map:{
   ; localTableName  = TableB  ; foreignTableName  = TableC
   ; localColumnName = B_column_1 ; foreignColumnName = C_column_1
   ; fixedSuffix = ById
 }

 ; FK_TableA_TO_TableB_BY_ID = map:{
   ; localTableName  = TableA  ; foreignTableName  = TableB
   ; localColumnName = A_column_1 ; foreignColumnName = B_column_1
   ; fixedSuffix = ById
 }

JAVA:
 //TableA情報の取得

#### **`TableACB cb = tableABhv.newMyConditionBean();`**
```newMyConditionBean();

cb.setupSelect_TableBById();
cb.setupSelect_TableBById().join();
cb.setupSelect_TableBById().withTableCBId();
cb.setupSelect_TableBById().withTableCBId().join();```

---------------------------------------
 3, Expected execution result SQL (TableA / TableB and TableB / TableC and TableA / TableC join):

SELECT * 
FROM TableA A
LEFT JOIN TableB B
ON A.A_column_1 = B.B_column_1
LEFT JOIN TableC C
ON B.B_column_1 = C.C_column_1
AND A.A_column_2 = C.C_column_2

 setting file:
 ; FK_TableA_TO_TableB_BY_ID = map:{
   ; localTableName  = TableA  ; foreignTableName  = TableB
   ; localColumnName = A_column_1 ; foreignColumnName = B_column_1
   ; fixedSuffix = ById
 }

 ; FK_TableA_TO_TableC_BY_ID = map:{
   ; localTableName  = TableA  ; foreignTableName  = TableC
   ; localColumnName = A_column_2 ; foreignColumnName = C_column_2
   ; fixedCondition = `$$foreignAlias$$.C_column_1 = $$over($localTable.tableBById)$$.B_column_1`
   ; fixedSuffix = ById
 }

JAVA:
 //TableA情報の取得

#### **`TableACB cb = tableABhv.newMyConditionBean();`**
```newMyConditionBean();

cb.setupSelect_TableBById();
cb.setupSelect_TableCById();```

---------------------------------------
 4, Execution result SQL (correlated subquery in fixedCondition):

SELECT * 
FROM TableA A
INNER JOIN TableB B
ON A.A_column_1 = B.B_column_1
INNER JOIN TableC C
ON B.B_column_1 = C.C_column_1
AND A.A_column_2 = C.C_column_2
INNER JOIN 
	(SELECT TC.C_column_1 
	  		,MAX(CAST(TC.C_column_2 AS INT)) AS VER 
 			FROM TableC TC
			GROUP BY
			TC.C_column_1
			TC.C_column_2
		) C_0 
ON C_0.C_column_1 = C.C_column_1


 setting file:

 ; FK_TableA_TO_TableB_BY_ID = map:{
   ; localTableName  = TableA  ; foreignTableName  = TableB
   ; localColumnName = A_column_1 ; foreignColumnName = B_column_1
   ; fixedSuffix = ById
 }

 ; FK_TableA_TO_TableC_BY_ID = map:{
   ; localTableName  = TableA  ; foreignTableName  = TableC
   ; localColumnName = A_column_2 ; foreignColumnName = C_column_2
   ; fixedCondition = `$$foreignAlias$$.C_column_1 = $$over($localTable.tableBById)$$.B_column_1`
	INNER JOIN 
		(SELECT TC.C_column_1 
	  		,MAX(CAST(TC.C_column_2 AS INT)) AS VER 
 			FROM TableC TC
			GROUP BY
			TC.C_column_1
			TC.C_column_2
		) C_0 
	ON C_0.C_column_1 = `$$foreignAlias$$.C_column_1`
   ; fixedSuffix = ById
 }


JAVA:

 //TableA情報の取得

#### **`TableACB cb = tableABhv.newMyConditionBean();`**
```newMyConditionBean();

cb.setupSelect_TableBById();
cb.setupSelect_TableBById().join();
cb.setupSelect_TableCById();
cb.setupSelect_TableCById().join();```

---------------------------------------
#### Configuration file terminology

 I think that the Macs shown above have the following meanings and will be used after understanding them.

 `$$ foreignAlias $$`: Elias of referenced table
 `$$ localAlias $$`: Elias of the referencing table
 `$$ over ([table name]. [Relation name]) $$`: Elias of the table of another relation
 `$$ over ($ localTable. [Relation name]) $$`: Foreign table of Local table
 `$$ over ($ foreignTable. [Relation name]) $$`: Foreign table of Foreign table

---------------------------------------
# Summary
 If you combine the BDflute configuration file and the JAVA source in pairs, you can join all the tables without using external sql.
 This explanation is a method to deal with joining multiple tables that are often used in the field, but in reality I think that it is a lot of detailed know-how, but next time I will explain the know-how to be implemented on the JAVA side.

---------------------------------------
# Reference material
 ~ Site-oriented O / R mapper ~
http://dbflute.seasar.org/

 What is additionalForeignKeyMap?
http://dbflute.seasar.org/ja/manual/reference/dfprop/additionalforeignkey/










Recommended Posts

How to join a table without using DBFlute and sql
How to convert A to a and a to A using AND and OR in Java
How to add columns to a table
How to execute a contract using web3j
How to sort a List using Comparator
[Rails] How to create a table, add a column, and change the column type
How to output Excel and PDF using Excella
Realize a decision table without using conditional branching
How to execute and mock methods using JUnit
[Rails] How to create a graph using lazy_high_charts
How to delete a controller etc. using a command
How to play audio and music using javascript
[Ethereum] How to execute a contract using web3j-Part 2-
How to generate a primary key using @GeneratedValue
When performing a full outer join without using a full outer join
How to clear all data in a particular table
How to update devise user information without a password
How to delete custom Adapter elements using a custom model
How to store Rakuten API data in a table
How to start tomcat local server without using eclipse
How to use Play Framework without using typesafe activator
How to make a cache without thinking too much
[Rails] How to install a decorator using gem draper
[Docker] How to update using a container on Heroku and how to deal with Migrate Error
How to implement a circular profile image in Rails using CarrierWave and R Magick
How to test including images when using ActiveStorage and Faker
How to leave a comment
How to develop separately into Xib files without using Storyboard
How to use rails join
How to set and describe environment variables using Rails zsh
How to install JDK 8 on Windows without using the installer
How to develop and register a Sota app in Java
How to install PHP 7.4 and SQL Server drivers in CentOS 7.7
How to insert a video
How to create a method
How to create and launch a Dockerfile for Payara Micro
How to register as a customer with Square using Tomcat
How to authorize using graphql-ruby
How to control transactions in Spring Boot without using @Transactional
How to get the latest live stream ID for a channel without using the YouTube Data API
How to create a jar file or war file using the jar command
How to make a hinadan for a Spring Boot project using SPRING INITIALIZR
How to create a registration / update function where the table crosses
How to run a mock server on Swagger-ui using stoplight/prism (using AWS/EC2/Docker)
[Rails 6] How to create a dynamic form input screen using cocoon
How to POST JSON in Java-Method using OkHttp3 and method using HttpUrlConnection-
How to load a Spring upload file and view its contents
How to read a file and treat it as standard input
[Rails] How to upload images to AWS S3 using Carrierwave and fog-aws
[Rails] How to upload images to AWS S3 using refile and refile-s3
[Reading impression] "How to learn Rails, how to write a book, and how to teach"
[Swift5] How to communicate from ViewController to Model and pass a value
How to build a little tricky with dynamic SQL query generation
How to make a groundbreaking diamond using Java for statement wwww
How to use StringBurrer and Arrays.toString.
How to use EventBus3 and ThreadMode
How to make a Java container
[Java] How to use join method
How to make a JDBC driver
How to call classes and methods
How to write a ternary operator