Use JDBC To Connect Microsoft SQL Server

This article will tell you how to connect Microsoft SQL Server use JDBC.

You can access Sql Server use JDBC with following two methods.

  1. Use com.microsoft.sqlserver.jdbc.SQLServerDataSource java object.
    	SQLServerDataSource sqlDs = new SQLServerDataSource();
    						
    	// Use window integrate authentication.
    	//sqlDs.setIntegratedSecurity(true);
    			
    	/* Use sql server account authentication.*/
    	sqlDs.setIntegratedSecurity(false);
    	sqlDs.setUser("sa");
    	sqlDs.setPassword("sa");
    						
    	// Set ds server name or ip.
    	sqlDs.setServerName("localhost");
    	// Set sql server listening port number.
    	sqlDs.setPortNumber(1433); 
    	// Set the database name.
    	sqlDs.setDatabaseName("TestDB");
    			
    	// Get connection
    	dbConn = sqlDs.getConnection();
    
  2. Use JDBC connection url.
    jdbc:sqlserver://serverName\ serverIP:portNumber;databaseName=dbName;integratedSecurity=true;property1=value1;property2=value2;
    If set integratedSecurity to true, means login sql server use windows authentication, if set it to false means login sql server use sql server account, you should provide username and password in this case like below.
    jdbc:sqlserver://serverName\ serverIP:portNumber;databaseName=dbName;integratedSecurity=false;user=sa;password=sa;
    But before this, you should download and install the sql server jdbc driver first.

Download Sql Server JDBC Driver File

  1. Go to below web page to get the latest version of SQL Server jdbc driver.
    download sql server jdbc driver
  2. For Windows check the checkbox before the enu.exe, for other OS check the checkbox before enu.tar.gz. Click Next button. Please Note: if you want to use Windows Authentication to connect to sql server in java code, you should download and install the enu.exe file.
    choose sql server jdbc driver download file
  3. If you download windows exe file, click it to install. If you download the enu.tar.gz file, unzip it to a local folder like below.C:\WorkSpace\dev2qa.com\Lib\sqljdbc_6.0\enu

Add The JDBC Driver Jar Into Java Project 

After download and extract, you can locate jdbc driver jar file in below folders.

sql server jdbc jar file location

Add related jar file to your java project build path. If you use jdk1.7 then add jre7\sqljdbc41.jar, if jdk1.8 then add jre8\sqljdbc42.jar

add sql server jdbc driver jar file in java project build path

Now you can write java code to use jdbc to connect to Microsoft Sql Server.

READ :   JDBC Statement Examples - Insert, Delete, Update, Select Record

Connect Sql Server Use JDBC DataSource

	/* Use SQLServerDataSource object to access sql server in jdbc. */
        public void connectSqlServerUseDS()
	{
		// Declare the JDBC objects.
		Connection dbConn = null;
		CallableStatement cstmt = null;
		ResultSet rs = null;
		
		try {
			// Create a sql server data source object. 
			SQLServerDataSource sqlDs = new SQLServerDataSource();
			
			// Set ds related info.
			
			// If you want to use window integrate authentication.
			//sqlDs.setIntegratedSecurity(true);
			
			/* If you want to use sql server account authentication.*/
			sqlDs.setIntegratedSecurity(false);
			sqlDs.setUser("sa");
			sqlDs.setPassword("sa");
						
			// Set ds server name or ip.
			sqlDs.setServerName("localhost");
			// Set sql server listening port number.
			sqlDs.setPortNumber(1433); 
			// Set the database name.
			sqlDs.setDatabaseName("TestDB");
			
			// Get connection
			dbConn = sqlDs.getConnection();
			 
	        // Run a callable statement which will return data list.
            cstmt = dbConn.prepareCall("{call dbo.uspGetStudentTeachers(?)}");
            // Set the prepared statement input parameter value.
            cstmt.setInt(1, 100);
            // Execute the stored procedure and get the returned data in result set.
            rs = cstmt.executeQuery();

	        // Show the result data in a loop.
	        while (rs.next()) 
	        {
	           		
	        	System.out.println("Student: " + rs.getString("StudentLastName") + ", " + rs.getString("StudentFirstName"));
	            System.out.println("Teacher: " + rs.getString("TeacherLastName") + ", " + rs.getString("TeacherFirstName"));
	            System.out.println();
	        }
	    }catch (Exception ex) {
	    	ex.printStackTrace();
	    }finally {
	    	if (rs != null) 
	    	{
	    		try { 
	    			rs.close(); 
	    		}catch(Exception ex) {
	    			ex.printStackTrace();
	    		}
	    	}
	    	
	    	if (cstmt != null) 
	    	{
	    		try { 
	    			cstmt.close(); 
	    		}catch(Exception ex) 
	    		{
	    			ex.printStackTrace();
	    		}
	    	}
	    	
	    	if (dbConn != null) 
	    	{
	    		try { 
	    			dbConn.close(); 
	    		} catch(Exception ex) {
	    			ex.printStackTrace();	    			
	    		}
	    	}
	    }
	}

Connect Sql Server Use URL String

	/* Use jdbc connection url to access sql server.*/
        public void connectSqlServerUseURL()
	{
			// Build sql server jdbc connection url use sql server account authentication.
			String sqlServerConnectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=TestDB;user=sa;password=008632";
			
			// Use windows authentication. 
			//String sqlServerConnectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=TestDB;integratedSecurity=true;";
	
			// Declare the JDBC objects.
			Connection dbConn = null;
			Statement stmt = null;
			ResultSet rs = null;
		
        	try {
        		// Load jdbc driver class.
        		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            	// Get connection.	
        		dbConn = DriverManager.getConnection(sqlServerConnectionUrl);
            
            	// Execute sql and return data result.
            	String SQL = "SELECT * FROM Student";
            	stmt = dbConn.createStatement();
            	rs = stmt.executeQuery(SQL);
            
            	// Loop the data result and display the data.
            	while (rs.next()) {
            		System.out.println(rs.getString(1));
            	}
        	}catch (Exception ex) {
        		ex.printStackTrace();
        	}finally {
    	    	if (rs != null) 
    	    	{
    	    		try { 
    	    			rs.close(); 
    	    		}catch(Exception ex) {
    	    			ex.printStackTrace();
    	    		}
    	    	}
    	    	
    	    	if (stmt != null) 
    	    	{
    	    		try { 
    	    			stmt.close(); 
    	    		}catch(Exception ex) 
    	    		{
    	    			ex.printStackTrace();
    	    		}
    	    	}
    	    	
    	    	if (dbConn != null) 
    	    	{
    	    		try { 
    	    			dbConn.close(); 
    	    		} catch(Exception ex) {
    	    			ex.printStackTrace();	    			
    	    		}
    	    	}
    	    }
	}

Source Code:

  1. Download “SqlServerExample.zip” SqlServerExample.zip – Downloaded 102 times – 1 KB

(Visited 857 times, 8 visits today)

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.