Examples with SqlQueryDefinition org.jbpm.kie.services.impl.query.SqlQueryDefinition used on opensource projects

Example 31 with SqlQueryDefinition

use of org.jbpm.kie.services.impl.query.SqlQueryDefinition in project jbpm by kiegroup.

the class QueryServiceImplTest method testGetProcessInstancesGroupWithoutInterval.

@Test
public void testGetProcessInstancesGroupWithoutInterval() {
    query = new SqlQueryDefinition("getAllProcessInstances", dataSourceJNDIname);
    query.setExpression("select * from processinstancelog");
    queryService.registerQuery(query);
    List<QueryDefinition> queries = queryService.getQueries(new QueryContext());
    assertNotNull(queries);
    assertEquals(1, queries.size());
    QueryParam[] parameters = QueryParam.getBuilder().append(QueryParam.groupBy(COLUMN_START)).get();
    Collection<List<Object>> instances = queryService.query(query.getName(), RawListQueryMapper.get(), new QueryContext(), parameters);
    assertNotNull(instances);
    assertEquals(0, instances.size());
    processInstanceId = processService.startProcess(deploymentUnit.getIdentifier(), "org.jbpm.writedocument");
    assertNotNull(processInstanceId);
    long processInstanceId2 = processService.startProcess(deploymentUnit.getIdentifier(), "org.jboss.qa.bpms.HumanTask");
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
    // expected date is day when processes started
    String expectedDate = sdf.format(new Date());
    instances = queryService.query(query.getName(), RawListQueryMapper.get(), new QueryContext(), parameters);
    assertNotNull(instances);
    assertEquals(1, instances.size());
    // write document process
    List<Object> result = instances.iterator().next();
    assertNotNull(result);
    assertEquals(1, result.size());
    assertEquals(expectedDate, result.get(0));
    processService.abortProcessInstance(processInstanceId2);
    processService.abortProcessInstance(processInstanceId);
    processInstanceId = null;
}

Example 32 with SqlQueryDefinition

use of org.jbpm.kie.services.impl.query.SqlQueryDefinition in project jbpm by kiegroup.

the class QueryServiceImplTest method testGetProcessInstancesGroupWithInterval.

@Test
public void testGetProcessInstancesGroupWithInterval() {
    query = new SqlQueryDefinition("getAllProcessInstances", dataSourceJNDIname);
    query.setExpression("select * from processinstancelog");
    queryService.registerQuery(query);
    List<QueryDefinition> queries = queryService.getQueries(new QueryContext());
    assertNotNull(queries);
    assertEquals(1, queries.size());
    QueryParam[] parameters = QueryParam.getBuilder().append(QueryParam.groupBy(COLUMN_START, QueryParam.DAY, 30)).get();
    Collection<List<Object>> instances = queryService.query(query.getName(), RawListQueryMapper.get(), new QueryContext(), parameters);
    assertNotNull(instances);
    assertEquals(0, instances.size());
    processInstanceId = processService.startProcess(deploymentUnit.getIdentifier(), "org.jbpm.writedocument");
    assertNotNull(processInstanceId);
    long processInstanceId2 = processService.startProcess(deploymentUnit.getIdentifier(), "org.jboss.qa.bpms.HumanTask");
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    // expected date is day when processes started
    String expectedDate = sdf.format(new Date());
    instances = queryService.query(query.getName(), RawListQueryMapper.get(), new QueryContext(), parameters);
    assertNotNull(instances);
    assertEquals(1, instances.size());
    // write document process
    List<Object> result = instances.iterator().next();
    assertNotNull(result);
    assertEquals(1, result.size());
    assertEquals(expectedDate, result.get(0));
    processService.abortProcessInstance(processInstanceId2);
    processService.abortProcessInstance(processInstanceId);
    processInstanceId = null;
}

Example 33 with SqlQueryDefinition

use of org.jbpm.kie.services.impl.query.SqlQueryDefinition in project jbpm by kiegroup.

the class TaskVariablesQueryServiceTest method testTaskVariableQueryOnBigTaskSetAsJPA.

