JPA使用.pdf
Spring 数据扩展

@Query 取值方式

https://spring.io/blog/2014/07/15/spel-support-in-spring-data-jpa-query-definitions

  1. @Query("select u from User u where u.age = ?#{[0]}")
  2. List<User> findUsersByAge(int age);
  4. # 实体取值
  5. @Query("select u from User u where u.firstname = :#{#customer.firstname}")
  6. List<User> findUsersByCustomersFirstname(@Param("customer") Customer customer);

@Query 使用方式

https://docs.spring.io/spring-data/jpa/docs/2.5.6/reference/html/#jpa.query-methods.at-query

  1. public interface UserRepository extends JpaRepository<User, Long> {
  3.       @Query("select u from User u where u.emailAddress = ?1")
  4.       User findByEmailAddress(String emailAddress);
  6.     @Query("select u from User u where u.emailAddress = :emailAddress")
  7.     User findByEmailAddress2(@Param("nickname") String emailAddress);
  9.     # 实体取值
  10.     @Query("select u from User u where u.firstname = :#{#customer.firstname}")
  11.     List<User> findUsersByCustomersFirstname(@Param("customer") Customer customer);
  14. }

Repository 的使用

https://docs.spring.io/spring-data/jpa/docs/2.5.6/reference/html/#jpa.query-methods.query-creation

Keyword 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)

返回自定义实体

推荐第一种：定义返回实体接口

定义返回实体接口

  1.     // 只需要有get方法即可,注意命名要规范
  2.     public interface UserOrRole2 {  
  3.         String getId();
  4.         String getUserId();
  5.         String getNickname();
  6.         String getSex();
  7.         String getRoleName();
  8.     }
  10.     /**
  11.     * jpql 返回自定义 实体
  12.     * @param nickname
  13.     * @return
  14.     */
  15.     @Query(value = " SELECT u.userId,u.id,u.nickname,u.sex,u.roleName FROM UserBean u where u.nickname = :nickname ")
  16.     public List<UserOrRole2> test(@Param("nickname") String nickname);

定义返回实体

  1.     public class UserOrRole2 {  
  2.         String id;
  3.         String userId;
  4.         String nickname;
  5.         String sex;
  6.         String roleName;
  7.         // 省略get set
  8.     }    
  9.    /**
  10.     * jpql 返回自定义 实体
  11.     * @param nickname
  12.     * @return
  13.     */
  14.     @Query(value = " SELECT new com.tn.jpahi.bean.UserOrRole2(r.userId,r.id,u.nickname,u.sex,r.roleName) FROM UserBean u  where u.nickname =:nickname ")
  15.     public List<UserOrRole2> test(@Param("nickname") String nickname);

常用注解

设置索引 @Table @Index @UniqueConstraint

unique = true 唯一索引

  1. // 复合注解
  2. @Table(name = "数据表名称", schema = "数据库名称",
  3.         indexes = {
  4.                 @Index(name = "索引名称", columnList = "字段1", unique = true),
  5.                 @Index(name = "索引名称", columnList = "字段1,字段2", unique = true),
  6.         })
  7. @Table(name="数据表名称",indexes={@Index(name="索引名称",columnList="字段1",unique=true})
  8. // 单个注解                                             
  9. @Index(name="索引名称",columnList="字段1",unique=false)})
  10. // 单个注解 - 唯一索引
  11. @UniqueConstraint(name = "索引名称", columnNames = {"字段1","字段2"})})

标识实体类中属性与数据表中字段的对应关系 @Column

  1. columnDefinition我常用

    1.  @Column(columnDefinition = " int(4)  not null   default 1 comment ' 停启用状态(0、停用 ，1、启用，默认1）' ")
    2.  private Integer status;

  2. 根据属性设置 

    1. @Target({ElementType.METHOD, ElementType.FIELD})
    2. @Retention(RetentionPolicy.RUNTIME)
    3. public @interface Column {
    4. String name() default "";  // 定义了该属性字段在数据库表中所对应字段的名称
    6. boolean unique() default false;  //表示该字段是否为唯一标识，默认为false
    8. boolean nullable() default true;  //表示该字段是否可以为null值，默认为true
    10. boolean insertable() default true; //表示在使用“INSERT”脚本插入数据时，是否允许插入该字段的值
    12. boolean updatable() default true;  //表示在使用“UPDATE”脚本插入数据时，是否允许更新该字段的值. 
    14. String columnDefinition() default ""; //表示创建表时，该字段创建的SQL语句，一般用于通过Entity生成表定义时使用。
    16. String table() default "";  //定义了包含当前字段的表名，缺省值时默认该字段存在于主表下
    18. int length() default 255; // 表示字段的长度，当字段的类型为varchar时，该属性才有效，默认为255个字符
    20. int precision() default 0; //表示数值的总长度
    22. int scale() default 0;  //表示小数点所占的位数
    23. }

