Examples with SqlStatement org.jumpmind.pos.persist.SqlStatement used on opensource projects

Example 1 with SqlStatement

use of org.jumpmind.pos.persist.SqlStatement in project openpos-framework by JumpMind.

the class DmlTemplate method generateSQL.

public SqlStatement generateSQL(String sql, Map<String, Object> params) {
    List<String> keys = new ArrayList<>();
    StringSubstitutor literalSubstitution = new StringSubstitutor(new StringLookup() {

        @Override
        public String lookup(String key) {
            Object paramValue = params.get(key);
            return paramValue != null ? paramValue.toString() : "null";
        }
    }, "$${", "}", '\\');
    StringSubstitutor sub = new StringSubstitutor(new StringLookup() {

        @Override
        public String lookup(String key) {
            keys.add(key);
            return ":" + key;
        }
    });
    String preparedSql = literalSubstitution.replace(sql);
    preparedSql = sub.replace(preparedSql);
    String preppedWhereClause = literalSubstitution.replace(this.getWhere());
    preppedWhereClause = sub.replace(preppedWhereClause);
    StringBuilder buff = new StringBuilder();
    preparedSql = stripWhere(preparedSql);
    boolean hasWhereKeyword = false;
    buff.append(preparedSql);
    if (!StringUtils.isEmpty(preppedWhereClause)) {
        hasWhereKeyword = true;
        buff.append(" WHERE ");
        buff.append(preppedWhereClause);
    }
    for (String optionalWhereClause : this.getOptionalWhereClauses()) {
        Set<String> optionalWhereClauseKeys = new LinkedHashSet<>();
        String preppedOptionalWhereClause = literalSubstitution.replace(optionalWhereClause);
        StringSubstitutor optionalSubstitution = new StringSubstitutor(new StringLookup() {

            @Override
            public String lookup(String key) {
                optionalWhereClauseKeys.add(key);
                return ":" + key;
            }
        });
        preppedOptionalWhereClause = optionalSubstitution.replace(preppedOptionalWhereClause);
        boolean shouldInclude = true;
        for (String key : optionalWhereClauseKeys) {
            if (!params.containsKey(key)) {
                shouldInclude = false;
                break;
            }
        }
        if (shouldInclude) {
            if (!hasWhereKeyword) {
                buff.append(" WHERE 1=1 ");
                hasWhereKeyword = true;
            }
            buff.append(" AND (");
            buff.append(preppedOptionalWhereClause);
            buff.append(")");
            keys.addAll(optionalWhereClauseKeys);
        }
    }
    SqlStatement sqlStatement = new SqlStatement();
    sqlStatement.setSql(buff.toString());
    for (String key : keys) {
        Object value = params.get(key);
        if (value == null) {
            value = params.get("*");
            params.put(key, value);
        }
        if (value == null) {
            if (params.containsKey(key)) {
                throw new PersistException(String.format("Required query parameter '%s' was present but the value is null. A value must be provided. Cannot build query: %s", key, sqlStatement.getSql()));
            } else {
                throw new PersistException(String.format("Missing required query parameter '%s'. Cannot build query: %s", key, sqlStatement.getSql()));
            }
        } else if (value instanceof Boolean) {
            boolean bool = (Boolean) value;
            value = bool ? 1 : 0;
            params.put(key, value);
        } else if (value instanceof AbstractTypeCode) {
            value = ((AbstractTypeCode) value).value();
            params.put(key, value);
        }
    }
    if (params != null) {
        params.remove("*");
    }
    sqlStatement.setParameters(params);
    return sqlStatement;
}

Example 2 with SqlStatement

use of org.jumpmind.pos.persist.SqlStatement in project openpos-framework by JumpMind.

the class QueryTemplateTest method generateSql.

@Test
public void generateSql() {
    SqlStatement sqlStatement = queryTemplate.generateSQL(query, params);
    assertEquals(DEFAULT_SELECT, sqlStatement.getSql());
    assertEquals(0, sqlStatement.getParameters().getValues().size());
}

Example 3 with SqlStatement

use of org.jumpmind.pos.persist.SqlStatement in project openpos-framework by JumpMind.

the class QueryTemplateTest method testTranslateTrueToOne.

@Test
public void testTranslateTrueToOne() throws Exception {
    QueryTemplate template = new QueryTemplate();
    template.setSelect("select antique from car");
    template.setWhere("antique=${antique}");
    Map<String, Object> params = new HashMap<>();
    params.put("antique", true);
    SqlStatement sql = template.generateSQL(new Query<CarModel>().result(CarModel.class), params);
    assertNotNull(sql.getParameters());
    assertEquals(1, sql.getParameters().getValues().size());
    assertEquals(1, sql.getParameters().getValue("antique"));
}

Example 4 with SqlStatement

use of org.jumpmind.pos.persist.SqlStatement in project openpos-framework by JumpMind.

the class QueryTemplateTest method generateSQLWithInClauseAndParametersAsList.

@Test
public void generateSQLWithInClauseAndParametersAsList() {
    queryTemplate.setWhere("baz in ( ${para} )");
    params.put("para", Arrays.asList("a", "b"));
    SqlStatement sqlStatement = queryTemplate.generateSQL(query, params);
    assertEquals("select foo from bar WHERE baz in ( :para )", sqlStatement.getSql());
    assertEquals(1, sqlStatement.getParameters().getValues().size());
    assertEquals(Arrays.asList("a", "b"), sqlStatement.getParameters().getValues().get("para"));
}

Example 5 with SqlStatement

use of org.jumpmind.pos.persist.SqlStatement in project openpos-framework by JumpMind.

the class QueryTemplateTest method generateSQLWithInClauseAndParametersAsListAndNeedToWithMultiple.

@Test
public void generateSQLWithInClauseAndParametersAsListAndNeedToWithMultiple() {
    queryTemplate.setWhere("baz in ( ${para} ) and waz not in ( ${arap} )");
    params.put("para", Arrays.asList("a", "b", "c", "d"));
    params.put("arap", Arrays.asList("z", "y", "x", "w", "v"));
    query.setMaxInParameters(2);
    SqlStatement sqlStatement = queryTemplate.generateSQL(query, params);
    assertEquals("select foo from bar WHERE (baz in ( :para$0,:para$1 ) OR baz in ( :para$2,:para$3 )) and (waz not in ( :arap$0,:arap$1 ) AND waz not in ( :arap$2,:arap$3 ) AND waz not in ( :arap$4 ))", sqlStatement.getSql());
    assertEquals(11, sqlStatement.getParameters().getValues().size());
    assertEquals(Arrays.asList("a", "b", "c", "d"), sqlStatement.getParameters().getValues().get("para"));
    assertEquals("a", sqlStatement.getParameters().getValues().get("para$0"));
    assertEquals("b", sqlStatement.getParameters().getValues().get("para$1"));
    assertEquals("c", sqlStatement.getParameters().getValues().get("para$2"));
    assertEquals("d", sqlStatement.getParameters().getValues().get("para$3"));
    assertEquals(Arrays.asList("z", "y", "x", "w", "v"), sqlStatement.getParameters().getValues().get("arap"));
    assertEquals("z", sqlStatement.getParameters().getValues().get("arap$0"));
    assertEquals("y", sqlStatement.getParameters().getValues().get("arap$1"));
    assertEquals("x", sqlStatement.getParameters().getValues().get("arap$2"));
    assertEquals("w", sqlStatement.getParameters().getValues().get("arap$3"));
    assertEquals("v", sqlStatement.getParameters().getValues().get("arap$4"));
}