SqlServerInstanceJdbcUrl.java - sqljdbc JDBC URL for Instance

Q

How to use instance name instead of port number in the connection URL string for a specific SQL Server database instance using the sqljdbc JDBC driver? I want to see a Java program example.

✍: FYIcenter.com

A

If your SQL Server is running multiple instances on a single host, and the SQL Server Browser service is running to serve instance name to port number conversion, you can following the Java example below specify instance name instead of port number in the connection URL string using the sqljdbc JDBC driver:

// Copyright (c) 2016 FYIcenter.com
import java.sql.DriverManager;
import java.sql.Connection;

// Example of sqljdbc JDBC connection URL
public class SqlServerInstanceJdbcUrl {
   public static void main(String [] args) throws Exception {

      // sqljdbc JDBC connection URL to the default instance
      String url = "jdbc:sqlserver://localhost;user=sa;password=fyicenter";
      Connection con = DriverManager.getConnection(url);
      System.out.println("JDBC connection URL: "+url); 
      System.out.println("   Connected to: "+con.getCatalog()); 
      con.close();

      // sqljdbc JDBC connection URL with instance name appended to host name
      url = "jdbc:sqlserver://localhost\\SQLEXPRESS;user=sa;password=fyicenter";
      con = DriverManager.getConnection(url);
      System.out.println("JDBC connection URL: "+url); 
      System.out.println("   Connected to: "+con.getCatalog()); 
      con.close();

      // sqljdbc JDBC connection URL with instance name as a property
      url = "jdbc:sqlserver://localhost;user=sa;password=fyicenter;instanceName=SQLEXPRESS";
      con = DriverManager.getConnection(url);
      System.out.println("JDBC connection URL: "+url); 
      System.out.println("   Connected to: "+con.getCatalog()); 
      con.close();
   }
}

You can compile and run the above example in a command window as shown below:

C:\fyicenter>\local\jdk-1.8.0\bin\javac 
   SqlServerInstanceJdbcUrl.java

C:\fyicenter>\local\jdk-1.8.0\bin\java
   -cp .;C:\local\sqljdbc_6.0\enu\sqljdbc42.jar 
   SqlServerInstanceJdbcUrl

JDBC connection URL: 
   jdbc:sqlserver://localhost;user=sa;password=fyicenter
   Connected to: master
   
JDBC connection URL: 
   jdbc:sqlserver://localhost\SQLEXPRESS;user=sa;password=fyicenter
   Connected to: master
   
JDBC connection URL:
   jdbc:sqlserver://localhost;user=sa;password=fyicenter;instanceName=SQLEXPRESS
   Connected to: master

 

Examples for sqljdbc - JDBC Driver for SQL Server

⇒⇒FAQ for sqljdbc - JDBC Driver for SQL Server

2016-12-24, 218👍, 0💬