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

package com.testsigma.customfunc.examples;

import java.sql.*;
import org.openqa.selenium.WebDriver;

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

public class SelectQuery {

  protected static WebDriver webdriver;

  public SelectQuery(WebDriver webdriver) {
    this.webdriver = webdriver;

  }

  @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 exc) {
      result.setStatus(ResultConstants.FAILURE);
      result.setMessage(exc.getMessage());
    }
    return result;

  }
}



2. Custom Function to Insert Query

package com.testsigma.customfunc.examples;

import java.sql.*;

import org.openqa.selenium.WebDriver;

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

public class InsertQuery {

  protected WebDriver webdriver;

  public InsertQuery(WebDriver webdriver) {
    this.webdriver = webdriver;
  }

  @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 exc) {
      result.setStatus(ResultConstants.FAILURE);
      result.setMessage(exc.getMessage());
    }
    return result;

  }
}



3. Custom Function to Update Query

package com.testsigma.customfunc.examples;

import java.sql.*;

import org.openqa.selenium.WebDriver;

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

public class UpdateQuery {

  protected WebDriver webdriver;

  public UpdateQuery(WebDriver webdriver) {
    this.webdriver = webdriver;
  }

  @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 exc) {
      result.setStatus(ResultConstants.FAILURE);
      result.setMessage(exc.getMessage());
    }
    return result;

  }
}



4. Custom Function to Delete Query

package com.testsigma.customfunc.examples;

import java.sql.*;

import org.openqa.selenium.WebDriver;

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

public class DeleteQuery {
  protected static WebDriver webdriver;

  public DeleteQuery(WebDriver webdriver) {
    this.webdriver = webdriver;
  }

  @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 exc) {
      result.setStatus(ResultConstants.FAILURE);
      result.setMessage(exc.getMessage());
    }
    return result;

  }

}