How to get column specifications from MySQL with Java?

Question

In MySQL, every column have flags Binary, Unsigned and Zerofill. Is there a way to get the values of this flags with Java 1.8? DatabaseMetaData.getColumns() doesn't return this values, as I can see.

score 3 · Accepted Answer · edited May 23 '17 at 12:32

Assuming a test table created with

CREATE TABLE tableA 
(   id int auto_increment primary key,
    w INT(8) ZEROFILL NOT NULL, 
    x INT(8) NOT NULL,
    y int signed not null,
    z int unsigned not null,
    shortBinaryCharString char(10) binary not null,
    myBlob blob(10000) null
);

As mostly borrowed from here as a starting point.

I would recommend using standard querying against the tables in the INFORMATION_SCHEMA database. Such as

select table_schema, table_name, column_name, ordinal_position, is_nullable, data_type, 
character_set_name, collation_name, column_type, column_key, extra
from INFORMATION_SCHEMA.columns 
where table_schema = 'so_gibberish' -- your database/schema name
and table_name='tableA'


+--------------+------------+-----------------------+------------------+-------------+-----------+--------------------+----------------+--------------------------+------------+----------------+
| table_schema | table_name | column_name           | ordinal_position | is_nullable | data_type | character_set_name | collation_name | column_type              | column_key | extra          |
+--------------+------------+-----------------------+------------------+-------------+-----------+--------------------+----------------+--------------------------+------------+----------------+
| so_gibberish | tablea     | id                    |                1 | NO          | int       | NULL               | NULL           | int(11)                  | PRI        | auto_increment |
| so_gibberish | tablea     | w                     |                2 | NO          | int       | NULL               | NULL           | int(8) unsigned zerofill |            |                |
| so_gibberish | tablea     | x                     |                3 | NO          | int       | NULL               | NULL           | int(8)                   |            |                |
| so_gibberish | tablea     | y                     |                4 | NO          | int       | NULL               | NULL           | int(11)                  |            |                |
| so_gibberish | tablea     | z                     |                5 | NO          | int       | NULL               | NULL           | int(10) unsigned         |            |                |
| so_gibberish | tablea     | shortBinaryCharString |                6 | NO          | char      | utf8               | utf8_bin       | char(10)                 |            |                |
| so_gibberish | tablea     | myBlob                |                7 | YES         | blob      | NULL               | NULL           | blob                     |            |                |
+--------------+------------+-----------------------+------------------+-------------+-----------+--------------------+----------------+--------------------------+------------+----------------+

You also may have luck with executing

SHOW FIELDS FROM tableA;

or

show create table tableA

Using standard java resultsets as you are most likely already doing would suffice.

try {
      con = DriverManager.getConnection(url, user, password);
      st = con.createStatement();
      rs = st.executeQuery("show fields from tableA");
      while (rs.next()) {
           System.out.println(rs.getString(1)+": "+rs.getString(2));
} catch (SQLException ex) {
     Logger lgr = Logger.getLogger(myTest.class.getName());
     lgr.log(Level.SEVERE, ex.getMessage(), ex);
}

Note: the reason the character set and collation columns were shown is that there is a nuance difference between having a char binary column with a certain character set sort order. As compared to a true binary blob/text column that has no sort order other than binary. See the Mysql Manual Page entitled The BINARY and VARBINARY Types and determine how that works into your notion of "binary". Often, one has to parse a column of output to determine if zerofill is turned on.

How to get column specifications from MySQL with Java?

1 Answers1