Database-Independent Methods of Obtaining Table Information
Problem
You
want a way to get table information that doesn’t use
MySQL-specific queries like SHOW
COLUMNS
.
Solution
This isn’t possible for all APIs. One exception is JDBC, which provides a standard interface to table metadata.
Discussion
The preceding methods for obtaining table information used specific
SHOW
or SELECT
queries and
showed how to process them using each API. These techniques are
MySQL-specific. JDBC provides a way to access this information
through a standard interface that makes no reference to particular
queries, so you can use it portably with database engines other than
MySQL. With this interface, you use your connection object to obtain
a database metadata object, then invoke the getColumns( )
method of that object to retrieve column information.
getColumns( )
returns a result set containing one
row per column name, so you must run a fetch loop to retrieve
information about successive columns. Elements of result set rows
that are relevant for MySQL are:
Index |
Meaning |
---|---|
3 |
Table name |
4 |
Column name |
6 |
Column type name |
7 |
Column size (for numeric columns, this is the precision) |
8 |
Number of decimal places, for numeric columns |
18 |
Whether or not column values can be |
Here’s an example that shows how to use
getColumns( )
to print a list of column names and
types:
DatabaseMetaData md = conn.getMetaData ( ); ResultSet rs = md.getColumns (dbName, "", tblName, "%"); int i = 0; while (rs.next ( )) { i++; ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.