@Test
public void testTaskVariableQueryOnBigTaskSetAsJPA() throws Exception {
    Map<String, String> variableMap = new HashMap<String, String>();
    variableMap.put("COUNTRY", "string");
    variableMap.put("PRODUCTCODE", "string");
    variableMap.put("QUANTITY", "integer");
    variableMap.put("PRICE", "double");
    variableMap.put("SALEDATE", "date");
    SqlQueryDefinition query = new SqlQueryDefinition("getAllTaskInstancesWithCustomVariables", "jdbc/testDS1");
    query.setExpression("select ti.*,  c.country, c.productCode, c.quantity, c.price, c.saleDate " + "from AuditTaskImpl ti " + "    inner join (select mv.map_var_id, mv.taskid from MappedVariable mv) mv " + "      on (mv.taskid = ti.taskId) " + "    inner join ProductSale c " + "      on (c.id = mv.map_var_id)");
    queryService.registerQuery(query);
    SqlQueryDefinition queryTPO = new SqlQueryDefinition("getMyTaskInstancesWithCustomVariables", "jdbc/testDS1", Target.PO_TASK);
    queryTPO.setExpression("select ti.*,  c.country, c.productCode, c.quantity, c.price, c.saleDate, oe.id oeid " + "from AuditTaskImpl ti " + "    inner join (select mv.map_var_id, mv.taskid from MappedVariable mv) mv " + "      on (mv.taskid = ti.taskId) " + "    inner join ProductSale c " + "      on (c.id = mv.map_var_id), " + "  PeopleAssignments_PotOwners po, OrganizationalEntity oe " + "    where ti.taskId = po.task_id and po.entity_id = oe.id");
    queryService.registerQuery(queryTPO);
    queryService.query(query.getName(), UserTaskInstanceWithCustomVarsQueryMapper.get(variableMap), new QueryContext(), QueryParam.equalsTo("productCode", "EAP"));
    long currentTime = System.currentTimeMillis();
    Calendar cal = Calendar.getInstance();
    RuntimeManager manager = deploymentService.getRuntimeManager(deploymentUnitSalesId);
    assertNotNull(manager);
    Class<?> clazz = Class.forName("org.jbpm.test.ProductSale", true, ((InternalRuntimeManager) manager).getEnvironment().getClassLoader());
    Random random = new Random();
    int i = 0;
    for (i = 0; i < 10000; i++) {
        cal.setTimeInMillis(currentTime);
        // add random number of days
        cal.add(Calendar.DAY_OF_YEAR, random.nextInt(60));
        Object product = clazz.newInstance();
        // set fields
        setFieldValue(product, "country", countries.get(random.nextInt(9)));
        setFieldValue(product, "productCode", productCodes.get(random.nextInt(9)));
        setFieldValue(product, "quantity", random.nextInt(50));
        setFieldValue(product, "price", (random.nextDouble() * 1000));
        setFieldValue(product, "saleDate", cal.getTime());
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("product", product);
        params.put("sales", "john,actor" + i);
        params.put("salesGroup", "Crusaders");
        logger.debug("Params : " + params);
        processService.startProcess(deploymentUnitSalesId, "product-sale.sale-product", params);
    }
    logger.info("Generated {} process instances... doing searches now", i);
    logger.info("let's find tasks for product EAP only");
    long timestamp = System.currentTimeMillis();
    List<UserTaskInstanceWithVarsDesc> taskInstanceLogs = queryService.query(query.getName(), UserTaskInstanceWithCustomVarsQueryMapper.get(variableMap), new QueryContext(), QueryParam.equalsTo("productCode", "EAP"));
    logger.info("Task query by variable took {} ms with result size {}", (System.currentTimeMillis() - timestamp), taskInstanceLogs.size());
    logger.info("let's find tasks for product EAP or Wildfly");
    timestamp = System.currentTimeMillis();
    taskInstanceLogs = queryService.query(query.getName(), UserTaskInstanceWithCustomVarsQueryMapper.get(variableMap), new QueryContext(), QueryParam.in("productCode", Arrays.asList("EAP", "WILDFLY")));
    logger.info("Task query by variable took {} ms with result size {}", (System.currentTimeMillis() - timestamp), taskInstanceLogs.size());
    logger.info("let's find tasks for product EAP and country Brazil");
    timestamp = System.currentTimeMillis();
    taskInstanceLogs = queryService.query(query.getName(), UserTaskInstanceWithCustomVarsQueryMapper.get(variableMap), new QueryContext(), QueryParam.equalsTo("productCode", "EAP"), QueryParam.equalsTo("country", "Brazil"));
    logger.info("Task query by variable took {} ms with result size {}", (System.currentTimeMillis() - timestamp), taskInstanceLogs.size());
    logger.info("let's find tasks for product BPMS and BRMS by using wildcard search");
    timestamp = System.currentTimeMillis();
    taskInstanceLogs = queryService.query(query.getName(), UserTaskInstanceWithCustomVarsQueryMapper.get(variableMap), new QueryContext(), QueryParam.likeTo("productCode", false, "B%"));
    logger.info("Task query by variable took {} ms with result size {}", (System.currentTimeMillis() - timestamp), taskInstanceLogs.size());
    logger.info("let's find tasks for product Weblogic or WebSphere by wildcard and country Canada");
    timestamp = System.currentTimeMillis();
    taskInstanceLogs = queryService.query(query.getName(), UserTaskInstanceWithCustomVarsQueryMapper.get(variableMap), new QueryContext(), QueryParam.likeTo("productCode", false, "WEB%"), QueryParam.equalsTo("country", "Canada"));
    logger.info("Task query by variable took {} ms with result size {}", (System.currentTimeMillis() - timestamp), taskInstanceLogs.size());
    logger.info("let's find tasks for product EAP and country Brazil and tasks with status Ready and Reserved");
    timestamp = System.currentTimeMillis();
    taskInstanceLogs = queryService.query(query.getName(), UserTaskInstanceWithCustomVarsQueryMapper.get(variableMap), new QueryContext(), QueryParam.equalsTo("productCode", "EAP"), QueryParam.equalsTo("country", "Brazil"), QueryParam.in("status", Arrays.asList(Status.Ready.toString(), Status.Reserved.toString())));
    logger.info("Task query by variable took {} ms with result size {}", (System.currentTimeMillis() - timestamp), taskInstanceLogs.size());
    logger.info("let's find tasks for product Weblogic or WebSphere by wildcard where quantity is bigger than 20");
    timestamp = System.currentTimeMillis();
    taskInstanceLogs = queryService.query(query.getName(), UserTaskInstanceWithCustomVarsQueryMapper.get(variableMap), new QueryContext(), QueryParam.likeTo("productCode", false, "WEB%"), QueryParam.greaterOrEqualTo("quantity", 20));
    logger.info("Task query by variable took {} ms with result size {}", (System.currentTimeMillis() - timestamp), taskInstanceLogs.size());
    logger.info("let's find tasks for product EAP where sale was put in one month from now");
    cal.setTimeInMillis(currentTime);
    Date from = cal.getTime();
    cal.add(Calendar.MONTH, 1);
    Date to = cal.getTime();
    timestamp = System.currentTimeMillis();
    taskInstanceLogs = queryService.query(query.getName(), UserTaskInstanceWithCustomVarsQueryMapper.get(variableMap), new QueryContext(), QueryParam.equalsTo("productCode", "EAP"), QueryParam.between("saleDate", from, to));
    logger.info("Task query by variable took {} ms with result size {}", (System.currentTimeMillis() - timestamp), taskInstanceLogs.size());
    // now same queries but with user/group filtering
    logger.info("################################################");
    logger.info("Task with user/group filtering");
    logger.info("################################################");
    identityProvider.setName("john");
    timestamp = System.currentTimeMillis();
    taskInstanceLogs = queryService.query(queryTPO.getName(), UserTaskInstanceWithCustomVarsQueryMapper.get(variableMap), new QueryContext(), QueryParam.equalsTo("productCode", "EAP"));
    logger.info("Task query by variable took {} ms with result size {}", (System.currentTimeMillis() - timestamp), taskInstanceLogs.size());
    logger.info("let's find tasks for product EAP or Wildfly");
    timestamp = System.currentTimeMillis();
    taskInstanceLogs = queryService.query(queryTPO.getName(), UserTaskInstanceWithCustomVarsQueryMapper.get(variableMap), new QueryContext(), QueryParam.in("productCode", Arrays.asList("EAP", "WILDFLY")));
    logger.info("Task query by variable took {} ms with result size {}", (System.currentTimeMillis() - timestamp), taskInstanceLogs.size());
    logger.info("let's find tasks for product EAP and country Brazil");
    timestamp = System.currentTimeMillis();
    taskInstanceLogs = queryService.query(queryTPO.getName(), UserTaskInstanceWithCustomVarsQueryMapper.get(variableMap), new QueryContext(), QueryParam.equalsTo("productCode", "EAP"), QueryParam.equalsTo("country", "Brazil"));
    logger.info("Task query by variable took {} ms with result size {}", (System.currentTimeMillis() - timestamp), taskInstanceLogs.size());
    logger.info("let's find tasks for product BPMS and BRMS by using wildcard search");
    timestamp = System.currentTimeMillis();
    taskInstanceLogs = queryService.query(queryTPO.getName(), UserTaskInstanceWithCustomVarsQueryMapper.get(variableMap), new QueryContext(), QueryParam.likeTo("productCode", false, "B%"));
    logger.info("Task query by variable took {} ms with result size {}", (System.currentTimeMillis() - timestamp), taskInstanceLogs.size());
    logger.info("let's find tasks for product Weblogic or WebSphere by wildcard and country Canada");
    timestamp = System.currentTimeMillis();
    taskInstanceLogs = queryService.query(queryTPO.getName(), UserTaskInstanceWithCustomVarsQueryMapper.get(variableMap), new QueryContext(), QueryParam.likeTo("productCode", false, "WEB%"), QueryParam.equalsTo("country", "Canada"));
    logger.info("Task query by variable took {} ms with result size {}", (System.currentTimeMillis() - timestamp), taskInstanceLogs.size());
    logger.info("let's find tasks for product EAP and country Brazil and tasks with status Ready and Reserved");
    timestamp = System.currentTimeMillis();
    taskInstanceLogs = queryService.query(queryTPO.getName(), UserTaskInstanceWithCustomVarsQueryMapper.get(variableMap), new QueryContext(), QueryParam.equalsTo("productCode", "EAP"), QueryParam.equalsTo("country", "Brazil"), QueryParam.in("status", Arrays.asList(Status.Ready.toString(), Status.Reserved.toString())));
    logger.info("Task query by variable took {} ms with result size {}", (System.currentTimeMillis() - timestamp), taskInstanceLogs.size());
    logger.info("let's find tasks for product Weblogic or WebSphere by wildcard where quantity is bigger than 20");
    timestamp = System.currentTimeMillis();
    taskInstanceLogs = queryService.query(queryTPO.getName(), UserTaskInstanceWithCustomVarsQueryMapper.get(variableMap), new QueryContext(), QueryParam.likeTo("productCode", false, "WEB%"), QueryParam.greaterOrEqualTo("quantity", 20));
    logger.info("Task query by variable took {} ms with result size {}", (System.currentTimeMillis() - timestamp), taskInstanceLogs.size());
    logger.info("let's find tasks for product EAP where sale was put in one month from now");
    timestamp = System.currentTimeMillis();
    taskInstanceLogs = queryService.query(queryTPO.getName(), UserTaskInstanceWithCustomVarsQueryMapper.get(variableMap), new QueryContext(), QueryParam.equalsTo("productCode", "EAP"), QueryParam.between("saleDate", from, to));
    logger.info("Task query by variable took {} ms with result size {}", (System.currentTimeMillis() - timestamp), taskInstanceLogs.size());
}

