Typy pripojení SQL

1. Úvod

V tomto tutoriáli si ukážeme rôzne typy spojení SQL a ako je možné ich ľahko implementovať v Jave.

2. Definovanie modelu

Začnime vytvorením dvoch jednoduchých tabuliek:

CREATE TABLE AUTHOR (ID int NOT NULL PRIMARY KEY, FIRST_NAME varchar (255), LAST_NAME varchar (255)); CREATE TABLE ARTICLE (ID int NOT NULL PRIMARY KEY, TITLE varchar (255) NOT NULL, AUTHOR_ID int, FOREIGN KEY (AUTHOR_ID) REFERENCES AUTHOR (ID)); 

A vyplňte ich niekoľkými testovacími údajmi:

VLOŽTE DO AUTORSKÝCH HODNÔT (1, „Siena“, „Kerr“), (2, „Daniele“, „Ferguson“), (3, „Luciano“, „Múdry“), (4, „Jonas“, „Lugo“ ); INSERT INTO ARTICLE VALUES (1, 'First steps in Java', 1), (2, 'SpringBoot tutorial', 1), (3, 'Java 12 insights', null), (4, 'SQL JOINS', 2) , (5, „Úvod do jarnej bezpečnosti“, 3);

Upozorňujeme, že v našom vzorovom súbore údajov nemajú články všetci autori a naopak. Toto bude hrať veľkú úlohu v našich príkladoch, ktoré uvidíme neskôr.

Poďme tiež definovať POJO, ktoré použijeme na ukladanie výsledkov operácií JOIN v celom našom výučbe:

