Category Archives for Spring Boot

group running

4 Ways To Control Access to Spring Data REST

4 Ways To Control Access to Spring Data REST

This post looks at 4 ways to control access to Spring Data REST using RepositoryDetectionStrategies’s

This post forms part of a series looking at Spring Data REST –

RepositoryDetectionStrategies

You can control access to Spring Data REST using the 4 RepositoryDetectionStrategies –

Field

ALL

Description

Exposes all Spring Data Repositories regardless of annotations or access control

ANNOTATED

Only Repositories exposed with RepositoryRestResource or RestResource, and the exported flag is not set to false.

DEFAULT

public Spring Data Repositories or ones annotated with RepositoryRestResource

VISIBILITY

Only public Spring Data Repositories

Source Code

You can run the examples by –

mvnw spring-boot:run

Examples

Ive created a Spring Rest Configuration class for this example –

@Component
public class SpringRestConfiguration extends RepositoryRestConfigurerAdapter {
@Override
public void configureRepositoryRestConfiguration(RepositoryRestConfiguration config) {
config.setRepositoryDetectionStrategy(RepositoryDetectionStrategy.RepositoryDetectionStrategies.DEFAULT);
}
}

All of the examples will be changing the enumerated value of RepositoryDetectionStrategy.RepositoryDetectionStrategies, and testing what REST end points are exposed.

I have also annoted one of our Spring Data Repositories –

@RepositoryRestResource
@PreAuthorize("hasRole('ROLE_USER')")
public interface ParkrunCourseRepository extends CrudRepository<ParkrunCourse, Long> {
@Override
@PreAuthorize("hasRole('ROLE_ADMIN')")
ParkrunCourse save(ParkrunCourse parkrunCourse);
}

RepositoryDetectionStrategies.DEFAULT

A default setting RepositoryDetectionStrategy detect –

  • public interfaces
  • annotated with RepositoryRestResource or RestResource

So with our example we would expect to see both interfaces exposed as REST end points

We can confirm this by calling http://localhost:8080/rest/profile –

curl -u user:user -X GET http://localhost:8080/rest/profile
{
 "_links" : {
 "self" : {
 "href" : "http://localhost:8080/rest/profile"
 },
 "secrets" : {
 "href" : "http://localhost:8080/rest/profile/secrets"
 },
 "parkrunCourses" : {
 "href" : "http://localhost:8080/rest/profile/parkrunCourses"
 }
 }
}

RepositoryDetectionStrategies.ANNOTATED

Annotated will only detect interfaces annotated with RepositoryRestResource or RestResource, and an exported flag that is true(default). In our test case we would only expect to see the end point for ParkrunCourseRepository to be exposed as it is annotated with @RepositoryRestResource

We can test and confirm this –

curl -u user:user -X GET http://localhost:8080/rest/profile
{
 "_links" : {
 "self" : {
 "href" : "http://localhost:8080/rest/profile"
 },
 "parkrunCourses" : {
 "href" : "http://localhost:8080/rest/profile/parkrunCourses"
 }
 }
}

RepositoryDetectionStrategies.VISIBILITY

VISIBILITY will only detect REST and expose reportories based if they are publicly exposed.

Lets make a change to SecretRepository to leave the interface with default visibility –

interface SecretRepository extends CrudRepository<Secret, Long> {
}

We would not expect to see this end point, and would only expect to see the ParkrunCourseRepository end point. This is confirmed when we call the rest/profile –

curl -u user:user -X GET http://localhost:8080/rest/profile
{
 "_links" : {
 "self" : {
 "href" : "http://localhost:8080/rest/profile"
 },
 "parkrunCourses" : {
 "href" : "http://localhost:8080/rest/profile/parkrunCourses"
 }
 }
}

RepositoryDetectionStrategies.ALL

Finally if we use RepositoriesDetectionStrategies.ALL with SecretRepository at default access. Then we would expect to see both the parkrunCourses and Secret’s end point. All will also expose end points that have an exported false attribute.

Restart the server and test –

