基於Spring Boot，使用JPA動態調用Sql查詢資料，springjpa

基於Spring Boot，使用JPA動態調用Sql查詢資料，springjpa

在《基於Spring Boot，使用JPA操作Sql Server資料庫完成CRUD》，《基於Spring Boot，使用JPA調用Sql Server資料庫的預存程序並返回記錄集合》完成了CRUD，調用預存程序查詢資料。

很多複雜的情況下，會存在要直接執行SQL來擷取資料。

通過“EntityManager”建立NativeQuery方法來執行動態SQL。

 

1.查詢結果集映射

在包“com.kxh.example.demo.domain”下的“Contact”實體上編寫命名的結果集映射，因為可以寫很多映射。

@SqlResultSetMapping註解即為映射。

name參數，可以為結果集映射取個名字。

entities參數，用來說明把Entity和查詢的結果欄位進行關聯說明。

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@SqlResultSetMapping(        name = "conatctMapping",         entities = @EntityResult(            entityClass = Contact.class,             fields = {                @FieldResult(name = "name", column = "name"),                @FieldResult(name = "phone", column = "phone"),                @FieldResult(name = "mail", column = "mail")}))@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.通過業務對象調用

在包“com.kxh.example.demo.service”下的類“ContactsService”中添加執行函數。

通過"EntityManager"建立NativeQuery函數，第一參數是Sql，第二個參數就是上面定義的結果集映射名。

然後傳入查詢條件參數，設定最大返回結果記錄數，擷取查詢結果集。

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();    }        @SuppressWarnings("unchecked")    public List<Contact> findAllByViaQuery(String name) {        List<Contact> contacts = this.entityManager                .createNativeQuery("select name, phone, mail from contact where name like :name", "conatctMapping")                .setParameter("name", name)                .setMaxResults(5)                .getResultList();                return contacts;    }}

 

4.通過RestController向外提供服務

增加一個新的訪問路徑映射，在處理方法中調用contactsService.findAllByViaQuery(nameWhere)擷取查詢結果集。

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;//省略//通過動態sql查    @RequestMapping(value="/query/viadnq/likename", method=RequestMethod.GET)    public List<Contact> findContactsUseDyanamicQueryLikeName(String name) {        System.out.println("kxh1");        String nameWhere = org.apache.commons.lang.StringUtils.join(new String[]{"%", name, "%"}, "");        List<Contact> contacts = contactsService.findAllByViaQuery(nameWhere);        if(contacts == null) {            System.out.println("kxh4");            return new ArrayList<Contact>();        } else {            System.out.println("kxh5");            return contacts;        }    }}

 

代碼

 

End