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

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

Source:ExecuteSQLQueryTestRunnerTest.java

...14 * limitations under the License.15 */16package com.consol.citrus.dsl.runner;17import com.consol.citrus.TestCase;18import com.consol.citrus.actions.ExecuteSQLQueryAction;19import com.consol.citrus.context.TestContext;20import com.consol.citrus.script.ScriptTypes;21import com.consol.citrus.testng.AbstractTestNGUnitTest;22import com.consol.citrus.validation.script.ScriptValidationContext;23import com.consol.citrus.validation.script.sql.SqlResultSetScriptValidator;24import org.mockito.Mockito;25import org.springframework.core.io.ClassPathResource;26import org.springframework.core.io.Resource;27import org.springframework.jdbc.core.JdbcTemplate;28import org.springframework.transaction.PlatformTransactionManager;29import org.testng.Assert;30import org.testng.annotations.Test;31import java.io.ByteArrayInputStream;32import java.io.IOException;33import java.util.*;34import static org.mockito.Mockito.*;35/**36 * @author Christoph Deppisch37 * @since 2.338 */39public class ExecuteSQLQueryTestRunnerTest extends AbstractTestNGUnitTest {40    private JdbcTemplate jdbcTemplate = Mockito.mock(JdbcTemplate.class);41    private PlatformTransactionManager transactionManager = Mockito.mock(PlatformTransactionManager.class);42    private Resource resource = Mockito.mock(Resource.class);43    private SqlResultSetScriptValidator validator = Mockito.mock(SqlResultSetScriptValidator.class);44    45    @Test46    public void testExecuteSQLQueryWithResource() throws IOException {47        List<Map<String, Object>> results = new ArrayList<>();48        results.add(Collections.<String, Object>singletonMap("NAME", "Leonard"));49        reset(jdbcTemplate);50        when(jdbcTemplate.queryForList(anyString())).thenReturn(results)51                                                    .thenReturn(Collections.singletonList(Collections.<String, Object>singletonMap("CNT_EPISODES", "100000")));52        MockTestRunner builder = new MockTestRunner(getClass().getSimpleName(), applicationContext, context) {53            @Override54            public void execute() {55                variable("episodeId", "citrus:randomNumber(5)");56                query(builder -> builder.jdbcTemplate(jdbcTemplate)57                        .sqlResource(new ClassPathResource("com/consol/citrus/dsl/runner/query-script.sql"))58                        .validate("NAME", "Leonard")59                        .validate("CNT_EPISODES", "100000")60                        .extract("NAME", "actorName"));61            }62        };63        TestContext context = builder.getTestContext();64        Assert.assertNotNull(context.getVariable("NAME"));65        Assert.assertNotNull(context.getVariable("actorName"));66        Assert.assertNotNull(context.getVariable("CNT_EPISODES"));67        Assert.assertEquals(context.getVariable("NAME"), "Leonard");68        Assert.assertEquals(context.getVariable("actorName"), "Leonard");69        Assert.assertEquals(context.getVariable("CNT_EPISODES"), "100000");70        TestCase test = builder.getTestCase();71        Assert.assertEquals(test.getActionCount(), 1);72        Assert.assertEquals(test.getActions().get(0).getClass(), ExecuteSQLQueryAction.class);73        74        ExecuteSQLQueryAction action = (ExecuteSQLQueryAction)test.getActions().get(0);75        76        Assert.assertEquals(action.getName(), "sql-query");77        Assert.assertEquals(action.getControlResultSet().size(), 2);78        Set<Map.Entry<String, List<String>>> rows = action.getControlResultSet().entrySet();79        Assert.assertEquals(getRow("NAME", rows).toString(), "NAME=[Leonard]");80        Assert.assertEquals(getRow("CNT_EPISODES", rows).toString(), "CNT_EPISODES=[100000]");81        Assert.assertEquals(action.getExtractVariables().size(), 1);82        Assert.assertEquals(action.getExtractVariables().entrySet().iterator().next().toString(), "NAME=actorName");83        Assert.assertNull(action.getScriptValidationContext());84        Assert.assertEquals(action.getJdbcTemplate(), jdbcTemplate);85        Assert.assertEquals(action.getStatements().size(), 2);86        Assert.assertNull(action.getSqlResourcePath());87        Assert.assertNull(action.getValidator());88    }89    90    @Test91    public void testExecuteSQLQueryWithStatements() {92        List<Map<String, Object>> results = new ArrayList<>();93        results.add(Collections.<String, Object>singletonMap("NAME", "Penny"));94        results.add(Collections.<String, Object>singletonMap("NAME", "Sheldon"));95        reset(jdbcTemplate);96        when(jdbcTemplate.queryForList("SELECT NAME FROM ACTORS")).thenReturn(results);97        when(jdbcTemplate.queryForList("SELECT COUNT(*) as CNT_EPISODES FROM EPISODES")).thenReturn(Collections.singletonList(Collections.<String, Object>singletonMap("CNT_EPISODES", "9999")));98        MockTestRunner builder = new MockTestRunner(getClass().getSimpleName(), applicationContext, context) {99            @Override100            public void execute() {101                query(builder -> builder.jdbcTemplate(jdbcTemplate)102                        .statement("SELECT NAME FROM ACTORS")103                        .statement("SELECT COUNT(*) as CNT_EPISODES FROM EPISODES")104                        .validate("NAME", "Penny", "Sheldon")105                        .validate("CNT_EPISODES", "9999")106                        .extract("CNT_EPISODES", "cntEpisodes"));107            }108        };109        TestContext context = builder.getTestContext();110        Assert.assertNotNull(context.getVariable("NAME"));111        Assert.assertNotNull(context.getVariable("CNT_EPISODES"));112        Assert.assertNotNull(context.getVariable("cntEpisodes"));113        Assert.assertEquals(context.getVariable("NAME"), "Penny");114        Assert.assertEquals(context.getVariable("CNT_EPISODES"), "9999");115        Assert.assertEquals(context.getVariable("cntEpisodes"), "9999");116        TestCase test = builder.getTestCase();117        Assert.assertEquals(test.getActionCount(), 1);118        Assert.assertEquals(test.getActions().get(0).getClass(), ExecuteSQLQueryAction.class);119        120        ExecuteSQLQueryAction action = (ExecuteSQLQueryAction)test.getActions().get(0);121        122        Assert.assertEquals(action.getName(), "sql-query");123        Assert.assertEquals(action.getControlResultSet().size(), 2);124        Set<Map.Entry<String, List<String>>> rows = action.getControlResultSet().entrySet();125        Assert.assertEquals(getRow("NAME", rows).toString(), "NAME=[Penny, Sheldon]");126        Assert.assertEquals(getRow("CNT_EPISODES", rows).toString(), "CNT_EPISODES=[9999]");127        Assert.assertEquals(action.getExtractVariables().size(), 1);128        Assert.assertEquals(action.getExtractVariables().entrySet().iterator().next().toString(), "CNT_EPISODES=cntEpisodes");129        Assert.assertEquals(action.getStatements().size(), 2);130        Assert.assertEquals(action.getStatements().toString(), "[SELECT NAME FROM ACTORS, SELECT COUNT(*) as CNT_EPISODES FROM EPISODES]");131        Assert.assertNull(action.getScriptValidationContext());132        Assert.assertEquals(action.getJdbcTemplate(), jdbcTemplate);133        Assert.assertNull(action.getValidator());134    }135    @Test136    public void testExecuteSQLQueryWithTransaction() {137        List<Map<String, Object>> results = new ArrayList<>();138        results.add(Collections.<String, Object>singletonMap("NAME", "Penny"));139        results.add(Collections.<String, Object>singletonMap("NAME", "Sheldon"));140        reset(jdbcTemplate, transactionManager);141        when(jdbcTemplate.queryForList("SELECT NAME FROM ACTORS")).thenReturn(results);142        when(jdbcTemplate.queryForList("SELECT COUNT(*) as CNT_EPISODES FROM EPISODES")).thenReturn(Collections.singletonList(Collections.<String, Object>singletonMap("CNT_EPISODES", "9999")));143        MockTestRunner builder = new MockTestRunner(getClass().getSimpleName(), applicationContext, context) {144            @Override145            public void execute() {146                query(builder -> builder.jdbcTemplate(jdbcTemplate)147                        .transactionManager(transactionManager)148                        .transactionTimeout(5000)149                        .transactionIsolationLevel("ISOLATION_READ_COMMITTED")150                        .statement("SELECT NAME FROM ACTORS")151                        .statement("SELECT COUNT(*) as CNT_EPISODES FROM EPISODES")152                        .validate("NAME", "Penny", "Sheldon")153                        .validate("CNT_EPISODES", "9999")154                        .extract("CNT_EPISODES", "cntEpisodes"));155            }156        };157        TestContext context = builder.getTestContext();158        Assert.assertNotNull(context.getVariable("NAME"));159        Assert.assertNotNull(context.getVariable("CNT_EPISODES"));160        Assert.assertNotNull(context.getVariable("cntEpisodes"));161        Assert.assertEquals(context.getVariable("NAME"), "Penny");162        Assert.assertEquals(context.getVariable("CNT_EPISODES"), "9999");163        Assert.assertEquals(context.getVariable("cntEpisodes"), "9999");164        TestCase test = builder.getTestCase();165        Assert.assertEquals(test.getActionCount(), 1);166        Assert.assertEquals(test.getActions().get(0).getClass(), ExecuteSQLQueryAction.class);167        ExecuteSQLQueryAction action = (ExecuteSQLQueryAction)test.getActions().get(0);168        Assert.assertEquals(action.getName(), "sql-query");169        Assert.assertEquals(action.getControlResultSet().size(), 2);170        Set<Map.Entry<String, List<String>>> rows = action.getControlResultSet().entrySet();171        Assert.assertEquals(getRow("NAME", rows).toString(), "NAME=[Penny, Sheldon]");172        Assert.assertEquals(getRow("CNT_EPISODES", rows).toString(), "CNT_EPISODES=[9999]");173        Assert.assertEquals(action.getExtractVariables().size(), 1);174        Assert.assertEquals(action.getExtractVariables().entrySet().iterator().next().toString(), "CNT_EPISODES=cntEpisodes");175        Assert.assertEquals(action.getStatements().size(), 2);176        Assert.assertEquals(action.getStatements().toString(), "[SELECT NAME FROM ACTORS, SELECT COUNT(*) as CNT_EPISODES FROM EPISODES]");177        Assert.assertNull(action.getScriptValidationContext());178        Assert.assertEquals(action.getJdbcTemplate(), jdbcTemplate);179        Assert.assertEquals(action.getTransactionManager(), transactionManager);180        Assert.assertEquals(action.getTransactionTimeout(), "5000");181        Assert.assertEquals(action.getTransactionIsolationLevel(), "ISOLATION_READ_COMMITTED");182        Assert.assertNull(action.getValidator());183    }184    @Test185    public void testValidationScript() {186        List<Map<String, Object>> results = new ArrayList<>();187        results.add(Collections.<String, Object>singletonMap("NAME", "Penny"));188        results.add(Collections.<String, Object>singletonMap("NAME", "Sheldon"));189        reset(jdbcTemplate);190        when(jdbcTemplate.queryForList("SELECT NAME FROM ACTORS")).thenReturn(results);191        MockTestRunner builder = new MockTestRunner(getClass().getSimpleName(), applicationContext, context) {192            @Override193            public void execute() {194                query(builder -> builder.jdbcTemplate(jdbcTemplate)195                        .statement("SELECT NAME FROM ACTORS")196                        .validateScript("assert rows[0].NAME == 'Penny'\n" +197                                "assert rows[1].NAME == 'Sheldon'", ScriptTypes.GROOVY));198            }199        };200        TestCase test = builder.getTestCase();201        Assert.assertEquals(test.getActionCount(), 1);202        Assert.assertEquals(test.getActions().get(0).getClass(), ExecuteSQLQueryAction.class);203        204        ExecuteSQLQueryAction action = (ExecuteSQLQueryAction)test.getActions().get(0);205        206        Assert.assertEquals(action.getName(), "sql-query");207        Assert.assertEquals(action.getControlResultSet().size(), 0);208        Assert.assertEquals(action.getExtractVariables().size(), 0);209        Assert.assertNotNull(action.getScriptValidationContext());210        Assert.assertTrue(action.getScriptValidationContext().getValidationScript().startsWith("assert rows[0].NAME == 'Penny'"));211        Assert.assertNull(action.getScriptValidationContext().getValidationScriptResourcePath());212        Assert.assertEquals(action.getStatements().size(), 1);213        Assert.assertEquals(action.getStatements().toString(), "[SELECT NAME FROM ACTORS]");214        Assert.assertEquals(action.getJdbcTemplate(), jdbcTemplate);215    }216    217    @Test218    public void testValidationScriptResource() throws IOException {219        List<Map<String, Object>> results = new ArrayList<>();220        results.add(Collections.<String, Object>singletonMap("NAME", "Radj"));221        reset(jdbcTemplate, resource);222        when(resource.getInputStream()).thenReturn(new ByteArrayInputStream("assert rows[0].NAME == 'Radj'".getBytes()));223        when(jdbcTemplate.queryForList("SELECT NAME FROM ACTORS")).thenReturn(results);224        MockTestRunner builder = new MockTestRunner(getClass().getSimpleName(), applicationContext, context) {225            @Override226            public void execute() {227                query(builder -> builder.jdbcTemplate(jdbcTemplate)228                        .statement("SELECT NAME FROM ACTORS")229                        .validateScript(resource, ScriptTypes.GROOVY));230            }231        };232        TestCase test = builder.getTestCase();233        Assert.assertEquals(test.getActionCount(), 1);234        Assert.assertEquals(test.getActions().get(0).getClass(), ExecuteSQLQueryAction.class);235        236        ExecuteSQLQueryAction action = (ExecuteSQLQueryAction)test.getActions().get(0);237        238        Assert.assertEquals(action.getName(), "sql-query");239        Assert.assertEquals(action.getControlResultSet().size(), 0);240        Assert.assertEquals(action.getExtractVariables().size(), 0);241        Assert.assertNotNull(action.getScriptValidationContext());242        Assert.assertEquals(action.getScriptValidationContext().getValidationScript(), "assert rows[0].NAME == 'Radj'");243        Assert.assertNull(action.getScriptValidationContext().getValidationScriptResourcePath());244        Assert.assertEquals(action.getStatements().size(), 1);245        Assert.assertEquals(action.getStatements().toString(), "[SELECT NAME FROM ACTORS]");246        Assert.assertEquals(action.getJdbcTemplate(), jdbcTemplate);247    }248    249    @Test250    public void testGroovyValidationScript() {251        List<Map<String, Object>> results = new ArrayList<>();252        results.add(Collections.<String, Object>singletonMap("NAME", "Howard"));253        results.add(Collections.<String, Object>singletonMap("NAME", "Sheldon"));254        reset(jdbcTemplate);255        when(jdbcTemplate.queryForList("SELECT NAME FROM ACTORS")).thenReturn(results);256        MockTestRunner builder = new MockTestRunner(getClass().getSimpleName(), applicationContext, context) {257            @Override258            public void execute() {259                query(builder -> builder.jdbcTemplate(jdbcTemplate)260                        .statement("SELECT NAME FROM ACTORS")261                        .groovy("assert rows[0].NAME == 'Howard'"));262            }263        };264        TestCase test = builder.getTestCase();265        Assert.assertEquals(test.getActionCount(), 1);266        Assert.assertEquals(test.getActions().get(0).getClass(), ExecuteSQLQueryAction.class);267        268        ExecuteSQLQueryAction action = (ExecuteSQLQueryAction)test.getActions().get(0);269        270        Assert.assertEquals(action.getName(), "sql-query");271        Assert.assertEquals(action.getControlResultSet().size(), 0);272        Assert.assertEquals(action.getExtractVariables().size(), 0);273        Assert.assertNotNull(action.getScriptValidationContext());274        Assert.assertEquals(action.getScriptValidationContext().getValidationScript(), "assert rows[0].NAME == 'Howard'");275        Assert.assertNull(action.getScriptValidationContext().getValidationScriptResourcePath());276        Assert.assertEquals(action.getStatements().size(), 1);277        Assert.assertEquals(action.getStatements().toString(), "[SELECT NAME FROM ACTORS]");278        Assert.assertEquals(action.getJdbcTemplate(), jdbcTemplate);279    }280    281    @Test282    public void testGroovyValidationScriptResource() throws IOException {283        List<Map<String, Object>> results = new ArrayList<>();284        results.add(Collections.<String, Object>singletonMap("NAME", "Penny"));285        results.add(Collections.<String, Object>singletonMap("NAME", "Howard"));286        results.add(Collections.<String, Object>singletonMap("NAME", "Sheldon"));287        reset(jdbcTemplate, resource);288        when(resource.getInputStream()).thenReturn(new ByteArrayInputStream("assert rows[1].NAME == 'Howard'".getBytes()));289        when(jdbcTemplate.queryForList("SELECT NAME FROM ACTORS")).thenReturn(results);290        MockTestRunner builder = new MockTestRunner(getClass().getSimpleName(), applicationContext, context) {291            @Override292            public void execute() {293                query(builder -> builder.jdbcTemplate(jdbcTemplate)294                        .statement("SELECT NAME FROM ACTORS")295                        .groovy(resource));296            }297        };298        299        TestCase test = builder.getTestCase();300        Assert.assertEquals(test.getActionCount(), 1);301        Assert.assertEquals(test.getActions().get(0).getClass(), ExecuteSQLQueryAction.class);302        303        ExecuteSQLQueryAction action = (ExecuteSQLQueryAction)test.getActions().get(0);304        305        Assert.assertEquals(action.getName(), "sql-query");306        Assert.assertEquals(action.getControlResultSet().size(), 0);307        Assert.assertEquals(action.getExtractVariables().size(), 0);308        Assert.assertNotNull(action.getScriptValidationContext());309        Assert.assertEquals(action.getScriptValidationContext().getValidationScript(), "assert rows[1].NAME == 'Howard'");310        Assert.assertNull(action.getScriptValidationContext().getValidationScriptResourcePath());311        Assert.assertEquals(action.getStatements().size(), 1);312        Assert.assertEquals(action.getStatements().toString(), "[SELECT NAME FROM ACTORS]");313        Assert.assertEquals(action.getJdbcTemplate(), jdbcTemplate);314    }315    @Test316    public void testCustomScriptValidator() {317        List<Map<String, Object>> results = new ArrayList<>();318        results.add(Collections.<String, Object>singletonMap("NAME", "Howard"));319        results.add(Collections.<String, Object>singletonMap("NAME", "Penny"));320        results.add(Collections.<String, Object>singletonMap("NAME", "Sheldon"));321        reset(jdbcTemplate, validator);322        when(jdbcTemplate.queryForList("SELECT NAME FROM ACTORS")).thenReturn(results);323        MockTestRunner builder = new MockTestRunner(getClass().getSimpleName(), applicationContext, context) {324            @Override325            public void execute() {326                query(builder -> builder.jdbcTemplate(jdbcTemplate)327                        .statement("SELECT NAME FROM ACTORS")328                        .groovy("assert rows[0].NAME == 'Howard'")329                        .validator(validator));330            }331        };332        TestCase test = builder.getTestCase();333        Assert.assertEquals(test.getActionCount(), 1);334        Assert.assertEquals(test.getActions().get(0).getClass(), ExecuteSQLQueryAction.class);335        ExecuteSQLQueryAction action = (ExecuteSQLQueryAction)test.getActions().get(0);336        Assert.assertEquals(action.getName(), "sql-query");337        Assert.assertEquals(action.getControlResultSet().size(), 0);338        Assert.assertEquals(action.getExtractVariables().size(), 0);339        Assert.assertNotNull(action.getScriptValidationContext());340        Assert.assertEquals(action.getScriptValidationContext().getValidationScript(), "assert rows[0].NAME == 'Howard'");341        Assert.assertNull(action.getScriptValidationContext().getValidationScriptResourcePath());342        Assert.assertEquals(action.getStatements().size(), 1);343        Assert.assertEquals(action.getStatements().toString(), "[SELECT NAME FROM ACTORS]");344        Assert.assertEquals(action.getJdbcTemplate(), jdbcTemplate);345        Assert.assertEquals(action.getValidator(), validator);346        verify(validator).validateSqlResultSet(any(List.class), any(ScriptValidationContext.class), any(TestContext.class));347    }348    /**349     * Gets row from result set with given column name....

Source:ExecuteSQLQueryTestDesignerTest.java

...14 * limitations under the License.15 */16package com.consol.citrus.dsl.design;17import com.consol.citrus.TestCase;18import com.consol.citrus.actions.ExecuteSQLQueryAction;19import com.consol.citrus.script.ScriptTypes;20import com.consol.citrus.testng.AbstractTestNGUnitTest;21import com.consol.citrus.validation.script.sql.SqlResultSetScriptValidator;22import org.mockito.Mockito;23import org.springframework.core.io.Resource;24import org.springframework.transaction.PlatformTransactionManager;25import org.testng.Assert;26import org.testng.annotations.Test;27import javax.sql.DataSource;28import java.io.*;29import static org.mockito.Mockito.reset;30import static org.mockito.Mockito.when;31/**32 * @author Christoph Deppisch33 * @since 2.334 */35public class ExecuteSQLQueryTestDesignerTest extends AbstractTestNGUnitTest {36    private DataSource dataSource = Mockito.mock(DataSource.class);37    private PlatformTransactionManager transactionManager = Mockito.mock(PlatformTransactionManager.class);38    private Resource resource = Mockito.mock(Resource.class);39    private SqlResultSetScriptValidator validator = Mockito.mock(SqlResultSetScriptValidator.class);40    41    @Test42    public void testExecuteSQLQueryWithResource() throws IOException {43        MockTestDesigner builder = new MockTestDesigner(applicationContext, context) {44            @Override45            public void configure() {46                query(dataSource)47                    .sqlResource(resource)48                    .validate("COLUMN", "value")49                    .extract("COLUMN", "variable");50            }51        };52        53        reset(resource);54        when(resource.getFile()).thenReturn(Mockito.mock(File.class));55        when(resource.getInputStream()).thenReturn(new ByteArrayInputStream("SELECT * FROM DUAL;".getBytes()));56        builder.configure();57        TestCase test = builder.getTestCase();58        Assert.assertEquals(test.getActionCount(), 1);59        Assert.assertEquals(test.getActions().get(0).getClass(), ExecuteSQLQueryAction.class);60        61        ExecuteSQLQueryAction action = (ExecuteSQLQueryAction)test.getActions().get(0);62        63        Assert.assertEquals(action.getName(), "sql-query");64        Assert.assertEquals(action.getControlResultSet().size(), 1);65        Assert.assertEquals(action.getControlResultSet().entrySet().iterator().next().toString(), "COLUMN=[value]");66        Assert.assertEquals(action.getExtractVariables().size(), 1);67        Assert.assertEquals(action.getExtractVariables().entrySet().iterator().next().toString(), "COLUMN=variable");68        Assert.assertNull(action.getScriptValidationContext());69        Assert.assertEquals(action.getDataSource(), dataSource);70        Assert.assertEquals(action.getStatements().size(), 1);71        Assert.assertEquals(action.getStatements().get(0), "SELECT * FROM DUAL;");72        Assert.assertNull(action.getSqlResourcePath());73        Assert.assertNull(action.getValidator());74    }75    76    @Test77    public void testExecuteSQLQueryWithStatements() {78        MockTestDesigner builder = new MockTestDesigner(applicationContext, context) {79            @Override80            public void configure() {81                query(dataSource)82                    .statement("stmt1")83                    .statement("stmt2")84                    .statement("stmt3")85                    .validate("COLUMN", "value1", "value2")86                    .extract("COLUMN", "variable");87            }88        };89        builder.configure();90        TestCase test = builder.getTestCase();91        Assert.assertEquals(test.getActionCount(), 1);92        Assert.assertEquals(test.getActions().get(0).getClass(), ExecuteSQLQueryAction.class);93        94        ExecuteSQLQueryAction action = (ExecuteSQLQueryAction)test.getActions().get(0);95        96        Assert.assertEquals(action.getName(), "sql-query");97        Assert.assertEquals(action.getControlResultSet().size(), 1);98        Assert.assertEquals(action.getControlResultSet().entrySet().iterator().next().toString(), "COLUMN=[value1, value2]");99        Assert.assertEquals(action.getExtractVariables().size(), 1);100        Assert.assertEquals(action.getExtractVariables().entrySet().iterator().next().toString(), "COLUMN=variable");101        Assert.assertEquals(action.getStatements().size(), 3);102        Assert.assertEquals(action.getStatements().toString(), "[stmt1, stmt2, stmt3]");103        Assert.assertNull(action.getScriptValidationContext());104        Assert.assertEquals(action.getDataSource(), dataSource);105        Assert.assertNull(action.getValidator());106    }107    @Test108    public void testExecuteSQLQueryWithTransaction() {109        MockTestDesigner builder = new MockTestDesigner(applicationContext, context) {110            @Override111            public void configure() {112                query(dataSource)113                    .transactionManager(transactionManager)114                    .transactionTimeout(5000)115                    .transactionIsolationLevel("ISOLATION_READ_COMMITTED")116                    .statement("stmt1")117                    .statement("stmt2")118                    .statement("stmt3")119                    .validate("COLUMN", "value1", "value2")120                    .extract("COLUMN", "variable");121            }122        };123        builder.configure();124        TestCase test = builder.getTestCase();125        Assert.assertEquals(test.getActionCount(), 1);126        Assert.assertEquals(test.getActions().get(0).getClass(), ExecuteSQLQueryAction.class);127        ExecuteSQLQueryAction action = (ExecuteSQLQueryAction)test.getActions().get(0);128        Assert.assertEquals(action.getName(), "sql-query");129        Assert.assertEquals(action.getControlResultSet().size(), 1);130        Assert.assertEquals(action.getControlResultSet().entrySet().iterator().next().toString(), "COLUMN=[value1, value2]");131        Assert.assertEquals(action.getExtractVariables().size(), 1);132        Assert.assertEquals(action.getExtractVariables().entrySet().iterator().next().toString(), "COLUMN=variable");133        Assert.assertEquals(action.getStatements().size(), 3);134        Assert.assertEquals(action.getStatements().toString(), "[stmt1, stmt2, stmt3]");135        Assert.assertNull(action.getScriptValidationContext());136        Assert.assertEquals(action.getDataSource(), dataSource);137        Assert.assertNull(action.getValidator());138        Assert.assertEquals(action.getTransactionManager(), transactionManager);139        Assert.assertEquals(action.getTransactionTimeout(), "5000");140        Assert.assertEquals(action.getTransactionIsolationLevel(), "ISOLATION_READ_COMMITTED");141    }142    143    @Test144    public void testValidationScript() {145        MockTestDesigner builder = new MockTestDesigner(applicationContext, context) {146            @Override147            public void configure() {148                query(dataSource)149                    .statement("stmt")150                    .validateScript("assert rows[0].COLUMN == 'value1'", ScriptTypes.GROOVY);151            }152        };153        builder.configure();154        TestCase test = builder.getTestCase();155        Assert.assertEquals(test.getActionCount(), 1);156        Assert.assertEquals(test.getActions().get(0).getClass(), ExecuteSQLQueryAction.class);157        158        ExecuteSQLQueryAction action = (ExecuteSQLQueryAction)test.getActions().get(0);159        160        Assert.assertEquals(action.getName(), "sql-query");161        Assert.assertEquals(action.getControlResultSet().size(), 0);162        Assert.assertEquals(action.getExtractVariables().size(), 0);163        Assert.assertNotNull(action.getScriptValidationContext());164        Assert.assertEquals(action.getScriptValidationContext().getValidationScript(), "assert rows[0].COLUMN == 'value1'");165        Assert.assertNull(action.getScriptValidationContext().getValidationScriptResourcePath());166        Assert.assertEquals(action.getStatements().size(), 1);167        Assert.assertEquals(action.getStatements().toString(), "[stmt]");168        Assert.assertEquals(action.getDataSource(), dataSource);169    }170    171    @Test172    public void testValidationScriptResource() throws IOException {173        MockTestDesigner builder = new MockTestDesigner(applicationContext, context) {174            @Override175            public void configure() {176                query(dataSource)177                    .statement("stmt")178                    .validateScript(resource, ScriptTypes.GROOVY);179            }180        };181        182        reset(resource);183        when(resource.getInputStream()).thenReturn(new ByteArrayInputStream("someScript".getBytes()));184        builder.configure();185        TestCase test = builder.getTestCase();186        Assert.assertEquals(test.getActionCount(), 1);187        Assert.assertEquals(test.getActions().get(0).getClass(), ExecuteSQLQueryAction.class);188        189        ExecuteSQLQueryAction action = (ExecuteSQLQueryAction)test.getActions().get(0);190        191        Assert.assertEquals(action.getName(), "sql-query");192        Assert.assertEquals(action.getControlResultSet().size(), 0);193        Assert.assertEquals(action.getExtractVariables().size(), 0);194        Assert.assertNotNull(action.getScriptValidationContext());195        Assert.assertEquals(action.getScriptValidationContext().getValidationScript(), "someScript");196        Assert.assertNull(action.getScriptValidationContext().getValidationScriptResourcePath());197        Assert.assertEquals(action.getStatements().size(), 1);198        Assert.assertEquals(action.getStatements().toString(), "[stmt]");199        Assert.assertEquals(action.getDataSource(), dataSource);200    }201    202    @Test203    public void testGroovyValidationScript() {204        MockTestDesigner builder = new MockTestDesigner(applicationContext, context) {205            @Override206            public void configure() {207                query(dataSource)208                    .statement("stmt")209                    .groovy("assert rows[0].COLUMN == 'value1'");210            }211        };212        builder.configure();213        TestCase test = builder.getTestCase();214        Assert.assertEquals(test.getActionCount(), 1);215        Assert.assertEquals(test.getActions().get(0).getClass(), ExecuteSQLQueryAction.class);216        217        ExecuteSQLQueryAction action = (ExecuteSQLQueryAction)test.getActions().get(0);218        219        Assert.assertEquals(action.getName(), "sql-query");220        Assert.assertEquals(action.getControlResultSet().size(), 0);221        Assert.assertEquals(action.getExtractVariables().size(), 0);222        Assert.assertNotNull(action.getScriptValidationContext());223        Assert.assertEquals(action.getScriptValidationContext().getValidationScript(), "assert rows[0].COLUMN == 'value1'");224        Assert.assertNull(action.getScriptValidationContext().getValidationScriptResourcePath());225        Assert.assertEquals(action.getStatements().size(), 1);226        Assert.assertEquals(action.getStatements().toString(), "[stmt]");227        Assert.assertEquals(action.getDataSource(), dataSource);228    }229    230    @Test231    public void testGroovyValidationScriptResource() throws IOException {232        MockTestDesigner builder = new MockTestDesigner(applicationContext, context) {233            @Override234            public void configure() {235                query(dataSource)236                    .statement("stmt")237                    .groovy(resource);238            }239        };240        241        reset(resource);242        when(resource.getInputStream()).thenReturn(new ByteArrayInputStream("someScript".getBytes()));243        builder.configure();244        TestCase test = builder.getTestCase();245        Assert.assertEquals(test.getActionCount(), 1);246        Assert.assertEquals(test.getActions().get(0).getClass(), ExecuteSQLQueryAction.class);247        248        ExecuteSQLQueryAction action = (ExecuteSQLQueryAction)test.getActions().get(0);249        250        Assert.assertEquals(action.getName(), "sql-query");251        Assert.assertEquals(action.getControlResultSet().size(), 0);252        Assert.assertEquals(action.getExtractVariables().size(), 0);253        Assert.assertNotNull(action.getScriptValidationContext());254        Assert.assertEquals(action.getScriptValidationContext().getValidationScript(), "someScript");255        Assert.assertNull(action.getScriptValidationContext().getValidationScriptResourcePath());256        Assert.assertEquals(action.getStatements().size(), 1);257        Assert.assertEquals(action.getStatements().toString(), "[stmt]");258        Assert.assertEquals(action.getDataSource(), dataSource);259    }260    @Test261    public void testCustomScriptValidator() {262        MockTestDesigner builder = new MockTestDesigner(applicationContext, context) {263            @Override264            public void configure() {265                query(dataSource)266                        .statement("stmt")267                        .validateScript("assert something", ScriptTypes.GROOVY)268                        .validator(validator);269            }270        };271        builder.configure();272        TestCase test = builder.getTestCase();273        Assert.assertEquals(test.getActionCount(), 1);274        Assert.assertEquals(test.getActions().get(0).getClass(), ExecuteSQLQueryAction.class);275        ExecuteSQLQueryAction action = (ExecuteSQLQueryAction)test.getActions().get(0);276        Assert.assertEquals(action.getName(), "sql-query");277        Assert.assertEquals(action.getControlResultSet().size(), 0);278        Assert.assertEquals(action.getExtractVariables().size(), 0);279        Assert.assertNotNull(action.getScriptValidationContext());280        Assert.assertEquals(action.getScriptValidationContext().getValidationScript(), "assert something");281        Assert.assertNull(action.getScriptValidationContext().getValidationScriptResourcePath());282        Assert.assertEquals(action.getStatements().size(), 1);283        Assert.assertEquals(action.getStatements().toString(), "[stmt]");284        Assert.assertEquals(action.getDataSource(), dataSource);285        Assert.assertEquals(action.getValidator(), validator);286    }287}...

Source:ItemTest.java

...7import org.springframework.beans.factory.annotation.Autowired;8import org.springframework.http.HttpStatus;9import org.testng.annotations.Test;10import static com.consol.citrus.actions.ExecuteSQLAction.Builder.sql;11import static com.consol.citrus.actions.ExecuteSQLQueryAction.Builder.query;12import static com.consol.citrus.http.actions.HttpActionBuilder.http;13import static com.consol.citrus.validation.json.JsonPathMessageValidationContext.Builder.jsonPath;14@Test15public class ItemTest extends TestNGCitrusSpringSupport {16    @Autowired17    private HttpClient secondHandApiEndpoint;18    @Autowired19    private BasicDataSource secondHandApiDataSource;20    @CitrusTest21    public void getOneItem() {22        description("WENN ein Item in der Datenbank vorhanden ist " +23                "UND alle Items abegrufen werden " +24                "DANN sollte die API ein JSON mit einem Item herausgeben");25        $(sql(secondHandApiDataSource)...

ExecuteSQLQueryAction

Using AI Code Generation

1package com.consol.citrus.sql.actions;2import com.consol.citrus.annotations.CitrusTest;3import com.consol.citrus.testng.CitrusParameters;4import com.consol.citrus.testng.CitrusXmlTestNG;5import org.testng.annotations.Test;6public class ExecuteSQLQueryActionJavaIT extends CitrusXmlTestNG {7    @Test(dataProvider = "testDataProvider")8    @CitrusParameters("testName")9    public void ExecuteSQLQueryActionJavaIT(String testName) {10        executeTest(testName);11    }12    protected String getTestPath() {13        return "src/test/resources/com/consol/citrus/sql/actions/ExecuteSQLQueryActionJavaIT";14    }15}16    <variable name="statement" value="CREATE TABLE TEST_TABLE (ID INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, NAME VARCHAR(255))"/>17    <variable name="statement1" value="INSERT INTO TEST_TABLE (NAME) VALUES ('citrus:concat(''Hello '', citrus:randomNumber(3))')"/>18    <variable name="statement2" value="INSERT INTO TEST_TABLE (NAME) VALUES ('citrus:concat(''Hello '', citrus:randomNumber(3))')"/>19        <sql-query url="${url}" driver="${driver}" user="${user}" password="${password}">20            <statement>${statement}</statement>21            <statement>${statement1}</statement>22            <statement>${statement2}</statement>23        <sql-query url="${url}" driver="${driver}" user="${user}" password="${password}">24            <statement>${query}</statement>

ExecuteSQLQueryAction

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.springframework.beans.factory.annotation.Autowired;6import org.testng.annotations.Test;7import javax.sql.DataSource;8public class ExecuteSQLQueryActionJavaITest {9    private DataSource dataSource;10    @CitrusParameters("param1=1")11    public void executeSQLQueryActionJavaITest(TestRunner runner) {12        runner.executeSQLQueryAction(dataSource)13                .statement("SELECT * FROM test_table WHERE id = :id")14                .validate("id", "1")15                .validate("name", "test");16    }17}18package com.consol.citrus.dsl.testng;19import com.consol.citrus.annotations.CitrusTest;20import com.consol.citrus.dsl.runner.TestRunner;21import com.consol.citrus.testng.CitrusParameters;22import org.springframework.beans.factory.annotation.Autowired;23import org.springframework.jdbc.core.JdbcTemplate;24import org.testng.annotations.Test;25public class ExecuteSQLUpdateActionJavaITest {26    private JdbcTemplate jdbcTemplate;27    @CitrusParameters("param1=1")28    public void executeSQLUpdateActionJavaITest(TestRunner runner) {29        runner.executeSQLUpdateAction(jdbcTemplate)30                .statement("INSERT INTO test_table (id, name) VALUES (:id, 'test')")31                .validate("INSERT", 1);32    }33}34package com.consol.citrus.dsl.testng;35import com.consol.citrus.annotations.CitrusTest;36import com.consol.citrus.dsl.runner.TestRunner;37import com.consol.citrus.testng.CitrusParameters;38import org.testng.annotations.Test;39public class FailActionJavaITest {40    @CitrusParameters("param1=1")41    public void failActionJavaITest(TestRunner runner) {42        runner.failAction("Fail test");43    }44}

ExecuteSQLQueryAction

Using AI Code Generation

1import com.consol.citrus.context.TestContext;2import com.consol.citrus.dsl.testng.TestNGCitrusTestDesigner;3import com.consol.citrus.exceptions.CitrusRuntimeException;4import com.consol.citrus.jdbc.actions.ExecuteSQLQueryAction;5import com.consol.citrus.jdbc.message.JdbcMessage;6import com.consol.citrus.jdbc.model.JdbcResultSet;7import com.consol.citrus.message.MessageType;8import com.consol.citrus.testng.CitrusParameters;9import org.springframework.core.io.ClassPathResource;10import org.springframework.jdbc.core.JdbcTemplate;11import org.springframework.jdbc.datasource.DriverManagerDataSource;12import org.springframework.util.CollectionUtils;13import org.testng.annotations.DataProvider;14import org.testng.annotations.Test;15import javax.sql.DataSource;16import java.util.HashMap;17import java.util.List;18import java.util.Map;19public class ExecuteSQLQueryActionTest extends TestNGCitrusTestDesigner {20    @DataProvider(name = "testData")21    public Object[][] testData() {22        return new Object[][] {23                new Object[] { "classpath:sql/executeQueryActionTest.sql", "classpath:com/consol/citrus/actions/executeQueryActionTestResult.xml" }24        };25    }26    @Test(dataProvider = "testData")27    @CitrusParameters({"sqlScript", "resultFile"})28    public void executeSQLQueryActionTest(String sqlScript, String resultFile) {29        DriverManagerDataSource dataSource = new DriverManagerDataSource();30        dataSource.setDriverClassName("org.hsqldb.jdbcDriver");31        dataSource.setUrl("jdbc:hsqldb:mem:sqltest");32        dataSource.setUsername("sa");33        dataSource.setPassword("");34        JdbcTemplate jdbcTemplate = new JdbcTemplate();35        jdbcTemplate.setDataSource(dataSource);36        String sql = context.replaceDynamicContentInString(new ClassPathResource(sqlScript).toString());37        jdbcTemplate.execute(sql);38        String query = "select * from test_table";39        ExecuteSQLQueryAction.Builder builder = new ExecuteSQLQueryAction.Builder();40        builder.query(query);41        builder.dataSource(dataSource);42        builder.resultType(MessageType.XML.name());43        builder.result(new ClassPathResource(resultFile));44        builder.queryTimeout(5000L);45        builder.validateResultSet(true);46        builder.validateHeaders(true);47        builder.validateRowNumber(true);48        builder.validateColumnNames(true);

ExecuteSQLQueryAction

Using AI Code Generation

1package com.consol.citrus.samples;2import java.util.ArrayList;3import java.util.HashMap;4import java.util.List;5import java.util.Map;6import org.springframework.context.ApplicationContext;7import org.springframework.context.support.ClassPathXmlApplicationContext;8import com.consol.citrus.actions.ExecuteSQLQueryAction;9import com.consol.citrus.dsl.junit.JUnit4CitrusTest;10import com.consol.citrus.dsl.runner.TestRunner;11import com.consol.citrus.dsl.testng.TestNGCitrusTest;12import com.consol.citrus.report.TestActionListeners;13import com.consol.citrus.report.TestListeners;14import com.consol.citrus.report.TestReporter;15import com.consol.citrus.report.TestReporterFactory;16import com.consol.citrus.report.TestReporterFactoryBean;17import com.consol.citrus.report.TestReporterMode;18import com.consol.citrus.testng.CitrusParameters;19import cucumber.api.CucumberOptions;20import cucumber.api.testng.AbstractTestNGCucumberTests;21@CucumberOptions(features="src/test/resources/features", glue="com.consol.citrus.samples")22public class ExecuteSQLQueryActionTest extends AbstractTestNGCucumberTests  {23	private TestRunner runner;24	private ApplicationContext context;25	private ExecuteSQLQueryAction executeSQLQueryAction;26	private Map<String, Object> parameters;27	private Map<String, Object> headers;28	private List<Map<String, Object>> result;29	private Map<String, Object> resultRow;30	private Map<String, Object> row;31	private List<Map<String, Object>> rows;32	private List<String> columnNames;33	private String query;34	private String dataSourceName;35	private String variableName;36	private String columnName;37	private String value;38	private String variable;39	private String variablePrefix;40	private String variableSuffix;41	private String resultVariable;42	private String resultVariablePrefix;43	private String resultVariableSuffix;44	private String variableExpression;45	private String resultVariableExpression;46	private String resultVariableName;47	private String validationScript;48	private String validationScriptResourcePath;49	private String validationScriptEngine;50	private String validationScriptVariableName;51	private String validationScriptVariableValue;52	private String validationScriptVariableExpression;53	private String validationScriptVariablePrefix;54	private String validationScriptVariableSuffix;55	private String validationScriptVariable;56	private Map<String, Object> validationScriptVariables;57	private Map<String, Object> variableExpressions;58	private Map<String, Object> resultVariableExpressions;59	private Map<String, Object> validationScriptVariableExpressions;60	private Map<String, Object> validationScriptVariableValues;

ExecuteSQLQueryAction

Using AI Code Generation

1package com.consol.citrus;2import org.springframework.context.support.ClassPathXmlApplicationContext;3public class 4 {4    public static void main(String[] args) {5        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");6        ExecuteSQLQueryAction executeSQLQueryAction = context.getBean(ExecuteSQLQueryAction.class);7        executeSQLQueryAction.execute(context);8    }9}

ExecuteSQLQueryAction

Using AI Code Generation

1package com.consol.citrus.dsl.design;2import org.springframework.jdbc.core.JdbcTemplate;3import org.springframework.jdbc.datasource.DriverManagerDataSource;4import org.testng.annotations.Test;5import com.consol.citrus.dsl.testng.TestNGCitrusTestDesigner;6public class ExecuteSQLQueryActionTest extends TestNGCitrusTestDesigner {7    public void testExecuteSQLQueryAction() {8        DriverManagerDataSource dataSource = new DriverManagerDataSource();9        dataSource.setDriverClassName("org.hsqldb.jdbcDriver");10        dataSource.setUrl("jdbc:hsqldb:mem:db1");11        dataSource.setUsername("sa");12        dataSource.setPassword("");13        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);14        jdbcTemplate.execute("CREATE TABLE CUSTOMER (ID INT PRIMARY KEY, NAME VARCHAR(255))");15        jdbcTemplate.execute("INSERT INTO CUSTOMER VALUES (1, 'Joe')");16        jdbcTemplate.execute("INSERT INTO CUSTOMER VALUES (2, 'John')");17        variable("name", "Joe");18        variable("id", "1");19        variable("name1", "John");20        variable("id1", "2");21        variable("name2", "Mark");22        variable("id2", "3");23        executeSQLQueryAction(dataSource)24            .sqlQuery("SELECT * FROM CUSTOMER WHERE NAME = :name")25            .validate("ID", "1")26            .validate("NAME", "Joe");27        executeSQLQueryAction(dataSource)28            .sqlQuery("SELECT * FROM CUSTOMER WHERE NAME = :name1")29            .validate("ID", "2")30            .validate("NAME", "John");31        executeSQLQueryAction(dataSource)32            .sqlQuery("SELECT * FROM CUSTOMER WHERE NAME = :name2")33            .validate("ID", "3")34            .validate("NAME", "Mark");35    }36}37package com.consol.citrus.dsl.design;38import org.springframework.jdbc.core.JdbcTemplate;39import org.springframework.jdbc.datasource.DriverManagerDataSource;40import org.testng.annotations.Test;41import com.consol.citrus.dsl.testng.TestNGCitrusTestDesigner;42public class ExecuteSQLUpdateActionTest extends TestNGCitrusTestDesigner {43    public void testExecuteSQLUpdateAction() {44        DriverManagerDataSource dataSource = new DriverManagerDataSource();45        dataSource.setDriverClassName("org.hsqldb.jdbcDriver");

ExecuteSQLQueryAction

Using AI Code Generation

1package com.consol.citrus.actions;2import java.util.HashMap;3import java.util.Map;4import javax.sql.DataSource;5import org.springframework.beans.factory.annotation.Autowired;6import org.springframework.jdbc.core.JdbcTemplate;7import org.springframework.jdbc.datasource.DriverManagerDataSource;8import org.testng.annotations.Test;9import com.consol.citrus.annotations.CitrusTest;10import com.consol.citrus.testng.CitrusParameters;11import groovy.lang.Binding;12public class ExecuteSQLQueryActionTest {13    private JdbcTemplate jdbcTemplate;14    @CitrusParameters("sqlQuery")15    public void executeSQLQueryAction() {16        DriverManagerDataSource dataSource = new DriverManagerDataSource();17        dataSource.setDriverClassName("org.h2.Driver");18        dataSource.setUrl("jdbc:h2:mem:test");19        dataSource.setUsername("sa");20        dataSource.setPassword("sa");21        jdbcTemplate = new JdbcTemplate(dataSource);22        jdbcTemplate.execute("create table employee (id int, name varchar(255))");23        jdbcTemplate.execute("insert into employee values (1, 'John')");24        jdbcTemplate.execute("insert into employee values (2, 'Jane')");25        jdbcTemplate.execute("insert into employee values (3, 'Bob')");26        jdbcTemplate.execute("insert into employee values (4, 'Mary')");27        ExecuteSQLQueryAction.Builder builder = new ExecuteSQLQueryAction.Builder();28        builder.jdbcTemplate(jdbcTemplate);29        builder.query("select * from employee where id = 1");30        builder.validate("name", "John");31        builder.validate("id", "1");32        builder.build().execute();33    }34}

ExecuteSQLQueryAction

Using AI Code Generation

1package com.consol.citrus;2import org.springframework.context.support.ClassPathXmlApplicationContext;3public class 4 {4	public static void main(String[] args) {5		ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");6		ExecuteSQLQueryAction executeSQLQueryAction = context.getBean("executeSQLQueryAction", ExecuteSQLQueryAction.class);7		executeSQLQueryAction.execute(context);8	}9}

ExecuteSQLQueryAction

Using AI Code Generation

1package com.citrus;2import com.consol.citrus.dsl.runner.TestRunner;3import com.consol.citrus.dsl.testng.TestNGCitrusTest;4import org.testng.annotations.Test;5public class ExecuteSQLQueryActionTest extends TestNGCitrusTest {6public void testExecuteSQLQueryAction() {7TestRunner runner = citrus.createTestRunner();8runner.executeSQLQueryAction()9.withDataSource("jdbc/myDataSource")10.withQuery("SELECT * FROM MY_TABLE WHERE MY_COLUMN = 'MY_VALUE'")11.validate("ID", "1")12.validate("NAME", "Citrus");13}14}15Build SUCCESSFUL (total time: 1 second)

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.