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

Example 1 with SelectStatement

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

the class SessionTest method testPreparedStatementsCleanup.

@Test
public void testPreparedStatementsCleanup() {
    assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.14")), "MySQL 8.0.14+ is required to run this test.");
    try {
        // Prepare test data.
        this.schema.createCollection("testPrepStmtClean", true).add("{\"_id\":\"1\"}").execute();
        SessionFactory sf = new SessionFactory();
        /*
             * Test common usage.
             */
        Session testSession = sf.getSession(this.testProperties);
        int sessionThreadId = getThreadId(testSession);
        assertPreparedStatementsCount(sessionThreadId, 0, 1);
        assertPreparedStatementsStatusCounts(testSession, 0, 0, 0);
        // Initialize several *Statement objects.
        FindStatement testFind1 = testSession.getDefaultSchema().getCollection("testPrepStmtClean").find();
        SelectStatement testSelect1 = testSession.getDefaultSchema().getCollectionAsTable("testPrepStmtClean").select("_id");
        FindStatement testFind2 = testSession.getDefaultSchema().getCollection("testPrepStmtClean").find();
        SelectStatement testSelect2 = testSession.getDefaultSchema().getCollectionAsTable("testPrepStmtClean").select("_id");
        // 1st execute -> don't prepare.
        testFind1.execute();
        assertPreparedStatementsCountsAndId(testSession, 0, testFind1, 0, -1);
        testSelect1.execute();
        assertPreparedStatementsCountsAndId(testSession, 0, testSelect1, 0, -1);
        testFind2.execute();
        assertPreparedStatementsCountsAndId(testSession, 0, testFind2, 0, -1);
        testSelect2.execute();
        assertPreparedStatementsCountsAndId(testSession, 0, testSelect2, 0, -1);
        assertPreparedStatementsStatusCounts(testSession, 0, 0, 0);
        // 2nd execute -> prepare + execute.
        testFind1.execute();
        assertPreparedStatementsCountsAndId(testSession, 1, testFind1, 1, 1);
        testSelect1.execute();
        assertPreparedStatementsCountsAndId(testSession, 2, testSelect1, 2, 1);
        testFind2.execute();
        assertPreparedStatementsCountsAndId(testSession, 3, testFind2, 3, 1);
        testSelect2.execute();
        assertPreparedStatementsCountsAndId(testSession, 4, testSelect2, 4, 1);
        assertPreparedStatementsStatusCounts(testSession, 4, 4, 0);
        assertPreparedStatementsCount(sessionThreadId, 4, 1);
        /*
             * The following verifications are non-deterministic as System.gc() only hints the JVM to perform a garbage collection. This approach allows some
             * time for the JVM to execute the GC. In case of failure the repeats or wait times may have to be adjusted.
             * The test can be deleted entirely if no reasonable setup can be found.
             */
        // Nullify first statement.
        testFind1 = null;
        System.gc();
        int psCount, countdown = 10;
        do {
            try {
                Thread.sleep(100);
            } catch (InterruptedException e) {
            }
            testSession.sql("SELECT 1").execute();
            psCount = getPreparedStatementsCount(sessionThreadId);
        } while (psCount != 3 && --countdown > 0);
        assertPreparedStatementsStatusCounts(testSession, 4, 4, 1);
        assertPreparedStatementsCount(sessionThreadId, 3, 1);
        // Nullify second and third statements.
        testSelect1 = null;
        testFind2 = null;
        System.gc();
        countdown = 10;
        do {
            try {
                Thread.sleep(100);
            } catch (InterruptedException e) {
            }
            testSession.sql("SELECT 1").execute();
            psCount = getPreparedStatementsCount(sessionThreadId);
        } while (psCount != 1 && --countdown > 0);
        assertPreparedStatementsStatusCounts(testSession, 4, 4, 3);
        assertPreparedStatementsCount(sessionThreadId, 1, 1);
        // Nullify last statement.
        testSelect2 = null;
        System.gc();
        countdown = 10;
        do {
            try {
                Thread.sleep(100);
            } catch (InterruptedException e) {
            }
            testSession.sql("SELECT 1").execute();
            psCount = getPreparedStatementsCount(sessionThreadId);
        } while (psCount != 0 && --countdown > 0);
        assertPreparedStatementsStatusCounts(testSession, 4, 4, 4);
        assertPreparedStatementsCount(sessionThreadId, 0, 1);
        testSession.close();
        assertPreparedStatementsCount(sessionThreadId, 0, 1);
    } finally {
        this.schema.dropCollection("testPrepStmtClean");
    }
}

