Using metadata in JDBC

Why use meta data ?
- Fact:

Example program

Problem:

We use the classic algorithm for finding the minumum value:

min = first value; while ( there are more values ) { if ( next value < min ) min = next value; }

Attempt to construct a solution:

/* ========================================= Read in query ========================================= */ Scanner in = new Scanner(System.in); System.out.print("Enter an SQL query: "); String query = in.nextLine( ); // Read in a query .... (code for JDBC initialization omitted for brevity) .... try { ResultSet rset = stmt.executeQuery(query); // Execute the query /* ================================ Check if rset is empty ================================ */ if ( rset.next( ) == false ) { System.out.println("Empty rset set. Done."); System.exit(1); } min = rset.getString(1); // First value while ( rset.next( ) ) { if ( rset.getString(1).compareTo(min) < 0 ) min = rset.getString(1); // Next value is smaller } /* =============================== Print the minimum found =============================== */ System.out.println("Min " + meta.getColumnName(1) + " = " + min); } catch (Exception e) { System.out.println(e.getMessage()); // Print the error message }

Example Program: (Demo above code)

Program: /home/cs377001/demo/JDBC/UseMetaData1.java Compile: javac -cp ".:/home/cs377001/lib/mysql-connector-java.jar" UseMetaData1.java Run: java -cp ".:/home/cs377001/lib/mysql-connector-java.jar" UseMetaData1 Example: java -cp ".:/home/cs377001/lib/mysql-connector-java.jar" UseMetaData1 Enter an SQL query: select fname from employee Attribute values of the first attribute fname: John Frankl Alicia Jennif Ramesh Joyce Ahmad James Min fname = Ahmad However: java -cp ".:/home/cs377001/lib/mysql-connector-java.jar" UseMetaData1 Enter an SQL query: select salary from employee Attribute values of the first attribute salary: 9999.00 40000.00 25000.00 43000.00 38000.00 25000.00 25000.00 55000.00 Min salary = 25000.00

Problem:

The program read (= converts) all the attribute values at strings
We cannot compare numeric value as strings because:
Check out this program: /home/cs377001/demo/JDBC/StringCmp.java

We must write a separate program to find the minimum for decimal/double typed attributes:

Program: /home/cs377001/demo/JDBC/UseMetaData2.java /* ================================ Check if rset is empty ================================ */ if ( rset.next( ) == false ) { System.out.println("Empty rset set. Done."); System.exit(1); } double min = rset.getDouble(1); while ( rset.next( ) ) { if ( rset.getDouble(1) < min ) min = rset.getDouble(1); }

How to write a more general program that works for all data types

Solution:

We use the Physical Data Independence technique that we have learned at the start of this course:

String min_s = ""; // Handle all possible data types int min_i = 0; double min_d = 0; try { ResultSet rset = stmt.executeQuery(query); ResultSetMetaData meta = rset.getMetaData(); /* ------------------------------------------- Retrieve the first tuple of the result set ------------------------------------------- */ if ( rset.next( ) == false ) // rset.next( ) gets first tuple { System.out.println("Empty rset set. Done."); System.exit(1); } if ( meta.getColumnType(1) == Types.CHAR ) { /* Find String typed min */ min_s = rset.getString(1); // min = first value while ( rset.next( ) ) { if ( rset.getString(1).compareTo(min_s) < 0 ) min_s = rset.getString(1); } System.out.println("String min " + meta.getColumnName(1) + " = " + min_s); } else if ( meta.getColumnType(1) == Types.INTEGER ) { /* Find integer typed min */ min_i = rset.getInt(1); // min = first value while ( rset.next( ) ) { if ( rset.getInt(1) < min_i ) min_i = rset.getInt(1); } System.out.println("String min " + meta.getColumnName(1) + " = " + min_i); } else if ( meta.getColumnType(1) == Types.DECIMAL || meta.getColumnType(1) == Types.DOUBLE ) { /* Find floating point typed min */ min_d = rset.getDouble(1); // min = first value while ( rset.next( ) ) { if ( rset.getDouble(1) < min_d ) min_d = rset.getDouble(1); } System.out.println("String min " + meta.getColumnName(1) + " = " + min_d); min_d = rset.getDouble(1); } else { System.out.println("Unhandled type. Done."); System.exit(1); } conn.close(); } catch (Exception e) { System.out.println(e.getMessage()); // Print the error message }

Example Program: (Demo above code)

Program: /home/cs377001/demo/JDBC/UseMetaData2.java Compile: javac -cp ".:/home/cs377001/lib/mysql-connector-java.jar" UseMetaData3.java Run: java -cp ".:/home/cs377001/lib/mysql-connector-java.jar" UseMetaData3 Example: java -cp ".:/home/cs377001/lib/mysql-connector-java.jar" UseMetaData3 Enter an SQL query: select fname from employee Attribute values of the first attribute fname: John Frankl Alicia Jennif Ramesh Joyce Ahmad James Min fname = Ahmad Furthermore: java -cp ".:/home/cs377001/lib/mysql-connector-java.jar" UseMetaData3 Enter an SQL query: select salary from employee Attribute values of the first attribute salary: 9999.00 40000.00 25000.00 43000.00 38000.00 25000.00 25000.00 55000.00 Min salary = 9999.00