Documentation Home
MySQL Connector/J Developer Guide
Related Documentation Download this Manual
PDF (US Ltr) - 0.8Mb
PDF (A4) - 0.8Mb

MySQL Connector/J Developer Guide  /  Connector/J Reference  /  Using Query Attributes

6.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 6.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( {
    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:


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

Example 6.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 ( {
            String col1 = rs.getString(1);
            System.out.println("It is on the "+col1);

        // Prints "It is on the mat"

        rs = stmt.executeQuery("SELECT c2 FROM t11 where " +
                "c1 = mysql_query_attribute_string('attr1')");

        if ( {
            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 6.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 ( {
            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 6.22 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.