org.apache.ignite.cache.query.SqlFieldsQuery Java Examples

/**
 * Example for SQL-based fields queries that return only required fields instead of whole key-value pairs.
 */
private static void sqlFieldsQueryWithJoin() {
    IgniteCache<?, ?> cache = Ignition.ignite().cache(EMPLOYEE_CACHE_NAME);

    // Create query to get names of all employees.
    SqlFieldsQuery qry = new SqlFieldsQuery(
        "select e.ename, d.dname " +
            "from Employee e, \"" + DEPARTMENT_CACHE_NAME + "\".Department d " +
            "where e.deptno = d.deptno");

    // Execute query to get collection of rows. In this particular
    // case each row will have one element with full name of an employees.
    Collection<List<?>> res = cache.query(qry).getAll();

    // Print persons' names and departments' names.
    logDecorated("==Names of all employees and departments they belong to (SQL join)==", res);
}
 

/**
 * Query SQL fields.
 *
 * @param cctx Cache context.
 * @param qry Query.
 * @param cliCtx Client context.
 * @param keepBinary Keep binary flag.
 * @param failOnMultipleStmts If {@code true} the method must throws exception when query contains
 *      more then one SQL statement.
 * @param cancel Hook for query cancellation.
 * @return Cursor.
 */
public List<FieldsQueryCursor<List<?>>> querySqlFields(
    @Nullable final GridCacheContext<?, ?> cctx,
    final SqlFieldsQuery qry,
    final SqlClientContext cliCtx,
    final boolean keepBinary,
    final boolean failOnMultipleStmts,
    @Nullable final GridQueryCancel cancel
) {
    return querySqlFields(
        cctx,
        qry,
        cliCtx,
        keepBinary,
        failOnMultipleStmts,
        GridCacheQueryType.SQL_FIELDS,
        cancel
    );
}
 

/**
 * @param cache Cache.
 * @param minId Minimal ID.
 * @param pageSize Page size.
 * @param expSize The size of the expected results.
 * @param exp Expected results.
 * @param lazy Lazy mode flag.
 */
private void checkSqlFieldsQuery(ClientCache<Integer, Person> cache, int minId, int pageSize, int expSize,
    Map<Integer, Person> exp, boolean lazy) {
    SqlFieldsQuery qry = new SqlFieldsQuery("select id, name from Person where id >= ?")
        .setArgs(minId)
        .setPageSize(pageSize)
        .setLazy(lazy);

    try (QueryCursor<List<?>> cur = cache.query(qry)) {
        List<List<?>> res = cur.getAll();

        assertEquals(expSize, res.size());

        Map<Integer, Person> act = res.stream().collect(Collectors.toMap(
            r -> Integer.parseInt(r.get(0).toString()),
            r -> new Person(Integer.parseInt(r.get(0).toString()), r.get(1).toString())
        ));

        assertEquals(exp, act);
    }
}
 

/**
 * @param cache Cache.
 * @param sql SQL.
 */
private void checkNoIndexError(final IgniteCache<Object, Object> cache, final String sql) {
    Throwable err = GridTestUtils.assertThrows(log, new Callable<Void>() {
        @Override public Void call() throws Exception {
            SqlFieldsQuery qry = new SqlFieldsQuery(sql);

            qry.setDistributedJoins(true);

            cache.query(qry).getAll();

            return null;
        }
    }, CacheException.class, null);

    log.info("Error: " + err.getMessage());

    assertTrue("Unexpected error message: " + err.getMessage(),
        err.getMessage().contains("join condition does not use index"));
}
 

/**
 * @throws Exception If failed.
 */
@Test
public void testFieldsQueryMetadata() throws Exception {
    IgniteCache<UUID, Person> cache = jcache(UUID.class, Person.class);

    for (int i = 0; i < 100; i++)
        cache.put(UUID.randomUUID(), new Person("name-" + i, (i + 1) * 100));

    QueryCursor<List<?>> cur = cache.query(new SqlFieldsQuery("select name, salary from Person where name like ?")
        .setArgs("name-"));

    assertTrue(cur instanceof QueryCursorEx);

    QueryCursorEx<List<?>> curEx = (QueryCursorEx<List<?>>)cur;

    List<GridQueryFieldMetadata> meta = curEx.fieldsMeta();

    assertNotNull(meta);
    assertEquals(2, meta.size());
}
 

