Java DatabaseMetaData getProcedureColumns() method with example

In this article, we will learn the DatabaseMetaData getProcedureColumns() method in Java. In Java getProcedureColumns(), allows us to retrieve information about the input and output parameters of stored procedures in a database.

What is getProcedureColumns()?

The getProcedureColumns() method of DatabaseMetaData retrieves a ResultSet containing details about the parameters (input, output, and return values) of a stored procedure.

Syntax

ResultSet rs = metaData.getProcedureColumns(null, null, "myprocedure", null);

This method retrieves the description of the procedure parameter and results in columns of a database/catalog. It accepts 4 parameters ?

• catalog: The catalog name; pass null to get all procedures.
  
• schemaPattern: The schema name; pass null to get all schemas.
  
• procedureNamePattern:  The procedure name pattern.
  
• columnNamePattern: The column name pattern; null retrieves all columns.

This method returns a ResultSet object describing specified procedure columns. This object holds values for the following details (as column names) ?

Retrieves the Description of the Columns

The following program demonstrates how to use the getProcedureColumns() method. It connects to a MySQL database and retrieves a description of the columns of a stored procedure in the database.

To get the description of the columns of a stored procedure in the database ?

• Make sure your database is up and running.
  
• Register the driver using the registerDriver() method of the DriverManager class. Pass an object of the driver class corresponding to the underlying database.
  
• Get the connection object using the getConnection() method of the DriverManager class. Pass the URL of the database and, the user name, and password of a user in the database, as String variables.
  
• Get the DatabaseMetaData object to the current connection using the getMetaData() method of the Connection interface.
  
• Finally, the get ResultSet object holds the description of the required procedure columns, by invoking the getProcedureColumns() method of the DatabaseMetaData interface.

Registering the Driver: Select the required database and register the Driver class of the particular database using the registerDriver() method of the DriverManager class ?

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

Get connection: Create a connection object by passing the URL of the database, username, and password of a user in the database (in string format) as parameters to the getConnection() method of the DriverManager class ?

DriverManager.getConnection(url, user, password);

Retrieving Metadata: retrieves the DatabaseMetaData object ?

DatabaseMetaData metaData = con.getMetaData();

Fetching Type Information: The ResultSet object holds the description of the required procedure columns, by invoking the getProcedureColumns() method  ?

ResultSet rs = metaData.getProcedureColumns(null, null, "myprocedure", null);

Procedure

Let us create a procedure with the name myprocedure in the MySQL database using the CREATE statement as shown below ?

CREATE PROCEDURE myprocedure(
   IN Product_Name VARCHAR(255),
   IN Name_Of_Customer VARCHAR(255),
   IN Month_Of_Dispatch VARCHAR(255),
   IN Price INT, IN Location VARCHAR(255))
   BEGIN
insert into Dispatches values (
   Product_Name,
   Name_Of_Customer,
   Month_Of_Dispatch,
   Price,
   Location);

Example

Following the JDBC program establishes a connection with MySQL database, and retrieves the description of the procedure parameter and result columns of a catalog ?

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DatabaseMetaData_getProcedureColumns {
   public static void main(String args[]) throws SQLException {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String url = "jdbc:mysql://localhost/mydatabase";
      Connection con = DriverManager.getConnection(url, "root", "password");
      System.out.println("Connection established......");
      //Retrieving the meta data object
      DatabaseMetaData metaData = con.getMetaData();
      //Retrieving the columns in the database
      ResultSet rs = metaData.getProcedureColumns(null, null, "myprocedure", null);
      //Printing the column name and size
      while (rs.next()) {
         System.out.println("Procedure name: "+rs.getString("PROCEDURE_NAME"));
         System.out.println("Column name: "+rs.getString("COLUMN_NAME"));
         System.out.println("Type name: "+rs.getString("TYPE_NAME"));
         System.out.println("Precision: "+rs.getString("PRECISION"));
         System.out.println("Radix: "+rs.getString("RADIX"));
         System.out.println(" ");
      }
   }
}

Output

Connection established......
Procedure name: myprocedure
Column name: Product_Name
Type name: VARCHAR
Precision: 255
Radix: 10
Procedure name: myprocedure
Column name: Name_Of_Customer
Type name: VARCHAR
Precision: 255
Radix: 10
Procedure name: myprocedure
Column name: Month_Of_Dispatch
Type name: VARCHAR
Precision: 255
Radix: 10
Procedure name: myprocedure
Column name: Price
Type name: INT
Precision: 10
Radix: 10
Procedure name: myprocedure
Column name: Location
Type name: VARCHAR
Precision: 255
Radix: 10

Conclusion

The DatabaseMetaData.getProcedureColumns() method is a powerful tool for fetching details about stored procedure parameters. This is useful for database schema analysis and dynamic query generation.