Please check this article if you are new to Custom Functions - 

How to create and use Custom Functions in Testsigma?


Here's a list of Sample Custom Functions for Database Operations using Testsigma

  1. Custom Function to Select Query

  2. Custom Function to Insert Query

  3. Custom Function to Update Query

  4. Custom Function to Delete Query


1. Custom Function to Select Query

import java.sql.*;

import com.testsigma.customfunc.common.CustomTestStep;
import com.testsigma.customfunc.result.ResultConstants;
import com.testsigma.customfunc.result.TestStepResult;
import com.testsigma.customfunc.common.TestsigmaCustomFunctions;

public class SelectQuery extends TestsigmaCustomFunctions{

  @CustomTestStep
  public TestStepResult select() throws Exception {
    TestStepResult result = new TestStepResult();

    try {
      // 1.Get connection to Database
      Connection myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", "root", "root");
      // 2.Create a Statement.
      Statement myStmt = myConn.createStatement();
      String query = "SELECT * FROM employees WHERE Last_name='House'";
      ResultSet Myrs = myStmt.executeQuery(query);
      if (Myrs.next()) {
        result.setStatus(ResultConstants.SUCCESS);
        result.setMessage("Custom Step Executed successfully");
      } else {
        result.setStatus(ResultConstants.FAILURE);
        result.setMessage("Fail to verify SelectQuery");
      }      
        myStmt.close();
    } catch (Exception ex) {
      result.setStatus(ResultConstants.FAILURE);
      result.setMessage(ex.getMessage());
    }
    return result;
  }
}



2. Custom Function to Insert Query

import java.sql.*;

import com.testsigma.customfunc.common.CustomTestStep;
import com.testsigma.customfunc.result.ResultConstants;
import com.testsigma.customfunc.result.TestStepResult;
import com.testsigma.customfunc.common.TestsigmaCustomFunctions;

public class InsertQuery extends TestsigmaCustomFunctions{
  @CustomTestStep

  public TestStepResult InsertQuery() throws Exception {
    TestStepResult result = new TestStepResult();

    try {
      // 1.Get connection to Database
      Connection myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", "root", "root");
      // 2.Create a Statement.
      Statement myStmt = myConn.createStatement();
      String query = " Insert into employees (id,First_name, Last_name,email) values(?,?,?,?)";
      PreparedStatement preparedStmt = myConn.prepareStatement(query);
      preparedStmt.setString(1, "15");
      preparedStmt.setString(2, "Stephen");
      preparedStmt.setString(3, "Gregory");
      preparedStmt.setString(4, "Stephen@Gregory.com");
      preparedStmt.execute();
      ResultSet Myrs = myStmt.executeQuery("select * from employees WHERE id=11 AND Last_name123='Stephen'");
      if (Myrs.next()) {
        result.setStatus(ResultConstants.SUCCESS);
        result.setMessage("custom step Executed successfully");
      } else {
        result.setStatus(ResultConstants.FAILURE);
        result.setMessage("Fail to verify InsertQuery");
      }
      
          myStmt.close();
      
    } catch (Exception ex) {
      result.setStatus(ResultConstants.FAILURE);
      result.setMessage(ex.getMessage());
    }
    return result;

  }
}



3. Custom Function to Update Query

import java.sql.*;

import com.testsigma.customfunc.common.CustomTestStep;
import com.testsigma.customfunc.result.ResultConstants;
import com.testsigma.customfunc.result.TestStepResult;
import com.testsigma.customfunc.common.TestsigmaCustomFunctions;

public class UpdateQuery extends TestsigmaCustomFunctions{

  @CustomTestStep
  public TestStepResult UpdateQuery() throws Exception {
    
    TestStepResult result = new TestStepResult();
    try {
      // 1.Get connection to Database
      Connection myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", "root", "root");
      // 2.Create a Statement.
      Statement myStmt = myConn.createStatement();
      String querry = "UPDATE employees SET Last_name = 'Sean Cooper' where id=5";
      myStmt.executeUpdate(querry);

      ResultSet Myrs = myStmt.executeQuery("select * from employees WHERE Last_name = 'Sean Cooper'");
      if (Myrs.next()) {
        result.setStatus(ResultConstants.SUCCESS);
        result.setMessage("custom step Executed successfully");
      } else {
        result.setStatus(ResultConstants.FAILURE);
        result.setMessage("Fail to verify UpdateQuery");
      }
      
          myStmt.close();

    } catch (Exception ex) {
      result.setStatus(ResultConstants.FAILURE);
      result.setMessage(ex.getMessage());
    }
    return result;

  }
}



4. Custom Function to Delete Query

import java.sql.*;

import com.testsigma.customfunc.common.TestsigmaCustomFunctions;
import com.testsigma.customfunc.common.CustomTestStep;
import com.testsigma.customfunc.result.ResultConstants;
import com.testsigma.customfunc.result.TestStepResult;

public class DeleteQuery extends TestsigmaCustomFunctions{
  
  @CustomTestStep
  public TestStepResult DeleteQuery() throws Exception {

    TestStepResult result = new TestStepResult();
    try {
      // 1.Get connection to Database
      Connection myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", "root", "root");
      // 2.Create a Statement.
      Statement myStmt = myConn.createStatement();
      String querry = "DELETE FROM employees WHERE id = 5";
      int value = myStmt.executeUpdate(querry);
      if (value != 0) {

        result.setStatus(ResultConstants.SUCCESS);
        result.setMessage("executed successfully");
      } else {
        result.setStatus(ResultConstants.FAILURE);
        result.setMessage("Fail to verify DeleteQuery");
      }
      
          myStmt.close();

    } catch (Exception ex) {
      result.setStatus(ResultConstants.FAILURE);
      result.setMessage(ex.getMessage());
    }
    return result;

  }

}