前言
2023-8-4 16:51:42
以下内容源自《【Mysql】》
仅供学习交流使用
版权
禁止其他平台发布时删除以下此话
本文首次发布于CSDN平台
作者是CSDN@日星月云
博客主页是https://blog.csdn.net/qq_51625007
禁止其他平台发布时删除以上此话
推荐
无
Java实现数据库表中的七种连接
左外连接
/** * 左外连接 * 计算 * SELECT cla1.`id`,cla1.`type`,cla2.`name` * FROM cla1 * LEFT JOIN cla2 * ON cla1.`id`=cla2.`id` * (Where cla2.id IS NULL); * * @param list1 * @param list2 * @param isNull 有没有括号中的IS NULL这条语句 * @param out 输出吗? * @return */ public static List<Cla3> leftJoin(List<Cla3> list1, List<Cla3> list2,boolean isNull,boolean out) { List<Cla3> leftJoin=new ArrayList<>(); //左表遍历 list1.forEach(c1->{ //在右表中有没有找到 AtomicBoolean flag= new AtomicBoolean(false); list2.forEach(c2->{ //找到了 if(c1.id.equals(c2.id)){ //如果cla2.id is null,就不需要添加 if (!isNull) { leftJoin.add(new Cla3(c1.id, c1.type, c2.name)); } flag.set(true); } }); //没有找到添加 右表属性 NULL if(!flag.get()){ leftJoin.add(new Cla3(c1.id,c1.type,"null")); } }); return leftJoin; }
右外连接
/** * 右外连接 * 计算 * SELECT cla2.`id`,cla1.`type`,cla2.`name` * FROM cla1 * RIGHT JOIN cla2 * ON cla1.`id`=cla2.`id` * (WHERE cla1.`id` IS NULL); * * @param list1 * @param list2 * @param isNull 有没有括号中的IS NULL这条语句 * @return */ public static List<Cla3> rightJoin(List<Cla3> list1, List<Cla3> list2,boolean isNull,boolean out) { List<Cla3> rightJoin=new ArrayList<>(); //右表遍历 list2.forEach(c2->{ //在左表中有没有找到 AtomicBoolean flag= new AtomicBoolean(false); list1.forEach(c1->{ //找到了 if(c1.id.equals(c2.id)){ //如果cla1.id is null,就不需要添加 if (!isNull){ rightJoin.add(new Cla3(c2.id, c1.type,c2.name)); } flag.set(true); } }); //没有找到添加 左表属性 NULL if(!flag.get()){ rightJoin.add(new Cla3(c2.id,"null",c2.name)); } }); return rightJoin; }
其他连接
外连接 * 左外+右外 * 右外+左外
内连接 * 左外-左外ISNULL * 右外-右外ISNULL
外连接-内连接
附录
七种连接
SQL测试
CREATE DATABASE cla; USE cla; CREATE TABLE cla1( `id` VARCHAR(10), `type` VARCHAR(10) ); CREATE TABLE cla2( `id` VARCHAR(10), `name` VARCHAR(10) ); INSERT INTO cla1 VALUES('22','cde'); INSERT INTO cla1 VALUES('11','abc'); INSERT INTO cla1 VALUES('44','cdef'); INSERT INTO cla1 VALUES('55','cdefg'); INSERT INTO cla2 VALUES ('11','name1'), ('22','name2'), ('33','name3'), ('44','name4'), ('aa','nameaa'); #leftJoin SELECT cla1.`id`,cla1.`type`,cla2.`name` FROM cla1 LEFT JOIN cla2 ON cla1.`id`=cla2.`id` /* id type name 22 cde name2 11 abc name1 44 cdef name4 55 cdefg \N */ #leftJoin isnull SELECT cla1.`id`,cla1.`type`,cla2.`name` FROM cla1 LEFT JOIN cla2 ON cla1.`id`=cla2.`id` WHERE cla2.`id` IS NULL; /* id type name 55 cdefg \N */ #rightJoin SELECT cla2.`id`,cla1.`type`,cla2.`name` FROM cla1 RIGHT JOIN cla2 ON cla1.`id`=cla2.`id` /* id type name 11 abc name1 22 cde name2 33 \N name3 44 cdef name4 aa \N nameaa */ #rightJoin ISNULL SELECT cla2.`id`,cla1.`type`,cla2.`name` FROM cla1 RIGHT JOIN cla2 ON cla1.`id`=cla2.`id` WHERE cla1.`id` IS NULL; /* id type name 33 \N name3 aa \N nameaa */ #innerJoin leftBefore SELECT cla1.`id`,cla1.`type`,cla2.`name` FROM cla1 INNER JOIN cla2 ON cla1.`id`=cla2.`id` /* id type name 11 abc name1 22 cde name2 44 cdef name4 */ #innerJoin rightBefore SELECT cla2.`id`,cla1.`type`,cla2.`name` FROM cla2 INNER JOIN cla1 ON cla2.`id`=cla1.`id` /* id type name 11 abc name1 22 cde name2 44 cdef name4 */ #outJoin leftBefore #左1+右2 SELECT cla1.`id`,cla1.`type`,cla2.`name` FROM cla1 LEFT JOIN cla2 ON cla1.`id`=cla2.`id` UNION ALL SELECT cla2.`id`,cla1.`type`,cla2.`name` FROM cla1 RIGHT JOIN cla2 ON cla1.`id`=cla2.`id` WHERE cla1.`id` IS NULL; /* id type name 22 cde name2 11 abc name1 44 cdef name4 55 cdefg \N 33 \N name3 aa \N nameaa */ #outJoin rightBefore #右1+左2 SELECT cla2.`id`,cla1.`type`,cla2.`name` FROM cla1 RIGHT JOIN cla2 ON cla1.`id`=cla2.`id` UNION ALL SELECT cla1.`id`,cla1.`type`,cla2.`name` FROM cla1 LEFT JOIN cla2 ON cla1.`id`=cla2.`id` WHERE cla2.`id` IS NULL; /* id type name 11 abc name1 22 cde name2 33 \N name3 44 cdef name4 aa \N nameaa 55 cdefg \N */
Java测试
转换方法
package test.algo; import test.algo.main2.Cla1; import test.algo.main2.Cla2; import test.algo.main2.Cla3; import java.util.ArrayList; import java.util.List; import java.util.concurrent.atomic.AtomicBoolean; public class testJoin { static List<Cla1> claList1 = new ArrayList<>(); static List<Cla2> claList2 = new ArrayList<>(); static List<Cla3> list1 = new ArrayList<>(); static List<Cla3> list2 = new ArrayList<>(); public static void main(String[] args) { test(); init(claList1,claList2); leftJoin(list1, list2,false,true); /* {id: 22, type: cde, name: name2} {id: 11, type: abc, name: name1} {id: 44, type: cdef, name: name4} {id: 55, type: cdefg, name: null} */ leftJoin(list1, list2,true,true);; /* {id: 55, type: cdefg, name: null} */ rightJoin(list1, list2,false,true); /* {id: 11, type: abc, name: name1} {id: 22, type: cde, name: name2} {id: 33, type: null, name: name3} {id: 44, type: cdef, name: name4} {id: aa, type: null, name: nameaa} */ rightJoin(list1, list2,true,true); /* {id: 33, type: null, name: name3} {id: aa, type: null, name: nameaa} */ outJoin(list1, list2,true,true); /* {id: 22, type: cde, name: name2} {id: 11, type: abc, name: name1} {id: 44, type: cdef, name: name4} {id: 55, type: cdefg, name: null} {id: 33, type: null, name: name3} {id: aa, type: null, name: nameaa} */ outJoin(list1, list2,false,true); /* {id: 11, type: abc, name: name1} {id: 22, type: cde, name: name2} {id: 33, type: null, name: name3} {id: 44, type: cdef, name: name4} {id: aa, type: null, name: nameaa} {id: 55, type: cdefg, name: null} */ innerJoin(list1,list2,true,true); /* {id: 22, type: cde, name: name2} {id: 11, type: abc, name: name1} {id: 44, type: cdef, name: name4} */ innerJoin(list1,list2,false,true); /* {id: 11, type: abc, name: name1} {id: 22, type: cde, name: name2} {id: 44, type: cdef, name: name4} */ outJoin_InnerJoin(list1,list2,true,true); /* {id: 55, type: cdefg, name: null} {id: 33, type: null, name: name3} {id: aa, type: null, name: nameaa} */ outJoin_InnerJoin(list1,list2,false,true); /* {id: 33, type: null, name: name3} {id: aa, type: null, name: nameaa} {id: 55, type: cdefg, name: null} */ } /** * 初始两个表中的数据 */ public static void test(){ claList1.add(new Cla1("22", "cde")); claList1.add(new Cla1("11", "abc")); claList1.add(new Cla1("44", "cdef")); claList1.add(new Cla1("55", "cdefg")); claList2.add(new Cla2("11", "name1")); claList2.add(new Cla2("22", "name2")); claList2.add(new Cla2("33", "name3")); claList2.add(new Cla2("44", "name4")); claList2.add(new Cla2("aa", "nameaa")); } /** * 初始结果表中的数据 */ public static void init(List<Cla1> claList1, List<Cla2> claList2){ claList1.forEach(cla1 -> list1.add(new Cla3(cla1.getId(), cla1.getType(), "null"))); claList2.forEach(cla2 -> list2.add(new Cla3(cla2.getId(), "null", cla2.getName()))); } /** *外连接-内连接 * @param list1 * @param list2 * @param leftBefore 左边在前 * @param out 是否输出 * @return */ public static List<Cla3> outJoin_InnerJoin(List<Cla3> list1, List<Cla3> list2,boolean leftBefore,boolean out){ List<Cla3> outJoin_InnerJoin=new ArrayList<>(); outJoin_InnerJoin.addAll(outJoin(list1, list2, leftBefore, false)); outJoin_InnerJoin.removeAll(innerJoin(list1, list2, leftBefore, false)); if(out){ System.out.println("--------------------outJoin_InnerJoin"+((leftBefore)?"leftBefore":"rightBefore")+"-------------------------------"); outJoin_InnerJoin.forEach(System.out::println); System.out.println("--------------------outJoin_InnerJoin"+((leftBefore)?"leftBefore":"rightBefore")+"-------------------------------"); } return outJoin_InnerJoin; } /** * 内连接 * 左外-左外ISNULL * 右外-右外ISNULL * @param list1 * @param list2 * @param leftBefore 左边在前 * @param out 是否输出 * @return */ public static List<Cla3> innerJoin(List<Cla3> list1, List<Cla3> list2,boolean leftBefore,boolean out){ List<Cla3> innerJoin=new ArrayList<>(); if(leftBefore){ innerJoin.addAll(leftJoin(list1, list2, false, false)); innerJoin.removeAll(leftJoin(list1, list2, true, false)); }else { innerJoin.addAll(rightJoin(list1, list2, false, false)); innerJoin.removeAll(rightJoin(list1, list2, true, false)); } if(out){ System.out.println("--------------------innerJoin"+((leftBefore)?"leftBefore":"rightBefore")+"-------------------------------"); innerJoin.forEach(System.out::println); System.out.println("--------------------innerJoin"+((leftBefore)?"leftBefore":"rightBefore")+"-------------------------------"); } return innerJoin; } /** * 左外连接 * 计算 * SELECT cla1.`id`,cla1.`type`,cla2.`name` * FROM cla1 * LEFT JOIN cla2 * ON cla1.`id`=cla2.`id` * (Where cla2.id IS NULL); * * @param list1 * @param list2 * @param isNull 有没有括号中的IS NULL这条语句 * @param out 输出吗? * @return */ public static List<Cla3> leftJoin(List<Cla3> list1, List<Cla3> list2,boolean isNull,boolean out) { List<Cla3> leftJoin=new ArrayList<>(); list1.forEach(c1->{ AtomicBoolean flag= new AtomicBoolean(false); list2.forEach(c2->{ if(c1.id.equals(c2.id)){ if (!isNull) { leftJoin.add(new Cla3(c1.id, c1.type, c2.name)); } flag.set(true); } }); if(!flag.get()){ leftJoin.add(new Cla3(c1.id,c1.type,"null")); } }); if(out){ System.out.println("--------------------leftJoin---"+((isNull)?"isNull":"----")+"-------------------------------"); leftJoin.forEach(System.out::println); System.out.println("--------------------leftJoin---"+((isNull)?"isNull":"----")+"-------------------------------"); System.out.println(); } return leftJoin; } /** * 右外连接 * 计算 * SELECT cla2.`id`,cla1.`type`,cla2.`name` * FROM cla1 * RIGHT JOIN cla2 * ON cla1.`id`=cla2.`id` * (WHERE cla1.`id` IS NULL); * * @param list1 * @param list2 * @param isNull 有没有括号中的IS NULL这条语句 * @return */ public static List<Cla3> rightJoin(List<Cla3> list1, List<Cla3> list2,boolean isNull,boolean out) { List<Cla3> rightJoin=new ArrayList<>(); list2.forEach(c2->{ AtomicBoolean flag= new AtomicBoolean(false); list1.forEach(c1->{ if(c1.id.equals(c2.id)){ if (!isNull){ rightJoin.add(new Cla3(c2.id, c1.type,c2.name)); } flag.set(true); } }); if(!flag.get()){ rightJoin.add(new Cla3(c2.id,"null",c2.name)); } }); if (out){ System.out.println("--------------------rightJoin---"+((isNull)?"isNull":"----")+"-------------------------------"); rightJoin.forEach(System.out::println); System.out.println("--------------------rightJoin---"+((isNull)?"isNull":"----")+"-------------------------------"); System.out.println(); } return rightJoin; } /** * 外连接 * 左外+右外 * 右外+左外 * SELECT * * FROM tableA A * FULL OUTER JOIN TableB B * ON A.key=B.key * * @param leftBefore 结果集左表在前还是右边在前 * @param out 输出吗 * @return */ public static List<Cla3> outJoin(List<Cla3> list1, List<Cla3> list2,boolean leftBefore,boolean out) { List<Cla3> outJoin=new ArrayList<>(); List<Cla3> leftJoin = leftJoin(list1, list2,!leftBefore,false); List<Cla3> rightJoin = rightJoin(list1, list2,leftBefore,false); if (leftBefore){ outJoin.addAll(leftJoin); outJoin.addAll(rightJoin); }else { outJoin.addAll(rightJoin); outJoin.addAll(leftJoin); } if(out){ System.out.println("--------------------outJoin"+((leftBefore)?"leftBefore":"rightBefore")+"-------------------------------"); outJoin.forEach(System.out::println); System.out.println("--------------------outJoin"+((leftBefore)?"leftBefore":"rightBefore")+"-------------------------------"); } return outJoin; } }
类 Cla1
package test.algo.main2; public class Cla1 { public String id; public String type; public Cla1(String id, String type) { this.id = id; this.type = type; } public String getId() { return id; } public void setId(String id) { this.id = id; } public String getType() { return type; } public void setType(String type) { this.type = type; } @Override public String toString() { return "{id: " + id + ", type: " + type + "}"; } }
类 Cla2
package test.algo.main2; public class Cla2 { public String id; public String name; public Cla2(String id, String name) { this.id = id; this.name = name; } public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } @Override public String toString() { return "{id: " + id + ", name: " + name + "}"; } }
类Cla3
需要重写equals()
id==id
package test.algo.main2; import java.util.Objects; public class Cla3 { public String id; public String name; public String type; public Cla3(String id, String type, String name) { this.id = id; this.type = type; this.name = name; } public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getType() { return type; } public void setType(String type) { this.type = type; } @Override public boolean equals(Object o) { if (this == o) return true; if (o == null || getClass() != o.getClass()) return false; Cla3 cla3 = (Cla3) o; return Objects.equals(id, cla3.id); } @Override public int hashCode() { return Objects.hash(id); } @Override public String toString() { return "{id: " + id + ", type: " + type + ", name: " + name + "}"; } }
最后
2023-8-4 17:04:28
我们都有光明的未来
祝大家考研上岸
祝大家工作顺利
祝大家得偿所愿
祝大家如愿以偿
点赞收藏关注哦