168. JDBC Programming
Version 6.0

This one-day course covers the fundamentals of database programming in Java using JDBC (Java Database connectivity) in an incremental fashion, to get the programmer up to speed quickly and then to layer a deeper understanding of JDBC upon that foundation.  This course has been designed to work with any of these relational database management systems:


    * Apache Derby 10.4

    * MySQL 5.0

    * PostgreSQL 8.3

    * Oracle, version 9i, 10g, or 11g

Lab exercises use SQL99 constructs whenever possible in order to be relevant to databases from all vendors.  The course software also includes an optional overlay of workspace and project files to support use of the Eclipse IDE in the classroom.  (This requires that the instructor be experienced in use of Eclipse and able to walk students through basic tasks in the IDE.)

This revision of the course targets the 6.0 version of the Java language and Core API.  For training targeted to the 1.4 environment, please see version 4.5.1 of this course; to read more about different versions of Java and for help deciding on which version of this course to use, see "Java Versions and Terminology Demystified".

Prerequisites

  * Experience in Java Programming is essential.  The student must be comfortable with object-oriented Java, interfaces and abstract classes.  Course 103, "Java Programming," is excellent preparation.
  * Understanding of basic SQL usage or other work with relational databases will be helpful, but is not required.

Learning Objectives

  * Briefly review relational database fundamentals with an awareness of the differences between SQL99 and SQL implementations by database vendors.
  * Connect to a database using JDBC and perform a simple query. 
  * Update relational data using JDBC to execute updates, inserts and deletes.
  * Use prepared statements to produce reusable database queries and optimize execution time.
  * Use callable statements to access database procedures.  
  * Use scrollable and updatable results sets for more robust solutions.
  * Use commit, rollback, and savepoint to build transactional systems. 
  * Use batch processing for efficient handling of large datasets.  
  * Understand the use of RowSets and the improved techniques and portability that they bring to JDBC.

Timeline: 1 days.

IDE Support: Eclipse Europa

In addition to the primary lab files, an optional overlay is available that adds support for Eclipse Europa.  Students can code and build all exercises from within the IDE.  Most exercises can be tested from within the IDE as well, though some must be tested from the command line.  See also our orientation to Using Capstone's Eclipse Overlays, and please be advised that this is an optional feature; it is not a separate version of the course, and the course itself does not contain explicit Eclipse-specific lab instructions.

Chapter 1. Database and SQL Fundamentals
    Relational Databases and SQL
    SQL Versions and Code Portability
    Database, Schema, Tables, Columns and Rows
    DDL -- Creating and Managing Database Objects
    DML -- Retrieving and Managing Data
    Sequences
    Stored Procedures
    Result Sets and Cursors
    Using SQL Terminals

Chapter 2. JDBC Fundamentals
    What is the JDBC API?
    JDBC Drivers
    Making a Connection
    Creating and Executing a Statement
    Retrieving Values from a ResultSet
    SQL and Java Datatypes
    SQL NULL Versus Java null
    Creating and Updating Tables
    Handling SQL Exceptions and Proper Cleanup
    Handling SQLWarning
    The JDBC 4.0 Cause Facility

Chapter 3. Advanced JDBC
    SQL Escape Syntax
    Using Prepared Statements
    Using Callable Statements
    Scrollable Result Sets
    Updatable Result Sets
    Transactions
    Commits, Rollbacks, and Savepoints
    Batch Processing
    Alternatives to JDBC

Chapter 4. Introduction to Row Sets
    Row Sets in GUI and J2EE programming
    Advantages of RowSets
    RowSet Specializations
    Using CachedRowSets

Appendix A. Learning Resources

Appendix B. Course Schema

Appendix C. Methods and Types

System Requirements

    Hardware, Minimum: 500 MHz, 256 meg RAM, 500 meg disk space.

    Hardware, Recommended: 1.5 GHz, 512 meg RAM, 1 gig disk space.

    Operating System: Tested on Windows XP Professional.  Course software should be viable on all systems which support a Java 6 Developer's Kit.

    Network and Security: Limited privileges required -- please see our standard security requirements at http://capcourse.com/Guides/Security.gen.html.

    Software Requirements:  All free downloadable tools, with an option to use certain commercial products.