How to connect to MSSQL 2005 Database using Type 4 JDBC Driver

Purpose – To obtain a Database connection from Java application using Type 4 JDBC driver

Pre-requisities –
OS – Windows XP – SP 2
Database – MS-SQL 2005 (Express Edition with SQL Server Management studio)

Procedure – 

Step1: Make sure MS-SQL server 2005 is installed and running on Port 1433 (default)

Step2: Add the AdventureWorksDB by downloading and installing the AdventureWorksDB.msi

Step3: Download and install the SQL JDBC driver – sqljdbc_3.0.1301.101_enu.exe

– Make sure the classpath variable is pointing to the sqljdbc4.jar

Step4: Compile and run the below source code to test the connection to Database from Java application

/*
 * Created on Dec 23, 2013
 *
 * TODO To change the template for this generated file go to
 * Window – Preferences – Java – Code Style – Code Templates
 */

/**
 * @author Sudhir
 *
 * TODO To change the template for this generated type comment go to
 * Window – Preferences – Java – Code Style – Code Templates
 */

import java.sql.*;
public class Type4Data {

public static void main(String[] args) throws ClassNotFoundException {

try
{
Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver”);
String connectionUrl = “jdbc:sqlserver://localhost:1433;” +
  “databaseName=AdventureWorks;user=sa;password=XXX;”;
Connection con = DriverManager.getConnection(connectionUrl);
System.out.println(“Connection obtained”);
Statement stm = con.createStatement();
ResultSet rs = stm.executeQuery(“select top 10 * from HumanResources.Employee;”);
while(rs.next())
{
System.out.print(“:” + rs.getString(1));
System.out.print(“:” + rs.getString(2));
System.out.print(“:” + rs.getString(3));
System.out.print(“:” + rs.getString(4));
System.out.print(“:” + rs.getString(5));
System.out.println(“n”);
}

rs.close();
stm.close();
con.close();

}catch (SQLException e)
{
e.printStackTrace();
}

}
}

Step5: Verify the output from the query

Hope you enjoyed reading this article. Thank you.