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

Example 6 with Row

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

the class CollectionFindTest method testCollectionFindInSanity.

@Test
public void testCollectionFindInSanity() throws Exception {
    assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");
    int i = 0, maxrec = 10;
    DbDoc doc = null;
    DocResult docs = null;
    DbDoc[] jsonlist = new DbDocImpl[maxrec];
    for (i = 0; i < maxrec; i++) {
        DbDoc newDoc2 = new DbDocImpl();
        newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
        newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
        newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(10 * (i + 1) + 0.1234)));
        newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(i + 1)));
        newDoc2.add("F4", new JsonNumber().setValue(String.valueOf(10000 - i)));
        jsonlist[i] = newDoc2;
        newDoc2 = null;
    }
    this.collection.add(jsonlist).execute();
    assertEquals((maxrec), this.collection.count());
    /* find without Condition */
    docs = this.collection.find().fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3,$.F2/10 as tmp1,1/2 as tmp2").orderBy("$.F3").execute();
    i = 0;
    while (docs.hasNext()) {
        doc = docs.next();
        i++;
    }
    assertEquals((maxrec), i);
    /* find with single element IN which uses json_contains */
    docs = this.collection.find("'1001' in $._id").execute();
    doc = docs.next();
    assertEquals("1001", (((JsonString) doc.get("_id")).getString()));
    assertFalse(docs.hasNext());
    /* find with multiple IN which uses json_contains */
    String findCond = "";
    for (i = 1; i < maxrec; i++) {
        findCond = findCond + "'";
        findCond = findCond + String.valueOf(i + 1000) + "' not in $._id";
        if (i != maxrec - 1) {
            findCond = findCond + " and ";
        }
    }
    docs = this.collection.find(findCond).execute();
    doc = docs.next();
    assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
    assertFalse(docs.hasNext());
    /* find with single IN for string with orderBy */
    docs = this.collection.find("'Field-1-Data-2' in $.F1").orderBy("CAST($.F4 as SIGNED)").execute();
    doc = docs.next();
    assertEquals(String.valueOf(1002), (((JsonString) doc.get("_id")).getString()));
    assertFalse(docs.hasNext());
    /* find with single IN for numeric with orderBy */
    docs = this.collection.find("10000 in $.F4").orderBy("CAST($.F4 as SIGNED)").execute();
    doc = docs.next();
    assertEquals(String.valueOf(1000), (((JsonString) doc.get("_id")).getString()));
    assertFalse(docs.hasNext());
    /* find with single IN for float with orderBy */
    docs = this.collection.find("20.1234 in $.F2").orderBy("CAST($.F4 as SIGNED)").execute();
    doc = docs.next();
    assertEquals(String.valueOf(1001), (((JsonString) doc.get("_id")).getString()));
    assertFalse(docs.hasNext());
    /* Testing with table */
    Table table = this.schema.getCollectionAsTable(this.collectionName);
    /* find with single IN for string */
    RowResult rows = table.select("doc->$._id as _id").where("'1001' in doc->$._id").execute();
    Row r = rows.next();
    assertEquals(r.getString("_id"), "\"1001\"");
    assertFalse(rows.hasNext());
    /* find with multiple IN in single select */
    findCond = "";
    for (i = 1; i < maxrec; i++) {
        findCond = findCond + "'";
        findCond = findCond + String.valueOf(i + 1000) + "' not in doc->$._id";
        if (i != maxrec - 1) {
            findCond = findCond + " and ";
        }
    }
    /* find with single IN for string */
    rows = table.select("doc->$._id as _id").where(findCond).execute();
    r = rows.next();
    assertEquals(r.getString("_id"), "\"1000\"");
    assertFalse(rows.hasNext());
    /* find with single IN for float */
    rows = table.select("doc->$._id as _id").where("20.1234 in doc->$.F2").execute();
    r = rows.next();
    assertEquals(r.getString("_id"), "\"1001\"");
    assertFalse(rows.hasNext());
    /* find with single IN for string */
    rows = table.select("doc->$._id as _id").where("'Field-1-Data-2' in doc->$.F1").execute();
    r = rows.next();
    assertEquals(r.getString("_id"), "\"1002\"");
    assertFalse(rows.hasNext());
    /* find with single IN for numeric */
    rows = table.select("doc->$._id as _id").where("10000 in doc->$.F4").execute();
    r = rows.next();
    assertEquals(r.getString("_id"), "\"1000\"");
    assertFalse(rows.hasNext());
}