Example 34 with SqlQueryDefinition

use of org.jbpm.kie.services.impl.query.SqlQueryDefinition in project jbpm by kiegroup.

the class UserTaskAdminServiceImplTest method testGetTaskInstancesAsPotOwners.

@Test
public void testGetTaskInstancesAsPotOwners() {
    String PO_TASK_QUERY = "select ti.taskId, ti.activationTime, ti.actualOwner, ti.createdBy, ti.createdOn, ti.deploymentId, " + "ti.description, ti.dueDate, ti.name, ti.parentId, ti.priority, ti.processId, ti.processInstanceId, " + "ti.processSessionId, ti.status, ti.workItemId, oe.id, eo.entity_id " + "from AuditTaskImpl ti " + "left join PeopleAssignments_PotOwners po on ti.taskId = po.task_id " + "left join OrganizationalEntity oe on po.entity_id = oe.id " + " left join PeopleAssignments_ExclOwners eo on ti.taskId = eo.task_id ";
    SqlQueryDefinition query = new SqlQueryDefinition("getMyTaskInstances", "jdbc/testDS1", Target.PO_TASK);
    query.setExpression(PO_TASK_QUERY);
    queryService.registerQuery(query);
    List<QueryDefinition> queries = queryService.getQueries(new QueryContext());
    assertNotNull(queries);
    assertEquals(1, queries.size());
    QueryDefinition registeredQuery = queries.get(0);
    assertNotNull(registeredQuery);
    assertEquals(query.getName(), registeredQuery.getName());
    assertEquals(query.getSource(), registeredQuery.getSource());
    assertEquals(query.getExpression(), registeredQuery.getExpression());
    assertEquals(query.getTarget(), registeredQuery.getTarget());
    registeredQuery = queryService.getQuery(query.getName());
    assertNotNull(registeredQuery);
    assertEquals(query.getName(), registeredQuery.getName());
    assertEquals(query.getSource(), registeredQuery.getSource());
    assertEquals(query.getExpression(), registeredQuery.getExpression());
    assertEquals(query.getTarget(), registeredQuery.getTarget());
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("approval_document", "initial content");
    processInstanceId = processService.startProcess(deploymentUnit.getIdentifier(), "org.jbpm.writedocument", params);
    assertNotNull(processInstanceId);
    identityProvider.setName("notvalid");
    List<UserTaskInstanceDesc> taskInstanceLogs = queryService.query(query.getName(), UserTaskInstanceQueryMapper.get(), new QueryContext());
    assertNotNull(taskInstanceLogs);
    assertEquals(0, taskInstanceLogs.size());
    identityProvider.setName("salaboy");
    taskInstanceLogs = queryService.query(query.getName(), UserTaskInstanceQueryMapper.get(), new QueryContext());
    assertNotNull(taskInstanceLogs);
    assertEquals(1, taskInstanceLogs.size());
    List<TaskSummary> taskSummaries = queryService.query(query.getName(), TaskSummaryQueryMapper.get(), new QueryContext());
    assertNotNull(taskSummaries);
    assertEquals(1, taskSummaries.size());
    identityProvider.setName("Administrator");
    userTaskAdminService.addPotentialOwners(taskSummaries.get(0).getId(), false, factory.newUser("john"));
    identityProvider.setName("salaboy");
    taskInstanceLogs = queryService.query(query.getName(), UserTaskInstanceQueryMapper.get(), new QueryContext());
    assertNotNull(taskInstanceLogs);
    assertEquals(1, taskInstanceLogs.size());
    taskSummaries = queryService.query(query.getName(), TaskSummaryQueryMapper.get(), new QueryContext());
    assertNotNull(taskSummaries);
    assertEquals(1, taskSummaries.size());
    QueryParam[] parameters = QueryParam.getBuilder().append(QueryParam.groupBy(COLUMN_NAME)).append(QueryParam.count(COLUMN_TASKID)).get();
    Collection<List<Object>> instances = queryService.query(query.getName(), RawListQueryMapper.get(), new QueryContext(), parameters);
    assertNotNull(instances);
    assertEquals(1, instances.size());
    List<Object> result = instances.iterator().next();
    assertNotNull(result);
    assertEquals(2, result.size());
    // here we have count set to 2 because group by is on name and thus it returns duplicates
    assertTrue(result.get(1) instanceof Number);
    assertEquals(2, ((Number) result.get(1)).intValue());
    processService.abortProcessInstance(processInstanceId);
    processInstanceId = null;
}

Example 35 with SqlQueryDefinition

use of org.jbpm.kie.services.impl.query.SqlQueryDefinition in project jbpm by kiegroup.

the class UserTaskAdminServiceImplTest method testGetTaskInstancesAsPotOwnersMultipleInstances.

@Test
public void testGetTaskInstancesAsPotOwnersMultipleInstances() {
    String PO_TASK_QUERY = "select ti.taskId, ti.activationTime, ti.actualOwner, ti.createdBy, ti.createdOn, ti.deploymentId, " + "ti.description, ti.dueDate, ti.name, ti.parentId, ti.priority, ti.processId, ti.processInstanceId, " + "ti.processSessionId, ti.status, ti.workItemId, oe.id, eo.entity_id " + "from AuditTaskImpl ti " + "left join PeopleAssignments_PotOwners po on ti.taskId = po.task_id " + "left join OrganizationalEntity oe on po.entity_id = oe.id " + " left join PeopleAssignments_ExclOwners eo on ti.taskId = eo.task_id ";
    SqlQueryDefinition query = new SqlQueryDefinition("getMyTaskInstances", "jdbc/testDS1", Target.PO_TASK);
    query.setExpression(PO_TASK_QUERY);
    queryService.registerQuery(query);
    List<QueryDefinition> queries = queryService.getQueries(new QueryContext());
    assertNotNull(queries);
    assertEquals(1, queries.size());
    QueryDefinition registeredQuery = queries.get(0);
    assertNotNull(registeredQuery);
    assertEquals(query.getName(), registeredQuery.getName());
    assertEquals(query.getSource(), registeredQuery.getSource());
    assertEquals(query.getExpression(), registeredQuery.getExpression());
    assertEquals(query.getTarget(), registeredQuery.getTarget());
    registeredQuery = queryService.getQuery(query.getName());
    assertNotNull(registeredQuery);
    assertEquals(query.getName(), registeredQuery.getName());
    assertEquals(query.getSource(), registeredQuery.getSource());
    assertEquals(query.getExpression(), registeredQuery.getExpression());
    assertEquals(query.getTarget(), registeredQuery.getTarget());
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("approval_document", "initial content");
    processInstanceId = processService.startProcess(deploymentUnit.getIdentifier(), "org.jbpm.writedocument", params);
    assertNotNull(processInstanceId);
    Long processInstanceId2 = processService.startProcess(deploymentUnit.getIdentifier(), "org.jbpm.writedocument", params);
    assertNotNull(processInstanceId);
    assertNotNull(processInstanceId2);
    identityProvider.setName("notvalid");
    List<UserTaskInstanceDesc> taskInstanceLogs = queryService.query(query.getName(), UserTaskInstanceQueryMapper.get(), new QueryContext());
    assertNotNull(taskInstanceLogs);
    assertEquals(0, taskInstanceLogs.size());
    identityProvider.setName("salaboy");
    taskInstanceLogs = queryService.query(query.getName(), UserTaskInstanceQueryMapper.get(), new QueryContext());
    assertNotNull(taskInstanceLogs);
    assertEquals(2, taskInstanceLogs.size());
    identityProvider.setName("Administrator");
    userTaskAdminService.addPotentialOwners(taskInstanceLogs.get(0).getTaskId(), false, factory.newUser("john"));
    identityProvider.setName("salaboy");
    taskInstanceLogs = queryService.query(query.getName(), UserTaskInstanceQueryMapper.get(), new QueryContext());
    assertNotNull(taskInstanceLogs);
    assertEquals(2, taskInstanceLogs.size());
    processService.abortProcessInstance(processInstanceId);
    processInstanceId = null;
    processService.abortProcessInstance(processInstanceId2);
    processInstanceId2 = null;
}