1 Chapter 37 Java Database Programming Liang Introduction

2 What is a Database System? e. g. , Access, My. SQL, Oracle, and MS SQL Server Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

3 Database Application Systems Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson

4 Relational Databases Most of today’s database systems are relational database systems, based on the relational data model. Components: F Structure – data representation F Integrity – constraints F Language – for accessing and manipulating data. Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

5 Course Table Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education,

6 Student Table Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education,

7 Enrollment Table Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education,

8 Table vs. File NOTE: Table (relation) is not the same as file. Most relational database systems store multiple tables in a file. Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

9 Integrity Constraints Integrity constraints impose conditions that the relations must satisfy. Øintra-relational constraints (involve 1 relation) Ødomain constraint Øprimary key constraint Øinter-relational (involve more than 1 relation) Øforeign key constraint. Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

10 Domain Constraints specify the permissible values for an attribute. domain constraint Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

11 Primary Key Constraints Used to identify records in a relation Primary key constraint Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

12 Foreign Key Constraints Define relationships between tables Foreign key constraint Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

Superkey Key Candidate key A superkey is an attribute or a set of attributes that uniquely identify the relation. No two tuples have the same values on the superkey. Primary key A relation consists of a set of distinct tuples. The set of all attributes in the relation forms a superkey. Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671 13

Key and Candidate Key Superkey 14 Key A key K is a minimal superkey, meaning that any proper subset of K is not a superkey. Candidate key It is possible that a relation has several keys. Primary key In this case, each of the keys is called a candidate key. Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

Primary Key Superkey 15 Key The primary key is a candidate key designated by the database designer. Candidate key Often used to identify tuples in a relation. Primary key create table Course( subject. Code char(4), course. Number int, title varchar(50), num. Of. Credits int constraint greater. Than. One check (num. Of. Credits >= 1), primary key (subject. Code, course. Number)); Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

Foreign Key Example 16 create table Enrollment ( ssn char(9), course. Id char(5), date. Registered date, grade char(1), primary key (ssn, course. Id), foreign key (ssn) references Student, foreign key (course. Id) references Course ); Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

17 Foreign Key Discussion, cont. A foreign key is not necessarily the primary key or part of the primary in the relation. The referencing relation and the referenced relation may be the same table. (supervisor. Id is a foreign key in Faculty that references faculty. Id in Faculty) Foreign key and its referenced primary key can have different names, as long as they have the same domain. Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

18 SQL: Structured Query Language Ø To access database data ØTo manipulate data ØUniversal language for accessing relational database systems Ø Application programs may allow users to access database without directly using SQL, since the underlying applications use SQL. Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

Examples of simple SQL statements Create table Drop table Describe table Select Insert Delete Update create table Course ( course. Id char(5), subject. Id char(4) not null, course. Number integer, title varchar(50) not null, num. Of. Credits integer, primary key (course. Id) ); create table Student ( ssn char(9), first. Name varchar(25), mi char(1), last. Name varchar(25), birth. Date date, street varchar(25), phone char(11), zip. Code char(5), dept. Id char(4), primary key (ssn) ); Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671 19

Examples of simple SQL statements Create table Drop table Describe table Select Insert Delete Update drop table Enrollment; drop table Course; drop table Student; Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671 20

Examples of simple SQL statements Create table Drop table Describe table Select Insert Delete Update describe Course; -- Oracle Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671 21

Examples of simple SQL statements Create table Drop table Describe table Select Insert Delete Update select first. Name, mi, last. Name from Student where dept. Id = 'CS'; select first. Name, mi, last. Name from Student where dept. Id = 'CS' and zip. Code = '31411'; select * from Student where dept. Id = 'CS' and zip. Code = '31411'; Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671 22

Examples of simple SQL statements Create table Drop table Describe table Select Insert Delete Update insert into Course (course. Id, subject. Id, course. Number, title) values ('11113', 'CSCI', '3720', 'Database Systems', 3); Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671 23

Examples of simple SQL statements Create table Drop table Describe table Select Insert Update Delete update Course set num. Of. Credits = 4 where title = 'Database Systems'; Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671 24

Examples of simple SQL statements Create table Drop table Describe table Select Insert Update Delete delete Course where title = 'Database System'; Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671 25

The Architecture of JDBC Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson

27 The JDBC Interfaces Loading drivers Establishing connections Creating and executing statements Processing Result. Set Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

Developing JDBC Programs Loading drivers Statement to load a driver: Class. for. Name("JDBCDriver. Class"); Establishing connections A driver is a class. For example: Creating and executing statements Database Driver Class Source Access sun. jdbc. odbc. Jdbc. Odbc. Driver Already in JDK My. SQL com. mysql. jdbc. Driver Website Oracle oracle. jdbc. driver. Oracle. Driver Website Processing Result. Set 28 The JDBC-ODBC driver for Access is bundled in JDK. My. SQL driver class is in mysqljdbc. jar Oracle driver class is in classes 12. jar To use the My. SQL and Oracle drivers, you have to add mysqljdbc. jar and classes 12. jar in the classpath using the following DOS command on Windows: classpath=%classpath%; c: bookmysqljdbc. jar; c: bookclasses 12. jar Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

Developing JDBC Programs 29 Connection connection = Driver. Manager. get. Connection(database. URL); Database URL Pattern Establishing Access jdbc: odbc: data. Source connections My. SQL jdbc: mysql: //hostname/dbname Oracle jdbc: oracle: thin: @hostname: port#: oracle. DBSID Creating and executing See Supplement IV. D for statements Examples: creating an ODBC data source For Access: Processing Connection connection = Driver. Manager. get. Connection Result. Set ("jdbc: odbc: Example. MDBData. Source"); Loading drivers For My. SQL: Connection connection = Driver. Manager. get. Connection ("jdbc: mysql: //localhost/test"); For Oracle: Connection connection = Driver. Manager. get. Connection ("jdbc: oracle: thin: @liang. armstrong. edu: 1521: orcl", "scott", "tiger"); Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

