《基于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虾米 (左侧二维码扫一扫)欢迎添加!

MySQL列出当前月的每一天知识解答
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。