/**
 * Tests updating of all Date and Time fields.
 * <p>
 * Trim precision of {@link LocalDateTime} to milliseconds.
 * Nanosecond precision test -- {@link #testUpdateAllFields}.
 */
@Test
public void testUpdateAllFieldsMillisTimePrecision() {
    EntityWithDateTimeFields expEntity = new EntityWithDateTimeFields(entity);

    expEntity.setLocalTime(expEntity.getLocalTime().plusHours(1).withNano(0).plus(123, MILLIS));
    expEntity.setLocalDate(expEntity.getLocalDate().plusDays(1));
    expEntity.setLocalDateTime(LocalDateTime.of(expEntity.getLocalDate(), expEntity.getLocalTime()));

    SqlFieldsQuery qry = new SqlFieldsQuery(
        "update EntityWithDateTimeFields set locTime = ?, locDate = ?, locDateTime = ? where id = ?"
    ).setArgs(expEntity.getLocalTime(), expEntity.getLocalDate(), expEntity.getLocalDateTime(), entity.getId());

    List<List<?>> qryResults = cache.query(qry).getAll();

    assertEquals(1, qryResults.size());
    assertEquals(1L, qryResults.get(0).get(0));
    assertEquals(expEntity, cache.get(expEntity.getId()));
}
 

/**
 * It should not matter what cache mode does entry cache use, if there is no join
 */
@Test
public void testCrossCacheModeQuery() throws Exception {
    Ignite ignite = startGrid();

    ignite.cache(LOCAL_CACHE_NAME).put(1, LOCAL_CACHE_NAME);
    ignite.cache(REPLICATED_CACHE_NAME).put(1, REPLICATED_CACHE_NAME);
    ignite.cache(PARTITIONED_CACHE_NAME).put(1, PARTITIONED_CACHE_NAME);

    final List<String> cacheNamesList = F.asList(LOCAL_CACHE_NAME, REPLICATED_CACHE_NAME, PARTITIONED_CACHE_NAME);

    for (String entryCacheName: cacheNamesList) {
        for (String qryCacheName: cacheNamesList) {
            if (entryCacheName.equals(qryCacheName))
                continue;

            QueryCursor<List<?>> cursor = ignite.cache(entryCacheName).query(
                new SqlFieldsQuery("SELECT _VAL FROM \"" + qryCacheName + "\".String"));

            assertEquals(qryCacheName, (String)cursor.getAll().get(0).get(0));
        }
    }
}
 

/**
 * @param ignite Ignite instance.
 * @param fieldName Field name to check.
 * @param shouldExist Should exist flag.
 */
private void checkField(Ignite ignite, String cacheName, String fieldName, boolean shouldExist) {
    SqlFieldsQuery q = new SqlFieldsQuery(
        "SELECT * FROM " + cacheName + "." + TABLE_NAME + " LIMIT 1 OFFSET 0" );

    boolean exists = false;

    try (FieldsQueryCursor<List<?>> cursor = ignite.cache(cacheName).query(q)) {
        consume(cursor);

        for (int i = 0, cols = cursor.getColumnsCount(); i < cols; i++) {
            if (cursor.getFieldName(i).equals(fieldName.toUpperCase())) {
                exists = true;

                break;
            }
        }
    }

    Assert.assertEquals("Check field (" + fieldName + ") exists", shouldExist, exists);
}
 

/**
 * Example for SQL-based fields queries that return only required fields instead of whole key-value pairs.
 */
private static void groupByQuery() {
    IgniteCache<?, ?> cache = Ignition.ignite().cache(EMPLOYEE_CACHE_NAME);

    // Create query to get salary averages grouped by department name.
    // We don't need to perform any extra manual steps here, because
    // Employee data is colocated based on department IDs.
    SqlFieldsQuery qry = new SqlFieldsQuery(
        "select avg(e.sal), d.dname " +
            "from Employee e, \"" + DEPARTMENT_CACHE_NAME + "\".Department d " +
            "where e.deptno = d.deptno " +
            "group by d.dname " +
            "having avg(e.sal) > ?");

    // Execute query to get collection of rows.
    logDecorated("==Average salaries per Department (group-by query)==", cache.query(qry.setArgs(500)).getAll());
}
 

/**
 * @throws Exception On error.
 */
@Test
public void testPutAll() throws Exception {
    IgniteEx ign = startGrid(0);

    IgniteCache<Object, Object> cache = ign.createCache(new CacheConfiguration<>(DEFAULT_CACHE_NAME)
        .setAtomicityMode(atomicity())
        .setIndexedTypes(Integer.class, Integer.class));

    final Map<Integer, Integer> batch = new HashMap<>();

    for (int i = 0; i < KEYS; ++i)
        batch.put(i, i);

    Thread t = new Thread(() -> {
        cache.putAll(batch);
    });

    t.start();
    t.interrupt();
    t.join();

    assertEquals(cache.size(), cache.query(new SqlFieldsQuery("select * from Integer")).getAll().size());
}
 

/**
 * Check results of aggregate functions if no rows are selected.
 *
 * @throws Exception If failed,
 */
@Test
public void testEmptyCacheAggregates() throws Exception {
    final String cacheName = "ints";

    IgniteCache<Integer, Value> cache = ignite(0).getOrCreateCache(cacheConfig(cacheName, true,
        Integer.class, Value.class));

    try (QueryCursor<List<?>> qry = cache.query(new SqlFieldsQuery(
        "SELECT count(fst), sum(snd), avg(snd), min(snd), max(snd) FROM Value"))) {
        List<List<?>> result = qry.getAll();

        assertEquals(1, result.size());

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

        assertEquals("count", 0L, ((Number)row.get(0)).longValue());
        assertEquals("sum", null, row.get(1));
        assertEquals("avg", null, row.get(2));
        assertEquals("min", null, row.get(3));
        assertEquals("max", null, row.get(4));
    }
    finally {
        cache.destroy();
    }
}
 

/**
 *
 */
@Test
public void testGroupConcatWithDistinct() {
    IgniteCache c = ignite(CLIENT).cache(CACHE_NAME);

    HashMap<Integer, String> exp = new HashMap<>();

    exp.put(1, "A");
    exp.put(2, "B,C");
    exp.put(3, "D,E,F");
    exp.put(4, "G,H,I,J");
    exp.put(5, "K,L,M,N,O");
    exp.put(6, "P,Q,R,S,T,U");

    List<List<Object>> res = c.query(
        new SqlFieldsQuery("select grp, GROUP_CONCAT(DISTINCT str0 ORDER BY str0) from Value group by grp")
            .setCollocated(true)).getAll();

    HashMap<Integer, String> resMap = resultMap(res);

    assertEquals(exp, resMap);
}
 

/**
 * Constructor.
 *
 * @param qryDesc Query descriptor.
 * @param qryParams Query parameters.
 * @param remainingQry Remaining query.
 * @param paramsMeta metadata info about positional parameters of current parsed query (not includes remainingSql).
 * @param select Select.
 * @param dml DML.
 * @param cmd Command.
 */
public QueryParserResult(
    QueryDescriptor qryDesc,
    QueryParameters qryParams,
    SqlFieldsQuery remainingQry,
    @NotNull List<JdbcParameterMeta> paramsMeta,
    @Nullable QueryParserResultSelect select,
    @Nullable QueryParserResultDml dml,
    @Nullable QueryParserResultCommand cmd
) {
    assert paramsMeta != null;

    this.qryDesc = qryDesc;
    this.qryParams = qryParams;
    this.remainingQry = remainingQry;
    this.paramsMeta = paramsMeta;
    this.select = select;
    this.dml = dml;
    this.cmd = cmd;
}
 

