关于JpaRepository的关联查询和@Query查询

关于JpaRepository的关联查询和@Query查询

目录一 pom二 启动类三 配置文件四 新建数据库五 实体类1 Clazz2 Student六 控制器七 Repository层1 ClazzRepository2 StudentRepository八 Service层九 测试

一 pom

xmlns:xsi="http://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">

4.0.0

org.fkit

springdatajpaquerytest

0.0.1-SNAPSHOT

jar

springdatajpaquerytest

http://maven.apache.org

org.springframework.boot

spring-boot-starter-parent

2.0.0.RELEASE

UTF-8

UTF-8

1.8

org.springframework.boot

spring-boot-starter-web

org.springframework.boot

spring-boot-starter-thymeleaf

mysql

mysql-connector-java

org.springframework.boot

spring-boot-starter-data-jpa

junit

junit

test

xmlns:xsi="http://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">

4.0.0

org.fkit

springdatajpaquerytest

0.0.1-SNAPSHOT

jar

springdatajpaquerytest

http://maven.apache.org

org.springframework.boot

spring-boot-starter-parent

2.0.0.RELEASE

UTF-8

UTF-8

1.8

org.springframework.boot

spring-boot-starter-web

org.springframework.boot

spring-boot-starter-thymeleaf

mysql

mysql-connector-java

org.springframework.boot

spring-boot-starter-data-jpa

junit

junit

test

二 启动类

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 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 getStudents() {

return students;

}

public void setStudents(Set 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 clazzs = new ArrayList<>();

clazzs.add(clazz1);

BMnEYRYclazzs.add(clazz2);

schoolService.saveClazzAll(clazzs);

Student swk = new Student("孙悟空","广州",700,'男',clazz1);

Student zzj = new Student("蜘蛛精","广州",700,'女',clazz1);

Student nmw = new Student("牛魔王","广州",500,'男',clazz2);

List students = new ArrayList<>();

students.add(swk);

students.add(zzj);

students.add(nmw);

schoolService.saveStudentAll(students);

return "保存学生对象成功";

}

/**

* 查询某个班级下所有的学生姓名，年龄，性别

* @param clazzName

* @return

*/

@RequestMapping("/getClazzStus")

public List

return schoolService.getStusByClazzName(clazzName);

}

/**

* 查询某个班级下所有的学生姓名，性别

* @param clazzName

* @return

*/

@RequestMapping("/findNameAndSexByClazzName")

public List

return schoolService.findNameAndSexByClazzName(clazzName);

}

/**

* ，查询某个班级下某种性别的所有学生的姓名

* @param clazzName

* @return

*/

@RequestMapping("/findNameByClazzNameAndSex")

public List 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 {

}

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 {

/**

* 根据班级名称查询这个班级下所有的学生信息

* 相当于JPQL语句： select s from Student s where s.clazz.name = ?1

* @param clazzName

* @return

*/

List findByClazz_name(String clazzName);

/**

* @Query写法

* 根据班级名称查询这个班级下所有的学生信息

* ?1此处使用的是参数的位置，代表的是第一个参数

* 此写法与 findByClazz_name方法实现的功能完全一致

* */

@Query("select s from Student s where s.clazz.name = ?1")

List 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

/**

* 使用@Query注解的形式，查询某个班级下某种性别的所有学生的姓名

* 上面方法是用的是参数的位置来查询的，Spring Data JPA中还支持用

* 名称来匹配查询使用格式 “:参数名称” 引用

* @param clazzName

* @return

*/

@Query("select s.name from Student s "

+ "where s.clazz.name = :clazzName and s.sex = :sex ")

List 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 clazzs) {

clazzRepository.saveAll(clazzs);

}

@Transactional

public void saveStudentAll(List students) {

studentRepository.saveAll(students);

}

public List

// 使用"_" 和 @Query查询方式结果一致

List students = studentRepository.findByClazz_name(clazzName);

// List students = studentRepository.findStudentsByClazzName(clazzName);

List

// 遍历查询出的学生对象，提取姓名，年龄，性别信息

for(Student student:students){

Map 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

return studentRepository.findNameAndSexByClazzName(clazzName);

}

public List 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);

}

}

九 测试

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。