SpringDataJpa （二）-動態查詢&多表操作

向科銘．發表於2020-10-22
Spring

SpringDataJpa （二）-動態查詢&多表操作

一.動態查詢

1.Specifications動態查詢

繼承的是JpaSpecificationExecutor介面
方法列表：
1.findone(Specification spec)
2.List findAll(Specification spec)
Pageable 查詢全部分頁
3.Page findAll(Specification spec，Pageable pageable)
Sort 排序
4…List findAll(Specification spec，Sort sort)
5.long count(Specification spec)

Specification（是一個介面）：查詢條件
需要自定義我們自己的Specification實現類
需要實現以下方法：
Predicate toPredicate (Root root,CriteriaQuery<?> query,CriteriaBuilder cb)
其中的引數：
1.root：查詢的根物件（查詢的任何屬性都可以從跟根物件中獲取）
2.CriteriaQuery：頂層查詢物件，自定義查詢方法（瞭解，一般不用）
3.CriteriaBuilder：查詢的構造器，封裝很多查詢條件

1.1 搭建測試環境

1.1.1 匯入座標

    <properties>
        <spring.version>4.2.4.RELEASE</spring.version>
        <hibernate.version>5.0.7.Final</hibernate.version>
        <slf4j.version>1.6.6</slf4j.version>
        <log4j.version>1.2.12</log4j.version>
        <c3p0.version>0.9.1.2</c3p0.version>
        <mysql.version>5.1.6</mysql.version>
    </properties>

    <dependencies>
        <!-- junit單元測試 -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.9</version>
            <scope>test</scope>
        </dependency>

        <!-- spring beg -->
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>1.6.8</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aop</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context-support</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <!-- spring end -->

        <!-- hibernate beg -->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>${hibernate.version}</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-entitymanager</artifactId>
            <version>${hibernate.version}</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-validator</artifactId>
            <version>5.2.1.Final</version>
        </dependency>
        <!-- hibernate end -->

        <!-- c3p0 beg -->
        <dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>${c3p0.version}</version>
        </dependency>
        <!-- c3p0 end -->

        <!-- log end -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>${log4j.version}</version>
        </dependency>

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>${slf4j.version}</version>
        </dependency>

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>${slf4j.version}</version>
        </dependency>
        <!-- log end -->


        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-jpa</artifactId>
            <version>1.9.0.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>4.2.4.RELEASE</version>
        </dependency>

        <!-- el beg 使用spring data jpa 必須引入 -->
        <dependency>
            <groupId>javax.el</groupId>
            <artifactId>javax.el-api</artifactId>
            <version>2.2.4</version>
        </dependency>

        <dependency>
            <groupId>org.glassfish.web</groupId>
            <artifactId>javax.el</artifactId>
            <version>2.2.4</version>
        </dependency>

        <dependency>
            <groupId>javax.xml.bind</groupId>
            <artifactId>jaxb-api</artifactId>
            <version>2.3.0</version>
        </dependency>
        <dependency>
            <groupId>com.sun.xml.bind</groupId>
            <artifactId>jaxb-impl</artifactId>
            <version>2.3.0</version>
        </dependency>
        <dependency>
            <groupId>com.sun.xml.bind</groupId>
            <artifactId>jaxb-core</artifactId>
            <version>2.3.0</version>
        </dependency>
        <dependency>
            <groupId>javax.activation</groupId>
            <artifactId>activation</artifactId>
            <version>1.1.1</version>
        </dependency>
        <!-- el end -->
    </dependencies>
    <build>
        <pluginManagement>
            <plugins>
                <!--jdk編譯外掛-->
                <plugin>
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-compiler-plugin</artifactId>
                    <version>3.8.0</version>
                    <configuration>
                        <source>1.8</source>
                        <target>1.8</target>
                        <encoding>utf-8</encoding>
                    </configuration>
                </plugin>
                <!--tomcat外掛-->
                <plugin>
                    <groupId>org.apache.tomcat.maven</groupId>
                    <!-- tomcat7的外掛， 不同tomcat版本這個也不一樣 -->
                    <artifactId>tomcat7-maven-plugin</artifactId>
                    <version>2.1</version>
                    <configuration>
                        <!-- 通過maven tomcat7:run執行專案時，訪問專案的埠號 -->
                        <port>80</port>
                        <!-- 專案訪問路徑  本例：localhost:9090,  如果配置的aa， 則訪問路徑為localhost:9090/aa-->
                        <path>/travel</path>
                    </configuration>
                </plugin>
            </plugins>
        </pluginManagement>
    </build>

