📦JPA常用方法

> 一些常用的方法实例

Repository 的使用

https://docs.spring.io/spring-data/jpa/docs/2.6.9/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)

@Query 取值方式

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

注意： 使用 like 是 jpa不会给你加上百分号需要自己加哦

public interface UserRepository extends JpaRepository<User, Long> {

    @Query("select u from User u where u.emailAddress = ?1")
    User findByEmailAddress(String emailAddress);

    @Query("select u from User u where u.emailAddress = :emailAddress")
    User findByEmailAddress2(@Param("nickname") String emailAddress);
    
    # 实体取值
    @Query("select u from User u where u.firstname = :#{#customer.firstname}")
    List<User> findUsersByCustomersFirstname(@Param("customer") Customer customer); 
}

创建索引

@Table中的 indexes 属性 ，或者 @UniqueConstraint

// unique true: 唯一索引 false:单值索引
@Table(name="sys_user_msg_collect",
       indexes={
           @Index(name="uuid_index",columnList="uuid",unique=true),
           @Index(name="loginName_index",columnList="loginName",unique=false)
       }
      )

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

实体类与数据表字段

字段

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

上面的写法会在人大金仓数据库中出错

  // 字段类型
  @Column(columnDefinition = " varchar(200)  not null  " )
  // 字段注释
  @Comment("名称，同级唯一")
  private  String  name 

表

// 表注释
@org.hibernate.annotations.Table(appliesTo = "auth_route", comment = "路由（功能菜单)")
# 更新和新增时，仅在预处理sql语句中引用已更改（新增）的列
@DynamicUpdate
@DynamicInsert

存储JSON

需要依赖

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>${hibernate-types.version}</version>
</dependency>

@Entity
@TypeDef(
        name = "json",
        typeClass = JsonStringType.class
)
public class UserEntity {
    /**
     * 用户详情json(存储不明确的字段)
     */
    @Column(columnDefinition = " json comment ' 用户详情json(存储不明确的字段)' " )
    @Type(type = "json")
    private Map<String,Object> userInfo;
}

使用枚举

枚举

import lombok.AllArgsConstructor;
import lombok.Getter;

import java.util.Objects;

/**
 * 存量类型
 *
 * @author tn
 * @date 2022-01-19 16:31
 */
@AllArgsConstructor
@Getter
public enum  StockTypeEnum {

    /* 总藏 = 在馆的所有书籍（包括外借的） */
    TOTAL(1,"总藏（在馆的所有书籍（包括外借的））"),

    /* 入藏 = 买的所有书籍*/
    INTO(2,"入藏（买的所有书籍）"),

    /* 在馆 = 在馆的所有书籍（不包括外借的） */
    AT_HOME(3,"在馆（在馆的所有书籍（不包括外借的））"),

    /* 总资源 =书+刊+古籍+多媒体+电子资源（这次不包括） */
    TOTAL_RESOURCE(4,"总资源（书+刊+古籍+多媒体+电子资源）"),


    ;

    private final int stockType;
    private final String stockName;


    //获取枚举实例
    public static StockTypeEnum fromValue(Integer value) {
        for (StockTypeEnum stockTypeEnum : StockTypeEnum.values()) {
            if (Objects.equals(value, stockTypeEnum.getStockType())) {
                return stockTypeEnum;
            }
        }
        throw new IllegalArgumentException();
    }
}

撰写转换类

import javax.persistence.AttributeConverter;

/**
 * @author tn
 */
public class StockEnumConvert implements AttributeConverter<StockTypeEnum, Integer> {
    @Override
    public Integer convertToDatabaseColumn(StockTypeEnum attribute) {
        return attribute.getStockType();
    }

    @Override
    public StockTypeEnum convertToEntityAttribute(Integer dbData) {
        return StockTypeEnum.fromValue(dbData);
    }
}

实体中使用

import lombok.Data;
import lombok.EqualsAndHashCode;
import org.hibernate.annotations.DynamicInsert;
import org.hibernate.annotations.DynamicUpdate;

import javax.persistence.*;

/**
 * 藏书存量（全馆全时间段统计）实体类
 *
 * @author tn
 * @date 2022-01-19
 **/
@Entity
@Table(name = "library_stock", indexes = {
                @Index(name = "stockType_index", columnList = "stockType", unique = true)
        })
@org.hibernate.annotations.Table(appliesTo = "library_stock", comment = "藏书存量（全馆全时间段统计）")
@Data
@EqualsAndHashCode(callSuper = true)
@DynamicUpdate
@DynamicInsert
public class LibraryStockEntity extends SerializableVO<LibraryStockEntity> {

    /**
     * 主键;自增长
     **/
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(columnDefinition = "int comment '主键;自增长'")
    private Integer id;

    /**
     * 存量类型
     * 存储的是 int 
     **/
    @Column(columnDefinition = "int comment '存量类型（具体信息：StockTypeEnum）'")
    @Convert(converter = StockEnumConvert.class)
    private StockTypeEnum stockType;

    /**
     * 统计时间
     **/
    @Column(columnDefinition = "varchar(100) not null comment '统计时间'")
    private String countTime;

    /**
     * 存量
     **/
    @Column(columnDefinition = "bigint default 0 comment '存量'")
    private Long stock;

}