3.5.8 Using Query Attributes

Connector/J supports Query Attributes when it has been enabled on the server by installing the query_attributes component (see Prerequisites for Using Query Attributes for details).

Attributes are set for a query by using the setAttribute() method of the JdbcStatement interface. Here is the method's signature:

JdbcStatement.setAttribute(String name, Object value)

Here is an example of using the query attributes with a JdbcStatement:

Example 3.1 Using Query Attributes with a Plain Statement

conn = DriverManager.getConnection("jdbc:mysql://localhost/test", "myuser", "password");
Statement stmt = conn.createStatement();
JdbcStatement jdbcStmt = (JdbcStatement) stmt;
jdbcStmt.executeUpdate("CREATE TABLE t11 (c1 CHAR(20), c2 CHAR(20))");
jdbcStmt.setAttribute("attr1", "cat");
jdbcStmt.setAttribute("attr2", "mat");
jdbcStmt.executeUpdate("INSERT INTO t11 (c1, c2) VALUES(\n" +
    " mysql_query_attribute_string('attr1'),\n" +
    " mysql_query_attribute_string('attr2')\n" +
    " );");
ResultSet rs = stmt.executeQuery("SELECT * from t11");
while(rs.next()) {
    String col1 = rs.getString(1);
    String col2 = rs.getString(2);
    System.out.println("The "+col1+" is on the "+col2);
    }

While query attributes are cleared on the server after each query, they are kept on the side of Connector/J, so they can be resent for the next query. To clear the attributes, use the clearAttributes() method of the JdbcStatement interface:

JdbcStatement.clearAttributes()

The following example (a continuation of the code in Example 3.1, “Using Query Attributes with a Plain Statement”) shows how the attributes are preserved for a statement until it is cleared :

Example 3.2 Preservation of Query Attributes

/* Continuing from the code in the last example, where query attributes have
already been set and used */ 
rs = stmt.executeQuery("SELECT c2 FROM t11 where " +
                "c1 = mysql_query_attribute_string('attr1')");
        if (rs.next()) {
            String col1 = rs.getString(1);
            System.out.println("It is on the "+col1);
        }
        // Prints "It is on the mat"
        jdbcStmt.clearAttributes();
        rs = stmt.executeQuery("SELECT c2 FROM t11 where " +
                "c1 = mysql_query_attribute_string('attr1')");
        if (rs.next()) {
            String col1 = rs.getString(1);
            System.out.println("It is on the "+col1);
        }
        else {
            System.out.println("No results!");
        }
        // Prints "No results!" as attribute string attr1 is empty

Attributes can also be set for client-side and server-side prepared statements, using the setAttribute() method:

Example 3.3 Using Query Attributes with a Prepared Statement

conn = DriverManager.getConnection("jdbc:mysql://localhost/test", "myuser", "password");
PreparedStatement ps = conn.prepareStatement(
                "select ?, c2 from t11 where c1 = mysql_query_attribute_string('attr1')");
ps.setString(1, "It is on a ");
JdbcStatement jdbcPs = (JdbcStatement) ps;
jdbcPs.setAttribute("attr1", "cat");
rs = ps.executeQuery();
if (rs.next()) {
            System.out.println(rs.getString(1)+" "+ rs.getString(2));
}

Not all MySQL data types are supported by the setAttribute() method; only the following MySQL data types are supported and are directly mapped to from specific Java objects or their subclasses:

Table 3.25 Data Type Mappings for Query Attributes

MySQL Data Type	Java Object
MYSQL_TYPE_STRING	java.lang.String
MYSQL_TYPE_TINY	java.lang.Boolean, java.lang.Byte
MYSQL_TYPE_SHORT	java.lang.Short
MYSQL_TYPE_LONG	java.lang.Integer
MYSQL_TYPE_LONGLONG	java.lang.Long, java.math.BigInteger
MYSQL_TYPE_FLOAT	java.lang.Float
MYSQL_TYPE_DOUBLE	java.lang.Double, java.math.BigDecimal
MYSQL_TYPE_DATE	java.sql.Date, java.time.LocalDate
MYSQL_TYPE_TIME	java.sql.Time, java.time.LocalTime, java.time.OffsetTime, java.time.Duration
MYSQL_TYPE_DATETIME	java.time.LocalDateTime
MYSQL_TYPE_TIMESTAMP	java.sql.Timestamp, java.time.Instant, java.time.OffsetDateTime, java.time.ZonedDateTime, java.util.Date, java.util.Calendar

When there is no direct mapping from a Java object type to any MySQL data type, the attribute is set with a string value that comes from converting the supplied object to a String using the .toString() method.