实体中嵌入对象

https://www.codeleading.com/article/81262695760/ @Embeddable
@Access @embedded

默认使用方式

公共字段，需要被嵌入到其他地方使用

@Embeddable @Access 

  1. package com.detabes.gzyanalysis.entity.door;
  3. import lombok.Getter;
  4. import lombok.Setter;
  5. import org.hibernate.annotations.DynamicInsert;
  6. import org.hibernate.annotations.DynamicUpdate;
  7. import org.hibernate.annotations.SelectBeforeUpdate;
  9. import javax.persistence.Access;
  10. import javax.persistence.AccessType;
  11. import javax.persistence.Column;
  12. import javax.persistence.Embeddable;
  14. /**
  15.  * 门禁实体公共字段
  16.  *
  17.  * @author tn
  18.  * @date 2022-03-03 11:02
  19.  */
  20. @Embeddable
  21. @DynamicInsert
  22. @DynamicUpdate
  23. @SelectBeforeUpdate
  24. @Access(AccessType.FIELD)
  25. @Getter
  26. @Setter
  27. public class DoorEntity {
  28.     /**
  29.      * 用户编码;用户编码
  30.      **/
  31.     @Column(columnDefinition = "varchar(60) comment '用户编码'")
  32.     private String userNo;
  34.     /**
  35.      * 闸机号;闸机号
  36.      **/
  37.     @Column(columnDefinition = "varchar(60) not null comment '闸机号'")
  38.     private String gateNo;
  40. }

实体类使用嵌入字段

@Embedded 

  1. package com.detabes.gzyanalysis.entity.door;
  3. import com.detabes.gzyanalysis.entity.CommonBean;
  4. import lombok.Data;
  5. import lombok.EqualsAndHashCode;
  6. import org.hibernate.annotations.DynamicInsert;
  7. import org.hibernate.annotations.DynamicUpdate;
  9. import javax.persistence.Column;
  10. import javax.persistence.Embedded;
  11. import javax.persistence.Entity;
  12. import javax.persistence.Table;
  14. /**
  15.  * 入馆数据实体类
  16.  *
  17.  * @author tn
  18.  * @date 2022-03-03
  19.  **/
  20. @Entity
  21. @Table(name = "door_input")
  22. @org.hibernate.annotations.Table(appliesTo = "door_input", comment = "入馆数据")
  23. @Data
  24. @EqualsAndHashCode(callSuper = true)
  25. @DynamicUpdate
  26. @DynamicInsert
  27. public class DoorInputEntity extends CommonBean<DoorInputEntity> {
  30.     /**
  31.      * 入馆时间;入馆时间
  32.      **/
  33.     @Column(columnDefinition = "varchar(60) comment '入馆时间'")
  34.     private String inTime;
  36.     /**
  37.      * 入馆方式;0.不明 1. 扫码 2. 刷卡 3. 远程开门（保安开的 4. 等等
  38.      **/
  39.     @Column(columnDefinition = "int not null default 0 comment '入馆方式;0.不明 1. 扫码 2. 刷卡 3. 远程开门（保安开的 '")
  40.     private Integer inType;
  42.     /**
  43.      * 嵌入公共字段对象
  44.      */
  45.     @Embedded
  46.     private DoorEntity doorEntity;
  47. }

使用二 修改公共字段的column名称后在使用

