Connecting a Java Program to SQL Server


By:   |   Updated: 2017-02-21   |   Comments (1)   |   Related: More > Application Development

Problem

I need to write a Java program to connect to a SQL Server database using Windows authentication and retrieve rows from a SELECT statement. How can I accomplish this?

Solution

Java connects to SQL Server via the Microsoft JDBC Driver. In this tip we will download and install the JDBC driver, set the classpath to the driver, examine the code, and then execute the program from the Windows command prompt.

First, let's download and install the Microsoft JDBC 4 driver. Please remember the path and file name of the driver as it is needed in the next step. In this tip, the installation program was run with Administrator rights and the driver was installed in the directory C:\Program Files\Microsoft JDBC Driver 6.0 for SQL Server.

Install the JDBC driver

Next, we need to add the full path and file name of the Microsoft JDBC 4 driver to the CLASSPATH system environment variable. In this example, the path and file name is C:\Program Files\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\sqljdbc4.jar.

  • Open the Control Panel.
  • Click on System.
  • Click on Advanced System Settings.
Advanced System Settings

Click on Environment Variables...

Click on the Environment Variables of System Properties

Look for a CLASSPATH variable in the System Variables box.

Edit Existing Classpath variable

If a CLASSPATH variable exists, click on CLASSPATH and then Edit. In the Variable Value box, append a semicolon and the full path to the JDBC jar file to the existing CLASSPATH value. Make sure to include the file name. Click on OK to close the Edit System Variable window.

Edit Existing Classpath Value

If CLASSPATH does not exist click on New.

Classpath Is Not Defined

In the Variable Value box enter the full path and file name to the JDBC jar file. Click on OK to close the New System Variable window.

New classpath entered

Now that the CLASSPATH is pointing to the JDBC driver, click on OK to close the Environment Variable window.

Classpath changes finished

Click on OK to close the System Properties window.

We will run the following query in the Java program.

SSMS query results

Now we are ready to examine the Java code. There are comments throughout the code below. Some of the lines of code are long, so please feel free to copy and paste the code below into your favorite Java development environment.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
* Connect to SQL Server, execute a SELECT query, print the results.
*
*/  
public class SelectFromSQLServer
{
  //The SQL Server JDBC Driver is in 
  //C:\Program Files\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\auth\x64
  private static final String jdbcDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
  
  //The JDBC connection URL which allows for Windows authentication is defined below.
  private static final String jdbcURL = "jdbc:sqlserver://localhost:1433;databasename=AdventureWorksDW2014;integratedSecurity=true;";
  //To make Windows authentication work we have to set the path to sqljdbc_auth.dll at the command line
  
  /**
  * main method.
  *
  * @param  args  command line arguments
  */  
  public static void main(String[] args)
  {
    System.out.println("Program started");
    try
    {
       Class.forName(jdbcDriver).newInstance();
       System.out.println("JDBC driver loaded");
    }
    catch (Exception err)
    {
       System.err.println("Error loading JDBC driver");
       err.printStackTrace(System.err);
       System.exit(0);
    }
    
    Connection databaseConnection= null;
    try
    {
      //Connect to the database
      databaseConnection = DriverManager.getConnection(jdbcURL);
      System.out.println("Connected to the database");
    
      //declare the statement object
      Statement sqlStatement = databaseConnection.createStatement();

      //declare the result set    
      ResultSet rs = null;
  
      //Build the query string, making sure to use column aliases
      String queryString="select ";
      queryString+="op.OrganizationName as ParentOrganizationName, ";
      queryString+="oc.OrganizationName as OrganizationName, ";
      queryString+="c.CurrencyName as CurrencyName ";
      queryString+="from dbo.DimOrganization as oc ";
      queryString+="inner join dbo.DimOrganization as op on op.OrganizationKey=oc.ParentOrganizationKey ";
      queryString+="inner join dbo.DimCurrency as c on oc.CurrencyKey=c.CurrencyKey ";
      queryString+="order by ParentOrganizationName, OrganizationName ";

      //print the query string to the screen
      System.out.println("\nQuery string:");
      System.out.println(queryString);
      
      //execute the query
      rs=sqlStatement.executeQuery(queryString);
      
      //print a header row
      System.out.println("\nParentOrganizationName\t|\tOrganizationName\t|\tCurrencyName");
      System.out.println("----------------------\t|\t----------------\t|\t------------");
      
      //loop through the result set and call method to print the result set row
      while (rs.next())
      {
        printResultSetRow(rs);
      }    
      
      //close the result set
      rs.close();
      System.out.println("Closing database connection");

      //close the database connection
      databaseConnection.close();
    }
    catch (SQLException err)
    {
       System.err.println("Error connecting to the database");
       err.printStackTrace(System.err);
       System.exit(0);
    }
    System.out.println("Program finished");
  }
  
  /**
  * Prints each row in the ResultSet object to the screen.
  *
  * @param  rs  the result set from the SELECT query
  * @throws SQLException SQLException thrown on error
  */  
  public static void printResultSetRow(ResultSet rs) throws SQLException
  {
    //Use the column name alias as specified in the above query
    String OrganizationName= rs.getString("OrganizationName");
    String ParentOrganizationName= rs.getString("ParentOrganizationName");
    String CurrencyName= rs.getString("CurrencyName");
    System.out.println(ParentOrganizationName+"\t|\t"+ OrganizationName + "\t|\t" + CurrencyName);  
  }
}

After compiling, it is time to execute and test our Java program. We have to include a path to the sqljdbc_auth.dll file which is required for Windows authentication. At the command line enter the following command.

C:\MSSQLTips\ConnectJavaToSQLServer>java -Djava.library.path="C:\Program Files\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\auth\x64" SelectFromSQLServer

The results are shown below.

Program execution results
Next Steps
  • Please feel free to change the query and the result set handling code.
  • Also, please check out other tips on MSSQLTips.com.


Last Updated: 2017-02-21


get scripts

next tip button



About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, February 21, 2017 - 11:44:20 AM - Jacque Back To Top

 Hi Dallas,

Great tip, what about using a Java program to run SSRS reports? 

Jacque

 



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools