sql

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.

Fig. 1: Java Database Connectivity (JDBC) Architecture
Fig. 1: Java Database Connectivity (JDBC) Architecture

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!

Fig. 2: JDBC MSSQL Application Project Structure
Fig. 2: JDBC MSSQL Application Project Structure

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.

Fig. 3: Create Maven Project
Fig. 3: Create 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.

Fig. 4: Project Details
Fig. 4: Project Details

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.

Fig. 5: Archetype Parameters
Fig. 5: Archetype Parameters

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:

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.

Fig. 6: Java Package Creation
Fig. 6: Java Package Creation

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

Fig. 7: Java Package Name (com.jcg.jdbc.mssql)
Fig. 7: Java Package Name (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.

Fig. 8: Java Class Creation
Fig. 8: Java Class Creation

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.

Fig. 9: Java Class (JdbcMsSql.java)
Fig. 9: Java Class (JdbcMsSql.java)

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.

Fig. 10: Run Application
Fig. 10: Run Application

5. Project Demo

The code shows the following status as output:

Fig. 11: Application Output
Fig. 11: Application 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.

Download
You can download the full source code of this example here: Jdbc Mssql Example

Yatin

An experience full-stack engineer well versed with Core Java, Spring/Springboot, MVC, Security, AOP, Frontend (Angular & React), and cloud technologies (such as AWS, GCP, Jenkins, Docker, K8).
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

4 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
anurag singh
6 years ago

This article is really very useful and simple. It is very well explained the article in detail.
thanks for sharing this post.

Ennis
Ennis
5 years ago

Thank you so match! You are the only one helped me to connect my project to database.

Back to top button