Java setNull for SQL type TEXT in prepared statement

Question

What java.sql.Types should I use for setting NULL in a prepared statement when the MySQL column type is TEXT? I don't see a type TEXT here: http://docs.oracle.com/javase/7/docs/api/java/sql/Types.html.

Here's my table definition (simplified):

CREATE TABLE IF NOT EXISTS `mytable` (
  `txtcolumn` text DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Here's what I'd like to do:

...
java.sql.Connection c = getConnection();
PreparedStatement s = c.prepareStatement( "Insert into mytable (txtcolumn) values (?)" );
s.setNull( 0, java.sql.Types.TEXT );
...

...but there is no java.sql.Types.TEXT.

Answer 1

You can use BLOB, CLOB, String.

All TEXT types return Types.LONGVARCHAR with different getPrecision() values (65535, 255, 16777215, and 2147483647 respectively) with getColumnType() returning -1. This behavior is intentional even though TINYTEXT does not fall, regarding to its size, within the LONGVARCHAR category. This is to avoid different handling inside the same base type. And getColumnType() returns -1 because the internal server handling is of type TEXT, which is similar to BLOB.

Also note that getColumnTypeName() will return VARCHAR even though getColumnType() returns Types.LONGVARCHAR, because VARCHAR is the designated column database-specific name for this type.

http://dev.mysql.com/doc/connector-j/en/connector-j-reference-type-conversions.html