公共字段

  1. package com.detabes.gzyanalysis.entity.door;
  3. import lombok.Getter;
  4. import lombok.Setter;
  5. import org.hibernate.annotations.DynamicInsert;
  6. import org.hibernate.annotations.DynamicUpdate;
  7. import org.hibernate.annotations.SelectBeforeUpdate;
  9. import javax.persistence.Access;
  10. import javax.persistence.AccessType;
  11. import javax.persistence.Column;
  12. import javax.persistence.Embeddable;
  14. /**
  15.  * 门禁实体公共字段
  16.  *
  17.  * @author tn
  18.  * @date 2022-03-03 11:02
  19.  */
  20. @Embeddable
  21. @DynamicInsert
  22. @DynamicUpdate
  23. @SelectBeforeUpdate
  24. @Access(AccessType.FIELD)
  25. @Getter
  26. @Setter
  27. public class DoorEntity {
  30.     /**
  31.      * 出入馆时间
  32.      **/
  33.     @Column(columnDefinition = "varchar(60) comment '出入馆时间'")
  34.     private String times;
  36.     /**
  37.      * 出入馆方式;0.不明 1. 扫码 2. 刷卡 3. 远程开门（保安开的 4. 等等
  38.      **/
  39.     @Column(columnDefinition = "int not null default 0 comment '出入馆方式;0.不明 1. 扫码 2. 刷卡 3. 远程开门（保安开的 '")
  40.     private Integer type;
  42.     /**
  43.      * 用户编码;用户编码
  44.      **/
  45.     @Column(columnDefinition = "varchar(60) comment '用户编码'")
  46.     private String userNo;
  48.     /**
  49.      * 闸机号;闸机号
  50.      **/
  51.     @Column(columnDefinition = "varchar(60) not null comment '闸机号'")
  52.     private String gateNo;
  54. }

实体类使用嵌入类时修改表字段名

这种方式如果要使用 索引方法如下：

  1. // 嵌入类时修改表字段时， 不能写 DoorEntity 里面的原本实体字段名，会报错
  2. @Table(name = "door_input",
  3.         indexes = {
  4.                 @Index(name = "in_type_index", columnList = "in_type", unique = true),
  5.         })
  1. import com.detabes.gzyanalysis.entity.CommonBean;
  2. import lombok.Data;
  3. import lombok.EqualsAndHashCode;
  4. import org.hibernate.annotations.DynamicInsert;
  5. import org.hibernate.annotations.DynamicUpdate;
  7. import javax.persistence.*;
  9. /**
  10.  * 入馆数据实体类
  11.  *
  12.  * @author tn
  13.  * @date 2022-03-03
  14.  **/
  15. @Entity
  16. @Table(name = "door_input")
  17. @org.hibernate.annotations.Table(appliesTo = "door_input", comment = "入馆数据")
  18. @Data
  19. @EqualsAndHashCode(callSuper = true)
  20. @DynamicUpdate
  21. @DynamicInsert
  22. public class DoorInputEntity extends CommonBean<DoorInputEntity> {
  24.     /**
  25.      * 嵌入公共字段对象
  26.      */
  27.     @Embedded
  28.     @AttributeOverrides({
  29.             @AttributeOverride(name="type",column=@Column(name="in_type",columnDefinition = "int not null default 0 comment '入馆方式;0.不明 1. 扫码 2. 刷卡 3. 远程开门（保安开的 '")),
  30.             @AttributeOverride(name="times",column=@Column(name="in_time",columnDefinition = "varchar(60) comment '入馆时间'"))
  31.     })
  32.     private DoorEntity doorEntity;
  33. }

效果

  1. CREATE TABLE `door_input` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键，自动生成',
  3.   `update_user_name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '更新人',
  4.   `create_user_name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
  5.   `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日期',
  6.   `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期',
  7.   `in_time` varchar(60) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '入馆时间',
  8.   `in_type` int(11) NOT NULL DEFAULT '0' COMMENT '入馆方式;0.不明 1. 扫码 2. 刷卡 3. 远程开门（保安开的 ',
  9.   `user_no` varchar(60) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户编码',
  10.   `gate_no` varchar(60) COLLATE utf8mb4_general_ci NOT NULL COMMENT '闸机号',
  11.   PRIMARY KEY (`id`)
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='入馆数据';

枚举参数

https://blog.csdn.net/weixin_42994251/article/details/110147426

