Introduction
JDBC is a set of classes and interfaces written in Java that allows Java programs to access a database. Microsoft provides a JDBC type 4 driver. Depending on the driver version, it requires a certain level of Java, the latest 3.0 drivers support Java 5 and 6.
Download driver package
At the time of testing, I got a package called sqljdbc_3.0.1301.101_enu.tar.gz. I found that latest driver here We can verify the content of the file:
fm@susie:~> zcat sqljdbc_3.0.1301.101_enu.tar.gz | tar tf - | grep jar sqljdbc_3.0/enu/sqljdbc.jar sqljdbc_3.0/enu/sqljdbc4.jar
Install the driver package
In order to work with the driver, Java must be able to find it when called. We can achieve this by adding the drivers zip file location to the Java classpath, or by simply placing the driver files into the Java standard directory for extensions, for JAVA JRE this is the $JAVA_HOME/jre/lib/ext (i.e. /usr/lib64/jvm/jre-1.6.0-ibm/lib/ext/) directory. We need to copy the extracted sqljdbc.jar and sqljdbc4.jar.
Use the driver to access MS SQL Server through Java
The following example code JdbcTestMssql.java can be used to quickly access and test the JDBC connection.
vi JdbcTestMssql.java
//JdbcTestMssql.java
import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.ResultSet ;
import java.sql.Statement ;
import java.sql.SQLException;
class JdbcTestMssql {
public static void main (String args[]) {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
}
catch (ClassNotFoundException e) {
System.err.println (e) ;
System.exit (-1) ;
}
try {
// open connection to database
Connection connection = DriverManager.getConnection(
//"jdbc:sqlserver://localhost:1433;databaseName=dbname;user=dbuser;password=dbpwd;"
"jdbc:sqlserver://192.168.98.125:1433;databaseName=master;user=sa;password=pass;");
// build query, here we use table "sys.databases"
String query = "SELECT * From sys.databases" ;
// execute query
Statement statement = connection.createStatement () ;
ResultSet rs = statement.executeQuery (query) ;
// return query result
while ( rs.next () )
// display content from column "name"
System.out.println ("MS-SQL Query result: " + rs.getString ("name")) ;
connection.close () ;
}
catch (java.sql.SQLException e) {
System.err.println (e) ;
System.exit (-1) ;
}
}
}
Compile and test run
fm@susie:~> javac JdbcTestMssql.java fm@susie:~> java JdbcTestMssql MS-SQL Query result: master MS-SQL Query result: tempdb MS-SQL Query result: model MS-SQL Query result: msdb
Should this test fail, typical reasons are:
- Wrong database port? MS SQL Server typically uses port tcp/1433
- Wrong database user, or database user does not have admin rights
Additional Literature: Microsoft SQL Server JDBC Driver