Examples with ServerPreparedStatement com.mysql.cj.jdbc.ServerPreparedStatement used on opensource projects

Example 6 with ServerPreparedStatement

use of com.mysql.cj.jdbc.ServerPreparedStatement in project ABC by RuiPinto96274.

the class StatementRegressionTest method testBug66430.

/**
 * Tests fix for Bug#66430 - setCatalog on connection leaves ServerPreparedStatement cache for old catalog.
 *
 * @throws Exception
 */
@Test
public void testBug66430() throws Exception {
    createDatabase("testBug66430DB1");
    createTable("testBug66430DB1.testBug66430", "(id INT)");
    this.stmt.executeUpdate("INSERT INTO testBug66430DB1.testBug66430 VALUES (1)");
    createDatabase("testBug66430DB2");
    createTable("testBug66430DB2.testBug66430", "(id INT)");
    this.stmt.executeUpdate("INSERT INTO testBug66430DB2.testBug66430 VALUES (2)");
    boolean useSPS = false;
    boolean cachePS = false;
    do {
        final String testCase = String.format("Case: [useSPS: %s, cachePS: %s ]", useSPS ? "Y" : "N", cachePS ? "Y" : "N");
        Properties props = new Properties();
        props.setProperty(PropertyKey.useSSL.getKeyName(), "false");
        props.setProperty(PropertyKey.allowPublicKeyRetrieval.getKeyName(), "true");
        props.setProperty(PropertyKey.cachePrepStmts.getKeyName(), Boolean.toString(cachePS));
        props.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), Boolean.toString(useSPS));
        Connection testConn = getConnectionWithProps(props);
        if (((JdbcConnection) testConn).getPropertySet().<DatabaseTerm>getEnumProperty(PropertyKey.databaseTerm).getValue() == DatabaseTerm.SCHEMA) {
            testConn.setSchema("testBug66430DB1");
        } else {
            testConn.setCatalog("testBug66430DB1");
        }
        PreparedStatement testPStmt = testConn.prepareStatement("SELECT * FROM testBug66430 WHERE id > ?");
        testPStmt.setInt(1, 0);
        this.rs = testPStmt.executeQuery();
        assertTrue(this.rs.next(), testCase);
        assertEquals(1, this.rs.getInt(1), testCase);
        assertFalse(this.rs.next(), testCase);
        testPStmt.close();
        if (((JdbcConnection) testConn).getPropertySet().<DatabaseTerm>getEnumProperty(PropertyKey.databaseTerm).getValue() == DatabaseTerm.SCHEMA) {
            testConn.setSchema("testBug66430DB2");
        } else {
            testConn.setCatalog("testBug66430DB2");
        }
        testPStmt = testConn.prepareStatement("SELECT * FROM testBug66430 WHERE id > ?");
        testPStmt.setInt(1, 0);
        this.rs = testPStmt.executeQuery();
        assertTrue(this.rs.next(), testCase);
        assertEquals(2, this.rs.getInt(1), testCase);
        assertFalse(this.rs.next(), testCase);
        testPStmt.close();
        // Do it again to make sure cached prepared statements behave correctly.
        if (((JdbcConnection) testConn).getPropertySet().<DatabaseTerm>getEnumProperty(PropertyKey.databaseTerm).getValue() == DatabaseTerm.SCHEMA) {
            testConn.setSchema("testBug66430DB1");
        } else {
            testConn.setCatalog("testBug66430DB1");
        }
        testPStmt = testConn.prepareStatement("SELECT * FROM testBug66430 WHERE id > ?");
        testPStmt.setInt(1, 0);
        this.rs = testPStmt.executeQuery();
        assertTrue(this.rs.next(), testCase);
        assertEquals(1, this.rs.getInt(1), testCase);
        assertFalse(this.rs.next(), testCase);
        testPStmt.close();
        if (((JdbcConnection) testConn).getPropertySet().<DatabaseTerm>getEnumProperty(PropertyKey.databaseTerm).getValue() == DatabaseTerm.SCHEMA) {
            testConn.setSchema("testBug66430DB2");
        } else {
            testConn.setCatalog("testBug66430DB2");
        }
        testPStmt = testConn.prepareStatement("SELECT * FROM testBug66430 WHERE id > ?");
        testPStmt.setInt(1, 0);
        this.rs = testPStmt.executeQuery();
        assertTrue(this.rs.next(), testCase);
        assertEquals(2, this.rs.getInt(1), testCase);
        assertFalse(this.rs.next(), testCase);
        testPStmt.close();
        testConn.close();
    } while ((useSPS = !useSPS) || (cachePS = !cachePS));
}

Example 7 with ServerPreparedStatement

use of com.mysql.cj.jdbc.ServerPreparedStatement in project ABC by RuiPinto96274.

the class StatementRegressionTest method testBug87429.

/**
 * Tests fix for Bug#87429 - repeated close of ServerPreparedStatement causes memory leak.
 *
 * Original de-cache on double close() behavior modified by:
 * WL#11101 - Remove de-cache and close of SSPSs on double call to close().
 *
 * @throws Exception
 */
@Test
public void testBug87429() throws Exception {
    Field stmtsCacheField = ConnectionImpl.class.getDeclaredField("serverSideStatementCache");
    stmtsCacheField.setAccessible(true);
    ToIntFunction<Connection> getStmtsCacheSize = (c) -> {
        try {
            LRUCache<?, ?> stmtsCacheObj = (LRUCache<?, ?>) stmtsCacheField.get(c);
            return stmtsCacheObj == null ? -1 : stmtsCacheObj.size();
        } catch (IllegalArgumentException | IllegalAccessException e) {
            fail("Fail getting the statemets cache size.");
            return -1;
        }
    };
    final String sql1 = "SELECT 1, ?";
    final String sql2 = "SELECT 2, ?";
    boolean useSPS = false;
    boolean cachePS = false;
    do {
        Properties props = new Properties();
        props.setProperty(PropertyKey.useSSL.getKeyName(), "false");
        props.setProperty(PropertyKey.allowPublicKeyRetrieval.getKeyName(), "true");
        props.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), Boolean.toString(useSPS));
        props.setProperty(PropertyKey.cachePrepStmts.getKeyName(), Boolean.toString(cachePS));
        props.setProperty(PropertyKey.prepStmtCacheSize.getKeyName(), "5");
        boolean cachedSPS = useSPS && cachePS;
        JdbcConnection testConn = (JdbcConnection) getConnectionWithProps(props);
        // Single PreparedStatement, closed multiple times.
        for (int i = 0; i < 100; i++) {
            this.pstmt = testConn.prepareStatement(sql1);
            assertTrue(this.pstmt.isPoolable());
            assertEquals(1, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
            // Close & cache.
            this.pstmt.close();
            assertEquals(cachedSPS ? 1 : 0, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 1 : -1, getStmtsCacheSize.applyAsInt(testConn));
            // No-op.
            this.pstmt.close();
            assertEquals(cachedSPS ? 1 : 0, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 1 : -1, getStmtsCacheSize.applyAsInt(testConn));
            // No-op.
            this.pstmt.close();
            assertEquals(cachedSPS ? 1 : 0, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 1 : -1, getStmtsCacheSize.applyAsInt(testConn));
            try {
                // De-caches the statement or no-op if not cached.
                this.pstmt.setPoolable(false);
            } catch (SQLException e) {
                assertFalse(cachedSPS, "Exception [" + e.getMessage() + "] not expected.");
            }
            // No-op.
            this.pstmt.close();
            assertEquals(0, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
        }
        testConn.close();
        assertEquals(0, testConn.getActiveStatementCount());
        assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
        testConn = (JdbcConnection) getConnectionWithProps(props);
        // Multiple PreparedStatements interchanged, two queries, closed multiple times.
        for (int i = 0; i < 100; i++) {
            for (int j = 1; j <= 4; j++) {
                PreparedStatement pstmt1 = testConn.prepareStatement(j == 1 ? sql2 : sql1);
                PreparedStatement pstmt2 = testConn.prepareStatement(j == 2 ? sql2 : sql1);
                PreparedStatement pstmt3 = testConn.prepareStatement(j == 3 ? sql2 : sql1);
                PreparedStatement pstmt4 = testConn.prepareStatement(j == 4 ? sql2 : sql1);
                assertEquals(4, testConn.getActiveStatementCount());
                // Close and cache statements successively.
                pstmt4.close();
                // No-op.
                pstmt4.close();
                assertEquals(cachedSPS ? 4 : 3, testConn.getActiveStatementCount());
                pstmt3.close();
                // No-op.
                pstmt3.close();
                assertEquals(cachedSPS ? (j >= 3 ? 4 : 3) : 2, testConn.getActiveStatementCount());
                pstmt2.close();
                // No-op.
                pstmt2.close();
                assertEquals(cachedSPS ? (j >= 2 ? 3 : 2) : 1, testConn.getActiveStatementCount());
                pstmt1.close();
                // No-op.
                pstmt1.close();
                assertEquals(cachedSPS ? 2 : 0, testConn.getActiveStatementCount());
                // No-ops.
                pstmt4.close();
                pstmt4.close();
                pstmt3.close();
                pstmt3.close();
                pstmt2.close();
                pstmt2.close();
                pstmt1.close();
                pstmt1.close();
                assertEquals(cachedSPS ? 2 : 0, testConn.getActiveStatementCount());
                // De-cache statements successively.
                try {
                    // De-caches the statement or no-op if not cached.
                    pstmt4.setPoolable(false);
                } catch (SQLException e) {
                    assertFalse(cachedSPS && j == 4, "Exception [" + e.getMessage() + "] not expected.");
                }
                // No-op.
                pstmt4.close();
                assertEquals(cachedSPS ? (j < 4 ? 2 : 1) : 0, testConn.getActiveStatementCount());
                try {
                    // De-caches the statement or no-op if not cached.
                    pstmt3.setPoolable(false);
                } catch (SQLException e) {
                    assertFalse(cachedSPS && j == 3, "Exception [" + e.getMessage() + "] not expected.");
                }
                // No-op.
                pstmt3.close();
                assertEquals(cachedSPS ? (j < 3 ? 2 : 1) : 0, testConn.getActiveStatementCount());
                try {
                    // De-caches the statement or no-op if not cached.
                    pstmt2.setPoolable(false);
                } catch (SQLException e) {
                    assertFalse(cachedSPS && j == 2, "Exception [" + e.getMessage() + "] not expected.");
                }
                // No-op.
                pstmt2.close();
                assertEquals(cachedSPS ? 1 : 0, testConn.getActiveStatementCount());
                try {
                    // De-caches the statement or no-op if not cached.
                    pstmt1.setPoolable(false);
                } catch (SQLException e) {
                    assertFalse(cachedSPS, "Exception [" + e.getMessage() + "] not expected.");
                }
                // No-op.
                pstmt1.close();
                assertEquals(0, testConn.getActiveStatementCount());
            }
        }
        testConn.close();
        assertEquals(0, testConn.getActiveStatementCount());
    } while ((useSPS = !useSPS) || (cachePS = !cachePS));
}

Example 8 with ServerPreparedStatement

use of com.mysql.cj.jdbc.ServerPreparedStatement in project ABC by RuiPinto96274.

the class ResultSetRegressionTest method testBug31747910.

/**
 * Tests fix for Bug#31747910, BUG 30474158 FIX IMPROVES JDBC COMPLIANCE BUT CHANGES DEFAULT RESULTSETTYPE HANDLING.
 *
 * @throws Exception
 */
@Test
public void testBug31747910() throws Exception {
    createTable("testBug31747910", "(id INT)");
    this.stmt.executeUpdate("INSERT INTO testBug31747910 VALUES (1), (2), (3), (4), (5)");
    /*
         * Expected exceptions with default RS type and:
         * 0. static;
         * 1. streaming;
         * 2. streaming & scroll-tolerant;
         * 3. cursor-based;
         * 4. cursor-based & scroll-tolerant.
         */
    String[] connOpts = new String[] { "useSSL=false,allowPublicKeyRetrieval=true", "useSSL=false,allowPublicKeyRetrieval=true", "useSSL=false,allowPublicKeyRetrieval=true,scrollTolerantForwardOnly=true", "useSSL=false,allowPublicKeyRetrieval=true,useCursorFetch=true", "useSSL=false,allowPublicKeyRetrieval=true,useCursorFetch=true,scrollTolerantForwardOnly=true" };
    int[] fetchSize = new int[] { 0, Integer.MIN_VALUE, Integer.MIN_VALUE, 2, 2 };
    for (int i = 0; i < connOpts.length; i++) {
        for (int j = 0; j < 3; j++) {
            // Statement; PreparedStatement and ServerPreparedStatement.
            Connection testConn = null;
            Statement testStmt = null;
            switch(j) {
                case 0:
                    // Default behavior using Statement
                    testConn = getConnectionWithProps(connOpts[i]);
                    testStmt = testConn.createStatement();
                    if (fetchSize[i] != 0) {
                        testStmt.setFetchSize(fetchSize[i]);
                    }
                    this.rs = testStmt.executeQuery("SELECT * FROM testBug31747910");
                    break;
                case 1:
                    // Default behavior using PreparedStatement
                    testConn = getConnectionWithProps(connOpts[i]);
                    testStmt = testConn.prepareStatement("SELECT * FROM testBug31747910");
                    if (fetchSize[i] != 0) {
                        testStmt.setFetchSize(fetchSize[i]);
                    }
                    this.rs = ((PreparedStatement) testStmt).executeQuery();
                    break;
                case 2:
                    // Default behavior using ServerPreparedStatement
                    testConn = getConnectionWithProps("useServerPrepStmts=true," + connOpts[i]);
                    testStmt = testConn.prepareStatement("SELECT * FROM testBug31747910");
                    if (fetchSize[i] != 0) {
                        testStmt.setFetchSize(fetchSize[i]);
                    }
                    this.rs = ((PreparedStatement) testStmt).executeQuery();
                    break;
            }
            assertTrue(this.rs.next());
            assertEquals(1, this.rs.getInt(1));
            assertThrows(SQLException.class, "Operation not allowed for a result set of type ResultSet\\.TYPE_FORWARD_ONLY\\.", () -> this.rs.last());
            assertThrows(SQLException.class, "Operation not allowed for a result set of type ResultSet\\.TYPE_FORWARD_ONLY\\.", () -> this.rs.previous());
            assertThrows(SQLException.class, "Operation not allowed for a result set of type ResultSet\\.TYPE_FORWARD_ONLY\\.", () -> this.rs.first());
            assertThrows(SQLException.class, "Operation not allowed for a result set of type ResultSet\\.TYPE_FORWARD_ONLY\\.", () -> this.rs.absolute(3));
            assertThrows(SQLException.class, "Operation not allowed for a result set of type ResultSet\\.TYPE_FORWARD_ONLY\\.", () -> this.rs.relative(-1));
            assertThrows(SQLException.class, "Operation not allowed for a result set of type ResultSet\\.TYPE_FORWARD_ONLY\\.", () -> {
                this.rs.beforeFirst();
                return null;
            });
            assertThrows(SQLException.class, "Operation not allowed for a result set of type ResultSet\\.TYPE_FORWARD_ONLY\\.", () -> {
                this.rs.afterLast();
                return null;
            });
            testStmt.close();
            testConn.close();
        }
    }
    // Scroll-tolerant behavior using: Statement; PreparedStatement; ServerPreparedStatement.
    for (int i = 0; i < 3; i++) {
        Connection testConn = null;
        Statement testStmt = null;
        switch(i) {
            case 0:
                // Scroll-tolerant using Statement
                testConn = getConnectionWithProps("useSSL=false,allowPublicKeyRetrieval=true,scrollTolerantForwardOnly=true");
                testStmt = testConn.createStatement();
                this.rs = testStmt.executeQuery("SELECT * FROM testBug31747910");
                break;
            case 1:
                // Scroll-tolerant using PreparedStatement
                testConn = getConnectionWithProps("useSSL=false,allowPublicKeyRetrieval=true,scrollTolerantForwardOnly=true");
                testStmt = testConn.prepareStatement("SELECT * FROM testBug31747910");
                this.rs = ((PreparedStatement) testStmt).executeQuery();
                break;
            case 2:
                // Scroll-tolerant using ServerPreparedStatement
                testConn = getConnectionWithProps("useSSL=false,allowPublicKeyRetrieval=true,useServerPrepStmts=true,scrollTolerantForwardOnly=true");
                testStmt = testConn.prepareStatement("SELECT * FROM testBug31747910");
                this.rs = ((PreparedStatement) testStmt).executeQuery();
                break;
        }
        assertTrue(this.rs.next());
        assertEquals(1, this.rs.getInt(1));
        assertTrue(this.rs.last());
        assertEquals(5, this.rs.getInt(1));
        assertTrue(this.rs.previous());
        assertEquals(4, this.rs.getInt(1));
        assertTrue(this.rs.first());
        assertEquals(1, this.rs.getInt(1));
        assertTrue(this.rs.absolute(3));
        assertEquals(3, this.rs.getInt(1));
        assertTrue(this.rs.relative(-1));
        assertEquals(2, this.rs.getInt(1));
        this.rs.beforeFirst();
        assertTrue(this.rs.isBeforeFirst());
        this.rs.afterLast();
        assertTrue(this.rs.isAfterLast());
        testStmt.close();
        testConn.close();
    }
}

Example 9 with ServerPreparedStatement

use of com.mysql.cj.jdbc.ServerPreparedStatement in project ABC by RuiPinto96274.

the class SyntaxRegressionTest method testJsonType.

/**
 * WL#7909 - Server side JSON functions
 *
 * Test support for data type JSON.
 *
 * New JSON functions added in MySQL 5.7.8:
 * - JSON_APPEND(), Append data to JSON document (only in 5.7.8)
 * - JSON_ARRAY_APPEND(), Append data to JSON document (added in 5.7.9+)
 * - JSON_ARRAY_INSERT(), Insert into JSON array
 * - JSON_ARRAY(), Create JSON array
 * - JSON_CONTAINS_PATH(), Whether JSON document contains any data at path
 * - JSON_CONTAINS(), Whether JSON document contains specific object at path
 * - JSON_DEPTH(), Maximum depth of JSON document
 * - JSON_EXTRACT(), Return data from JSON document
 * - JSON_INSERT(), Insert data into JSON document
 * - JSON_KEYS(), Array of keys from JSON document
 * - JSON_LENGTH(), Number of elements in JSON document
 * - JSON_MERGE(), Merge JSON documents (up to 8.0.2)
 * - JSON_MERGE_PRESERVE(), Merge JSON documents (since to 8.0.3)
 * - JSON_OBJECT(), Create JSON object
 * - JSON_QUOTE(), Quote JSON document
 * - JSON_REMOVE(), Remove data from JSON document
 * - JSON_REPLACE(), Replace values in JSON document
 * - JSON_SEARCH(), Path to value within JSON document
 * - JSON_SET(), Insert data into JSON document
 * - JSON_TYPE(), Type of JSON value
 * - JSON_UNQUOTE(), Unquote JSON value
 * - JSON_VALID(), Whether JSON value is valid
 *
 * @throws Exception
 */
@Test
public void testJsonType() throws Exception {
    assumeTrue(versionMeetsMinimum(5, 7, 8), "MySQL 5.7.8+ is required to run this test.");
    createTable("testJsonType", "(id INT PRIMARY KEY, jsonDoc JSON)");
    assertEquals(1, this.stmt.executeUpdate("INSERT INTO testJsonType VALUES (1, '{\"key1\": \"value1\"}')"));
    // Plain statement.
    this.rs = this.stmt.executeQuery("SELECT * FROM testJsonType");
    assertEquals("JSON", this.rs.getMetaData().getColumnTypeName(2));
    assertTrue(this.rs.next());
    assertEquals("{\"key1\": \"value1\"}", this.rs.getString(2));
    assertEquals("{\"key1\": \"value1\"}", this.rs.getObject(2));
    assertFalse(this.rs.next());
    // Updatable ResultSet.
    Statement testStmt = this.conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    this.rs = testStmt.executeQuery("SELECT * FROM testJsonType");
    assertTrue(this.rs.next());
    this.rs.updateString(2, "{\"key1\": \"value1\", \"key2\": \"value2\"}");
    this.rs.updateRow();
    this.rs = testStmt.executeQuery("SELECT * FROM testJsonType");
    assertEquals("JSON", this.rs.getMetaData().getColumnTypeName(2));
    assertTrue(this.rs.next());
    assertEquals("{\"key1\": \"value1\", \"key2\": \"value2\"}", this.rs.getString(2));
    assertEquals("{\"key1\": \"value1\", \"key2\": \"value2\"}", this.rs.getObject(2));
    assertFalse(this.rs.next());
    // PreparedStatement.
    this.pstmt = this.conn.prepareStatement("SELECT * FROM testJsonType");
    this.rs = this.pstmt.executeQuery();
    assertEquals("JSON", this.rs.getMetaData().getColumnTypeName(2));
    assertTrue(this.rs.next());
    assertEquals("{\"key1\": \"value1\", \"key2\": \"value2\"}", this.rs.getString(2));
    assertEquals("{\"key1\": \"value1\", \"key2\": \"value2\"}", this.rs.getObject(2));
    assertFalse(this.rs.next());
    // ServerPreparedStatement.
    Connection testConn = getConnectionWithProps("useServerPrepStmts=true");
    this.pstmt = testConn.prepareStatement("SELECT * FROM testJsonType");
    this.rs = this.pstmt.executeQuery();
    assertEquals("JSON", this.rs.getMetaData().getColumnTypeName(2));
    assertTrue(this.rs.next());
    assertEquals("{\"key1\": \"value1\", \"key2\": \"value2\"}", this.rs.getString(2));
    assertEquals("{\"key1\": \"value1\", \"key2\": \"value2\"}", this.rs.getObject(2));
    assertFalse(this.rs.next());
    testConn.close();
    // CallableStatement.
    createProcedure("testJsonTypeProc", "(OUT jsonDoc JSON) SELECT t.jsonDoc INTO jsonDoc FROM testJsonType t");
    CallableStatement testCstmt = this.conn.prepareCall("{CALL testJsonTypeProc(?)}");
    testCstmt.registerOutParameter(1, Types.CHAR);
    testCstmt.execute();
    assertEquals("{\"key1\": \"value1\", \"key2\": \"value2\"}", testCstmt.getString(1));
    assertEquals("{\"key1\": \"value1\", \"key2\": \"value2\"}", testCstmt.getObject(1));
    // JSON functions.
    testJsonTypeCheckFunction(versionMeetsMinimum(5, 7, 9) ? "SELECT JSON_ARRAY_APPEND('[1]', '$', 2)" : "SELECT JSON_APPEND('[1]', '$', 2)", "[1, 2]");
    testJsonTypeCheckFunction("SELECT JSON_ARRAY_INSERT('[2]', '$[0]', 1)", "[1, 2]");
    testJsonTypeCheckFunction("SELECT JSON_ARRAY(1, 2)", "[1, 2]");
    testJsonTypeCheckFunction("SELECT JSON_CONTAINS_PATH('{\"a\": 1}', 'one', '$.a')", "1");
    testJsonTypeCheckFunction("SELECT JSON_CONTAINS('{\"a\": 1}', '1', '$.a')", "1");
    testJsonTypeCheckFunction("SELECT JSON_DEPTH('{\"a\": 1}')", "2");
    testJsonTypeCheckFunction("SELECT JSON_EXTRACT('[1, 2]', '$[0]')", "1");
    testJsonTypeCheckFunction("SELECT JSON_INSERT('[1]', '$[1]', 2)", "[1, 2]");
    testJsonTypeCheckFunction("SELECT JSON_KEYS('{\"a\": 1}')", "[\"a\"]");
    testJsonTypeCheckFunction("SELECT JSON_LENGTH('{\"a\": 1}')", "1");
    testJsonTypeCheckFunction(versionMeetsMinimum(8, 0, 3) ? "SELECT JSON_MERGE_PRESERVE('[1]', '[2]')" : "SELECT JSON_MERGE('[1]', '[2]')", "[1, 2]");
    testJsonTypeCheckFunction("SELECT JSON_OBJECT('a', 1)", "{\"a\": 1}");
    testJsonTypeCheckFunction("SELECT JSON_QUOTE('[1]')", "\"[1]\"");
    testJsonTypeCheckFunction("SELECT JSON_REMOVE('[1, 2]', '$[1]')", "[1]");
    testJsonTypeCheckFunction("SELECT JSON_REPLACE('[0]', '$[0]', 1)", "[1]");
    testJsonTypeCheckFunction("SELECT JSON_SEARCH('{\"a\": \"1\"}', 'one', '1')", "\"$.a\"");
    testJsonTypeCheckFunction("SELECT JSON_SET('[1, 1]', '$[1]', 2)", "[1, 2]");
    testJsonTypeCheckFunction("SELECT JSON_TYPE('[]')", "ARRAY");
    testJsonTypeCheckFunction("SELECT JSON_UNQUOTE('\"[1]\"')", "[1]");
    testJsonTypeCheckFunction("SELECT JSON_VALID('{\"a\": 1}')", "1");
}

Example 10 with ServerPreparedStatement

use of com.mysql.cj.jdbc.ServerPreparedStatement in project JavaSegundasQuintas by ecteruel.

the class StatementsTest method testServerPreparedStatementsCaching.

/**
 * WL#11101 - Remove de-cache and close of SSPSs on double call to close()
 *
 * @throws Exception
 */
@Test
public void testServerPreparedStatementsCaching() throws Exception {
    // Non prepared statements must be non-poolable by default.
    assertFalse(this.stmt.isPoolable());
    Field stmtsCacheField = ConnectionImpl.class.getDeclaredField("serverSideStatementCache");
    stmtsCacheField.setAccessible(true);
    ToIntFunction<Connection> getStmtsCacheSize = (c) -> {
        try {
            LRUCache<?, ?> stmtsCacheObj = (LRUCache<?, ?>) stmtsCacheField.get(c);
            return stmtsCacheObj == null ? -1 : stmtsCacheObj.size();
        } catch (IllegalArgumentException | IllegalAccessException e) {
            fail("Fail getting the statemets cache size.");
            return -1;
        }
    };
    Function<Connection, ServerPreparedStatement> getStmtsCacheSingleElem = (c) -> {
        try {
            @SuppressWarnings("unchecked") LRUCache<?, ServerPreparedStatement> stmtsCacheObj = (LRUCache<?, ServerPreparedStatement>) stmtsCacheField.get(c);
            return stmtsCacheObj.get(stmtsCacheObj.keySet().iterator().next());
        } catch (IllegalArgumentException | IllegalAccessException e) {
            fail("Fail getting the statemets cache element.");
            return null;
        }
    };
    final String sql1 = "SELECT 1, ?";
    final String sql2 = "SELECT 2, ?";
    boolean useSPS = false;
    boolean cachePS = false;
    do {
        Properties props = new Properties();
        props.setProperty(PropertyKey.sslMode.getKeyName(), "DISABLED");
        props.setProperty(PropertyKey.allowPublicKeyRetrieval.getKeyName(), "true");
        props.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), Boolean.toString(useSPS));
        props.setProperty(PropertyKey.cachePrepStmts.getKeyName(), Boolean.toString(cachePS));
        props.setProperty(PropertyKey.prepStmtCacheSize.getKeyName(), "5");
        boolean cachedSPS = useSPS && cachePS;
        /*
             * Cache the prepared statement and de-cache it later.
             * (*) if server prepared statement and caching is enabled.
             */
        {
            JdbcConnection testConn = (JdbcConnection) getConnectionWithProps(props);
            PreparedStatement testPstmt = testConn.prepareStatement(sql1);
            assertEquals(1, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
            assertTrue(testPstmt.isPoolable());
            // Caches this PS (*).
            testPstmt.close();
            assertEquals(cachedSPS ? 1 : 0, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 1 : -1, getStmtsCacheSize.applyAsInt(testConn));
            // No-op.
            testPstmt.close();
            assertEquals(cachedSPS ? 1 : 0, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 1 : -1, getStmtsCacheSize.applyAsInt(testConn));
            if (cachedSPS) {
                assertTrue(testPstmt.isPoolable());
                // De-caches this PS; it gets automatically closed (*).
                testPstmt.setPoolable(false);
                assertEquals(0, testConn.getActiveStatementCount());
                assertEquals(0, getStmtsCacheSize.applyAsInt(testConn));
            }
            // No-op.
            testPstmt.close();
            assertEquals(0, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
            assertThrows(SQLException.class, "No operations allowed after statement closed\\.", () -> {
                testPstmt.setPoolable(false);
                return null;
            });
            assertThrows(SQLException.class, "No operations allowed after statement closed\\.", () -> {
                testPstmt.isPoolable();
                return null;
            });
            testConn.close();
            assertEquals(0, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
        }
        /*
             * Set not to cache the prepared statement.
             * (*) if server prepared statement and caching is enabled.
             */
        {
            JdbcConnection testConn = (JdbcConnection) getConnectionWithProps(props);
            PreparedStatement testPstmt = testConn.prepareStatement(sql1);
            assertEquals(1, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
            assertTrue(testPstmt.isPoolable());
            // Don't cache this PS (*).
            testPstmt.setPoolable(false);
            assertFalse(testPstmt.isPoolable());
            // Doesn't cache this PS (*).
            testPstmt.close();
            assertEquals(0, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
            // No-op.
            testPstmt.close();
            assertEquals(0, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
            assertThrows(SQLException.class, "No operations allowed after statement closed\\.", () -> {
                testPstmt.setPoolable(true);
                return null;
            });
            assertThrows(SQLException.class, "No operations allowed after statement closed\\.", () -> {
                testPstmt.isPoolable();
                return null;
            });
            testConn.close();
            assertEquals(0, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
        }
        /*
             * Set not to cache the prepared statement but change mind before closing it.
             * Reuse the cached prepared statement and don't re-cache it.
             * (*) if server prepared statement and caching is enabled.
             */
        {
            JdbcConnection testConn = (JdbcConnection) getConnectionWithProps(props);
            PreparedStatement testPstmt = testConn.prepareStatement(sql1);
            assertEquals(1, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
            // Don't cache this PS (*).
            testPstmt.setPoolable(false);
            assertFalse(testPstmt.isPoolable());
            testPstmt.setPoolable(true);
            // Changed my mind, let it be cached (*).
            assertTrue(testPstmt.isPoolable());
            // Caches this PS (*).
            testPstmt.close();
            assertEquals(cachedSPS ? 1 : 0, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 1 : -1, getStmtsCacheSize.applyAsInt(testConn));
            // No-op.
            testPstmt.close();
            assertEquals(cachedSPS ? 1 : 0, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 1 : -1, getStmtsCacheSize.applyAsInt(testConn));
            PreparedStatement testPstmtOld = testPstmt;
            // Takes the cached statement (*), or creates a fresh one.
            testPstmt = testConn.prepareStatement(sql1);
            if (cachedSPS) {
                assertSame(testPstmtOld, testPstmt);
            } else {
                assertNotSame(testPstmtOld, testPstmt);
            }
            assertEquals(1, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
            assertTrue(testPstmt.isPoolable());
            // Don't cache this PS (*).
            testPstmt.setPoolable(false);
            assertFalse(testPstmt.isPoolable());
            // Doesn't cache this PS (*).
            testPstmt.close();
            assertEquals(0, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
            testPstmtOld = testPstmt;
            // Creates a fresh prepared statement.
            testPstmt = testConn.prepareStatement(sql1);
            assertNotSame(testPstmtOld, testPstmt);
            assertEquals(1, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
            assertTrue(testPstmt.isPoolable());
            testConn.close();
            assertEquals(0, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
        }
        /*
             * Caching of multiple copies of same prepared statement.
             * (*) if server prepared statement and caching is enabled.
             */
        {
            int psCount = 5;
            JdbcConnection testConn = (JdbcConnection) getConnectionWithProps(props);
            PreparedStatement[] testPstmts = new PreparedStatement[psCount];
            for (int i = 0; i < psCount; i++) {
                testPstmts[i] = testConn.prepareStatement(sql1);
            }
            assertEquals(5, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
            for (int i = 0; i < psCount; i++) {
                assertTrue(testPstmts[i].isPoolable());
                // Caches this PS and replaces existing if same (*).
                testPstmts[i].close();
                assertEquals(cachedSPS ? psCount - i : psCount - i - 1, testConn.getActiveStatementCount());
                assertEquals(cachedSPS ? 1 : -1, getStmtsCacheSize.applyAsInt(testConn));
                if (cachedSPS) {
                    assertSame(testPstmts[i], getStmtsCacheSingleElem.apply(testConn));
                }
            }
            PreparedStatement testPstmt = testConn.prepareStatement(sql1);
            assertEquals(1, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
            for (int i = 0; i < psCount; i++) {
                if (cachedSPS && i == psCount - 1) {
                    assertSame(testPstmts[i], testPstmt);
                } else {
                    assertNotSame(testPstmts[i], testPstmt);
                }
            }
            // Don't cache this PS (*).
            testPstmt.setPoolable(false);
            // Doesn't cache this PS (*).
            testPstmt.close();
            assertEquals(0, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
            testConn.close();
            assertEquals(0, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
        }
        /*
             * Combine caching different prepared statements.
             * (*) if server prepared statement and caching is enabled.
             */
        {
            int psCount = 5;
            JdbcConnection testConn = (JdbcConnection) getConnectionWithProps(props);
            PreparedStatement[] testPstmts1 = new PreparedStatement[psCount];
            for (int i = 0; i < psCount; i++) {
                testPstmts1[i] = testConn.prepareStatement(sql1);
            }
            PreparedStatement testPstmt = testConn.prepareStatement(sql2);
            assertEquals(6, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
            assertTrue(testPstmt.isPoolable());
            // Caches this PS (*).
            testPstmt.close();
            assertEquals(cachedSPS ? 1 : -1, getStmtsCacheSize.applyAsInt(testConn));
            for (int i = 0; i < psCount; i++) {
                assertTrue(testPstmts1[i].isPoolable());
                // Caches this PS and replaces existing if same (*).
                testPstmts1[i].close();
                assertEquals(cachedSPS ? psCount - i + 1 : psCount - i - 1, testConn.getActiveStatementCount());
                assertEquals(cachedSPS ? 2 : -1, getStmtsCacheSize.applyAsInt(testConn));
            }
            PreparedStatement testPstmt1 = testConn.prepareStatement(sql1);
            assertEquals(cachedSPS ? 2 : 1, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 1 : -1, getStmtsCacheSize.applyAsInt(testConn));
            for (int i = 0; i < psCount; i++) {
                if (cachedSPS && i == psCount - 1) {
                    assertSame(testPstmts1[i], testPstmt1);
                } else {
                    assertNotSame(testPstmts1[i], testPstmt1);
                }
            }
            PreparedStatement testPstmt2 = testConn.prepareStatement(sql2);
            assertEquals(2, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
            if (cachedSPS) {
                assertSame(testPstmt, testPstmt2);
            } else {
                assertNotSame(testPstmt, testPstmt2);
            }
            // Don't cache this PS (*).
            testPstmt1.setPoolable(false);
            // Doesn't cache this PS (*).
            testPstmt1.close();
            assertEquals(1, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
            // Don't cache this PS (*).
            testPstmt2.setPoolable(false);
            // Doesn't cache this PS (*).
            testPstmt2.close();
            assertEquals(0, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
            testConn.close();
            assertEquals(0, testConn.getActiveStatementCount());
            assertEquals(cachedSPS ? 0 : -1, getStmtsCacheSize.applyAsInt(testConn));
        }
    } while ((useSPS = !useSPS) || (cachePS = !cachePS));
}