How Do You Get Information from a ParameterMetaData Object? JDBC

After you create a ParameterMetaData object, you may use the
ParameterMetaData’s methods for getting information on the number, mode, nullability, and type of parameters, as well as other useful related information.

To use a ParameterMetaData object, perform these basic steps:

  1. Create a ParameterMetaData object (see Section 5.3).
  2. Invoke ParameterMetaData.getParameterCount() to determine the number of parameters in the PreparedStatement. Once you have the number of parameters for a PreparedStatement object, then use the following code snippet to obtain parameter information (the first parameter is 1, the second is 2, etc.): for (int param=1; param <= numberOfParameters; param++) {
    method-information = ParameterMetaData.<method-name>(param);
    }
  3. Invoke ParameterMetaData methods on individual parameters.

For example, the following snippet shows how to get the number of parameters and the type of each parameter. Each line of code uses paramMetaData, created in the previous code fragment, to get the number of parameters in the PreparedStatement object pstmt:

PreparedStatement pstmt = <a-valid-PreparedStatement-object>;
paramMetaData = pstmt.getParameterMetaData();
if (paramMetaData == null) {
System.out.println("db vendor does NOT support ParameterMetaData");
}
else {
System.out.println("db vendor supports ParameterMetaData");
// find out the number of dynamic parameters
int paramCount = paramMetaData.getParameterCount();
System.out.println("paramCount="+paramCount);
for (int param=1; param <= paramCount; param++) {
int sqlTypeCode = paramMetaData.getParameterType(param);
System.out.println("param number="+param);
System.out.println("param SQL type="+ sqlTypeCode);
}
}

The method ParameterMetaData.getParameterCount() is the only method in the ParameterMetaData interface that takes no parameters. All of the other methods take an integer(Java’s int primitive data type) that indicates the position of the parameter to which the information applies. Parameter position numbering starts at one, so the first parameter is 1, the second parameter is 2, and so on. For example, in the following line of code, the method ParameterMetaData.getParameterType() returns the SQL data type of the second parameter(provided that the PreparedStatement that produced ParameterMetaData has at least two parameters):

int sqlTypeCode = paramMetaData.getParameterType(2);
if (sqlTypeCode == java.sql.Types.BLOB) {
// parameter is a SQL type BLOB

}
else if (sqlTypeCode == java.sql.Types.VARCHAR) {
// parameter is a SQL type VARCHAR

}

The parameter type obtained from the method
ParameterMetaData.getParameterType() maps into constants defined in the java.sql.Types class (the Types class defines the constants that are used to identify generic SQL types, called JDBC types).

The Solution for HSQLDB

The following solution shows how to use ParameterMetaData methods to get information about a PreparedStatement object:

import java.util.*;
import java.io.*;
import java.sql.*;
import jcb.util.DatabaseUtil;
public class ExamineParameterMetaData_HSQLDB {
public static Connection getConnection(String dbName)
throws Exception {
// load the HSQL Database Engine JDBC driver
// hsqldb.jar should be in the class path
Class.forName("org.hsqldb.jdbcDriver");
// connect to the database. This will load the
// db files and start the database if it is not
// already running. dbName is used to open or
// create files that hold the state of the db.
return DriverManager.getConnection("jdbc:hsqldb:"
+ dbName, // filename
"sa", // username
""); // password
}
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
ParameterMetaData paramMetaData = null;
String query = "select id, str_col, num_col " +
"from sample_table where id > ? and str_col = ? and num_col = ?";
try {
conn = getConnection("db_file"); // db file name
System.out.println("conn="+conn);
pstmt = conn.prepareStatement(query);
paramMetaData = pstmt.getParameterMetaData();
if (paramMetaData == null) {
System.out.println("db vendor does NOT support ParameterMetaData");
}
else {
System.out.println("db vendor supports ParameterMetaData");
// find out the number of dynamic parameters
int paramCount = paramMetaData.getParameterCount();
System.out.println("paramCount="+paramCount);
System.out.println("-------------------");
for (int param=1; param <= paramCount; param++) {
System.out.println("param number="+param);
int sqlTypeCode = paramMetaData.getParameterType(param);
System.out.println("param SQL type code="+ sqlTypeCode);
String paramTypeName = paramMetaData.getParameterTypeName(param);
System.out.println("param SQL type name="+ paramTypeName);
String paramClassName = paramMetaData.getParameterClassName(param);
System.out.println("param class name="+ paramClassName);
int paramMode = paramMetaData.getParameterMode(param);
System.out.println("param mode="+ paramMode);
if (paramMode == ParameterMetaData.parameterModeOut){
System.out.println("the parameter's mode is OUT.");
}
else if (paramMode == ParameterMetaData.parameterModeIn){
System.out.println("the parameter's mode is IN.");
}
else if (paramMode == ParameterMetaData.parameterModeInOut){
System.out.println("the parameter's mode is INOUT.");
}
else {
System.out.println("the mode of a parameter is unknown.");
}
int nullable = paramMetaData.isNullable(param);
if (nullable == ParameterMetaData.parameterNoNulls){
System.out.println("parameter will not allow NULL values.");
}
else if (nullable == ParameterMetaData.parameterNullable){
System.out.println("parameter will allow NULL values.");
}
else {
System.out.println("nullability of a parameter is unknown.");
}
System.out.println("-------------------");
}
}
}
catch(Exception e){
e.printStackTrace();
System.exit(1);
}
finally {
// release database resources
DatabaseUtil.close(pstmt);
DatabaseUtil.close(conn);
}
}
}

Running the Solution for HSQLDB

java ExamineParameterMetaData_HSQLDB
conn=org.hsqldb.jdbc.jdbcConnection@c7e553
db vendor supports ParameterMetaData
paramCount=3
-------------------
param number=1
param SQL type code=4
param SQL type name=INTEGER
param class name=java.lang.Integer
param mode=1
the parameter's mode is IN.
parameter will not allow NULL values.
-------------------
param number=2
param SQL type code=12
param SQL type name=VARCHAR
param class name=java.lang.String
param mode=1
the parameter's mode is IN.
parameter will allow NULL values.
-------------------
param number=3
param SQL type code=4
param SQL type name=INTEGER
param class name=java.lang.Integer
param mode=1
the parameter's mode is IN.
parameter will allow NULL values.
-------------------

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

JDBC Topics