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 copyJdbcStatement.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
Press CTRL+C to copyconn = 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 copyJdbcStatement.clearAttributes()
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
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 6.3 Using Query Attributes with a Prepared Statement
Press CTRL+C to copyconn = 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 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.