在《基于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
