Examples with Row com.mysql.cj.xdevapi.Row used on opensource projects

Example 1 with Row

use of com.mysql.cj.xdevapi.Row in project aws-mysql-jdbc by awslabs.

the class SecureSessionTest method assertUser.

private void assertUser(String user, Session sess) {
    SqlResult rows = sess.sql("SELECT USER(),CURRENT_USER()").execute();
    Row row = rows.fetchOne();
    assertEquals(user, row.getString(0).split("@")[0]);
    assertEquals(user, row.getString(1).split("@")[0]);
}

Example 2 with Row

use of com.mysql.cj.xdevapi.Row in project aws-mysql-jdbc by awslabs.

the class SessionTest method testFetchOneFetchAllAsync.

@Test
public void testFetchOneFetchAllAsync() throws Exception {
    Row r = null;
    List<Row> rowList = null;
    try {
        CompletableFuture<SqlResult> asyncSqlRes = this.session.sql("drop table if exists testFetchOneFetchAllAsync").executeAsync();
        SqlResult sqlRes = asyncSqlRes.get();
        asyncSqlRes = this.session.sql("create table testFetchOneFetchAllAsync(a int,b bigint,c double,d blob)").executeAsync();
        sqlRes = asyncSqlRes.get();
        asyncSqlRes = this.session.sql("insert into testFetchOneFetchAllAsync values(?,?,?,?)").bind(1, 11).bind(21, "A").executeAsync();
        sqlRes = asyncSqlRes.get();
        asyncSqlRes = this.session.sql("insert into testFetchOneFetchAllAsync values(?,?,?,?)").bind(2, 12).bind(22, "B").executeAsync();
        sqlRes = asyncSqlRes.get();
        asyncSqlRes = this.session.sql("insert into testFetchOneFetchAllAsync values(?,?,?,?)").bind(3, 13).bind(23, "C").executeAsync();
        sqlRes = asyncSqlRes.get();
        asyncSqlRes = this.session.sql("insert into testFetchOneFetchAllAsync values(?,?,?,?)").bind(4, 14).bind(23, "D").executeAsync();
        sqlRes = asyncSqlRes.get();
        // With FetchOne()
        asyncSqlRes = this.session.sql("select * from testFetchOneFetchAllAsync where a<=? order by a asc").bind(5).executeAsync();
        SqlResult sqlRes1 = asyncSqlRes.get();
        int i = 0;
        while (sqlRes1.hasNext()) {
            r = sqlRes1.fetchOne();
            assertEquals((long) (i + 1), r.getInt(0));
            i++;
        }
        assertThrows(WrongArgumentException.class, "Cannot fetchAll\\(\\) after starting iteration", () -> sqlRes1.fetchAll());
        asyncSqlRes = this.session.sql("select * from testFetchOneFetchAllAsync where a<=? order by a asc").bind(3).executeAsync();
        sqlRes = asyncSqlRes.get();
        rowList = sqlRes.fetchAll();
        assertEquals((long) 3, (long) rowList.size());
        for (i = 0; i < rowList.size(); i++) {
            r = rowList.get(i);
            assertEquals((long) (i + 1), r.getInt(0));
            i++;
        }
    } finally {
        sqlUpdate("drop table if exists testFetchOneFetchAllAsync");
    }
}

Example 3 with Row

use of com.mysql.cj.xdevapi.Row in project aws-mysql-jdbc by awslabs.

the class SessionTest method sqlArguments.

@Test
public void sqlArguments() {
    SqlStatement stmt = this.session.sql("select ? as a, 40 + ? as b, ? as c");
    SqlResult res = stmt.bind(1).bind(2).bind(3).execute();
    Row r = res.next();
    assertEquals("1", r.getString("a"));
    assertEquals("42", r.getString("b"));
    assertEquals("3", r.getString("c"));
}

Example 4 with Row

use of com.mysql.cj.xdevapi.Row in project aws-mysql-jdbc by awslabs.

the class SessionTest method testExecAsync.