Example 7 with Row

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

the class CollectionTest method validateArrayIndex.

private void validateArrayIndex(String keydName, String collName, int noFields) throws Exception {
    int indexFound = 0;
    boolean arrayExpr = false;
    Session sess = null;
    try {
        sess = new SessionFactory().getSession(this.baseUrl);
        SqlResult res = sess.sql("show index from `" + collName + "`").execute();
        assertTrue(res.hasNext());
        for (Row row : res.fetchAll()) {
            if (keydName.equals(row.getString("Key_name"))) {
                indexFound++;
                assertEquals(collName, row.getString("Table"));
                String expr = row.getString("Expression");
                System.out.println(expr);
                if (expr != null) {
                    arrayExpr = true;
                }
            }
        }
    } finally {
        if (sess != null) {
            sess.close();
            sess = null;
        }
    }
    if ((indexFound != noFields) || (!arrayExpr)) {
        throw new Exception("Index not matching");
    }
}

Example 8 with Row

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

the class SessionTest method testBug23721537.

@Test
public void testBug23721537() throws Exception {
    try {
        sqlUpdate("drop table if exists testBug23721537");
        sqlUpdate("create table testBug23721537 (id int, name varchar(20) not null)");
        sqlUpdate("insert into testBug23721537 values (0, 'a')");
        this.session.sql("drop procedure if exists newproc").execute();
        this.session.sql("create procedure newproc (in p1 int,in p2 char(20)) begin select 1; update testBug23721537 set name='b' where id=0; select 2; select 3; end;").execute();
        /* sync execution */
        SqlResult res1 = this.session.sql("call newproc(?,?)").bind(10).bind("X").execute();
        assertTrue(res1 instanceof SqlMultiResult);
        assertTrue(res1.hasData());
        assertTrue(res1.hasNext());
        Row r = res1.next();
        assertEquals(1, r.getInt(0));
        assertFalse(res1.hasNext());
        // res1 should finish streaming here
        SqlResult res2 = this.session.sql("select 10").execute();
        assertTrue(res1.nextResult());
        assertTrue(res1.hasData());
        assertTrue(res1.hasNext());
        r = res1.next();
        assertEquals(2, r.getInt(0));
        assertFalse(res1.hasNext());
        assertTrue(res1.nextResult());
        assertTrue(res1.hasData());
        assertTrue(res1.hasNext());
        r = res1.next();
        assertEquals(3, r.getInt(0));
        assertFalse(res1.hasNext());
        assertFalse(res1.nextResult());
        // 
        assertTrue(res2.hasData());
        assertTrue(res2.hasNext());
        r = res2.next();
        assertEquals(10, r.getInt(0));
        assertFalse(res2.hasNext());
        assertFalse(res2.nextResult());
        /* async execution */
        res1 = this.session.sql("call newproc(?,?)").bind(10).bind("X").executeAsync().get();
        assertTrue(res1.hasData());
        r = res1.next();
        assertEquals(1, r.getInt(0));
        assertFalse(res1.hasNext());
        // res1 should finish streaming here
        res2 = this.session.sql("select 10").executeAsync().get();
        assertTrue(res1.nextResult());
        assertTrue(res1.hasData());
        assertTrue(res1.hasNext());
        r = res1.next();
        assertEquals(2, r.getInt(0));
        assertFalse(res1.hasNext());
        assertTrue(res1.nextResult());
        assertTrue(res1.hasData());
        assertTrue(res1.hasNext());
        r = res1.next();
        assertEquals(3, r.getInt(0));
        assertFalse(res1.hasNext());
        assertFalse(res1.nextResult());
        // 
        assertTrue(res2.hasData());
        assertTrue(res2.hasNext());
        r = res2.next();
        assertEquals(10, r.getInt(0));
        assertFalse(res2.hasNext());
        assertFalse(res2.nextResult());
    } finally {
        sqlUpdate("drop table if exists testBug23721537");
        this.session.sql("drop procedure if exists newproc").execute();
    }
}

