Java to call stored procedure

Java – Calling Stored Procedure using JDBC CallableStatement interface with Batch execution

Let us move on and code an example to connect MySQL database from Java application to invoke or call database stored procedure using JDBC API. But before that, we will list down required things to connect database

• database server IP or address (localhost)
• sever port (3306)
• database name (PLAYER_INFO)
• username (root)
• password ([email protected])

Note: All bold are database values to connect MySQL database

3. Stored Procedure :

• Set of DML statements like insert, update and delete with business logic completely on database side
• With performance boost as these statements are pre-compiled

3.2 Advantages of using stored procedure :

• Increases performance, as these are pre-compiled
• Faster execution
• Business logic completely inside database with extra-level of security
• If there are any changes in business logic, all changes need to be done at database end and not making Java code dirty
• Stored Procedure: spInsertNewPlayerRecord

use PLAYER_INFO; delimiter / CREATE PROCEDURE spInsertNewPlayerRecord( IN name VARCHAR(50), IN age INT, IN matches INT) BEGIN INSERT INTO PLAYER (NAME, AGE, MATCHES) VALUES (name, age, matches); END; / delimiter ;

3.3 Calling Stored Procedure using CallableStatement with Batch execution :

• As we are ready with required things to connect MySQL database from Java application
• Syntax: To invoke database stored procedure use below syntax,

// set values for new player record - 1 callableStatement.setString(1, "Herschelle Gibbs"); callableStatement.setInt(2, 42); callableStatement.setInt(3, 90); // add to batch - 1 callableStatement.addBatch();

BatchStoredProcedureUsingJDBCCallableStatement.java

package in.bench.resources.mysql.db.proc; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class BatchStoredProcedureUsingJDBCCallableStatement < public static void main(String[] args) < // variables Connection connection = null; CallableStatement callableStatement = null; // Step 1: Loading or registering MySQL JDBC driver class try < Class.forName("com.mysql.jdbc.Driver"); >catch(ClassNotFoundException cnfex) < System.out.println("Problem in loading MySQL JDBC driver"); cnfex.printStackTrace(); >// Step 2: Opening database connection try < // Step 2.A: Create and get connection using DriverManager connection = DriverManager.getConnection( "jdbc:mysql://localhost:3306/PLAYER_INFO", "root", "[email protected]"); // Step 2.B: Creating JDBC CallableStatement callableStatement = connection .prepareCall(""); // set values for new player record - 1 callableStatement.setString(1, "Herschelle Gibbs"); callableStatement.setInt(2, 42); callableStatement.setInt(3, 90); // add to batch - 1 callableStatement.addBatch(); // set values for new player record - 2 callableStatement.setString(1, "Mervyn Dillon"); callableStatement.setInt(2, 41); callableStatement.setInt(3, 38); // add to batch - 2 callableStatement.addBatch(); // set values for new player record - 3 callableStatement.setString(1, "Nathan Astle"); callableStatement.setInt(2, 44); callableStatement.setInt(3, 81); // add to batch - 3 callableStatement.addBatch(); // Step 2.C: Executing CallableStatement int[] insertCounts = callableStatement.executeBatch(); // retrieving result and checking for(int result: insertCounts) < System.out.println(result + " indicates insertion is successful"); >> catch(SQLException sqlex) < sqlex.printStackTrace(); >finally < // Step 3: Closing database connection try < if(null != connection) < // cleanup resources, once after processing callableStatement.close(); // and then finally close connection connection.close(); >> catch (SQLException sqlex) < sqlex.printStackTrace(); >> > >

Output:

1 indicates insertion is successful 1 indicates insertion is successful 1 indicates insertion is successful

3.4 Database Screen Capture:

4. Download :

Related Articles :