@Test
public void testExecAsync() throws Exception {
    int i = 0;
    int NUMBER_OF_QUERIES = 5000;
    SqlResult sqlRes = null;
    Row r = null;
    CompletableFuture<SqlResult> asyncRes = null;
    long l1 = Long.MAX_VALUE, l2 = Long.MIN_VALUE;
    try {
        asyncRes = this.session.sql("drop Procedure if exists testExecAsyncProc").executeAsync();
        sqlRes = asyncRes.get();
        asyncRes = this.session.sql("create procedure testExecAsyncProc (in p1 int,in p2 int) begin select 1; select p1; select p2; end;").executeAsync();
        sqlRes = asyncRes.get();
        asyncRes = this.session.sql("drop table if exists testExecAsync").executeAsync();
        sqlRes = asyncRes.get();
        asyncRes = this.session.sql("create table testExecAsync (c1 int,c2 bigint,c3 double,c4 bool,c5 year,c6 float,c7 blob,c8 enum('xs','s','m','l','xl'),c9 set('a','b','c','d'))").executeAsync();
        sqlRes = asyncRes.get();
        asyncRes = this.session.sql("insert into testExecAsync values(1," + l1 + ",100.11,true,2000,100.101,'v1','xs',('a,b'))").executeAsync();
        sqlRes = asyncRes.get();
        assertEquals(1, sqlRes.getAffectedItemsCount());
        asyncRes = this.session.sql("insert into testExecAsync values(2," + (l1 - 1) + ",101.11,false,2001,101.101,'v2','s',('b,c')),(3," + (l2 - 2) + ",102.11,true,2002,102.101,'v3','m',('a,b,c'))").executeAsync();
        sqlRes = asyncRes.get();
        assertEquals(2, sqlRes.getAffectedItemsCount());
        asyncRes = this.session.sql("insert into testExecAsync values(4," + (l1 - 4) + ",104.11,false,2004,104.101,'v4','s',('b,c,d')),(5," + (l2 - 5) + ",105.11,true,2005,105.101,'v5','m',('a,c'))").executeAsync();
        sqlRes = asyncRes.get();
        assertEquals(2, sqlRes.getAffectedItemsCount());
        this.session.startTransaction();
        asyncRes = this.session.sql("insert into testExecAsync select * from testExecAsync").executeAsync();
        sqlRes = asyncRes.get();
        assertEquals(5, sqlRes.getAffectedItemsCount());
        asyncRes = this.session.sql("select c1 as 'col1',c2 as 'col2', c3 as 'col3',c4 as 'col4',c5 as 'col5', c6 as 'col6', c7 as 'col7', c8 as 'col8' , c9 as 'col9' from testExecAsync order by c1 asc").executeAsync();
        sqlRes = asyncRes.get();
        assertTrue(sqlRes.hasData());
        while (sqlRes.hasNext()) {
            r = sqlRes.next();
        // System.out.println("col1 :" + r.getInt("col1"));
        // System.out.println("col2 :" + r.getLong("col2"));
        // System.out.println("col3 :" + r.getBigDecimal("col3"));
        // System.out.println("col4 :" + r.getBoolean("col4"));
        // System.out.println("col5 :" + r.getInt("col5"));
        // System.out.println("col6 :" + r.getDouble("col6"));
        // System.out.println("col7 :" + r.getString("col7"));
        // System.out.println("col8 :" + r.getString("col8"));
        // System.out.println("col9 :" + r.getString("col9"));
        }
        asyncRes = this.session.sql("update testExecAsync set c2=c2-1").executeAsync();
        sqlRes = asyncRes.get();
        assertEquals(10, sqlRes.getAffectedItemsCount());
        this.session.rollback();
        asyncRes = this.session.sql("create  unique index idx on  testExecAsync(c1)").executeAsync();
        sqlRes = asyncRes.get();
        asyncRes = this.session.sql("select count(*) from testExecAsync").executeAsync();
        sqlRes = asyncRes.get();
        r = sqlRes.next();
        assertEquals(5, r.getInt(0));
        asyncRes = this.session.sql("Delete from testExecAsync where c1=5 ").executeAsync();
        sqlRes = asyncRes.get();
        assertEquals(1, sqlRes.getAffectedItemsCount());
        /* WIth Bind */
        asyncRes = this.session.sql("insert into testExecAsync values  (?,?,?,?,?,?,?,?,?)").bind(6).bind(l1 - 6).bind(106.11).bind(true).bind(2006).bind(106.101).bind("v6").bind("xl").bind("a,a,a,a,a,a,a,a,a").executeAsync();
        sqlRes = asyncRes.get();
        assertEquals(1, sqlRes.getAffectedItemsCount());
        List<CompletableFuture<SqlResult>> futures = new ArrayList<>();
        for (i = 0; i < NUMBER_OF_QUERIES; ++i) {
            if (i % 5 == 0) {
                futures.add(this.session.sql("insert into testExecAsync (c1,c2,c9) values  (?,?,?)").bind(10).bind(l1 - 10).bind("a,d,c").executeAsync());
            } else if (i % 5 == 1) {
                futures.add(this.session.sql("REPLACE  DELAYED  into testExecAsync (c1,c2,c9) values  (?,?,?)").bind(10).bind(l1 - 100).bind("a,c").executeAsync());
            } else if (i % 5 == 2) {
                futures.add(this.session.sql("update  testExecAsync set c9 =? where c1 = (?+?+?)").bind("a,d,c,b").bind(3).bind(5).bind(2).executeAsync());
            } else if (i % 5 == 3) {
                futures.add(this.session.sql("select * from testExecAsync where c9&8 and c9&1 and c1 = (?+?)").bind(9).bind(1).executeAsync());
            } else {
                futures.add(this.session.sql("delete from testExecAsync where  c9 & ? and c9&1 and c1 = (?+?)").bind(8).bind(9).bind(1).executeAsync());
            }
        }
        for (i = 0; i < NUMBER_OF_QUERIES; ++i) {
            if (i % 5 == 0) {
                sqlRes = futures.get(i).get();
                assertEquals(1, sqlRes.getAffectedItemsCount());
            } else if (i % 5 == 1) {
                sqlRes = futures.get(i).get();
                assertEquals(2, sqlRes.getAffectedItemsCount());
            } else if (i % 5 == 2) {
                sqlRes = futures.get(i).get();
                assertEquals(1, sqlRes.getAffectedItemsCount());
            } else if (i % 5 == 3) {
                sqlRes = futures.get(i).get();
                r = sqlRes.next();
                assertEquals(10, r.getInt(0));
                assertFalse(sqlRes.hasNext());
            } else {
                sqlRes = futures.get(i).get();
                assertEquals(1, sqlRes.getAffectedItemsCount());
            }
        }
    } finally {
        sqlUpdate("drop Procedure if exists testExecAsyncProc");
        sqlUpdate("drop table if exists testExecAsync");
    }
}

