JDBC for MySQL - Herong's Tutorial Examples - v3.13, by Herong Yang
getProcedures() - Listing Stored Procedures
This section describes how to get a list of stored procedures in the database server.
If you want to get a list of stored procedures through the JDBC interface, you can use the getProcedures() method on the DatabaseMetaData object as shown in the program program below:
/* MySqlCallGetProcedures.java
* Copyright (c) HerongYang.com. All Rights Reserved.
*/
import java.sql.*;
public class MySqlCallGetProcedures {
public static void main(String [] args) {
Connection con = null;
try {
com.mysql.cj.jdbc.MysqlDataSource ds
= new com.mysql.cj.jdbc.MysqlDataSource();
ds.setServerName("localhost");
ds.setPortNumber(3306);
ds.setDatabaseName("HerongDB");
ds.setUser("Herong");
ds.setPassword("TopSecret");
ds.setServerTimezone(java.util.TimeZone.getDefault().getID());
con = ds.getConnection();
DatabaseMetaData meta = con.getMetaData();
// Listing all stored procedures
ResultSet res = meta.getProcedures(null, null, "%");
System.out.println("Stored procedures:");
while (res.next()) {
System.out.println(
" "+res.getString("PROCEDURE_CAT")
+ ", "+res.getString("PROCEDURE_SCHEM")
+ ", "+res.getString("PROCEDURE_NAME"));
}
res.close();
// Listing all tables
res = meta.getTables(null, null, "%", null);
System.out.println("Stored tables:");
while (res.next()) {
System.out.println(
" "+res.getString("TABLE_CAT")
+ ", "+res.getString("TABLE_SCHEM")
+ ", "+res.getString("TABLE_NAME"));
}
res.close();
con.close();
} catch (Exception e) {
System.err.println("Exception: "+e.getMessage());
e.printStackTrace();
}
}
}
Here is the output with MySQL Connector/J 8.0.19 driver:
herong> java -cp mysql-connector-java.jar \ MySqlCallGetProcedures.java Stored procedures: HerongDB, null, C2F HerongDB, null, HeadTail HerongDB, null, HelloWorld HerongDB, null, Info HerongDB, null, ReverseProcedure sys, null, create_synonym_db sys, null, diagnostics sys, null, execute_prepared_stmt sys, null, extract_schema_from_file_name sys, null, extract_table_from_file_name ... Stored tables: HerongDB, null, profile information_schema, null, CHARACTER_SETS information_schema, null, CHECK_CONSTRAINTS information_schema, null, COLLATION_CHARACTER_SET_APPLICABILITY information_schema, null, COLLATIONS information_schema, null, COLUMN_PRIVILEGES information_schema, null, COLUMN_STATISTICS information_schema, null, COLUMNS information_schema, null, ENGINES ...
Note that the same program with MySQL Connector/J 5.1.36 driver running JDK 8 gives me a shorter list.
herong> java -cp .;mysql-connector-java-5.1.36-bin.jar \ MySqlCallGetProcedures Stored procedures: herongdb, null, C2F herongdb, null, HeadTail herongdb, null, HelloWorld herongdb, null, Info herongdb, null, ReverseProcedure Stored tables: HerongDB, null, profile
Note that the same program with MySQL Connector/J 5.0.7 driver gave me the following empty output. This could be a bug in the driver.
herong> java -cp .;mysql-connector-java-5.0.7-bin.jar \ MySqlCallGetProcedures Stored procedures: Stored tables: HerongDB, null, profile
Table of Contents
JDBC (Java Database Connectivity) Introduction
MySQL JDBC Driver (MySQL Connector/J)
MySQL - Reference Implementation of JdbcRowSet
►MySQL - JBDC CallableStatement
Overview of CallableStatement Objects
"CREATE PROCEDURE" - Creating a Simple Procedure
Creating Procedures with IN and OUT Parameters
Creating Procedures with INOUT Parameters
Creating Procedures with Multiple Queries
Creating CallableStatement Objects with prepareCall()
Capturing ResultSet with executeQuery()
Creating CallableStatement Objects with Parameters
Common Errors with CallableStatement Parameters
Creating CallableStatement Objects with INOUT Parameters
Retrieving Multiple ResultSet Objects
Executing Stored Procedures without Permission
►getProcedures() - Listing Stored Procedures
MySQL CLOB (Character Large Object) - TEXT
MySQL BLOB (Binary Large Object) - BLOB