• Java – Introduction to JDBC
• Java – JDBC Driver types
• Java – Core JDBC components
• Java – JDBC Driver list for all leading database
• Java – JDBC connection steps
• Java – An example to connect MySQL database
• Java – An example to connect Oracle database
• Java – An example to connect MS Access database
• Java 8 – An example to connect MS Access database in Java 8
• Java – JDBC program to connect IBM DB2 database running on Mainframe z/OS system
• Java – Creating database using JDBC Statement interface
• Java – Droping database using JDBC Statement interface
• Java – Creating a table using JDBC Statement interface
• Java – Inserting a record using JDBC Statement interface
• Java – Getting all list of records using JDBC Statement interface
• Java – Getting single record using JDBC Statement interface
• Java – Updating a record using JDBC Statement interface
• Java – Deleting a record using JDBC Statement interface
• Java – Dropping a table using JDBC Statement interface
• Java – Batch update using JDBC Statement interface
• Java – Batch insert using JDBC Statement interface
• Java – Creating a table using JDBC PreparedStatement interface
• Java – Inserting a record using JDBC PreparedStatement interface
• Java – Getting all list of records using JDBC PreparedStatement interface
• Java – Getting single record using JDBC PreparedStatement interface
• Java – Updating a record using JDBC PreparedStatement interface
• Java – Deleting a record using JDBC PreparedStatement interface
• Java – Batch update using JDBC PreparedStatement interface
• Java – Batch insert using JDBC PreparedStatement interface
• Java – Calling Stored Procedure using JDBC CallableStatement interface
• Java – Calling Stored Function using JDBC CallableStatement interface
• Java – Calling Stored Procedure using JDBC CallableStatement interface with Batch execution
• Java – Transaction handling using JDBC Statement interface
• Java – Transaction handling using JDBC PreparedStatement interface
• Java – Integration with Spring framework (Spring JDBC)
• Java – Where clause example using JDBC Statement interface
• Java – Like clause example using JDBC Statement interface
• Java – Order by clause example using JDBC Statement interface
• Java – Metadata of database using JDBC DatabaseMetaData interface
• Java – Metadata of Resultset using JDBC ResultSetMetaData interface
• Java – Interview question and answer on JDBC

References :

Happy Coding !!
Happy Learning !!

Источник

How to call stored procedures in JPA

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, 2 monthly Q&A calls, monthly coding challenges, a community of like-minded developers, and regular expert sessions.

Using stored procedures to implement parts of the application logic in the database is a widely used approach in huge, data-heavy applications. Nevertheless, there was no good support for them before JPA 2.1. You had to use a native query, to call the stored procedure in the database.

Since the JPA 2.1 release, JPA supports two different ways to call stored procedures, the dynamic StoredProcedureQuery, and the declarative @NamedStoredProcedureQuery. In this article, I will show you how to define a @NamedStoredProcedureQuery and how to use it to call a stored procedure in the database. And next week, I will tell you more about the StoredProcedureQuery.

If you want to learn more about the features introduced in JPA 2.1, have a look at JPA 2.1 – 12 features every developer should know and make sure to download the New Features in JPA 2.1 cheat sheet.

Example application and test setup

The example application for this post is small and simple. It consists of a Book and a Review entity which you can see in the following diagram.

The JPA 2.1 specification defines the @NamedStoredProcedureQuery, and you can, therefore, use it with any JPA 2.1 implementation, like Eclipse Link or Hibernate. I use Hibernate 4.3.7 for this tutorial.

My database is a PostgreSQL 9.4. The syntax and supported features of stored procedures are very different between the various database systems. Therefore, you have to check your database documentation on how to implement stored procedures and which kind of parameters it supports.

Parameter modes

Before I show you how to call your first stored procedure with JPA, I have to tell you something about the different parameter modes. You can use 4 different modes of parameters to implement a stored procedure:

• IN: for input parameters,
• OUT: for output parameters,
• INOUT: for parameters which are used for input and output and
• REF_CURSOR: for cursors on a result set.

I will use IN and OUT parameters in the first and REF_CURSOR in the second example. INOUT parameters are used similarly as the IN and OUT parameters.

Stored procedures with IN and OUT parameters

Let’s start with a simple stored procedure which takes 2 IN parameters, sums them up and returns the result as an OUT parameter.

