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

Example 11 with Row

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

the class TableSelectTest method basicQuery.

@Test
public void basicQuery() {
    try {
        sqlUpdate("drop table if exists basicQuery");
        sqlUpdate("create table basicQuery (_id varchar(32), name varchar(20), birthday date, age int)");
        sqlUpdate("insert into basicQuery values ('some long UUID', 'Sakila', '2000-05-27', 14)");
        Table table = this.schema.getTable("basicQuery");
        Map<String, Object> params = new HashMap<>();
        params.put("name", "Saki%");
        params.put("age", 20);
        RowResult rows = table.select("birthday, `_id`, name").where("name like :name AND age < :age").bind(params).execute();
        // verify metadata
        List<String> columnNames = rows.getColumnNames();
        assertEquals("birthday", columnNames.get(0));
        assertEquals("_id", columnNames.get(1));
        assertEquals("name", columnNames.get(2));
        Row row = rows.next();
        assertEquals("2000-05-27", row.getString(0));
        assertEquals("2000-05-27", row.getString("birthday"));
        assertEquals("Sakila", row.getString(2));
        assertEquals("Sakila", row.getString("name"));
        assertEquals("some long UUID", row.getString(1));
        assertEquals("some long UUID", row.getString("_id"));
        // select with multiple projection params
        rows = table.select("`_id`", "name", "birthday").where("name like :name AND age < :age").bind(params).execute();
        // verify metadata
        columnNames = rows.getColumnNames();
        assertEquals("_id", columnNames.get(0));
        assertEquals("name", columnNames.get(1));
        assertEquals("birthday", columnNames.get(2));
    } finally {
        sqlUpdate("drop table if exists basicQuery");
    }
}

Example 12 with Row

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

the class TableSelectTest method testComplexQuery.

@Test
public void testComplexQuery() {
    try {
        sqlUpdate("drop table if exists complexQuery");
        sqlUpdate("create table complexQuery (name varchar(32), age int, something int)");
        sqlUpdate("insert into complexQuery values ('Mamie', 11, 0)");
        sqlUpdate("insert into complexQuery values ('Eulalia', 11, 0)");
        sqlUpdate("insert into complexQuery values ('Polly', 12, 0)");
        sqlUpdate("insert into complexQuery values ('Rufus', 12, 0)");
        sqlUpdate("insert into complexQuery values ('Cassidy', 13, 0)");
        sqlUpdate("insert into complexQuery values ('Olympia', 14, 0)");
        sqlUpdate("insert into complexQuery values ('Lev', 14, 0)");
        sqlUpdate("insert into complexQuery values ('Tierney', 15, 0)");
        sqlUpdate("insert into complexQuery values ('Octavia', 15, 0)");
        sqlUpdate("insert into complexQuery values ('Vesper', 16, 0)");
        sqlUpdate("insert into complexQuery values ('Caspian', 17, 0)");
        sqlUpdate("insert into complexQuery values ('Romy', 17, 0)");
        Table table = this.schema.getTable("complexQuery");
        // Result:
        // age_group | cnt
        // 11        | 2   <-- filtered out by where
        // 12        | 2   <-- filtered out by limit
        // 13        | 1   <-- filtered out by having
        // 14        | 2   * second row in result
        // 15        | 2   * first row in result
        // 16        | 1   <-- filtered out by having
        // 17        | 2   <-- filtered out by offset
        SelectStatement stmt = table.select("age as age_group, count(name) as cnt, something");
        stmt.where("age > 11 and 1 < 2 and 40 between 30 and 900");
        stmt.groupBy("something", "age_group");
        stmt.having("cnt > 1");
        stmt.orderBy("age_group desc");
        RowResult rows = stmt.limit(2).offset(1).execute();
        Row row = rows.next();
        assertEquals(15, row.getInt(0));
        assertEquals(2, row.getInt(1));
        assertEquals(2, row.getByte(1));
        assertEquals(2, row.getLong(1));
        assertEquals(new BigDecimal("2"), row.getBigDecimal(1));
        assertEquals(true, row.getBoolean(1));
        row = rows.next();
        assertEquals(14, row.getInt(0));
        assertEquals(2, row.getInt(1));
        assertFalse(rows.hasNext());
    } finally {
        sqlUpdate("drop table if exists complexQuery");
    }
}

Example 13 with Row

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

the class TableSelectTest method allColumns.

@Test
public void allColumns() {
    try {
        sqlUpdate("drop table if exists allColumns");
        sqlUpdate("create table allColumns (x int, y int, z int)");
        sqlUpdate("insert into allColumns values (1,2,3)");
        Table table = this.schema.getTable("allColumns");
        // * must come first, as with SQL
        SelectStatement stmt = table.select("*, 42 as a_number, '43' as a_string");
        Row row = stmt.execute().next();
        assertEquals(42, row.getInt("a_number"));
        assertEquals(1, row.getInt("x"));
        assertEquals(2, row.getInt("y"));
        assertEquals(3, row.getInt("z"));
        assertEquals("43", row.getString("a_string"));
    } finally {
        sqlUpdate("drop table if exists allColumns");
    }
}

Example 14 with Row

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

the class TableSelectTest method testBug22931433.

/**
 * Tests fix for Bug#22931433, GETTING VALUE OF BIT COLUMN RESULTS IN EXCEPTION.
 */
@Test
public void testBug22931433() {
    sqlUpdate("drop table if exists testBug22931433");
    sqlUpdate("create table testBug22931433(c1 bit(8), c2 bit(16), c3 bit(24), c4 bit(32), c5 bit(40), c6 bit(48), c7 bit(56), c8 bit(64), cb1 bit(1), cb2 bit(64))");
    Table table = this.schema.getTable("testBug22931433");
    table.insert("c1", "c2", "c3", "c4", "c5", "c6", "c7", "c8", "cb1", "cb2").values("a", "ba", "cba", "dcba", "edcba", "fedcba", "gfedcba", "hgfedcba", 0x01, -1).execute();
    table.insert("c1", "c2", "c3", "c4", "c5", "c6", "c7", "c8", "cb1", "cb2").values(0xcc, 0xcccc, 0xcccccc, 0xccccccccL, 0xccccccccccL, 0xccccccccccccL, 0xccccccccccccccL, 0xccccccccccccccccL, 0x00, -2).execute();
    String testUrl = this.baseUrl + (this.baseUrl.contains("?") ? "&" : "?") + makeParam(PropertyKey.jdbcCompliantTruncation, "false", true);
    Session s1 = this.fact.getSession(testUrl);
    table = s1.getDefaultSchema().getTable("testBug22931433");
    RowResult rows = table.select("c1, c2, c3, c4, c5, c6, c7, c8, cb1, cb2").execute();
    Row row = rows.next();
    assertEquals('a', row.getByte("c1"));
    assertEquals('a', row.getByte("c2"));
    assertEquals('a', row.getByte("c3"));
    assertEquals('a', row.getByte("c4"));
    assertEquals('a', row.getByte("c5"));
    assertEquals('a', row.getByte("c6"));
    assertEquals('a', row.getByte("c7"));
    assertEquals('a', row.getByte("c8"));
    assertEquals(97, row.getInt("c1"));
    assertEquals(25185, row.getInt("c2"));
    assertEquals(6513249, row.getInt("c3"));
    assertEquals(1684234849, row.getInt("c4"));
    // truncated to 4 bytes
    assertEquals(1684234849, row.getInt("c5"));
    // truncated to 4 bytes
    assertEquals(1684234849, row.getInt("c6"));
    // truncated to 4 bytes
    assertEquals(1684234849, row.getInt("c7"));
    // truncated to 4 bytes
    assertEquals(1684234849, row.getInt("c8"));
    assertEquals(97, row.getLong("c1"));
    assertEquals(25185, row.getLong("c2"));
    assertEquals(6513249, row.getLong("c3"));
    assertEquals(1684234849, row.getLong("c4"));
    assertEquals(435475931745L, row.getLong("c5"));
    assertEquals(112585661964897L, row.getLong("c6"));
    assertEquals(29104508263162465L, row.getLong("c7"));
    assertEquals(7523094288207667809L, row.getLong("c8"));
    assertEquals(BigDecimal.valueOf(97), row.getBigDecimal("c1"));
    assertEquals(BigDecimal.valueOf(25185), row.getBigDecimal("c2"));
    assertEquals(BigDecimal.valueOf(6513249), row.getBigDecimal("c3"));
    assertEquals(BigDecimal.valueOf(1684234849), row.getBigDecimal("c4"));
    assertEquals(BigDecimal.valueOf(435475931745L), row.getBigDecimal("c5"));
    assertEquals(BigDecimal.valueOf(112585661964897L), row.getBigDecimal("c6"));
    assertEquals(BigDecimal.valueOf(29104508263162465L), row.getBigDecimal("c7"));
    assertEquals(BigDecimal.valueOf(7523094288207667809L), row.getBigDecimal("c8"));
    assertEquals(Double.valueOf(97), Double.valueOf(row.getDouble("c1")));
    assertEquals(Double.valueOf(25185), Double.valueOf(row.getDouble("c2")));
    assertEquals(Double.valueOf(6513249), Double.valueOf(row.getDouble("c3")));
    assertEquals(Double.valueOf(1684234849), Double.valueOf(row.getDouble("c4")));
    assertEquals(Double.valueOf(435475931745L), Double.valueOf(row.getDouble("c5")));
    assertEquals(Double.valueOf(112585661964897L), Double.valueOf(row.getDouble("c6")));
    assertEquals(Double.valueOf(29104508263162465L), Double.valueOf(row.getDouble("c7")));
    assertEquals(Double.valueOf(7523094288207667809L), Double.valueOf(row.getDouble("c8")));
    assertEquals(true, row.getBoolean("c1"));
    assertEquals(true, row.getBoolean("cb1"));
    assertEquals(true, row.getBoolean("cb2"));
    assertEquals(BigDecimal.valueOf(97).toString(), row.getString("c1"));
    assertEquals(BigDecimal.valueOf(25185).toString(), row.getString("c2"));
    assertEquals(BigDecimal.valueOf(6513249).toString(), row.getString("c3"));
    assertEquals(BigDecimal.valueOf(1684234849).toString(), row.getString("c4"));
    assertEquals(BigDecimal.valueOf(435475931745L).toString(), row.getString("c5"));
    assertEquals(BigDecimal.valueOf(112585661964897L).toString(), row.getString("c6"));
    assertEquals(BigDecimal.valueOf(29104508263162465L).toString(), row.getString("c7"));
    assertEquals(BigDecimal.valueOf(7523094288207667809L).toString(), row.getString("c8"));
    assertThrows(DataConversionException.class, "Unsupported conversion from BIT to java.sql.Date", new Callable<Void>() {

        public Void call() throws Exception {
            row.getDate("c1");
            return null;
        }
    });
    assertThrows(DataConversionException.class, "Unsupported conversion from BIT to com.mysql.cj.xdevapi.DbDoc", new Callable<Void>() {

        public Void call() throws Exception {
            row.getDbDoc("c1");
            return null;
        }
    });
    assertThrows(DataConversionException.class, "Unsupported conversion from BIT to java.sql.Time", new Callable<Void>() {

        public Void call() throws Exception {
            row.getTime("c1");
            return null;
        }
    });
    assertThrows(DataConversionException.class, "Unsupported conversion from BIT to java.sql.Timestamp", new Callable<Void>() {

        public Void call() throws Exception {
            row.getTimestamp("c1");
            return null;
        }
    });
    // test negative values
    Row row2 = rows.next();
    assertEquals(-52, row2.getByte("c1"));
    assertEquals(-52, row2.getByte("c2"));
    assertEquals(-52, row2.getByte("c3"));
    assertEquals(-52, row2.getByte("c4"));
    assertEquals(-52, row2.getByte("c5"));
    assertEquals(-52, row2.getByte("c6"));
    assertEquals(-52, row2.getByte("c7"));
    assertEquals(-52, row2.getByte("c8"));
    assertEquals(204, row2.getInt("c1"));
    assertEquals(52428, row2.getInt("c2"));
    assertEquals(13421772, row2.getInt("c3"));
    assertEquals(-858993460, row2.getInt("c4"));
    // truncated to 4 bytes
    assertEquals(-858993460, row2.getInt("c5"));
    // truncated to 4 bytes
    assertEquals(-858993460, row2.getInt("c6"));
    // truncated to 4 bytes
    assertEquals(-858993460, row2.getInt("c7"));
    // truncated to 4 bytes
    assertEquals(-858993460, row2.getInt("c8"));
    assertEquals(204, row2.getLong("c1"));
    assertEquals(52428, row2.getLong("c2"));
    assertEquals(13421772, row2.getLong("c3"));
    assertEquals(3435973836L, row2.getLong("c4"));
    assertEquals(879609302220L, row2.getLong("c5"));
    assertEquals(225179981368524L, row2.getLong("c6"));
    assertEquals(57646075230342348L, row2.getLong("c7"));
    assertEquals(-3689348814741910324L, row2.getLong("c8"));
    assertEquals(BigDecimal.valueOf(204), row2.getBigDecimal("c1"));
    assertEquals(BigDecimal.valueOf(52428), row2.getBigDecimal("c2"));
    assertEquals(BigDecimal.valueOf(13421772), row2.getBigDecimal("c3"));
    assertEquals(BigDecimal.valueOf(3435973836L), row2.getBigDecimal("c4"));
    assertEquals(BigDecimal.valueOf(879609302220L), row2.getBigDecimal("c5"));
    assertEquals(BigDecimal.valueOf(225179981368524L), row2.getBigDecimal("c6"));
    assertEquals(BigDecimal.valueOf(57646075230342348L), row2.getBigDecimal("c7"));
    assertEquals(new BigDecimal(new BigInteger("14757395258967641292")), row2.getBigDecimal("c8"));
    assertEquals(Double.valueOf(204), Double.valueOf(row2.getDouble("c1")));
    assertEquals(Double.valueOf(52428), Double.valueOf(row2.getDouble("c2")));
    assertEquals(Double.valueOf(13421772), Double.valueOf(row2.getDouble("c3")));
    assertEquals(Double.valueOf(3435973836L), Double.valueOf(row2.getDouble("c4")));
    assertEquals(Double.valueOf(879609302220L), Double.valueOf(row2.getDouble("c5")));
    assertEquals(Double.valueOf(225179981368524L), Double.valueOf(row2.getDouble("c6")));
    assertEquals(Double.valueOf(57646075230342348L), Double.valueOf(row2.getDouble("c7")));
    assertEquals(Double.valueOf(new BigInteger("14757395258967641292").doubleValue()), Double.valueOf(row2.getDouble("c8")));
    assertEquals(false, row2.getBoolean("c8"));
    assertEquals(false, row2.getBoolean("cb1"));
    assertEquals(false, row2.getBoolean("cb2"));
    sqlUpdate("drop table if exists testBug22931433");
    s1.close();
}

