How Do You Get a Table's Metadata Without Selecting Any Rows? JDBC

How do you get table’s metadata without selecting any rows? This is possible by selecting required columns without selecting any records or rows. If you are interested in only the ResultSetMetaData (and not the ResultSet itself ), then select required columns from a table so that the SQL query’s where condition will be false. For example:

select id, name from employees where 1 = 0;

The condition "1 = 0" (as a boolean expression) is always false; therefore, no data will be selected at all, but you will get the result set metadata information.

The Solution

Our solution generates result set metadata for a given table identified with the tableName parameter. Using tableName, we create the following query (note that Oracle requires the table name to be in uppercase characters, while MySQL does not care):

String query = "select * from " + tableName.toUpperCase() + " where 1 = 0";

Then, we execute query (note that our query does not select any records from a given table because the where clause is always false) and then call the getResultSetMetaData➥ (ResultSet rs) method. The reason for conversion of table name to uppercase characters is that some databases (such as Oracle) prefer, or even require, table names in uppercase.

/**
* Get table's 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 conn the Connection object.
* @param tableName the table name.
* @return result set's metadata as an 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 getTableMetaData(Connection conn,
String tableName)
throws Exception {
Statement stmt = null;
ResultSet rs = null;
String query = null;
try {
if ((conn == null) ||
(tableName == null) ||
(tableName.length() == 0)) {
return null;
}
query = "select * from "+ tableName.toUpperCase() + " where 1 = 0";
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
// Retrieves the number, types and properties
// of this ResultSet object's columns.
return getResultSetMetaData(rs);
}
finally {
DatabaseUtil.close(rs);
DatabaseUtil.close((stmt);
}
}

Oracle Database Setup

$ sqlplus octopus/octopus
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Feb 26 17:38:26 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> describe zdepts;
Name Null? Type
----------------- -------- ------------
DEPT NOT NULL VARCHAR2(32)
NAME NOT NULL VARCHAR2(32)
LOCATION NOT NULL VARCHAR2(64)
COSTCENTER CHAR(32)

Client Using an Oracle Table

import java.util.*;
import java.io.*;
import java.sql.*;
import jcb.db.*;
import jcb.meta.*;
public class TestOracleTableResultSetMetaDataTool {
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;
try {
conn = getConnection();
System.out.println("-------- getResultSetMetaData -------------");
System.out.println("conn="+conn);
String deptTableName = "zdepts";
String rsMetaData =
ResultSetMetaDataTool.getTableMetaData(conn, deptTableName);
System.out.println(rsMetaData);
System.out.println("------------------------------------");
}
catch(Exception e){
e.printStackTrace();
System.exit(1);
}
finally {
DatabaseUtil.close(conn);
}
}
}

Output Using an Oracle Table

-------- getTableMetaData -------------
conn=oracle.jdbc.driver.OracleConnection@169ca65
<?xml version='1.0'>
<resultSetMetaData columnCount="4">
<columnMetaData column="1" columnDisplaySize="32"
columnLabel="DEPT" columnName="DEPT"
columnType="12" columnTypeName="VARCHAR2"
columnClassName="java.lang.String" tableName=""
precision="32" scale="0"
isAutoIncrement="false" isCurrency="false"
isWritable="true" isDefinitelyWritable="false"
isNullable="0" isReadOnly="false"
isCaseSensitive="true" isSearchable="true"
isSigned="true" catalog="" schema="" />
<columnMetaData column="2" columnDisplaySize="32"
columnLabel="NAME" columnName="NAME"
columnType="12" columnTypeName="VARCHAR2"
columnClassName="java.lang.String" tableName=""
precision="32" scale="0"
isAutoIncrement="false" isCurrency="false"
isWritable="true" isDefinitelyWritable="false"
isNullable="0" isReadOnly="false"
isCaseSensitive="true" isSearchable="true"
isSigned="true" catalog="" schema="" />
<columnMetaData column="3" columnDisplaySize="64"
columnLabel="LOCATION" columnName="LOCATION"
columnType="12" columnTypeName="VARCHAR2"
columnClassName="java.lang.String" tableName=""
precision="64" scale="0"
isAutoIncrement="false" isCurrency="false"
isWritable="true" isDefinitelyWritable="false"
isNullable="0" isReadOnly="false"
isCaseSensitive="true" isSearchable="true"
isSigned="true" catalog="" schema="" />
<columnMetaData column="4" columnDisplaySize="32"
columnLabel="COSTCENTER" columnName="COSTCENTER"
columnType="1" columnTypeName="CHAR"
columnClassName="java.lang.String" tableName=""
precision="32" scale="0"
isAutoIncrement="false" isCurrency="false"
isWritable="true" isDefinitelyWritable="false"
isNullable="1" isReadOnly="false"
isCaseSensitive="true" isSearchable="true"
isSigned="true" catalog="" schema="" />
</resultSetMetaData>
------------------------------------
MySQL Database Setup
mysql> describe zperson;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| photo | blob | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.03 sec)

Client Using a MySQL Table

import java.util.*;
import java.io.*;
import java.sql.*;
import jcb.db.*;
import jcb.meta.*;
public class TestMySqlTableResultSetMetaDataTool {
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;
try {
conn = getConnection();
System.out.println("-------- getTableMetaData -------------");
System.out.println("conn="+conn);
String deptTableName = "zperson";
String rsMetaData =
ResultSetMetaDataTool.getTableMetaData(conn, deptTableName);
System.out.println(rsMetaData);
System.out.println("------------------------------------");
}
catch(Exception e){
e.printStackTrace();
System.exit(1);
}
finally {
DatabaseUtil.close(conn);
}
}
}

Output Using a MySQL Table

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