JDBC Statement – DatabaseMetaData GetProcedures Example

Java

Using the DatabaseMetaData interface you can obtain meta data all the procedures in the entire database or as is my case for a particular (Catalog and Schema). In this tutorial, we will specifically use getProcedures() to get details about stored procedures contained within the database. We will be using the getTables() method, passing in the following four parameters (“tutorial”, “dbo”, “%”, null). If you would prefer to get a list of all tables/views in the entire database then you can pass in null for all four parameters.

Common Methods used with DatabaseMetaData

Method NameDescription
getDriverName()Retrieves the name of this JDBC driver
getDriverVersion()Retrieves the version number of this JDBC driver as a String
getDatabaseProductName()Retrieves the name of this database product
getDatabaseProductVersion()Retrieves the version number of this database product
getProcedures(String catalog, String schemaPattern, String procedureNamePattern)Retrieves a description of the stored procedures available in the given catalog
getSchemas(String catalog, String schemaPattern)Retrieves the schema names available in this database
getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types)Retrieves a description of the tables available in the given catalog

Using the DatabaseMetaData instance, getProcedures() Method

DatabaseMetaData metadata = connection.getMetaData();
System.out.println("nList of Stored Procedures Available for 'dbo'...n");
ResultSet rs = metadata.getProcedures("tutorial", "dbo", null);
while (rs.next()) {
  System.out.format("%-15s %-10s %-40sn", rs.getString(1), rs.getString(2), rs.getString(3));
}

Creating Stored Proc DDL for SQL Server

CREATE PROCEDURE dbo.get_all_employees
as
BEGIN
  SELECT employee_id, first_name, last_name, pay_type, pay_rate, start_date, end_date
  FROM dbo.employee
  WHERE end_date is null
END

Full Program Listing

package com.avaldes.tutorials;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCDatabaseMetaDataProceduresExample {

  public static void main(String[] args)  throws SQLException {
    String database_url   = "jdbc:sqlserver://localhost:1433;databaseName=tutorial";
    String username     = "webuser";
    String password     = "webuser123";
    Connection connection   = null;
    
    try {
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    } catch (ClassNotFoundException e) {
      System.out.println("ERROR: Unable to load SQLServer JDBC Driver");
      e.printStackTrace();
      return;
    }
   
    try {
      connection = DriverManager.getConnection(database_url, username, password);
    } catch (SQLException e) {
      System.out.println("ERROR:  Unable to establish a connection with the database!");
      e.printStackTrace();
      return;
    }
   
    try {
      if (connection != null) {
        DatabaseMetaData metadata = connection.getMetaData();
          System.out.println("nList of Stored Procedures Available for 'dbo'...n");
          System.out.format("%-15s %-10s %-40sn", "DATABASE", "SCHEMA", "PROCEDURE NAME");
          System.out.format("%-15s %-10s %-40sn", "-------------", "--------", "--------------------------------------------");
          ResultSet rs = metadata.getProcedures("tutorial", "dbo", null);
          //ResultSet rs = metadata.getTables(null, null, null);
          while (rs.next()) {
            System.out.format("%-15s %-10s %-40sn", rs.getString(1), rs.getString(2), 
                      rs.getString(3));
          }
          
      } else {
        System.out.println("ERROR: Unable to make a database connection!");
      }
      
      
    } catch (SQLException e) {
      e.printStackTrace();
      return;
    } finally {
        if (connection != null) connection.close();
    }
  }
}

Output

List of Stored Procedures Available for 'dbo'...

DATABASE        SCHEMA     PROCEDURE NAME                          
-------------   --------   --------------------------------------------
tutorial        dbo        get_all_employees;1   

Please Share Us on Social Media

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Leave a Reply

Your email address will not be published. Required fields are marked *