spring data jpa关联查询(一对一、一对多、多对多)
在实际过往的项目中,常用的查询操作有:1、单表查询,2、一对一查询(主表和详情表)3、一对多查询(一张主表,多张子表)4、多对多查询(如权限控制,用户、角色多对多)。做个总结,所以废话不多说。
使用idea构建springboot项目,引入依赖如下:
dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency><dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional><span style="color: #0000ff;">true</span></optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies></pre>
使用h2数据库做测试用,application.yml配置如下:
spring: jpa: generate-ddl: true hibernate: ddl-auto: update properties: hibenate: format_sql: false show-sql: true
首先,一对一有好几种,这里举例的是常用的一对一双向外键关联(改造成单向很简单,在对应的实体类去掉要关联其它实体的属性即可),并且配置了级联删除和添加,相关类如下:
package io.powerx;import lombok.*;
import javax.persistence.*;
/**
Created by Administrator on 2018/8/15.
*/
@Getter
@Setter
@Entity
public class Book {
@Id
@GeneratedValue
private Integer id;private String name;
@OneToOne(cascade = {CascadeType.PERSIST,CascadeType.REMOVE})
@JoinColumn(name="detailId",referencedColumnName = "id")
private BookDetail bookDetail;public Book(){
super();
}
public Book(String name){
super();
this.name =name;
}public Book(String name, BookDetail bookDetail) {
super();
this.name = name;
this.bookDetail = bookDetail;
}
@Override
public String toString() {
if (null == bookDetail) {
return String.format("Book [id=%s, name=%s, number of pages=%s]", id, name, "<EMPTY>");
}</span><span style="color: #0000ff;">return</span> String.format("Book [id=%s, name=%s, number of pages=%s]"<span style="color: #000000;">, id, name, bookDetail.getNumberOfPages());
}
}
package io.powerx;import lombok.Getter;
import lombok.Setter;import javax.persistence.*;
@Getter
@Setter
@Entity(name = "BOOK_DETAIL")
public class BookDetail {@Id @GeneratedValue </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> Integer id; @Column(name </span>= "NUMBER_OF_PAGES"<span style="color: #000000;">) </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> Integer numberOfPages; @OneToOne(mappedBy </span>= "bookDetail"<span style="color: #000000;">) </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> Book book; </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> BookDetail() { </span><span style="color: #0000ff;">super</span><span style="color: #000000;">(); } </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> BookDetail(Integer numberOfPages) { </span><span style="color: #0000ff;">super</span><span style="color: #000000;">(); </span><span style="color: #0000ff;">this</span>.numberOfPages =<span style="color: #000000;"> numberOfPages; } @Override </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String toString() { </span><span style="color: #0000ff;">if</span> (<span style="color: #0000ff;">null</span> ==<span style="color: #000000;"> book) { </span><span style="color: #0000ff;">return</span> String.format("Book [id=%s, name=%s, number of pages=%s]", id, "<EMPTY>"<span style="color: #000000;">); } </span><span style="color: #0000ff;">return</span> String.format("Book [id=%s, name=%s, number of pages=%s]"<span style="color: #000000;">, id,book.getId(),book.getName()); }
}
package io.powerx;import org.springframework.data.jpa.repository.JpaRepository;
/**
- Created by Administrator on 2018/8/15.
*/
public interface BookRepository extends JpaRepository<Book,Integer> {
Book findByName(String name);
}
package io.powerx;import org.springframework.data.jpa.repository.JpaRepository;
/**
Created by Administrator on 2018/8/15.
*/
public interface BookDetailRepository extends JpaRepository<BookDetail, Integer>{BookDetail findByNumberOfPages(Integer numberOfPages);
}
package io.powerx;import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;import java.util.Arrays;
import static org.junit.Assert.assertThat;
@RunWith(SpringRunner.class)
@SpringBootTest
public class OnetooneApplicationTests {@Autowired </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> BookRepository bookRepository; @Autowired </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> BookDetailRepository bookDetailRepository; @Before </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> init() { Book bookA </span>= <span style="color: #0000ff;">new</span> Book("Spring in Action", <span style="color: #0000ff;">new</span> BookDetail(208<span style="color: #000000;">)); Book bookB </span>= <span style="color: #0000ff;">new</span> Book("Spring Data in Action", <span style="color: #0000ff;">new</span> BookDetail(235<span style="color: #000000;">)); Book bookC </span>= <span style="color: #0000ff;">new</span> Book("Spring Boot in Action"<span style="color: #000000;">); bookRepository.saveAll(Arrays.asList(bookA, bookB, bookC)); } @After </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> clear() { bookRepository.deleteAll(); } @Test </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> find() { Book book </span>= bookRepository.findByName("Spring in Action"<span style="color: #000000;">); System.err.println(book.toString()); } @Test </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> save() { Book book </span>= <span style="color: #0000ff;">new</span> Book("springboot"<span style="color: #000000;">); BookDetail bookDetail </span>= <span style="color: #0000ff;">new</span> BookDetail(124<span style="color: #000000;">); book.setBookDetail(bookDetail); bookRepository.save(book); } @Test </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> delete() { bookRepository.deleteById(</span>31<span style="color: #000000;">); } @Test </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> findbook(){ BookDetail bd </span>= bookDetailRepository.findByNumberOfPages(235<span style="color: #000000;">); System.err.println(bd.toString()); }
}
一对多双向,相关类如下:
package io.powerx;import lombok.Data;
import lombok.Getter;
import lombok.Setter;import javax.persistence.*;
@Getter
@Setter
@Entity
public class Book {
@Id
@GeneratedValue
private Integer id;</span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String name; @ManyToOne @JoinColumn(name</span>="publishId"<span style="color: #000000;">) </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> Publisher publisher; @Override </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String toString() { </span><span style="color: #0000ff;">return</span> "Book{" + "id=" + id + ", name='" + name + '\'' + ", publisher=" + publisher.getName() + '}'<span style="color: #000000;">; } </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> Book(String name) { </span><span style="color: #0000ff;">this</span>.name =<span style="color: #000000;"> name; } </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> Book() { }
}
package io.powerx;import lombok.Data;
import lombok.Getter;
import lombok.Setter;import javax.persistence.*;
import java.util.HashSet;
import java.util.Set;/**
Created by Administrator on 2018/8/16.
*/
@Getter
@Setter
@Entity
public class Publisher {
@Id
@GeneratedValue
private Integer id;private String name;
@OneToMany(cascade = CascadeType.ALL,fetch = FetchType.EAGER)
@JoinColumn(name="publishId",referencedColumnName = "id")
private Set<Book> books;public Publisher() {
super();
}public Publisher(String name) {
super();
this.name = name;
}@Override
public String toString() {
return "Publisher{" +
"id=" + id +
", name='" + name + ''' +
", books=" + books.size() +
'}';
}}
package io.powerx;import org.springframework.data.jpa.repository.JpaRepository;
/**
Created by Administrator on 2018/8/16.
*/
public interface BookRepository extends JpaRepository<Book,Integer>{Book findByName(String name);
}
package io.powerx;import org.springframework.data.jpa.repository.JpaRepository;
/**
Created by Administrator on 2018/8/16.
*/
public interface PublisherRepository extends JpaRepository<Publisher,Integer> {Publisher findByName(String name);
}
package io.powerx;import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;import java.util.HashSet;
import java.util.Set;@RunWith(SpringRunner.class)
@SpringBootTest
public class OnetomanyApplicationTests {@Autowired </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> PublisherRepository publisherRepository; @Autowired </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> BookRepository bookRepository; @Before </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> init() { Book book1 </span>= <span style="color: #0000ff;">new</span> Book("spring"<span style="color: #000000;">); Book book2 </span>= <span style="color: #0000ff;">new</span> Book("mvc"<span style="color: #000000;">); Book book3 </span>= <span style="color: #0000ff;">new</span> Book("mybatis"<span style="color: #000000;">); Publisher publisher </span>= <span style="color: #0000ff;">new</span> Publisher("zhonghua"<span style="color: #000000;">); Set</span><Book> set = <span style="color: #0000ff;">new</span> HashSet<Book><span style="color: #000000;">(); set.add(book1); set.add(book2); set.add(book3); publisher.setBooks(set); publisherRepository.save(publisher); } @After </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> clear() { publisherRepository.deleteAll(); } @Test </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> find() { Publisher publisher </span>= publisherRepository.findByName("zhonghua"<span style="color: #000000;">); System.out.println(publisher); } @Test </span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> find2() { Book book </span>= bookRepository.findByName("mvc"<span style="color: #000000;">); System.out.println(book); }
}
多对多双向,相关代码如下:
package io.powerx;import lombok.Getter;
import lombok.Setter;
import lombok.ToString;import javax.persistence.*;
import java.util.Set;@Getter
@Setter
@Entity
public class Author {@Id @GeneratedValue </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> Integer id; </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String name; @ManyToMany(mappedBy </span>= "authors",fetch =<span style="color: #000000;"> FetchType.EAGER) </span><span style="color: #0000ff;">private</span> Set<Book><span style="color: #000000;"> books; </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> Author() { </span><span style="color: #0000ff;">super</span><span style="color: #000000;">(); } </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> Author(String name) { </span><span style="color: #0000ff;">super</span><span style="color: #000000;">(); </span><span style="color: #0000ff;">this</span>.name =<span style="color: #000000;"> name; } @Override </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String toString() { </span><span style="color: #0000ff;">return</span> "Author{" + "id=" + id + ", name='" + name + '\'' + ", books=" + books.size() + '}'<span style="color: #000000;">; }
}
package io.powerx;import lombok.Getter;
import lombok.Setter;
import lombok.ToString;import javax.persistence.*;
import java.util.HashSet;
import java.util.Set;@Getter
@Setter
@Entity
public class Book {@Id @GeneratedValue </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> Integer id; </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String name; @ManyToMany(cascade </span>= CascadeType.ALL,fetch =<span style="color: #000000;"> FetchType.EAGER) @JoinTable(name </span>= "BOOK_AUTHOR", joinColumns =<span style="color: #000000;"> { @JoinColumn(name </span>= "BOOK_ID", referencedColumnName = "ID")}, inverseJoinColumns =<span style="color: #000000;"> { @JoinColumn(name </span>= "AUTHOR_ID", referencedColumnName = "ID"<span style="color: #000000;">)}) </span><span style="color: #0000ff;">private</span> Set<Author><span style="color: #000000;"> authors; </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> Book() { </span><span style="color: #0000ff;">super</span><span style="color: #000000;">(); } </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> Book(String name) { </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>.authors = <span style="color: #0000ff;">new</span> HashSet<><span style="color: #000000;">(); } </span><span style="color: #0000ff;">public</span> Book(String name, Set<Author><span style="color: #000000;"> authors) { </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>.authors =<span style="color: #000000;"> authors; } @Override </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String toString() { </span><span style="color: #0000ff;">return</span> "Book{" + "id=" + id + ", name='" + name + '\'' + ", authors=" + authors.size() + '}'<span style="color: #000000;">; }
}
package io.powerx;import org.springframework.data.jpa.repository.JpaRepository;
import java.util.List;
public interface AuthorRepository extends JpaRepository<Author, Integer> {
Author findByName(String name); List</span><Author><span style="color: #000000;"> findByNameContaining(String name);
}
package io.powerx;import org.springframework.data.jpa.repository.JpaRepository;
import java.util.List;
public interface BookRepository extends JpaRepository<Book, Integer> {
Book findByName(String name); List</span><Book><span style="color: #000000;"> findByNameContaining(String name);
}
在调试过程中,注意实体类的tostring方法的重写,避免相互引用;此外如果超过两张表的关联查询,建议使用自定义sql,建立相应的pojo来接收查询结果。