streamalism.org

1. Uvod

U ovom uputstvu prikazat ćemo različite vrste SQL pridruživanja i kako ih je lako implementirati u Javi.

2. Definiranje modela

Počnimo s izradom dvije jednostavne tablice:

STVARI AUTORA TABLE (ID int NIJE NULL PRIMARNI KLJUČ, FIRST_NAME varchar (255), LAST_NAME varchar (255)); STVARI ČLANAK TABLE (ID int NIJE NULL PRIMARNI KLJUČ, NASLOV varchar (255) NIJE NULL, AUTHOR_ID int, STRANI KLJUČ (AUTHOR_ID) REFERENCE AUTOR (ID)); 

I napunite ih nekim testnim podacima:

UMESTI U AUTORSKE VRIJEDNOSTI (1, 'Siena', 'Kerr'), (2, 'Daniele', 'Ferguson'), (3, 'Luciano', 'Wise'), (4, 'Jonas', 'Lugo' ); INSERT IN TO ARTICLE VALUES (1, 'First steps in Java', 1), (2, 'SpringBoot tutorial', 1), (3, 'Java 12 insights', null), (4, 'SQL JOINS', 2) , (5, 'Uvod u proljetnu sigurnost', 3);

Imajte na umu da u našem uzorku podataka nemaju svi autori članaka i obrnuto. Ovo će igrati veliku ulogu u našim primjerima, koje ćemo vidjeti kasnije.

Definirajmo i POJO koji ćemo koristiti za spremanje rezultata JOIN operacija u cijelom našem vodiču:

class ArticleWithAuthor {naslov privatnog niza; private String authorFirstName; private String authorLastName; // standardni konstruktor, postavljači i dobivači}

U našim ćemo primjerima iz tablice ARTICLE izvući naslov, a iz tablice AUTHOR podatke o autorima.

3. Konfiguracija

Za naše primjere upotrijebit ćemo vanjsku bazu podataka PostgreSQL koja radi na portu 5432. Osim PUNOG PRIDRUŽIVANJA, koji nije podržan ni u MySQL-u ni u H2, svi pruženi isječci trebali bi raditi s bilo kojim SQL dobavljačem.

Za našu implementaciju Jave trebat će nam PostgreSQL upravljački program:

 org.postgresql postgresql 42.2.5 test 

Prvo konfigurirajmo a java.sql.Connection za rad s našom bazom podataka:

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

Dalje, kreirajmo klasu DAO i neke korisne metode:

class ArticleWithAuthorDAO {privatna konačna veza veze; // konstruktor privatni popis ExecuteQuery (String upit) {try (Statement statement = connection.createStatement ()) {ResultSet resultSet = statement.executeQuery (query); vratiti mapToList (resultSet); } catch (SQLException e) {e.printStackTrace (); } vrati novi ArrayList (); } privatni popis mapToList (ResultSet resultSet) baca SQLException {Popis popisa = novi ArrayList (); while (resultSet.next ()) {ArticleWithAuthor articleWithAuthor = new ArticleWithAuthor (resultSet.getString ("TITLE"), resultSet.getString ("FIRST_NAME"), resultSet.getString ("LAST_NAME")); list.add (articleWithAuthor); } popis za povratak; }}

U ovom članku nećemo ulaziti u detalje o upotrebi Skup rezultata, Izjava, i Veza. Te su teme obrađene u našim člancima vezanim uz JDBC.

Počnimo s istraživanjem SQL pridruživanja u odjeljcima ispod.

4. Unutarnje pridruživanje

Počnimo s vjerojatno najjednostavnijom vrstom spajanja. UNUTARNJE PRIDRUŽIVANJE operacija je koja iz obje tablice odabire retke koji odgovaraju zadanom stanju. Upit se sastoji od najmanje tri dijela: odabir stupaca, spajanje tablica i uvjet pridruživanja.

Imajući to na umu, sama sintaksa postaje prilično jednostavna:

ODABERITE ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME IZ ARTICLE INNER PRIDRUŽITE SE AUTORU NA AUTHOR.ID = ARTICLE.AUTHOR_ID

Također možemo ilustrirati rezultat UNUTARNJE PRIDRUŽIVANJE kao zajednički dio skupova koji se sijeku:

Primijenimo sada metodu za UNUTARNJO PRIDRUŽIVANJE u ArticleWithAuthorDAO razred:

Popis članakaInnerJoinAuthor () {String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "FROM ARTICLE INNER PRIDRUŽITE SE AUTORU NA AUTHOR.ID = ARTICLE.AUTHOR_ID"; povratak executeQuery (upit); }

I isprobajte:

@Test javna void whenQueryWithInnerJoin_thenShouldReturnProperRows () 

Kao što smo već spomenuli, INNER JOIN odabire samo uobičajene retke prema navedenom stanju. Gledajući naše umetke, vidimo da imamo jedan članak bez autora i jedan autor bez članka. Ovi se redovi preskaču jer ne ispunjavaju navedeni uvjet. Kao rezultat, dohvaćamo četiri spojena rezultata, a niti jedan nema prazne podatke o autorima niti prazan naslov.

5. Lijevo pridruživanje

Dalje, usredotočimo se na LIJEVO PRIDRUŽIVANJE. Ova vrsta spajanja odabire sve retke iz prve tablice i podudara se s odgovarajućim redovima iz druge tablice. Kad nema podudaranja, stupci se popunjavaju s null vrijednosti.

Prije nego što krenemo u implementaciju Jave, pogledajmo grafički prikaz LIJEVOG PRIDRUŽIVANJA:

U ovom slučaju, rezultat LEFT JOIN uključuje svaki zapis iz skupa koji predstavlja prvu tablicu s vrijednostima koje se sijeku iz druge tablice.

Sada, prijeđimo na implementaciju Jave:

Popis članakaLeftJoinAuthor () {String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "FROM ARTICLE LEFT PRIDRUŽITE SE AUTORU NA AUTHOR.ID = ARTICLE.AUTHOR_ID"; povratak executeQuery (upit); }

Jedina razlika u odnosu na prethodni primjer je ta što smo umjesto ključne riječi INNER koristili ključnu riječ LIJEVO.

Prije nego što testiramo našu metodu LIJEVOG PRIDRUŽIVANJA, pogledajmo ponovno naše umetke. U ovom ćemo slučaju dobiti sve zapise iz tablice ARTICLE i njihove odgovarajuće retke iz tablice AUTHOR. Kao što smo već spomenuli, još nema svaki članak autora, pa očekujemo da ima null vrijednosti umjesto podataka o autoru:

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

6. Pravo pridruživanje

DESNO PRIDRUŽIVANJE je slično LIJEVOM PRIDRUŽENJU, ali vraća sve retke iz druge tablice i podudara se s retcima iz prve tablice. Kao u slučaju LIJEVOG PRIDRUŽIVANJA, prazne šibice zamjenjuju se sa null vrijednosti.

Grafički prikaz ove vrste spajanja zrcalni je odraz onog koji smo ilustrirali LIJEVIM PRIDRUŽIVANJEM:

Primijenimo PRAVO PRIDRUŽIVANJE u Javi:

Popis članakaRightJoinAuthor () {String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "FROM ARTICLE DESNO PRIDRUŽITE SE AUTORU NA AUTHOR.ID = ARTICLE.AUTHOR_ID"; povratak executeQuery (upit); }

Ponovno, pogledajmo naše testne podatke. Budući da ova operacija pridruživanja dohvaća sve zapise iz druge tablice, očekujemo dohvaćanje pet redaka, a budući da nije svaki autor već napisao članak, očekujemo neke null vrijednosti u stupcu TITLE:

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

7. Potpuno vanjsko pridruživanje

Ova operacija spajanja je vjerojatno najzaslužnija. FULL JOIN odabire sve retke i iz prve i iz druge tablice, bez obzira je li uvjet zadovoljen ili ne.

Također možemo predstaviti istu ideju kao i sve vrijednosti iz svakog skupa koji se sijeku:

Pogledajmo implementaciju Jave:

Popis članakaOuterJoinAuthor () {String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "FROM ARTICLE FULL PRIDRUŽITE SE AUTORU NA AUTORU.ID = ARTICLE.AUTHOR_ID"; povratak executeQuery (upit); }

Sada možemo testirati našu metodu:

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

Još jednom, pogledajmo podatke o ispitivanju. Imamo pet različitih članaka, od kojih jedan nema autora, i četiri autora, od kojih jedan nema dodijeljeni članak. Kao rezultat PUNOG PRIDRUŽIVANJA očekujemo dohvaćanje šest redaka. Četiri se međusobno podudaraju, a preostala dva nisu. Iz tog razloga, također pretpostavljamo da će postojati barem jedan redak s null vrijednosti u oba stupca podataka AUTHOR i onom s a null vrijednost u stupcu TITLE.

8. Zaključak

U ovom smo članku istražili osnovne tipove SQL pridruživanja. Pogledali smo primjere četiri vrste pridruživanja i kako se oni mogu implementirati u Javi.

Kao i uvijek, cjeloviti kôd korišten u ovom članku dostupan je na GitHubu.