I am learning Spring Data JPA as part of my Spring Boot revision. I am trying to use Stored procedures on the database, and i want to call them from my Spring Data JPA repositories.
I am trying to use the @Procedure annotation as well as native queries, I tried every possible combination and tried to be as detailed as possible,
I can't help but thin either I am missing something right in front of me, or there is a bug
I have checked the data types and everything matches as well as I can see.
-- The Postgresql stored procedure:
CREATE OR REPLACE PROCEDURE misc.createmisc(
IN _name VARCHAR,
IN _mkey VARCHAR,
IN _mvalue integer,
OUT newid integer
)
LANGUAGE sql
AS $$
INSERT INTO misc.misc (name, mkey, mvalue) VALUES (_name, _mkey, _mvalue) RETURNING id;
$$
If i call the procedure from the database client it works perfect:
-- Calling the procedure, both named & positional params work:
-- Positional params:
CALL createmisc('Name 2', 'mk2', 5, NULL);
-- Named params:
CALL createmisc(_name => 'Name 2', _mkey => 'mk3', _mvalue => 5, newId => NULL);
// The code in Spring Boot:
@Entity
/**
* Need to create custom Getters and Setters for mKey and mValue
* because the first char lower, then second caps, is causing issues with lombok,
* but it works with intelliJ
*/
public class Misc {
/**
* Here this is a simple project, and only 1 misc will be inserted at a time,
* For this, the IDENTITY generation type is okay
*/
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String name;
private String mKey;
private Integer mValue;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public void setmKey(String mKey) {
this.mKey = mKey;
}
public String getmKey() {
return mKey;
}
public Integer getmValue() {
return mValue;
}
/**
* Just a normal method to try and show these as rich models,
* If I have a rich model, It can do specific tasks,
*
* @return
*/
public String expressMisc() {
return "Misc: " + name + " is amazing ";
}
}
/**
* Learning how to call Stored Procedures from Spring Data JPA to PostgreSQL database
*/
public interface MiscStoredProcedureCrudRepository extends CrudRepository<Misc, Integer> {
/**
* This works when return type is void
* @param name
* @param mkey
* @param mvalue
*/
@Procedure(procedureName = "createmisc", outputParameterName = "newid")
public void callCreateMiscWithVoidReturn(String name, String mkey, Integer mvalue);
/**
* Here I am not mentioning anything to do with OUT parameter,
* PostgreSQL expects an OUT parameter, but when there is a return type it fails,
* Even though I choose the most generic object:
* @param name
* @param mkey
* @param mvalue
* @return
*/
@Procedure(procedureName = "createmisc")
public Object callStoredProcBaldenbugStyle(String name, String mkey, Integer mvalue);
/**
* Here I am going to try using the native query,
* The arguments match completely fine but for some reason it can't find the procedure
*/
@Query(
value = "CALL misc.createmisc(:name, :mkey, :mvalue, :newid)",
nativeQuery = true
)
public Integer callCreateMiscWithNativeQuery(
@Param("name") String name,
@Param("mkey") String mkey,
@Param("mvalue") Integer mvalue,
@Param("newid") Integer newid
);
/**
* Native query without Out parameter
*/
@Query(
value = "CALL createmisc(:name, :mkey, :mvalue, NULL)",
nativeQuery = true
)
public Integer callCreateMiscWithNativeQueryNoOutParameter(
@Param("name") String name,
@Param("mkey") String mkey,
@Param("mvalue") Integer mvalue
);
/**
* Calling native query without OUT parameter and No nulls
*/
@Query(
value = "CALL createmisc(:name, :mkey, :mvalue)",
nativeQuery = true
)
public Integer callCreateMiscWithNativeQueryNoOutParameterNoNull(
@Param("name") String name,
@Param("mkey") String mkey,
@Param("mvalue") Integer mvalue
);
}
I have followed this article: https://www.baeldung.com/spring-data-jpa-stored-procedures I also tried every possible combination, It keeps saying procedure not found, might need explicit casts
Here is the tests for each of the functions with the trace:
@Test
// Passes:
void callCreateMiscWithVoidReturn() {
Misc misc = new Misc();
misc.setName("Misc from SP");
misc.setmKey("mkj");
misc.setMValue(110);
miscStoredProcedureCrudRepository.callCreateMiscWithVoidReturn(
misc.getName(), misc.getmKey(), misc.getmValue()
);
}
@Test
void callStoredProcBaldenbugStyle() {
Misc misc = new Misc();
misc.setName("Misc from SP");
misc.setmKey("mkj");
misc.setMValue(110);
Object returningInt = miscStoredProcedureCrudRepository.callStoredProcBaldenbugStyle(
misc.getName(), misc.getmKey(), misc.getmValue()
);
}
Caused by: org.postgresql.util.PSQLException: ERROR: procedure createmisc(character varying, character varying, integer, unknown) does not exist
Hint: No procedure matches the given name and argument types. You might need to add explicit type casts.
Position: 6
@Test
void callCreateMiscWithNativeQuery() {
Misc misc = new Misc();
misc.setName("Misc from SP");
misc.setmKey("mkj");
misc.setMValue(110);
miscStoredProcedureCrudRepository.callCreateMiscWithNativeQuery(
misc.getName(), misc.getmKey(), misc.getmValue(), null
);
}
Caused by: org.postgresql.util.PSQLException: ERROR: procedure misc.createmisc(character varying, character varying, integer, integer) does not exist
Hint: No procedure matches the given name and argument types. You might need to add explicit type casts.
Position: 6
@Test
void callCreateMiscWithNativeQueryNoOutParameter() {
Misc misc = new Misc();
misc.setName("Misc from SP");
misc.setmKey("mkj");
misc.setMValue(110);
miscStoredProcedureCrudRepository.callCreateMiscWithNativeQueryNoOutParameter(
misc.getName(), misc.getmKey(), misc.getmValue()
);
}
Caused by: org.postgresql.util.PSQLException: ERROR: procedure createmisc(character varying, character varying, integer, unknown) does not exist
Hint: No procedure matches the given name and argument types. You might need to add explicit type casts.
Position: 6
@Test
void callCreateMiscWithNativeQueryNoOutParameterNoNull() {
Misc misc = new Misc();
misc.setName("Misc from SP");
misc.setmKey("mkj");
misc.setMValue(110);
miscStoredProcedureCrudRepository.callCreateMiscWithNativeQueryNoOutParameterNoNull(
misc.getName(), misc.getmKey(), misc.getmValue()
);
}
org.springframework.orm.jpa.JpaSystemException: JDBC exception executing SQL [CALL createmisc(?, ?, ?)] [No results were returned by the query.] [n/a]
Caused by: org.postgresql.util.PSQLException: No results were returned by the query.
at app//org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:135)
at app//com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
at app//com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
at app//org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:246)
I have tried every combination imaginable, but I can't seem to find a way, Same thing is happenning with functions:
CREATE OR REPLACE FUNCTION misc.createmiscasfunc(_name character varying, _mkey character varying, _mvalue integer)
RETURNS integer
LANGUAGE sql
AS $$
INSERT INTO misc (name, mkey, mvalue) VALUES (_name, _mkey, _mvalue) RETURNING id;
$$
SELECT createmiscasfunc(
_name => 'Named Param',
_mkey => 'ddd2',
_mvalue => 100
);
Creating a named native query:
/**
*
* @param name
* @param mkey
* @param mvalue
* @return
*/
@Query(
value = "SELECT misc.createmiscasfunc(_name => :name, _mkey => :mkey, _mvalue => :mvalue) ",
nativeQuery = true
)
public Integer createMiscAsFunc(
@Param("name") String name,
@Param("mkey") String mkey,
@Param("mvalue") Integer mvalue
);
@Test
void createMiscAsFunc() {
Misc misc = new Misc();
misc.setName("Misc from SP");
misc.setmKey("mkj");
misc.setMValue(110);
miscStoredProcedureCrudRepository.createMiscAsFunc(
misc.getName(), misc.getmKey(), misc.getmValue()
);
}
Caused by: org.postgresql.util.PSQLException: ERROR: function misc.createmiscasfunc(_name => character varying, _mkey => character varying, _mvalue => integer) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 8
Write a stored function instead of a procedure:
create or replace function misc.createmisc(_name text, _mkey text, _mvalue integer)
returns integer language sql volatile as
$$
insert into misc.misc (name, mkey, mvalue) values (_name, _mkey, _mvalue)
returning id;
$$;
Then the wrapper in Java:
@Query(
value = "select misc.createmisc(:name, :mkey, :mvalue)",
nativeQuery = true
)
public Integer createMiscWithNativeQuery(
@Param("name") String name,
@Param("mkey") String mkey,
@Param("mvalue") Integer mvalue
);