基於Spring Boot，使用JPA動態調用Sql查詢數據

在《基於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;

@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();
    }
    
    @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