streamalism.org

1. Introduksjon

I denne veiledningen viser vi forskjellige typer SQL-sammenføyninger og hvordan de enkelt kan implementeres i Java.

2. Definere modellen

La oss starte med å lage to enkle tabeller:

CREATE TABLE AUTHOR (ID int NOT NULL PRIMARY KEY, FIRST_NAME varchar (255), LAST_NAME varchar (255)); OPPRETT TABELLARTIKEL (ID int IKKE NULL PRIMÆR NØKKEL, TITEL varchar (255) IKKE NULL, AUTHOR_ID int, UTENLANDSK TAST (AUTHOR_ID) REFERANSER FORFATTER (ID)); 

Og fyll dem med noen testdata:

INSERT I FORFATTERVERDIER (1, 'Siena', 'Kerr'), (2, 'Daniele', 'Ferguson'), (3, 'Luciano', 'Wise'), (4, 'Jonas', 'Lugo' ); INSERT I ARTICLE VALUES (1, 'First steps in Java', 1), (2, 'SpringBoot tutorial', 1), (3, 'Java 12 insights', null), (4, 'SQL JOINS', 2) , (5, 'Introduction to Spring Security', 3);

Vær oppmerksom på at i vårt eksempeldatasett har ikke alle forfattere artikler, og omvendt. Dette vil spille en stor rolle i eksemplene våre, som vi vil se senere.

La oss også definere en POJO som vi vil bruke til å lagre resultatene av JOIN-operasjoner gjennom hele opplæringen:

klasse ArticleWithAuthor {private String title; private String authorFirstName; private String authorLastName; // standard konstruktør, settere og getters}

I eksemplene våre trekker vi ut en tittel fra ARTICLE-tabellen og forfatterdata fra AUTHOR-tabellen.

3. Konfigurasjon

For eksemplene våre bruker vi en ekstern PostgreSQL-database som kjører på port 5432. Bortsett fra FULL JOIN, som ikke støttes i verken MySQL eller H2, bør alle medfølgende utdrag fungere med enhver SQL-leverandør.

For vår Java-implementering trenger vi en PostgreSQL-driver:

 org.postgresql postgresql 42.2.5 test 

La oss først konfigurere en java.sql.Tilkobling for å jobbe med databasen vår:

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

Deretter la oss lage en DAO-klasse og noen verktøymetoder:

klasse ArticleWithAuthorDAO {privat sluttforbindelse; // constructor private List executeQuery (String query) {try (Statement statement = connection.createStatement ()) {ResultSet resultSet = statement.executeQuery (query); return mapToList (resultSet); } fange (SQLException e) {e.printStackTrace (); } returner ny ArrayList (); } private List mapToList (ResultSet resultSet) kaster 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); } returliste; }}

I denne artikkelen vil vi ikke dykke ned i detaljer om bruk ResultatSett, uttalelse, og Forbindelse. Disse emnene er dekket i våre JDBC-relaterte artikler.

La oss begynne å utforske SQL sammenføyninger i seksjonene nedenfor.

4. Indre sammenføyning

La oss starte med muligens den enkleste typen sammenføyning. INNER JOIN er en operasjon som velger rader som samsvarer med en gitt tilstand fra begge tabellene. Spørringen består av minst tre deler: velg kolonner, bli med tabeller og bli med tilstand.

Med tanke på dette blir syntaksen ganske grei:

VELG ARTIKEL.TITEL, FORfatter.LAST_NAME, FORHOLDER.FIRST_NAME FRA ARTIKEL INNRE BLI MEDFØRER FORFATTER PÅ AUTHOR.ID = ARTIKEL.AUTHOR_ID

Vi kan også illustrere resultatet av INNER JOIN som en vanlig del av kryssende sett:

La oss nå implementere metoden for INNER JOIN i ArticleWithAuthorDAO klasse:

Liste artikkelInnerJoinAuthor () {String spørring = "VELG ARTIKEL.TITEL, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "FRA ARTIKEL INNRE BLI MED FORFATTER PÅ AUTHOR.ID = ARTIKEL.AUTHOR_ID"; returnere executeQuery (spørring); }

Og test det:

@Test offentlig ugyldig nårQueryWithInnerJoin_thenShouldReturnProperRows () 

Som vi nevnte tidligere, velger INNER JOIN bare vanlige rader etter gitt tilstand. Ser vi på innleggene våre, ser vi at vi har en artikkel uten forfatter og en forfatter uten artikkel. Disse radene hoppes over fordi de ikke oppfyller den angitte betingelsen. Som et resultat henter vi fire sammenføyde resultater, og ingen av dem har tomme forfatterdata eller tom tittel.

5. Venstre bli med

Neste, la oss fokusere på VENSTRE JOIN. Denne typen sammenkobling velger alle rader fra den første tabellen og samsvarer med tilsvarende rader fra den andre tabellen. For når det ikke er samsvar, fylles kolonnene med null verdier.

Før vi dykker ned i Java-implementering, la oss se på en grafisk fremstilling av LEFT JOIN:

I dette tilfellet er resultatet av LEFT JOIN inkluderer hver post fra settet som representerer den første tabellen med kryssende verdier fra den andre tabellen.

La oss nå gå til Java-implementeringen:

Liste artikkelLeftJoinAuthor () {String spørring = "VELG ARTIKEL.TITEL, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "FRA ARTIKEL VENSTRE BLI MED FORFATTER PÅ AUTHOR.ID = ARTICLE.AUTHOR_ID"; returnere executeQuery (spørring); }

Den eneste forskjellen til forrige eksempel er at vi brukte VENSTRE nøkkelordet i stedet for det INNRE nøkkelordet.

Før vi tester vår LEFT JOIN-metode, la oss igjen ta en titt på innsatsene våre. I dette tilfellet vil vi motta alle postene fra ARTIKEL-tabellen og tilhørende rader fra AUTHOR-tabellen. Som vi nevnte tidligere, har ikke hver artikkel en forfatter ennå, så vi forventer å ha null verdier i stedet for forfatterdata:

@Test offentlig ugyldig nårQueryWithLeftJoin_thenShouldReturnProperRows () {List articleWithAuthorList = articleWithAuthorDAO.articleLeftJoinAuthor (); assertThat (articleWithAuthorList) .hasSize (5); assertThat (articleWithAuthorList) .anyMatch (rad -> row.getAuthorFirstName () == null); }

6. Right Join

RIGHT JOIN er omtrent som LEFT JOIN, men den returnerer alle radene fra den andre tabellen og samsvarer med radene fra den første tabellen. Som i tilfelle LEFT JOIN, blir tomme kamper erstattet av null verdier.

Den grafiske representasjonen av denne typen sammenføyning er en speilrefleksjon av den vi har illustrert for VENSTRE JOIN:

La oss implementere RIGHT JOIN i Java:

Liste artikkelRightJoinAuthor () {String spørring = "VELG ARTIKEL.TITEL, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "FRA ARTIKEL HØYRE BLI MED FORFATTER PÅ AUTHOR.ID = ARTICLE.AUTHOR_ID"; returnere executeQuery (spørring); }

Igjen, la oss se på testdataene våre. Siden denne sammenføyningsoperasjonen henter alle poster fra den andre tabellen, forventer vi å hente fem rader, og fordi ikke alle forfattere allerede har skrevet en artikkel, forventer vi at noen null verdier i TITLE-kolonnen:

@Test offentlig ugyldig nårQueryWithRightJoin_thenShouldReturnProperRows () {List articleWithAuthorList = articleWithAuthorDAO.articleRightJoinAuthor (); assertThat (articleWithAuthorList) .hasSize (5); assertThat (articleWithAuthorList) .anyMatch (rad -> row.getTitle () == null); }

7. Full ytre sammenføyning

Denne tilknytningsoperasjonen er trolig den vanskeligste. FULL JOIN velger alle rader fra både første og andre tabell, uavhengig av om betingelsen er oppfylt eller ikke.

Vi kan også representere den samme ideen som alle verdier fra hvert av skjæringspunktene:

La oss ta en titt på Java-implementeringen:

Liste artikkelOuterJoinAuthor () {String spørring = "VELG ARTIKEL.TITEL, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "FRA ARTIKEL FULL BLI MEDFØR FORFATTER PÅ AUTHOR.ID = ARTIKEL.AUTHOR_ID"; returnere executeQuery (spørring); }

Nå kan vi teste metoden vår:

@Test offentlig ugyldig nårQueryWithFullJoin_thenShouldReturnProperRows () {List articleWithAuthorList = articleWithAuthorDAO.articleOuterJoinAuthor (); assertThat (articleWithAuthorList) .hasSize (6); assertThat (articleWithAuthorList) .anyMatch (rad -> row.getTitle () == null); assertThat (articleWithAuthorList) .anyMatch (rad -> row.getAuthorFirstName () == null); }

Nok en gang, la oss se på testdataene. Vi har fem forskjellige artikler, hvorav den ene ikke har noen forfatter, og fire forfattere, den ene har ingen tildelt artikkel. Som et resultat av FULL JOIN, forventer vi å hente seks rader. Fire av dem matches hverandre, og de resterende to ikke. Av den grunn antar vi også at det vil være minst en rad med null verdier i begge AUTHOR-datakolonner og en med a null verdi i TITLE-kolonnen.

8. Konklusjon

I denne artikkelen undersøkte vi de grunnleggende typene SQL-sammenkoblinger. Vi så på eksempler på fire typer sammenføyninger og hvordan de kan implementeres i Java.

Som alltid er den komplette koden som brukes i denne artikkelen tilgjengelig på GitHub.