curl -u user:user -X GET http://localhost:8080/rest/profile
{
 "_links" : {
 "self" : {
 "href" : "http://localhost:8080/rest/profile"
 },
 "parkrunCourses" : {
 "href" : "http://localhost:8080/rest/profile/parkrunCourses"
 },
 "secrets" : {
 "href" : "http://localhost:8080/rest/profile/secrets"
 }
 }
}

Conclusions

This post looked at 4 ways to control access to Spring Data REST using RepositoryDetectionStrategies allow or restrict access to the underlying Spring Data JPA repositories. The options are – ALL, ANNOTATED, VISIBILITY or DEFAULT

Thanks to Mayra Carreno @ https://unsplash.com/@mayracarreno?utm_campaign=photographer-credit

Data Hiding using JsonIgnore and Spring Data JPA

Data Hiding using JsonIgnore and Spring Data JPA

Data Hiding using JsonIgnore and Spring Data JPA is achieved using two approaches –

  • @JsonIgnore and @JsonIgnoreProperties
  • Repository Detection Strategies

This post considers @JsonIgnore and @JsonIgnoreProperties

Code

The code is available at –

Code Changes

I’ve added an extra table to for this example –

@Entity
public class Secrets {
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private long id;
private String mySecrets;
public String getMySecrets() {
return mySecrets;
}
public void setMySecrets(String mySecrets) {
this.mySecrets = mySecrets;
}
}

With its associated repository –

@PreAuthorize("hasRole('ROLE_USER')")
public interface SecretsRepository extends CrudRepository<Secrets, Long> {
}

Running The Code

I have left the security from the last tutorial, Securing Spring Data REST with PreAuthorize, in place – but we can run this code using –

mvnw spring-boot:run

We can then call rest/profile to see the two exposed repositories –

curl -u user:user -X GET http://localhost:8080/rest/profile
{
"_links" : {
"self" : {
"href" : "http://localhost:8080/rest/profile"
},
"secrets" : {
"href" : "http://localhost:8080/rest/profile/secrets"
},
"parkrunCourses" : {
"href" : "http://localhost:8080/rest/profile/parkrunCourses"
}
}
}

And calling the secrets REST end point-

curl -u user:user -X GET http://localhost:8080/rest/secrets/1
{
"mySecret" : "I want to hide this",
"_links" : {
"self" : {
"href" : "http://localhost:8080/rest/secrets/1"
},
"secret" : {
"href" : "http://localhost:8080/rest/secrets/1"
}
}
}

This posts looks at techniques I can use to not expose the SecretRepository

@JsonIgnore and @JsonIgnoreProperties

The purpose of @JsonIgnore, and @JsonIgnoreProperties is to hide attributes from the Jackson parser by instructing it to Ignore these fields

Usage is simply a matter of tagging the attribute with the @JsonIgnore

@Entity
public class Secret {
//
@JsonIgnore
private String mySecret;
//
}

Or we can achieve the same using @JsonIgnoreProperties annotation –

@JsonIgnoreProperties({"mySecret"})
@Entity
public class Secret {
//
private String mySecret;
//
}

With either of these changes we can then call our secrets REST end point, and the mySecret field is no longer exposed –

curl -u user:user -X GET http://localhost:8080/rest/secrets/1
{
"_links" : {
"self" : {
"href" : "http://localhost:8080/rest/secrets/1"
},
"secret" : {
"href" : "http://localhost:8080/rest/secrets/1"
}
}
}

Conclusion

@JsonIgnore or @JsonIgnoreProperties simply hides the field from the Jackson parser. This is good for hiding small pieces of information. The downside is we still have an exposed end point due to the default Repository Detection Strategies.

 

 

Securing Spring Data REST with PreAuthorize

Securing Spring Data REST with PreAuthorize is an alternative method to securing Spring Data REST API’s, building on the previous apporach covered in Spring Security and Spring Data REST.

Securing Spring Data REST with Preauthorize

Securing Spring Data REST with PreAuthorize code is available on github –

https://github.com/farrelmr/introtospringdatarest/tree/3.0.0

https://github.com/farrelmr/introtospringdatarest/releases/tag/3.0.0

Run the code by typing –

mvnw spring-boot:run

Maven

In order to use the preAuthorise attributes you need to import spring-boot-security –

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-security</artifactId>
</dependency>

SecurityConfig

