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

Example 11 with SessionFactory

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

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

the class CollectionTest method testArrayIndex014.

@Test
public void testArrayIndex014() throws Exception {
    assumeTrue(mysqlVersionMeetsMinimum(this.baseUrl, ServerVersion.parseVersion("8.0.17")), "MySQL 8.0.17+ is required to run this test.");
    String collname = "coll1";
    DbDoc doc = null;
    Session sess = null;
    try {
        sess = new SessionFactory().getSession(this.baseUrl);
        Schema sch = sess.getDefaultSchema();
        sch.dropCollection(collname);
        Collection coll = sch.createCollection(collname, true);
        /* create basic index */
        coll.createIndex("intArrayIndex", "{\"fields\": [{\"field\": \"$.intField\", \"type\": \"SIGNED INTEGER\", \"array\": true}]}");
        coll.createIndex("uintArrayIndex", "{\"fields\": [{\"field\": \"$.uintField\", \"type\": \"UNSIGNED INTEGER\", \"array\": true}]}");
        coll.createIndex("floatArrayIndex", "{\"fields\": [{\"field\": \"$.floatField\", \"type\": \"DECIMAL(10,2)\", \"array\": true}]}");
        coll.createIndex("dateArrayIndex", "{\"fields\": [{\"field\": \"$.dateField\", \"type\": \"DATE\", \"array\": true}]}");
        coll.createIndex("datetimeArrayIndex", "{\"fields\": [{\"field\": \"$.datetimeField\", \"type\": \"DATETIME\", \"array\": true}]}");
        coll.createIndex("timeArrayIndex", "{\"fields\": [{\"field\": \"$.timeField\", \"type\": \"TIME\", \"array\": true}]}");
        coll.createIndex("charArrayIndex", "{\"fields\": [{\"field\": \"$.charField\", \"type\": \"CHAR(256)\", \"array\": true}]}");
        coll.createIndex("binaryArrayIndex", "{\"fields\": [{\"field\": \"$.binaryField\", \"type\": \"BINARY(256)\", \"array\": true}]}");
        validateArrayIndex("intArrayIndex", "coll1", 1);
        validateArrayIndex("uintArrayIndex", "coll1", 1);
        validateArrayIndex("floatArrayIndex", "coll1", 1);
        validateArrayIndex("dateArrayIndex", "coll1", 1);
        validateArrayIndex("datetimeArrayIndex", "coll1", 1);
        validateArrayIndex("timeArrayIndex", "coll1", 1);
        validateArrayIndex("charArrayIndex", "coll1", 1);
        validateArrayIndex("binaryArrayIndex", "coll1", 1);
        coll.remove("true").execute();
        coll.add("{\"intField\" : [1,2,3], \"uintField\" : [51,52,53], \"dateField\" : [\"2019-1-1\", \"2019-2-1\", \"2019-3-1\"], \"datetimeField\" : [\"9999-12-30 23:59:59\", \"9999-12-31 23:59:59\", \"9999-12-31 23:59:59\"], \"charField\" : [\"abcd1\", \"abcd1\", \"abcd2\", \"abcd4\"], \"binaryField\" : [\"abcd1\", \"abcd1\", \"abcd2\", \"abcd4\"],\"timeField\" : [\"10.30\", \"11.30\", \"12.30\"], \"floatField\" : [51.2,52.4,53.6],\"dateFieldWOI\" : \"2019-1-1\"}").execute();
        coll.add("{\"intField\" : [11,12,3], \"uintField\" : [51,52,53], \"dateField\" : [\"2019-1-1\", \"2019-2-1\", \"2019-3-1\"], \"datetimeField\" : [\"9999-12-30 23:59:59\", \"9999-12-29 23:59:59\", \"9999-12-31 23:59:59\"], \"charField\" : [\"abcd1\", \"abcd1\", \"abcd2\", \"abcd4\"], \"binaryField\" : [\"abcd1\", \"abcd1\", \"abcd2\", \"abcd4\"], \"timeField\" : [\"10.30\", \"11.30\", \"12.30\"], \"floatField\" : [51.1,52.9,53.0],\"dateFieldWOI\" : \"2019-1-1\"}").execute();
        coll.add("{\"intField\" : [12,23,34], \"uintField\" : [51,52,53], \"dateField\" : [\"2019-1-1\", \"2019-2-1\", \"2019-3-1\"], \"datetimeField\" : [\"9999-12-31 23:59:59\", \"9999-12-31 23:59:59\", \"9999-12-7 23:59:59\"], \"charField\" : [\"abcd1\", \"abcd1\", \"abcd2\", \"abcd4\"], \"binaryField\" : [\"abcd1\", \"abcd1\", \"abcd2\", \"abcd4\"], \"timeField\" : [\"10.30\", \"7.30\", \"12.30\"], \"floatField\" : [51.2,52.7,53.6],\"dateFieldWOI\" : \"2019-2-1\"}").execute();
        try {
            coll.add("{\"intField\" : \"[1,2,3]\", \"uintField\" : [51,52,53], \"dateField\" : [\"2019-1-1\", \"2019-2-1\", \"2019-3-1\"], \"datetimeField\" : [\"9999-12-30 23:59:59\", \"9999-12-31 23:59:59\", \"9999-12-31 23:59:59\"], \"charField\" : [\"abcd1\", \"abcd1\", \"abcd2\", \"abcd4\"], \"binaryField\" : [\"abcd1\", \"abcd1\", \"abcd2\", \"abcd4\"],\"timeField\" : [\"10.30\", \"11.30\", \"12.30\"], \"floatField\" : [51.2,52.4,53.6]}").execute();
            assertTrue(false);
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("functional index"));
        }
        DocResult docs = coll.find(":intField in $.intField").bind("intField", 12).execute();
        doc = null;
        int i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            i++;
        }
        assertTrue(i == 2);
        docs = coll.find(":uintField in $.uintField").bind("uintField", 52).execute();
        doc = null;
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            i++;
        }
        assertTrue(i == 3);
        docs = coll.find(":charField in $.charField").bind("charField", "abcd1").execute();
        doc = null;
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            i++;
        }
        assertTrue(i == 3);
        docs = coll.find(":binaryField in $.binaryField").bind("binaryField", "abcd1").execute();
        doc = null;
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            i++;
        }
        assertTrue(i == 3);
        docs = coll.find(":floatField in $.floatField").bind("floatField", 51.2).execute();
        doc = null;
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            i++;
        }
        System.out.println("Count = " + i);
        assertTrue(i == 2);
        docs = coll.find("CAST(CAST('2019-2-1' as DATE) as JSON) in $.dateField").execute();
        doc = null;
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            i++;
        }
        System.out.println("Count = " + i);
        assertTrue(i == 3);
        docs = coll.find("CAST(CAST('2019-2-1' as DATE) as JSON) not in $.dateField").execute();
        doc = null;
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            i++;
        }
        System.out.println("Using NOT IN");
        System.out.println("Count = " + i);
        // assertTrue(i == 0);
        docs = coll.find("'2019-1-1' not in $.dateFieldWOI").execute();
        doc = null;
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            System.out.println((((JsonString) doc.get("dateFieldWOI")).getString()));
            i++;
        }
        System.out.println("Using NOT IN Without Index");
        System.out.println("Count = " + i);
        docs = coll.find("CAST(CAST('2019-2-1' as DATE) as JSON) overlaps $.dateField").execute();
        doc = null;
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            i++;
        }
        System.out.println("Count = " + i);
        assertTrue(i == 3);
        docs = coll.find("CAST(CAST('2019-2-1' as DATE) as JSON) not overlaps $.dateField").execute();
        doc = null;
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            i++;
        }
        System.out.println("Using NOT OVERLAPS");
        System.out.println("Count = " + i);
        // assertTrue(i == 0);
        docs = coll.find("CAST(CAST(:datetimeField as DATETIME) as JSON) in $.datetimeField").bind("datetimeField", "9999-12-30 23:59:59").execute();
        doc = null;
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            i++;
        }
        System.out.println("Count = " + i);
        assertTrue(i == 2);
        docs = coll.find("CAST(CAST(:timeField as TIME) as JSON) in $.timeField").bind("timeField", "7.30").execute();
        doc = null;
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            i++;
        }
        System.out.println("Count = " + i);
        assertTrue(i == 1);
        // Integration scenarios between Index and Overlaps. Explicit casting added due to server Bug#29752056. NOT IN and NOT OVERLAPS doesn't require explicit casting
        docs = coll.find("CAST(CAST('2019-2-1' as DATE) as JSON) in $.dateField").execute();
        // System.out.println("Number of rows using IN with indexed array = "+docs.count());
        assertTrue(docs.count() == 3);
        docs = coll.find("'2019-2-1' not in $.dateField").execute();
        // System.out.println("Number of rows using NOT IN without casting = "+docs.count());
        assertTrue(docs.count() == 0);
        docs = coll.find("CAST(CAST('2019-2-1' as DATE) as JSON) overlaps $.dateField").execute();
        // System.out.println("Number of rows using OVERLAPS with indexed array = "+docs.count());
        assertTrue(docs.count() == 3);
        docs = coll.find("'2019-2-1' not overlaps $.dateField").execute();
        // System.out.println("Number of rows using NOT OVERLAPS without casting = "+docs.count());
        assertTrue(docs.count() == 0);
        // Integration scenarios for time
        docs = coll.find("CAST(CAST(:timeField as TIME) as JSON) in $.timeField").bind("timeField", "7.30").execute();
        assertTrue(docs.count() == 1);
        docs = coll.find(":timeField not in $.timeField").bind("timeField", "7.30").execute();
        assertTrue(docs.count() == 2);
        docs = coll.find("CAST(CAST(:timeField as TIME) as JSON) overlaps $.timeField").bind("timeField", "7.30").execute();
        assertTrue(docs.count() == 1);
        docs = coll.find(":timeField not overlaps $.timeField").bind("timeField", "7.30").execute();
        assertTrue(docs.count() == 2);
        // Integration scenarios for datetime
        docs = coll.find("CAST(CAST(:datetimeField as DATETIME) as JSON) in $.datetimeField").bind("datetimeField", "9999-12-30 23:59:59").execute();
        assertTrue(docs.count() == 2);
        docs = coll.find(":datetimeField NOT IN $.datetimeField").bind("datetimeField", "9999-12-30 23:59:59").execute();
        assertTrue(docs.count() == 1);
        docs = coll.find("CAST(CAST(:datetimeField as DATETIME) as JSON) OVERLAPS $.datetimeField").bind("datetimeField", "9999-12-30 23:59:59").execute();
        assertTrue(docs.count() == 2);
        docs = coll.find(":datetimeField NOT OVERLAPS $.datetimeField").bind("datetimeField", "9999-12-30 23:59:59").execute();
        assertTrue(docs.count() == 1);
        // Integration scenaris of Integer
        docs = coll.find(":intField not in $.intField").bind("intField", 12).execute();
        assertTrue(docs.count() == 1);
        docs = coll.find(":intField overlaps $.intField").bind("intField", 12).execute();
        assertTrue(docs.count() == 2);
        docs = coll.find(":intField not overlaps $.intField").bind("intField", 12).execute();
        assertTrue(docs.count() == 1);
        // Integration scenaris of unsigned integer
        docs = coll.find(":uintField not in $.uintField").bind("uintField", 52).execute();
        assertTrue(docs.count() == 0);
        docs = coll.find(":uintField overlaps $.uintField").bind("uintField", 52).execute();
        assertTrue(docs.count() == 3);
        docs = coll.find(":uintField not overlaps $.uintField").bind("uintField", 52).execute();
        assertTrue(docs.count() == 0);
        // Integration scenaris of character type
        docs = coll.find(":charField not in $.charField").bind("charField", "abcd1").execute();
        assertTrue(docs.count() == 0);
        docs = coll.find(":charField overlaps $.charField").bind("charField", "abcd1").execute();
        assertTrue(docs.count() == 3);
        docs = coll.find(":charField not overlaps $.charField").bind("charField", "abcd1").execute();
        assertTrue(docs.count() == 0);
        // Integration scenarios of binary type
        docs = coll.find(":binaryField not in $.binaryField").bind("binaryField", "abcd1").execute();
        assertTrue(docs.count() == 0);
        docs = coll.find(":binaryField overlaps $.binaryField").bind("binaryField", "abcd1").execute();
        assertTrue(docs.count() == 3);
        docs = coll.find(":binaryField not overlaps $.binaryField").bind("binaryField", "abcd1").execute();
        assertTrue(docs.count() == 0);
        sch.dropCollection(collname);
    } finally {
        if (sess != null) {
            sess.close();
            sess = null;
        }
    }
}

Example 13 with SessionFactory

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

the class CollectionTest method testArrayIndex012.

/**
 * START testArrayIndexBasic tests
 *
 * @throws Exception
 */
@Test
public void testArrayIndex012() throws Exception {
    System.out.println("testCreateIndexSanity");
    String collname = "coll1";
    Session sess = null;
    try {
        sess = new SessionFactory().getSession(this.baseUrl);
        Schema sch = sess.getDefaultSchema();
        sch.dropCollection(collname);
        Collection coll = sch.createCollection(collname, true);
        try {
            coll.createIndex("intArrayIndex", "{\"fields\": [{\"field\": \"$.intField\", \"type\": \"SIGNED INTEGER\", \"array\": \"\"}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        try {
            coll.createIndex("uintArrayIndex", "{\"fields\": [{\"field\": \"$.uintField\", \"type\": \"UNSIGNED INTEGER\", \"array\": \"\"}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        try {
            coll.createIndex("floatArrayIndex", "{\"fields\": [{\"field\": \"$.floatField\", \"type\": \"DECIMAL(10,2)\", \"array\": \"\"}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        try {
            coll.createIndex("dateArrayIndex", "{\"fields\": [{\"field\": \"$.dateField\", \"type\": \"DATE\", \"array\": \"\"}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        try {
            coll.createIndex("datetimeArrayIndex", "{\"fields\": [{\"field\": \"$.datetimeField\", \"type\": \"DATETIME\", \"array\": \"\"}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        try {
            coll.createIndex("timeArrayIndex", "{\"fields\": [{\"field\": \"$.timeField\", \"type\": \"TIME\", \"array\": \"\"}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        try {
            coll.createIndex("charArrayIndex", "{\"fields\": [{\"field\": \"$.charField\", \"type\": \"CHAR(256)\", \"array\": \"\"}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        try {
            coll.createIndex("binaryArrayIndex", "{\"fields\": [{\"field\": \"$.binaryField\", \"type\": \"BINARY(256)\", \"array\": \"\"}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        try {
            coll.createIndex("intArrayIndex", "{\"fields\": [{\"field\": \"$.intField\", \"type\": \"SIGNED INTEGER\", \"array\": null}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        try {
            coll.createIndex("uintArrayIndex", "{\"fields\": [{\"field\": \"$.uintField\", \"type\": \"UNSIGNED INTEGER\", \"array\": null}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        try {
            coll.createIndex("floatArrayIndex", "{\"fields\": [{\"field\": \"$.floatField\", \"type\": \"DECIMAL(10,2)\", \"array\": null}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        try {
            coll.createIndex("dateArrayIndex", "{\"fields\": [{\"field\": \"$.dateField\", \"type\": \"DATE\", \"array\": null}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        try {
            coll.createIndex("datetimeArrayIndex", "{\"fields\": [{\"field\": \"$.datetimeField\", \"type\": \"DATETIME\", \"array\": null}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        try {
            coll.createIndex("timeArrayIndex", "{\"fields\": [{\"field\": \"$.timeField\", \"type\": \"TIME\", \"array\": null}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        try {
            coll.createIndex("charArrayIndex", "{\"fields\": [{\"field\": \"$.charField\", \"type\": \"CHAR(256)\", \"array\": null}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        try {
            coll.createIndex("binaryArrayIndex", "{\"fields\": [{\"field\": \"$.binaryField\", \"type\": \"BINARY(256)\", \"array\": null}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        try {
            coll.createIndex("intArrayIndex", "{\"fields\": [{\"field\": \"$.intField\", \"type\": \"SIGNED INTEGER\", \"array\": []}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        try {
            coll.createIndex("uintArrayIndex", "{\"fields\": [{\"field\": \"$.uintField\", \"type\": \"UNSIGNED INTEGER\", \"array\": []}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        try {
            coll.createIndex("floatArrayIndex", "{\"fields\": [{\"field\": \"$.floatField\", \"type\": \"DECIMAL(10,2)\", \"array\": []}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        try {
            coll.createIndex("dateArrayIndex", "{\"fields\": [{\"field\": \"$.dateField\", \"type\": \"DATE\", \"array\": []}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        try {
            coll.createIndex("datetimeArrayIndex", "{\"fields\": [{\"field\": \"$.datetimeField\", \"type\": \"DATETIME\", \"array\": []}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        try {
            coll.createIndex("timeArrayIndex", "{\"fields\": [{\"field\": \"$.timeField\", \"type\": \"TIME\", \"array\": []}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        try {
            coll.createIndex("charArrayIndex", "{\"fields\": [{\"field\": \"$.charField\", \"type\": \"CHAR(256)\", \"array\": []}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        try {
            coll.createIndex("binaryArrayIndex", "{\"fields\": [{\"field\": \"$.binaryField\", \"type\": \"BINARY(256)\", \"array\": []}]}");
        } catch (Exception e) {
            System.out.println("ERROR : " + e.getMessage());
            assertTrue(e.getMessage().contains("Index field 'array' member must be boolean."));
        }
        sch.dropCollection(collname);
    } finally {
        if (sess != null) {
            sess.close();
            sess = null;
        }
    }
}

Example 14 with SessionFactory

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

the class SessionTest method urlWithDefaultSchema.

@Test
public void urlWithDefaultSchema() {
    try {
        // Create user with mysql_native_password authentication plugin as it can be used with any of the authentication mechanisms.
        this.session.sql("CREATE USER IF NOT EXISTS 'testUserN'@'%' IDENTIFIED WITH mysql_native_password BY 'testUserN'").execute();
        this.session.sql("GRANT SELECT ON *.* TO 'testUserN'@'%'").execute();
        final String testSchemaName = getRandomTestSchemaName();
        this.session.createSchema(testSchemaName);
        final SessionFactory testSessionFactory = new SessionFactory();
        final String testUriPattern = "mysqlx://testUserN:testUserN@%s:%s/%s?xdevapi.auth=%s";
        // Check if the default schema is correctly sent when using different authentication mechanisms.
        String[] authMechs = mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.4")) ? new String[] { "PLAIN", "MYSQL41", "SHA256_MEMORY" } : new String[] { "PLAIN", "MYSQL41" };
        for (String authMech : authMechs) {
            final String testCase = "Testing default schema provided in authentication mecanism '" + authMech + "'.";
            // Test using a connection String.
            final String testUri = String.format(testUriPattern, getTestHost(), getTestPort(), testSchemaName, authMech);
            Session testSession = testSessionFactory.getSession(testUri);
            assertTrue(testSession.getUri().contains("/" + testSchemaName + "?"), testCase);
            assertEquals(testSchemaName, testSession.getDefaultSchemaName(), testCase);
            assertNotNull(testSession.getDefaultSchema(), testCase);
            assertEquals(testSchemaName, testSession.getDefaultSchema().getName(), testCase);
            assertEquals(testSchemaName, testSession.sql("SELECT database()").execute().fetchOne().getString(0), testCase);
            testSession.close();
            // Test using a properties map.
            final Properties testProps = new Properties();
            testProps.setProperty(PropertyKey.USER.getKeyName(), "testUserN");
            testProps.setProperty(PropertyKey.PASSWORD.getKeyName(), "testUserN");
            testProps.setProperty(PropertyKey.HOST.getKeyName(), getTestHost());
            testProps.setProperty(PropertyKey.PORT.getKeyName(), String.valueOf(getTestPort()));
            testProps.setProperty(PropertyKey.DBNAME.getKeyName(), testSchemaName);
            testProps.setProperty(PropertyKey.xdevapiAuth.getKeyName(), authMech);
            testSession = testSessionFactory.getSession(testProps);
            assertTrue(testSession.getUri().contains("/" + testSchemaName + "?"), testCase);
            assertEquals(testSchemaName, testSession.getDefaultSchemaName(), testCase);
            assertNotNull(testSession.getDefaultSchema(), testCase);
            assertEquals(testSchemaName, testSession.getDefaultSchema().getName(), testCase);
            assertEquals(testSchemaName, testSession.sql("SELECT database()").execute().fetchOne().getString(0), testCase);
            testSession.close();
        }
    } finally {
        this.session.sql("DROP USER IF EXISTS testUserN").execute();
    }
}

Example 15 with SessionFactory

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