How to Connect to MS SQL Server via JDBC
There are two main drivers that can be used to connect to Microsoft SQL Server via JDBC. They are the SQL Server JDBC driver from Microsoft and the open source jTDS driver.
Newer versions of MS SQL Server, especially those that use SSL encryption to communicate to and from the database server, may not work correctly with the jTDS driver. There are some issues with the jTDS driver, the Jave runtime environment, and the handling of SSL/TLS communications with certain SQL Server configurations. If the jTDS driver is not able to connect, generally the MS SQL Server driver from Microsoft will work. The only major difference between the two drivers is that when connecting to SQL Server from non-Windows systems, the MS SQL Server driver does not support Windows authentication. It requires SQL Server authentication. The jTDS driver does support Windows authentication from non-Windows systems.
Both the Microsoft SQL Server JDBC driver and the jTDS driver require no software other than the driver jar file if making a connection using SQL Server authentication. If connecting via Windows authentication or via Windows single sign-on with the jTDS driver, additional Windows specific libraries need to be accessible from the java runtime environment (for example, by placing the .dll files in the jre/bin directory). For the MS driver, the sqljdbc_auth.dll file is needed. For the jTDS driver, the ntlmauth.dll file is needed. Both the MS driver and the jTDS driver connect to SQL Server using TCP/IP (note that the jTDS driver does have some support for named pipe communication). Because of this, the TCP/IP protocol needs to be enabled on the MS SQL Server database in order for either driver to make a connection.
Both the MS SQL Server and jTDS JDBC drivers can be downloaded from their respective web sites. Once the driver jar file is downloaded and included the the classpath of the Java program, a connection can be made to the database. In order for the Java program to load the driver, the program needs to know the main entry class of the driver. Listed below are the classes to use for the Microsoft driver and the jTDS driver:
Microsoft SQL Server JDBC Driver Java Class:
com.microsoft.sqlserver.jdbc.SQLServerDriver
jTDS Driver Java Class:
net.sourceforge.jtds.jdbc.Driver
The next step in creating the connection is to construct the JDBC URL. Listed below are some example JDBC URL formats.
Microsoft SQL Server JDBC Driver URL Formats
Example JDBC URL with host, port, and database name using SQL Server authentication:
jdbc:sqlserver://192.168.1.172:53000;databaseName=sample
Example JDBC URL with host, port, and database name, using Windows Authentication:
jdbc:sqlserver://192.168.1.172:53000;databaseName=sample;integratedSecurity=true
Example JDBC URL with server-side cursors enabled to improve performance, especially when dealing with larger result sets:
jdbc:sqlserver://192.168.1.172:53000;databaseName=sample;selectMethod=cursor
Example JDBC URL with SSL encryption and the server certificate validation turned off:
jdbc:sqlserver://192.168.1.172:53000;databaseName=sample;trustServerCertificate=true;encrypt=true
jTDS JDBC Driver URL Formats
Example JDBC URL with host, port, and database name using SQL Server authentication:
jdbc:jtds:sqlserver://192.168.1.172:53000/sample
Example JDBC URL with server-side cursors enabled to improve performance, especially when dealing with larger result sets:
jdbc:jtds:sqlserver://192.168.1.172:53000/sample;useCursors=true
Sample Code for Making a JDBC Connection
Below is sample Java code using JDBC to access an MS SQL Server database.
Class dbDriver = Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String jdbcURL = "jdbc:sqlserver://192.168.1.172:53000;databaseName=sample;selectMethod=cursor";
Connection connection = DriverManager.getConnection(jdbcURL, "user", "password");
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery("select * from employee");
while(rs.next())
{
System.out.println("name = " + rs.getString("name"));
System.out.println("id = " + rs.getInt("id"));
}
.
.
.
MySQL: How to Connect to MySQL via JDBC
Oracle: How to Connect to Oracle via JDBC
PostgreSQL: How to Connect to PostgreSQL via JDBC
Redshift: How to Connect to Redshift via JDBC
SQLite: How to Connect to SQLite via JDBC
