JpaRepository关联查询和@Query查询
chengqiuming 人气:2一 pom
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>org.fkit</groupId> <artifactId>springdatajpaquerytest</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>springdatajpaquerytest</name> <url>http://maven.apache.org</url> <!-- spring-boot-starter-parent是Spring Boot的核心启动器, 包含了自动配置、日志和YAML等大量默认的配置,大大简化了我们的开发。 引入之后相关的starter引入就不需要添加version配置, spring boot会自动选择最合适的版本进行添加。 --> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.0.0.RELEASE</version> <relativePath /> </parent> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <java.version>1.8</java.version> </properties> <dependencies> <!-- 添加spring-boot-starter-web模块依赖 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!-- 添加spring-boot-starter-thymeleaf模块依赖 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency> <!-- 添加MySQL依赖 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <!-- 添加Spring Data JPA依赖 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <scope>test</scope> </dependency> </dependencies> </project>
二 启动类
package org.fkit.springdatajpaquerytest; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; @SpringBootApplication public class App { public static void main( String[] args ) { // SpringApplication 用于从main方法启动Spring应用的类。 SpringApplication.run(App.class, args); } }
三 配置文件
######################################################## ### \u6570\u636E\u6E90\u4FE1\u606F\u914D\u7F6E ######################################################## # \u6570\u636E\u5E93\u5730\u5740 spring.datasource.url=jdbc:mysql://localhost:3306/springdatajpaquery # \u7528\u6237\u540D spring.datasource.username=root # \u5BC6\u7801 spring.datasource.password= # \u6570\u636E\u5E93\u9A71\u52A8 spring.datasource.driverClassName=com.mysql.jdbc.Driver # \u6307\u5B9A\u8FDE\u63A5\u6C60\u4E2D\u6700\u5927\u7684\u6D3B\u8DC3\u8FDE\u63A5\u6570. spring.datasource.max-active=20 # \u6307\u5B9A\u8FDE\u63A5\u6C60\u6700\u5927\u7684\u7A7A\u95F2\u8FDE\u63A5\u6570\u91CF. spring.datasource.max-idle=8 # \u6307\u5B9A\u5FC5\u987B\u4FDD\u6301\u8FDE\u63A5\u7684\u6700\u5C0F\u503C spring.datasource.min-idle=8 # \u6307\u5B9A\u542F\u52A8\u8FDE\u63A5\u6C60\u65F6\uFF0C\u521D\u59CB\u5EFA\u7ACB\u7684\u8FDE\u63A5\u6570\u91CF spring.datasource.initial-size=10 ######################################################## ### JPA\u6301\u4E45\u5316\u914D\u7F6E ######################################################## # \u6307\u5B9A\u6570\u636E\u5E93\u7684\u7C7B\u578B spring.jpa.database=MySQL # \u6307\u5B9A\u662F\u5426\u9700\u8981\u5728\u65E5\u5FD7\u4E2D\u663E\u793Asql\u8BED\u53E5 spring.jpa.show-sql=true # \u6307\u5B9A\u81EA\u52A8\u521B\u5EFA|\u66F4\u65B0|\u9A8C\u8BC1\u6570\u636E\u5E93\u8868\u7ED3\u6784\u7B49\u914D\u7F6E\uFF0C\u914D\u7F6E\u6210update # \u8868\u793A\u5982\u679C\u6570\u636E\u5E93\u4E2D\u5B58\u5728\u6301\u4E45\u5316\u7C7B\u5BF9\u5E94\u7684\u8868\u5C31\u4E0D\u521B\u5EFA\uFF0C\u4E0D\u5B58\u5728\u5C31\u521B\u5EFA\u5BF9\u5E94\u7684\u8868 spring.jpa.hibernate.ddl-auto=update # Naming strategy # \u6307\u5B9A\u547D\u540D\u7B56\u7565 spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.ImprovedNamingStrategy # \u6307\u5B9A\u6570\u636E\u5E93\u65B9\u8A00 spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
四 新建数据库
springdatajpaquery
五 实体类
1 Clazz
package org.fkit.springdatajpaquerytest.bean; import java.io.Serializable; import java.util.HashSet; import java.util.Set; import javax.persistence.Entity; import javax.persistence.FetchType; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.OneToMany; import javax.persistence.Table; @Entity @Table(name="tb_clazz") public class Clazz implements Serializable{ private static final long serialVersionUID = 1L; @Id @GeneratedValue(strategy=GenerationType.IDENTITY) private int code ; private String name ; // 班级与学生是一对多的关联 @OneToMany( fetch=FetchType.LAZY, targetEntity=Student.class, mappedBy="clazz" ) private Set<Student> students = new HashSet<>(); public Clazz() { } // 班级对象 public Clazz(String name) { this.name = name; } public int getCode() { return code; } public void setCode(int code) { this.code = code; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Set<Student> getStudents() { return students; } public void setStudents(Set<Student> students) { this.students = students; } }
2 Student
package org.fkit.springdatajpaquerytest.bean; import java.io.Serializable; import javax.persistence.Entity; import javax.persistence.FetchType; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.JoinColumn; import javax.persistence.ManyToOne; import javax.persistence.Table; @Entity @Table(name="tb_student") public class Student implements Serializable{ private static final long serialVersionUID = 1L; @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private int id; private String name ; private String address ; private int age ; private char sex; // 学生与班级是多对一的关系,这里配置的是双向关联 @ManyToOne(fetch=FetchType.LAZY, targetEntity=Clazz.class ) @JoinColumn(name="clazzId",referencedColumnName="code") private Clazz clazz ; public Student() { } public Student(String name, String address, int age, char sex, Clazz clazz) { super(); this.name = name; this.address = address; this.age = age; this.sex = sex; this.clazz = clazz; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public char getSex() { return sex; } public void setSex(char sex) { this.sex = sex; } public Clazz getClazz() { return clazz; } public void setClazz(Clazz clazz) { this.clazz = clazz; } }
六 控制器
package org.fkit.springdatajpaquerytest.controller; import java.util.ArrayList; import java.util.List; import java.util.Map; import javax.annotation.Resource; import org.fkit.springdatajpaquerytest.bean.Clazz; import org.fkit.springdatajpaquerytest.bean.Student; import org.fkit.springdatajpaquerytest.service.SchoolService; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; @RestController @RequestMapping("/student") public class StudentController { // 注入ShcoolService @Resource private SchoolService schoolService; @RequestMapping("/save") public String save() { Clazz clazz1 = new Clazz("疯狂java开发1班"); Clazz clazz2 = new Clazz("疯狂java开发2班"); // 保存班级对象数据 List<Clazz> clazzs = new ArrayList<>(); clazzs.add(clazz1); clazzs.add(clazz2); schoolService.saveClazzAll(clazzs); Student swk = new Student("孙悟空","广州",700,'男',clazz1); Student zzj = new Student("蜘蛛精","广州",700,'女',clazz1); Student nmw = new Student("牛魔王","广州",500,'男',clazz2); List<Student> students = new ArrayList<>(); students.add(swk); students.add(zzj); students.add(nmw); schoolService.saveStudentAll(students); return "保存学生对象成功"; } /** * 查询某个班级下所有的学生姓名,年龄,性别 * @param clazzName * @return */ @RequestMapping("/getClazzStus") public List<Map<String, Object>> getClazzStus(String clazzName){ return schoolService.getStusByClazzName(clazzName); } /** * 查询某个班级下所有的学生姓名,性别 * @param clazzName * @return */ @RequestMapping("/findNameAndSexByClazzName") public List<Map<String, Object>> findNameAndSexByClazzName(String clazzName){ return schoolService.findNameAndSexByClazzName(clazzName); } /** * ,查询某个班级下某种性别的所有学生的姓名 * @param clazzName * @return */ @RequestMapping("/findNameByClazzNameAndSex") public List<String> findNameByClazzNameAndSex(String clazzName ,Character sex){ return schoolService.findNameByClazzNameAndSex(clazzName ,sex); } /** * 查询某个学生属于哪个班级 * @param clazzName * @return */ @RequestMapping("/findClazzNameByStuName") public String findClazzNameByStuName(String stuName){ return schoolService.findClazzNameByStuName(stuName); } /** * 删除某个学生对象 * @param clazzName * @return */ @RequestMapping("/deleteStuByStuName") public String deleteStuByStuName(String stuName){ return "删除数据:"+schoolService.deleteStuByStuName(stuName); } }
七 Repository层
1 ClazzRepository
package org.fkit.springdatajpaquerytest.repository; import org.fkit.springdatajpaquerytest.bean.Clazz; import org.springframework.data.jpa.repository.JpaRepository; public interface ClazzRepository extends JpaRepository<Clazz, Integer> { }
2 StudentRepository
package org.fkit.springdatajpaquerytest.repository; import java.util.List; import java.util.Map; import org.fkit.springdatajpaquerytest.bean.Student; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.Modifying; import org.springframework.data.jpa.repository.Query; import org.springframework.data.repository.query.Param; public interface StudentRepository extends JpaRepository<Student, Integer> { /** * 根据班级名称查询这个班级下所有的学生信息 * 相当于JPQL语句: select s from Student s where s.clazz.name = ?1 * @param clazzName * @return */ List<Student> findByClazz_name(String clazzName); /** * @Query写法 * 根据班级名称查询这个班级下所有的学生信息 * ?1此处使用的是参数的位置,代表的是第一个参数 * 此写法与 findByClazz_name方法实现的功能完全一致 * */ @Query("select s from Student s where s.clazz.name = ?1") List<Student> findStudentsByClazzName(String clazzName); /** * 使用@Query注解的形式,查询某个班级下所有学生的姓名和性别 * @param clazzName * @return */ @Query("select new Map(s.name as name , s.sex as sex) " + "from Student s where s.clazz.name = ?1") List<Map<String, Object>> findNameAndSexByClazzName(String clazzName); /** * 使用@Query注解的形式,查询某个班级下某种性别的所有学生的姓名 * 上面方法是用的是参数的位置来查询的,Spring Data JPA中还支持用 * 名称来匹配查询使用格式 “:参数名称” 引用 * @param clazzName * @return */ @Query("select s.name from Student s " + "where s.clazz.name = :clazzName and s.sex = :sex ") List<String> findNameByClazzNameAndSex(@Param("clazzName")String clazzName , @Param("sex")char sex); /** * 使用@Query注解的形式,查询某个学生属于哪个班级 * @param stuName * @return */ @Query("select c.name from Clazz c inner join c.students s " + "where s.name = ?1 ") String findClazzNameByStuName(String stuName); /** * 执行更新查询,使用@Query与@Modifying可以执行更新操作 * 例如删除牛魔王这个学生 * */ @Modifying @Query("delete from Student s where s.name = ?1") int deleteStuByStuName(String stuName); }
八 Service层
package org.fkit.springdatajpaquerytest.service; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.annotation.Resource; import org.fkit.springdatajpaquerytest.bean.Clazz; import org.fkit.springdatajpaquerytest.bean.Student; import org.fkit.springdatajpaquerytest.repository.ClazzRepository; import org.fkit.springdatajpaquerytest.repository.StudentRepository; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; @Service public class SchoolService { // 注入数据访问层接口对象 @Resource private StudentRepository studentRepository; @Resource private ClazzRepository clazzRepository; @Transactional public void saveClazzAll(List<Clazz> clazzs) { clazzRepository.saveAll(clazzs); } @Transactional public void saveStudentAll(List<Student> students) { studentRepository.saveAll(students); } public List<Map<String, Object>> getStusByClazzName(String clazzName) { // 使用"_" 和 @Query查询方式结果一致 List<Student> students = studentRepository.findByClazz_name(clazzName); // List<Student> students = studentRepository.findStudentsByClazzName(clazzName); List<Map<String, Object>> results = new ArrayList<>(); // 遍历查询出的学生对象,提取姓名,年龄,性别信息 for(Student student:students){ Map<String , Object> stu = new HashMap<>(); stu.put("name", student.getName()); stu.put("age", student.getAge()); stu.put("sex", student.getSex()); results.add(stu); } return results; } public List<Map<String, Object>> findNameAndSexByClazzName(String clazzName) { return studentRepository.findNameAndSexByClazzName(clazzName); } public List<String> findNameByClazzNameAndSex( String clazzName, char sex) { return studentRepository.findNameByClazzNameAndSex(clazzName, sex); } public String findClazzNameByStuName(String stuName) { return studentRepository.findClazzNameByStuName(stuName); } @Transactional public int deleteStuByStuName(String stuName) { return studentRepository.deleteStuByStuName(stuName); } }
九 测试
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。
加载全部内容