Example 5 with Row

use of com.mysql.cj.xdevapi.Row in project aws-mysql-jdbc by awslabs.

the class CollectionFindTest method testCollectionFindOverlaps.

@Test
public void testCollectionFindOverlaps() throws Exception {
    assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");
    try {
        int i = 0, j = 0, maxrec = 8, minArraySize = 3;
        DbDoc doc = null;
        DocResult docs = null;
        long l3 = 2147483647;
        double d1 = 1000.1234;
        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
            newDoc2.add("F1", new JsonNumber().setValue(String.valueOf(i + 1)));
            JsonArray jarray = new JsonArray();
            for (j = 0; j < (minArraySize + i); j++) {
                jarray.addValue(new JsonNumber().setValue(String.valueOf((l3 + j + i))));
            }
            newDoc2.add("ARR1", jarray);
            JsonArray karray = new JsonArray();
            for (j = 0; j < (minArraySize + i); j++) {
                karray.addValue(new JsonNumber().setValue(String.valueOf((d1 + j + i))));
            }
            newDoc2.add("ARR2", karray);
            JsonArray larray = new JsonArray();
            for (j = 0; j < (minArraySize + i); j++) {
                larray.addValue(new JsonString().setValue("St_" + i + "_" + j));
            }
            newDoc2.add("ARR3", larray);
            this.collection.add(newDoc2).execute();
            newDoc2 = null;
            jarray = null;
        }
        assertEquals((maxrec), this.collection.count());
        /* find with single OVERLAPS in array */
        docs = this.collection.find("2147483647 overlaps $.ARR1").execute();
        doc = docs.next();
        assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());
        Table table = this.schema.getCollectionAsTable(this.collectionName);
        RowResult rows = table.select("doc->$._id as _id").where("2147483647 overlaps $.ARR1").execute();
        Row r = rows.next();
        assertEquals("\"1000\"", r.getString("_id"));
        assertFalse(rows.hasNext());
        /* find with array OVERLAPS array */
        docs = this.collection.find("[2147483647, 2147483648, 2147483649] overlaps $.ARR1").execute();
        doc = docs.next();
        assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
        doc = docs.next();
        assertEquals("1001", (((JsonString) doc.get("_id")).getString()));
        doc = docs.next();
        assertEquals("1002", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());
        rows = table.select("doc->$._id as _id").where("[2147483647, 2147483648, 2147483649] overlaps $.ARR1").execute();
        r = rows.next();
        assertEquals("\"1000\"", r.getString("_id"));
        r = rows.next();
        assertEquals("\"1001\"", r.getString("_id"));
        r = rows.next();
        assertEquals("\"1002\"", r.getString("_id"));
        assertFalse(rows.hasNext());
        /* find with array OVERLAPS array with orderBy */
        docs = this.collection.find("[2147483648, 2147483648, 2147483649] overlaps $.ARR1").orderBy("_id").execute();
        doc = docs.next();
        assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
        doc = docs.next();
        assertEquals("1001", (((JsonString) doc.get("_id")).getString()));
        doc = docs.next();
        assertEquals("1002", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());
        /* */
        docs = this.collection.find("[!false && true] OVERLAPS [true]").execute();
        assertEquals(maxrec, docs.count());
        /* Overlaps with NULL */
        docs = this.collection.find("NULL overlaps $.ARR1").execute();
        assertFalse(docs.hasNext());
        rows = table.select("doc->$._id as _id").where("NULL overlaps $.ARR1").execute();
        assertFalse(rows.hasNext());
        docs = this.collection.find("$.ARR1 overlaps null").execute();
        assertFalse(docs.hasNext());
        rows = table.select("doc->$._id as _id").where("$.ARR1 overlaps NULL").execute();
        assertFalse(rows.hasNext());
        /* Not Overlaps with NULL */
        docs = this.collection.find("NULL not overlaps $.ARR1").execute();
        assertTrue(docs.hasNext());
        assertEquals(maxrec, docs.count());
        rows = table.select("doc->$._id as _id").where("NULL not overlaps $.ARR1").execute();
        assertTrue(rows.hasNext());
        assertEquals(maxrec, docs.count());
        docs = this.collection.find("$.ARR1 not overlaps null").execute();
        assertTrue(docs.hasNext());
        assertEquals(maxrec, docs.count());
        rows = table.select("doc->$._id as _id").where("$.ARR1 not overlaps null").execute();
        assertTrue(rows.hasNext());
        assertEquals(maxrec, docs.count());
        /* Test OVERLAPS/NOT OVERLAPS with empty array - Expected to pass, though the array is empty but still valid */
        // checking the case insensitivity as well
        docs = this.collection.find("[] Overlaps $.ARR1").execute();
        assertFalse(docs.hasNext());
        rows = table.select().where("[] ovErlaps $.ARR1").execute();
        assertFalse(rows.hasNext());
        // checking the case insensitivity as well
        docs = this.collection.find("$.ARR1 overlapS []").execute();
        assertFalse(docs.hasNext());
        rows = table.select().where("$.ARR1 ovErlaps []").execute();
        assertFalse(rows.hasNext());
        docs = this.collection.find("[] not overlaps $.ARR1").execute();
        assertTrue(docs.hasNext());
        assertEquals(maxrec, docs.count());
        rows = table.select().where("[] not overlaps $.ARR1").execute();
        assertTrue(rows.hasNext());
        assertEquals(maxrec, docs.count());
        // checking the case insensitivity as well
        docs = this.collection.find("$.ARR1 not oveRlaps []").execute();
        assertTrue(docs.hasNext());
        assertEquals(maxrec, docs.count());
        rows = table.select().where("$.ARR1 not overlaps []").execute();
        assertTrue(rows.hasNext());
        assertEquals(maxrec, docs.count());
        /* When the right number of operands are not provided - error should be thrown */
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 0", () -> this.collection.find("overlaps $.ARR1").execute());
        assertThrows(WrongArgumentException.class, "No more tokens when expecting one at token pos 4", () -> this.collection.find("$.ARR1 OVERLAPS").execute());
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 0", () -> this.collection.find("OVERLAPS").execute());
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 1", () -> this.collection.find("not overlaps $.ARR1").execute());
        assertThrows(WrongArgumentException.class, "No more tokens when expecting one at token pos 5", () -> this.collection.find("$.ARR1 NOT OVERLAPS").execute());
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 1", () -> this.collection.find("not OVERLAPS").execute());
        final Table table1 = this.schema.getCollectionAsTable(this.collectionName);
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 0", () -> table1.select().where("overlaps $.ARR1").execute());
        assertThrows(WrongArgumentException.class, "No more tokens when expecting one at token pos 4", () -> table1.select().where("$.ARR1 OVERLAPS").execute());
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 0", () -> table1.select().where("OVERLAPS").execute());
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 1", () -> table1.select().where("not overlaps $.ARR1").execute());
        assertThrows(WrongArgumentException.class, "No more tokens when expecting one at token pos 5", () -> table1.select().where("$.ARR1 NOT OVERLAPS").execute());
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 1", () -> table1.select().where("not OVERLAPS").execute());
        /* invalid criteria, e.g. .find("[1, 2, 3] OVERLAPS $.age") . where $.age is atomic value */
        dropCollection("coll2");
        Collection coll2 = this.schema.createCollection("coll2", true);
        coll2.add("{ \"_id\": \"1\", \"name\": \"nonjson\", \"age\": \"50\",\"arrayField\":[1,[7]]}").execute();
        // The below command should give exception, but X-plugin doesn't return any error
        docs = coll2.find("[1,2,3] overlaps $.age").execute();
        assertEquals(0, docs.count());
        docs = coll2.find("arrayField OVERLAPS [7]").execute();
        assertEquals(0, docs.count());
        docs = coll2.find("arrayField[1] OVERLAPS [7]").execute();
        assertEquals(1, docs.count());
        table = this.schema.getCollectionAsTable("coll2");
        rows = table.select().where("[1,2,3] overlaps $.age").execute();
        assertEquals(0, rows.count());
        /* Test with empty spaces */
        dropCollection("coll3");
        Collection coll3 = this.schema.createCollection("coll3", true);
        // List contains an array without any space
        coll3.add("{ \"_id\":1, \"name\": \"Record1\",\"list\":[\"\"], \"age\":15, \"intList\":[1,2,3] }").execute();
        // List contains an array with space
        coll3.add("{ \"_id\":2, \"name\": \"overlaps\",\"list\":[\" \"],\"age\":24}").execute();
        coll3.add("{ \"_id\":3, \"overlaps\": \"overlaps\",\"age\":30}").execute();
        docs = coll3.find("[''] OVERLAPS $.list").execute();
        assertEquals(1, docs.count());
        assertEquals(new Integer(1), ((JsonNumber) docs.next().get("_id")).getInteger());
        table = this.schema.getCollectionAsTable("coll3");
        rows = table.select("doc->$._id as _id").where("[''] overlaps $.list").execute();
        r = rows.next();
        assertEquals("1", r.getString("_id"));
        docs = coll3.find("[' '] OVERLAPS $.list").execute();
        assertEquals(1, docs.count());
        assertEquals(new Integer(2), ((JsonNumber) docs.next().get("_id")).getInteger());
        rows = table.select("doc->$._id as _id").where("[' '] overlaps $.list").execute();
        r = rows.next();
        assertEquals("2", r.getString("_id"));
        docs = coll3.find("'overlaps' OVERLAPS $.name").execute();
        assertEquals(1, docs.count());
        assertEquals(new Integer(2), ((JsonNumber) docs.next().get("_id")).getInteger());
        rows = table.select("doc->$._id as _id").where("'overlaps' overlaps $.name").execute();
        r = rows.next();
        assertEquals(1, docs.count());
        assertEquals("2", r.getString("_id"));
        docs = coll3.find("[3] OVERLAPS $.intList").execute();
        assertEquals(1, docs.count());
        rows = table.select().where("[3] overlaps $.intList").execute();
        assertEquals(1, rows.count());
        /* Escape the keyword, to use it as identifier */
        docs = coll3.find("`overlaps` OVERLAPS $.`overlaps`").execute();
        assertEquals(1, docs.count());
        rows = table.select().where("'overlaps' overlaps $.`overlaps`").execute();
        assertEquals(1, rows.count());
        docs = coll3.find("$.`overlaps` OVERLAPS `overlaps`").execute();
        assertEquals(1, docs.count());
        rows = table.select().where("$.`overlaps` overlaps 'overlaps'").execute();
        assertEquals(1, rows.count());
        dropCollection("coll4");
        Collection coll4 = this.schema.createCollection("coll4", true);
        coll4.add("{\"overlaps\":{\"one\":1, \"two\":2, \"three\":3},\"list\":{\"one\":1, \"two\":2, \"three\":3},\"name\":\"one\"}").execute();
        coll4.add("{\"overlaps\":{\"one\":1, \"two\":2, \"three\":3},\"list\":{\"four\":4, \"five\":5, \"six\":6},\"name\":\"two\"}").execute();
        coll4.add("{\"overlaps\":{\"one\":1, \"three\":3, \"five\":5},\"list\":{\"two\":2, \"four\":4, \"six\":6},\"name\":\"three\"}").execute();
        coll4.add("{\"overlaps\":{\"one\":1, \"three\":3, \"five\":5},\"list\":{\"three\":3, \"six\":9, \"nine\":9},\"name\":\"four\"}").execute();
        coll4.add("{\"overlaps\":{\"one\":1, \"three\":3, \"five\":5},\"list\":{\"three\":6, \"six\":12, \"nine\":18},\"name\":\"five\"}").execute();
        coll4.add("{\"overlaps\":{\"one\":[1,2,3]}, \"list\":{\"one\":[3,4,5]}, \"name\":\"six\"}").execute();
        coll4.add("{\"overlaps\":{\"one\":[1,2,3]}, \"list\":{\"one\":[1,2,3]}, \"name\":\"seven\"}").execute();
        docs = coll4.find("`overlaps` OVERLAPS `list`").execute();
        assertEquals(3, docs.count());
        doc = docs.fetchOne();
        assertEquals("one", (((JsonString) doc.get("name")).getString()));
        doc = docs.fetchOne();
        assertEquals("four", (((JsonString) doc.get("name")).getString()));
        doc = docs.fetchOne();
        assertEquals("seven", (((JsonString) doc.get("name")).getString()));
        table = this.schema.getCollectionAsTable("coll4");
        rows = table.select("doc->$.name as name").where("$.`overlaps` OVERLAPS $.`list`").execute();
        assertEquals(3, rows.count());
        r = rows.next();
        assertEquals("\"one\"", r.getString("name"));
    } finally {
        dropCollection("coll4");
        dropCollection("coll3");
        dropCollection("coll2");
    }
}