美文网首页右耳菌-邓小白的Java架构师的修炼之路
Spring Data Jpa 的基本操作

Spring Data Jpa 的基本操作

作者: 右耳菌 | 来源:发表于2022-04-30 21:25 被阅读0次

一、JPA的关键字拼凑法 - 单表SQL操作

Keywrod Sample JPQL snippet
Distinct findDistinctByLastnameAndFirstname select distinct … where x.lastname = ?1 and x.firstname = ?2
And findByLastnameAndFirstname … where x.lastname = ?1 and x.firstname = ?2
Or findByLastnameOrFirstname … where x.lastname = ?1 or x.firstname = ?2
Is, Equals findByFirstname,findByFirstnameIs,findByFirstnameEquals … where x.firstname = ?1
Between findByStartDateBetween … where x.startDate between ?1 and ?2
LessThan findByAgeLessThan … where x.age < ?1
LessThanEqual findByAgeLessThanEqual … where x.age <= ?1
GreaterThan findByAgeGreaterThan … where x.age > ?1
GreaterThanEqual findByAgeGreaterThanEqual … where x.age >= ?1
After findByStartDateAfter … where x.startDate > ?1
Before findByStartDateBefore … where x.startDate < ?1
IsNull, Null findByAge(Is)Null … where x.age is null
IsNotNull, NotNull findByAge(Is)NotNull … where x.age not null
Like findByFirstnameLike … where x.firstname like ?1
NotLike findByFirstnameNotLike … where x.firstname not like ?1
StartingWith findByFirstnameStartingWith … where x.firstname like ?1 (parameter bound with appended %)
EndingWith findByFirstnameEndingWith … where x.firstname like ?1 (parameter bound with prepended %)
Containing findByFirstnameContaining … where x.firstname like ?1 (parameter bound wrapped in %)
OrderBy findByAgeOrderByLastnameDesc … where x.age = ?1 order by x.lastname desc
Not findByLastnameNot … where x.lastname <> ?1
In findByAgeIn(Collection<Age> ages) … where x.age in ?1
NotIn findByAgeNotIn(Collection<Age> ages) … where x.age not in ?1
True findByActiveTrue() … where x.active = true
False findByActiveFalse() … where x.active = false
IgnoreCase findByFirstnameIgnoreCase … where UPPER(x.firstname) = UPPER(?1)

二、单表SQL操作-关键字拼凑法的例子

1. 创建SpringBoot项目并配置相关的内容
  • pom.xml
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</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>
  • application.properties
# MySql配置
spring.datasource.url=jdbc:mysql://localhost:3306/cloud_study?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.username=####
spring.datasource.password=####
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# 支持SQL输出
spring.jpa.show-sql=true
# 格式化 SQL 输出
spring.jpa.properties.hibernate.format_sql=true
# 开启数据库表结构自动更新，让表根据Entity类的变化而变化，有多个选项 如update, create、create-drop, 生产环境下非必要不要开启
spring.jpa.hibernate.ddl-auto=create #这里表示每次运行程序都删除掉表，然后重新构建
2. 创建实体类Person
package cn.lazyfennec.springdatajpademo2.entity;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.GenericGenerator;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

/**
 * @Author: Neco
 * @Description:
 * @Date: create in 2022/4/30 17:41
 */
@Data
@Entity
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class Person {
    @Id
    // 配置生成策略为UUID
    @GenericGenerator(name = "uuid", strategy = "uuid")
    @GeneratedValue(generator = "uuid")
    private String id;
    @Column
    private String name;
    @Column
    private String sex;
    @Column
    private Integer age;
    @Column
    private Boolean getmarried; // 避免使用拼凑法时语义错误，故不用驼峰而用小写
}
3. 创建PersonRepository
package cn.lazyfennec.springdatajpademo2.repository;

import cn.lazyfennec.springdatajpademo2.entity.Person;
import org.springframework.data.jpa.repository.JpaRepository;

import java.util.List;

/**
 * @Author: Neco
 * @Description:
 * @Date: create in 2022/4/30 17:47
 */
public interface PersonRepository extends JpaRepository<Person, String> {
    // 1. 查询年龄小于等于22岁的人
    List<Person> findAllByAgeLessThanEqual(Integer age);
    // 2. 查询出年龄在20岁到22岁之间并且性别是男的人
    List<Person> findAllByAgeBetweenAndSexEquals(Integer age1, Integer age2, String sex);
    // 3. 查询出已经结婚并且性别是男的人
    List<Person> findAllByGetmarriedIsTrueAndSexEquals(String sex);
}
4. 测试
package cn.lazyfennec.springdatajpademo2;

import cn.lazyfennec.springdatajpademo2.entity.Person;
import cn.lazyfennec.springdatajpademo2.repository.PersonRepository;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

@SpringBootTest
class SpringDataJpaDemo2ApplicationTests {

    @Resource
    private PersonRepository personRepository;

