By: Dallas Snider | Comments (1) | Related: > 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.
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.
Click on Environment Variables...
Look for a CLASSPATH variable in the System Variables box.
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.
If CLASSPATH does not exist click on New.
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.
Now that the CLASSPATH is pointing to the JDBC driver, click on OK to close the Environment Variable window.
Click on OK to close the System Properties window.
We will run the following query in the Java program.
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.
Next Steps
- Please feel free to change the query and the result set handling code.
- Also, please check out other tips on MSSQLTips.com.
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
