In last article "Based on Spring Boot, Complete CRUD with JPA Operating Sql Server Database" The CRUD operation of entity data using JPA is completed.

In some cases, then, some query statements are written in the stored procedure and returned to the recordset by the stored procedure.

Here, the call is completed by creating a named stored procedure through EntityManager.

1. Create SQL stored procedures

Stored procedures return all contacts.

USE [demodb]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <bobenut>
-- Create date: <2017/9/14>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[proc_get_contacts_like_name]
    @name varchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * from contact where name like @name;
END

2. Define named stored procedures.

Write the mapping of stored procedures on the Contact entity under the package "com.kxh.example.demo.domain".

The @NamedStoredProcedureQueries annotation indicates that a mapping can contain multiple stored procedures.

The @NamedStoredProcedureQuery annotation is a mapping of a stored procedure.

The parameter name, which gives the mapping a name, is used in subsequent calls.

The parameter procedureName is the name of the real stored procedure in the database.

Parameters are mapping definitions of input or output parameters of stored procedures.

package com.kxh.example.demo.domain;

import javax.persistence.Entity;
import javax.persistence.EntityResult;
import javax.persistence.FieldResult;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedStoredProcedureQueries;
import javax.persistence.NamedStoredProcedureQuery;
import javax.persistence.ParameterMode;
import javax.persistence.SqlResultSetMapping;
import javax.persistence.StoredProcedureParameter;

@Entity
@NamedStoredProcedureQueries({
    @NamedStoredProcedureQuery(
            name = "getContactsLikeName", 
            procedureName = "proc_get_contacts_like_name", 
            resultClasses = { Contact.class },
            parameters = {
                    @StoredProcedureParameter(
                            mode = ParameterMode.IN, 
                            name = "name", 
                            type = String.class)
            }
        )
})
public class Contact {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    
    private String name;
    
    private String phone;
    
    private String mail;
    
    public Contact() {
        super();
    }
    
    public Contact(String name, String phone, String mail) {
        super();
        
        this.name = name;
        this.phone = phone;
        this.mail = mail;
    }
    
    public long getId() {
        return this.id;
    }
    
    public void setId(long value) {
        this.id = value;
    }
    
    public String getName() {
        return this.name;
    }
    
    public void setName(String value) {
        this.name = value;
    }
    
    public String getPhone() {
        return phone;
    }
    
    public void setPhone(String value) {
        this.phone = value;
    }
    
    public String getMail() {
        return this.mail;
    }
    
    public void setMail(String value) {
        this.mail = value;
    }
}

3. Call through business objects

Create the class ContactsService under the package "com.kxh.example.demo.service".

Within the class, the "EntityManager" is introduced and the @Autowire annotation is instantiated by the framework.

Create named stored procedure functions through EntityManager and pass in the mapping names defined above for specified calls.

Then set the input parameters for the stored procedure, execute and return the results.

package com.kxh.example.demo.service;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.StoredProcedureQuery;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.kxh.example.demo.domain.Contact;

@Component
public class ContactsService {
    @Autowired
    private EntityManager entityManager;
    
    @SuppressWarnings("unchecked")
    public List<Contact> findAllViaProc(String name) {
       StoredProcedureQuery storedProcedureQuery = this.entityManager.createNamedStoredProcedureQuery("getContactsLikeName");
       storedProcedureQuery.setParameter("name", name);
       storedProcedureQuery.execute();
       return storedProcedureQuery.getResultList();
    }
}

4. Providing services to the outside world through RestController

Introduce "ContactService" as a member variable and Autowired.

Add a new access path mapping to call contactsService.findAllViaProc(nameWhere) in the processing method to get the query result set.

package com.kxh.example.demo.controller;

import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import com.kxh.example.demo.dao.ContactsRepository;
import com.kxh.example.demo.domain.Contact;
import com.kxh.example.demo.service.ContactsService;

@RestController
@RequestMapping("/contacts")
public class ContactsController {
        
    @Autowired
    ContactsService contactsService;
    //ellipsis//Check through stored procedures
    @RequestMapping(value="/query/viaproc/likename", method=RequestMethod.GET)
    public List<Contact> findContactsUseProcLikeName(String name) {
        System.out.println("kxh1");
        String nameWhere = org.apache.commons.lang.StringUtils.join(new String[]{"%", name, "%"}, "");
        List<Contact> contacts = contactsService.findAllViaProc(nameWhere);
        if(contacts == null) {
            return new ArrayList<Contact>();
        } else {
            return contacts;
        }
    }
    
    //ellipsis
}

Code

End