    @Test
    void contextLoads() {
        initPeople();
        // 1. 查询年龄小于等于22岁的人
        System.out.println(personRepository.findAllByAgeLessThanEqual(22));
        // 2. 查询出年龄在20岁到22岁之间并且性别是男的人
        System.out.println(personRepository.findAllByAgeBetweenAndSexEquals(20, 22, "男"));
        // 3. 查询出已经结婚并且性别是男的人
        System.out.println(personRepository.findAllByGetmarriedIsTrueAndSexEquals("男"));
    }

    // 初始化数据
    private void initPeople() {
        List<Person> list = new ArrayList<>();
        Collections.addAll(list,
                Person.builder().name("张三").sex("男").age(18).getmarried(false).build(),
                Person.builder().name("李四").sex("男").age(25).getmarried(true).build(),
                Person.builder().name("王五").sex("女").age(18).getmarried(false).build(),
                Person.builder().name("马六").sex("男").age(22).getmarried(false).build(),
                Person.builder().name("赵七").sex("女").age(23).getmarried(true).build()
        );
        personRepository.saveAll(list);
    }
}

结果如下

Hibernate: 
    select
        person0_.id as id1_0_,
        person0_.age as age2_0_,
        person0_.getmarried as getmarri3_0_,
        person0_.name as name4_0_,
        person0_.sex as sex5_0_ 
    from
        person person0_ 
    where
        person0_.age<=?
[Person(id=4028b8818079f1cb018079f1d45b0000, name=张三, sex=男, age=18, getmarried=false), Person(id=4028b8818079f1cb018079f1d46f0002, name=王五, sex=女, age=18, getmarried=false), Person(id=4028b8818079f1cb018079f1d46f0003, name=马六, sex=男, age=22, getmarried=false)]
Hibernate: 
    select
        person0_.id as id1_0_,
        person0_.age as age2_0_,
        person0_.getmarried as getmarri3_0_,
        person0_.name as name4_0_,
        person0_.sex as sex5_0_ 
    from
        person person0_ 
    where
        (
            person0_.age between ? and ?
        ) 
        and person0_.sex=?
[Person(id=4028b8818079f1cb018079f1d46f0003, name=马六, sex=男, age=22, getmarried=false)]
Hibernate: 
    select
        person0_.id as id1_0_,
        person0_.age as age2_0_,
        person0_.getmarried as getmarri3_0_,
        person0_.name as name4_0_,
        person0_.sex as sex5_0_ 
    from
        person person0_ 
    where
        person0_.getmarried=1 
        and person0_.sex=?
[Person(id=4028b8818079f1cb018079f1d46f0001, name=李四, sex=男, age=25, getmarried=true)]

三、关键字拼凑无法解决的问题

  • 实体类的属性名与表的字段无法映射，导致关键字找不到
  • CRUD操作方式比较另类或者是不想用关键字的方法
  • 涉及到了多表操作

四、使用注解来手写SQL语句

两种方式

  • 使用SQL 语句来书写SQL
  • 使用HQL语句来书写SQL

注意事项

  • 如果是删改操作，需要加@Modifying和@Transactional注解
  • 如果是SQL语句，请在@Query注解上加上NativeQuery=true的属性
  • 传参的方式有两种:
    • 使用?+数字的方式，数字从1开始，代表第几个参数
    • 使用:+参数名的方式，这种方式最好配合@Param注解一起使用

例子

    1. 根据name来模糊删除一个person数据
    // 4. 根据name来模糊删除一个person数据
    @Modifying
    @Transactional
    @Query(value = "delete from Person where name like %:name%")
    void deleteByPersonName(@Param("name") String name);
    1. 使用HQL或sql来书写一个查询语句，查询年龄在20-22岁，性别为女的人
    // 5. 使用HQL或sql来书写一个查询语句，查询年龄在20-22岁，性别为女的人
    //@Query(value = "select * from person where age between 20 and 22 and sex='女'", nativeQuery = true) // 表示使用SQL语句
    @Query(value = "select p from Person p where p.age between 20 and 22 and p.sex='女'") // 使用HQL语句
    List<Person> findPeople1();
    1. 使用SPEL表达式来完成person表的修改操作
    // 6. 使用SPEL表达式来完成person表的修改操作
    @Modifying
    @Transactional
    //@Query(value = "update person set name=:#{#person.name},sex=:#{#person.sex}, age=:#{#person.age} " +
    //        " where id=:#{#person.id}", nativeQuery = true) // SQL
    @Query(value = "update Person p set p.name=:#{#person.name},p.sex=:#{#person.sex}, p.age=:#{#person.age} " + 
                " where p.id=:#{#person.id}") // HQL
    void updatePerson(@Param("person") Person person);

五、Spring Data JPA 多表查询

