Java Connect to Microsoft SQL Server Example

Details
Written by  
Last Updated on 13 March 2020   |   Print  Email


This JDBC tutorial helps you understand how to get JDBC driver and write code for making database connection to Microsoft SQL Server from a Java client. Suppose you have a light weight version of SQL Server installed, such as Microsoft SQL Server Express.

For visua howtos, watch this video.

Table of content:

    1. Download Microsoft JDBC driver
    2. JDBC database URL for SQL Server
    3. Register JDBC driver for SQL Server and establish connection
    4. Example program

 

1. Download Microsoft JDBC driver for SQL server

To enable a Java program connects to Microsoft SQL Server database, we need to have a suitable JDBC driver present in the classpath. Click here to download the latest version of Microsoft JDBC Driver for SQL Server. Currently, the latest version is Microsoft JDBC driver 8.2 which supports Java 8, 11 and 13.

Extract the downloaded archive file, and put the mssql-jdbc-8.2.0.jreVERSION.jar to your project's classpath. If you use Maven, then declare the following dependency:

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>8.2.1.jre11</version>
</dependency>

  

2. JDBC database URL for SQL Server

The syntax of database URL for SQL Server is as follows:

jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

Where:

NOTE:SQL Server has two authentication modes:

Following are some examples:

-          Connect to default instance of SQL server running on the same machine as the JDBC client, using Windows authentication:

jdbc:sqlserver://localhost;integratedSecurity=true;

-          Connect to an instance named sqlexpress on the host dbServer, using SQL Server authentication:

jdbc:sqlserver://dbHost\sqlexpress;user=sa;password=secret

-          Connect to a named database testdb on localhost using Windows authentication:

jdbc:sqlserver://localhost:1433;databaseName=testdb;integratedSecurity=true;

 

3. Register JDBC driver for SQL Server and establish connection

The JDBC driver class of SQL Server is com.microsoft.sqlserver.jdbc.SQLServerDriver, so to register this driver, use the following statement:

DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

Or:

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

However, that is not required since JDBC 4.0 (JDK 6.0) because the driver manager can detect and load the driver class automatically as long as a suitable JDBC driver present in the classpath.

To make a connection, call the method getConnection() of the DriverManager class. Here is a code snippet that connects the user sa with password secret to the instance sqlexpress on localhost:

String dbURL = "jdbc:sqlserver://localhost\\sqlexpress;user=sa;password=secret";
Connection conn = DriverManager.getConnection(dbURL);
if (conn != null) {
	System.out.println("Connected");
}

The following code passes username and password as arguments to the method getConnection():

String dbURL = "jdbc:sqlserver://localhost\\sqlexpress";
String user = "sa";
String pass = "secret";
conn = DriverManager.getConnection(dbURL, user, pass);

 

We can also use a java.util.Properties object to store connection properties, as in the following example:

String dbURL = "jdbc:sqlserver://localhost\\sqlexpress";
Properties properties = new Properties();
properties.put("user", "sa");
properties.put("password", "secret");
conn = DriverManager.getConnection(dbURL, properties);

NOTE: if you want to use Windows authentication mode (integratedSecurity=true), you must have the sqljdbc_auth.dll in the classpath.


4. Java Code Example to connect to SQL Server

To demonstrate, we create a small program that connects to an SQL Server instance on localhost and print out some database information as follows:

package net.codejava.jdbc;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * This program demonstrates how to establish database connection to Microsoft
 * SQL Server.
 * @author www.codejava.net
 *
 */
public class JdbcSQLServerConnection {

	public static void main(String[] args) {

		Connection conn = null;

		try {

			String dbURL = "jdbc:sqlserver://localhost\\sqlexpress";
			String user = "sa";
			String pass = "secret";
			conn = DriverManager.getConnection(dbURL, user, pass);
			if (conn != null) {
				DatabaseMetaData dm = (DatabaseMetaData) conn.getMetaData();
				System.out.println("Driver name: " + dm.getDriverName());
				System.out.println("Driver version: " + dm.getDriverVersion());
				System.out.println("Product name: " + dm.getDatabaseProductName());
				System.out.println("Product version: " + dm.getDatabaseProductVersion());
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (conn != null && !conn.isClosed()) {
					conn.close();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
	}
}

The program would produce the following output:

Driver name: Microsoft JDBC Driver 8.2 for SQL Server
Driver version: 8.2.0.0
Product name: Microsoft SQL Server
Product version: 15.00.2000

That's some Java code example to establish database connection to Microsoft SQL Server. For a video guide, you can watch the following video:

 

JDBC API References:

 

Related JDBC Tutorials:


About the Author:

is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.

Attachments:
Download this file (JdbcSQLServerConnection.java)JdbcSQLServerConnection.java[Example program]1 kB