Az SQL csatlakozások típusai

1. Bemutatkozás

Ebben az oktatóanyagban bemutatjuk az SQL-csatlakozások különféle típusait, és azt, hogy miként lehet őket egyszerűen megvalósítani a Java-ban.

2. A modell meghatározása

Kezdjük két egyszerű táblázat létrehozásával:

TÁBLÁZATTARTÓ LÉTREHOZÁSA (ID nem NULL PRIMARY KEY, FIRST_NAME varchar (255), LAST_NAME varchar (255)); TABLE CIKK LÉTREHOZÁSA (ID int NULL PRIMARY KEY, TITLE varchar (255) NOT NULL, AUTHOR_ID int, FOREIGN KEY (AUTHOR_ID) REFERENCIA AUTHOR (ID)); 

És töltse ki őket néhány tesztadattal:

SZúrja be a szerzői értékekbe (1, 'Siena', 'Kerr'), (2, 'Daniele', 'Ferguson'), (3, 'Luciano', 'Wise'), (4, 'Jonas', 'Lugo' ); BESZERZÉS CIKKÉRTÉKEKBE (1, „Első lépések a Java-ban”, 1), (2, „SpringBoot bemutató”, 1), (3, „Java 12 insights”, null), (4, „SQL JOINS”, 2) , (5, „Bevezetés a tavaszi biztonságba”, 3);

Ne feledje, hogy mintaadatkészletünkben nem minden szerzőnek vannak cikkei, és fordítva. Ez nagy szerepet játszik majd példáinkban, amelyeket később megnézünk.

Határozzunk meg egy POJO-t is, amelyet a JOIN műveletek eredményeinek tárolásához használunk az oktatóanyagunk során:

class ArticleWithAuthor {private String title; privát karakterlánc szerzőFirstName; privát karakterlánc authorLastName; // szabványos kivitelező, beállítók és szerelők}

Példáinkban kivonunk egy címet az ARTICLE táblázatból, a szerzők adatait pedig az AUTHOR táblázatból.

3. Konfiguráció

Példaként egy külső PostgreSQL adatbázist fogunk használni, amely az 5432-es porton fut. A FULL JOIN kivételével, amelyet sem a MySQL, sem a H2 nem támogat, az összes megadott kivonatnak működnie kell bármelyik SQL szolgáltatóval.

Java megvalósításunkhoz szükségünk lesz egy PostgreSQL illesztőprogramra:

