使用JPA進(jìn)行數(shù)據(jù)查詢和關(guān)聯(lián)查詢
在開(kāi)發(fā)中經(jīng)常會(huì)使用mybatis、jpa等框架來(lái)解決O/R映射技術(shù)實(shí)現(xiàn)數(shù)據(jù)訪問(wèn)。本文主要介紹使用JPA實(shí)現(xiàn)數(shù)據(jù)訪問(wèn)。
通常在關(guān)聯(lián)查詢的時(shí)候，表與表之前存在的關(guān)系有</br>
OneToOne</br>
OneToMany</br>
ManyToOne</br>
ManyToMany</br>
想要理清兩個(gè)表之間的關(guān)系需要根據(jù)實(shí)際場(chǎng)景進(jìn)行區(qū)分酥诽。在建立實(shí)體類的時(shí)候，需要知道哪一個(gè)是主體皱埠。通常情況下一對(duì)多和多對(duì)一始終是以多的一方為主體的肮帐。注解在使用中“始終在非主體的一方標(biāo)記自己在主體中的名稱”
基本數(shù)據(jù)查詢
舉例:有如下幾個(gè)表的關(guān)系,表之間的ER圖如下:
1
image.png
一對(duì)一
Student和Sore對(duì)應(yīng)的關(guān)系，score類如下:</br>
package spring.demo.security.entity.test;
import javax.persistence.*;
/**
* Created by td on 2017/10/12.
*/
@Entity
@Table(name = "score")
public class Score {
@Id
@GeneratedValue
private Integer id;
@Column(name = "chinese_score")
private Integer chinese;
@Column(name = "math_score")
private Integer math;
//通常情況下score和student中边器，認(rèn)為studnet是主體训枢，我們需要在sore非主體中標(biāo)記他在主體中的名字
@OneToOne(mappedBy = "score")
private Student student;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getChinese() {
return chinese;
}
public void setChinese(Integer chinese) {
this.chinese = chinese;
}
public Integer getMath() {
return math;
}
public void setMath(Integer math) {
this.math = math;
}
}
student類如下:</br>
package spring.demo.security.entity.test;
import javax.persistence.*;
/**
* Created by td on 2017/10/12.
*/
@Entity
@Table(name = "student")
public class Student {
@Id
@GeneratedValue
private Integer id;
private String username;
@OneToOne
private Score score;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
}
```</br>
這兩個(gè)類中需要區(qū)分誰(shuí)是主體，按照?qǐng)鼍袄斫釹tudent和Score會(huì)認(rèn)為Student,所以我們需要在非主體的Score中指定其在Student中的名字忘巧。</br>
此外還可以設(shè)置級(jí)聯(lián)恒界，只需要在注解中增加參數(shù)(cascade = CascadeType.REMOVE )，必需要在主體一側(cè)添加級(jí)聯(lián)砚嘴。(一般情況下不用)<br>
#### 一對(duì)多（多對(duì)一）
以下建立student和school的關(guān)系,student是主體,下面是School類代碼<br>
```java
package spring.demo.security.entity.test;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import java.util.List;
/**
* Created by td on 2017/10/12.
*/
public class School {
@Id
@GeneratedValue
private Integer id;
private String name;
@OneToMany(mappedBy = "school")
private List<Student> students;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<Student> getStudents() {
return students;
}
public void setStudents(List<Student> students) {
this.students = students;
}
}
在Student中加入school類的十酣，多對(duì)一關(guān)系,并且需要在school類中指定其在student中的名字
// student類中加入school類，多對(duì)一關(guān)系
@ManyToOne
private School school;
// school類中加入student并指明在student中的名字
@OneToMany(mappedBy = "school")
private List<Student> students;
多對(duì)多
Subject和Student之間是多對(duì)多的關(guān)系际长，下面建立subject類
package spring.demo.security.entity.test;
import javax.persistence.*;
import java.util.List;
/**
* Created by td on 2017/10/12.
*/
@Entity
@Table(name = "subject")
public class Subject {
@Id
@GeneratedValue
private Integer id;
@Column(length = 10)
private String name;
@ManyToMany(mappedBy = "subjects")
private List<Student> students;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
在student類中加入
@ManyToMany
private List<Subject> subjects;
基本查詢
基本查詢通過(guò)繼承JpaRepository<T,key>就可以了,如果符合規(guī)范可以不用寫實(shí)現(xiàn)
public interface StudentRepository extends JpaRepository<Student,Integer> {
}
通過(guò)以上就可以進(jìn)行基本的增刪改查
如果JpaRepository條件不能滿足需求耸采，也可以自定義Repository自定義條件:
舉例：需要通過(guò)一個(gè)學(xué)生的id 查詢出這個(gè)學(xué)生所在學(xué)校的的名字
根據(jù)SimpleJpaRepository寫一個(gè)Repository。
package spring.demo.security.dao;
import org.apache.commons.beanutils.ConvertUtils;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.transaction.Transactional;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* @author td
* @date 2017/10/19
*/
@Transactional
@Repository
public class CustomDao {
@PersistenceContext
EntityManager entityManager;
public List<?> queryListEntity (String sql, Map<String,Object> params,Class<?> clazz) {
Session session = entityManager.unwrap(Session.class);
SQLQuery sqlQuery = session.createSQLQuery(sql);
System.out.println(sqlQuery.toString());
if (params != null) {
for (String key:params.keySet()) {
sqlQuery.setParameter(key,params.get(key));
}
}
System.out.println(sqlQuery.toString());
sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
List<Map<String,Object>> result = sqlQuery.list();
if (clazz != null) {
List<Object> entityList = convert(clazz,result);
return entityList;
}
return result;
}
private List<Object> convert(Class<?> clazz, List<Map<String, Object>> list) {
List<Object> result;
if (CollectionUtils.isEmpty(list)) {
return null;
}
result = new ArrayList<Object>();
try {
PropertyDescriptor[] props = Introspector.getBeanInfo(clazz).getPropertyDescriptors();
for (Map<String, Object> map : list) {
Object obj = clazz.newInstance();
for (String key:map.keySet()) {
String attrName = key.toLowerCase();
for (PropertyDescriptor prop : props) {
attrName = removeUnderLine(attrName);
if (!attrName.equals(prop.getName())) {
continue;
}
Method method = prop.getWriteMethod();
Object value = map.get(key);
if (value != null) {
value = ConvertUtils.convert(value,prop.getPropertyType());
}
method.invoke(obj,value);
}
}
result.add(obj);
}
} catch (Exception e) {
throw new RuntimeException("數(shù)據(jù)轉(zhuǎn)換錯(cuò)誤");
}
return result;
}
// 將下劃線轉(zhuǎn)換為駝峰命名方式
private String removeUnderLine(String attrName) {
//去掉數(shù)據(jù)庫(kù)字段的下劃線
if(attrName.contains("_")) {
String[] names = attrName.split("_");
String firstPart = names[0];
String otherPart = "";
for (int i = 1; i < names.length; i++) {
String word = names[i].replaceFirst(names[i].substring(0, 1), names[i].substring(0, 1).toUpperCase());
otherPart += word;
}
attrName = firstPart + otherPart;
}
return attrName;
}
public Integer getCountBy(String sql,Map<String,Object> params) {
Query query = entityManager.createNativeQuery(sql);
if (params != null) {
for (String key : params.keySet()) {
query.setParameter(key,params.get(key));
}
}
BigInteger bigInteger = (BigInteger) query.getSingleResult();
return bigInteger.intValue();
}
public Integer deleteOrUpDate(String sql,Map<String,Object> params) {
Query query = entityManager.createNativeQuery(sql);
if (params != null) {
for (String key: params.keySet()) {
query.setParameter(key,params.get(key));
}
}
return query.executeUpdate();
}
}
進(jìn)行測(cè)試
// 查詢一個(gè)id為1的學(xué)生學(xué)校名字
String sql = "select b.* from student a left join school b on a.school_id=b.id where a.id=:id";
// String sql = "select * from person where id = :id";
Map<String,Object> map = new HashMap<>();
map.put("id",1);
List<School> schools = (List<School>) customDao.queryListEntity(sql,map, School.class);
System.out.println(schools.get(0).getId()+"-"+schools.get(0).getName());
int count = customDao.getCountBy("select count(*) from student",null);
System.out.println(count);
關(guān)聯(lián)查詢
舉例:需要通過(guò)學(xué)校的id查詢?cè)搶W(xué)生的所有信息
從以上需求可以看出需要到school和student關(guān)系的主體表中進(jìn)行查詢工育。即在student表中進(jìn)行查詢虾宇，通過(guò)jpa規(guī)范書寫查詢
//通過(guò)學(xué)生id進(jìn)行查詢
SysRole findById(Integer id);
//通過(guò)學(xué)校id進(jìn)行關(guān)聯(lián)查詢角色，關(guān)聯(lián)的用戶表用在主體表中的表示名Student 關(guān)聯(lián)的id后用_Id
//所以可以看出關(guān)聯(lián)查詢主要是通過(guò)_進(jìn)行的
List<Student> findBySchool_Id(int id);
從例子中可以看出關(guān)聯(lián)查詢主要是用""符號(hào)翅娶，
如果通過(guò)本章表中id查詢本章表中的role信息可以這么寫：findById()
如果通過(guò)本章用戶表中id查詢本章表中的role信息可以這么寫:findBySUser_Id()
如果既要通過(guò)本章表中的id查詢有要通過(guò)用戶表中的id進(jìn)行查詢,可以這么寫:findBySUser_IdAndId()
如何還想關(guān)聯(lián)更多的表可以在后面添加：And+表名字+“”+表中要查詢的字段文留。
