Tutorial: Connect to SQL server from a Java application using JDBC Driver

Aug 27, 2018

I've started supporting Microsoft JDBC Driver for SQL server. The driver provides Java database connectivity from any Java application, application server, or Java-enabled applet. As a beginner of Java programming, I am writing this tutorial to record how to create a simple console Java application to connect SQL server using JDBC Driver.

  1. Download and install JDK from https://www.oracle.com/technetwork/java/javase/downloads/index.html, in this tutorial we will use JDK 8 (8u181) as it is the recommended Java Version for JDBC Driver 7.0.
  2. As we will use javac.exe to compile the Java program, we need to add C:\Program Files\Java\jdk1.8.0_181\bin to the Path variable so we can call javac.exe from anywhere.

  3. Download Microsoft JDBC Driver 7.0 for SQL Server from: https://www.microsoft.com/en-us/download/details.aspx?id=57175.

  4. Run sqljdbc_7.0.0.0_enu.exe and unzip the content to anywhere you like, we will extract it to C:\jdbc7 in this tutorial.

  5. You will find the JDBC package for JRE8 at: C:\jdbc7\sqljdbc_7.0\enu\mssql-jdbc-7.0.0.jre8.jar, let’s set it as classpath in System variables so that the compiler is able to find it.

  6. Create a folder for the JDBC test application: C:\jdbc-app.

  7. Save following application code to C:\jdbc-app\JDBCTest.java

     import java.sql.Connection;
     import java.sql.DriverManager;
     import java.sql.ResultSet;
     import java.sql.SQLException;
     import java.sql.Statement;
    
     public class JDBCTest {
         public static void main(String[] args) {
             String connectionUrl =
                     "jdbc:sqlserver://192.168.199.221:1433;databaseName=TestDB;integratedSecurity=true";
    
             try (Connection con = DriverManager.getConnection(connectionUrl);
                     Statement stmt = con.createStatement();) {
                 String SQL = "SELECT TOP 10 * FROM Person;";
                 ResultSet rs = stmt.executeQuery(SQL);
                 while (rs.next()) {
                     System.out.println(rs.getString("Name"));
                 }
             } catch (SQLException e) {
                 e.printStackTrace();
             }
         }
     }
    
  8. In this tutorial, we use integrated authentication to authenticate a remote SQL server. We will need the sqljdbc_auth.dll for integrated authentication to work. You can find sqljdbc_auth.dll at C:\jdbc7\sqljdbc_7.0\enu\auth\x64 (or C:\jdbc7\sqljdbc_7.0\enu\auth\x86 for a 32bit OS). Let's copy sqljdbc_auth.dll to C:\jdbc-app.

  9. Add C:\jdbc-app to classpath as well.

  10. Run a command prompt, cd to C:\jdbc-app, run javac JDBCTest.java to compile the test application, it will create JDBCTest.class when it completes.

  11. Run java JDBCTest to test the console application.

    C:\jdbc-app>javac JDBCTest.java
    
    C:\jdbc-app>java JDBCTest
    John
    Marry
    Kevin
    
    C:\jdbc-app>
    

Keywords:

JDBCSQL ServerJava