Developing JDBC Programs Loading drivers Establishing connections Creating and executing statements Processing Result. Set Creating statement: Statement statement = connection. create. Statement(); Executing statement (for update, delete, insert): statement. execute. Update ("create table Temp (col 1 char(5), col 2 char(5))"); Executing statement (for select): // Select the columns from the Student table Result. Set result. Set = statement. execute. Query ("select first. Name, mi, last. Name from Student where last. Name " + " = 'Smith'"); Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671 30

Developing JDBC Programs Loading drivers Establishing connections Creating and executing statements Processing Result. Set 31 Executing statement (for select): // Select the columns from the Student table Result. Set result. Set = stmt. execute. Query ("select first. Name, mi, last. Name from Student where last. Name " + " = 'Smith'"); Processing Result. Set (for select): // Iterate through the result and print the student names while (result. Set. next()) System. out. println(result. Set. get. String(1) + " " + result. Set. get. String(2) + ". " + result. Set. get. String(3)); Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

import java. sql. *; public class Simple. Jdbc { public static void main(String[] args) throws SQLException, Class. Not. Found. Exception { // Load the JDBC driver Class. for. Name("com. mysql. jdbc. Driver"); System. out. println("Driver loaded"); // Establish a connection Connection connection = Driver. Manager. get. Connection ("jdbc: mysql: //localhost/test"); System. out. println("Database connected"); // Create a statement Statement statement = connection. create. Statement(); // Execute a statement Result. Set result. Set = statement. execute. Query ("select first. Name, mi, last. Name from Student where last. Name " + " = 'Smith'"); // Iterate through the result and print the student names while (result. Set. next()) System. out. println(result. Set. get. String(1) + "t" + result. Set. get. String(2) + "t" + result. Set. get. String(3)); // Close the connection. close(); } } Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All Simple JDBC Example rights reserved. 0136012671 32

Creating ODBC Data Source 33 Please follow the steps in Supplement on the Companion Website to create an ODBC data source on Windows. Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

34 Example: Accessing Database from Java Applets This example demonstrates connecting to a database from a Java applet. The applet lets the user enter the SSN and the course ID to find a student’s grade. Find. Grade Run NOTE: To run this program from here, you need: 1. To have a My. SQL database setup just like the one in the text. 2. Set My. SQL JDBC driver in the classpath. Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

35 Processing Statements Once a connection to a particular database is established, it can be used to send SQL statements from your program to the database. JDBC provides the Statement, Prepared. Statement, and Callable. Statement interfaces to facilitate sending statements to a database for execution and receiving execution results from the database. Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

Processing Statements Diagram Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education,

37 execute, execute. Query, execute. Update Methods for executing SQL statements: Øexecute. Query (single result set) Øexecute. Update (0 or 1 update counts) Parameters: string containing a SQL statement , passed to database for execution Use execute if multiple result sets, multiple update counts, or a combination of result sets and update counts are produced. Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

38 Retrieving Database Metadata Database metadata: information that describes the database itself. JDBC includes ØDatabase. Meta. Data interface for obtaining database wide information ØResult. Set. Meta. Data interface for descriptive information on a specific Result. Set. Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

39 Database Metadata, cont. Database. Meta. Data methods can be divided into three groups: Ø retrieve general information: Ø find database capabilities Ø get object descriptions Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

40 Database Metadata, cont. Database. Meta. Data methods can be divided into three groups: Ø retrieve general information: Ø URL Ø username Øproduct version Ø driver name / version Øavailable functions / data types Ø find database capabilities Ø get object descriptions Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

41 Database Metadata, cont. Database. Meta. Data methods can be divided into three groups: Ø retrieve general information: Ø find database capabilities Ø does the database supports GROUP BY ? Ø is add column option valid in ALTER TABLE? Øsupported types of SQL grammar Ø get object descriptions Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

42 Database Metadata, cont. Database. Meta. Data methods can be divided into three groups: Ø retrieve general information: Ø find database capabilities Øget object descriptions Ø describe table Ø describe view Ø describe procedure Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671

Database. Meta. Data db. Meta. Data = connection. get. Meta. Data(); System. out. println("database URL: " + db. Meta. Data. get. URL()); System. out. println("database username: " + db. Meta. Data. get. User. Name()); System. out. println("database product name: " + db. Meta. Data. get. Database. Product. Name()); System. out. println("database product version: " + db. Meta. Data. get. Database. Product. Version()); System. out. println("JDBC driver name: " + db. Meta. Data. get. Driver. Name()); System. out. println("JDBC driver version: " + db. Meta. Data. get. Driver. Version()); System. out. println("JDBC driver major version: " + new Integer(db. Meta. Data. get. Driver. Major. Version())); System. out. println("JDBC driver minor version: " + new Integer(db. Meta. Data. get. Driver. Minor. Version())); System. out. println("Max number of connections: " + new Integer(db. Meta. Data. get. Max. Connections())); System. out. println("Max. Table. Name. Lentgh: " + new Integer(db. Meta. Data. get. Max. Table. Name. Length())); System. out. println("Max. Columns. In. Table: " + new Integer(db. Meta. Data. get. Max. Columns. In. Table())); connection. close(); Examples Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All rights reserved. 0136012671 43