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
- execute the stored-procedure and get the results back as Entity
- 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);
entityManager.persist(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
$BODY$
DECLARE
new_record unique_code_result;
BEGIN
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;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
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
$BODY$
DECLARE
select_request varchar;
result VARCHAR;
unique_code_key bigint;
BEGIN
select_request = 'SELECT pk FROM ' || table_name || ' WHERE ' || unique_column || ' = $1';
LOOP
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;
IF NOT FOUND THEN
RETURN result;
END IF;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
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.*;
@Entity
//result-type mapping used by native-query call
@SqlResultSetMapping(name = "UniqueCode.implicit", entities = {
@EntityResult(entityClass = UniqueCode.class)
}
)
@NamedNativeQueries({
@NamedNativeQuery(name = "UniqueCode.generateNew",
query = "select * from generate_unique_code (:length, :prefix)",
resultSetMapping = "UniqueCode.implicit")
}
)
@Table(name = "unique_code")
public class UniqueCode {
@Id
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.
It will be not easy to read and understand this article, so please remember that you always have a choice to not read it 🙂
Developer Notes 