How Do You Express ResultSetMetaData in XML? JDBC

What follows is a single method, ResultSetMetaDataTool.getResultSetMetaData(), which creates an XML document for a ResultSetMetaData from a given ResultSet. The method getResultSetMetaData() can be used by any type of client (based on XML tags, clients may extract their needed information). The generated XML will have the following syntax:

<?xml version="1.0"?>
<resultSetMetaData columnCount="number-of-columns">
<columnMetaData column="column-number"
columnDisplaySize="column-display-size"
columnLabel="column-label"
columnName="column-name"
columnType="column-type"
columnTypeName="column-type-name"
columnClassName="column-class-name"
tableName="table-name"
precision="precision-of-column"
scale="scale-of-column"
isAutoIncrement="true/false"
isCurrency="true/false"
isWritable="true/false"
isDefinitelyWritable="true/false"
isNullable="0/1"
isReadOnly="true/false"
isCaseSensitive="true/false"
isSearchable="true/false"
isSigned="true/false"
catalog="catalog-name"
schema="schema-name" />
<columnMetaData ... />
...
<columnMetaData ... />
</resultSetMetaData>

getResultSetMetaData()

The getResultSetMetaData() method accepts an instance of ResultSet and outputs its metadata as XML (expressed as a serialized String object):

/**
* Gets column names and their associated attributes (type,
* size, nullable). The result is returned as XML (as
* a String object); if table name is null/empty
* it returns null.
*
* @param rs the result set (ResultSet) object.
* @return result set's metadata as XML as String object;
* this metadata includes column names and their associated
* attributes: type, size, nullable.
* @exception Failed to get the result set's metadata as XML.
*/
public static String getResultSetMetaData(ResultSet rs)
throws Exception {
if (rs == null ) {
return null;
}
// Retrieves the number, types and properties
// of this ResultSet object's columns.
ResultSetMetaData rsMetaData = rs.getMetaData();
if (rsMetaData == null ) {
return null;
}
StringBuffer sb = new StringBuffer();
sb.append("<resultSetMetaData columnCount=\"");
int numberOfColumns = rsMetaData.getColumnCount();
sb.append(numberOfColumns);
sb.append("\">");
for (int i=1; i<=numberOfColumns; i++) {
sb.append(getColumnMetaData(rsMetaData, i));
}
sb.append("</resultSetMetaData>");
return sb.toString();
}

getColumnMetaData() method:

/**
* Gets specific column's associated attributes
* (type, size, nullable). The result is returned
* as XML (represented as a String object).
* XML attributes (as constants) are prefixed
* with the "XML_METADATA_TAG_".
*
* @param rsMetaData the result set metadata object.
* @param columnNumber the column number.
* @return result set's metadata as XML as
* String object; this metadata includes
* column names and their associated attributes:
* type, size, nullable.
* @exception Failed to get the result set's
* meta data as an XML.
*/
private static String getColumnMetaData
(ResultSetMetaData rsMetaData,
int columnNumber)
throws Exception {
StringBuffer sb = new StringBuffer();
sb.append("<columnMetaData ");
append(sb, XML_METADATA_TAG_COLUMN, columnNumber);
// Indicates the designated column's normal
// maximum width in characters
append(sb, XML_METADATA_TAG_COLUMN_DISPLAY_SIZE,
rsMetaData.getColumnDisplaySize(columnNumber));
// Gets the designated column's suggested title
// for use in printouts and displays.
append(sb, XML_METADATA_TAG_COLUMN_LABEL,
rsMetaData.getColumnLabel(columnNumber));
// Gets the designated column's name.
append(sb, XML_METADATA_TAG_COLUMN_NAME,
rsMetaData.getColumnName(columnNumber));
// Gets the designated column's SQL type.
append(sb, XML_METADATA_TAG_COLUMN_TYPE,
rsMetaData.getColumnType(columnNumber));
// Gets the designated column's SQL type name.
append(sb, XML_METADATA_TAG_COLUMN_TYPE_NAME,
rsMetaData.getColumnTypeName(columnNumber));
// Gets the designated column's class name.
append(sb, XML_METADATA_TAG_COLUMN_CLASS_NAME,
rsMetaData.getColumnClassName(columnNumber));
// Gets the designated column's table name.
append(sb, XML_METADATA_TAG_TABLE_NAME,
rsMetaData.getTableName(columnNumber));
// Gets the designated column's number of decimal digits.
append(sb, XML_METADATA_TAG_PRECISION,
rsMetaData.getPrecision(columnNumber));
// Gets the designated column's number of
// digits to right of the decimal point.
append(sb, XML_METADATA_TAG_SCALE,
rsMetaData.getScale(columnNumber));
// Indicates whether the designated column is
// automatically numbered, thus read-only.
append(sb, XML_METADATA_TAG_IS_AUTO_INCREMENT,
rsMetaData.isAutoIncrement(columnNumber));
// Indicates whether the designated column is a cash value.
append(sb, XML_METADATA_TAG_IS_CURRENCY,
rsMetaData.isCurrency(columnNumber));
// Indicates whether a write on the designated
// column will succeed.
append(sb, XML_METADATA_TAG_IS_WRITABLE,
rsMetaData.isWritable(columnNumber));
// Indicates whether a write on the designated
// column will definitely succeed.
append(sb, XML_METADATA_TAG_IS_DEFINITELY_WRITABLE,
rsMetaData.isDefinitelyWritable(columnNumber));
// Indicates the nullability of values
// in the designated column.
append(sb, XML_METADATA_TAG_IS_NULLABLE,
rsMetaData.isNullable(columnNumber));
// Indicates whether the designated column
// is definitely not writable.
append(sb, XML_METADATA_TAG_IS_READ_ONLY,
rsMetaData.isReadOnly(columnNumber));
// Indicates whether a column's case matters
// in the designated column.
append(sb, XML_METADATA_TAG_IS_CASE_SENSITIVE,
rsMetaData.isCaseSensitive(columnNumber));
// Indicates whether a column's case matters
// in the designated column.
append(sb, XML_METADATA_TAG_IS_SEARCHABLE,
rsMetaData.isSearchable(columnNumber));
// Indicates whether values in the designated
// column are signed numbers.
append(sb, XML_METADATA_TAG_IS_SIGNED,
rsMetaData.isSigned(columnNumber));
// Gets the designated column's table's catalog name.
append(sb, XML_METADATA_TAG_CATALOG_NAME,
rsMetaData.getCatalogName(columnNumber));
// Gets the designated column's table's schema name.
append(sb, XML_METADATA_TAG_SCHEMA_NAME,
rsMetaData.getSchemaName(columnNumber));
sb.append("/>");
return sb.toString();
}
Support methods are provided here:
/**
* Append attribute=value to the string buffer denoted by sb.
* @param sb the string buffer.
* @param attribute the attribute name.
* @param value the value of the attribute.
*/
private static void append(StringBuffer sb,
String attribute,
String value) {
sb.append(attribute);
sb.append("=\"");
sb.append(value);
sb.append("\" ");
}
/**
* Append attribute=value to the string buffer denoted by sb.
* @param sb the string buffer.
* @param attribute the attribute name.
* @param value the value of the attribute.
*/
private static void append(StringBuffer sb,
String attribute,
int value) {
sb.append(attribute);
sb.append("=\"");
sb.append(value);
sb.append("\" ");
}
/**
* Append attribute=value to the string buffer denoted by sb.
* @param sb the string buffer.
* @param attribute the attribute name.
* @param value the value of the attribute.
*/
private static void append(StringBuffer sb,
String attribute,
boolean value) {
sb.append(attribute);
sb.append("=\"");
sb.append(value);
sb.append("\" ");
}

Oracle Database Setup

The client using Oracle database will use the employees table described here:

$ sqlplus octopus/octopus
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Feb 25 08:13:46 2003
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
SQL> describe employees;
Name Null? Type
----------------- -------- ----------------
BADGENUMBER NOT NULL NUMBER(38)
NAME VARCHAR2(60)
EMPLOYEETYPE VARCHAR2(30)
PHOTO BINARY FILE LOB

Client Using Oracle

import java.util.*;
import java.io.*;
import java.sql.*;
import jcb.db.*;
import jcb.meta.*;
public class TestOracleResultSetMetaDataTool {
public static Connection getConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:maui";
String username = "octopus";
String password = "octopus";
Class.forName(driver); // load Oracle driver
return DriverManager.getConnection(url, username, password);
}
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = getConnection();
// Create a result set
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT * FROM employees");
System.out.println("-------- getResultSetMetaData -------------");
System.out.println("conn="+conn);
String rsMetaData = ResultSetMetaDataTool.getResultSetMetaData(rs);
System.out.println(rsMetaData);
System.out.println("------------------------------------");
}
catch(Exception e){
e.printStackTrace();
System.exit(1);
}
finally {
DatabaseUtil.close(stmt);
DatabaseUtil.close(rs);
DatabaseUtil.close(conn);
}
}
}

Client Output Using Oracle

