基于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;
</span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String name;

</span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String phone;

</span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String mail;

</span><span style="color: #0000ff;">public</span><span style="color: #000000;"> Contact() {
    </span><span style="color: #0000ff;">super</span><span style="color: #000000;">();
}

</span><span style="color: #0000ff;">public</span><span style="color: #000000;"> Contact(String name, String phone, String mail) {
    </span><span style="color: #0000ff;">super</span><span style="color: #000000;">();
    
    </span><span style="color: #0000ff;">this</span>.name =<span style="color: #000000;"> name;
    </span><span style="color: #0000ff;">this</span>.phone =<span style="color: #000000;"> phone;
    </span><span style="color: #0000ff;">this</span>.mail =<span style="color: #000000;"> mail;
}

</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">long</span><span style="color: #000000;"> getId() {
    </span><span style="color: #0000ff;">return</span> <span style="color: #0000ff;">this</span><span style="color: #000000;">.id;
}

</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span> setId(<span style="color: #0000ff;">long</span><span style="color: #000000;"> value) {
    </span><span style="color: #0000ff;">this</span>.id =<span style="color: #000000;"> value;
}

</span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String getName() {
    </span><span style="color: #0000ff;">return</span> <span style="color: #0000ff;">this</span><span style="color: #000000;">.name;
}

</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> setName(String value) {
    </span><span style="color: #0000ff;">this</span>.name =<span style="color: #000000;"> value;
}

</span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String getPhone() {
    </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> phone;
}

</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> setPhone(String value) {
    </span><span style="color: #0000ff;">this</span>.phone =<span style="color: #000000;"> value;
}

</span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String getMail() {
    </span><span style="color: #0000ff;">return</span> <span style="color: #0000ff;">this</span><span style="color: #000000;">.mail;
}

</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> setMail(String value) {
    </span><span style="color: #0000ff;">this</span>.mail =<span style="color: #000000;"> 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(</span>"unchecked"<span style="color: #000000;">)
</span><span style="color: #0000ff;">public</span> List&lt;Contact&gt;<span style="color: #000000;"> findAllViaProc(String name) {
   StoredProcedureQuery storedProcedureQuery </span>= <span style="color: #0000ff;">this</span>.entityManager.createNamedStoredProcedureQuery("getContactsLikeName"<span style="color: #000000;">);
   storedProcedureQuery.setParameter(</span>"name"<span style="color: #000000;">, name);
   storedProcedureQuery.execute();
   </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> 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();
    </span><span style="color: #0000ff;">return</span></span><span style="color: #000000;"><span style="background-color: #ffff00;"> contacts;
}</span>

}

 

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;</span><span style="color: #008000;">//省略</span>

//通过动态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 

 

原文地址：https://www.cnblogs.com/kongxianghai/p/7575988.html