JDBC Tutorials - Herong's Tutorial Examples - v3.12, by Dr. Herong Yang
JDBC Tutorials - Herong's Tutorial Examples
https://www.herongyang.com/JDBC
© 2022 Dr. Herong Yang. All rights reserved.
This JDBC tutorial book is a collection of notes and sample codes written by the author while he was learning JDBC technology himself. Topics include introduction to JDBC driver; installing JDK on Windows and other systems; Using Derby (Java DB) JDBC Driver; Using MySQL JDBC Driver (MySQL Connector/J); Using Oracle JDBC Driver; Using SQL Server JDBC Driver; Using JDBC-ODBC Bridge Driver. Updated in 2022 (Version v3.12) with minor changes.
Table of Contents
JDBC (Java Database Connectivity) Introduction
Establishing Connections from JDBC to Databases
DriverManager - Loading JDBC Driver
DriverManager - Connection URL
Downloading and Installing JDK
Adding JDK "bin" Directory to Path Setting
Downloading and Installing JDK on Mac
Downloading and Installing JDK on Linux
-cp or -classpath Syntax on Different Systems
JDK Documentation Installation
Installing and Running Java DB - Derby
Downloading and Installing Java DB (Derby)
Java DB (Derby) in the JDK 1.8 Package
Downloading Java DB (Derby) Documents
"sysinfo" - Checking Java DB (Derby) Installation
Setting Up Java DB (Derby) in Network Server
"ij" - Client Tool to Create New Databases
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 with INDENTITY Columns
Handling Date and Timestamp Values
Java DB (Derby) - ResultSet Objects of Queries
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 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
Creating Database and User with MySQL Monitor
MySQL JDBC Driver (MySQL Connector/J)
MySQL Connector/J - Download and Installation
Loading JDBC Driver for MySQL Server
Creating Connections with DataSource Class
Getting Driver and Server Information
Creating Tables with AUTO_INCREMENT Columns
PreparedStatement with Parameters
PreparedStatement in Batch Mode
Performance of Inserting Rows with a PreparedStatement
InnoDB (MySQL 5.5 Default Engine) Slower on INSERT
Performance of Inserting Rows with a Regular Statement
Performance of Inserting Rows with a ResultSet
MySQL - Reference Implementation of JdbcRowSet
Connecting JdbcRowSet to Database Servers
Sun Implementation of JdbcRowSet API
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
MySQL CLOB (Character Large Object) - TEXT
Overview of CLOB (Character Large Object)
Create Tables with CLOB Columns
Inserting CLOB Values with SQL INSERT Statements
Inserting CLOB Values with setString() Method
Inserting CLOB Values with setCharacterStream() Method
Retrieving CLOB Values with getString() Method
Retrieving CLOB Values with getCharacterStream() Method
Retrieving CLOB Values with getClob() Method
Inserting CLOB Values with setClob() Method
MySQL BLOB (Binary Large Object) - BLOB
Oracle Express Edition Installation on Windows
Oracle Database Express Edition (XE) Installation
Accessing Oracle Server through Web Interface
Creating Oracle Database Users
SQL*Plus - Oracle Command Line Interface
SQL*Plus - Create New User and Login
JDBC Thin Client-Side Driver Installation
Loading JDBC Driver Class - ojdbc16.jar
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
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
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
Oracle CLOB (Character Large Object) - TEXT
Oracle BLOB (Binary Large Object) - BLOB
Overview of BLOB (Binary Large Object)
Create Tables with CLOB Columns
Inserting BLOB Values with SQL INSERT Statements
Inserting BLOB Values with setBytes() Method
Inserting BLOB Values with setBinaryStream() Method
Closing InputStream Too Early on setBinaryStream()
Retrieving BLOB Values with getBytes() Method
Retrieving BLOB Values with getBinaryStream() Method
Retrieving BLOB Values with getBlob() Method
Inserting BLOB Values with setBlob() Method
Copying BLOB Values to New Rows
Microsoft SQL Server Express Edition
What Is Microsoft SQL Server Express Edition
Installing Microsoft SQL Server Express Edition
Installing Microsoft SQL Server 2014 Express Edition
SQLCMD SQL Server Command Line Tool
Installing AdventureWorks Sample Database
Create Login User in SQL Server
Microsoft JDBC Driver for SQL Server
Installing Microsoft JDBC Driver for SQL Server
Loading Driver Class Automatically
Loading Driver Class with Class.forName()
DriverManager.getConnection() and Connection URL
Enable TCP/IP with SQL Server Configuration Manager
Specifying Port Number in Connection URL
Instance Name Better than Port Number
Specifying Instance Name 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()
Microsoft JDBC Driver - DML Statements
"INSERT INTO" Statements with INDENTITY Columns
SQL Server - PreparedStatement
SQL Server CLOB (Character Large Object) - TEXT
Overview of CLOB (Character Large Object)
Create Tables with CLOB Columns
Inserting CLOB Values with SQL INSERT Statements
Inserting CLOB Values with setString() Method
Inserting CLOB Values with setCharacterStream() Method
Closing InputStream Too Early on setCharacterStream()
Retrieving CLOB Values with getString() Method
Retrieving CLOB Values with getCharacterStream() Method
Retrieving CLOB Values with getClob() Method
Inserting CLOB Values with setClob() Method
SQL Server BLOB (Binary Large Object) - BLOB
Overview of BLOB (Binary Large Object)
Create Tables with CLOB Columns
Inserting BLOB Values with SQL INSERT Statements
Inserting BLOB Values with setBytes() Method
Inserting BLOB Values with setBinaryStream() Method
Closing InputStream Too Early on setBinaryStream()
Retrieving BLOB Values with getBytes() Method
Retrieving BLOB Values with getBinaryStream() Method
Retrieving BLOB Values with getBlob() Method
Inserting BLOB Values with setBlob() Method
JDBC-ODBC Bridge Driver - sun.jdbc.odbc.JdbcOdbcDriver
JDBC-ODBC Bridge Driver Features
JDBC-ODBC - Loading sun.jdbc.odbc.JdbcOdbcDriver
JDBC-ODBC - Connecting to a DSN
JDBC-ODBC - Problem with Incorrect DSN
JDBC-ODBC Bridge Driver - Flat Text Files
JDBC-ODBC - Creating DSN 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 DSN 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 DSN 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
Connection URL Formats and Examples
Implementations of the DataSource Interface
Performances on Inserting Rows
Using Connection Pool with JDBC
What Is Database Connection Pool
Commons DBCP for Connection Pooling
Connection Pooling with Commons DBCP BasicDataSource
Connection Pooling with Commons DBCP PoolingDriver
Connection Pooling with C3P0 ComboPooledDataSource
Connection Pooling with C3P0 DataSources
Archived: Downloading and Installing JDK on Windows
Archived: Java SE 8 Installation
Archived: Java SE 1.6 Update 2 Installation
Archived: JDK 1.6 Documentation Installation
Archived: Downloading/Installing Java DB (Derby) 10.2.2
Archived: "sysinfo" - Checking Java DB Installation 10.2.2
Archived: MySQL Connector/J 5.1 - Download and Installation
Archived: MySQL 5.0 Download, Installation and Start
Archived: mysqladmin - MySQL 5.0 Admin Tool
Archived: MySQL Connector/J 5.0 - Download and Installation
Archived: Loading MySQL Connector Java 5.0
Archived: Installation of JdbcRowSet Reference Implementation
Archived: Oracle Database Express Edition (XE) Installation
Archived: Accessing Oracle Server through Web Interface
Archived: Creating Oracle Database Users
Archived: SQL*Plus - Oracle Command Line Interface
Archived: JDBC Thin Client-Side Driver Installation
Archived: Downloading Microsoft SQL Server 2005 Express Edition
Archived: Installing Microsoft SQL Server 2005 Express Edition
Archived: Installing AdventureWorksLT Sample Database
Archived: Installing Microsoft JDBC Driver 4.2
Keywords: JDBC, Java, Database, Tutorial, Example, Book