Based on Spring Boot, use JPA to call the stored procedures of the SQL Server database and return a set of records. springjpa

Based on Spring Boot, use JPA to call the stored procedures of the SQL Server database and return a set of records. springjpa

In the previous article "using Spring Boot and JPA to operate SQL Server database to complete CRUD", we completed the CRUD operation on Object Data Using JPA.

In some cases, some query statements are written in the stored procedure, and the stored procedure returns the record set.

In this case, you can use EntityManager to create a named stored procedure.

 

1. Create an SQL stored procedure

The stored procedure returns all contacts.

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

 

2. Define a named stored procedure.

Write the Stored Procedure ing on the "Contact" entity under the "com. kxh. example. demo. domain" package.

@ NamedStoredProcedureQueries the annotation indicates that it can contain Mappings of multiple stored procedures.

@ NamedStoredProcedureQuery annotation is the ing of a stored procedure.

Parameter name, which is used for subsequent calls.

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

Parameter parameters is a ing definition of input or output parameters in a stored procedure.

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. Calling through Business Objects

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

In the class, "EntityManager" is introduced, and the @ Autowired annotation is instantiated by the framework.

Use "EntityManager" to create a named stored procedure function, and pass in the ing name defined above for the specified call.

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;@Componentpublic 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. provide external services through RestController

Introduce "ContactService" as a member variable and use Autowired.

Add a new access path ing and call contactsService. findAllViaProc (nameWhere) in the processing method to obtain 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; // omitted // query through the Stored Procedure @ 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 ;}// omitted}

 

Code

 

End