copyright © 2002 prosofttraining. all rights reserved. building database client applications using...
TRANSCRIPT
![Page 1: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/1.jpg)
Copyright © 2002 ProsoftTraining. All rights reserved.
Building Database Client Applications Using JDBC 2.0
![Page 2: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/2.jpg)
Copyright © 2002 ProsoftTraining. All rights reserved.
Lesson 1:Introduction
to JDBC
![Page 3: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/3.jpg)
Objectives
• Describe the purpose of JDBC• Discuss the structure of a relational database• Describe the purpose of SQL• Discuss the client-server architecture• Describe the JDBC architecture
![Page 4: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/4.jpg)
JDBC—The Basics
• Sun Java database access API• Microsoft Open Database Connectivity
![Page 5: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/5.jpg)
RelationalDatabases
• Database management systems• Primary keys• Joins
![Page 6: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/6.jpg)
StructuredQuery Language
• DDL• DML• DCL• SQL data types
![Page 7: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/7.jpg)
SystemArchitecture
• Client/server architecture• Three-tier architecture
![Page 8: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/8.jpg)
Summary
Describe the purpose of JDBC Discuss the structure of a relational database Describe the purpose of SQL Discuss the client-server architecture Describe the JDBC architecture
![Page 9: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/9.jpg)
Copyright © 2002 ProsoftTraining. All rights reserved.
Lesson 2:Using JDBC Drivers
![Page 10: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/10.jpg)
Objectives
• Describe the various types of JDBC drivers• Select a JDBC driver• Load a JDBC driver• Establish a connection with a database
![Page 11: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/11.jpg)
JDBCDrivers
• JDBC-ODBC bridge driver• Native-API driver• JDBC-Net driver• Pure Java driver• Loading a driver
![Page 12: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/12.jpg)
Establishinga Connection
• JDBC URLs• The Connection interface
![Page 13: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/13.jpg)
Summary
Describe the various types of JDBC drivers Select a JDBC driver Load a JDBC driver Establish a connection with a database
![Page 14: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/14.jpg)
Copyright © 2002 ProsoftTraining. All rights reserved.
Lesson 3:Executing SQL Statements
![Page 15: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/15.jpg)
Objectives
• Define tables in a database using JDBC• Add new rows to a table using JDBC• Perform queries on a database using JDBC• Extract data from a ResultSet object• Describe the purpose of transactions• Use transactions to maintain data consistency
![Page 16: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/16.jpg)
TheStatement Interface
• Statement interface methods• executeUpdate• executeQuery
![Page 17: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/17.jpg)
TransactionsUsing SQL Statements
• Allow you to group statements so that changes do not take effect until both tables are updated
![Page 18: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/18.jpg)
Summary
Define tables in a database using JDBC Add new rows to a table using JDBC Perform queries on a database using JDBC Extract data from a ResultSet object Describe the purpose of transactions Use transactions to maintain data consistency
![Page 19: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/19.jpg)
Copyright © 2002 ProsoftTraining. All rights reserved.
Lesson 4:Prepared Statements
and Stored Procedures
![Page 20: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/20.jpg)
Objectives
• Describe the purpose of prepared statements• Use prepared statements to increase
efficiency• Describe the purpose of stored procedures• Use stored procedures
![Page 21: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/21.jpg)
PreparedStatements
• PreparedStatement interface• Prepared statement parameters• Executing prepared statements
![Page 22: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/22.jpg)
StoredProcedures
• Precompiled• Stored within the DBMS• Remain permanently within the DBMS
![Page 23: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/23.jpg)
Summary
Describe the purpose of prepared statements Use prepared statements to increase
efficiency Describe the purpose of stored procedures Use stored procedures
![Page 24: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/24.jpg)
Copyright © 2002 ProsoftTraining. All rights reserved.
Lesson 5:Metadata
![Page 25: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/25.jpg)
Objectives
• Describe the purpose of metadata• Extract metadata from a DBMS or database• Extract metadata from a result set
![Page 26: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/26.jpg)
Introductionto Metadata
• Metadata: data about data
![Page 27: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/27.jpg)
DatabaseMetaData
• Determining DBMS product information• Determining supported features• Determining the database schema
![Page 28: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/28.jpg)
ResultSetMetaData
• Extracting metadata from a result set
![Page 29: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/29.jpg)
Summary
Describe the purpose of metadata Extract metadata from a DBMS or database Extract metadata from a result set
![Page 30: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/30.jpg)
Copyright © 2002 ProsoftTraining. All rights reserved.
Lesson 6:SQLExceptions
![Page 31: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/31.jpg)
Objectives
• Describe the purpose of SQLExceptions• Catch SQLExceptions• Determine the cause of a SQLException• Recover efficiently from a SQLException
![Page 32: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/32.jpg)
Introduction to SQLExceptions
• SQLException class helps determine when an error occurs and what type of error has occurred
![Page 33: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/33.jpg)
try and catch Blocks
• catch block accepts an exception and provides some handling for recovering from an exception
• try block may have multiple associated catch blocks
![Page 34: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/34.jpg)
SQLExceptionMethods
• String getMessage( );• String getSQLState( );• int getErrorCode( );• SQLException getNextException( );
![Page 35: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/35.jpg)
Summary
Describe the purpose of SQLExceptions Catch SQLExceptions Determine the cause of a SQLException Recover efficiently from a SQLException
![Page 36: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/36.jpg)
Copyright © 2002 ProsoftTraining. All rights reserved.
Lesson 7:Scrollable and
Updateable Result Sets
![Page 37: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/37.jpg)
Objectives
• Describe the purpose of scrollable result sets• Create and use a scrollable result set• Describe the purpose of updateable result
sets• Create and use an updateable result set
![Page 38: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/38.jpg)
ScrollableResult Sets
• Creating a scrollable result set• Result set type constants• Scrolling through a scrollable result set
![Page 39: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/39.jpg)
UpdateableResult Sets
• Creating an updateable result set• Updating a result set• Inserting a row• Deleting a row
![Page 40: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/40.jpg)
Summary
Describe the purpose of scrollable result sets Create and use a scrollable result set Describe the purpose of updateable result
sets Create and use an updateable result set
![Page 41: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/41.jpg)
Copyright © 2002 ProsoftTraining. All rights reserved.
Lesson 8:Batch Updates
![Page 42: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/42.jpg)
Objectives
• Describe the purpose of batch updates• Perform a batch update• Perform a parameterized batch update• Catch a BatchUpdateException
![Page 43: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/43.jpg)
Introductionto Batch Updates
• Using batch updates– addBatch– executeBatch– clearBatch
![Page 44: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/44.jpg)
ParameterizedBatch Updates
• Combine the benefits of batch updates with those of prepared statements
![Page 45: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/45.jpg)
TheBatchUpdateException Class
• Performing batch updates
![Page 46: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/46.jpg)
Summary
Describe the purpose of batch updates Perform a batch update Perform a parameterized batch update Catch a BatchUpdateException
![Page 47: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/47.jpg)
Building Database Client Applications Using JDBC 2.0
Introduction to JDBC Using JDBC Drivers Executing SQL Statements Prepared Statements and Stored Procedures Metadata
![Page 48: Copyright © 2002 ProsoftTraining. All rights reserved. Building Database Client Applications Using JDBC 2.0](https://reader035.vdocuments.us/reader035/viewer/2022070403/56649f295503460f94c41f76/html5/thumbnails/48.jpg)
Building Database Client Applications Using JDBC 2.0
SQLExceptions Scrollable and Updateable Result Sets Batch Updates