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
Custom Function to Select Query
Custom Function to Insert Query
Custom Function to Update Query
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;
}
}