 org.postgresql postgresql 42.2.5 teszt 

Konfiguráljuk először a java.sql.Connection hogy működjön együtt az adatbázisunkkal:

Class.forName ("org.postgresql.Driver"); Csatlakozási kapcsolat = DriverManager. getConnection ("jdbc: postgresql: // localhost: 5432 / myDb", "user", "pass");

Ezután hozzunk létre egy DAO osztályt és néhány segédprogramot:

class ArticleWithAuthorDAO {privát végső kapcsolat kapcsolat; // konstruktor privát List executeQuery (String lekérdezés) {try (Statement utasítás = connection.createStatement ()) {ResultSet resultSet = utasítás.executeQuery (lekérdezés); return mapToList (resultSet); } catch (SQLException e) {e.printStackTrace (); } return new ArrayList (); } privát List mapToList (ResultSet resultSet) dobja az SQLException {List list = new ArrayList (); while (resultSet.next ()) {ArticleWithAuthor ArticleWithAuthor = new ArticleWithAuthor (resultSet.getString ("TITLE"), resultSet.getString ("FIRST_NAME"), resultSet.getString ("LAST_NAME"); list.add (articleWithAuthor); } visszatérési lista; }}

Ebben a cikkben nem részletezzük a használat részleteit ResultSet, Statement, és Kapcsolat. Ezeket a témákat a JDBC-vel kapcsolatos cikkeink tárgyalják.

Kezdjük vizsgálni az SQL csatlakozásokat az alábbi szakaszokban.

4. Belső csatlakozás

Kezdjük a legegyszerűbb típusú csatlakozással. Az INNER JOIN egy olyan művelet, amely mindkét táblázatból kiválaszt egy adott feltételnek megfelelő sorokat. A lekérdezés legalább három részből áll: oszlopok kiválasztása, táblák összekapcsolása és csatlakozás feltétele.

Ezt szem előtt tartva maga a szintaxis is elég egyértelművé válik:

CIKK, CÍM, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME KIVÁLASZTÁSA A CIKK BELSŐBEN CSATLAKOZZA A SZERZŐT AZ AUTHOR-NEK.

Illusztrálhatjuk a BELSŐ CSATLAKOZÁS a metsző halmazok közös részeként:

Vezessük be most a Belső Csatlakozás módját a ArticleWithAuthorDAO cikk osztály:

List ArticleInnerJoinAuthor () {String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "From ARTICLE INNER JOIN AUTHOR ON AUTHOR.ID = ARTICLE.AUTHOR_ID"; return executeQuery (lekérdezés); }

És tesztelje:

@Test public void whenQueryWithInnerJoin_thenShouldReturnProperRows () 

Mint korábban említettük, az INNER JOIN csak a közös sorokat választja ki egy feltétellel. Betétlapjainkat nézve azt látjuk, hogy egy cikkünk van szerző nélkül, egy pedig cikk nélkül. Ezeket a sorokat kihagyjuk, mert nem teljesítik a megadott feltételt. Ennek eredményeként négy összekapcsolt eredményt kapunk, és egyiküknek sem üres szerzői adatai, sem üres címe nincs.

5. Bal csatlakozás

Ezután összpontosítsunk a BAL CSATLAKOZÁSRA. Ez a fajta csatlakozás kiválasztja az első táblázat összes sorát, és megegyezik a második táblázat megfelelő soraival. Ha nincs egyezés, akkor az oszlopok kitöltésre kerülnek nulla értékek.

Mielőtt belevetnénk magunkat a Java megvalósításába, nézzük meg a LEFT JOIN grafikus ábrázolását:

Ebben az esetben a A BAL JOIN tartalmazza a készlet minden rekordját, amely az első táblázatot képviseli, a második táblázat metszőértékeivel.

Most térjünk át a Java implementációra:

List articleLeftJoinAuthor () {String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "From ARTICLE LEFT JOIN AUTHOR ON AUTHOR.ID = ARTICLE.AUTHOR_ID"; return executeQuery (lekérdezés); }

Az egyetlen különbség az előző példával szemben az, hogy a BAL kulcsot használtuk a BELSŐ kulcsszó helyett.

Mielőtt tesztelnénk a LEFT JOIN módszerünket, nézzük meg újra a betétjeinket. Ebben az esetben megkapjuk az ARTICLE táblázat összes rekordját és a megfelelő sorokat az AUTHOR táblából. Mint korábban említettük, még nem minden cikknek van szerzője, ezért elvárjuk, hogy legyen nulla értékek a szerzői adatok helyett:

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

6. Jobb csatlakozás

A JOBB CSATLAKOZÁS hasonlít a BAL CSATLAKOZÁShoz, de visszaadja a második táblázat összes sorát, és egyezik az első táblázat soraival. A BAL CSATLAKOZÁShoz hasonlóan az üres mérkőzések helyébe a következő lép nulla értékek.

Az ilyen típusú összekapcsolás grafikus ábrázolása tükrözi azt, amelyet a BAL CSATLAKOZÁSHOZ illusztráltunk:

Vezessük be a RIGHT JOIN-t Java-ban:

List articleRightJoinAuthor () {String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "CIKK JOBB CSATLAKOZÁSÁTÓL AZ AUTHORON. ID = ARTICLE.AUTHOR_ID"; return executeQuery (lekérdezés); }

Ismét nézzük meg a vizsgálati adatainkat. Mivel ez a csatlakozási művelet az összes rekordot beolvassa a második táblából, várhatóan öt sort fogunk letölteni, és mivel nem minden szerző írt még cikket, néhányra számíthatunk nulla értékek a TITLE oszlopban:

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

7. Teljes külső csatlakozás

Ez a csatlakozási művelet valószínűleg a legbonyolultabb. A FULL JOIN kiválasztja az első és a második táblázat összes sorát, függetlenül attól, hogy a feltétel teljesül-e vagy sem.

Ugyanazt az elképzelést képviselhetjük, mint az összes metsző halmaz összes értéke:

Vessünk egy pillantást a Java implementációra:

List articleOuterJoinAuthor () {String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "From ARTICLE CULL CHOIN AUTHOR ON AUTHOR.ID = ARTICLE.AUTHOR_ID"; return executeQuery (lekérdezés); }

Most kipróbálhatjuk módszerünket:

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

Még egyszer nézzük meg a teszt adatait. Öt különböző cikkünk van, amelyek közül az egyiknek nincs szerzője, és négy szerzőnk, az egyiknek nincs hozzárendelt cikke. A FULL JOIN eredményeként hat sor beolvasására számítunk. Közülük négyet egymással párosítanak, a maradék kettőt pedig nem. Ezért azt is feltételezzük, hogy legalább egy sor lesz nulla értékek mind az AUTHOR adatoszlopokban, mind az egyik a-val nulla érték a TITLE oszlopban.

8. Következtetés

Ebben a cikkben az SQL csatlakozások alapvető típusait tártuk fel. Megnéztük a csatlakozások négy típusának példáját, és azt, hogy miként valósíthatók meg a Java-ban.

Mint mindig, a cikkben használt teljes kód elérhető a GitHubon.