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.
 
 
 
 
 
 
 
 

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!

Thank you!

We will contact you soon.

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

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

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:

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:

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

Below are some examples:

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:

    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

    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

    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

    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

    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

    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)

    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

    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)

    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

    5. Project Demo

    The code shows the following status as 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
    Exit mobile version