/** Check automatic addition of index for keyFieldName column */
@Test
public void testAutoKeyFieldIndex() throws Exception {
    IgniteEx client = grid(NODE_CLIENT);
    IgniteCache<Integer, Person> cache = client.cache(CACHE_PERSON);

    QueryCursor<List<?>> cursor = cache.query(new SqlFieldsQuery("explain select * from Person where id = 1"));
    List<List<?>> results = cursor.getAll();
    assertEquals(1, results.size());
    assertTrue(((String)results.get(0).get(0)).contains("\"_key_PK_proxy\""));

    cursor = cache.query(new SqlFieldsQuery("explain select * from Person where _key = 1"));
    results = cursor.getAll();
    assertEquals(1, results.size());
    assertTrue(((String)results.get(0).get(0)).contains("\"_key_PK\""));
}
 

/**
 * @throws Exception If failed.
 */
@Test
public void testPrimitiveGeometry() throws Exception {
    IgniteCache<Long, Geometry> cache = createCache("geom", true, Long.class, Geometry.class);

    try {
        WKTReader r = new WKTReader();

        for (long i = 0; i < 100; i++)
            cache.put(i, r.read("POINT(" + i + " " + i + ")"));

        String plan = cache.query(new SqlFieldsQuery("explain select _key from Geometry where _val && ?")
            .setArgs(r.read("POLYGON((5 70, 5 80, 30 80, 30 70, 5 70))")).setLocal(true))
            .getAll().get(0).get(0).toString().toLowerCase();

        assertTrue("__ explain: " + plan, plan.contains("_val_idx"));
    }
    finally {
        cache.destroy();
    }
}
 

/** {@inheritDoc} */
@Override public List<FieldsQueryCursor<List<?>>> querySqlFields(
    String schemaName,
    SqlFieldsQuery qry,
    @Nullable SqlClientContext cliCtx,
    boolean keepBinary,
    boolean failOnMultipleStmts,
    GridQueryCancel cancel
) {
    IndexingWithContext.cliCtx = cliCtx;

    return super.querySqlFields(
        schemaName,
        qry,
        cliCtx,
        keepBinary,
        failOnMultipleStmts,
        cancel
    );
}
 

/**
 * @throws Exception If failed.
 */