Example 15 with Row

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

the class CollectionTest method validateIndex.

private void validateIndex(String keyName, String collName, int sequence, boolean unique, boolean required, boolean array, String dataType, boolean unsigned, Integer length) throws Exception {
    boolean indexFound = false;
    SqlResult res = this.session.sql("show index from `" + collName + "`").execute();
    assertTrue(res.hasNext());
    for (Row row : res.fetchAll()) {
        if (keyName.equals(row.getString("Key_name"))) {
            if (sequence != row.getInt("Seq_in_index")) {
                continue;
            }
            indexFound = true;
            assertEquals(collName.toUpperCase(), row.getString("Table").toUpperCase());
            assertEquals(unique ? "0" : "1", row.getString("Non_unique"));
            if (!array && row.getString("Column_name") != null) {
                String[] columnNameTokens = row.getString("Column_name").toString().split("_");
                assertEquals(dataType, unsigned ? columnNameTokens[1] + "_" + columnNameTokens[2] : columnNameTokens[1]);
            } else if (array && row.getString("Expression") != null) {
                String expr = row.getString("Expression");
                int typePos = expr.indexOf(" as ");
                assertTrue(typePos >= 0, "Not an array index?");
                expr = expr.substring(typePos + 4, expr.length() - 1);
                assertTrue(expr.endsWith("array"));
                expr = expr.substring(0, expr.indexOf(" array"));
                assertEquals(dataType, expr);
            } else {
                fail("Unexpected type of index");
            }
            // assertEquals("", row.getString("Collation")); // TODO enable when applicable
            assertEquals(length == null ? 0 : length.intValue(), row.getInt("Sub_part"));
            assertEquals(required ? "" : "YES", row.getString("Null"));
            break;
        }
    }
    if (!indexFound) {
        throw new Exception("Index not found.");
    }
}