CREATE OR REPLACE FUNCTION calculate( IN x double precision, IN y double precision, OUT sum double precision) RETURNS double precision AS $BODY$ BEGIN sum = x + y; END; $BODY$ LANGUAGE plpgsql

The definition of the @NamedStoredProcedureQuery for this stored procedure is straight forward. You just need to define the name of this query, the name of the stored procedure in the database and a list of 3 parameters that match the parameters of the stored procedure. The name of the query is required by the EntityManager to create the @NamedStoredProcedureQuery. So better choose something that’s easy to understand and remember.

The parameters are defined via @StoredProcedureParameter annotations which take a name, a mode and a type. The name is the name of the parameter in the stored procedure and used later to set and get the specific value. I will show you an example for position based parameter mapping in the REF_CURSOR examples. The mode attribute takes a ParameterMode enum with one of the previously described 4 parameter modes, and the type attribute defines the type of the parameter.

@NamedStoredProcedureQuery( name = "calculate", procedureName = "calculate", parameters = < @StoredProcedureParameter(mode = ParameterMode.IN, type = Double.class, name = "x"), @StoredProcedureParameter(mode = ParameterMode.IN, type = Double.class, name = "y"), @StoredProcedureParameter(mode = ParameterMode.OUT, type = Double.class, name = "sum") >)

That’s all we need to do to define the @NamedStoredProcedureQuery. So lets see how we can call it.

StoredProcedureQuery query = this.em.createNamedStoredProcedureQuery("calculate"); query.setParameter("x", 1.23d); query.setParameter("y", 4.56d); query.execute(); Double sum = (Double) query.getOutputParameterValue("sum");

As you can see, you get a StoredProcedureQuery for the defined @NamedStoredProcedureQuery by calling the createNamedStoredProcedureQuery(String name) method of the EntityManager. The StoredProcedureQuery provides the required methods to set values of the IN parameters, execute the stored procedure and get the OUT parameter.

Stored procedures with REF_CURSOR

Simple input and output parameters are often not enough, and the stored procedure returns the result of a query. This can be done with an REF_CURSOR parameter as it is done in the following example.

CREATE OR REPLACE FUNCTION get_reviews(bookid bigint) RETURNS refcursor AS $BODY$ DECLARE reviews refcursor; -- Declare cursor variables BEGIN OPEN reviews FOR SELECT id, comment, rating, version, book_id FROM review WHERE book_id = bookId; RETURN reviews; END; $BODY$ LANGUAGE plpgsql

As you can see in the following code snippet, the definition of the @NamedStoredProcedureQuery is similar to the previous example. The main differences are the different parameter mode and the definition of the result class returned by this stored procedure call. And I use the positional parameter mapping instead of the name based one we used in the previous example.

@NamedStoredProcedureQuery( name = "getReviews", procedureName = "get_reviews", resultClasses = Review.class, parameters = < @StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, type = void.class), @StoredProcedureParameter(mode = ParameterMode.IN, type = Long.class) >)

The usage of the query is also a little bit different. This time, we need to call getResultList() to get the result of the query. This method implicitly calls the execute() method we used before to call the stored procedure in the database.

List books = (List) this.em.createNamedStoredProcedureQuery("getBooks").getResultList(); for (Book b : books) < StoredProcedureQuery q = this.em.createNamedStoredProcedureQuery("getReviews"); q.setParameter(2, b.getId()); Listreviews = q.getResultList(); for (Review r : reviews) < // do something >>

Summary

JPA 2.1 introduced @NamedStoredProcedureQuery which can be used to declaratively define the stored procedure call. The stored procedure can use parameters of 4 different modes. IN, OUT, and INOUT can be used to define simple input and output parameters. The REF_CURSOR type can be used to return the result of a query.

After we have defined the @NamedStoredProcedureQuery, we can use the createNamedStoredProcedureQuery(String name) method of the EntityManager to create a StoredProcedureQuery object. This provides the required methods to set the input parameter, call the stored procedure and get the result.

Источник