The key points are –

  • Maintain security for roles “ADMIN” and “USER” on “/rest/**” requests
  • @EnableGlobalMethodSecurity(prePostEnabled = true) – this enables the annotations on the JPA model
package com.javabullets.springdata.jparest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import org.springframework.security.config.annotation.method.configuration.EnableGlobalMethodSecurity;

import org.springframework.http.HttpMethod;

@EnableWebSecurity
@EnableGlobalMethodSecurity(prePostEnabled = true)
public class SecurityConfig extends WebSecurityConfigurerAdapter {
	@Autowired
	public void configureGlobal(AuthenticationManagerBuilder auth)
			throws Exception {
		auth.
			inMemoryAuthentication()
				.withUser("user").password("user").roles("USER").and()
				.withUser("admin").password("admin").roles("USER","ADMIN");
	}

    @Override
    protected void configure(HttpSecurity http) throws Exception {
	    http
          .authorizeRequests()
            .antMatchers("/rest/**").hasAnyRole("ADMIN","USER").and()
          .httpBasic()
            .and()
		   .csrf().disable();		   
    }
}

ParkrunCourseRepository

The key changes are the use of PreAuthorize on the ParkrunCourseRepository –

package com.javabullets.springdata.jparest;

import org.springframework.data.repository.CrudRepository;
import org.springframework.security.access.prepost.PreAuthorize;

@PreAuthorize("hasRole('ROLE_USER')")
public interface ParkrunCourseRepository extends CrudRepository<ParkrunCourse, Long> {
	@Override
	@PreAuthorize("hasRole('ROLE_ADMIN')")
	ParkrunCourse save(ParkrunCourse parkrunCourse);
}

The key points are –

  • Use of hasRole – note you need to append ROLE_ to your roles
  • We could also add PreAuthorize security to custom methods defined in the repository

Putting It Together

mvnw spring-boot:run

POST methods – Access Forbidden

curl -u user:user -X POST -H "Content-Type:application/json" -d "{  \"courseName\" : \"adminOnly\",  \"url\" : \"url\",  \"averageTime\" : \"10000\" }" http://localhost:8080/rest/parkrunCourses

{"timestamp":1496134011261,"status":403,"error":"Forbidden","message":"Access is denied","path":"/rest/parkrunCourses"}

POST methods – Access Allowed

curl -u admin:admin -X POST -H "Content-Type:application/json" -d "{  \"courseName\" : \"adminOnly\",  \"url\" : \"url\",  \"averageTime\" : \"10000\" }" http://localhost:8080/rest/parkrunCourses

{
  "courseName" : "adminOnly",
  "url" : "url",
  "averageTime" : 10000,
  "_links" : {
    "self" : {
      "href" : "http://localhost:8080/rest/parkrunCourses/13"
    },
    "parkrunCourse" : {
      "href" : "http://localhost:8080/rest/parkrunCourses/13"
    }
  }
}

Discussion

This post shows a different approach to role based security of Spring Data REST with PreAuthorize. It is not a question of which method is better, but which is practical. It may be that you cannot use preAuthorize in your codebase, but can use role based authentication as outlined in Spring Security and Spring Data REST

From a design point of view you may want to define a custom repository with your core PreAuthorize rules, allowing your model to inherit security.

Conclusions

This post shows how Spring Security and Spring Data REST can be combined to secure REST API URL’s and HTTP methods. It used a basic form of Spring authentication, combining a MemoryRealm with the security configuration. We have also demonstrated how to restrict access to REST methods based on user group.

My next post will look at how Spring Data REST can restrict access by deciding what methods it exposes, and what fields are exposed

Spring Security and Spring Data REST

Spring Security and Spring Data REST are discussed in this post. It builds on the previous post “Introduction to Spring Data REST” and my series of posts on Spring Security –

Spring Security and Spring Data REST

Spring Security is split into two components –

  • Authentication – Defined by AuthenticationManager, or the source of the authentication credentials
  • Authorisation – what we want to protect – URL’s, Roles, method

This tutorial is only considering BasicAuthentication with an memory realm. I will probably return to this subject to show how to properly harden a RESTful API.

Source Code

Code available on github –

https://github.com/farrelmr/introtospringdatarest/tree/2.0.0

https://github.com/farrelmr/introtospringdatarest/releases/tag/2.0.0

Run the code by typing –

mvnw spring-boot:run

Maven

You can secure spring boot by simply including this dependency –

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>

You can then get the password when you startup spring boot, but that is not very practical for most usages.

SecurityConfig

Ive moved the Spring Data REST API URL to /rest in application.properties –

spring.data.rest.basePath=/rest

SecurityConfig has –

  • Two users – user(Role – USER), admin(Role – admin)
  • Restrictions –
    • Only “ADMIN” or “USER” roles can access “/rest”,
    • Only “ADMIN” users can POST to the web service –
package com.javabullets.springdata.jparest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import org.springframework.http.HttpMethod;

@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {
    @Autowired
    public void configureGlobal(AuthenticationManagerBuilder auth)
            throws Exception {
        auth.
            inMemoryAuthentication()
                .withUser("user").password("user").roles("USER").and()
                .withUser("admin").password("admin").roles("USER","ADMIN");
    }

    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http
          .authorizeRequests()
            .antMatchers(HttpMethod.POST, "/rest/parkrunCourses/**").hasRole("ADMIN")
            .antMatchers("/rest/**").hasAnyRole("ADMIN","USER").and()
          .httpBasic()
            .and()
           .csrf().disable();
    }
}

Putting It Together

mvnw spring-boot:run

Ive switched to curl for calling the API’s as its clearer for tutorials –

No credentials – Doesnt Authenticate

curl  -X GET -H "Content-Type:application/json" http://localhost:8080/rest/parkrunCourses/1
{"timestamp":1496069649024,"status":401,"error":"Unauthorized","message":"Full authentication is required to access this resource","path":"/rest/parkrunCourses/1"}

user/user credentials – Authenticates

curl -u user:user -X GET -H "Content-Type:application/json" http://localhost:8080/rest/parkrunCourses/1
{
  "courseName" : "Inverness",
  "url" : "http://www.parkrun.org.uk/inverness/",
  "averageTime" : 1582,
  "_links" : {
    "self" : {
      "href" : "http://localhost:8080/rest/parkrunCourses/1"
    },
    "parkrunCourse" : {
      "href" : "http://localhost:8080/rest/parkrunCourses/1"
    }
  }
}

POST methods – Access Forbidden

curl -u user:user -X POST -H "Content-Type:application/json" -d "{  \"courseName\" : \"adminOnly\",  \"url\" : \"url\",  \"averageTime\" : \"10000\" }" http://localhost:8080/rest/parkrunCourses
{"timestamp":1496069812087,"status":403,"error":"Forbidden","message":"Access is denied","path":"/rest/parkrunCourses"}

POST methods – Access Allowed

curl -u admin:admin -X POST -H "Content-Type:application/json" -d "{  \"courseName\" : \"adminOnly\",  \"url\" : \"url\",  \"averageTime\" : \"10000\" }" http://localhost:8080/rest/parkrunCourses
{
  "courseName" : "adminOnly",
  "url" : "url",
  "averageTime" : 10000,
  "_links" : {
    "self" : {
      "href" : "http://localhost:8080/rest/parkrunCourses/13"
    },
    "parkrunCourse" : {
      "href" : "http://localhost:8080/rest/parkrunCourses/13"
    }
  }
}

Conclusions

This post shows how Spring Security and Spring Data REST can be combined to secure REST API URL’s and HTTP methods. It used a basic form of Spring authentication, combining a MemoryRealm with the security configuration. We have also demonstrated how to restrict access to REST methods based on user group.

Introduction to Spring Data REST

This tutorial on Spring Data REST shows how Spring Data repositories can be exposed as a REST API. Its a really interesting idea, and can save you a lot of boilerplate code building microservices.

Introduction to Spring Data REST

Spring Boot makes it easy to create a Spring Data REST starter project using Spring Initializr

spring initializr

Spring Initializr – Spring Boot JPA Microservice

Im using Spring Boot 2.0.0, and the HAL Browser to quickly demonstrate REST API.

Download, expand and import into your IDE –

Expanded Spring Boot Project

Expanded Spring Boot Project

Code

For a quickstart the code is available on github at – https://github.com/farrelmr/introtospringdatarest/tree/1.0.0

Let’s keep this simple and use the classes from my parkrunpb project.

The starting point is the pom.xml –

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.javabullets.springdata</groupId>
    <artifactId>jparest</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>jparest</name>
    <description>Spring Data Rest Example</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.0.BUILD-SNAPSHOT</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-rest</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-rest-hal-browser</artifactId>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

    <repositories>
        <repository>
            <id>spring-snapshots</id>
            <name>Spring Snapshots</name>
            <url>https://repo.spring.io/snapshot</url>
            <snapshots>
                <enabled>true</enabled>
            </snapshots>
        </repository>
        <repository>
            <id>spring-milestones</id>
            <name>Spring Milestones</name>
            <url>https://repo.spring.io/milestone</url>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
        </repository>
    </repositories>

    <pluginRepositories>
        <pluginRepository>
            <id>spring-snapshots</id>
            <name>Spring Snapshots</name>
            <url>https://repo.spring.io/snapshot</url>
            <snapshots>
                <enabled>true</enabled>
            </snapshots>
        </pluginRepository>
        <pluginRepository>
            <id>spring-milestones</id>
            <name>Spring Milestones</name>
            <url>https://repo.spring.io/milestone</url>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
        </pluginRepository>
    </pluginRepositories>
</project>

So we have our JPA object –

package com.javabullets.springdata.jparest;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Entity
public class ParkrunCourse {
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Long id;

private String courseName;
private String url;
private Long averageTime;

public String getCourseName() {
return courseName;
}
public void setCourseName(String courseName) {
this.courseName = courseName;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public Long getAverageTime() {
return averageTime;
}
public void setAverageTime(Long averageTime) {
this.averageTime = averageTime;
}
}

Im using JPARepository –

package com.javabullets.springdata.jparest;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.data.rest.core.annotation.RepositoryRestResource;

public interface ParkrunCourseRepository extends JpaRepository<ParkrunCourse, Long> {
}

And a script to ensure the data is preloaded – stored in (src\main\resources\import.sql) –

INSERT INTO PARKRUN_COURSE(ID, COURSE_NAME, URL, AVERAGE_TIME) VALUES (1, 'Inverness', 'http://www.parkrun.org.uk/inverness/', 1582);
INSERT INTO PARKRUN_COURSE(ID, COURSE_NAME, URL, AVERAGE_TIME) VALUES (2, 'Aberdeen',    'http://www.parkrun.org.uk/aberdeen/', 1586);
INSERT INTO PARKRUN_COURSE(ID, COURSE_NAME, URL, AVERAGE_TIME) VALUES (3, 'Dundee(Camperdown)', 'http://www.parkrun.org.uk/camperdown/', 1752);
INSERT INTO PARKRUN_COURSE(ID, COURSE_NAME, URL, AVERAGE_TIME) VALUES (4, 'St Andrews', 'http://www.parkrun.org.uk/standrews/', 1669);
INSERT INTO PARKRUN_COURSE(ID, COURSE_NAME, URL, AVERAGE_TIME) VALUES (5, 'Perth', 'http://www.parkrun.org.uk/perth/', 1620);
INSERT INTO PARKRUN_COURSE(ID, COURSE_NAME, URL, AVERAGE_TIME) VALUES (6, 'Edinburgh', 'http://www.parkrun.org.uk/edinburgh/', 1523);

Running the Code

Run the code using the maven wrapper – mvnw –

mvnw spring-boot:run

We can then access the HALBrowser on –

http://localhost:8080/browser/index.html#http://localhost:8080/parkrunCourses

Spring Data REST - HALBrowser

Spring Data REST – HALBrowser

The response body returns all the parkrunCourse. You can also do GET, POST, PUT, PATCH and DELETE.

A good trick is to use the browser integrated SQL editor for the H2 database. I covered this in my spring boot security tutorial.

hal+json media type

Spring Data JPA allows you to make calls as json or hal+json. The purpose of hal+json makes it easier to navigate API’s following links. The HALBrowser is great for quickly checking your API.

Conclusions

This example shows how a spring data repository can easily be exposed as a REST API. My next post will look at the practicality of this approach, and what restrictions you might want to apply to your API.

Calling Stored Procedures From Spring Data JPA

Ok Ive struggled on Calling Stored Procedures From Spring Data JPA for a while. My issue is that my SP’s dont return any values, so the normal JPA and Spring Data approaches outlined in my previous posts Spring Data JPA – Custom Repository.

Calling Stored Procedures From Spring Data JPA

Consider the following procedure –

CREATE OR REPLACE PACKAGE test_pkg AS
PROCEDURE in_only_test (inParam1 IN VARCHAR2);
PROCEDURE in_and_out_test (inParam1 IN VARCHAR2, outParam1 OUT VARCHAR2);
END test_pkg;
/
CREATE OR REPLACE PACKAGE BODY test_pkg AS
PROCEDURE in_only_test(inParam1 IN VARCHAR2) AS
BEGIN
DBMS_OUTPUT.PUT_LINE('in_only_test');
END in_only_test;
PROCEDURE in_and_out_test(inParam1 IN VARCHAR2, outParam1 OUT VARCHAR2) AS
BEGIN
outParam1 := 'Woohoo Im an outparam, and this is my inparam ' || inParam1;
END in_and_out_test;
END test_pkg;

Ensure that you have granted the appropriate permissions to this class – eg –

GRANT EXECUTE ON TEST_PKG TO 'MYDB';

Implementation

@Entity
@Table(name = "MYTABLE")
@NamedStoredProcedureQueries({
@NamedStoredProcedureQuery(name = "in_only_test", procedureName = "test_pkg.in_only_test",
parameters = {
@StoredProcedureParameter(mode = ParameterMode.IN, name = "inParam1", type = String.class)
}
),
@NamedStoredProcedureQuery(name = "in_and_out_test", procedureName = "test_pkg.in_and_out_test",
parameters = {
@StoredProcedureParameter(mode = ParameterMode.IN, name = "inParam1", type = String.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "outParam1", type = String.class)
}
)
})
public class MyTable implements Serializable {
}

The key points are –

  • procedureName – This is the name of the stored procedure on the database
  • name – This is the name of the StoredProcedure in the JPA ecosystem
  • We then define the IN/OUT parameters

We then create the Spring Data JPA repository –

public interface MyTableRepository extends CrudRepository<MyTable, Long> {
@Procedure(name = "in_only_test")
void inOnlyTest(@Param("inParam1") String inParam1);
@Procedure(name = "in_and_out_test")
String inAndOutTest(@Param("inParam1") String inParam1);
}

The key points –

  • @Procedure – the name parameter must match the name on @NamedStoredProcedureQuery
  • @Param – Must match @StoredProcedureParameter name parameter

We can then call them –

// This version shows how a param can go in an be returned from a stored procedure
String inParam = "Hi Im an inputParam";
String outParam = myTableRepository.inAndOutTest(inParam);
Assert.assertEquals(outParam, "Woohoo Im an outparam, and this is my inparam Hi Im an inputParam");
// This version shows how to call a Stored Procedure which doesnt return any parameter -
myTableRepository.inOnlyTest(inParam);

Other Tricks

Ive occasionally struggled with the above approach, and resorted to accessing the stored procedure as a native query through a custom repository.

This is done by defining a custom repository –

public interface MyTableRepositoryCustom {
void inOnlyTest(String inParam1);
}

We then make sure our main repository extends this interface –

public interface MyTableRepository extends CrudRepository<MyTable, Long>, MyTableRepositoryCustom {
}

We then define our custom repository –

public class MyTableRepositoryImpl implements MyTableRepositoryCustom {
@PersistenceContext
private EntityManager em;
@Override
public void inOnlyTest(String inParam1) {
this.em.createNativeQuery("BEGIN in_only_test(:inParam1); END;")
.setParameter("inParam1", inParam1)
.executeUpdate();
}
}

This can then be called in the normal way –

@Autowired
MyTableRepository myTableRepository;
// And to call the method -
myTableRepository.inOnlyTest(inParam1);

Conclusions

Calling Stored Procedures From Spring Data JPA can be delivered using custom Spring Repositories.