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

• O / R mapping is to map (associate) "object" and "relational database".

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

• The above assumes that Table A, Table B, and Table C are not all linked to foreign keys. The following configuration file points to dbflute / XXX / dfprop / additionalforeignkey.dfprop.

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/