Chapter 23: Java Database Connectivity with JDBC23.1 Introduction23.1 Introduction (Cont.)23.2 Relational-Database Model23.2 Relational-Database Model (Cont.)Slide 623.3 Relational Database Overview: The books Database23.3 Relational Database Overview: The books Database (Cont.)Slide 9Slide 10Slide 11Slide 12Slide 1323.4 SQL23.4.1 Basic SELECT Query23.4.2 WHERE Clause23.4.2 WHERE Clause (Cont.)Slide 18Slide 1923.4.3 ORDER BY Clause23.4.3 ORDER BY Clause (Cont.)Slide 22Slide 23Slide 2423.4.4 Merging Data from Multiple Tables: Joining23.4.4 Merging Data from Multiple Tables: Joining (Cont.)23.4.5 INSERT Statement23.4.6 UPDATE Statement23.4.7 DELETE Statement23.5 Creating Database books in Cloudscape23.6 Manipulating Databases with JDBC23.6.1 Connecting to and Querying a DatabaseFig. 23.26 DisplayAuthors.java Displaying the authors table from the books database. Line 5Fig. 23.26 DisplayAuthors.java Displaying the authors table from the books database. Line 30 Line 33 Lines 36 Line 43 Line 47 Line 48Fig. 23.26 DisplayAuthors.java Displaying the authors table from the books database. Line 51 Line 58 Lines 64-70Fig. 23.26 DisplayAuthors.java Displaying the authors table from the books database. Lines 94-95Fig. 23.26 DisplayAuthors.java Displaying the authors table from the books database. Program output23.6.1 Connecting to and Querying a JDBC Data Source (Cont.)23.6.2 Querying the books DatabaseResultSetTableModel enables a Jtable to display the contents of a ResultSet.ResultSetTableModel enables a Jtable to display the contents of a ResultSet. Line 33 Lines 36-38 Line 44ResultSetTableModel enables a Jtable to display the contents of a ResultSet. Line 56 Line 59 Line 68ResultSetTableModel enables a Jtable to display the contents of a ResultSet. Line 80ResultSetTableModel enables a Jtable to display the contents of a ResultSet. Line 101ResultSetTableModel enables a Jtable to display the contents of a ResultSet. Line 133 Line 135ResultSetTableModel enables a Jtable to display the contents of a ResultSet. Line 156 Line 162 Line 163 Line 166ResultSetTableModel enables a Jtable to display the contents of a ResultSet. Lines 174-17523.6.2 Querying the books Database (Cont.)Slide 49DisplayQueryResults for querying database books. Lines 15, 16, 19DisplayQueryResults for querying database books. Lines 36-37DisplayQueryResults for querying database books. Line 58 Lines 66-110 Line 75DisplayQueryResults for querying database books.DisplayQueryResults for querying database books.Slide 55DisplayQueryResults for querying database books. Program output23.7 Stored Procedures23.8 Internet and World Wide Web Resources 2003 Prentice Hall, Inc. All rights reserved.Chapter 23: Java Database Connectivity with JDBCOutline23.1 Introduction23.2 Relational-Database Model23.3 Relational Database Overview: The books Database23.4 SQL23.4.1 Basic SELECT Query23.4.2 WHERE Clause23.4.3 ORDER BY Clause23.4.4 Merging Data from Multiple Tables: INNER JOIN23.4.5 INSERT Statement23.4.6 UPDATE Statement23.4.7 DELETE Statement23.5 Creating Database books in Cloudscape23.6 Manipulating Databases with JDBC23.6.1 Connecting to and Querying a Database23.6.2 Querying the books Database23.7 Stored Procedures23.8 Internet and World Wide Web ResourcesTM 2003 Prentice Hall, Inc. All rights reserved.23.1 Introduction•Database–Collection of data•DBMS–Database management system–Stores and organizes data•SQL–Relational database–Structured Query Language 2003 Prentice Hall, Inc. All rights reserved.23.1 Introduction (Cont.)•RDBMS–Relational database management system–Cloudscape 5.0.4•www.ibm.com/software/data/cloudscape•JDBC–Java Database Connectivity–JDBC driver 2003 Prentice Hall, Inc. All rights reserved.23.2 Relational-Database Model•Relational database–Table•Rows, columns–Primary key•Unique data•SQL statement–Query 2003 Prentice Hall, Inc. All rights reserved.23.2 Relational-Database Model (Cont.)Fig. 23.1 Employee table sample data.Number Name Department Salary Location23603 Jones 413 1100 New Jersey24568 Kerwin 413 2000 New Jersey34589 Larson 642 1800 Los Angeles35761 Myers 611 1400 Orlando47132 Neumann 413 9000 New Jersey78321 Stephens 611 8500 OrlandoRowColumnPrimary key 2003 Prentice Hall, Inc. All rights reserved.23.2 Relational-Database Model (Cont.)Fig. 23.2 Result of selecting distinct Department and Location data from the Employee table.Department Location413 New Jersey611 Orlando642 Los Angeles 2003 Prentice Hall, Inc. All rights reserved.23.3 Relational Database Overview: The books Database•Sample books database–Four tables•authors, publishers, authorISBN and titles–Relationships among the tables 2003 Prentice Hall, Inc. All rights reserved.23.3 Relational Database Overview: The books Database (Cont.)Column Description authorID Author’s ID number in the database. In the books database, this integer column is defined as autoincremented. For each row inserted in this table, the database automatically increments the authorID value to ensure that each row has a unique authorID. This column represents the table’s primary key. firstName Author’s first name (a string). lastName Author’s last name (a string). Fig. 23.3 authors table from books. authorID firstName lastName 1 Harvey Deitel 2 Paul Deitel 3 Tem Nieto 4 Sean Santry Fig. 23.4 Sample data from the authors table. 2003 Prentice Hall, Inc. All rights reserved.23.3 Relational Database Overview: The books Database (Cont.)Column Description publisherID The publisher’s ID number in the database. This autoincremented integer is the table’s primary key. publisherName The name of the publisher (a string). Fig. 23.5 publishers table from books. publisherID publisherName 1 Prentice Hall 2 Prentice Hall PTG Fig. 23.6 Data from the publishers table. 2003 Prentice Hall, Inc. All rights reserved.23.3 Relational Database Overview: The books Database (Cont.)Column Description isbn ISBN of the book (a string). The table’s primary key. title Title of the book (a string). editionNumber Edition number of the book (an integer). copyright Copyright year of the book (a string). publisherID Publisher’s ID number (an integer). A foreign key to the publishers table. imageFile Name of the file containing the book’s cover image (a string). price Suggested retail price of the book (a real number). [Note: The prices shown in this book are for example purposes