Java Code Examples for org.apache.ignite.cache.query.SqlFieldsQuery#setArgs()

/**
 * @param sql SQL.
 * @param cache Cache.
 * @param enforceJoinOrder Enforce join order flag.
 * @param expSize Expected results size.
 * @param args Arguments.
 */
private void checkQuery(String sql,
    IgniteCache<Object, Object> cache,
    boolean enforceJoinOrder,
    int expSize,
    Object... args) {
    SqlFieldsQuery qry = new SqlFieldsQuery(sql);

    qry.setDistributedJoins(true);
    qry.setEnforceJoinOrder(enforceJoinOrder);
    qry.setArgs(args);

    log.info("Plan: " + queryPlan(cache, qry));

    QueryCursor<List<?>> cur = cache.query(qry);

    List<List<?>> res = cur.getAll();

    if (expSize != res.size())
        log.info("Results: " + res);

    assertEquals(expSize, res.size());
}
 

/**
 * @param sql SQL.
 * @param cache Cache.
 * @param expSize Expected results size.
 * @param args Arguments.
 */
private void checkQuery(String sql,
    IgniteCache<Object, Object> cache,
    int expSize,
    Object... args) {
    String plan = (String)cache.query(new SqlFieldsQuery("explain " + sql))
        .getAll().get(0).get(0);

    log.info("Plan: " + plan);

    SqlFieldsQuery qry = new SqlFieldsQuery(sql);

    qry.setArgs(args);

    QueryCursor<List<?>> cur = cache.query(qry);

    List<List<?>> res = cur.getAll();

    if (expSize != res.size())
        log.info("Results: " + res);

    assertEquals(expSize, res.size());
}
 

/**
 * @param sql SQL.
 * @param cache Cache.
 * @param enforceJoinOrder Enforce join order flag.
 * @param expSize Expected results size.
 * @param args Arguments.
 * @return Results.
 */
private List<List<?>> checkQuery(String sql,
    IgniteCache<Object, Object> cache,
    boolean enforceJoinOrder,
    int expSize,
    Object... args) {
    SqlFieldsQuery qry = new SqlFieldsQuery(sql);

    qry.setDistributedJoins(true);
    qry.setEnforceJoinOrder(enforceJoinOrder);
    qry.setArgs(args);

    log.info("Plan: " + queryPlan(cache, qry));

    QueryCursor<List<?>> cur = cache.query(qry);

    List<List<?>> res = cur.getAll();

    if (expSize != res.size())
        log.info("Results: " + res);

    assertEquals(expSize, res.size());

    return res;
}
 

/**
 * @throws Exception If failed.
 */
@Test
public void testSqlQuery() throws Exception {
    Ignite node = ignite(0);

    IgniteCache c1 = node.createCache(personCacheConfiguration(GROUP1, "c1"));
    IgniteCache c2 = node.createCache(personCacheConfiguration(GROUP1, "c2"));

    SqlFieldsQuery qry = new SqlFieldsQuery("select name from Person where name=?");
    qry.setArgs("p1");

    assertEquals(0, c1.query(qry).getAll().size());
    assertEquals(0, c2.query(qry).getAll().size());

    c1.put(1, new Person("p1"));

    assertEquals(1, c1.query(qry).getAll().size());
    assertEquals(0, c2.query(qry).getAll().size());

    c2.put(2, new Person("p1"));

    assertEquals(1, c1.query(qry).getAll().size());
    assertEquals(1, c2.query(qry).getAll().size());
}
 

/**
 * Benchmarked action that inserts and immediately deletes row.
 *
 * {@inheritDoc}
 */
@Override public boolean test(Map<Object, Object> ctx) throws Exception {
    long insertKey = ThreadLocalRandom.current().nextLong(args.range()) + 1 + args.range();
    long insertVal = insertKey + 1;

    SqlFieldsQuery insert = new SqlFieldsQuery("INSERT INTO test_long (id, val) VALUES (?, ?)");

    insert.setArgs(insertKey, insertVal);

    SqlFieldsQuery delete = new SqlFieldsQuery("DELETE FROM test_long WHERE id = ?");

    delete.setArgs(insertKey);

    GridQueryProcessor qryProc = ((IgniteEx)ignite()).context().query();

    try (FieldsQueryCursor<List<?>> insCur = qryProc.querySqlFields(insert, false);
         FieldsQueryCursor<List<?>> delCur = qryProc.querySqlFields(delete, false)) {
        // No-op, there is no result
    }
    catch (Exception ign) {
        // collision occurred, ignoring
    }

    return true;
}
 

