Java DatabaseMetaData getProcedures() method with example

In this article, we will learn the DatabaseMetaData getProcedures() method in Java. In Java, the DatabaseMetaData.getProcedures() method is used to retrieve information about stored procedures available in a database.

What is getProcedures()?

The getProcedures() method of the DatabaseMetaData interface returns a ResultSet containing metadata about stored procedures available in a database. This allows developers to query the database for procedure names, catalog information, procedure types, and specific names.

Syntax

ResultSet procedures = metaData.getProcedures(null, null, "myprocedure");

This method retrieves the description of the stored procedures of a database/catalog. It accepts 3 parameters ?

• catalog: The catalog name of the procedure. If null, it is ignored.
  
• schemaPattern: The schema name pattern. If null, it is ignored.
  
• procedureNamePattern: The procedure name pattern. If null, all procedures are returned.

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

Retrieving Stored Procedure Information

The following program demonstrates how to use the getProcedures() method. It connects to a MySQL database and retrieves information about stored procedures available in a database.

To get the description of the stored procedures 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 for the current connection using the getMetaData() method of the Connection interface.
  
• Finally, to get the ResultSet object holding the description of the required procedure, by invoking the getProcedures() 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: Fetches metadata about the procedure named "myprocedure" and stores it in the ResultSet ?

ResultSet procedures = metaData.getProcedures(null, null, "myprocedure");

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 the MySQL database, and retrieves the description of the above-created procedure.

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DatabaseMetaData_getProcedures {
   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 procedures = metaData.getProcedures(null, null, "myprocedure");
      //Printing the column name and size
      while (procedures.next()) {
         System.out.println("Catalog name: "+procedures.getString("PROCEDURE_CAT"));
         System.out.println("Procedure name: "+procedures.getString("PROCEDURE_NAME"));
         System.out.println("Type of the procedure: "+procedures.getShort("PROCEDURE_TYPE"));
         System.out.println("Specific name of the procedure: "+procedures.getString("SPECIFIC_NAME"));
         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.getProcedures() method provides an efficient way to retrieve stored procedure metadata from a database. Using this method, developers can programmatically access details about stored procedures and use them to build robust, database-independent applications.