---

Getting meta-data (column names and attributes) in JDBC

---

• Data and Meta Data
  • The data is the information obtained (e.g., by the SELECT command such as fname = "John", lname = "Smith", and so on)

  • The meta data is information about the data in the result set

    E.g.: fname is a string, salary is a double, and so on.

  ---

  ---

  ---

  ---

• Abstract Data Type (ADT) for storing Meta data in JDBC

  • ResultSetMetaData: (Java Doc: click here)

    The Java class ResultSetMetaData contains: Methods to retrieve/obtain the meta data of a result set Variables to store values of the meta data of a result set

  ---

  ---

  ---

  ---

• Using the ResultSetMetaData class

  • Recall that the executeQuery( ) method returns a result set object:

    ResultSet rset; rset = SQLstatement.executeQuery("SQL-query");

    We can use the ResultSet object to obtain the meta data on the result set

    (The structure of the result set is stored inside the ResultSet object)

    ---

  • How to obtain the meta data on a result set:

    (1) Execute a query: ResultSet rset; rset = SQLstatement.executeQuery("SQL-query"); (2) Get the meta data on the result set: ResultSetMetaData metaData; // Define variable to hold meta data metaData = rset.getMetaData(); // Get meta data of result set

    The meta data of the result set is stored in the ResultSetMetaData typed variable metaData

    ---

    ---

    ---

  • Using the meta data information:

    The ResultSetMetaData object contains methods to retrieve the meta data Sample methods: int     getColumnCount() returns the number of columns (= attributes) in the tuples of the result set String     getColumnName(int columnIndex) returns the name of the column whose index is "columnIndex" Note: column index starts with 1 !!! String     getColumnType(int columnIndex) returns an integer code for the data type of the attribute whose index is "columnIndex" The integer codes are defined in the Java.lang.Types class. See: the documentation for Java.lang.Types: click here Note: column index starts with 1 !!! String     getColumnTypeName(int columnIndex) returns the type of column whose index is "columnIndex" Note: column index starts with 1 !!! int     getColumnDisplaySize(int columnIndex) returns the display width (number of characters needed to display) of column whose index is "columnIndex" Note: column index starts with 1 !!! int     getPrecision(int columnIndex) returns the number of digits of field/column whose index is "columnIndex" (The data type of this attribute must be numeric !!) Note: column index starts with 1 !!! int     getScale(int columnIndex) returns the number of decimal places of field/column whose index is "columnIndex" (The data type of this attribute must be numeric !!) Note: column index starts with 1 !!! String     getColumnClassName(int columnIndex) returns the name of the Java class of column whose index is "columnIndex" (the output looks like this: "java.lang.String", "java.lang.Integer" , etc) Note: column index starts with 1 !!!

    ---

  • Example Usage:

    ResultSet rset; rset = SQLstatement.executeQuery("SQL-query"); ResultSetMetaData metaData; // Define variable to hold meta data metaData = rset.getMetaData(); // Get meta data of result set int nCols = metaData.getColumnCount(); // Get # fields (columns) in result String name = metaData.getColumnName(1); // Get attr. name of column 1 (first column)

  ---

  ---

  ---

  ---

• Complete example on retrieving Meta Data using JDBC
  • Complete example:

    import java.sql.*; public class MetaData { String url = "jdbc:mysql://holland.mathcs.emory.edu:3306/"; String dbName = "companyDB"; String userName = "cs377"; String password = "abc123"; public static void main (String args[]) { try { // Load the MySQL JDBC driver DriverManager.registerDriver( new com.mysql.jdbc.Driver() ); } catch (Exception e) { System.out.println("Failed to load JDBC driver."); return; } Connection conn = null; Statement stmt = null; try { // Connect to the database conn = DriverManager.getConnection(url+dbName,userName,password); // Allocate buffer to execute SQL statements stmt = conn.createStatement (); } catch (Exception e) { System.err.println("problems connecting to " + url+dbName); } try { // Execute an SQL command ResultSet rset = stmt.executeQuery("select * from employee"); // Get the meta data ResultSetMetaData meta = rset.getMetaData(); // Print the meta data int NCols = meta.getColumnCount(); // # of attributes in result System.out.println ("Name Type DispSz Scale Precis ClassName"); System.out.println ("-----------------------------------------------"); for (int i = 1; i <= NCols; i++) { System.out.println (meta.getColumnName(i) + "\t" + meta.getColumnType(i) + "\t" + meta.getColumnTypeName(i) + "\t" + meta.getColumnDisplaySize(i) + "\t" + meta.getScale(i) + "\t" + meta.getPrecision(i) + "\t" + meta.getColumnClassName(i) + "\t" ); } conn.close(); } catch (Exception e) { System.out.println("Error: " + e.getMessage() ); // Print error message } } }

    ---

    ---

  • Example Program: (Demo above code)                                                
    • Prog file: click here

    How to run the program:

    Right click on link and save in a scratch directory To compile:   javac -cp ".:/home/cs377001/lib/mysql-connector-java.jar" MetaData.java To run:          java -cp ".:/home/cs377001/lib/mysql-connector-java.jar" MetaData

    ---

  • Output:

    Types codes: String: 1 (Type codes obtained from: java.lang.Types class) INT: 4 DECIMAL: 3 Name TypeCode TypeName DispSz Scale Precis ClassName --------------------------------------------------------------------- fname 1 CHAR 6 0 6 java.lang.String minit 1 CHAR 1 0 1 java.lang.String lname 1 CHAR 8 0 8 java.lang.String ssn 1 CHAR 9 0 9 java.lang.String bdate 1 CHAR 10 0 10 java.lang.String address 1 CHAR 25 0 25 java.lang.String sex 1 CHAR 1 0 1 java.lang.String salary 3 DECIMAL 9 2 7 java.math.BigDecimal superssn 1 CHAR 9 0 9 java.lang.String dno 4 INT 11 0 11 java.lang.Integer