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_IDMôž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.
