Use JDBC To Connect Microsoft SQL Server

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

1. You can access Microsoft SQL Server use JDBC with the 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 likejdbc:sqlserver://serverName\ serverIP:portNumber;databaseName=dbName;integratedSecurity=true;property1=value1;property2=value2;
  3. If you set the parameter integratedSecurity value to true, means that you log in to SQL server use windows authentication.
  4. If set it to false means login SQL server uses 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;
  5. But before this, you should download and install the Microsoft SQL Server JDBC driver first.

2. Download Microsoft SQL Server JDBC Driver File.

  1. Click here to get the latest version of SQL Server JDBC driver.
  2. After you download the enu.tar.gz file, unzip it to a local folder like below.C:\WorkSpace\dev2qa.com\Lib\sqljdbc_6.0\enu

3. Add The JDBC Driver Jar Into Java Project.

  1. After download and extract, you can locate the Microsoft SQL Server JDBC driver jar file in the extracted folder ( such as \sqljdbc_9.2.1.0_enu\sqljdbc_9.2\enu ).
  2. Add the related jar file to your java project build path. If you use JDK 15 then add mssql-jdbc-9.2.1.jre15.jar, if you use JDK 8 then add mssql-jdbc-9.2.1.jre8.jar. You can read the article How To Add Selenium Server Standalone Jar File Into Eclipse Java Project And Maven Project to learn how to add them to your java project build path.
  3. Now you can write java code to use JDBC to connect to Microsoft SQL Server.

4. Connect Microsoft 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();	    			
	    	}
	    }
        }
}

5. Connect Microsoft SQL Server Use Connection 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();	    			
    		}
    	}
    }
}
0 0 votes
Article Rating
Subscribe
Notify of
guest

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

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x