西安专业房产网站建设,普通网站服务器,企业网站建设设计任务书,企业网站建设的评价指标Java实现数据库表中的七种连接【Mysql】 前言版权推荐Java实现数据库表中的七种连接左外连接右外连接其他连接 附录七种连接SQL测试Java测试转换方法类 Cla1类 Cla2类Cla3 最后 前言
2023-8-4 16:51:42
以下内容源自《【Mysql】》 仅供学习交流使用
版权
禁止其他平台发布时… Java实现数据库表中的七种连接【Mysql】 前言版权推荐Java实现数据库表中的七种连接左外连接右外连接其他连接 附录七种连接SQL测试Java测试转换方法类 Cla1类 Cla2类Cla3 最后 前言
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.idcla2.id* (Where cla2.id IS NULL);** param list1* param list2* param isNull 有没有括号中的IS NULL这条语句* param out 输出吗* return*/public static ListCla3 leftJoin(ListCla3 list1, ListCla3 list2,boolean isNull,boolean out) {ListCla3 leftJoinnew 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);}});//没有找到添加 右表属性 NULLif(!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.idcla2.id* (WHERE cla1.id IS NULL);** param list1* param list2* param isNull 有没有括号中的IS NULL这条语句* return*/public static ListCla3 rightJoin(ListCla3 list1, ListCla3 list2,boolean isNull,boolean out) {ListCla3 rightJoinnew 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);}});//没有找到添加 左表属性 NULLif(!flag.get()){rightJoin.add(new Cla3(c2.id,null,c2.name));}});return rightJoin;}其他连接
外连接* 左外右外* 右外左外内连接* 左外-左外ISNULL* 右外-右外ISNULL外连接-内连接附录
七种连接
MySQL笔记第06章_多表查询 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.idcla2.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.idcla2.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.idcla2.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.idcla2.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.idcla2.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.idcla1.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.idcla2.id
UNION ALL
SELECT cla2.id,cla1.type,cla2.name
FROM cla1
RIGHT JOIN cla2
ON cla1.idcla2.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.idcla2.id
UNION ALL
SELECT cla1.id,cla1.type,cla2.name
FROM cla1
LEFT JOIN cla2
ON cla1.idcla2.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 ListCla1 claList1 new ArrayList();static ListCla2 claList2 new ArrayList();static ListCla3 list1 new ArrayList();static ListCla3 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(ListCla1 claList1, ListCla2 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 ListCla3 outJoin_InnerJoin(ListCla3 list1, ListCla3 list2,boolean leftBefore,boolean out){ListCla3 outJoin_InnerJoinnew 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 ListCla3 innerJoin(ListCla3 list1, ListCla3 list2,boolean leftBefore,boolean out){ListCla3 innerJoinnew 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.idcla2.id* (Where cla2.id IS NULL);** param list1* param list2* param isNull 有没有括号中的IS NULL这条语句* param out 输出吗* return*/public static ListCla3 leftJoin(ListCla3 list1, ListCla3 list2,boolean isNull,boolean out) {ListCla3 leftJoinnew 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.idcla2.id* (WHERE cla1.id IS NULL);** param list1* param list2* param isNull 有没有括号中的IS NULL这条语句* return*/public static ListCla3 rightJoin(ListCla3 list1, ListCla3 list2,boolean isNull,boolean out) {ListCla3 rightJoinnew 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.keyB.key** param leftBefore 结果集左表在前还是右边在前* param out 输出吗* return*/public static ListCla3 outJoin(ListCla3 list1, ListCla3 list2,boolean leftBefore,boolean out) {ListCla3 outJoinnew ArrayList();ListCla3 leftJoin leftJoin(list1, list2,!leftBefore,false);ListCla3 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;}Overridepublic 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;}Overridepublic String toString() {return {id: id , name: name };}
}
类Cla3
需要重写equals() idid
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;}Overridepublic 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);}Overridepublic int hashCode() {return Objects.hash(id);}Overridepublic String toString() {return {id: id , type: type , name: name };}
}
最后
2023-8-4 17:04:28
我们都有光明的未来
祝大家考研上岸 祝大家工作顺利 祝大家得偿所愿 祝大家如愿以偿 点赞收藏关注哦
