SQL Server Automation in Java

Why do we need database automation?

  • To get test data - If we automate the database, we can directly fetch the test data from database and then work on them in test automation script
  • To verify result - In automation we can verify the front end result with backend entry in the database
  • To delete test data created - In automation it is good practice to delete the test data created, using database automation, we directly fire the delete query to delete the test data created
  • To update certain data - As per the need of test script, the test data can be updated using update query

Connecting to SQL Server in Java

Database automation in SQL Server involves the following steps-

  • Loading the required JDBC Driver class, which in our case is com.microsoft.sqlserver.jdbc.SQLServerDriver. You can download the jar from here and add it to your classpath or add it as maven dependency in you pom.xml file in case you are using maven project.
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    

  • Creating a connection to the database-
    Connection conn = DriverManager.getConnection("DatabaseURL","UserName", "Password");
    

  • Executing SQL queries-
    Statement st = conn.createStatement();
    String Sql = "select * from [tableName] where <condition>";
    ResultSet rs = st.executeQuery(Sql);
    

  • Fetching data from result set-
    while (rs.next()) {
    	System.out.println(rs.getString(<requiredField>));
    }
    

Sample code to connect to a SQL Server database

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
 
public class connectingToDBExample {
 
	public static void main(String[] args) throws SQLException, ClassNotFoundException {
		
		//Loading the required JDBC Driver class
		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");	
		
		//Creating a connection to the database
		Connection conn = DriverManager.getConnection("jdbc:sqlserver://artOfTesting.com/testDB","userName","password");
		
		//Executing SQL query and fetching the result
		Statement st = conn.createStatement();
		String sqlStr = "select * from testTable";
		ResultSet rs = st.executeQuery(sqlStr);
		while (rs.next()) {
			System.out.println(rs.getString("name"));
		}		
	}
}