Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Programming \ Spring Data JPA call a stored procedure examples     - How to implement large-scale distributed Yahoo depth study on the Hadoop cluster (Server)

- Debian (Wheezy) were installed wxPython GUI development (Linux)

- HTML5 postMessage cross-domain data exchange (Programming)

- OpenGL Superb Learning Notes - New Patterns (Programming)

- How to monitor Linux system performance Nmon (Linux)

- numpy and SciPy installation under Python for scientific computing package (Linux)

- MongoDB learning Notes (2) basic connection example of -Nodejs and MongoDB (Database)

- Matters Oracle 11.2 single instance when connecting ASM need to pay attention and deal with the problem (Database)

- Use Swift remove the spaces in the string (Programming)

- How open source code libraries hosted on Github (Linux)

- Linux system started to learn: how to view the Linux thread of a process (Linux)

- Getting Started with Linux system to learn: how to check in a package is installed on Ubuntu (Linux)

- Linux device driver development small example --LED lights (Programming)

- True and false in Perl (Programming)

- Spring JDBC Comments (Programming)

- Configuring a Linux operating system security management services Explain (Linux)

- Protection Docker container Notice (Server)

- Based LNMP + uWSGI deploy Django applications (Server)

- Linux system monitoring, top command of the diagnostic tool Detailed (Linux)

- Two programs HAProxy + Heartbeat (note, the secondary server vip, normal HAProxy boot method) (Server)

  Spring Data JPA call a stored procedure examples
  Add Date : 2018-11-21      
  JPA connect to the database, call a stored procedure, this demand is very common. This article will address this point, how to use Spring Data JPA call a stored procedure approach.

1, stored procedures
Suppose stored procedure is as follows:

  PROCEDURE in_only_test (inParam1 IN VARCHAR2);
  PROCEDURE in_and_out_test (inParam1 IN VARCHAR2, outParam1 OUT VARCHAR2);
END test_pkg;
  PROCEDURE in_only_test (inParam1 IN VARCHAR2) AS
      DBMS_OUTPUT.PUT_LINE ( 'in_only_test');
  END in_only_test;
  PROCEDURE in_and_out_test (inParam1 IN VARCHAR2, outParam1 OUT VARCHAR2) AS
      outParam1: = 'Woohoo Im an outparam, and this is my inparam' || inParam1;
  END in_and_out_test;
END test_pkg;
There are two stored procedures:

1) in_only_test
It requires an input parameter inParam1, but does not return a value

2) in_and_out_test
It requires an input parameter inParam1, and the return value outParam1

2, @ NamedStoredProcedureQueries
We can use @NamedStoredProcedureQueries comments to call a stored procedure.

@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 {
Key points:

Stored procedures used comments @NamedStoredProcedureQuery, and bind to a JPA table.
procedureName is the name of the stored procedure
name is the name of the stored procedure JPA
To define the stored procedure used IN / OUT parameters using annotations @StoredProcedureParameter
3. Create a database Spring Data JPA
Let's create the Spring Data JPA database:

public interface MyTableRepository extends CrudRepository {
    @Procedure (Name = "in_only_test")
    void inOnlyTest (@Param ( "inParam1") String inParam1);

    @Procedure (Name = "in_and_out_test")
    String inAndOutTest (@Param ( "inParam1") String inParam1);
Key points:

@Procedure The name parameter must match the name @NamedStoredProcedureQuery
@Param Must match the name parameter @StoredProcedureParameter comment
The return type must match: in_only_test stored procedure that returns a void, in_and_out_test stored procedure must return String
4, call
We can call a stored procedure:

    // Pass parameters to the stored procedure and returns a value
    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");
    // Pass parameters to the stored procedure does not return value
    myTableRepository.inOnlyTest (inParam);
5. Other tips
If the above code does not work, so to solve. Define custom Repository to call the stored procedure last night local query.

Define custom Repository:

public interface MyTableRepositoryCustom {
    void inOnlyTest (String inParam1);
Then we want to ensure that the primary Repository classes inherit this interface.

public interface MyTableRepository extends CrudRepository , MyTableRepositoryCustom {
6. Create Repository implementation class
Then create the implementation class of the Repository:

public class MyTableRepositoryImpl implements MyTableRepositoryCustom {
private EntityManager em;

public void inOnlyTest (String inParam1) {
    this.em.createNativeQuery ( "BEGIN in_only_test (: inParam1); END;") setParameter ( "inParam1", inParam1).
            .executeUpdate ();

It can be called in a conventional manner:

MyTableRepository myTableRepository;
// Call a stored procedure
myTableRepository.inOnlyTest (inParam1);
It is not very simple.
- Unable to start the network after restart clone a virtual machine (Linux)
- MySQL and MariaDB new master from the cluster configuration GTID (Database)
- How to use Monit to deploy a server to monitor the system (Server)
- Restore database fault encountered ORA-0600 (Database)
- A custom implementation of the Android sidebar (Programming)
- MySQL 5.5 on master-slave copy filter (Database)
- Getting the Linux shell expr use (Programming)
- Linux, Firefox unloading and installation and upgrade (Linux)
- Ubuntu system process is bound CPU core (Linux)
- Oracle 11g creates virtual private directory RMAN-06004 ORA-00942 error handling (Database)
- Using Linux / Unix Text processing (Linux)
- RabbitMQ installation, configuration, monitoring (Linux)
- MySQL Authorized a recovered (Database)
- Linux SSH login without a password (Linux)
- MySQL dual master configuration (Database)
- ImportTsv-HBase data import tool (Database)
- 64 Ubuntu 15.04 Linux kernel upgrade to Linux 4.1.0 (Linux)
- The PostgreSQL database pg_dump command line does not enter a password method (Database)
- Windows Desktop use VNC remote connect Linux (Linux)
- CentOS 6.6 permanent method to modify the DNS address (Linux)
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.