Chapter 32 Java Database Programming PDF Free Download

2y ago

35 Views

1 Downloads

1.04 MB

60 Pages

Report/dmca

Download PDF

Transcription

Objectives Tounderstand the concept of database and database managementsystems (§32.2). To understand the relational data model: relational data structures,constraints, and languages (§32.2). To use SQL to create and drop tables, and to retrieve and modifydata (§32.3). To learn how to load a driver, connect to a database, executestatements, and process result sets using JDBC (§32.4). To use prepared statements to execute precompiled SQL statements(§32.5). To use callable statements to execute stored SQL procedures andfunctions (§32.6). To explore database metadata using the DatabaseMetaData andResultSetMetaData interfaces (§32.7).Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.2

Rational Database and RelationalData ModelMost of today’s database systems are relational databasesystems, based on the relational data model. A relational datamodel has three key A relational data model has three keycomponents: structure, integrity and languages. Structure defines the representation of the data. Integrity imposes constraints on the data. Language provides the means for accessing andmanipulating data.Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.5

Relational StructureA relational database consists of a set ofrelations. A relation has two things in one: aschema and an instance of the schema. Theschema defines the relation and an instance isthe content of the relation at a given time. Aninstance of a relation is nothing more than atable with rows and named columns. Forconvenience with no confusion, we referinstances of relations as just relations or tables.Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.6

Table vs. FileNOTE:A table or a relation is not same as a file.Most of the relational database systemsstore multiple tables in a file.Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.10

Integrity ConstraintsAn integrity constraint imposes a condition that alllegal instances of the relations must satisfy. Ingeneral, there are three types of constraints: domainconstraint, primary key constraint, and foreign keyconstraint. Domain constraints and primary keyconstraints are known as intra-relationalconstraints, meaning that a constraint involves onlyone relation. The foreign key constraint is knownas inter-relational, meaning that a constraintinvolves more than one relation.Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.11

Primary Key ConstraintsEnrollment 2004-03-19CEach value in courseId in theEnrollment table must match a valuein courseId in the Course tableCourse ion to Java IIntroduction to Java IIDatabase Systems433Each row must have avalue for couserId, andthe value must be uniquePrimary keyconstraintEach value in thenumOfCredits column must begreater than 0 and less than 5Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.13

Foreign Key ConstraintsEnrollment 2004-03-19CEach value in courseId in theEnrollment table must match a valuein courseId in the Course tableCourse ion to Java IIntroduction to Java IIDatabase Systems433Each row must have avalue for couserId, andthe value must be uniqueForeign keyconstraintEach value in thenumOfCredits column must begreater than 0 and less than 5Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.14

Domain ConstraintsDomain constraints specify the permissible valuesfor an attribute. Domains can be specified usingstandard data types such as integers, floating-pointnumbers, fixed-length strings, and variant-lengthstrings. The standard data type specifies a broadrange of values. Additional constraints can bespecified to narrow the ranges. You can also specifywhether an attribute can be null.Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.15

Domain Constraints Examplecreate table Course (courseId char(5),subjectId char(4) not null,courseNumber integer,title varchar(50) not null,numOfCredits integer,);constraint greaterThanOnecheck (numOfCredits 1));Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.16

SuperkeySuperkeyKeyCandidatekeyPrimarykeyA superkey is an attribute or a set of attributesthat uniquely identify the relation. That is, notwo tuples have the same values on the superkey.By definition, a relation consists of a set ofdistinct tuples. The set of all attributes in therelation forms a superkey.Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.17

Key and Candidate KeySuperkeyKeyCandidatekeyA key K is a minimal superkey, meaning that anyproper subset of K is not a superkey. It ispossible that a relation has several keys. In thiscase, each of the keys is called a candidate key.PrimarykeyLiang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.18

Primary KeySuperkeyKeyThe primary key is one of the candidate keysdesignated by the database designer. The primarykey is often used to identify tuples in a relation.CandidatekeyPrimarykeycreate table Course(subjectCode char(4),courseNumber int,title varchar(50), numOfCredits intconstraint greaterThanOne check (numOfCredits 1),primary key (subjectCode, courseNumber));Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.19

Primary KeyThe primary key is one of the candidate keys designated bythe database designer. The primary key is often used toidentify tuples in a relation.create table Course (courseId char(5),subjectId char(4) not null,courseNumber integer,title varchar(50) not null,numOfCredits integer,primary key (courseId));Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.20

Primary Key ConstraintsThe primary key constraint specifies that the primary keyvalue of a tuple cannot be null and no two tuples in therelation can have the same value on the primary key. TheDBMS enforces the primary key constraint. For example, ifyou attempt to insert a record with the same primary key asan existing record in the table, the DBMS would report anerror and reject the operation.Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.21

Foreign Key ConstraintsIn a relational database, data are related. Tuples in arelation are related and tuples in different relationsare related through their common attributes.Informally speaking, the common attributes areforeign keys. The foreign key constraints define therelationships among relations.Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.22

Foreign Key Constraints FormalDefinitionFormally, a set of attributes FK is a foreign key in arelation R that references relation T if it satisfies thefollowing two rules: The attributes in FK have the same domain asthe primary key in T. A non-null value on FK in R must match aprimary key value in T.FKRLiang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.T23

Foreign Key Examplecreate table Enrollment (ssn char(9),courseId char(5),dateRegistered date,grade char(1),primary key (ssn, courseId),foreign key (ssn) references Student,foreign key (courseId) references Course);Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.24

Foreign Key DiscussionA foreign key is not necessarily the primary key or partof the primary in the relation. For example, subjectCodeis a foreign key in the Course table that references theSubject table, but it is not the primary key in Course.departmentCode is a foreign key in the Subject tablethat references Department, but it is not the primary keyin Subject.Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.25

Foreign Key Discussion, cont.The referencing relation and the referencedrelation may be the same table. For example,supervisorId is a foreign key in Faculty thatreferences facultyId in Faculty.Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.26

Foreign Key Discussion, cont.The foreign key is not necessary to have thesame name as its referenced primary key as longas they have the same domain. For example,headId is a foreign key in Department thatreferences facultyId in Faculty.Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.27

Foreign Key Discussion, cont.A relation may have more than one foreign key.For example, headId and collegeCode are bothforeign keys in Department.Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.28

SQLStructured Query Language, pronounced S-Q-L, or SequelTo access or write applications for database systems, you needto use the Structured Query Language (SQL). SQL is theuniversal language for accessing relational databasesystems. Application programs may allow users to accessdatabase without directly using SQL, but these applicationsthemselves must use SQL to access the database.Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.29

Examples of simple SQL statementsCreate tableDrop tableDescribe tableSelectInsertDeleteUpdatecreate table Course (courseId char(5),subjectId char(4) not null,courseNumber integer,title varchar(50) not null,numOfCredits integer,primary key (courseId));create table Student (ssn char(9),firstName varchar(25),mi char(1),lastName varchar(25),birthDate date,street varchar(25),phone char(11),zipCode char(5),deptId char(4),primary key (ssn));Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.30

Examples of simple SQL statementsCreate tableDrop tableDescribe tableSelectInsertDeleteUpdatedrop table Enrollment;drop table Course;drop table Student;Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.31

Examples of simple SQL statementsCreate tableDrop tableDescribe tableSelectInsertDeleteUpdatedescribe Course; -- OracleLiang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.32

Examples of simple SQL statementsCreate tableDrop tableDescribe tableSelectInsertDeleteUpdateselect firstName, mi, lastNamefrom Studentwhere deptId 'CS';select firstName, mi, lastNamefrom Studentwhere deptId 'CS' and zipCode '31411';select *from Studentwhere deptId 'CS' and zipCode '31411';Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.33

Examples of simple SQL statementsCreate tableDrop tableDescribe tableSelectInsertDeleteUpdateinsert into Course (courseId, subjectId, courseNumber, title)values ('11113', 'CSCI', '3720', 'Database Systems', 3);Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.34

Examples of simple SQL statementsCreate tableDrop tableDescribe tableSelectInsertUpdateDeleteupdate Courseset numOfCredits 4where title 'Database Systems';Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.35

Examples of simple SQL statementsCreate tableDrop tableDescribe tableSelectInsertUpdateDeletedelete Coursewhere title 'Database System';Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.36

Why Java for Database Programming?First, Java is platform independent. You can developplatform-independent database applications using SQLand Java for any relational database systems. Second, the support for accessing database systems fromJava is built into Java API, so you can create databaseapplications using all Java code with a commoninterface. Third, Java is taught in almost every university either asthe first programming language or as the secondprogramming language. Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.37

Developing JDBC ProgramsLoadingdriversStatement to load a ngconnectionsA driver is a class. For example:Creating cessingResultSetDriver iveroracle.jdbc.driver.OracleDriverSourceAlready in JDKWebsiteWebsiteThe JDBC-ODBC driver for Access is bundled in JDK.MySQL driver class is in mysqljdbc.jarOracle driver class is in classes12.jarTo use the MySQL and Oracle drivers, you have to add mysqljdbc.jar andclasses12.jar in the classpath using the following DOS command onWindows:classpath 2.jarLiang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.41

Developing JDBC ing n connection DriverManager.getConnection(databaseURL);Database URL PatternAccessjdbc:odbc:dataSourceMySQL :@hostname:port#:oracleDBSIDExamples:For Access:See Supplement IV.D forcreating an ODBC data sourceConnection connection ataSource");For MySQL:Connection connection t/test");For Oracle:Connection connection ng.armstrong.edu:1521:orcl", "scott", "tiger");Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.42

Developing JDBC ing andexecutingstatementsProcessingResultSetCreating statement:Statement statement connection.createStatement();Executing statement (for update, delete, insert):statement.executeUpdate("create table Temp (col1 char(5), col2 char(5))");Executing statement (for select):// Select the columns from the Student tableResultSet resultSet statement.executeQuery("select firstName, mi, lastName from Student where lastName " " 'Smith'");Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.43

Developing JDBC ing andexecutingstatementsProcessingResultSetExecuting statement (for select):// Select the columns from the Student tableResultSet resultSet stmt.executeQuery("select firstName, mi, lastName from Student where lastName " " 'Smith'");Processing ResultSet (for select):// Iterate through the result and print the student nameswhile String(1) " " resultSet.getString(2) ". " resultSet.getString(3));Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.44

import java.sql.*;public class SimpleJdbc {public static void main(String[] args)throws SQLException, ClassNotFoundException {// Load the JDBC m.out.println("Driver loaded");SimpleJDBCExample// Establish a connectionConnection connection t/test");System.out.println("Database connected");// Create a statementStatement statement connection.createStatement();// Execute a statementResultSet resultSet statement.executeQuery("select firstName, mi, lastName from Student where lastName " " 'Smith'");// Iterate through the result and print the student nameswhile String(1) "\t" resultSet.getString(2) "\t" resultSet.getString(3));// Close the connectionconnection.close();}}Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.45

Creating ODBC Data SourcePlease follow the steps in Supplement on the CompanionWebsite to create an ODBC data source on Windows.Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.46

Example:Accessing Database from JavaFXThis example demonstrates connecting to adatabase from a Java applet. The applet lets theuser enter the SSN and the course ID to find astudent’s grade.FindGradeRunNOTE: To run this program from here, you need:1.To have a MySQL database setup just like the one in the text.2.Set MySQL JDBC driver in the classpath.Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.47

Processing StatementsOnce a connection to a particular database isestablished, it can be used to send SQL statementsfrom your program to the database. JDBC providesthe Statement, PreparedStatement, andCallableStatement interfaces to facilitate sendingstatements to a database for execution andreceiving execution results from the database.Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.48

The execute, executeQuery, andexecuteUpdate MethodsThe methods for executing SQL statements areexecute, executeQuery, and executeUpdate, eachof which accepts a string containing a SQLstatement as an argument. This string is passed tothe database for execution. The execute methodshould be used if the execution produces multipleresult sets, multiple update counts, or acombination of result sets and update counts.Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.50

The execute, executeQuery, andexecuteUpdate Methods, cont.The executeQuery method should be used ifthe execution produces a single result set,such as the SQL select statement. TheexecuteUpdate method should be used if thestatement results in a single update count orno update count, such as a SQL INSERT,DELETE, UPDATE, or DDL statement.Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.51

PreparedStatementThe PreparedStatement interface is designedto execute dynamic SQL statements andSQL-stored procedures with IN parameters.These SQL statements and stored proceduresare precompiled for efficient use whenrepeatedly executed.Statement pstmt connection.prepareStatement("insert into Student (firstName, mi, lastName) values (?, ?, ?)");Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.52

Example:Using PreparedStatement to ExecuteDynamic SQL StatementsThis example rewrites the preceding exampleusing iang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.Run53

Retrieving Database MetadataDatabase metadata is the information thatdescribes database itself. JDBC provides theDatabaseMetaData interface for obtainingdatabase wide information and theResultSetMetaData interface for obtaining theinformation on the specific ResultSet.Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.54

DatabaseMetadata, cont.The DatabaseMetaData interface providesmore than 100 methods for getting databasemetadata concerning the database as a whole.These methods can be divided into threegroups: for retrieving general information, forfinding database capabilities, and for gettingobject descriptions.Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.55

General InformationThe general information includes the URL,username, product name, product version,driver name, driver version, availablefunctions, available data types and so on.Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.56

Obtaining Database CapabilitiesThe examples of the database capabilities arewhether the database supports the GROUP BYoperator, the ALTER TABLE command withadd column option, supports entry-level or fullANSI92 SQL grammar.Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.57

Obtaining Object Descriptionsthe examples of the database objects aretables, views, and procedures.Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allrights reserved.58

DatabaseMetaData dbMetaData ase URL: " dbMetaData.getURL());System.out.println("database username: " base product name: " intln("database product version: " .println("JDBC driver name: " BC driver version: " "JDBC driver major version: " new m.out.println("JDBC driver minor version: " new m.out.println("Max number of connections: " new t.println("MaxTableNameLentgh: " new m.out.println("MaxColumnsInTable: " new Integer(dbMetaData.getMaxColumnsInTable()));Liang, Introduction to Java Programming, Ninth Edition, (c) 2013 Pearson Education, Inc. Allconnection.close();rights reserved.ExamplesSample run onnext slide59