1.1.2 建立客戶實體類

@Entity
@Table(name="cst_customer")
public class Customer {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "cust_id")
    private long custId; //客戶主鍵
    @Column(name = "cust_name")
    private String custName; //客戶名稱
    @Column(name = "cust_source")
    private String custSource; //客戶來源
    @Column(name = "cust_industry")
    private String custIndustry; //客戶級別
    @Column(name = "cust_level")
    private String custLevel;  //客戶所屬行業
    @Column(name = "cust_address")
    private String custAddress; //客戶地址
    @Column(name = "cust_phone")
    private String custPhone; //客戶聯絡方式

1.1.3 springdatajpa 核心配置檔案

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:jpa="http://www.springframework.org/schema/data/jpa" xmlns:task="http://www.springframework.org/schema/task"
       xsi:schemaLocation="
		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
		http://www.springframework.org/schema/data/jpa
		http://www.springframework.org/schema/data/jpa/spring-jpa.xsd">

    <bean id="dataSources" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.jdbc.Driver" />
        <property name="jdbcUrl" value="jdbc:mysql:///jpa" />
        <property name="user" value="root" />
        <property name="password" value="root" />
    </bean>
<!--1.建立entityManagerfactory物件交給spring容器管理-->
    <bean id="entityManager" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="dataSource" ref="dataSources"></property>
<!--        2.配置的是掃描的包，實體類所在包-->
        <property name="packagesToScan" value="cn.xkm.domain"></property>
<!--        3.jpa實現廠家-->
        <property name="persistenceProvider">
            <bean class="org.hibernate.jpa.HibernatePersistenceProvider"></bean>
        </property>
        <!--JPA的供應商介面卡-->
        <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
<!--            配置是否自動建立資料庫表-->
            <property name="generateDdl" value="false" />
<!--            指定資料庫型別-->
            <property name="database" value="MYSQL" />
<!--            資料庫方言，支援的特有語法-->
            <property name="databasePlatform" value="org.hibernate.dialect.MySQLDialect" />
            <property name="showSql" value="true" />
        </bean>
        </property>
<!--        jpa的方言：高階的特性-->
        <property name="jpaDialect">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect"></bean>
        </property>
    </bean>

<!--    整合springdatajpa-->
    <jpa:repositories base-package="cn.xkm.dao" transaction-manager-ref="transactionManager"
                      entity-manager-factory-ref="entityManager"></jpa:repositories>
    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="entityManager"></property>
    </bean>
<!--    spring宣告式事務,此處省略-->
<!--    註解包掃描-->
    <context:component-scan base-package="cn.xkm"></context:component-scan>
</beans>

1.1.4 編寫dao介面

public interface Customer extends JpaRepository<cn.xkm.domain.Customer,Long>, JpaSpecificationExecutor<cn.xkm.domain.Customer> {
    
}

1.1.5 案列測試程式碼

