streamalism.org

1. הקדמה

במדריך זה נציג סוגים שונים של צירופי SQL וכיצד ניתן ליישם אותם בקלות ב- Java.

2. הגדרת המודל

נתחיל ביצירת שתי טבלאות פשוטות:

צור מחבר טבלה (מזהה int לא מפתח ראשוני NULL, FIRST_NAME varchar (255), LAST_NAME varchar (255)); צור מאמר טבלה (מזהה int לא מפתח ראשוני NULL, כותרת varchar (255) לא NULL, AUTHOR_ID int, מפתח חוץ (AUTHOR_ID) הפניות מחבר (ID)); 

ומלא אותם בכמה נתוני בדיקה:

הכנס לערכי מחבר (1, 'סיינה', 'קר'), (2, 'דניאלה', 'פרגוסון'), (3, 'לוצ'יאנו', 'ווייז'), (4, 'ג'ונאס', 'לוגו' ); הכנס לערכי מאמרים (1, 'השלבים הראשונים בג'אווה', 1), (2, 'מדריך SpringBoot', 1), (3, 'תובנות Java 12', null), (4, 'SQL JOINS', 2) , (5, 'מבוא לאבטחת אביב', 3);

שים לב שבמערכת הנתונים לדוגמא שלנו, לא לכל המחברים יש מאמרים, ולהיפך. זה יהיה חלק גדול בדוגמאות שלנו, שנראה בהמשך.

בואו נגדיר POJO בו נשתמש לאחסון התוצאות של פעולות JOIN במהלך כל ההדרכה שלנו:

class ArticleWithAuthor {כותרת מחרוזת פרטית; פרטי מחרוזת authorFirstName; פרטי מחרוזת authorLastName; // קונסטרוקטור סטנדרטי, סטרים וגטרים}

בדוגמאות שלנו, נחלץ כותרת מהטבלה ARTICLE ונתוני מחברים מהטבלה AUTHOR.

3. תצורה

לדוגמאות שלנו, נשתמש במסד נתונים חיצוני של PostgreSQL הפועל ביציאה 5432. מלבד ה- FULL JOIN, שאינו נתמך ב- MySQL או ב- H2, כל התוספים המסופקים צריכים לעבוד עם כל ספק SQL.

לצורך הטמעת Java שלנו, נצטרך מנהל התקן PostgreSQL:

 org.postgresql postgresql 42.2.5 מבחן 

בואו קודם להגדיר את java.sql.Connect לעבוד עם מסד הנתונים שלנו:

Class.forName ("org.postgresql.Driver"); חיבור חיבור = DriverManager. getConnection ("jdbc: postgresql: // localhost: 5432 / myDb", "user", "pass");

לאחר מכן, בואו ניצור מחלקת DAO וכמה שיטות שירות:

כיתה ArticleWithAuthorDAO {חיבור חיבור סופי פרטי; // constructor פרטי רשימה executeQuery (שאילתת מחרוזת) {try (הצהרת הצהרה = connection.createStatement ()) {ResultSet resultSet = statement.executeQuery (שאילתה); return mapToList (resultSet); } לתפוס (SQLException e) {e.printStackTrace (); } להחזיר ArrayList חדש (); } mapToList (ListSet resultSet) של רשימה פרטית זורק SQLException {List list = ArrayList new (); בעוד (resultSet.next ()) {ArticleWithAuthor articleWithAuthor = ArticleWithAuthor חדש (resultSet.getString ("TITLE"), resultSet.getString ("FIRST_NAME"), resultSet.getString ("LAST_NAME")); list.add (articleWithAuthor); } רשימת החזרה; }}

במאמר זה לא נצלול לפרטים אודות השימוש ResultSet, הצהרה, ו חיבור. נושאים אלה סוקרים במאמרים הקשורים ל- JDBC שלנו.

נתחיל לחקור הצטרפות SQL בחלקים שלהלן.

4. הצטרפות פנימית

נתחיל בסוג ההצטרפות אולי הפשוט ביותר. הצטרפות INNER היא פעולה שבוחרת שורות התואמות לתנאי מסופק משתי הטבלאות. השאילתה מורכבת משלושה חלקים לפחות: בחר עמודות, הצטרף לטבלאות ותנאי הצטרפות.

בהתחשב בכך התחביר עצמו נהיה די פשוט:

בחר ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME FROM ARTICLE INNER הצטרף למחבר ב- AUTHOR.ID = ARTICLE.AUTHOR_ID

אנו יכולים גם להמחיש את התוצאה של INNER JOIN כחלק נפוץ מסטים מצטלבים:

בואו וניישם כעת את השיטה ל- INNER JOIN ב- ArticleWithAuthorDAO מעמד:

רשימה מאמרInnerJoinAuthor () {String query = "בחר ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "מאת ARTICLE פנימי הצטרף למחבר ב- AUTHOR.ID = ARTICLE.AUTHOR_ID"; החזר executeQuery (שאילתה); }

ובדוק את זה:

@ מבחן ציבורי בטל כאשר QueryWithInnerJoin_thenShouldReturnProperRows () 

כפי שהזכרנו קודם, ה- INNER JOIN בוחר רק שורות נפוצות לפי תנאי מסופק. כשמסתכלים על התוספות שלנו, נראה שיש לנו מאמר אחד בלי מחבר ומחבר אחד בלי מאמר. מדלגים על שורות אלה מכיוון שהן אינן ממלאות את התנאי שנקבע. כתוצאה מכך, אנו מאחזרים ארבע תוצאות שהצטרפו, ולאף אחת מהן אין נתוני מחברים ריקים ולא כותרת ריקה.

5. הצטרף שמאל

לאחר מכן, נתמקד ב- JOIN JOIN. סוג זה של צירוף בוחר את כל השורות מהטבלה הראשונה ומתאים לשורות המתאימות מהטבלה השנייה. שכן כאשר אין התאמה, העמודות מלאות ריק ערכים.

לפני שנצלול ליישום Java, בואו נסתכל על ייצוג גרפי של ה- LEFT JOIN:

במקרה זה, התוצאה של LEFT JOIN כולל כל רשומה מהסט המייצגת את הטבלה הראשונה עם ערכים מצטלבים מהטבלה השנייה.

עכשיו, בואו נעבור ליישום Java:

רשימה ArticleLeftJoinAuthor () {String query = "בחר ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "מאת ARTICLE LEFT הצטרף ל- AUTHOR.ID = ARTICLE.AUTHOR_ID"; החזר executeQuery (שאילתה); }

ההבדל היחיד לדוגמא הקודמת הוא שהשתמשנו במילת המפתח LEFT במקום במילת המפתח INNER.

לפני שנבדוק את שיטת ה- LEFT JOIN שלנו, בואו נסתכל שוב על התוספות שלנו. במקרה זה נקבל את כל הרשומות מטבלת ARTICLE ואת השורות התואמות שלהן מטבלת AUTHOR. כפי שהזכרנו קודם, לא בכל מאמר יש מחבר עדיין, ולכן אנו מצפים שיהיה ריק ערכים במקום נתוני המחבר:

@Test ציבורי בטל כאשר QueryWithLeftJoin_thenShouldReturnProperRows () {List articleWithAuthorList = articleWithAuthorDAO.articleLeftJoinAuthor (); assertThat (articleWithAuthorList) .hasSize (5); assertThat (articleWithAuthorList) .anyMatch (שורה -> row.getAuthorFirstName () == null); }

6. הצטרף נכון

ה- RIGHT JOIN דומה מאוד ל- JOIN JOIN, אך הוא מחזיר את כל השורות מהטבלה השנייה ומתאים לשורות מהטבלה הראשונה. כמו במקרה של LEFT JOIN, התאמות ריקות מוחלפות ב ריק ערכים.

הייצוג הגרפי של סוג זה של צירוף הוא שיקוף מראה של זה שהמחשנו עבור JOIN JOIN:

בואו נשתמש ב- RIGHT JOIN ב- Java:

רשימה ArticleRightJoinAuthor () {String query = "בחר ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "מאת ARTICLE RIGHT הצטרף ל- AUTHOR.ID = ARTICLE.AUTHOR_ID"; החזר executeQuery (שאילתה); }

שוב, בואו נסתכל על נתוני הבדיקה שלנו. מכיוון שפעולת ההצטרפות הזו מאחזרת את כל הרשומות מהטבלה השנייה אנו מצפים לאחזר חמש שורות, ומכיוון שלא כל מחבר כבר כתב מאמר, אנו מצפים לכמה ריק ערכים בעמודה TITLE:

@ מבחן ציבורי בטל כאשר QueryWithRightJoin_thenShouldReturnProperRows () {List articleWithAuthorList = articleWithAuthorDAO.articleRightJoinAuthor (); assertThat (articleWithAuthorList) .hasSize (5); assertThat (articleWithAuthorList) .anyMatch (שורה -> row.getTitle () == null); }

7. הצטרפות חיצונית מלאה

פעולת הצטרפות זו היא כנראה המסובכת ביותר. ה- FULL JOIN בוחר את כל השורות מהטבלה הראשונה וגם מהטבלה השנייה, בין אם התנאי מתקיים או לא.

אנו יכולים גם לייצג את אותו רעיון כמו כל הערכים מכל אחת מהסטים המצטלבים:

בואו נסתכל על יישום Java:

רשימת מאמרOuterJoinAuthor () {String query = "בחר ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME" + "מאת ARTICLE הצטרף מלא ל- AUTHOR.ID = ARTICLE.AUTHOR_ID"; החזר executeQuery (שאילתה); }

כעת נוכל לבדוק את השיטה שלנו:

@ מבחן פומבי בטל כאשר QueryWithFullJoin_thenShouldReturnProperRows () {List articleWithAuthorList = articleWithAuthorDAO.articleOuterJoinAuthor (); assertThat (articleWithAuthorList) .hasSize (6); assertThat (articleWithAuthorList) .anyMatch (שורה -> row.getTitle () == null); assertThat (articleWithAuthorList) .anyMatch (שורה -> row.getAuthorFirstName () == null); }

שוב, בואו נסתכל על נתוני הבדיקה. יש לנו חמישה מאמרים שונים, לאחד מהם אין מחבר, וארבעה מחברים, אחד מהם לא מאמר שהוקצה. כתוצאה מה- FULL JOIN אנו מצפים לאחזר שש שורות. ארבעה מהם מותאמים זה לזה, והשניים הנותרים לא. מסיבה זו, אנו מניחים כי תהיה לפחות שורה אחת עם ריק ערכים בשתי עמודות הנתונים של AUTHOR ואחד עם a ריק ערך בעמודה TITLE.

8. מסקנה

במאמר זה בחנו את הסוגים הבסיסיים של הצטרפות SQL. בדקנו דוגמאות לארבעה סוגים של צירופים וכיצד ניתן ליישם אותם בג'אווה.

כמו תמיד, הקוד השלם המשמש במאמר זה זמין באתר GitHub.