Search in sources :

Example 11 with JdbcStatement

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

the class QueryAttributesTest method rewriteQueriesWithAttributesInClientPreparedStatement.

/**
 * Tests whether the query attributes are propagated to the internally created statement on query rewrites in client prepared statements.
 *
 * @throws Exception
 */
@Test
void rewriteQueriesWithAttributesInClientPreparedStatement() throws Exception {
    createTable("testRewriteClientPstmt", "(c1 VARCHAR(100), c2 VARCHAR(100))");
    Properties props = new Properties();
    props.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "false");
    props.setProperty(PropertyKey.rewriteBatchedStatements.getKeyName(), "true");
    Connection testConn = getConnectionWithProps(props);
    PreparedStatement testPstmt = testConn.prepareStatement("INSERT INTO testRewriteClientPstmt VALUES (?, mysql_query_attribute_string('qa'))");
    assertTrue(JdbcStatement.class.isInstance(testPstmt));
    JdbcStatement testJdbcStmt = (JdbcStatement) testPstmt;
    testJdbcStmt.setAttribute("qa", "MySQL Connector/J");
    testPstmt.setString(1, "Row 1");
    testPstmt.addBatch();
    testPstmt.setString(1, "Row 2");
    testPstmt.addBatch();
    testPstmt.setString(1, "Row 3");
    testPstmt.addBatch();
    testPstmt.setString(1, "Row 4");
    testPstmt.addBatch();
    testPstmt.setString(1, "Row 5");
    testPstmt.addBatch();
    testPstmt.executeBatch();
    this.rs = this.stmt.executeQuery("SELECT * FROM testRewriteClientPstmt");
    assertTrue(this.rs.next());
    assertEquals("Row 1", this.rs.getString(1));
    assertEquals("MySQL Connector/J", this.rs.getString(2));
    assertTrue(this.rs.next());
    assertEquals("Row 2", this.rs.getString(1));
    assertEquals("MySQL Connector/J", this.rs.getString(2));
    assertTrue(this.rs.next());
    assertEquals("Row 3", this.rs.getString(1));
    assertEquals("MySQL Connector/J", this.rs.getString(2));
    assertTrue(this.rs.next());
    assertEquals("Row 4", this.rs.getString(1));
    assertEquals("MySQL Connector/J", this.rs.getString(2));
    assertTrue(this.rs.next());
    assertEquals("Row 5", this.rs.getString(1));
    assertEquals("MySQL Connector/J", this.rs.getString(2));
    assertFalse(this.rs.next());
    testConn.close();
}
Also used : JdbcStatement(com.mysql.cj.jdbc.JdbcStatement) Connection(java.sql.Connection) PreparedStatement(java.sql.PreparedStatement) Properties(java.util.Properties) Test(org.junit.jupiter.api.Test)

Example 12 with JdbcStatement

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

the class QueryAttributesTest method serverPreparedStatementWithQueryAttributesInMultiHost.

/**
 * Tests whether proxied server prepared statement objects created in multi-host connections handle query attributes correctly.
 *
 * @throws Exception
 */
@Test
void serverPreparedStatementWithQueryAttributesInMultiHost() throws Exception {
    Properties props = new Properties();
    props.setProperty("useServerPrepStmts", "true");
    // Failover connection.
    Connection testConn = getFailoverConnection(props);
    PreparedStatement testPstmt = testConn.prepareStatement("SELECT mysql_query_attribute_string('qa')");
    assertTrue(JdbcStatement.class.isInstance(testPstmt));
    JdbcStatement testJdbcStmt = (JdbcStatement) testPstmt;
    testJdbcStmt.setAttribute("qa", "MySQL Connector/J");
    this.rs = testPstmt.executeQuery();
    assertTrue(this.rs.next());
    assertEquals("MySQL Connector/J", this.rs.getString(1));
    assertFalse(this.rs.next());
    testConn.close();
    // Loadbalanced connection.
    testConn = getLoadBalancedConnection(props);
    testPstmt = testConn.prepareStatement("SELECT mysql_query_attribute_string('qa')");
    assertTrue(JdbcStatement.class.isInstance(testPstmt));
    testJdbcStmt = (JdbcStatement) testPstmt;
    testJdbcStmt.setAttribute("qa", "MySQL Connector/J");
    this.rs = testPstmt.executeQuery();
    assertTrue(this.rs.next());
    assertEquals("MySQL Connector/J", this.rs.getString(1));
    assertFalse(this.rs.next());
    testConn.close();
    // Replication connection.
    testConn = getSourceReplicaReplicationConnection(props);
    testPstmt = testConn.prepareStatement("SELECT mysql_query_attribute_string('qa')");
    assertTrue(JdbcStatement.class.isInstance(testPstmt));
    testJdbcStmt = (JdbcStatement) testPstmt;
    testJdbcStmt.setAttribute("qa", "MySQL Connector/J");
    this.rs = testPstmt.executeQuery();
    assertTrue(this.rs.next());
    assertEquals("MySQL Connector/J", this.rs.getString(1));
    assertFalse(this.rs.next());
    testConn.close();
}
Also used : JdbcStatement(com.mysql.cj.jdbc.JdbcStatement) Connection(java.sql.Connection) PreparedStatement(java.sql.PreparedStatement) Properties(java.util.Properties) Test(org.junit.jupiter.api.Test)

Example 13 with JdbcStatement

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

the class QueryAttributesTest method rewriteQueriesWithAttributesInPlainStatement.

/**
 * Tests whether the query attributes are propagated to the internally created statement on query rewrites in plain statements.
 *
 * @throws Exception
 */
@Test
void rewriteQueriesWithAttributesInPlainStatement() throws Exception {
    createTable("testRewritePlainStmt", "(c1 VARCHAR(100), c2 VARCHAR(100))");
    Properties props = new Properties();
    props.setProperty(PropertyKey.rewriteBatchedStatements.getKeyName(), "true");
    Connection testConn = getConnectionWithProps(props);
    Statement testStmt = testConn.createStatement();
    assertTrue(JdbcStatement.class.isInstance(testStmt));
    JdbcStatement testJdbcStmt = (JdbcStatement) testStmt;
    testJdbcStmt.setAttribute("qa", "MySQL Connector/J");
    testStmt.addBatch("INSERT INTO testRewritePlainStmt VALUES ('Row 1', mysql_query_attribute_string('qa'))");
    testStmt.addBatch("INSERT INTO testRewritePlainStmt VALUES ('Row 2', mysql_query_attribute_string('qa'))");
    testStmt.addBatch("INSERT INTO testRewritePlainStmt VALUES ('Row 3', mysql_query_attribute_string('qa'))");
    testStmt.addBatch("INSERT INTO testRewritePlainStmt VALUES ('Row 4', mysql_query_attribute_string('qa'))");
    testStmt.addBatch("INSERT INTO testRewritePlainStmt VALUES ('Row 5', mysql_query_attribute_string('qa'))");
    // Need 5 to rewrite as multi-queries.
    testStmt.executeBatch();
    this.rs = this.stmt.executeQuery("SELECT * FROM testRewritePlainStmt");
    assertTrue(this.rs.next());
    assertEquals("Row 1", this.rs.getString(1));
    assertEquals("MySQL Connector/J", this.rs.getString(2));
    assertTrue(this.rs.next());
    assertEquals("Row 2", this.rs.getString(1));
    assertEquals("MySQL Connector/J", this.rs.getString(2));
    assertTrue(this.rs.next());
    assertEquals("Row 3", this.rs.getString(1));
    assertEquals("MySQL Connector/J", this.rs.getString(2));
    assertTrue(this.rs.next());
    assertEquals("Row 4", this.rs.getString(1));
    assertEquals("MySQL Connector/J", this.rs.getString(2));
    assertTrue(this.rs.next());
    assertEquals("Row 5", this.rs.getString(1));
    assertEquals("MySQL Connector/J", this.rs.getString(2));
    assertFalse(this.rs.next());
    testConn.close();
}
Also used : JdbcStatement(com.mysql.cj.jdbc.JdbcStatement) JdbcStatement(com.mysql.cj.jdbc.JdbcStatement) PreparedStatement(java.sql.PreparedStatement) Statement(java.sql.Statement) Connection(java.sql.Connection) Properties(java.util.Properties) Test(org.junit.jupiter.api.Test)

Example 14 with JdbcStatement

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

the class QueryAttributesTest method queryAttributesTypesInClientPreparedStatement.

/**
 * Tests all supported query attributes types when used in client prepared statements.
 *
 * @throws Exception
 */
@Test
public void queryAttributesTypesInClientPreparedStatement() throws Exception {
    // Tuesday, May 23, 1995 08:00:26.987654 GMT
    long testInstInMilli = 801216026987l;
    long testInstInSecs = 801216026;
    int testInstHour = 8;
    int testInstMin = 0;
    int testInstSec = 26;
    int testInstNano = 987654321;
    int testOffset = 2;
    String testZoneId = "UTC+2";
    String testTimezone = "Europe/Stockholm";
    Calendar testCal = Calendar.getInstance(TimeZone.getTimeZone(testTimezone));
    testCal.setTimeInMillis(testInstInMilli);
    List<String> testList = Arrays.asList("MySQL", "Connector/J");
    String expectedLocalTime = new SimpleDateFormat("HH:mm:ss.SSS000").format(new Date(testInstInMilli));
    String expectedLocalDatetime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS000").format(new Date(testInstInMilli)) + new SimpleDateFormat("XXX").format(new Date(testInstInMilli)).replaceAll("([+-])0", "$1").replace("Z", "+0:00");
    Properties props = new Properties();
    props.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "false");
    Connection testConn = getConnectionWithProps(props);
    PreparedStatement testPstmt = testConn.prepareStatement("SELECT ?, " + IntStream.range(1, 26).mapToObj(i -> String.format("mysql_query_attribute_string('qa%1$02d') AS qa%1$02d", i)).collect(Collectors.joining(", ")) + ", ?");
    testPstmt.setString(1, "MySQL Connector/J");
    testPstmt.setString(2, "8.0.26");
    assertTrue(JdbcStatement.class.isInstance(testPstmt));
    JdbcStatement testJdbcStmt = (JdbcStatement) testPstmt;
    testJdbcStmt.setAttribute("qa01", null);
    testJdbcStmt.setAttribute("qa02", "Query Attributes");
    testJdbcStmt.setAttribute("qa03", false);
    testJdbcStmt.setAttribute("qa04", (byte) 42);
    testJdbcStmt.setAttribute("qa05", (short) -42);
    testJdbcStmt.setAttribute("qa06", Integer.MAX_VALUE);
    testJdbcStmt.setAttribute("qa07", Long.MAX_VALUE);
    testJdbcStmt.setAttribute("qa08", new BigInteger("351910092110"));
    testJdbcStmt.setAttribute("qa09", 2.71828182f);
    testJdbcStmt.setAttribute("qa10", 3.141592653589793d);
    testJdbcStmt.setAttribute("qa11", new BigDecimal("1.61803398874989484820"));
    testJdbcStmt.setAttribute("qa12", new java.sql.Date(testInstInMilli));
    testJdbcStmt.setAttribute("qa13", LocalDate.of(1995, 5, 23));
    testJdbcStmt.setAttribute("qa14", new Time(testInstInMilli));
    testJdbcStmt.setAttribute("qa15", LocalTime.of(testInstHour, testInstMin, testInstSec, testInstNano));
    testJdbcStmt.setAttribute("qa16", OffsetTime.of(testInstHour, testInstMin, testInstSec, testInstNano, ZoneOffset.ofHours(testOffset)));
    testJdbcStmt.setAttribute("qa17", Duration.ofDays(-2).plusHours(2).plusMinutes(20));
    testJdbcStmt.setAttribute("qa18", LocalDateTime.ofEpochSecond(testInstInSecs, testInstNano, ZoneOffset.ofHours(testOffset)));
    testJdbcStmt.setAttribute("qa19", new Timestamp(testInstInMilli));
    testJdbcStmt.setAttribute("qa20", Instant.ofEpochMilli(testInstInMilli));
    testJdbcStmt.setAttribute("qa21", OffsetDateTime.ofInstant(Instant.ofEpochMilli(testInstInMilli), ZoneId.of(testZoneId)));
    testJdbcStmt.setAttribute("qa22", ZonedDateTime.ofInstant(Instant.ofEpochMilli(testInstInMilli), ZoneId.of(testTimezone)));
    testJdbcStmt.setAttribute("qa23", new Date(testInstInMilli));
    testJdbcStmt.setAttribute("qa24", testCal);
    testJdbcStmt.setAttribute("qa25", testList);
    this.rs = testPstmt.executeQuery();
    assertTrue(this.rs.next());
    assertEquals("MySQL Connector/J", this.rs.getString(1));
    assertNull(this.rs.getString("qa01"));
    assertTrue(this.rs.wasNull());
    assertEquals("Query Attributes", this.rs.getString("qa02"));
    assertEquals("0", this.rs.getString("qa03"));
    assertEquals("42", this.rs.getString("qa04"));
    assertEquals("-42", this.rs.getString("qa05"));
    assertEquals("2147483647", this.rs.getString("qa06"));
    assertEquals("9223372036854775807", this.rs.getString("qa07"));
    assertEquals("351910092110", this.rs.getString("qa08"));
    assertTrue(this.rs.getString("qa09").startsWith("2.71828"));
    assertTrue(this.rs.getString("qa10").startsWith("3.14159"));
    assertTrue(this.rs.getString("qa11").startsWith("1.61803"));
    assertEquals("1995-05-23", this.rs.getString("qa12"));
    assertEquals("1995-05-23", this.rs.getString("qa13"));
    assertEquals(expectedLocalTime, this.rs.getString("qa14"));
    assertEquals("08:00:26.987654", this.rs.getString("qa15"));
    assertEquals("08:00:26.987654", this.rs.getString("qa16"));
    assertEquals("-45:40:00.000000", this.rs.getString("qa17"));
    assertEquals("1995-05-23 10:00:26.987654", this.rs.getString("qa18"));
    assertEquals(expectedLocalDatetime, this.rs.getString("qa19"));
    assertEquals("1995-05-23 08:00:26.987000+0:00", this.rs.getString("qa20"));
    assertEquals("1995-05-23 10:00:26.987000+2:00", this.rs.getString("qa21"));
    assertEquals("1995-05-23 10:00:26.987000+2:00", this.rs.getString("qa22"));
    assertEquals(expectedLocalDatetime, this.rs.getString("qa23"));
    assertEquals("1995-05-23 10:00:26.987000+2:00", this.rs.getString("qa24"));
    assertEquals("[MySQL, Connector/J]", this.rs.getString("qa25"));
    assertEquals("8.0.26", this.rs.getString(27));
    assertFalse(this.rs.next());
    testConn.close();
}
Also used : Calendar(java.util.Calendar) Connection(java.sql.Connection) PreparedStatement(java.sql.PreparedStatement) Time(java.sql.Time) ZonedDateTime(java.time.ZonedDateTime) LocalDateTime(java.time.LocalDateTime) LocalTime(java.time.LocalTime) OffsetTime(java.time.OffsetTime) OffsetDateTime(java.time.OffsetDateTime) Properties(java.util.Properties) Timestamp(java.sql.Timestamp) Date(java.util.Date) LocalDate(java.time.LocalDate) BigDecimal(java.math.BigDecimal) JdbcStatement(com.mysql.cj.jdbc.JdbcStatement) BigInteger(java.math.BigInteger) SimpleDateFormat(java.text.SimpleDateFormat) Test(org.junit.jupiter.api.Test)

Example 15 with JdbcStatement

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

the class QueryAttributesTest method preserveAndClearAttributesInClientPreparedStatement.

/**
 * Tests if query attributes are preserved between client prepared statement executions and cleared after calling the 'clearAttributes' method.
 *
 * @throws Exception
 */
@Test
public void preserveAndClearAttributesInClientPreparedStatement() throws Exception {
    Properties props = new Properties();
    props.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "false");
    Connection testConn = getConnectionWithProps(props);
    PreparedStatement testPstmt = testConn.prepareStatement("SELECT ?, mysql_query_attribute_string('qa') AS qa");
    testPstmt.setString(1, "MySQL Connector/J");
    assertTrue(JdbcStatement.class.isInstance(testPstmt));
    JdbcStatement testJdbcStmt = (JdbcStatement) testPstmt;
    testJdbcStmt.setAttribute("qa", "8.0.26");
    for (int c = 0; c < 2; c++) {
        this.rs = testPstmt.executeQuery();
        assertTrue(this.rs.next());
        assertEquals("MySQL Connector/J", this.rs.getString(1));
        assertEquals("8.0.26", this.rs.getString("qa"));
        assertFalse(this.rs.next());
    }
    // Execute twice. Query Attributes must be preserved.
    testJdbcStmt.clearAttributes();
    this.rs = testPstmt.executeQuery();
    assertTrue(this.rs.next());
    assertEquals("MySQL Connector/J", this.rs.getString(1));
    assertNull(this.rs.getString("qa"));
    assertTrue(this.rs.wasNull());
    assertFalse(this.rs.next());
    testConn.close();
}
Also used : JdbcStatement(com.mysql.cj.jdbc.JdbcStatement) Connection(java.sql.Connection) PreparedStatement(java.sql.PreparedStatement) Properties(java.util.Properties) Test(org.junit.jupiter.api.Test)

Aggregations

JdbcStatement (com.mysql.cj.jdbc.JdbcStatement)18 Test (org.junit.jupiter.api.Test)18 PreparedStatement (java.sql.PreparedStatement)17 Connection (java.sql.Connection)13 Properties (java.util.Properties)12 Statement (java.sql.Statement)6 BigDecimal (java.math.BigDecimal)5 Time (java.sql.Time)5 Timestamp (java.sql.Timestamp)5 LocalDate (java.time.LocalDate)5 LocalDateTime (java.time.LocalDateTime)5 LocalTime (java.time.LocalTime)5 Calendar (java.util.Calendar)5 BigInteger (java.math.BigInteger)4 SimpleDateFormat (java.text.SimpleDateFormat)3 OffsetDateTime (java.time.OffsetDateTime)3 OffsetTime (java.time.OffsetTime)3 ZonedDateTime (java.time.ZonedDateTime)3 Date (java.util.Date)3 ClientPreparedStatement (com.mysql.cj.jdbc.ClientPreparedStatement)2