SpringDataJpa （二）-动态查询&多表操作

一.动态查询1.Specifications动态查询1.1 搭建测试环境1.1.1 导入坐标1.1.2 创建客户实体类1.1.3 springdatajpa 核心配置文件1.1.4 编写dao接口1.1.5 案列测试代码1.1.5.1 查询所有1.1.5.2 多条件查询1.1.5.3 模糊查询1.1.5.4 Sort排序1.1.5.5 pageable分页 2.多表查询（一对多）2.1.配置环境2.1.1 新建一个工程，添加两个表的类并配置映射关系2.1.2 测试保存操作 2.2 级联2.2.1 级联添加2.2.2 级联删除 3.多对多3.1 配置环境3.2 测试保存代码3.3 多对多放弃维护权3.4 多对多级联操作 4. 对象导航查询

一.动态查询

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<>();