JPA and stored procedures

In a majority number of cases the Java-Persistence API (JPA) to create/load/update entities cover all your needs. But sometimes you need to do something special inside the DB with help of stored-procedures. The good thing about JPA is that you still can use it to call the stored procedures and get the results back. Here is two cases of using it

  1. execute the stored-procedure and get the results back as Entity
  2. execute stored procedure and get the result back as non-entity (for example set of name-value pairs)

For the 1-st case you need to define the stored procedure in DB (syntax depends on DB, I’ll use the Postgre-SQL syntax)
In the code example below I’ll show how to create a new entity using the call to the stored-procedure. It solve the real problem of how to generate unique-record in DB. If we’ll try to use the standard JPA API we may end up with the code like that

public UniqueCode generateUniqueCode(int length, String prefix) {
do {
    String code = generateRandomString(length, prefix);
    if  ( notExistsInDB() // call to entityManager to check if such code exists) {
        UniqueCode code = new UniqueCode(code);
        return code;
} while(true);
//no way to get to that point
return null;

So, at least 2 calls to DB (+1 for each loop cycle). Quite non-optimal, so why not do that with a single call to DB ?
Just in case – no need to inspect SQL code example below

DROP TYPE IF EXISTS unique_code_result CASCADE ;

-- we define the result-type of returned data (postgre need a type to be returned, it can't return RECORD)
CREATE TYPE unique_code_result AS (pk bigint, code character varying);

-- call generate_unique_code_on_table to generate unique value, insert it in the table
-- AND return the result of INSERT command as "unique_code_result" type
CREATE OR REPLACE FUNCTION generate_unique_code(length integer, prefix varchar)
RETURNS unique_code_result AS
 new_record unique_code_result;

  INSERT INTO unique_code (pk, code) VALUES
    (nextVal('unique_code_s'), generate_unique_code_on_table('unique_code', 'code', length, prefix))
  RETURNING pk, code INTO new_record;

RETURN new_record;

COST 100;

-- generate_unique_code_on_table generates the UNIQUE column-value string for the "unique_column" on the table "table_name".
-- to implement that it generate the random string in the cycle and check if it is already exists by SQL request
-- If it doesn't exists - the value is returned, otherwise it generate next random value and check it
CREATE OR REPLACE FUNCTION generate_unique_code_on_table(table_name varchar, unique_column varchar, code_length integer, prefix varchar)
  RETURNS varchar AS
    select_request varchar;
    result VARCHAR;
    unique_code_key bigint;
    select_request = 'SELECT pk FROM ' || table_name || ' WHERE ' || unique_column || ' = $1';
        result := random_string(code_length);
        IF (prefix IS NOT NULL) THEN
	   result := prefix || result;
        END IF;

        EXECUTE select_request INTO unique_code_key USING result;
            RETURN result;
        END IF;
  COST 100;

Sorry for that long DB code, I want to show the real-life problem solution, not just plain INSERT.
Now, let’s call it from the JPA. In our example we generate and insert new record into the “unique_code” table. Let’s define that entity and required JPA annotations to call the stored procedure.

import javax.persistence.*;

//result-type mapping used by native-query call
@SqlResultSetMapping(name = "UniqueCode.implicit", entities = {
        @EntityResult(entityClass = UniqueCode.class)
        @NamedNativeQuery(name = "UniqueCode.generateNew",
                query = "select * from generate_unique_code (:length, :prefix)",
                resultSetMapping = "UniqueCode.implicit")
@Table(name = "unique_code")
public class UniqueCode {

    private Long pk;

    @Column(unique = true)
    private String code;

Now let’s call the Native-Query to generate the UniqueCode

        Query query = entityManager.createNamedQuery("UniqueCode.generateNew")
                .setParameter("length", length)
                .setParameter("prefix", prefix != null ? prefix : "");

        return (UniqueCode) query.getSingleResult();

That’s it! This call will return us new Entity which already exists in DB and can be used by other entities. Please note that we have to pass the empty-string (not null) as “prefix”, since DB will not be able to find corresponding db-procedure by signature if we’ll pass NULL.

It’s too much code already here, so I’ll show the 2-nd case “using native-query to return non-entities” in the next post.

Tagged with: , , , , , , , , , ,
Posted in Software Development, Tips and Tricks
5 comments on “JPA and stored procedures
  1. Anatoliy says:

    Hello, Andrey.

    But what if being a null parameter is essential in a stored procedure’s logic? For example let a parameter be a null date. And in a stored procedure there will be something like:
    IF (date IS NULL) THEN
    –omit a restriction by date
    END IF;
    It seems impossible to implement this, doesn’t it?

    Thanks, Anatoliy

  2. Well, not sure how to update the code on java-side to pass NULL values, but there is a workaround – you may pass some “SPECIAL” value which your stored procedure will treat as “NULL” value. So, pass some value which your application code will never pass – For example date like “1001-01-01”.

    • Anatoliy says:

      Andrey, actually at the moment I see two workarounds:
      1. As my stored procedure waits for 2 nullable parameters (in general there can be more and this workaround is ugly but nevertheless) I create 4 NamedNativeQueries, say:

      @NamedNativeQuery(name = “funcWithoutNulls”, query = “select * from func(:firstparam, :secondParam)”, resultClass = CustomClass.class),
      @NamedNativeQuery(name = “funcWithNullFirstParam”, query = “select * from func(NULL, :secondParam)”, resultClass = CustomClass.class),
      @NamedNativeQuery(name = “funcWithNullSecondParam”, query = “select * from func(:firstparam, NULL)”, resultClass = CustomClass.class),
      @NamedNativeQuery(name = “funcWithBothNullParams”, query = “select * from func(NULL, NULL)”, resultClass = CustomClass.class)

      2. After this I decided to change the stored procedure a little bit as you say, actually the special Null date is ” ‘epoch’::DATE ” (for postgresql) 🙂

  3. Anatoliy says:

    Andrey – In general null is passed as NULL in Java :). What are you exactly wondering about here? If about “select * from func(:firstparam, NULL)” then it is simply hardcoded as you can see. Hibernate passes null values to postgresql as “bytea”.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: