在实际过往的项目中,常用的查询操作有: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>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
使用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>"); } return String.format("Book [id=%s, name=%s, number of pages=%s]", 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 private Integer id; @Column(name = "NUMBER_OF_PAGES") private Integer numberOfPages; @OneToOne(mappedBy = "bookDetail") private Book book; public BookDetail() { super(); } public BookDetail(Integer numberOfPages) { super(); this.numberOfPages = numberOfPages; } @Override public String toString() { if (null == book) { return String.format("Book [id=%s, name=%s, number of pages=%s]", id, "<EMPTY>"); } return String.format("Book [id=%s, name=%s, number of pages=%s]", 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 private BookRepository bookRepository; @Autowired private BookDetailRepository bookDetailRepository; @Before public void init() { Book bookA = new Book("Spring in Action", new BookDetail(208)); Book bookB = new Book("Spring Data in Action", new BookDetail(235)); Book bookC = new Book("Spring Boot in Action"); bookRepository.saveAll(Arrays.asList(bookA, bookB, bookC)); } @After public void clear() { bookRepository.deleteAll(); } @Test public void find() { Book book = bookRepository.findByName("Spring in Action"); System.err.println(book.toString()); } @Test public void save() { Book book = new Book("springboot"); BookDetail bookDetail = new BookDetail(124); book.setBookDetail(bookDetail); bookRepository.save(book); } @Test public void delete() { bookRepository.deleteById(31); } @Test public void findbook(){ BookDetail bd = bookDetailRepository.findByNumberOfPages(235); 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; private String name; @ManyToOne @JoinColumn(name="publishId") private Publisher publisher; @Override public String toString() { return "Book{" + "id=" + id + ", name='" + name + '\'' + ", publisher=" + publisher.getName() + '}'; } public Book(String name) { this.name = name; } public 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 private PublisherRepository publisherRepository; @Autowired private BookRepository bookRepository; @Before public void init() { Book book1 = new Book("spring"); Book book2 = new Book("mvc"); Book book3 = new Book("mybatis"); Publisher publisher = new Publisher("zhonghua"); Set<Book> set = new HashSet<Book>(); set.add(book1); set.add(book2); set.add(book3); publisher.setBooks(set); publisherRepository.save(publisher); } @After public void clear() { publisherRepository.deleteAll(); } @Test public void find() { Publisher publisher = publisherRepository.findByName("zhonghua"); System.out.println(publisher); } @Test public void find2() { Book book = bookRepository.findByName("mvc"); 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 private Integer id; private String name; @ManyToMany(mappedBy = "authors",fetch = FetchType.EAGER) private Set<Book> books; public Author() { super(); } public Author(String name) { super(); this.name = name; } @Override public String toString() { return "Author{" + "id=" + id + ", name='" + name + '\'' + ", books=" + books.size() + '}'; } }
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 private Integer id; private String name; @ManyToMany(cascade = CascadeType.ALL,fetch = FetchType.EAGER) @JoinTable(name = "BOOK_AUTHOR", joinColumns = { @JoinColumn(name = "BOOK_ID", referencedColumnName = "ID")}, inverseJoinColumns = { @JoinColumn(name = "AUTHOR_ID", referencedColumnName = "ID")}) private Set<Author> authors; public Book() { super(); } public Book(String name) { super(); this.name = name; this.authors = new HashSet<>(); } public Book(String name, Set<Author> authors) { super(); this.name = name; this.authors = authors; } @Override public String toString() { return "Book{" + "id=" + id + ", name='" + name + '\'' + ", authors=" + authors.size() + '}'; } }
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<Author> 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<Book> findByNameContaining(String name); }
在调试过程中,注意实体类的tostring方法的重写,避免相互引用;此外如果超过两张表的关联查询,建议使用自定义sql,建立相应的pojo来接收查询结果。
原文地址:https://www.cnblogs.com/hhhshct/p/9492741.html