Spring data jpa使用
一、添加依赖
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency>
二、基本CRUD
1.在Dao层创建接口
public interface LabelDao extends JpaRepository<Label, String>, JpaSpecificationExecutor<Label> { }
2.该接口继承了jpa,所以自带了基本增删改查功能
labelDao.findAll(); //查所有 labelDao.findById(id); //根据id查 labelDao.save(label);//如果在数据库中主键不存在,save方法会执行添加操作,如果主键在数据库中已存在,save方法会执行更新操作 labelDao.deleteById(id); //根据id删
三、创建pojo。其中的注解都在javax.persistence包下
1.创建实体类
@Entity @Table(name = "tb_pl") public class Pl implements Serializable { @Id //联合主键 @GeneratedValue(strategy = GenerationType.IDENTITY) private String problemid; @Id //联合主键 @GenericGenerator(name = "custom-id", strategy = "com.jiuqi.framework.tool.SnowflakeIDGenerator") @GeneratedValue(strategy = GenerationType.AUTO, generator = "custom-id") private String labelid; public String getProblemid() { return problemid; } public void setProblemid(String problemid) { this.problemid = problemid; } public String getLabelid() { return labelid; } public void setLabelid(String labelid) { this.labelid = labelid; } }
2.@GeneratedValue可指定jpa自带的id生成策略,@GeneratedValue配合@GenericGenerator使用可自定义id生成策略
import cn.hutool.extra.spring.SpringUtil; import com.jiuqi.framework.impl.CachedUidGenerator; import org.hibernate.engine.spi.SharedSessionContractImplementor; import org.hibernate.id.IdentityGenerator; import java.io.Serializable; /** * 描述: 雪花算法id */ public class SnowflakeIDGenerator extends IdentityGenerator { @Override public Serializable generate(SharedSessionContractImplementor s, Object obj) { CachedUidGenerator bean = SpringUtil.getBean(CachedUidGenerator.class); return bean.getUID(); } }
3.声明字段
@Column(name = "user_name", nullable = false, length=32, columnDefinition = "varchar(50) default '-'") private String userName;
4.审计
1)配置类加@EnableJpaAuditing来开启审计功能
2)po实体类添加审计字段
方式1:在po相应字段上加@CreatedDate、@CreatedBy、@LastModifiedDate、@LastModifiedBy来指定创建时间、创建人、最后修改时间、最后修改人
方式2:po继承AbstractAuditBase
四、自定义方法名查询
sprngdatajpa根据你按命名规则定义的方法名自动进行条件查询。例:
public interface RecruitDao extends JpaRepository<Recruit, String>, JpaSpecificationExecutor<Recruit> { /** * 根据状态按时间倒序查职位列表(查6个) * * @param state * @return */ public List<Recruit> findTop6ByStateOrderByCreatetimeDesc(String state); /** * 根据反状态按时间倒序查职位列表(查12个) * * @param state * @return */ public List<Recruit> findTop12ByStateNotOrderByCreatetimeDesc(String state, Pageable pageable); //只要在方法参数加上Pageable类型的形参就能实现分页查询
//Pageable对象一般通过PageRequest.of(int page, int size)方法创建,其中page默认从0开始
}
命名规则表:
关键字 | 方法命名 | sql where字句 |
And | findByNameAndPwd | where name= ? and pwd =? |
Or | findByNameOrSex | where name= ? or sex=? |
Is,Equals | findById,findByIdEquals | where id= ? |
Between | findByIdBetween | where id between ? and ? |
LessThan | findByIdLessThan | where id < ? |
LessThanEquals | findByIdLessThanEquals | where id <= ? |
GreaterThan | findByIdGreaterThan | where id > ? |
GreaterThanEquals | findByIdGreaterThanEquals | where id > = ? |
After | findByIdAfter | where id > ? |
Before | findByIdBefore | where id < ? |
IsNull | findByNameIsNull | where name is null |
isNotNull,NotNull | findByNameNotNull | where name is not null |
Like | findByNameLike | where name like ? |
NotLike | findByNameNotLike | where name not like ? |
StartingWith | findByNameStartingWith | where name like '?%' |
EndingWith | findByNameEndingWith | where name like '%?' |
Containing | findByNameContaining | where name like '%?%' |
OrderBy | findByIdOrderByXDesc | where id=? order by x desc |
Not | findByNameNot | where name <> ? |
In | findByIdIn(Collection<?> c) | where id in (?) |
NotIn | findByNameNot | where name <> ? |
True | findByAaaTue | where aaa = true |
False | findByAaaFalse | where aaa = false |
IgnoreCase | findByNameIgnoreCase | where UPPER(name)=UPPER(?) |
top | findTop100 | top 10/where ROWNUM <=10 |
五、原生使用sql操作数据库
/** * 查询 * * @param labelId * @param pageable * @return */ @Query(value = "select * from tb_problem p, tb_pl pl where p.id = pl.problemid and pl.labelid = ?1 order by p.updatetime desc", nativeQuery = true) public Page<Problem> findNewProblemsByLabelId(String labelId, Pageable pageable);
其中1代表传入的第一个参数,以此类推
/** * 修改 * * @param articleId */ @Modifying @Query(value = "update tb_article set state = 1 where id = ?", nativeQuery = true) public void examine(String articleId);