Unlock 30% off on Manual Testing Annual Plans this Holiday Season.

Copied to Clipboard!

How to use validateSqlStatement method of com.consol.citrus.actions.ExecuteSQLQueryAction class

Best Citrus code snippet using com.consol.citrus.actions.ExecuteSQLQueryAction.validateSqlStatement

Source:ExecuteSQLQueryAction.java

...98        }99    }100    protected void executeStatements(List<Map<String, Object>> allResultRows, Map<String, List<String>> columnValuesMap, TestContext context) {101        for (String stmt : statements) {102            validateSqlStatement(stmt);103            final String toExecute;104            if (stmt.trim().endsWith(";")) {105                toExecute = context.replaceDynamicContentInString(stmt.trim().substring(0, stmt.trim().length()-1));106            } else {107                toExecute = context.replaceDynamicContentInString(stmt.trim());108            }109            if (log.isDebugEnabled()) {110                log.debug("Executing SQL query: " + toExecute);111            }112            List<Map<String, Object>> results = getJdbcTemplate().queryForList(toExecute);113            log.info("SQL query execution successful");114            allResultRows.addAll(results);115            fillColumnValuesMap(results, columnValuesMap);116        }117    }118    /**119     * Fills the (requested) test context variables with the db result values120     * @param columnValuesMap the map containing column names --> list of result values121     * @param context the test context the variables are stored to122     * @throws CitrusRuntimeException if requested column name was not found123     */124    private void fillContextVariables(Map<String, List<String>> columnValuesMap, TestContext context)125            throws CitrusRuntimeException {126        for (Entry<String, String> variableEntry : extractVariables.entrySet()) {127            String columnName = variableEntry.getKey();128            if (columnValuesMap.containsKey(columnName.toLowerCase())) {129                context.setVariable(variableEntry.getValue(), constructVariableValue(columnValuesMap.get(columnName.toLowerCase())));130            } else if (columnValuesMap.containsKey(columnName.toUpperCase())) {131                context.setVariable(variableEntry.getValue(), constructVariableValue(columnValuesMap.get(columnName.toUpperCase())));132            } else {133                throw new CitrusRuntimeException("Failed to create variables from database values! " +134                        "Unable to find column '" + columnName + "' in database result set");135            }136        }137    }138    /**139     * Form a Map object which contains all columns of the result as keys140     * and a List of row values as values of the Map141     * @param results result map from last jdbc query execution142     * @param columnValuesMap map holding all result columns and corresponding values143     */144    private void fillColumnValuesMap(List<Map<String, Object>> results, Map<String, List<String>> columnValuesMap) {145        for (Map<String, Object> row : results) {146            for (Entry<String, Object> column : row.entrySet()) {147                String columnValue;148                String columnName = column.getKey();149                if (!columnValuesMap.containsKey(columnName)) {150                    columnValuesMap.put(columnName, new ArrayList<String>());151                }152                if (column.getValue() instanceof byte[]) {153                    columnValue = Base64.encodeBase64String((byte[]) column.getValue());154                } else {155                    columnValue = column.getValue() == null ? null : column.getValue().toString();156                }157                columnValuesMap.get(columnName).add((columnValue));158            }159        }160    }161    /**162     * Gets the script validator implementation either autowired from application context163     * or if not set here a default implementation.164     */165    private SqlResultSetScriptValidator getScriptValidator() {166        if (validator != null) {167            return validator;168        } else {169            return new GroovySqlResultSetValidator();170        }171    }172    /**173     * Constructs a delimited string from multiple row values in result set in order to174     * set this expression as variable value.175     *176     * @param rowValues the list of values representing the allResultRows for a column in the result set.177     * @return the variable value as delimited string or single value.178     */179    private String constructVariableValue(List<String> rowValues) {180        if (CollectionUtils.isEmpty(rowValues)) {181            return "";182        } else if (rowValues.size() == 1) {183            return rowValues.get(0) == null ? NULL_VALUE : rowValues.get(0);184        } else {185            StringBuilder result = new StringBuilder();186            Iterator<String> it = rowValues.iterator();187            result.append(it.next());188            while (it.hasNext()) {189                String nextValue = it.next();190                result.append(";" + (nextValue == null ? NULL_VALUE : nextValue));191            }192            return result.toString();193        }194    }195    /**196     * Validates the database result set. At first script validation is done (if any was given).197     * Afterwards the control result set validation is performed.198     *199     * @param columnValuesMap map containing column names as keys and list of string as retrieved values from db200     * @param allResultRows list of all result rows retrieved from database201     * @return success flag202     * @throws UnknownElementException203     * @throws ValidationException204     */205    private void performValidation(final Map<String, List<String>> columnValuesMap,206            List<Map<String, Object>> allResultRows, TestContext context)207            throws UnknownElementException, ValidationException {208        // apply script validation if specified209        if (scriptValidationContext != null) {210            getScriptValidator().validateSqlResultSet(allResultRows, scriptValidationContext, context);211        }212        //now apply control set validation if specified213        if (CollectionUtils.isEmpty(controlResultSet)) {214            return;215        }216        performControlResultSetValidation(columnValuesMap, context);217        log.info("SQL query validation successful: All values OK");218    }219    private void performControlResultSetValidation(final Map<String, List<String>> columnValuesMap, TestContext context)220            throws CitrusRuntimeException {221        for (Entry<String, List<String>> controlEntry : controlResultSet.entrySet()) {222            String columnName = controlEntry.getKey();223            if (columnValuesMap.containsKey(columnName.toLowerCase())) {224                columnName = columnName.toLowerCase();225            } else if (columnValuesMap.containsKey(columnName.toUpperCase())) {226                columnName = columnName.toUpperCase();227            } else if (!columnValuesMap.containsKey(columnName)) {228                throw new CitrusRuntimeException("Could not find column '" + columnName + "' in SQL result set");229            }230            List<String> resultColumnValues = columnValuesMap.get(columnName);231            List<String> controlColumnValues = controlEntry.getValue();232            // first check size of column values (representing number of allResultRows in result set)233            if (resultColumnValues.size() != controlColumnValues.size()) {234                throw new CitrusRuntimeException("Validation failed for column: '" +  columnName + "' " +235                        "expected rows count: " + controlColumnValues.size() + " but was " + resultColumnValues.size());236            }237            Iterator<String> it = resultColumnValues.iterator();238            for (String controlValue : controlColumnValues) {239                String resultValue = it.next();240                //check if controlValue is variable or function (and resolve it)241                controlValue = context.replaceDynamicContentInString(controlValue);242                validateSingleValue(columnName, controlValue, resultValue, context);243            }244        }245    }246    /**247     * Does some simple validation on the SQL statement.248     * @param stmt The statement which is to be validated.249     */250    protected void validateSqlStatement(String stmt) {251        if (!stmt.toLowerCase().startsWith("select")) {252            throw new CitrusRuntimeException("Missing keyword SELECT in statement: " + stmt);253        }254    }255    protected void validateSingleValue(String columnName, String controlValue, String resultValue, TestContext context) {256        // check if value is ignored257        if (controlValue.equals(Citrus.IGNORE_PLACEHOLDER)) {258            if (log.isDebugEnabled()) {259                log.debug("Ignoring column value '" + columnName + "(resultValue)'");260            }261            return;262        }263        264        if (ValidationMatcherUtils.isValidationMatcherExpression(controlValue)) {...

validateSqlStatement

Using AI Code Generation

1package com.consol.citrus.dsl.testng;2import com.consol.citrus.annotations.CitrusTest;3import com.consol.citrus.dsl.runner.TestRunner;4import com.consol.citrus.testng.CitrusParameters;5import org.testng.annotations.Test;6public class ExecuteSQLQueryActionJavaITest extends AbstractTestNGCitrusTest {7    @CitrusParameters({"sqlQuery", "validateSqlStatement"})8    public void executeSQLQueryActionJavaITest(TestRunner runner) {9        runner.given(sql(builder -> builder10                .statement("INSERT INTO COUNTRY (ID, NAME) VALUES (1, 'Germany')")11                .dataSource("citrus:jdbc:dataSource")12        ));13        runner.when(sql(builder -> builder14                .statement("SELECT * FROM COUNTRY WHERE ID = 1")15                .dataSource("citrus:jdbc:dataSource")16                .validateSqlStatement("SELECT * FROM COUNTRY WHERE ID = 1")17        ));18        runner.then(sql(builder -> builder19                .statement("DELETE FROM COUNTRY WHERE ID = 1")20                .dataSource("citrus:jdbc:dataSource")21        ));22    }23}24package com.consol.citrus.dsl.testng;25import com.consol.citrus.annotations.CitrusTest;26import com.consol.citrus.dsl.runner.TestRunner;27import com.consol.citrus.testng.CitrusParameters;28import org.testng.annotations.Test;29public class ExecuteSQLQueryActionJavaITest extends AbstractTestNGCitrusTest {30    @CitrusParameters({"sqlQuery", "validateSqlStatement"})31    public void executeSQLQueryActionJavaITest(TestRunner runner) {

validateSqlStatement

Using AI Code Generation

1import com.consol.citrus.annotations.CitrusTest;2import com.consol.citrus.dsl.testng.TestNGCitrusTestRunner;3import com.consol.citrus.sql.message.SqlMessage;4import org.testng.annotations.Test;5public class ExecuteSQLQueryActionJavaIT extends TestNGCitrusTestRunner {6    public void executeSQLQueryActionJavaIT() {7        variable("sqlQuery", "SELECT * FROM CUSTOMER WHERE ID = 1");8        executeSQLQuery()9                .statement("${sqlQuery}")10                .validateSqlStatement("SELECT * FROM CUSTOMER WHERE ID = 1")11                .validateSqlStatement("${sqlQuery}")12                .validateSqlStatement("SELECT * FROM CUSTOMER WHERE ID = 2")13                .validateSqlStatement("${sqlQuery} AND ID = 2")14                .extractFromResult("ID", "customerId")15                .extractFromResult("FIRSTNAME", "customerFirstName")16                .extractFromResult("LASTNAME", "customerLastName")17                .extractFromResult("ADDRESS", "customerAddress")18                .extractFromResult("CITY", "customerCity")19                .extractFromResult("ZIP", "customerZip")20                .extractFromResult("STATE", "customerState")21                .extractFromResult("COUNTRY", "customerCountry")22                .validate("customerId", "1")23                .validate("customerFirstName", "John")24                .validate("customerLastName", "Doe")25                .validate("customerAddress", "123 Main Street")26                .validate("customerCity", "Anytown")27                .validate("customerZip", "12345")28                .validate("customerState", "CA")29                .validate("customerCountry", "US")30                .validate("ID", "1")31                .validate("FIRSTNAME", "John")32                .validate("LASTNAME", "Doe")33                .validate("ADDRESS", "123 Main Street")34                .validate("CITY", "Anytown")35                .validate("ZIP", "12345")36                .validate("STATE", "CA")37                .validate("COUNTRY", "US")38                .validate("ID", "2")39                .validate("FIRSTNAME", "Jane")40                .validate("LASTNAME", "Doe")41                .validate("ADDRESS", "123 Main Street")42                .validate("CITY", "Anytown")43                .validate("ZIP", "12345")

validateSqlStatement

Using AI Code Generation

1ExecuteSQLQueryAction.Builder sqlQueryActionBuilder = new ExecuteSQLQueryAction.Builder();2    .statement("SELECT * FROM test_table WHERE id = 1")3    .validateSqlStatement("SELECT * FROM test_table WHERE id = 1");4ExecuteSQLQueryAction.Builder sqlQueryActionBuilder = new ExecuteSQLQueryAction.Builder();5    .statement("SELECT * FROM test_table WHERE id = 1")6    .validateSqlStatement("SELECT * FROM test_table WHERE id = 1");7ExecuteSQLQueryAction sqlQueryAction = sqlQueryActionBuilder.build();8sqlQueryAction.execute(context);9ExecuteSQLQueryAction.Builder sqlQueryActionBuilder = new ExecuteSQLQueryAction.Builder();10    .statement("SELECT * FROM test_table WHERE id = 1")11    .validateSqlStatement("SELECT * FROM test_table WHERE id = 1");12ExecuteSQLQueryAction sqlQueryAction = sqlQueryActionBuilder.build();13sqlQueryAction.execute(context);14ExecuteSQLQueryAction.Builder sqlQueryActionBuilder = new ExecuteSQLQueryAction.Builder();15    .statement("SELECT * FROM test_table WHERE id = 1")16    .validateSqlStatement("SELECT * FROM test_table WHERE id = 1");17ExecuteSQLQueryAction sqlQueryAction = sqlQueryActionBuilder.build();18sqlQueryAction.execute(context);19ExecuteSQLQueryAction.Builder sqlQueryActionBuilder = new ExecuteSQLQueryAction.Builder();20    .statement("SELECT * FROM test_table WHERE id = 1")21    .validateSqlStatement("SELECT * FROM test_table WHERE id = 1");22ExecuteSQLQueryAction sqlQueryAction = sqlQueryActionBuilder.build();23sqlQueryAction.execute(context);24ExecuteSQLQueryAction.Builder sqlQueryActionBuilder = new ExecuteSQLQueryAction.Builder();25    .statement("SELECT * FROM test_table WHERE id = 1")

validateSqlStatement

Using AI Code Generation

1public void testValidateSqlStatement() {2    run(new TestCase()3        .actions(new ExecuteSQLQueryAction()4            .statement("SELECT * FROM CUSTOMERS")5            .validateSqlStatement("SELECT * FROM CUSTOMERS")6    );7}8public void testValidateSqlStatement() {9    run(new TestCase()10        .actions(new ExecuteSQLQueryAction()11            .statement("SELECT * FROM CUSTOMERS")12            .validateSqlStatement("SELECT * FROM CUSTOMERS")13    );14}15public void testValidateSqlStatement() {16    run(new TestCase()17        .actions(new ExecuteSQLQueryAction()18            .statement("SELECT * FROM CUSTOMERS")19            .validateSqlStatement("SELECT * FROM CUSTOMERS")20    );21}22public void testValidateSqlStatement() {23    run(new TestCase()24        .actions(new ExecuteSQLQueryAction()25            .statement("SELECT * FROM CUSTOMERS")26            .validateSqlStatement("SELECT * FROM CUSTOMERS")27    );28}29public void testValidateSqlStatement() {30    run(new TestCase()31        .actions(new ExecuteSQLQueryAction()32            .statement("SELECT * FROM CUSTOMERS")33            .validateSqlStatement("SELECT * FROM CUSTOMERS")34    );35}36public void testValidateSqlStatement() {37    run(new TestCase()38        .actions(new ExecuteSQLQueryAction()39            .statement("SELECT * FROM CUSTOMERS")40            .validateSqlStatement("SELECT * FROM CUSTOMERS")41    );42}43public void testValidateSqlStatement() {44    run(new TestCase()45        .actions(new ExecuteSQLQueryAction()46            .statement("SELECT * FROM CUSTOMERS")47            .validateSqlStatement("SELECT * FROM CUSTOMERS")48    );49}

validateSqlStatement

Using AI Code Generation

1public void validateSqlStatement() {2    CitrusSqlActions sqlActions = new CitrusSqlActions();3    sqlActions.setDataSource(dataSource);4    sqlActions.setSqlResource(new ClassPathResource("sql/test.sql"));5    sqlActions.setValidateSqlStatement("select * from test");6    sqlActions.execute(context);7}8public void validateSqlStatement() {9    CitrusSqlActions sqlActions = new CitrusSqlActions();10    sqlActions.setDataSource(dataSource);11    sqlActions.setSqlResource(new ClassPathResource("sql/test.sql"));12    sqlActions.setValidateSqlStatement("select * from test");13    sqlActions.execute(context);14}15public void validateSqlStatement() {16    CitrusSqlActions sqlActions = new CitrusSqlActions();17    sqlActions.setDataSource(dataSource);18    sqlActions.setSqlResource(new ClassPathResource("sql/test.sql"));19    sqlActions.setValidateSqlStatement("select * from test");20    sqlActions.execute(context);21}22public void validateSqlStatement() {23    CitrusSqlActions sqlActions = new CitrusSqlActions();24    sqlActions.setDataSource(dataSource);25    sqlActions.setSqlResource(new ClassPathResource("sql/test.sql"));26    sqlActions.setValidateSqlStatement("select * from test");27    sqlActions.execute(context);28}29public void validateSqlStatement() {30    CitrusSqlActions sqlActions = new CitrusSqlActions();31    sqlActions.setDataSource(dataSource);32    sqlActions.setSqlResource(new ClassPathResource("sql/test.sql"));33    sqlActions.setValidateSqlStatement("select * from test");34    sqlActions.execute(context);35}36public void validateSqlStatement() {37    CitrusSqlActions sqlActions = new CitrusSqlActions();38    sqlActions.setDataSource(dataSource);39    sqlActions.setSqlResource(new ClassPathResource("sql/test.sql"));40    sqlActions.setValidateSqlStatement("select * from test");41    sqlActions.execute(context

Automation Testing Tutorials

Learn to execute automation testing from scratch with LambdaTest Learning Hub. Right from setting up the prerequisites to run your first automation test, to following best practices and diving deeper into advanced test scenarios. LambdaTest Learning Hubs compile a list of step-by-step guides to help you be proficient with different test automation frameworks i.e. Selenium, Cypress, TestNG etc.