Stored Procedure
Stored procedure is a program which is physically stored within a database. All the databases support by DBVA support the stored procedure. Different databases have different syntaxes to create stored procedure. In the following example, we will use the MySQL to create the stored procedure, reverse by DBVA and then call stored procedure by generate program.
Create the following table, records, procedures and function in MySQL database.
CREATE TABLE stock_prices(ric varchar(6) PRIMARY KEY, price decimal(7,2), updated date);
INSERT INTO stock_prices VALUES('MSFT', 69.20, NOW());
INSERT INTO stock_prices VALUES('RSAS', 30.18, NOW());
INSERT INTO stock_prices VALUES('AMZN', 15.50, NOW());
INSERT INTO stock_prices VALUES('SUNW', 16.25, NOW());
INSERT INTO stock_prices VALUES('ORCL', 14.50, NOW());
delimiter //
CREATE procedure sp_get_stocks(IN v_price decimal)
BEGIN
SELECT ric, updated FROM stock_prices
WHERE price < v_price;
END;
//
CREATE procedure sp_count_stocks(OUT param1 int, INOUT param2 varchar(100))
BEGIN
SELECT count(*) INTO param1 FROM stock_prices;
SELECT 'finish' INTO param2;
END;
//
CREATE FUNCTION hello(s CHAR(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!')
//
delimiter ;
By executing the query above, you have created the stock_price table in MySQL database.
| Column name | type |
| ric | varchar(6) |
| price | decimal(7,2) |
| updated | date |
You also created the
sp_get_stocks procedure, you can find out the stock_price record by your input price. The
sp_count_stocks can return the total number of record inside the stock_prices table and return the status message. The hello function allow the user to input the string and return the ‘Hello’ message.
Reversing stored procedure from database
You can create the new project to reverse the table and stored procedures from the MySQL database.
Select
Tools >
Object-Relationship Mapping (ORM) >
Reverse Database… from the menu.
Select
Java Language, click
Next>.
Enter the information to connection MySQL database and select
Set as default checkbox, click
Next>
Select the
stock_prices table, click
Next>.
Click the
Select All button to select all the stored procedures to be reversed from the MySQL database, click
Finish.
The Entity Relational Diagram (ERD) is created.
Design the ResultSet in ERD
The ResultSet from the get_stock_price procedure cannot reverse from database, so if you want to achieve the value from the stored procedure(get_stock_price), you need to design the ResultSet for the stored procedure. The ResultSet include the field of ric, updated in stock_prices table, so they are very similar.
Select
Stored Procedure ResultSet on the ToolBar.
Create
stockRS ResultSet on the ERD and create column
ric and
updated.
Right click
sp_get_stocks(v_price decimal) procedure and select
Open Specification… to open the
Procedure Specification dialog .
Select the
stockRS for
Return resultset option.
Generate class diagram from ERD
Now the ResultSet is added for the sp_get_stocks procedure in ERD, you can generate class diagram from ERD.
Select
Tools >
Object-Relational Mapping (ORM) >
Synchronize to Class Diagram on the menu.
The Class Diagram is created. It include the
«SP Result» stereotype class to store multiple result values from the
sp_count_stocks procedure. The
«SP ResultSet» stereotype class to represent the ResultSet in ERD for the
sp_get_stocks procedure. Set the default package to
test.
Generate code
The Class Diagram is created. You can generate the Java code to call the store procedure of the MySQL database.
Select
Tools >
Object-Relational Mapping (ORM) >
Generate Code... to open
Database Code Generation dialog.
Select
Java language and configure
Output Path. You can de-select
Generate criteria,
Sample,
Ant file and
Batch file because they are not the necessary files for call stored procedure.
The
StoredProcedures Class is created, it consists methods to call reversed stored procedures.
/**
* "Visual Paradigm: DO NOT MODIFY THIS FILE!"
*
* This is an automatic generated file. It will be regenerated every time
* you generate persistence class.
*
* Modifying its content may cause the program not work, or your work may lost.
*/
/**
* Licensee: Demo
* License Type: Purchased
*/
package test;
import java.sql.*;
import org.orm.*;
/**
* ORM-Persistable Class
*/
public class StoredProcedures {
public static String hello(String aS) throws PersistentException, SQLException {
return hello(aS, test.UntitledPersistentManager.instance().getSession());
}
public static String hello(String aS, PersistentSession session) throws PersistentException,
SQLException {
CallableStatement lCallStmt = null;
String lReturnVal = null;
try {
Connection lConn = session.connection();
lCallStmt = lConn.prepareCall("{? = call hello(?)}");
lCallStmt.setString(1, aS);
lCallStmt.registerOutParameter(1, 1111);
lCallStmt.execute();
lReturnVal = lCallStmt.getString(1);
}
catch(Exception e) {
throw new PersistentException(e);
}
finally {
if(lCallStmt!= null)
lCallStmt.close();
}
return lReturnVal;
}
public static test.Sp_count_stocksResult sp_count_stocks(String aParam2) throws PersistentException,
SQLException {
return sp_count_stocks(aParam2, test.UntitledPersistentManager.instance().getSession());
}
public static test.Sp_count_stocksResult sp_count_stocks(String aParam2, PersistentSession session)
throws PersistentException, SQLException {
CallableStatement lCallStmt = null;
test.Sp_count_stocksResult lSp_count_stocksResult = new test.Sp_count_stocksResult();
try {
Connection lConn = session.connection();
lCallStmt = lConn.prepareCall("{call sp_count_stocks(?, ?)}");
lCallStmt.setString(2, aParam2);
lCallStmt.registerOutParameter(1, 4);
lCallStmt.registerOutParameter(2, 12);
lCallStmt.execute();
lSp_count_stocksResult.setParam1(lCallStmt.getInt(1));
lSp_count_stocksResult.setParam2(lCallStmt.getString(2));
}
catch(Exception e) {
throw new PersistentException(e);
}
finally {
if(lCallStmt!= null)
lCallStmt.close();
}
return lSp_count_stocksResult;
}
public static test.StockRS sp_get_stocks(java.math.BigDecimal aV_price) throws PersistentException {
return sp_get_stocks(aV_price, test.UntitledPersistentManager.instance().getSession());
}
public static test.StockRS sp_get_stocks(java.math.BigDecimal aV_price, PersistentSession session)
throws PersistentException {
CallableStatement lCallStmt = null;
test.StockRS lStockRS = null;
try {
Connection lConn = session.connection();
lCallStmt = lConn.prepareCall("{call sp_get_stocks(?)}");
lCallStmt.setBigDecimal(1, aV_price);
lCallStmt.execute();
lStockRS = new test.StockRS(lCallStmt, lCallStmt.getResultSet());
}
catch(Exception e) {
throw new PersistentException(e);
}
return lStockRS;
}
}
Calling stored procedure
The StoredProcedures class is created. You can create a TestSP class and write a main method for try to call the procedure by the StoredProcedures class’s methods.
This is the TestSP class in test package:
package test;
import java.math.*;
public class TestSP {
public static void main(String[] args) {
System.out.println("=== call hello stored procedure ===");
try {
System.out.println("hello result: " + StoredProcedures.hello("world"));
} catch (Exception e) {
e.printStackTrace();
}
System.out.println();
System.out.println("=== call sp_count_stocks stored procedure ===");
try {
Sp_count_stocksResult lResult = StoredProcedures.sp_count_stocks("initial");
System.out.println("param1 : " + lResult.getParam1());
System.out.println("param2 : " + lResult.getParam2());
} catch (Exception e) {
e.printStackTrace();
}
System.out.println();
System.out.println("=== call sp_get_stocks stored procedure ===");
try {
StockRS lStockRS = StoredProcedures.sp_get_stocks(new BigDecimal(16));
while(lStockRS.next()){
System.out.println(lStockRS.getRic());
System.out.println(lStockRS.getUpdated());
}
StockRS.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
Calling
hello function, it will return single String value only to add prefix “Hello” for your input String.
System.out.println("=== call hello stored procedure ===");
try {
System.out.println("hello result: " + StoredProcedures.hello("world"));
} catch (Exception e) {
e.printStackTrace();
} Calling
sp_count_stock, it receives one String parameter and return the total number stock in stock_prices table. The return result is stored in
Sp_count_stocksResult Class.
System.out.println("=== call sp_count_stocks stored procedure ===");
try {
Sp_count_stocksResult lResult = StoredProcedures.sp_count_stocks("initial");
System.out.println("param1 : " + lResult.getParam1());
System.out.println("param2 : " + lResult.getParam2());
} catch (Exception e) {
e.printStackTrace();
} Calling sp_get_stocks procedure, it will find the stock price less than the user input v_price and return multiple result by result set (StockRS class). It is similar with java.sql.ResultSet. You can call
next() method to Moves the cursor down one row from its current position and call
close() method to release resource finally.
System.out.println("=== call sp_get_stocks stored procedure ===");
try {
StockRS lStockRS = StoredProcedures.sp_get_stocks(new BigDecimal(16));
while(lStockRS.next()){
System.out.println(lStockRS.getRic());
System.out.println(lStockRS.getUpdated());
}
lStockRS.close();
} catch (Exception e) {
e.printStackTrace();
}
You can execute the TestSP class to call stored procedure in MySQL database but you must set the orm.jar and mysql.jar to the classpath when compile or execute. The result of execute the TestSP class:
=== call hello stored procedure ===
hello result: Hello, world!
=== call sp_count_stocks stored procedure ===
param1 : 5
param2 : finish
=== call sp_get_stocks stored procedure ===
AMZN
2006-04-18
ORCL
2006-04-18
Downloads
VP-UML Project file
-
Complete source
Supported Stored Procedure Databases
Resources
