Documentation Home
Connectors and APIs Manual
Download this Manual
PDF (US Ltr) - 4.5Mb
PDF (A4) - 4.5Mb


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:

Press CTRL+C to copy
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

Press CTRL+C to copy
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:

Press CTRL+C to copy
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

Press CTRL+C to copy
/* 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

Press CTRL+C to copy
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
MySQL Data Type Java Object

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.