HOME

Database testing is an essential aspect of software testing that focuses on verifying the correctness, reliability, and performance of the database components of an application. Database testing automation eliminates manual efforts involved in executing repetitive and time-consuming tasks, such as data setup, data validation, and query optimization. This article will teach us how to insert a new row in the table in Microsoft SQL Server by establishing a Java connection

Why do we need to connect to databases in automation?

To get test data – If the database is automated, test data may be immediately retrieved and processed using a test automation script.
To verify the result – To confirm the outcome, we may use automation to cross-check the front-end result to the database’s back-end record.
To delete test data created – It is best practice in automation to remove test data that has been created. To do this, we use database automation and immediately execute the delete query.
To update certain data – Update queries can be used to update test data per the requirements of the test script.

Before inserting a new row in the table

After inserting a row

Connecting to SQL Server in Java

1. Get a connection to Database
2. Create a statement object
3. Execute the SQL query
4. Process the Result Set

Get a connection to Database

Need a connection string in the form of a JDBC URL

jdbc:<driver protocol>:<driver connection details>

You can connect to MySQL, MS MySql Server, or Oracle using the below syntax:

MS MySql Server - jdbc:odbc:DemoDSN
MySQL - jdbc:mysql://localhost:3306/demodb
Oracle - jdbc:orac;e:thin@myserver:1521:demodb

Create a statement object

The statement object is based on connection. It will be used later to execute SQL query

String dbUrl = "jdbc:mysql://localhost:3306/demo";
String username = "student";
String password = "student1$";

Connection conn = DriverManager.getConnection(dbUrl,username,password)
Statement stmt = conn.createStatement();

Execute the SQL query

Pass in the SQL query. Here, we want to insert data, so we need to use executeUpdate.

executeUpdate – Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.
This method cannot be called on a PreparedStatement or CallableStatement.

 int rowAffected = stmt.executeUpdate(
                    "insert into employees (last_name, first_name, email, department,salary) values ('Singh', 'Vibha','vibha.test@gmail.com', 'QA', 85000)");

Process the resultSet

Result Set is initially placed before the first row.
Method: Result.next() -moves forward one row returns true if there are more rows to process
Looping through the result set.

DriverManager helps to connect an application based on the database connection string
JDBC API is defined by package – java.sql and javax.sql

Add mysql-connector-j dependency to the pom.xml.

<dependencies>
    <dependency>
      <groupId>com.mysql</groupId>
      <artifactId>mysql-connector-j</artifactId>
      <version>8.2.0</version>
</dependency>

Sample code to connect to SQL Server database

package org.example;

import java.sql.*;

public class InsertRow_Demo {
    public static void main(String[] args) throws SQLException {

        Connection conn;
        Statement stmt = null;
        ResultSet result = null;
        ResultSet result1 = null;
        ResultSet result2 = null;

        String dbUrl = "jdbc:mysql://localhost:3306/demo";
        String username = "student";
        String password = "student1$";

        try {
            //Get a connection to database
            conn = DriverManager.getConnection(dbUrl, username, password);

            System.out.println("Database connection is successful\n");

            //Create a statement
            stmt = conn.createStatement();

            System.out.println("Inserting a new employee\n");

            int rowAffected = stmt.executeUpdate(
                    "insert into employees (last_name, first_name, email, department,salary) values ('Singh', 'Vibha','vibha.test@gmail.com', 'QA', 85000)");

            System.out.println("No of rows inserted :" + rowAffected);

            //Execute the SQL Query
            result = stmt.executeQuery("Select * from employees");

            //Process the result set
            while (result.next()) {
                System.out.println("First_Name :" + result.getString("first_name") + " , " + ("Last_Name :" + result.getString("last_name")));

            }


        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

The output of the above program is

We are done! Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!!