image.png
1. 例子
  1. 导入数据库表
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
  `BID` int(4) NOT NULL AUTO_INCREMENT,
  `BNAME` varchar(255) DEFAULT NULL,
  `BPRICE` double(4,2) DEFAULT NULL,
  `PID` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`BID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

#
# Data for table "book"
#

INSERT INTO `book` VALUES
(1,'三国演义',19.90,'402882f870d847990170d847a73c0000'),
(2,'红楼梦',29.90,'402882f870d847990170d847a73c0000'),
(3,'西游记',39.90,'402882f870d847990170d847a7590003'),
(4,'水浒传',28.80,'402882f870d847990170d847a73c0000'),
(5,'变形金刚',30.00,'402882f870d847990170d847a7590003'),
(6,'樱桃小丸子',48.80,'402882f870d847990170d847a7590003'),
(7,'大宅门',18.00,'402882f870d847990170d847a7590003'),
(8,'世界史',58.80,'402882f870d847990170d847a73c0000'),
(9,'人类史',60.00,'402882f870d847990170d847a73c0000'),
(10,'生命简史',55.80,'402882f870d847990170d847a73c0000');
  1. 逆向生成Book类
package cn.lazyfennec.springdatajpademo2.entity;

import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import java.util.Objects;

/**
 * @Author: Neco
 * @Description:
 * @Date: create in 2022/4/30 20:54
 */
@Entity
public class Book {
    private int bid;
    private String bname;
    private Double bprice;
    private String pid;

    @Id
    @Column(name = "BID")
    public int getBid() {
        return bid;
    }

    public void setBid(int bid) {
        this.bid = bid;
    }

    @Basic
    @Column(name = "BNAME")
    public String getBname() {
        return bname;
    }

    public void setBname(String bname) {
        this.bname = bname;
    }

    @Basic
    @Column(name = "BPRICE")
    public Double getBprice() {
        return bprice;
    }

    public void setBprice(Double bprice) {
        this.bprice = bprice;
    }

    @Basic
    @Column(name = "PID")
    public String getPid() {
        return pid;
    }

    public void setPid(String pid) {
        this.pid = pid;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Book book = (Book) o;
        return bid == book.bid &&
                Objects.equals(bname, book.bname) &&
                Objects.equals(bprice, book.bprice) &&
                Objects.equals(pid, book.pid);
    }

    @Override
    public int hashCode() {
        return Objects.hash(bid, bname, bprice, pid);
    }
}
  1. 联表查询
  • 联表查询-根据书名来查该书籍的拥有者
    //7、联表查询-根据书名来查该书籍的拥有者
    @Query(value = "select p from Person p inner join Book b on p.id=b.pid where b.bname=:bname")
    Person findPersonByBname(@Param("bname") String bname);
  • 使用数据接口接收查询数据
  • 创建数据接口 PersonInfo
package cn.lazyfennec.springdatajpademo2.entity;

public interface PersonInfo {
   String getPid();
   String getPname();
   String getPsex();
   String getPage();
   String getGetmerried();
   Integer getBid();
   String getBname();
   double getBprice();
}
  • 联表查询-根据用户id来查询person和book，并用PersonInfo接口来接收，这里必须使用别名
    //8、联表查询-根据用户id来查询person和book
   @Query(value = "select p.id as id,p.name as name,p.sex as sex,p.getmarried as getmarried," +
           "b.bid as bid,b.bname as bname,b.bprice as bprice from Person p inner join Book b on p.id=b.pid " +
           "where p.id=:id")
   List<PersonInfo> findAllInfo(@Param("id") String id);
  • 直接使用List/Map等集合嵌套的方式来获取到接收数据
  • List
   @Query(value = "select p.pid as pid,p.pname as pname,p.psex as psex,p.getmarried as getmarried," +
           "b.bid as bid,b.bname as bname,b.bprice as bprice from Person p inner join Book b on p.pid=b.pid " +
           "where p.pid=:pid")
   List<Object> findAllInfo1(@Param("pid") String pid);
  • Map
   @Query(value = "select p.id as id,p.name as name,p.sex as sex,p.getmarried as getmarried," +
           "b.bid as bid,b.bname as bname,b.bprice as bprice from Person p inner join Book b on p.id=b.pid " +
           "where p.id=:id")
   List<Map<String,Object>> findAllInfo2(@Param("id") String id);

六、多表复杂查询的又一种方式-QueryDSL

  • 什么是QueryDSL
    QueryDSL仅仅是一个通用的查询框架，专注于通过JavaAPI构建类型安全的Sql查询，也可以说QueryDSL是基于各种ORM框架以及Sql之上的一个通用的查询框架，QueryDSL的查询，类是于SQL查询，很全面只不过一个是用SQL—个是用代码来代替SQL。
  • 建议
    单表乃至简单的多表操作，都不推荐使用QueryDSL，使用JPA自带API简洁又效率，但是涉及太复杂的查询，推荐使用QueryDSL

  • 例子


    例子
  • 官网介绍
    http://querydsl.com/static/querydsl/latest/reference/html/

更多知识，请点击关注查看我的主页信息哦~

相关文章

网友评论

    本文标题：Spring Data Jpa 的基本操作

    本文链接：https://www.haomeiwen.com/subject/etuyyrtx.html

    延伸阅读

    深度阅读

    • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

    栏目导航

    热点阅读

    右耳菌-邓小白的Java架构师的修炼之路

    关于我们|服务条款|联系我们|Spring Data Jpa 的基本操作|投稿指南|网站地图|RSS订阅|排版工具|手机版

    提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

    Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

    备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

    本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

    所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！