streamalism.org

1. Inleiding

In deze tutorial laten we verschillende soorten SQL-joins zien en hoe ze eenvoudig in Java kunnen worden geïmplementeerd.

2. Het model definiëren

Laten we beginnen met het maken van twee eenvoudige tabellen:

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)); 

En vul ze met enkele testgegevens:

INVOEGEN IN AUTEURSWAARDEN (1, 'Siena', 'Kerr'), (2, 'Daniele', 'Ferguson'), (3, 'Luciano', 'Wise'), (4, 'Jonas', 'Lugo' ); INVOEGEN IN ARTIKELWAARDEN (1, 'Eerste stappen in Java', 1), (2, 'SpringBoot tutorial', 1), (3, 'Java 12 insights', null), (4, 'SQL JOINS', 2) , (5, 'Inleiding tot Spring Security', 3);

Merk op dat in onze voorbeeldgegevensset niet alle auteurs artikelen hebben en vice versa. Dit zal een grote rol spelen in onze voorbeelden, die we later zullen zien.

Laten we ook een POJO definiëren die we zullen gebruiken voor het opslaan van de resultaten van JOIN-bewerkingen tijdens onze tutorial:

klasse ArticleWithAuthor {private String-titel; private String authorFirstName; private String authorLastName; // standaard constructor, setters en getters}

In onze voorbeelden extraheren we een titel uit de ARTICLE-tabel en auteursgegevens uit de AUTHOR-tabel.

3. Configuratie

Voor onze voorbeelden gebruiken we een externe PostgreSQL-database die op poort 5432 draait. Afgezien van de FULL JOIN, die niet wordt ondersteund in MySQL of H2, zouden alle meegeleverde fragmenten met elke SQL-provider moeten werken.

Voor onze Java-implementatie hebben we een PostgreSQL-stuurprogramma nodig:

 org.postgresql postgresql 42.2.5 test 

Laten we eerst een java.sql.Connection om met onze database te werken:

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

Laten we vervolgens een DAO-klasse en enkele hulpprogramma-methoden maken:

klasse ArticleWithAuthorDAO {privé definitieve verbinding verbinding; // constructor privélijst executeQuery (String-query) {try (Statement-instructie = connection.createStatement ()) {ResultSet resultSet = statement.executeQuery (query); return mapToList (resultSet); } catch (SQLException e) {e.printStackTrace (); } retourneer nieuwe ArrayList (); } private List mapToList (ResultSet resultSet) gooit SQLException {List list = new ArrayList (); while (resultSet.next ()) {ArticleWithAuthor articleWithAuthor = nieuw ArticleWithAuthor (resultSet.getString ("TITLE"), resultSet.getString ("FIRST_NAME"), resultSet.getString ("LAST_NAME")); list.add (articleWithAuthor); } retourlijst; }}

In dit artikel gaan we niet in op details over het gebruik van ResultSet, Statement, en Verbinding. Deze onderwerpen worden behandeld in onze JDBC-gerelateerde artikelen.

Laten we beginnen met het verkennen van SQL-joins in de onderstaande secties.

4. Innerlijke verbinding

Laten we beginnen met mogelijk het eenvoudigste type join. De INNER JOIN is een bewerking die rijen selecteert die overeenkomen met een opgegeven voorwaarde uit beide tabellen. De query bestaat uit ten minste drie delen: kolommen selecteren, tabellen samenvoegen en voorwaarde voor samenvoegen.

Met dat in gedachten wordt de syntaxis zelf vrij eenvoudig:

SELECTEER ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME VAN ARTIKEL INNER JOIN AUTHOR OP AUTHOR.ID = ARTICLE.AUTHOR_ID

We kunnen ook het resultaat van illustreren INNER JOIN als een veelvoorkomend onderdeel van kruisende sets:

Laten we nu de methode implementeren voor de INNER JOIN in het ArtikelMetAuthorDAO klasse:

List articleInnerJoinAuthor () {String query = "SELECTEER ARTIKEL.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "VAN ARTIKEL INNER JOIN AUTEUR OP AUTHOR.ID = ARTIKEL.AUTHOR_ID"; return executeQuery (query); }

En test het:

@Test openbare ongeldig whenQueryWithInnerJoin_thenShouldReturnProperRows () 

Zoals we eerder vermeldden, selecteert de INNER JOIN alleen gemeenschappelijke rijen op basis van een opgegeven voorwaarde. Als we naar onze bijlagen kijken, zien we dat we één artikel zonder auteur en één auteur zonder artikel hebben. Deze rijen worden overgeslagen omdat ze niet voldoen aan de opgegeven voorwaarde. Als resultaat halen we vier samengevoegde resultaten op, en geen van hen heeft lege auteursgegevens of lege titel.

5. Linker samenvoegen

Laten we ons vervolgens concentreren op de LEFT JOIN. Dit soort join selecteert alle rijen uit de eerste tabel en komt overeen met de corresponderende rijen uit de tweede tabel. Voor als er geen match is, worden kolommen gevuld met nul waarden.

Voordat we ingaan op de Java-implementatie, laten we eens kijken naar een grafische weergave van de LEFT JOIN:

In dit geval is het resultaat van de LEFT JOIN omvat elk record uit de set dat de eerste tabel vertegenwoordigt met kruisende waarden uit de tweede tabel.

Laten we nu naar de Java-implementatie gaan:

Lijst articleLeftJoinAuthor () {String query = "SELECTEER ARTIKEL.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "VAN ARTIKEL LINKS JOIN AUTEUR OP AUTHOR.ID = ARTIKEL.AUTHOR_ID"; return executeQuery (query); }

Het enige verschil met het vorige voorbeeld is dat we het trefwoord LEFT hebben gebruikt in plaats van het trefwoord INNER.

Laten we, voordat we onze LEFT JOIN-methode testen, nogmaals onze inserts bekijken. In dit geval ontvangen we alle records uit de ARTICLE-tabel en hun overeenkomende rijen uit de AUTHOR-tabel. Zoals we eerder vermeldden, heeft nog niet elk artikel een auteur, dus we verwachten dat er wel een is nul waarden in plaats van auteursgegevens:

@Test openbare leegte whenQueryWithLeftJoin_thenShouldReturnProperRows () {List articleWithAuthorList = articleWithAuthorDAO.articleLeftJoinAuthor (); assertThat (articleWithAuthorList) .hasSize (5); assertThat (articleWithAuthorList) .anyMatch (rij -> row.getAuthorFirstName () == null); }

6. Juiste verbinding

De RIGHT JOIN lijkt veel op de LEFT JOIN, maar retourneert alle rijen uit de tweede tabel en komt overeen met rijen uit de eerste tabel. Net als in het geval van LEFT JOIN, worden lege overeenkomsten vervangen door nul waarden.

De grafische weergave van dit soort join is een spiegelreflectie van degene die we hebben geïllustreerd voor de LEFT JOIN:

Laten we de RIGHT JOIN in Java implementeren:

Lijst articleRightJoinAuthor () {String query = "SELECTEER ARTIKEL.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "VAN ARTIKEL RECHTS JOIN AUTEUR OP AUTHOR.ID = ARTIKEL.AUTHOR_ID"; return executeQuery (query); }

Laten we nogmaals naar onze testgegevens kijken. Aangezien deze samenvoegbewerking alle records van de tweede tabel ophaalt, verwachten we vijf rijen op te halen, en omdat niet elke auteur al een artikel heeft geschreven, verwachten we enkele nul waarden in de TITLE-kolom:

@Test openbare leegte whenQueryWithRightJoin_thenShouldReturnProperRows () {List articleWithAuthorList = articleWithAuthorDAO.articleRightJoinAuthor (); assertThat (articleWithAuthorList) .hasSize (5); assertThat (articleWithAuthorList) .anyMatch (row -> row.getTitle () == null); }

7. Volledige Outer Join

Deze samenvoegbewerking is waarschijnlijk de meest lastige. De FULL JOIN selecteert alle rijen uit zowel de eerste als de tweede tabel, ongeacht of aan de voorwaarde is voldaan of niet.

We kunnen ook hetzelfde idee weergeven als alle waarden van elk van de kruisende sets:

Laten we eens kijken naar de Java-implementatie:

List articleOuterJoinAuthor () {String query = "SELECTEER ARTIKEL.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "VAN ARTIKEL VOLLEDIG BIJ AUTEUR OP AUTHOR.ID = ARTIKEL.AUTHOR_ID"; return executeQuery (query); }

Nu kunnen we onze methode testen:

@Test openbare leegte whenQueryWithFullJoin_thenShouldReturnProperRows () {List articleWithAuthorList = articleWithAuthorDAO.articleOuterJoinAuthor (); assertThat (articleWithAuthorList) .hasSize (6); assertThat (articleWithAuthorList) .anyMatch (row -> row.getTitle () == null); assertThat (articleWithAuthorList) .anyMatch (rij -> row.getAuthorFirstName () == null); }

Laten we nogmaals naar de testgegevens kijken. We hebben vijf verschillende artikelen, waarvan er één geen auteur heeft, en vier auteurs, waarvan er één geen toegewezen artikel heeft. Als resultaat van de FULL JOIN verwachten we zes rijen op te halen. Vier van hen worden tegen elkaar vergeleken, en de overige twee niet. Om die reden gaan we er ook vanuit dat er minimaal één rij met komt nul waarden in beide AUTHOR-gegevenskolommen en een met een nul waarde in de kolom TITLE.

8. Conclusie

In dit artikel hebben we de basistypen SQL-joins onderzocht. We hebben voorbeelden van vier soorten joins bekeken en hoe deze in Java kunnen worden geïmplementeerd.

Zoals altijd is de volledige code die in dit artikel wordt gebruikt, beschikbaar op GitHub.