Example 2 with SelectStatement

use of com.mysql.cj.xdevapi.SelectStatement 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 3 with SelectStatement

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

the class TableSelectTest method testPreparedStatements.

@Test
public void testPreparedStatements() {
    assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.14")), "MySQL 8.0.14+ is required to run this test.");
    try {
        // Prepare test data.
        sqlUpdate("DROP TABLE IF EXISTS testPrepareSelect");
        sqlUpdate("CREATE TABLE testPrepareSelect (id INT PRIMARY KEY, ord INT)");
        sqlUpdate("INSERT INTO testPrepareSelect VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8)");
        SessionFactory sf = new SessionFactory();
        /*
             * Test common usage.
             */
        Session testSession = sf.getSession(this.testProperties);
        int sessionThreadId = getThreadId(testSession);
        assertPreparedStatementsCount(sessionThreadId, 0, 1);
        assertPreparedStatementsStatusCounts(testSession, 0, 0, 0);
        Table testTbl = testSession.getDefaultSchema().getTable("testPrepareSelect");
        // Initialize several SelectStatement objects.
        // Select all.
        SelectStatement testSelect1 = testTbl.select("ord");
        // Criteria with one placeholder.
        SelectStatement testSelect2 = testTbl.select("ord").where("ord >= :n");
        // Criteria with same placeholder repeated.
        SelectStatement testSelect3 = testTbl.select("ord").where("ord >= :n AND ord <= :n + 3");
        // Criteria with multiple placeholders.
        SelectStatement testSelect4 = testTbl.select("ord").where("ord >= :n AND ord <= :m");
        assertPreparedStatementsCountsAndId(testSession, 0, testSelect1, 0, -1);
        assertPreparedStatementsCountsAndId(testSession, 0, testSelect2, 0, -1);
        assertPreparedStatementsCountsAndId(testSession, 0, testSelect3, 0, -1);
        assertPreparedStatementsCountsAndId(testSession, 0, testSelect4, 0, -1);
        assertPreparedStatementsStatusCounts(testSession, 0, 0, 0);
        // A. Set binds: 1st execute -> non-prepared.
        assertTestPreparedStatementsResult(testSelect1.execute(), 1, 8);
        assertPreparedStatementsCountsAndId(testSession, 0, testSelect1, 0, -1);
        assertTestPreparedStatementsResult(testSelect2.bind("n", 2).execute(), 2, 8);
        assertPreparedStatementsCountsAndId(testSession, 0, testSelect2, 0, -1);
        assertTestPreparedStatementsResult(testSelect3.bind("n", 2).execute(), 2, 5);
        assertPreparedStatementsCountsAndId(testSession, 0, testSelect3, 0, -1);
        assertTestPreparedStatementsResult(testSelect4.bind("n", 2).bind("m", 5).execute(), 2, 5);
        assertPreparedStatementsCountsAndId(testSession, 0, testSelect4, 0, -1);
        assertPreparedStatementsStatusCounts(testSession, 0, 0, 0);
        // B. Set orderBy resets execution count: 1st execute -> non-prepared.
        assertTestPreparedStatementsResult(testSelect1.orderBy("id").execute(), 1, 8);
        assertPreparedStatementsCountsAndId(testSession, 0, testSelect1, 0, -1);
        assertTestPreparedStatementsResult(testSelect2.orderBy("id").execute(), 2, 8);
        assertPreparedStatementsCountsAndId(testSession, 0, testSelect2, 0, -1);
        assertTestPreparedStatementsResult(testSelect3.orderBy("id").execute(), 2, 5);
        assertPreparedStatementsCountsAndId(testSession, 0, testSelect3, 0, -1);
        assertTestPreparedStatementsResult(testSelect4.orderBy("id").execute(), 2, 5);
        assertPreparedStatementsCountsAndId(testSession, 0, testSelect4, 0, -1);
        assertPreparedStatementsStatusCounts(testSession, 0, 0, 0);
        // C. Set binds reuse statement: 2nd execute -> prepare + execute.
        assertTestPreparedStatementsResult(testSelect1.execute(), 1, 8);
        assertPreparedStatementsCountsAndId(testSession, 1, testSelect1, 1, 1);
        assertTestPreparedStatementsResult(testSelect2.bind("n", 3).execute(), 3, 8);
        assertPreparedStatementsCountsAndId(testSession, 2, testSelect2, 2, 1);
        assertTestPreparedStatementsResult(testSelect3.bind("n", 3).execute(), 3, 6);
        assertPreparedStatementsCountsAndId(testSession, 3, testSelect3, 3, 1);
        assertTestPreparedStatementsResult(testSelect4.bind("m", 6).execute(), 2, 6);
        assertPreparedStatementsCountsAndId(testSession, 4, testSelect4, 4, 1);
        assertPreparedStatementsStatusCounts(testSession, 4, 4, 0);
        // D. Set binds reuse statement: 3rd execute -> execute.
        assertTestPreparedStatementsResult(testSelect1.execute(), 1, 8);
        assertPreparedStatementsCountsAndId(testSession, 4, testSelect1, 1, 2);
        assertTestPreparedStatementsResult(testSelect2.bind("n", 4).execute(), 4, 8);
        assertPreparedStatementsCountsAndId(testSession, 4, testSelect2, 2, 2);
        assertTestPreparedStatementsResult(testSelect3.bind("n", 4).execute(), 4, 7);
        assertPreparedStatementsCountsAndId(testSession, 4, testSelect3, 3, 2);
        assertTestPreparedStatementsResult(testSelect4.bind("n", 3).bind("m", 7).execute(), 3, 7);
        assertPreparedStatementsCountsAndId(testSession, 4, testSelect4, 4, 2);
        assertPreparedStatementsStatusCounts(testSession, 4, 8, 0);
        // E. Set where deallocates and resets execution count: 1st execute -> deallocate + non-prepared.
        assertTestPreparedStatementsResult(testSelect1.where("true").execute(), 1, 8);
        assertPreparedStatementsCountsAndId(testSession, 3, testSelect1, 0, -1);
        assertTestPreparedStatementsResult(testSelect2.where("true AND ord >= :n").bind("n", 4).execute(), 4, 8);
        assertPreparedStatementsCountsAndId(testSession, 2, testSelect2, 0, -1);
        assertTestPreparedStatementsResult(testSelect3.where("true AND ord >= :n AND ord <= :n + 3").bind("n", 4).execute(), 4, 7);
        assertPreparedStatementsCountsAndId(testSession, 1, testSelect3, 0, -1);
        assertTestPreparedStatementsResult(testSelect4.where("true AND ord >= :n AND ord <= :m").bind("n", 3).bind("m", 7).execute(), 3, 7);
        assertPreparedStatementsCountsAndId(testSession, 0, testSelect4, 0, -1);
        assertPreparedStatementsStatusCounts(testSession, 4, 8, 4);
        // F. No Changes: 2nd execute -> prepare + execute.
        assertTestPreparedStatementsResult(testSelect1.execute(), 1, 8);
        assertPreparedStatementsCountsAndId(testSession, 1, testSelect1, 1, 1);
        assertTestPreparedStatementsResult(testSelect2.bind("n", 4).execute(), 4, 8);
        assertPreparedStatementsCountsAndId(testSession, 2, testSelect2, 2, 1);
        assertTestPreparedStatementsResult(testSelect3.bind("n", 4).execute(), 4, 7);
        assertPreparedStatementsCountsAndId(testSession, 3, testSelect3, 3, 1);
        assertTestPreparedStatementsResult(testSelect4.bind("n", 3).bind("m", 7).execute(), 3, 7);
        assertPreparedStatementsCountsAndId(testSession, 4, testSelect4, 4, 1);
        assertPreparedStatementsStatusCounts(testSession, 8, 12, 4);
        // G. Set limit for the first time deallocates and re-prepares: 1st execute -> re-prepare + execute.
        assertTestPreparedStatementsResult(testSelect1.limit(2).execute(), 1, 2);
        assertPreparedStatementsCountsAndId(testSession, 4, testSelect1, 1, 1);
        assertTestPreparedStatementsResult(testSelect2.limit(2).execute(), 4, 5);
        assertPreparedStatementsCountsAndId(testSession, 4, testSelect2, 2, 1);
        assertTestPreparedStatementsResult(testSelect3.limit(2).execute(), 4, 5);
        assertPreparedStatementsCountsAndId(testSession, 4, testSelect3, 3, 1);
        assertTestPreparedStatementsResult(testSelect4.limit(2).execute(), 3, 4);
        assertPreparedStatementsCountsAndId(testSession, 4, testSelect4, 4, 1);
        assertPreparedStatementsStatusCounts(testSession, 12, 16, 8);
        // H. Set limit and offset reuse prepared statement: 2nd execute -> execute.
        assertTestPreparedStatementsResult(testSelect1.limit(1).offset(1).execute(), 2, 2);
        assertPreparedStatementsCountsAndId(testSession, 4, testSelect1, 1, 2);
        assertTestPreparedStatementsResult(testSelect2.limit(1).offset(1).execute(), 5, 5);
        assertPreparedStatementsCountsAndId(testSession, 4, testSelect2, 2, 2);
        assertTestPreparedStatementsResult(testSelect3.limit(1).offset(1).execute(), 5, 5);
        assertPreparedStatementsCountsAndId(testSession, 4, testSelect3, 3, 2);
        assertTestPreparedStatementsResult(testSelect4.limit(1).offset(1).execute(), 4, 4);
        assertPreparedStatementsCountsAndId(testSession, 4, testSelect4, 4, 2);
        assertPreparedStatementsStatusCounts(testSession, 12, 20, 8);
        // I. Set orderBy deallocates and resets execution count, set limit and bind has no effect: 1st execute -> deallocate + non-prepared.
        assertTestPreparedStatementsResult(testSelect1.orderBy("id").limit(2).execute(), 2, 3);
        assertPreparedStatementsCountsAndId(testSession, 3, testSelect1, 0, -1);
        assertTestPreparedStatementsResult(testSelect2.orderBy("id").limit(2).bind("n", 4).execute(), 5, 6);
        assertPreparedStatementsCountsAndId(testSession, 2, testSelect2, 0, -1);
        assertTestPreparedStatementsResult(testSelect3.orderBy("id").limit(2).bind("n", 4).execute(), 5, 6);
        assertPreparedStatementsCountsAndId(testSession, 1, testSelect3, 0, -1);
        assertTestPreparedStatementsResult(testSelect4.orderBy("id").limit(2).bind("m", 7).execute(), 4, 5);
        assertPreparedStatementsCountsAndId(testSession, 0, testSelect4, 0, -1);
        assertPreparedStatementsStatusCounts(testSession, 12, 20, 12);
        // J. Set offset reuse statement: 2nd execute -> prepare + execute.
        assertTestPreparedStatementsResult(testSelect1.offset(0).execute(), 1, 2);
        assertPreparedStatementsCountsAndId(testSession, 1, testSelect1, 1, 1);
        assertTestPreparedStatementsResult(testSelect2.offset(0).execute(), 4, 5);
        assertPreparedStatementsCountsAndId(testSession, 2, testSelect2, 2, 1);
        assertTestPreparedStatementsResult(testSelect3.offset(0).execute(), 4, 5);
        assertPreparedStatementsCountsAndId(testSession, 3, testSelect3, 3, 1);
        assertTestPreparedStatementsResult(testSelect4.offset(0).execute(), 3, 4);
        assertPreparedStatementsCountsAndId(testSession, 4, testSelect4, 4, 1);
        assertPreparedStatementsStatusCounts(testSession, 16, 24, 12);
        testSession.close();
        // Prepared statements won't live past the closing of the session.
        assertPreparedStatementsCount(sessionThreadId, 0, 10);
        /*
             * Test falling back onto non-prepared statements.
             */
        testSession = sf.getSession(this.testProperties);
        int origMaxPrepStmtCount = this.session.sql("SELECT @@max_prepared_stmt_count").execute().fetchOne().getInt(0);
        try {
            // Allow preparing only one more statement.
            this.session.sql("SET GLOBAL max_prepared_stmt_count = ?").bind(getPreparedStatementsCount() + 1).execute();
            sessionThreadId = getThreadId(testSession);
            assertPreparedStatementsCount(sessionThreadId, 0, 1);
            assertPreparedStatementsStatusCounts(testSession, 0, 0, 0);
            testTbl = testSession.getDefaultSchema().getTable("testPrepareSelect");
            testSelect1 = testTbl.select("ord");
            testSelect2 = testTbl.select("ord");
            // 1st execute -> don't prepare.
            assertTestPreparedStatementsResult(testSelect1.execute(), 1, 8);
            assertPreparedStatementsCountsAndId(testSession, 0, testSelect1, 0, -1);
            assertTestPreparedStatementsResult(testSelect2.execute(), 1, 8);
            assertPreparedStatementsCountsAndId(testSession, 0, testSelect2, 0, -1);
            assertPreparedStatementsStatusCounts(testSession, 0, 0, 0);
            // 2nd execute -> prepare + execute.
            assertTestPreparedStatementsResult(testSelect1.execute(), 1, 8);
            assertPreparedStatementsCountsAndId(testSession, 1, testSelect1, 1, 1);
            // Fails preparing, execute as non-prepared.
            assertTestPreparedStatementsResult(testSelect2.execute(), 1, 8);
            assertPreparedStatementsCountsAndId(testSession, 1, testSelect2, 0, -1);
            // Failed prepare also counts.
            assertPreparedStatementsStatusCounts(testSession, 2, 1, 0);
            // 3rd execute -> execute.
            assertTestPreparedStatementsResult(testSelect1.execute(), 1, 8);
            assertPreparedStatementsCountsAndId(testSession, 1, testSelect1, 1, 2);
            // Execute as non-prepared.
            assertTestPreparedStatementsResult(testSelect2.execute(), 1, 8);
            assertPreparedStatementsCountsAndId(testSession, 1, testSelect2, 0, -1);
            assertPreparedStatementsStatusCounts(testSession, 2, 2, 0);
            testSession.close();
            // Prepared statements won't live past the closing of the session.
            assertPreparedStatementsCount(sessionThreadId, 0, 10);
        } finally {
            this.session.sql("SET GLOBAL max_prepared_stmt_count = ?").bind(origMaxPrepStmtCount).execute();
        }
    } finally {
        sqlUpdate("DROP TABLE IF EXISTS testPrepareSelect");
    }
}

Example 4 with SelectStatement

use of com.mysql.cj.xdevapi.SelectStatement 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");
    }
}