trieda ArticleWithAuthor {súkromný názov reťazca; private String authorFirstName; private String authorLastName; // štandardný konštruktor, nastavovatelia a getri}

V našich príkladoch extrahujeme nadpis z tabuľky ČLÁNOK a údaje autorov z tabuľky AUTHOR.

3. Konfigurácia

Pre naše príklady použijeme externú databázu PostgreSQL bežiacu na porte 5432. Okrem FULL JOIN, ktorý nie je podporovaný ani v MySQL, ani v H2, by všetky poskytované úryvky mali fungovať s akýmkoľvek poskytovateľom SQL.

Pre našu implementáciu Java budeme potrebovať ovládač PostgreSQL:

 test org.postgresql postgresql 42.2.5 

Najprv nakonfigurujme a java.sql.Connection pracovať s našou databázou:

Class.forName ("org.postgresql.Driver"); Pripojenie pripojenia = DriverManager. getConnection ("jdbc: postgresql: // localhost: 5432 / myDb", "user", "pass");

Ďalej vytvoríme triedu DAO a niektoré metódy obslužných programov:

trieda ArticleWithAuthorDAO {súkromné ​​konečné pripojenie Pripojenie; // konštruktor private List executeQuery (reťazcový dotaz) {try (výpis príkazu = connection.createStatement ()) {ResultSet resultSet = výpis.executeQuery (dotaz); návrat mapToList (resultSet); } catch (SQLException e) {e.printStackTrace (); } vrátiť nový ArrayList (); } private List mapToList (ResultSet resultSet) hodí SQLException {List list = new ArrayList (); while (resultSet.next ()) {ArticleWithAuthor articleWithAuthor = nový ArticleWithAuthor (resultSet.getString ("TITLE"), resultSet.getString ("FIRST_NAME"), resultSet.getString ("LAST_NAME")); list.add (articleWithAuthor); } návratový zoznam; }}

V tomto článku sa nebudeme venovať podrobnostiam o používaní Sada výsledkov, výpis, a Pripojenie. Týmto témam sa venujeme v našich článkoch týkajúcich sa JDBC.

Začnime skúmať spojenia SQL v sekciách nižšie.

4. Vnútorné spojenie

Začnime s pravdepodobne najjednoduchším typom spojenia. INNER JOIN je operácia, ktorá z oboch tabuliek vyberie riadky zodpovedajúce zadanej podmienke. Dotaz sa skladá najmenej z troch častí: výber stĺpcov, spojenie tabuliek a podmienka spojenia.

Keď to vezmeme do úvahy, samotná syntax bude celkom jednoduchá:

VYBERTE ČLÁNOK.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME Z ČLÁNKU VNÚTORNÉ PRIPOJTE SA K AUTOROVI NA AUTHOR.ID = ARTICLE.AUTHOR_ID

Môžeme tiež ilustrovať výsledok INNER JOIN ako bežná súčasť pretínajúcich sa množín:

Poďme teraz implementovať metódu pre VNÚTORNÉ PRIPOJENIE do ArticleWithAuthorDAO trieda:

Zoznam articleInnerJoinAuthor () {String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "Z ČLÁNKU VNÚTORNÉHO PRIPOJENIA AUTORA NA AUTHOR.ID = ARTICLE.AUTHOR_ID"; return executeQuery (dopyt); }

A otestujte to:

@Test public void whenQueryWithInnerJoin_thenShouldReturnProperRows () 

Ako sme už spomenuli, INNER JOIN vyberie iba bežné riadky podľa poskytnutej podmienky. Pri pohľade na naše prílohy vidíme, že máme jeden článok bez autora a jedného autora bez článku. Tieto riadky sú preskočené, pretože nespĺňajú zadanú podmienku. Vo výsledku načítame štyri spojené výsledky a žiadny z nich nemá prázdne údaje autorov ani prázdny nadpis.

5. Pripojte sa doľava

Ďalej sa zamerajme na LEFT JOIN. Tento druh spojenia vyberie všetky riadky z prvej tabuľky a zodpovedá zodpovedajúcim riadkom z druhej tabuľky. Pokiaľ neexistuje zhoda, stĺpce sa vyplnia nulový hodnoty.

Predtým, ako sa ponoríme do implementácie Java, pozrime sa na grafické znázornenie LEFT JOIN:

V takom prípade je výsledkom LEFT JOIN obsahuje každý záznam z množiny predstavujúcej prvú tabuľku s pretínajúcimi sa hodnotami z druhej tabuľky.

Prejdime teraz k implementácii Java:

Zoznam článkuLeftJoinAuthor () {String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "Z ČLÁNKU LEVÉ PRIPOJTE SA AUTORA NA AUTHOR.ID = ARTICLE.AUTHOR_ID"; return executeQuery (dopyt); }

Jediný rozdiel oproti predchádzajúcemu príkladu je v tom, že sme namiesto kľúčového slova INNER použili kľúčové slovo LEFT.

Predtým, ako otestujeme našu metódu LEFT JOIN, pozrime sa opäť na naše vložky. V takom prípade dostaneme všetky záznamy z tabuľky ČLÁNOK a ich zodpovedajúce riadky z tabuľky AUTOR. Ako sme už spomínali, nie každý článok má zatiaľ autora, takže ho očakávame nulový hodnoty namiesto údajov autora:

@Test public void whenQueryWithLeftJoin_thenShouldReturnProperRows () {List articleWithAuthorList = articleWithAuthorDAO.articleLeftJoinAuthor (); assertThat (articleWithAuthorList) .hasSize (5); assertThat (articleWithAuthorList) .anyMatch (riadok -> riadok.getAuthorFirstName () == null); }

6. Pripojte sa správne

RIGHT JOIN je veľmi podobný LEFT JOIN, ale vracia všetky riadky z druhej tabuľky a zhoduje sa s riadkami z prvej tabuľky. Rovnako ako v prípade LEVÉHO PRIPOJENIA sa prázdne zhody nahradia znakom nulový hodnoty.

Grafické znázornenie tohto druhu spojenia je zrkadlovým odrazom toho, čo sme ilustrovali pre LEFT JOIN:

Poďme implementovať RIGHT JOIN v Jave:

Zoznam articleRightJoinAuthor () {String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "Z PRÁVA PRIHLÁSENIA AUTORA NA AUTHOR.ID = ARTICLE.AUTHOR_ID"; return executeQuery (dopyt); }

Opäť sa pozrime na naše testovacie údaje. Pretože táto operácia spojenia načíta všetky záznamy z druhej tabuľky, očakávame načítanie piatich riadkov, a pretože nie každý autor už napísal článok, očakávame nejaké nulový hodnoty v stĺpci TITLE:

@Test public void whenQueryWithRightJoin_thenShouldReturnProperRows () {List articleWithAuthorList = articleWithAuthorDAO.articleRightJoinAuthor (); assertThat (articleWithAuthorList) .hasSize (5); assertThat (articleWithAuthorList) .anyMatch (riadok -> riadok.getTitle () == null); }

7. Úplné vonkajšie pripojenie

Táto operácia spojenia je pravdepodobne najnáročnejšia. FULL JOIN vyberie všetky riadky z prvej aj druhej tabuľky bez ohľadu na to, či je podmienka splnená alebo nie.

Môžeme tiež predstaviť rovnakú myšlienku ako všetky hodnoty z každej z pretínajúcich sa množín:

Pozrime sa na implementáciu Java:

Zoznam articleOuterJoinAuthor () {String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "Z ČLÁNKU PLNÉ PRIPOJENIE AUTORA NA AUTHOR.ID = ARTICLE.AUTHOR_ID"; return executeQuery (dopyt); }

Teraz môžeme našu metódu otestovať:

@Test public void whenQueryWithFullJoin_thenShouldReturnProperRows () {List articleWithAuthorList = articleWithAuthorDAO.articleOuterJoinAuthor (); assertThat (articleWithAuthorList) .hasSize (6); assertThat (articleWithAuthorList) .anyMatch (riadok -> riadok.getTitle () == null); assertThat (articleWithAuthorList) .anyMatch (riadok -> riadok.getAuthorFirstName () == null); }

Ešte raz sa pozrime na údaje z testu. Máme päť rôznych článkov, z ktorých jeden nemá autora a štyroch autorov, z ktorých jeden nemá žiadny priradený článok. V dôsledku FULL JOIN očakávame načítanie šiestich riadkov. Štyri z nich sú porovnané proti sebe a zvyšné dva nie. Z tohto dôvodu tiež predpokladáme, že bude najmenej jeden riadok s nulový hodnoty v oboch stĺpcoch s údajmi AUTHOR a v jednom s a nulový hodnota v stĺpci TITLE.

8. Záver

V tomto článku sme preskúmali základné typy pripojení SQL. Pozreli sme sa na príklady štyroch typov spojení a na to, ako ich možno implementovať v Jave.

Úplný kód použitý v tomto článku je ako vždy k dispozícii na stránkach GitHub.