Java and MS SQL Server with JDBC Connection

We will need to add a jar file to our project library as SQL Server Driver for JDBC Service. The jar file is called “sqljdbc4.jar”.  This jar file can be downloaded from the following link:

http://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx.

The java.sql package contains API that performs all JDBC operations such as create and execute sql queries. The following are the important classes of java.sql package :

 Make Connection to database:

java.sql.DriverManager class makes connection with a driver.

Java.sql.Driver interface: provides API for registering and connecting drivers based on JDBC technology.

Send SQl statement to database:

Java.sql.Statement: send basic sql statement

Java.sql.Connection interface -- provides methods for creating statements and managing connections and their properties.

Retieve and Update Data:

Java.sql.Resultset Interface – provide access the result row by row.

There are two different authentication mode to connect to SQL server.

Windows Authentication Mode:

The first step is to setup ODBC. Start- Control panel -> Administrative tools -> ODBC -> add  select  “sql server” under name column. It will bring “create a new data source to Sql Server” window. Enter name , description and select the server from drop down box you like to connect. Click “next” and select “with windows NT authentication using the network login id” option and make sure to check “connect to Sql server to obtain default settings for the addition configuration options”. Click “next” and select the “change the default database to” option and select database from drop down box. Click “next” and then click “finish”. It will show “ODBC Microsoft SQL Server set up” window with server name, database name. Click “Test Data source” and it will show test result completed successfully.

Now the ODBC connection is completed. We’ll point to the Database connection string as jdbc:odbc:dsn name.

Code sample:

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class sqlconn {

           

            public static void main(String[] args)

            {

                         String DriverName="com.microsoft.sqlserver.jdbc.SQLServerDriver";

                         String connectionString="jdbc:odbc:mydb;

       

                         Connection con = null;

                         Statement query = null;

                    ResultSet rs = null;

                  try {

                        //dynamically load driver’s class file into memory                     

                          Class.forName(DriverName);

                    

                            // establish a connection

                          con=DriverManager.getConnection(connectionString);

                     

                      // Create and execute an SQL statement that returns some data.

                      String SQL = "SELECT TOP 10 * FROM Diagnosis";

                      query = con.createStatement();

                      rs = query.executeQuery(SQL);

                      // Iterate through the data in the result set and display it.

                      while (rs.next()) {

                         System.out.println("Diagnosis:" + rs.getString(4) + " " +         "DxCode:" + rs.getString(5));

                      }

                 

            } catch(ClassNotFoundException e) {

                        e.printStackTrace();

            } catch (Exception e) {

                          e.printStackTrace();

                       }

                      // close connection

                       finally {

                          if (rs != null) try { rs.close(); } catch(Exception e) {}

                          if (query != null) try { query.close(); } catch(Exception e) {}

                          if (con != null) try { con.close(); } catch(Exception e) {}

                       }

                    }

                 }

Output:

Diagnosis:CHOLERA DUE TO VIBRIO CHOLERAE DxCode:001.0

Diagnosis:CHOLERA DUE TO VIBRIO CHOLERAE EL TOR DxCode:001.1

Diagnosis:CHOLERA, UNSPECIFIED DxCode:001.9

Diagnosis:TYPHOID AND PARATYPHOID FEVERS DxCode:002

Diagnosis:TYPHOID FEVER DxCode:002.0

Diagnosis:PARATYPHOID FEVER A DxCode:002.1

Diagnosis:PARATYPHOID FEVER B DxCode:002.2

(2)Sql server Authentication Mode: We need to provide User credentials to connection string.

Just change the connection string as the following sample:

String connectionString="jdbc:sqlserver://yourSqlServerName;user=youruserid;password=yourpasswordpassword;databasename=yourdatabasename”;