/** */
private static void insertToCache(IgniteCache<?, ?> cache, List<String> train, String tableName) {
    SqlFieldsQuery insertTrain = new SqlFieldsQuery("insert into " + tableName + " " +
        "(passengerid, pclass, survived, name, sex, age, sibsp, parch, ticket, fare, cabin, embarked) " +
        "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

    int seq = 0;
    for (String s : train) {
        String[] line = s.split(";");
        int pclass = parseInteger(line[0]);
        int survived = parseInteger(line[1]);
        String name = line[2];
        String sex = line[3];
        double age = parseDouble(line[4]);
        double sibsp = parseInteger(line[5]);
        double parch = parseInteger(line[6]);
        String ticket = line[7];
        double fare = parseDouble(line[8]);
        String cabin = line[9];
        String embarked = line[10];
        insertTrain.setArgs(seq++, pclass, survived, name, sex, age, sibsp, parch, ticket, fare, cabin, embarked);
        cache.query(insertTrain);
    }
}
 

/**
 * Loads MovieLens dataset into cache.
 *
 * @param ignite Ignite instance.
 * @param cnt Number of rating point to be loaded.
 * @throws IOException If dataset not found.
 */
private static void loadMovieLensDataset(Ignite ignite, IgniteCache<?, ?> cache, int cnt) throws IOException {
    SqlFieldsQuery qry = new SqlFieldsQuery("insert into ratings (rating_id, movie_id, user_id, rating) values (?, ?, ?, ?)");
    int seq = 0;
    for (String s : new SandboxMLCache(ignite).loadDataset(MLSandboxDatasets.MOVIELENS)) {
        String[] line = s.split(",");

        int userId = Integer.valueOf(line[0]);
        int movieId = Integer.valueOf(line[1]);
        double rating = Double.valueOf(line[2]);

        qry.setArgs(seq++, movieId, userId, rating);
        cache.query(qry);

        if (seq == cnt)
            break;
    }
}
 

/**
 * Example for SQL queries based on all employees working
 * for a specific organization (query uses distributed join).
 */
private static void sqlQueryWithDistributedJoin() {
    IgniteCache<Long, Person> cache = Ignition.ignite().cache(PERSON_CACHE);

    // SQL clause query which joins on 2 types to select people for a specific organization.
    String joinSql =
        "select pers.* from Person as pers, \"" + ORG_CACHE + "\".Organization as org " +
        "where pers.orgId = org.id " +
        "and lower(org.name) = lower(?)";

    SqlFieldsQuery qry = new SqlFieldsQuery(joinSql).setArgs("ApacheIgnite");

    // Enable distributed joins for query.
    qry.setDistributedJoins(true);

    // Execute queries for find employees for different organizations.
    print("Following people are 'ApacheIgnite' employees (distributed join): ", cache.query(qry).getAll());

    qry.setArgs("Other");

    print("Following people are 'Other' employees (distributed join): ", cache.query(qry).getAll());
}
 

/**
 * Execute sql query with PUBLIC schema and specified positional arguments of sql query.
 *
 * @param sql query.
 * @param args positional arguments if sql query got ones.
 * @return fetched result set.
 */
private static List<List<?>> execute(String sql, Object... args) {
    SqlFieldsQuery qry = new SqlFieldsQuery(sql).setSchema("PUBLIC");

    if (!F.isEmpty(args))
        qry.setArgs(args);

    return cache.query(qry).getAll();
}
 

/**
 * @param ignite Ignite.
 * @param sql Sql.
 * @param args Args.
 * @return Results.
 */
@SuppressWarnings("unchecked")
private List<List<?>> execSql(Ignite ignite, String sql, Object... args) {
    SqlFieldsQuery qry = new SqlFieldsQuery(sql).setLocal(local);

    if (args != null && args.length > 0)
        qry.setArgs(args);

    return ((IgniteEx)ignite).context().query().querySqlFields(qry, false).getAll();
}
 

/**
 * Execute SQL query.
 *
 * @param sql SQL.
 * @param args Parameters arguments.
 * @return Query results.
 */
protected List<List<?>> executeSql(String sql, Object... args) {
    if (args == null || args.length == 0)
        System.out.println(">>> " + sql);
    else
        System.out.println(">>> " + sql + " " + Arrays.toString(args));

    SqlFieldsQuery qry = new SqlFieldsQuery(sql);

    if (args != null && args.length > 0)
        qry.setArgs(args);

    return executeSqlFieldsQuery(qry);
}
 

/**
 * @param cache Cache.
 */
private void checkPersonOrganizationGroupBy(IgniteCache cache) {
    if (skipQuery(cache, PERSON_CACHE_NAME, ORG_CACHE_NAME))
        return;

    qry = "checkPersonOrganizationGroupBy";

    // Max salary per organization.
    SqlFieldsQuery q = new SqlFieldsQuery("select max(p.salary) " +
        "from \"" + PERSON_CACHE_NAME + "\".Person p join \"" + ORG_CACHE_NAME + "\".Organization o " +
        "on p.orgId = o.id " +
        "group by o.name " +
        "having o.id = ?");

    q.setDistributedJoins(distributedJoins());

    for (Map.Entry<Integer, Integer> e : data.maxSalaryPerOrg.entrySet()) {
        Integer orgId = e.getKey();
        Integer maxSalary = e.getValue();

        q.setArgs(orgId);

        List<List<?>> res = cache.query(q).getAll();

        String errMsg = "Expected data [orgId=" + orgId + ", maxSalary=" + maxSalary + ", data=" + data + "]";

        // MaxSalary == -1 means that there are no persons at organization.
        if (maxSalary > 0) {
            assertEquals(errMsg, 1, res.size());
            assertEquals(errMsg, 1, res.get(0).size());
            assertEquals(errMsg, maxSalary, res.get(0).get(0));
        }
        else
            assertEquals(errMsg, 0, res.size());
    }
}
 

/**
 * @param minSalary Min salary.
 * @param maxSalary Max salary.
 * @return Query results.
 * @throws Exception If failed.
 */
private Collection<List<?>> executeQueryJoin(double minSalary, double maxSalary) throws Exception {
    IgniteCache<Integer, Object> cache = cacheForOperation(true);

    SqlFieldsQuery qry = new SqlFieldsQuery(
        "select p.id, p.orgId, p.firstName, p.lastName, p.salary, o.name " +
        "from Person p " +
        "left join Organization o " +
        "on p.id = o.id " +
        "where salary >= ? and salary <= ?");

    qry.setArgs(minSalary, maxSalary);

    return cache.query(qry).getAll();
}
 

/** {@inheritDoc} */
@SuppressWarnings("deprecation")
@Override public SqlFieldsQuery generateFieldsQuery(String cacheName, SqlQuery qry) {
    String schemaName = schema(cacheName);

    String type = qry.getType();

    H2TableDescriptor tblDesc = schemaMgr.tableForType(schemaName, cacheName, type);

    if (tblDesc == null)
        throw new IgniteSQLException("Failed to find SQL table for type: " + type,
            IgniteQueryErrorCode.TABLE_NOT_FOUND);

    String sql;

    try {
        sql = generateFieldsQueryString(qry.getSql(), qry.getAlias(), tblDesc);
    }
    catch (IgniteCheckedException e) {
        throw new IgniteException(e);
    }

    SqlFieldsQuery res = new SqlFieldsQuery(sql);

    res.setArgs(qry.getArgs());
    res.setDistributedJoins(qry.isDistributedJoins());
    res.setLocal(qry.isLocal());
    res.setPageSize(qry.getPageSize());
    res.setPartitions(qry.getPartitions());
    res.setReplicatedOnly(qry.isReplicatedOnly());
    res.setSchema(schemaName);
    res.setSql(sql);

    if (qry.getTimeout() > 0)
        res.setTimeout(qry.getTimeout(), TimeUnit.MILLISECONDS);

    return res;
}
 

/**
 * Test simple query by key.
 *
 * @param cacheName Cache name.
 * @param key1 Key 1.
 * @param key2 Key 2.
 * @param <T> Key type.
 */
private <T> void testKeyQuery(final String cacheName, final T key1, final T key2) {
    final IgniteCache<T, Person> cache = ignite(0).cache(cacheName);

    final Person p1 = new Person("p1");
    final Person p2 = new Person("p2");

    cache.put(key1, p1);
    cache.put(key2, p2);

    final SqlQuery<T, Person> qry = new SqlQuery<>(Person.class, "where _key=?");

    final SqlFieldsQuery fieldsQry = new SqlFieldsQuery("select _key, _val, * from Person where _key=?");

    qry.setLocal(isLocal());
    fieldsQry.setLocal(isLocal());

    qry.setArgs(key1);
    fieldsQry.setArgs(key1);

    final List<Cache.Entry<T, Person>> res = cache.query(qry).getAll();
    final List<List<?>> fieldsRes = cache.query(fieldsQry).getAll();

    assertEquals(1, res.size());
    assertEquals(1, fieldsRes.size());

    assertEquals(p1, res.get(0).getValue());
    assertEquals(key1, res.get(0).getKey());

    assertTrue(fieldsRes.get(0).size() >= 2);
    assertEquals(key1, fieldsRes.get(0).get(0));
    assertEquals(p1, fieldsRes.get(0).get(1));
}
 

/**
 * Test simple query by value.
 *
 * @param cacheName Cache name.
 * @param val1 Value 1.
 * @param val2 Value 2.
 * @param <T> Value type.
 */
private <T> void testValQuery(final String cacheName, final T val1, final T val2) {
    final IgniteCache<Person, T> cache = ignite(0).cache(cacheName);

    final Class<?> valType = val1.getClass();

    final Person p1 = new Person("p1");
    final Person p2 = new Person("p2");

    cache.put(p1, val1);
    cache.put(p2, val2);

    final SqlQuery<Person, T> qry = new SqlQuery<>(valType, "where _val=?");

    final SqlFieldsQuery fieldsQry = new SqlFieldsQuery("select _key, _val, * from " + valType.getSimpleName() + " where _val=?");

    qry.setLocal(isLocal());
    fieldsQry.setLocal(isLocal());

    qry.setArgs(val1);
    fieldsQry.setArgs(val1);

    final List<Cache.Entry<Person, T>> res = cache.query(qry).getAll();
    final List<List<?>> fieldsRes = cache.query(fieldsQry).getAll();

    assertEquals(1, res.size());
    assertEquals(1, fieldsRes.size());

    assertEquals(p1, res.get(0).getKey());
    assertEquals(val1, res.get(0).getValue());

    assertTrue(fieldsRes.get(0).size() >= 2);
    assertEquals(p1, fieldsRes.get(0).get(0));
    assertEquals(val1, fieldsRes.get(0).get(1));
}
 

/**
 * Ensure index is used in plan.
 *
 * @param node Node.
 * @param idxName Index name.
 * @param sql SQL.
 * @param args Arguments.
 */
protected static void assertIndexUsed(IgniteEx node, String idxName, String sql, Object... args) {
    SqlFieldsQuery qry = new SqlFieldsQuery("EXPLAIN " + sql);

    if (args != null && args.length > 0)
        qry.setArgs(args);

    String plan = (String)node.cache(CACHE_NAME).query(qry).getAll().get(0).get(0);

    assertTrue("Index is not used: " + plan, plan.toLowerCase().contains(idxName.toLowerCase()));
}
 

/**
 * Ensure index is not used in plan.
 *
 * @param node Node.
 * @param idxName Index name.
 * @param sql SQL.
 * @param args Arguments.
 */
protected static void assertIndexNotUsed(IgniteEx node, String idxName, String sql, Object... args) {
    SqlFieldsQuery qry = new SqlFieldsQuery("EXPLAIN " + sql);

    if (args != null && args.length > 0)
        qry.setArgs(args);

    String plan = (String)node.cache(CACHE_NAME).query(qry).getAll().get(0).get(0);

    assertFalse("Index is used: " + plan, plan.contains(idxName));
}
 

/**
 * Performs update.
 *
 * @param cache Cache.
 * @param sqlText SQL text.
 * @param args Parameters.
 * @return Update counter.
 * @throws SQLException If failed.
 */
private Integer doSingleUpdate(IgniteCache<?, ?> cache, String sqlText, List<Object> args) throws SQLException {
    SqlFieldsQuery qry = new SqlFieldsQueryEx(sqlText, false);

    qry.setPageSize(fetchSize);
    qry.setLocal(locQry);
    qry.setCollocated(collocatedQry);
    qry.setDistributedJoins(distributedJoins);
    qry.setSchema(schemaName);
    qry.setArgs(args == null ? null : args.toArray());

    QueryCursorImpl<List<?>> qryCursor = (QueryCursorImpl<List<?>>)cache.withKeepBinary().query(qry);

    if (qryCursor.isQuery()) {
        throw createJdbcSqlException(getError("Query produced result set", qry),
            IgniteQueryErrorCode.STMT_TYPE_MISMATCH);
    }

    List<List<?>> rows = qryCursor.getAll();

    if (F.isEmpty(rows))
        return SUCCESS_NO_INFO;

    if (rows.size() != 1)
        throw new SQLException(getError("Expected single row for update operation result", qry));

    List<?> row = rows.get(0);

    if (F.isEmpty(row) || row.size() != 1)
        throw new SQLException(getError("Expected row size of 1 for update operation", qry));

    Object objRes = row.get(0);

    if (!(objRes instanceof Long))
        throw new SQLException(getError("Unexpected update result type", qry));

    Long longRes = (Long)objRes;

    if (longRes > Integer.MAX_VALUE) {
        IgniteLogger log = ignite.log();

        if (log != null)
            log.warning(getError("Query updated row counter (" + longRes + ") exceeds integer range", qry));

        return Integer.MAX_VALUE;
    }

    return longRes.intValue();
}
 

/**
 * Benchmarked action that inserts and immediately deletes row.
 *
 * @param ctx Operation context.
 */
@Override public boolean test(Map<Object, Object> ctx) throws Exception {
    long insertKey = getGroupNumber(ctx) * args.range() + nextRandom(args.range() - 1);

    String insertQry = String.format("INSERT INTO %s VALUES (?, ?, ?, ?)", TBL_NAME);

    SqlFieldsQuery insert1 = new SqlFieldsQuery(insertQry);
    insert1.setArgs(insertKey, new BigDecimal(insertKey + 1), LocalDate.ofEpochDay(insertKey), insertKey + 2);

    ++insertKey;

    SqlFieldsQuery insert2 = new SqlFieldsQuery(insertQry);
    insert2.setArgs(insertKey, new BigDecimal(insertKey + 1), LocalDate.ofEpochDay(insertKey), insertKey + 2);

    GridQueryProcessor qryProc = ((IgniteEx)ignite()).context().query();

    long selectKey = nextRandom(args.range());

    SqlFieldsQuery select1 = new SqlFieldsQuery(String.format("select * FROM %s where DATE_COL >= ? " +
        "and DATE_COL < ? and DEC_COL= ?", TBL_NAME));
    select1.setArgs(LocalDate.ofEpochDay(selectKey), LocalDate.ofEpochDay(selectKey + 1), selectKey + 1);

    SqlFieldsQuery select2 = new SqlFieldsQuery(String.format("select * FROM %s where DATE_COL = ? " +
        "and DEC_COL= ?", TBL_NAME));
    select2.setArgs(LocalDate.ofEpochDay(selectKey), selectKey + 1);

    SqlFieldsQuery delete1 = new SqlFieldsQuery(String.format("DELETE FROM %s WHERE id = ?", TBL_NAME));
    delete1.setArgs(--insertKey);

    SqlFieldsQuery delete2 = new SqlFieldsQuery(String.format("DELETE FROM %s WHERE DATE_COL = ?", TBL_NAME));
    delete2.setArgs(LocalDate.ofEpochDay(++insertKey));

    try (FieldsQueryCursor<List<?>> insCur1 = qryProc.querySqlFields(insert1, false);
         FieldsQueryCursor<List<?>> insCur2 = qryProc.querySqlFields(insert2, false);
         FieldsQueryCursor<List<?>> selCur1 = qryProc.querySqlFields(select1, false);
         FieldsQueryCursor<List<?>> selCur2 = qryProc.querySqlFields(select2, false);
         FieldsQueryCursor<List<?>> delCur1 = qryProc.querySqlFields(delete1, false);
         FieldsQueryCursor<List<?>> delCur2 = qryProc.querySqlFields(delete2, false)) {

        resCount += insCur1.getAll().size();
        resCount += insCur2.getAll().size();
        resCount += selCur1.getAll().size();
        resCount += selCur2.getAll().size();
        resCount += delCur1.getAll().size();
        resCount += delCur2.getAll().size();
    }
    catch (Exception e) {
        BenchmarkUtils.error("error: ", e);
    }

    return true;
}