1.1.5.1 查詢所有
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:applicationContext.xml")
public class SpecTest {
   @Autowired
    private CustomerDao customerDao;
   @Test
    public void testSpec(){
//       匿名內部類
//       自定義查詢條件
       Specification<Customer> spec = new Specification<Customer>(){
           @Override
           public Predicate toPredicate(Root<Customer> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
//               1.獲取比較的屬性
               Path<Object> custName = root.get("custName");
//               2.構造查詢
               Predicate xkm = criteriaBuilder.equal(custName, "xkm");
               return xkm;
           }
       };
       List<Customer> all = customerDao.findAll(spec);
       all.forEach(a-> System.out.println(a));
   }
}
1.1.5.2 多條件查詢
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:applicationContext.xml")
public class SpecTest {
   @Autowired
    private CustomerDao customerDao;
   @Test
    public void testSpec(){
//       匿名內部類
//       自定義查詢條件
       Specification<Customer> spec = new Specification<Customer>(){
           @Override
           public Predicate toPredicate(Root<Customer> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
//               1.獲取比較的屬性
               Path<Object> custName = root.get("custName");
               Path<Object> custIndustry = root.get("custIndustry");
//               2.構造查詢
               Predicate xkm = criteriaBuilder.equal(custName, "xkm");
               Predicate boss = criteriaBuilder.equal(custIndustry, "Boss");
               Predicate and = criteriaBuilder.and(xkm, boss);
               return and;
           }
       };
       Customer one = customerDao.findOne(spec);
       System.out.println(one);

   }
}
1.1.5.3 模糊查詢
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:applicationContext.xml")
public class SpecTest {
   @Autowired
    private CustomerDao customerDao;
   @Test
    public void testSpec(){
//       匿名內部類
//       自定義查詢條件
       Specification<Customer> spec = new Specification<Customer>(){
           @Override
           public Predicate toPredicate(Root<Customer> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
//               1.獲取比較的屬性
               Path<Object> custName = root.get("custName");
               Predicate like = criteriaBuilder.like(custName.as(String.class), "x%");
               return like;
           }
       };
       List<Customer> all = customerDao.findAll(spec);
       all.forEach(a -> System.out.println(all));

   }
1.1.5.4 Sort排序
       Sort sort = new Sort(Sort.Direction.DESC,"custId");
       List<Customer> all = customerDao.findAll(spec,sort);
       for (Customer customer : all) {
           System.out.println(customer);
       }
1.1.5.5 pageable分頁
       Pageable pageable = new PageRequest(0, 2);
       Page<Customer> all = customerDao.findAll(spec, pageable);
       for (Customer customer : all) {
           System.out.println(customer);
       }
//       獲取結果列表
       System.out.println(all.getContent());
//       獲取當前頁總數
       System.out.println(all.getTotalPages());
//       獲取總結果數
       System.out.println(all.getTotalElements());

   }

2.多表查詢（一對多）

2.1.配置環境

2.1.1 新建一個工程，新增兩個表的類並配置對映關係

//配置客戶和聯絡人之間的關係
//    @OneToMany(targetEntity = LinkMan.class)
//    name:配置外來鍵欄位名稱
//    referencedColumnNam：對應的主鍵欄位名
//    @JoinColumn(name = "lkm_cusr_id",referencedColumnName = "cust_id")

	@OneToMany(mappedBy = "customer")//放棄外來鍵維護權，mappedBy：對配置關係的屬性名
    private Set<LinkMan> linkmans  = new HashSet<>();

    @ManyToOne(targetEntity = Customer.class,fetch = FetchType.LAZY)
    @JoinColumn(name = "lkm_cust_id",referencedColumnName = "cust_id")
    private Customer customer;

2.1.2 測試儲存操作

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:applicationContext.xml")
public class SpecTest {
   @Autowired
    private CustomerDao customerDao;
   @Autowired
   private LinkManDao linkManDao;
   @Test
   @Transactional
   @Rollback(false)
    public void saveTest(){
       Customer customer = new Customer();
       customer.setCustName("向科銘");
       LinkMan linkMan = new LinkMan();
       linkMan.setLkmName("美國隊長");
//       建立客戶和聯絡人的關係
//       customer.getLinkmans().add(linkMan);
		linkMan.setCystomer(customer);
       customerDao.save(customer);
       linkManDao.save(linkMan);

   }
}

2.2 級聯

概述：
1.級聯新增:當我儲存一個客戶的同時儲存聯絡人
2.級聯刪除：當我刪除一個客戶的同時刪除聯絡人

級聯操作：
1.需要區分操作主體
2.需要需要在操作主體的實體類上，新增級聯屬性（需要新增到多表對映關係的註解上）
3.cascade（配置級聯）

2.2.1 級聯新增

