×
首页数据库正文

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

评论关闭
IT序号网
IT序号网 Spring

微信公众号号：IT虾米 （左侧二维码扫一扫）欢迎添加！