JDBC for SQL Server - Herong's Tutorial Examples

https://www.herongyang.com/JDBC-SQL-Server

Copyright © 2003-2024 Herong Yang. All rights reserved.

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. Topics include installing JDK and SQL Server; using SQL Server JDBC and JDBC-ODBC Bridge drivers; database connection URLs; introduction to Result Set, Meta Data, Prepared Statement, CLOB, and BLOB; connection pooling with DBCP and C3P0. Updated in 2024 (Version v3.14) with minor changes.

Table of Contents

About This Book

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

JDK (Java SE) Installation

Downloading and Installing JDK

Adding JDK "bin" Directory to Path Setting

Downloading and Installing JDK on Mac

Downloading and Installing JDK on Linux

Compile and Run Java Programs

-cp or -classpath Syntax on Different Systems

JDK Documentation Installation

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()

"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

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

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

C3P0 for Connection Pooling

Connection Pooling with C3P0 ComboPooledDataSource

Connection Pooling with C3P0 DataSources

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

JDBC-ODBC Bridge Driver Features

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

JDBC-ODBC - Creating DSN

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

Archived Tutorials

Archived: Downloading and Installing JDK on Windows

Archived: Java SE 8 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

Archived: Installing Microsoft JDBC Driver 1.0

References

Full Version in PDF/EPUB

Keywords: JDBC, Java, Database, Tutorial, Example, Book