在多表中新增cascade = CascadeType.ALL屬性，然後測試程式碼配置好關係儲存客戶即可

  @OneToMany(mappedBy = "customer" ,cascade = CascadeType.ALL)
    private Set<LinkMan> linkmans  = new HashSet<>();

    public void saveTest(){
       Customer customer = new Customer();
       customer.setCustName("向科銘");
       LinkMan linkMan = new LinkMan();
       linkMan.setLkmName("美國隊長");
//       建立客戶和聯絡人的關係
      linkMan.setCustomer(customer);
      customer.getLinkmans().add(linkMan);
      customerDao.save(customer);

2.2.2 級聯刪除

配置好以上關係後，查詢出來刪除

  public void saveTest(){
      Customer one = customerDao.findOne(1l);
      customerDao.delete(one);
   }

3.多對多

3.1 配置環境

兩個表，分別配置好關係

//    中間表的名稱
			@ManyToMany(targetEntity = User.class)
   		 	@JoinTable(name = "sys_userandrole",
//            當前物件在中間表中的外來鍵
//            name 設定中間表對應的欄位名
            joinColumns ={@JoinColumn(name = "sys_roleid",referencedColumnName = "role_id")},
//            對方表在中間表的外來鍵
            inverseJoinColumns = {@JoinColumn(name = "sys_userid",referencedColumnName = "user_id")})
    private Set<User> users = new HashSet<>();

    		@ManyToMany(targetEntity = Role.class)
   	 		@JoinTable(name = "sys_userandrole",
//            當前物件在中間表中的外來鍵
//            name 設定中間表對應的欄位名
            joinColumns ={@JoinColumn(name = "sys_userid",referencedColumnName = "user_id")},
//            對方表在中間表的外來鍵
            inverseJoinColumns = {@JoinColumn(name = "sys_roleid",referencedColumnName = "role_id")})
    private Set<Role> roles = new HashSet<>();

3.2 測試儲存程式碼

 @Test
   @Transactional
   @Rollback(false)
    public void saveTest(){
      User user = new User();
      Role role = new Role();
      user.setUserName("向科銘");
      role.setRoleName("老闆");
      //新增關係
      user.getRoles().add(role);
      userDao.save(user);
      roleDao.save(role);
   }

3.3 多對多放棄維護權

原則：誰被動被選擇誰放棄
刪除中間表的配置，再配置自己再對方的對映set集合名稱

//    對方對映關係set集合的名稱
    @ManyToMany(mappedBy = "roles")
    private Set<User> users = new HashSet<>();

3.4 多對多級聯操作

原則：確認主體，再主體的類上加上級聯操作cascade = CascadeType.ALL

    		@ManyToMany(targetEntity = Role.class,cascade = CascadeType.ALL)
   			@JoinTable(name = "sys_userandrole",
//            當前物件在中間表中的外來鍵
//            name 設定中間表對應的欄位名
            joinColumns ={@JoinColumn(name = "sys_userid",referencedColumnName = "user_id")},
//            對方表在中間表的外來鍵
            inverseJoinColumns = {@JoinColumn(name = "sys_roleid",referencedColumnName = "role_id")})
    		private Set<Role> roles = new HashSet<>();

級聯刪除

   public void saveTest(){
       User one = userDao.findOne(1l);
       userDao.delete(one);
   }

4. 物件導航查詢

概述：通過一個物件通過get方法將關聯物件也查詢出來,
(一對多，只需要查詢出1的內容，可以getLinkmeans()得到對應的多內容，預設是延遲載入)
(多對一，只要查出多的內容再get一的方法即可，預設是立即載入)

  @Test
   @Transactional
   @Rollback(false)
    public void saveTest(){
       Customer one = customerDao.getOne(1l);
       Set<LinkMan> linkmans = one.getLinkmans();
       for (LinkMan linkman : linkmans) {
           System.out.println(linkman);
       }
   }

注：物件導航查詢 一查多預設使用的是延遲載入的形式查詢的
呼叫get方法不會立即傳送查詢，而是在使用關聯物件的時候才會查詢。

修改配置：新增以下配置開啟立即載入（不推薦使用）
fetch = FetchType.EAGER
（FetchType.LAZY 延遲載入）

    @OneToMany(mappedBy = "customer" ,cascade = CascadeType.ALL,fetch = FetchType.EAGER)
    private Set<LinkMan> linkmans  = new HashSet<>();

相關文章