枚举类

  1. package com.detabes.gzyanalysis.enums;
  3. import lombok.AllArgsConstructor;
  4. import lombok.Getter;
  6. import java.util.Objects;
  8. /**
  9.  * 存量类型
  10.  *
  11.  * @author tn
  12.  * @date 2022-01-19 16:31
  13.  */
  14. @AllArgsConstructor
  15. @Getter
  16. public enum  StockTypeEnum {
  18.     /* 总藏 = 在馆的所有书籍（包括外借的） */
  19.     TOTAL(1,"总藏（在馆的所有书籍（包括外借的））"),
  21.     /* 入藏 = 买的所有书籍*/
  22.     INTO(2,"入藏（买的所有书籍）"),
  24.     /* 在馆 = 在馆的所有书籍（不包括外借的） */
  25.     AT_HOME(3,"在馆（在馆的所有书籍（不包括外借的））"),
  27.     /* 总资源 =书+刊+古籍+多媒体+电子资源（这次不包括） */
  28.     TOTAL_RESOURCE(4,"总资源（书+刊+古籍+多媒体+电子资源）"),
  31.     ;
  33.     private final int stockType;
  34.     private final String stockName;
  37.     //获取枚举实例
  38.     public static StockTypeEnum fromValue(Integer value) {
  39.         for (StockTypeEnum stockTypeEnum : StockTypeEnum.values()) {
  40.             if (Objects.equals(value, stockTypeEnum.getStockType())) {
  41.                 return stockTypeEnum;
  42.             }
  43.         }
  44.         throw new IllegalArgumentException();
  45.     }
  46. }

自定义Convert

  1. package com.detabes.gzyanalysis.enums.convert;
  3. import com.detabes.gzyanalysis.enums.StockTypeEnum;
  5. import javax.persistence.AttributeConverter;
  7. /**
  8.  * @author tn
  9.  */
  10. public class StockEnumConvert implements AttributeConverter<StockTypeEnum, Integer> {
  11.     @Override
  12.     public Integer convertToDatabaseColumn(StockTypeEnum attribute) {
  13.         return attribute.getStockType();
  14.     }
  16.     @Override
  17.     public StockTypeEnum convertToEntityAttribute(Integer dbData) {
  18.         return StockTypeEnum.fromValue(dbData);
  19.     }
  20. }

实体类中使用

  1. import com.detabes.entity.basics.vo.SerializableVO;
  2. import com.detabes.gzyanalysis.enums.StockTypeEnum;
  3. import com.detabes.gzyanalysis.enums.convert.StockEnumConvert;
  4. import lombok.Data;
  5. import lombok.EqualsAndHashCode;
  6. import org.hibernate.annotations.DynamicInsert;
  7. import org.hibernate.annotations.DynamicUpdate;
  9. import javax.persistence.*;
  11. /**
  12.  * 藏书存量（全馆全时间段统计）实体类
  13.  *
  14.  * @author tn
  15.  * @date 2022-01-19
  16.  **/
  17. @Entity
  18. @Table(name = "library_stock", indexes = {
  19.                 @Index(name = "stockType_index", columnList = "stockType", unique = true)
  20.         })
  21. @org.hibernate.annotations.Table(appliesTo = "library_stock", comment = "藏书存量（全馆全时间段统计）")
  22. @Data
  23. @EqualsAndHashCode(callSuper = true)
  24. @DynamicUpdate
  25. @DynamicInsert
  26. public class LibraryStockEntity extends SerializableVO<LibraryStockEntity> {
  28.     /**
  29.      * 主键;自增长
  30.      **/
  31.     @Id
  32.     @GeneratedValue(strategy = GenerationType.IDENTITY)
  33.     @Column(columnDefinition = "int comment '主键;自增长'")
  34.     private Integer id;
  36.     /**
  37.      * 存量类型
  38.      * 存储的是 int 
  39.      **/
  40.     @Column(columnDefinition = "int comment '存量类型（具体信息：StockTypeEnum）'")
  41.     @Convert(converter = StockEnumConvert.class)
  42.     private StockTypeEnum stockType;
  44.     /**
  45.      * 统计时间
  46.      **/
  47.     @Column(columnDefinition = "varchar(100) not null comment '统计时间'")
  48.     private String countTime;
  50.     /**
  51.      * 存量
  52.      **/
  53.     @Column(columnDefinition = "bigint default 0 comment '存量'")
  54.     private Long stock;
  56. }

实体构建索引

@UniqueConstraint @Index 

  1. // unique true: 唯一索引 false:单值索引
  2. @Table(name="sys_user_msg_collect",
  3.        indexes={
  4.            @Index(name="uuid_index",columnList="uuid",unique=true),
  5.            @Index(name="loginName_index",columnList="loginName",unique=false)
  6.        }
  7.       )