This post is about Java stored procedures in Java DB.<\/p>\n
Java DB is a relational database management system that is based on the Java programming language and SQL. This is the Oracle release of the Apache Software Foundation’s open source Derby project. Java DB is included in the Java SE 7 SDK.<\/p>\n
Java code invoked within the database is a stored procedure (or procedure). Java stored procedures are database side JDBC (Java Database Connectivity) routines.<\/p>\n
The procedure code is defined in a Java class method and stored in the database. This is executed using SQL. The procedure code can be with or without any database related code.<\/p>\n
Other database side (or server side) programs are triggers and table functions.<\/p>\n
There are two types of stored procedures, based on the transaction in which they are invoked in: nested connections and non-nested connections.<\/p>\n
This type of procedure uses the same transaction as that of the SQL statement that called it. The procedure code uses the same connection as that of the parent SQL, using the connection URL syntax Note that the connection URL attributes are not supported for this type.<\/p>\n Example code at: 2.1 Creating<\/em>.<\/p>\n This type of procedure uses a new database connection. The procedure is executed in a different transaction than that of the calling SQL.<\/p>\n The stored procedure code can also connect to a different database.<\/p>\n Example code at: 3.1 Using a Non-nested Connection<\/em>.<\/p>\n SQL exceptions in procedures can be caught and handled within the procedure code, or propagated (and caught) in the calling program.<\/p>\n This describes the creating a Java stored procedure in Java DB database and using it interactively in SQL as also in Java code. The stored procedure code is created using the Java programming language. The procedure is Java code in a method with signature The procedure is invoked (or called) using a SQL command, or from a Java program using JDBC API.<\/p>\n Create a Java method, compile it, and store the procedure in database.<\/p>\n The following is an example method.<\/p>\n The code is created in a Java class, for example In the example code:<\/p>\n The procedure is created in the database using the CREATE PROCEDURE statement. This command is run interactively using The command syntax and details is as follows:<\/p>\n In the example, the procedure To list the procedures in the database, use the command SHOW PROCEDURES.<\/p>\n To change a procedure, delete the procedure from the database and create again.<\/p>\n Example for deleting a procedure using A procedure is run interactively using the SQL CALL command or from a client program using JDBC API.<\/p>\n The CALL SQL command supports only IN parameters. The JDBC API\u2019s The CALL statement is used to invoke a procedure. This does not return a value. Only procedures with IN parameters are supported when invoked using the CALL.<\/p>\n The following example shows a CALL command run from ij to invoke the procedure This example code invokes the procedure (PROC_NAME) created earlier (2.1 Creating).<\/p>\n The code uses JDBC API\u2019s Java JDBC API\u2019s The API provides a stored procedure SQL escape syntax that allows procedures to be called in a standard way for all RDBMSs. Syntax has one form that includes a result parameter and one that does not. If used, the result parameter must be registered as an OUT parameter. The other parameters (arg1, arg2, …) can be used for input, output or both.<\/p>\n The following are the syntax (with and without return value, respectively):<\/p>\n IN parameter values are set using the setter methods inherited from the The parameter attributes IN (the default), OUT, and INOUT are parameter modes.<\/p>\n To call a stored procedure, use execute(), executeQuery(), or executeUpdate() methods depending on how many The first parameter 1<\/em> is an IN parameter.<\/p>\n The second parameter has the parameter mode INOUT. It\u2019s IN value is specified by calling the setter method 2a<\/em> and register the OUT type with the registerOutParameter() method 2b<\/em>. The output value is retrieved by the getter method 2c<\/em>.<\/p>\n There are two examples: the first shows code to create and use a non-nested type procedure, and the next is an example usage of a Java DB\u2019s pre-defined procedure.<\/p>\n In this example, the Java procedure accesses a different database and connection, than the one used in the calling program. The procedure returns an OUT integer parameter value.<\/p>\n The procedure element READS SQL DATA specifies that the SQL in procedure method can only use SELECT statements.<\/li>\n SYSCS_UTIL.SYSCS_BACKUP_DATABASE is a pre-defined and Java DB specific system procedure. This backs up the database to a specified directory. The syntax is SYSCS_BACKUP_DATABASE(IN backupDirPath VARCHAR). The procedure does not return a value.<\/p>\n The following example SQL command invokes the procedure:<\/p>\n Note:<\/strong> A detailed example Java code can be found in the blog post titled Backing up a Java DB Database<\/em> at http:\/\/www.javaquizplayer.com\/blogposts\/blogpost4.html<\/a><\/p>\n Oracle\u2019s 10g and HyperSQL DataBase (HSQLDB) are some of the other databases that support Java stored procedures.<\/p>\n 1 Java Stored Procedure This post is about Java stored procedures in Java DB. Java DB is a relational database management system that is based on the Java programming language and SQL. This is the Oracle release of the Apache Software Foundation’s open source Derby project. Java DB is included in the Java SE 7 …<\/p>\n","protected":false},"author":469,"featured_media":148,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-16824","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-core-java"],"yoast_head":"\njdbc:default:connection<\/code>. The following is an example code bit to get a connection:<\/p>\nConnection c = DriverManager.getConnection(\"jdbc:default:connection\");<\/pre>\n
Non-nested Connections<\/h4>\n
1.2 SQL Exceptions in Procedures<\/h3>\n
2 Create and Use a Java Stored Procedure<\/h2>\n
public static void procedureMethod<\/code>. The stored procedure is created and stored in the Java DB database as a database object.<\/p>\n2.1 Creating<\/h3>\n
2.1.1 Create a Java Method<\/h4>\n
public static void testProc(int iParam1, String iParam2, int [] oParam)\r\n throws SQLException {\r\n String connectionURL = \"jdbc:default:connection\";\r\n Connection conn = DriverManager.getConnection(connectionURL);\r\n String DML = \"UPDATE TEST_TABLE SET NAME = ? WHERE ID = ?\";\r\n PreparedStatement pstmnt = conn.prepareStatement(DML);\r\n pstmnt.setString(1, iParam2);\r\n pstmnt.setInt(2, iParam1);\r\n int updateRowcount = pstmnt.executeUpdate();\r\n oParam [0] = updateRowcount;\r\n} \/\/ testProc()\r\n<\/pre>\nJavaStoredProcs.java<\/code>, and compiled. Any number of procedure methods can be created within a class.<\/p>\n\n
2.1.2 Create a Procedure in Database<\/h4>\n
ij<\/code>, or from a Java program using JDBC API\u2019s java.sql.Statement<\/code> interface.CREATE PROCEDURE procedure-Name(ProcedureParameters)ProcedureElements<\/pre>\n
procedure-Name<\/code>:<\/strong> is the procedure name as stored in the database; is created in the default schema, if not specified.<\/p>\nProcedureParameters<\/code>:<\/strong> specifies the parameter mode (IN, INOUT or OUT), an optional name and the data type. The data type is of the database data type. Java DB does not support long column types (for example Long Varchar, BLOB, \u2026) in procedures. Parameters are optional.<\/p>\nProcedureElements:<\/code><\/strong> This must contain the following three elements, and can have additional optional ones.<\/p>\n\n
ClassName.methodName Optional, procedure elements<\/code>:<\/li>\n\n
2.1.2.1 Create Procedure in Database Interactively Using
ij<\/code><\/h4>\nij<\/code> is a command line tool included with Java DB. ij<\/code> is a JDBC tool used to run interactive queries on a Java DB database.<\/p>\nij> CONNECT 'jdbc:derby:testDB';\r\nij> CREATE PROCEDURE PROC_NAME(IN id1 INTEGER, IN name2 VARCHAR(50), OUT count3 INTEGER) LANGUAGE JAVA EXTERNAL NAME 'JavaStoredProcs.testProc' PARAMETER STYLE JAVA;\r\n<\/pre>\n
PROC_NAME<\/code> is created in the testDB<\/code> database. The Java method created earlier (2.1.1 Create a Java Method<\/em>), is specified as the EXTERNAL NAME.<\/p>\n2.2.2 Change or Delete a Procedure<\/h4>\n
ij<\/code>:<\/p>\nij> DROP PROCEDURE procedureName;<\/pre>\n
2.2 Using (Invoking)<\/h3>\n
CallableStatement<\/code> interface is used to invoke a procedure with IN, INOUT or OUT parameters.<\/p>\n2.2.1 CALL SQL Statement<\/h4>\n
MyProc. MyProc<\/code> is the name of a procedure as defined in the database using CREATE PROCEDURE.<\/p>\nij> CALL MyProc();<\/pre>\n
2.2.2 Invoke Procedure from a Java Program<\/h4>\n
CallableStatement<\/code> interface (more details at 2.2.3 Notes on CallableStatement<\/em>). The input parameters to the procedure are set and the out parameter value is printed at the end of this example method. Note that this Java class is different than that of the class in which the procedure is created.<\/p>\nprivate static void runStoredProc(Connection conn)\r\n throws SQLException {\r\n int iParam1 = 1;\r\n String iParam2 = \"updated name data\";\r\n String proc = \"{call PROC_NAME(?, ?, ?)}\";\r\n CallableStatement cs = conn.prepareCall(proc);\r\n cs.setInt(1, iParam1);\r\n cs.setString(2, iParam2);\r\n cs.registerOutParameter(3, java.sql.Types.INTEGER);\r\n cs.executeUpdate();\r\n String oParam = cs.getInt(3);\r\n System.out.println(\"Updated row count from the proc: \" + oParam);\r\n} \/\/ runStoredProc()\r\n<\/pre>\n2.2.3 Notes on CallableStatement<\/h4>\n
CallableStatement<\/code> interface extends PreparedStatement<\/code> and is defined in java.sql<\/code> package. This is used to execute SQL stored procedures.<\/p>\n{? = call <procedure-name> [(arg1, arg2, ...)]}\r\n{call <procedure-name> [(arg1, arg2, ...)]}\r\n<\/pre>\nPreparedStatement<\/code>. The type of all OUT parameters must be\u00a0 registered prior to executing the stored procedure using registerOutParameter(); their values are retrieved after execution, via the getXxx(int parameterIndex \/ StringparameterName) methods (getBoolean(), getArray(),\u00a0 …).<\/p>\nParameter Modes<\/h4>\n
Calling a Stored Procedure<\/h4>\n
String procName = \"{call STORED_PRODURE_NAME(}\";\r\nCallableStatement cs = conn.prepareCall(procName);\r\nResultSet rs = cs.executeQuery();\r\n<\/pre>\nResultSet<\/code> objects\u00a0 the procedure returns. If not sure how many ResultSet<\/code> objects the procedure returns, use the execute() method.<\/p>\ncs = conn.prepareCall(\"{call INCR_PRICE(?, ?)}\");\r\ncs.setString(1, itemNameArg);\t\t\t\t\/\/ (1)\r\ncs.setFloat(2, newPriceArg);\t\t\t\t\/\/ (2a)\r\ncs.registerOutParameter(2, Types.NUMERIC); \t\/\/ (2b)\r\ncs.execute();\r\nfloat newPrice = cs.getFloat(2);\t\t\t\/\/ (2c)\r\n<\/pre>\n3 Examples<\/h2>\n
3.1 Using a Non-nested Connection<\/h4>\n
\n
public static void testProc4(int [] retval)\r\n throws SQLException {\r\n String connectionURL = \"jdbc:derby:testDB2\";\r\n Connection conn = DriverManager.getConnection(connectionURL);\r\n Statement stmt = conn.createStatement();\r\n ResultSet rs = stmt.executeQuery(\"SELECT * FROM ID_TABLE\");\r\n int nextid = 0;\r\n while(rs.next()) {\r\n nextid = rs.getInt(\"ID\");\r\n }\r\n retval[0] = nextid;\r\n conn.close(); \/\/ alternative: shutdown the database.\r\n} \/\/ testProc4()\r\n<\/pre>\n<\/li>\nCREATE PROCEDURE PROC_NAME_4(OUT paramname INTEGER)\r\n LANGUAGE JAVA\r\n EXTERNAL NAME 'JavaStoredProcs.testProc4'\r\n PARAMETER STYLE JAVA\r\n READS SQL DATA;\r\n<\/pre>\n
private static void runStoredProc4(Connection conn)\r\n throws SQLException {\r\n String proc = \"{call PROC_NAME_4(?)}\";\r\n CallableStatement cs = conn.prepareCall(proc);\r\n cs.registerOutParameter(1, java.sql.Types.INTEGER);\r\n cs.execute();\r\n int oParamData = cs.getInt(1); \/\/ proc output value\r\n} \/\/ runStoredProc4()\r\n<\/pre>\n<\/li>\n<\/ul>\n3.2 A Java DB Built-in System Procedure<\/h4>\n
CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE('c:\/backupdir');<\/pre>\n4 NOTES<\/h2>\n
4.1 Some Advantages of Procedures over Client Code<\/h4>\n
\n
4.2 Other RDBMSs<\/h4>\n
5 References<\/h2>\n
\n