Java JDBC MSSQL Connection Example

Java Database Connectivity (JDBC) is a Java-based data access technology that defines how a client may access a database. It provides methods for querying and updating the data in a database. The JDBC classes are contained in the Java package i.e. java.sql and javax.sql.
 
 
 
 
 
 
 
 

1. Introduction

In this article, we will describe how to write the application code to establish a database connection on Microsoft SQL Server from a Java client, using the JDBC API.

1.1 What is JDBC?

JDBC (Java Database Connectivity) is an application programming interface used to communicate or connect the Java application with a database in a database independent and platform independent manner. JDBC provides classes and interfaces to set-up the required communication between the Java application and the database.

JDBC lets a developer manage the below three important programming activities, i.e.:

• Connection to a database.
• Sending queries and update statements to the database.
• Retrieving and processing the results received from the database in answer to the SQL query.

The following simple code fragment gives an example of these three steps:

Example.java

Connection connObj = DriverManager.getConnection("jdbc:myDriver:testDb", "myLoginId", "myPassword");
			  
Statement stmtObj = connObj.createStatement();
ResultSet resObj = stmtObj.executeQuery("SELECT test_id, test_name, test_salary FROM testTable");
while (resObj.next()) {
	int id = resObj.getInt("test_id");
	String name = resObj.getString("test_name");
	float salary = resObj.getFloat("test_salary");
}

1.1.1 What’s new in JDBC 4.0?

JDBC 4.0 is a new specification for JDBC. It provides the following advance features:

• Connection Management.
• Automatic Loading of the Driver Interface.
• Better Exception Handling.
• Support for Large Object.
• Annotations in SQL Query.

1.2 JDBC Database URL for SQL Server

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

Syntax

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

where:

• serverName: Host name or the IP address of the machine on which SQL server is running.
• instanceName: Name of the instance to connect to on a serverName. If this parameter is not specified, the default instance is used.
• portNumber: The default port number for connecting to SQL server 1433. In case this parameter is missing, the default port is used.
• property=value: This parameter specifies one or more additional connection properties. To see the properties specific to the SQL server, visit this link.

Note: Microsoft SQL Server supports two authentication modes i.e.:

• Windows Authentication: This mode is used for the case when both client and the SQL server are running on the same machine. We specify this mode by adding the property integratedSecurity=true to the url.
• SQL Server Authentication: In this mode, we are using the SQL Server account to authenticate with the database. Here in this mode, we have to explicitly specify the username and password.

Below are some examples:

• Connect to a default instance of SQL server running on the same machine as the JDBC client by using Windows authentication

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

• Connect to an instance named: sqlexpress on the host dbServer by using the SQL Server authentication

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

• Connect to a named database: testdb on localhost by using the Windows authentication.

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

1.3 Download and Install Microsoft JDBC Driver

Click here to download the Microsoft JDBC Driver 4.0 for SQL Server which supports:

• SQL Server versions: 2005, 2008, 2008 R2, and 2012.
• Java JDK Version: 5.0 and 6.0.

Run the downloaded program i.e. sqljdbc_<version>_<language>.exe. This will extract the files into the Microsoft JDBC Driver 4.0 for SQL Server directory. Here, we will find two jar files i.e. sqljdbc.jar (for JDBC 3.0) and sqljdbc4.jar (for JDBC 4.0), .dll files, and the HTML help files. Place the required sqljdbc.jar file under the application’s classpath if you are using JDK 5.0 or sqljdbc4.jar file if you are using JDK 6.0 or later.

Now, open up the Eclipse IDE and let’s start building the application!

2. Java JDBC MSSQL Connection Example

2.1 Tools Used

We are using Eclipse Kepler SR2, JDK 7, MSSQL database and Maven (to download the open source MSSQL JDBC Client). Having said that, we have tested the code against JDK 1.7 and it works well.

2.2 Project Structure

Firstly, let’s review the final project structure, in case you are confused about where you should create the corresponding files or folder later!

2.3 Project Creation

This section will demonstrate on how to create a Java based Maven project with Eclipse. In Eclipse IDE, go to File -> New -> Maven Project.

In the New Maven Project window, it will ask you to select project location. By default, ‘Use default workspace location‘ will be selected. Select the ‘Create a simple project (skip archetype selection)‘ checkbox and just click on next button to proceed.

It will ask you to ‘Enter the group and the artifact id’. We will input the details as shown in the below image. Version number will be by default 0.0.1-SNAPSHOT.

Click on Finish and the creation of the maven project is completed. If you observe, it has downloaded the maven dependencies and a pom.xml file will be created. It will have the following code:

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>JdbcMssqlExample</groupId>
	<artifactId>JdbcMssqlExample</artifactId>
	<version>0.0.1-SNAPSHOT</version>
</project>

We can start adding the dependencies that developers want like MSSQL etc. Let’s start building the application!

3. Application Building

Below are the steps involved in developing this application:

Want to be a JDBC Master ?

Subscribe to our newsletter and download the JDBC Ultimate Guide right now!

In order to help you master database programming with JDBC, we have compiled a kick-ass guide with all the major JDBC features and use cases! Besides studying them online you may download the eBook in PDF format!

Download NOW!

Thank you!

We will contact you soon.

3.1 Maven Dependencies

In this example, we are using the latest MSSQL version i.e. sqljdbc4-4.0 dependency. The updated file will have the following code:

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>JdbcMssqlExample</groupId>
	<artifactId>JdbcMssqlExample</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>
	<dependencies>
		<dependency>
			<groupId>sqljdbc</groupId>
			<artifactId>sqljdbc4-4.0</artifactId>
			<version>4-4.0</version>
			<scope>system</scope>
			<systemPath>${project.basedir}/src/main/resources/sqljdbc4-4.0.jar</systemPath>
		</dependency>
	</dependencies>
	<build>
		<finalName>${project.artifactId}</finalName>
	</build>
</project>

3.2 Java Class Creation

Let’s create the required java files. Right click on src/main/java folder, New -> Package.

A new pop window will open where we will enter the package name as com.jcg.jdbc.mssql.

Once the package is created in the application, we will need to create the implementation class. Right click on the newly created package, New -> Class.

A new pop window will open and enter the file name as JdbcMsSql. The implementation class will be created inside the package: com.jcg.jdbc.mssql.

3.2.1 Implementation of Main Class

To demonstrate, we will create a program that connects to a Microsoft SQL Server instance on the localhost environment and print out the database information. Add the following code to it:

JdbcMsSql.java

package com.jcg.jdbc.mssql;

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

public class JdbcMsSql {

	public static Connection connObj;
	public static String JDBC_URL = "jdbc:sqlserver://localhost:1433;databaseName=tutorialDb;integratedSecurity=true";

	public static void getDbConnection() {
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			connObj = DriverManager.getConnection(JDBC_URL);
			if(connObj != null) {
				DatabaseMetaData metaObj = (DatabaseMetaData) connObj.getMetaData();
				System.out.println("Driver Name?= " + metaObj.getDriverName() + ", Driver Version?= " + metaObj.getDriverVersion() + ", Product Name?= " + metaObj.getDatabaseProductName() + ", Product Version?= " + metaObj.getDatabaseProductVersion());
			}
		} catch(Exception sqlException) {
			sqlException.printStackTrace();
		}
	}

	public static void main(String[] args) {
		getDbConnection();
	}
}

4. Run the Application

To run the application, Right click on the JdbcMsSql class, Run As -> Java Application.

5. Project Demo

The code shows the following status as output:

That’s all for this post. Happy Learning!!

6. Conclusion

Here, in this example, we have seen how to establish a JDBC connection between a MSSQL Database and a Java application by using SQL Server Windows Authentication. I hope this simple SQL Server JDBC reference is helpful.

7. Download the Eclipse Project

This was an example of JDBC MSSQL Database Connectivity.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.