JDBC for SQL Server - Herong's Tutorial Examples - v3.14, by Herong Yang
Creating Connections with DataSource Class
This section describes how to create connection objects with the DataSource class.
It is recommended now that connection objects are created by the DataSource implementation class, com.microsoft.sqlserver.jdbc.SQLServerDataSource. Here is a sample program that creates a connection object using the DataSource class without using JNDI services:
/* SqlServerDataSource.java * Copyright (c) HerongYang.com. All Rights Reserved. */ import java.sql.*; public class SqlServerDataSource { public static void main(String [] args) { Connection con = null; try { // Setting up the DataSource object com.microsoft.sqlserver.jdbc.SQLServerDataSource ds = new com.microsoft.sqlserver.jdbc.SQLServerDataSource(); ds.setServerName("localhost"); // ds.setPortNumber(60782); ds.setInstanceName("SQLEXPRESS"); ds.setDatabaseName("AdventureWorks2019"); ds.setUser("herong"); ds.setPassword("T0pSecret"); // Getting a connection object con = ds.getConnection(); // Database and driver info DatabaseMetaData meta = con.getMetaData(); System.out.println("Server name: " + meta.getDatabaseProductName()); System.out.println("Server version: " + meta.getDatabaseProductVersion()); System.out.println("Driver name: " + meta.getDriverName()); System.out.println("Driver version: " + meta.getDriverVersion()); System.out.println("JDBC major version: " + meta.getJDBCMajorVersion()); System.out.println("JDBC minor version: " + meta.getJDBCMinorVersion()); // Closing the connection con.close(); } catch (Exception e) { System.err.println("Exception: "+e.getMessage()); } } }
Run the above program with the latest JDK, JDBC driver and SQL Server. The output confirms that mssql-jdbc-9.4.1.jre16.jar supports JDBC API 4.3.
herong> java -cp .;mssql-jdbc-9.4.1.jre16.jar SqlServerDataSource Server name: Microsoft SQL Server Server version: 15.00.2000 Driver name: Microsoft JDBC Driver 9.4 for SQL Server Driver version: 9.4.1.0 JDBC major version: 4 JDBC minor version: 3
Running the same program with other versions of JDBC drivers gives different outputs:
herong> java -cp .:mssql-jdbc-7.4.1.jre12.jar SqlServerDataSource Server name: Microsoft SQL Server Server version: 11.00.3128 Driver name: Microsoft JDBC Driver 7.4 for SQL Server Driver version: 7.4.1.0 JDBC major version: 4 JDBC minor version: 3 herong> java -cp .;sqljdbc42.jar SqlServerDataSource Server name: Microsoft SQL Server Server version: 12.00.2000 Driver name: Microsoft JDBC Driver 4.2 for SQL Server Driver version: 4.2.6225.100 JDBC major version: 4 JDBC minor version: 2
Table of Contents
JDBC (Java Database Connectivity) Introduction
Microsoft SQL Server Express Edition
►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
Microsoft JDBC Driver - DatabaseMetaData Object
Microsoft JDBC Driver - DDL Statements
Microsoft JDBC Driver - DML Statements
SQL Server - PreparedStatement
SQL Server CLOB (Character Large Object) - TEXT
SQL Server BLOB (Binary Large Object) - BLOB
Using Connection Pool with JDBC
JDBC-ODBC Bridge Driver - sun.jdbc.odbc.JdbcOdbcDriver
JDBC-ODBC Bridge Driver - Flat Text Files
JDBC-ODBC Bridge Driver - MS Access