@Test
public void testQueryFromNewClientCustomSchemaName() throws Exception {
    Ignite srv = startGrid("server");

    IgniteCache<Integer, Integer> cache1 = srv.createCache(new CacheConfiguration<Integer, Integer>().
        setName("cache1").setSqlSchema("cache1_sql").setIndexedTypes(Integer.class, Integer.class));
    IgniteCache<Integer, Integer> cache2 = srv.createCache(new CacheConfiguration<Integer, Integer>().
        setName("cache2").setSqlSchema("cache2_sql").setIndexedTypes(Integer.class, Integer.class));

    for (int i = 0; i < 10; i++) {
        cache1.put(i, i);
        cache2.put(i, i);
    }

    Ignite client = startClientGrid("client");

    IgniteCache<Integer, Integer> cache = client.cache("cache1");

    List<List<?>> res = cache.query(new SqlFieldsQuery(
        "select i1._val, i2._val from Integer i1 cross join cache2_sql.Integer i2")).getAll();

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

/**
 * Check that local query can be canceled either using async or non-async method. Local query is performed using
 * cache.query() API with "local" property "true".
 */
@Test
public void testCancelLocalQueryNative() throws Exception {
    IgniteInternalFuture cancelRes = cancel(1, asyncCancel);

    GridTestUtils.assertThrowsAnyCause(log, () -> {
        ignite.cache(DEFAULT_CACHE_NAME).query(
            new SqlFieldsQuery("select * from Integer where _key in " +
                "(select _key from Integer where awaitLatchCancelled() = 0) and shouldNotBeCalledMoreThan(128)")
                .setLocal(true)
                .setLazy(false)
        ).getAll();

        return null;
    }, QueryCancelledException.class, "The query was cancelled while executing.");

    // Ensures that there were no exceptions within async cancellation process.
    cancelRes.get(CHECK_RESULT_TIMEOUT);
}
 

/**
 *
 */
@Test
public void testMergeWithExplicitKey() {
    IgniteCache<String, Person> p = ignite(0).cache("S2P").withKeepBinary();

    p.query(new SqlFieldsQuery("merge into Person (_key, id, firstName) values ('s', ?, ?), " +
        "('a', 2, 'Alex')").setArgs(1, "Sergi"));

    assertEquals(createPerson(1, "Sergi"), p.get("s"));

    assertEquals(createPerson(2, "Alex"), p.get("a"));
}
 

/**
 * 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);
}
 

/**
 * @throws Exception If failed.
 */
@Test
public void testMultiStatement() throws Exception {
    final IgniteInternalCache<Integer, FactPurchase> cache = ((IgniteKernal)ignite).getCache(PART_CACHE_NAME);

    final GridQueryProcessor qryProc = ((IgniteKernal) ignite).context().query();

    final SqlFieldsQuery qry = new SqlFieldsQuery(
        "insert into FactPurchase(_key, id, productId, storeId, price) values (555, 555, 555, 555, 555);" +
        "select count(*) from FactPurchase"
    );

    GridTestUtils.assertThrows(log,
        new Callable<Object>() {
            @Override public Object call() throws Exception {
                qryProc.querySqlFields(cache.context(), qry, null, false, true);

                return null;
            }
        }, IgniteSQLException.class, "Multiple statements queries are not supported");

    List<FieldsQueryCursor<List<?>>> cursors = qryProc.querySqlFields(cache.context(), qry, null, false, false);

    assertEquals(2, cursors.size());

    for (FieldsQueryCursor<List<?>> cur : cursors)
        U.closeQuiet(cur);

    qry.setLocal(true);

    GridTestUtils.assertThrows(log,
        new Callable<Object>() {
            @Override public Object call() throws Exception {
                qryProc.querySqlFields(cache.context(), qry, null, false, false);

                return null;
            }
        }, IgniteSQLException.class, "Multiple statements queries are not supported for local queries");
}
 

/** {@inheritDoc} */
@Override public List<JdbcParameterMeta> parameterMetaData(
    String schemaName,
    SqlFieldsQuery sql
) throws IgniteSQLException {
    return null;
}
 

/**
 * Check if query hangs (due to reducer spins retrying to reserve partitions but they can't be reserved), we still
 * able to cancel it. Used mocked indexing simulates 100% unability.
 */
@Test
public void testCancelQueryIfPartitionsCantBeReservedOnMapNodes() throws Exception {
    // Releases thread that kills query when map nodes receive query request. At least one map node received is ok.
    GridMessageListener qryStarted = (node, msg, plc) -> {
        if (msg instanceof GridH2QueryRequest)
            TestSQLFunctions.cancelLatch.countDown();
    };

    for (int i = 0; i < NODES_COUNT; i++)
        grid(i).context().io().addMessageListener(GridTopic.TOPIC_QUERY, qryStarted);

    // Suspends distributed queries on the map nodes.
    MockedIndexing.failReservations = true;

    try {
        IgniteInternalFuture cancelFut = cancel(1, asyncCancel);

        GridTestUtils.assertThrows(log, () -> {
            ignite.cache(DEFAULT_CACHE_NAME).query(
                new SqlFieldsQuery("select * from Integer where _val <> 42")
            ).getAll();

            return null;
        }, CacheException.class, "The query was cancelled while executing.");

        cancelFut.get(CHECK_RESULT_TIMEOUT);
    }
    finally {
        for (int i = 0; i < NODES_COUNT; i++)
            grid(i).context().io().removeMessageListener(GridTopic.TOPIC_QUERY, qryStarted);
    }
}
 

/**
 * @throws Exception If failed.
 */
@Test
public void testInsertAndFastDeleteWithoutVersionConflict() throws Exception {
    ccfg = cacheConfiguration(cacheMode(), FULL_SYNC, 2, DFLT_PARTITION_COUNT)
        .setIndexedTypes(Integer.class, Integer.class);

    startGridsMultiThreaded(2);

    IgniteCache<?, ?> cache0 = grid(0).cache(DEFAULT_CACHE_NAME);

    try (Transaction tx1 = grid(0).transactions().txStart(PESSIMISTIC, REPEATABLE_READ)) {
        // obtain tx version
        cache0.query(new SqlFieldsQuery("select * from Integer where _key = 1"));

        runAsync(() -> {
            cache0.query(new SqlFieldsQuery("insert into Integer(_key, _val) values(?, ?)").setArgs(1, 1));
        }).get();

        cache0.query(new SqlFieldsQuery("delete from Integer where _key = ?").setArgs(1));

        tx1.commit();
    }
    catch (Exception e) {
        e.printStackTrace();

        fail("Exception is not expected here");
    }
}
 

/**
 *
 */
@Test
public void testMergeWithDynamicKeyInstantiation() {
    IgniteCache<Key, Person> p = ignite(0).cache("K2P").withKeepBinary();

    p.query(new SqlFieldsQuery(
        "merge into Person (key, id, firstName) values (1, ?, ?), (2, 2, 'Alex')").setArgs(1, "Sergi"));

    assertEquals(createPerson(1, "Sergi"), p.get(new Key(1)));

    assertEquals(createPerson(2, "Alex"), p.get(new Key(2)));
}
 

/** {@inheritDoc} */
@Override public void setUp(BenchmarkConfiguration cfg) throws Exception {
    super.setUp(cfg);

    memberId = cfg.memberId();

    if (memberId < 0)
        throw new IllegalStateException("Member id should be initialized with non-negative value");

    // We assume there is no client nodes in the cluster except clients that are yardstick drivers.
    driversNodesCnt = ignite().cluster().forClients().nodes().size();

    IgniteCountDownLatch dataIsReady = ignite().countDownLatch("fillDataLatch", 1, true, true);

    try {
        if (memberId == 0) {
            fillData(cfg, (IgniteEx)ignite(), args.range(), args.atomicMode());

            dataIsReady.countDown();
        }
        else {
            println(cfg, "No need to upload data for memberId=" + memberId + ". Just waiting");

            dataIsReady.await(DATA_WAIT_TIMEOUT_MIN, TimeUnit.MINUTES);

            println(cfg, "Data is ready.");
        }

    }
    catch (Throwable th) {
        dataIsReady.countDownAll();

        throw new RuntimeException("Fill Data failed.", th);
    }

    // Workaround for "Table TEST_LONG not found" on sql update.
    execute(new SqlFieldsQuery("SELECT COUNT(*) FROM test_long"));
}
 

/**
 * Test metrics for SQL cross cache queries.
 *
 * @throws Exception In case of error.
 */
@Test
public void testSqlFieldsCrossCacheQueryNotFullyFetchedMetrics() throws Exception {
    IgniteCache<Integer, String> cache = grid(0).context().cache().jcache("A");

    SqlFieldsQuery qry = new SqlFieldsQuery("select * from \"B\".String");
    qry.setPageSize(10);

    checkQueryNotFullyFetchedMetrics(cache, qry, false);
}
 

/** Tests local query over partitions. */
@Test
public void testLocalQuery() {
    Affinity<Object> affinity = grid(0).affinity("cl");

    int[] parts = affinity.primaryPartitions(grid(0).localNode());

    Arrays.sort(parts);

    IgniteCache<ClientKey, Client> cl = grid(0).cache("cl");

    SqlQuery<ClientKey, Client> qry1 = new SqlQuery<>(Client.class, "1=1");
    qry1.setLocal(true);
    qry1.setPartitions(parts[0]);

    List<Cache.Entry<ClientKey, Client>> clients = cl.query(qry1).getAll();

    for (Cache.Entry<ClientKey, Client> client : clients)
        assertEquals("Incorrect partition", parts[0], affinity.partition(client.getKey()));

    SqlFieldsQuery qry2 = new SqlFieldsQuery("select cl._KEY, cl._VAL from \"cl\".Client cl");
    qry2.setLocal(true);
    qry2.setPartitions(parts[0]);

    List<List<?>> rows = cl.query(qry2).getAll();

    for (List<?> row : rows)
        assertEquals("Incorrect partition", parts[0], affinity.partition(row.get(0)));
}
 

/**
 * 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();
}
 

/**
 *
 */
@Test
public void testUuidHandling() {
    IgniteCache<UUID, Integer> p = ignite(0).cache("U2I");

    UUID id = UUID.randomUUID();

    p.query(new SqlFieldsQuery("insert into Integer(_key, _val) values (?, ?)").setArgs(id, 1));

    assertEquals(1, (int)p.get(id));
}