JDBC Tutorials

This JDBC tutorial book is a collection of notes and sample codes written by the author while he was learning JDBC technology himself. It can be used as a tutorial guide for beginners. Topics include Java, Database, JDBC, Driver, ODBC, Connection, DataSource, SQL, ResultSet, Metadata, Derby, MySQL, Oracle, SQL Server, MS Access.

JDBC (Java Database Connectivity) Introduction

What Is JDBC?

JDBC Version and History

JDBC Driver Types

Establishing Connections from JDBC to Databases

DriverManager – Loading JDBC Driver

DriverManager – Connection URL

Downloading and Installing JDK – Java SE

Java SE 1.6 Update 2 Installation

Compile and Run Java Programs

JDK Documentation Installation

Installing and Running Java DB – Derby

Downloading and Installing Java DB (Derby)

“sysinfo” – Checking Java DB (Derby) Installation

Setting Up Java DB (Derby) in Network Server

“ij” – Client Tool to Create New Databases

Derby (Java DB) JDBC Driver

Derby (Java DB) Driver Features

Loading Derby JDBC Driver Classes

Creating Connections to Java DB (Derby) Network Server

Java DB (Derby) Network Server and JDBC Driver Info

Java DB (Derby) – Creating New Tables

Java DB (Derby) – Inserting Data Rows to Existing Tables

Java DB (Derby) – Running SELECT Queries

Derby (Java DB) JDBC DataSource Objects

Derby – Connection with DataSource Objects

Derby – Using ClientDataSource Directly

Installing JNDI File System Service Provider

Derby – Storing ClientDataSource Objects on File System

Derby – Looking Up ClientDataSource Objects on File System

What Happens If Client JDBC DataSource JAR Is Missing?

Java DB (Derby) – DML Statements

Tables with Primary Key Column “GENERATED … AS IDENTITY”

“INSERT INTO” Statements

“INSERT INTO” Statements with INDENTITY Columns

Handling Date and Timestamp Values

“UPDATE” Statements

“DELETE FROM” Statements

Java DB (Derby) – ResultSet Objects of Queries

What Is ResultSet?

ResultSet Cursor and Scrollability

ResultSet Cursor Initial Position: Before First Row

Retrieving Column Values with getXXX() Methods

ResultSet Default Type: Forward-only

Scrollable ResultSet and Moving Cursor Backward

ResultSet Objects with Update Capability

insertRow() – Inserting New Rows through ResultSet Objects

updateXXX() – Updating Column Values for Row Update or Insert

deleteRow() – Deleting Rows through ResultSet Objects

Java DB (Derby) – PreparedStatement

PreparedStatement Overview

PreparedStatement with Parameters

PreparedStatement in Batch Mode

Performance of Inserting Rows with a PreparedStatement

Performance of Inserting Rows with a Regular Statement

Performance of Inserting Rows with a ResultSet

MySQL Installation on Windows

MySQL 5.0 Download, Installation and Start

mysqladmin – MySQL Admin Tool

mysql – Command Line Tool

Creating Database and User with MySQL Monitor

MySQL JDBC Driver (MySQL Connector/J)

MySQL Connector/J – Download and Installation

Loading JDBC Driver Class – mysql-connector-java-5.0.7-bin.jar

JDBC Driver Connection URL

Creating Connections with DataSource Class

Getting Driver and Server Information

Creating Tables with AUTO_INCREMENT Columns

“INSERT INTO” Statements

MySQL – PreparedStatement

PreparedStatement Overview

PreparedStatement with Parameters

PreparedStatement in Batch Mode

Performance of Inserting Rows with a PreparedStatement

Performance of Inserting Rows with a Regular Statement

Performance of Inserting Rows with a ResultSet

MySQL – Reference Implementation of JdbcRowSet

Overview of RowSet Objects

Installation of JdbcRowSet Reference Implementation

Connecting JdbcRowSet to Database Servers

Connecting JdbcRowSet with a Connection URL

Connecting JdbcRowSet with a Predefined Connection Object

Connecting JdbcRowSet with a Predefined ResultSet Object

Connecting JdbcRowSet with JNDI Directory Service

JdbcRowSet Query Statement with Parameters

Inserting Rows with JdbcRowSet Objects

MySQL – JBDC CallableStatement

Overview of CallableStatement Objects

“CREATE PROCEDURE” – Creating a Simple Procedure

Creating Procedures with IN and OUT Parameters

Creating Procedures with INOUT Parameters

Creating Procedures with Multiple Queries

Creating CallableStatement Objects with prepareCall()

Capturing ResultSet with executeQuery()

Creating CallableStatement Objects with Parameters

Common Errors with CallableStatement Parameters

Creating CallableStatement Objects with INOUT Parameters

Retrieving Multiple ResultSet Objects

Executing Stored Procedures without Permission

getProcedures() – Listing Stored Procedures

Oracle Express Edition Installation on Windows

Oracle Database 10g Express Edition (XE) Installation

Accessing Oracle Server through Web Interface

Creating Oracle Database Users

SQL*Plus – Oracle Command Line Interface

Oracle JDBC Drivers

Oracle JDBC Drivers Overview

JDBC Thin Client-Side Driver Installation

Loading JDBC Driver Class – ojdbc14.jar

JDBC Driver Connection URL

Creating Connections with DataSource Class

DataSource Error – makeURL() Failed

Getting Driver and Server Information

“CREATE TABLE” – Creating New Tables

“INSERT INTO” – Inserting New Data Rows

Oracle – Reference Implementation of JdbcRowSet

Overview of RowSet Objects

Installation of JdbcRowSet Reference Implementation

Connecting JdbcRowSet to Database Servers

Connecting JdbcRowSet with a Connection URL

Connecting JdbcRowSet with a Predefined Connection Object

Connecting JdbcRowSet with a Predefined ResultSet Object

Connecting JdbcRowSet with JNDI Directory Service

JdbcRowSet Query Statement with Parameters

Inserting Rows with JdbcRowSet Objects

Oracle – PreparedStatement

PreparedStatement Overview

PreparedStatement with Parameters

PreparedStatement in Batch Mode

Performance of Inserting Rows with a PreparedStatement

Performance of Inserting Rows with a Regular Statement

Performance of Inserting Rows with a ResultSet

Oracle – JBDC CallableStatement

Overview of CallableStatement Objects

“CREATE PROCEDURE” – Creating a Simple Procedure

Creating Procedures with IN and OUT Parameters

Creating CallableStatement Objects with prepareCall()

Creating CallableStatement Objects with Parameters

getProcedures() – Listing Stored Procedures

Microsoft SQL Server 2005 Express Edition

Downloading Microsoft SQL Server 2005 Express Edition

Installing Microsoft SQL Server 2005 Express Edition

SQLCMD SQL Server Command Line Tool

Installing AdventureWorksLT Sample Database

Microsoft JDBC Driver for SQL Server – sqljdbc.jar

Installing Microsoft JDBC Driver for SQL Server

Loading Driver Class with Class.forName()

DriverManager.getConnection() and Connection URL

Specifying Port Number in Connection URL

Closing the Database Connection – con.close()

Specifying Database Name in Connection URL

Incorrect Database Name in Connection URL

Creating Connections with DataSource Class

Microsoft JDBC Driver – Query Statements and Result Sets

Commonly Used JDBC Class Methods

Calling createStatement() and executeQuery

Receiving ResultSet Objects from executeQuery

Closing ResultSet Objects – res.close()

Looping through ResultSet with res.next()

Retrieving Field Values using res.get*() Methods

Using ResultSetMetaData Objects to List All Fields

Microsoft JDBC Driver – DatabaseMetaData Object

Commonly Used DatabaseMetaData Methods

Getting Database Server and Driver Info

Listing All Databases – getCatalogs()

Listing All Schemas – getSchemas()

Listing All Tables – getTables()

Listing All Culumns – getColumns()

Listing All Stored Procedures – getProcedures()

Microsoft JDBC Driver – DDL Statements

Executing “Update” Statements – executeUpdate()

“CREATE SCHEMA” Statements

“CREATE TABLE” Statements

“ALTER TABLE” Statements

“DROP TABLE” Statements

Microsoft JDBC Driver – DML Statements

“SELECT … INTO” Statements

“INSERT INTO” Statements

“INSERT INTO” Statements with INDENTITY Columns

“UPDATE” Statements

“DELETE FROM” Statements

SQL Server – PreparedStatement

Create a New User in SQL Server

Creating a Table with an IDENTITY Column

Inserting Rows to the Test Table

PreparedStatement Overview

PreparedStatement with Parameters

PreparedStatement in Batch Mode

Performance of Inserting Rows with a PreparedStatement

Performance of Inserting Rows with a Regular Statement

Performance of Inserting Rows with a ResultSet

JDBC-ODBC Bridge Driver – sun.jdbc.odbc.JdbcOdbcDriver

JDBC-ODBC Bridge Driver Features

JDBC-ODBC – Loading sun.jdbc.odbc.JdbcOdbcDriver

JDBC-ODBC – Creating DNS

JDBC-ODBC – Connecting to a DSN

JDBC-ODBC – Problem with Incorrect DSN

JDBC-ODBC Bridge Driver – Flat Text Files

JDBC-ODBC – Creating DNS for Flat Test File

JDBC-ODBC – Connecting to Flat Text Files

JDBC-ODBC – Getting Flat File Driver Info

JDBC-ODBC – CREATE TABLE in Flat Text Files

JDBC-ODBC – Listing Tables with meta.GetTables()

JDBC-ODBC – Tab Delimited Flat File Data

JDBC-ODBC – ODBC Configuration for Flat Files

JDBC-ODBC – Executing Queries on Flat Files

JDBC-ODBC – Missing Flat Data Files

JDBC-ODBC Bridge Driver – MS Access

JDBC-ODBC – Creating a MS Access Database File

JDBC-ODBC – Creating DNS for MS Access

JDBC-ODBC – Connecting to MS Access Database Files

JDBC-ODBC – MS Access Database and Driver Info

JDBC-ODBC – Creating New Tables in MS Access Database

JDBC-ODBC – Inserting Data Rows to MS Access Database

JDBC-ODBC – Running Queries on MS Access Database

Creating Connections with DataSource Class

JDBC-ODBC Bridge Driver – MS SQL Server

JDBC-ODBC – Configuring SQL Server for TCP/IP Connection

JDBC-ODBC – Creating DNS for SQL Server 2005

JDBC-ODBC – Connecting to SQL Server 2005

JDBC-ODBC – SQL Server and Driver Info

JDBC-ODBC – Setting Current Database

JDBC-ODBC – Looping through ResultSet

Summary of JDBC Drivers and Database Servers

JDBC Drivers Tested with Java SE 1.6

Connection URL Formats and Examples

Implementations of the DataSource Interface

Performances on Inserting Rows