HOW TO: Connect To MS SQL Using Java

In an effort to support the multi-platform movement, Microsoft has a set of tools available for Java developers to allow seemless integration to SQL Server. If you are interested, you can download this tool set here. The advantage of using a Microsoft native driver as opposed to writing one of your own or using a third party driver is the awesome support that Microsoft provides for future updates etc.  Let’s take a look at how you might use this driver in your code.

For your first pass at connecting to SQL server, you may want to check if your server is IPv6 or IPv4. If it is IPv6, you will need the following line of code to make sure your connections are successful.

System.setProperty("java.net.preferIPv6Addresses", "true");

Next we will need to build our connection string. A good template that I use is simple for all kinds of setup, and allows for integrated security etc.

Connect to the default database on the local computer by using integrated authentication:
jdbc:sqlserver://localhost;integratedSecurity=true;

Connect to a named database on a remote server:
jdbc:sqlserver://localhost;databaseName=AdventureWorks;integratedSecurity=true;

Connect on the default port to the remote server:
jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks;integratedSecurity=true;

Connect by specifying a customized application name:
jdbc:sqlserver://localhost;databaseName=AdventureWorks;integratedSecurity=true;applicationName=MyApp;

If you need more advanced options, you can find other properties that can be set in the connection string here. Finally, let’s create a connection for use in querying the database. The typical object to use is called the DriverManager.

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String strURL = "jdbc:sqlserver://localhost;database=AW;integratedSecurity=true;";
Connection con = DriverManager.getConnection(connectionUrl);

Most importantly, I’m sure you will want to know how to query data. This can be done through simple selects similar to coding in a .NET language. Below is an example of a select statement with parameters. For more information about using the JDBC driver for various SQL tasks, check here.

public static void executeStatement(Connection con) {
   try {
      String SQL = "SELECT LastName, FirstName FROM Person.Contact WHERE LastName = ?";
      PreparedStatement pstmt = con.prepareStatement(SQL);
      pstmt.setString(1, "Smith");
      ResultSet rs = pstmt.executeQuery();
      while (rs.next()) {
          System.out.println(rs.getString("LastName") + ", " + rs.getString("FirstName"));
      }
      rs.close();
      pstmt.close();
   }
   catch (Exception e) {
      e.printStackTrace();
   }
}

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>