Example 9 with Row

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

the class TableInsertTest method basicInsert.

@Test
public void basicInsert() {
    try {
        sqlUpdate("drop table if exists basicInsert");
        sqlUpdate("drop view if exists basicInsertView");
        sqlUpdate("create table basicInsert (_id varchar(32), name varchar(20) not null default 'unknown', birthday date, age int)");
        sqlUpdate("create view basicInsertView as select * from basicInsert");
        Table table = this.schema.getTable("basicInsert");
        // insert with fields and values separately
        table.insert("_id", "birthday", "age").values(1, "2015-01-01", 1).execute();
        // insert all fields with values
        table.insert().values(2, "Orlando", "2014-01-01", 2).execute();
        Map<String, Object> row = new HashMap<>();
        row.put("_id", 3);
        row.put("age", 3);
        // insert a row in k/v pair form
        table.insert(row).execute();
        Table view = this.schema.getTable("basicInsertView");
        // insert with fields and values separately
        view.insert("_id", "birthday", "age").values(4, "2015-01-01", 1).execute();
        // insert all fields with values
        view.insert().values(5, "Orlando", "2014-01-01", 2).execute();
        row = new HashMap<>();
        row.put("_id", 6);
        row.put("age", 3);
        // insert a row in k/v pair form
        view.insert(row).execute();
        RowResult rows = table.select("_id, name, birthday, age").orderBy("_id").execute();
        Row r = rows.next();
        assertEquals("1", r.getString("_id"));
        assertEquals("unknown", r.getString("name"));
        assertEquals("2015-01-01", r.getString("birthday"));
        assertEquals(1, r.getInt("age"));
        r = rows.next();
        assertEquals("2", r.getString("_id"));
        assertEquals("Orlando", r.getString("name"));
        assertEquals("2014-01-01", r.getString("birthday"));
        assertEquals(2, r.getInt("age"));
        r = rows.next();
        assertEquals("3", r.getString("_id"));
        assertEquals("unknown", r.getString("name"));
        assertEquals(null, r.getString("birthday"));
        assertEquals(3, r.getInt("age"));
        r = rows.next();
        assertEquals("4", r.getString("_id"));
        assertEquals("unknown", r.getString("name"));
        assertEquals("2015-01-01", r.getString("birthday"));
        assertEquals(1, r.getInt("age"));
        r = rows.next();
        assertEquals("5", r.getString("_id"));
        assertEquals("Orlando", r.getString("name"));
        assertEquals("2014-01-01", r.getString("birthday"));
        assertEquals(2, r.getInt("age"));
        r = rows.next();
        assertEquals("6", r.getString("_id"));
        assertEquals("unknown", r.getString("name"));
        assertEquals(null, r.getString("birthday"));
        assertEquals(3, r.getInt("age"));
        assertFalse(rows.hasNext());
    } finally {
        sqlUpdate("drop table if exists basicInsert");
        sqlUpdate("drop view if exists basicInsertView");
    }
}

Example 10 with Row

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

the class TableSelectTest method countAllColumns.

@Test
public void countAllColumns() {
    try {
        sqlUpdate("drop table if exists countAllColumns");
        sqlUpdate("create table countAllColumns(x int, y int)");
        sqlUpdate("insert into countAllColumns values (1,1), (2,2), (3,3), (4,4)");
        Table table = this.schema.getTable("countAllColumns");
        Row row = table.select("count(*) + 10").execute().next();
        assertEquals(14, row.getInt(0));
    } finally {
        sqlUpdate("drop table if exists countAllColumns");
    }
}