-------- getResultSetMetaData -------------
conn=oracle.jdbc.driver.OracleConnection@66e815
<?xml version='1.0'>
<resultSetMetaData columnCount="4">
<columnMetaData column="1"
columnDisplaySize="22" columnLabel="BADGENUMBER"
columnName="BADGENUMBER" columnType="2"
columnTypeName="NUMBER" columnClassName="java.math.BigDecimal"
tableName="" precision="38" scale="0"
isAutoIncrement="false" isCurrency="true"
isWritable="true" isDefinitelyWritable="false"
isNullable="0" isReadOnly="false"
isCaseSensitive="false" isSearchable="true" isSigned="true"
catalog="" schema="" />
<columnMetaData column="2"
columnDisplaySize="60" columnLabel="NAME"
columnName="NAME" columnType="12"
columnTypeName="VARCHAR2" columnClassName="java.lang.String"
tableName="" precision="60" scale="0"
isAutoIncrement="false" isCurrency="false"
isWritable="true" isDefinitelyWritable="false"
isNullable="1" isReadOnly="false"
isCaseSensitive="true" isSearchable="true" isSigned="true"
catalog="" schema="" />
<columnMetaData column="3"
columnDisplaySize="30" columnLabel="EMPLOYEETYPE"
columnName="EMPLOYEETYPE" columnType="12"
columnTypeName="VARCHAR2" columnClassName="java.lang.String"
tableName="" precision="30" scale="0"
isAutoIncrement="false" isCurrency="false"
isWritable="true" isDefinitelyWritable="false"
isNullable="1" isReadOnly="false"
isCaseSensitive="true" isSearchable="true" isSigned="true"
catalog="" schema="" />
<columnMetaData column="4"
columnDisplaySize="530" columnLabel="PHOTO"
columnName="PHOTO" columnType="-13"
columnTypeName="BFILE" columnClassName="oracle.sql.BFILE"
tableName="" precision="0" scale="0"
isAutoIncrement="false" isCurrency="false"
isWritable="true" isDefinitelyWritable="false"
isNullable="1" isReadOnly="false"
isCaseSensitive="false" isSearchable="false" isSigned="true"
catalog="" schema="" />
</resultSetMetaData>
------------------------------------

Oracle’s Limitations on ResultSetMetaData.getTableName()

Note that the Oracle implementation of ResultSetMetaData does not provide table names for result set metadata. the OracleResultSetMetaData interface does not implement the getSchemaName() and

MySQL Database Setup

The client using MySQL database will use the mypictures table described here:

mysql> describe mypictures;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | | PRI | 0 | |
| name | varchar(20) | YES | | NULL | |
| photo | blob | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Client Using MySQL

import java.util.*;
import java.io.*;
import java.sql.*;
import jcb.db.*;
import jcb.meta.*;
public class TestMySqlResultSetMetaDataTool {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/octopus";
String username = "root";
String password = "root";
Class.forName(driver); // load MySQL driver
return DriverManager.getConnection(url, username, password);
}
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = getConnection();
// Create a result set
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT * FROM mypictures");
System.out.println("-------- getResultSetMetaData -------------");
System.out.println("conn="+conn);
String rsMetaData = ResultSetMetaDataTool.getResultSetMetaData(rs);
System.out.println(rsMetaData);
System.out.println("------------------------------------");
}
catch(Exception e){
e.printStackTrace();
System.exit(1);
}
finally {
DatabaseUtil.close(stmt);
DatabaseUtil.close(rs);
DatabaseUtil.close(conn);
}
}
}

Client Output Using MySQL

-------- getResultSetMetaData -------------
conn=com.mysql.jdbc.Connection@1837697
<?xml version='1.0'>
<resultSetMetaData columnCount="3">
<columnMetaData column="1"
columnDisplaySize="11" columnLabel="id"
columnName="id" columnType="4"
columnTypeName="LONG" columnClassName="java.lang.Integer"
tableName="mypictures" precision="11" scale="0"
isAutoIncrement="false" isCurrency="false"
isWritable="false" isDefinitelyWritable="false"
isNullable="0" isReadOnly="false"
isCaseSensitive="false" isSearchable="true" isSigned="true"
catalog="null" schema="" />
<columnMetaData column="2"
columnDisplaySize="20" columnLabel="name"
columnName="name" columnType="12"
columnTypeName="VARCHAR" columnClassName="java.lang.String"
tableName="mypictures" precision="0" scale="0"
isAutoIncrement="false" isCurrency="false"
isWritable="false" isDefinitelyWritable="false"
isNullable="1" isReadOnly="false"
isCaseSensitive="true" isSearchable="true" isSigned="false"
catalog="null" schema="" />
<columnMetaData column="3"
columnDisplaySize="65535" columnLabel="photo"
columnName="photo" columnType="-4"
columnTypeName="BLOB" columnClassName="java.lang.Object"
tableName="mypictures" precision="0" scale="0"
isAutoIncrement="false" isCurrency="false"
isWritable="false" isDefinitelyWritable="false"
isNullable="1" isReadOnly="false"
isCaseSensitive="true" isSearchable="true" isSigned="false"
catalog="null" schema="" />
</resultSetMetaData>
------------------------------------

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

JDBC Topics