最近突然用到JPA的功能,之前没有接触过,在这里单纯整理和分享,只是自己的处理方法和思路
实体类注解
1.@Data 单纯理解是代替了get和set,写此代码可以不用再类中写get、set方法了,需要引入lombok包
2.@ApiModel 是可在Swagger页面显示的注解
3.@SQLDelete(sql = “update demo set isDelete = 1 where id = ?”) 在执行JPA的删除操作时,可根据后边的内容来确定每次删除时默认的条件,这里可以理解当传如id后,每次都将isDelete的字段改为1
4.@Where(clause = “isDelete = 0”) 每次Select时,都将默认加上isDelete=0这个条件
5.@DynamicInsert @DynamicUpdate 生成的SQL就会只输出SET有变化的字段
@Data
@Entity
@Table(name = "demo")
@ApiModel(value = "demo 对象")
@SQLDelete(sql = "update demo set isDelete = 1 where id = ?")
@Where(clause = "isDelete = 0")
@DynamicInsert
@DynamicUpdate
@Accessors(chain = true)
public class Demo implements Serializable {
@Id
@GeneratedValue(generator = "system-uuid")
@GenericGenerator(name = "system-uuid", strategy = "uuid")
@ApiModelProperty(value = "主键", name = "id", hidden = true)
private String id;
@ApiModelProperty(value = "姓名", name = "userName", required = false)
@Column
private String userName;
@ApiModelProperty(value = "年龄", name = "userAge", required = false)
private String userAge;
@ApiModelProperty(value = "性别", name = "userSex", required = false)
private String userSex;
@ApiModelProperty(value = "删除", name = "isDelete", required = false)
private Integer isDelete;
@ApiModelProperty(value = "时间", name = "createTime", required = false)
private String createTime;
}
DAO
@Modifying在update中使用
@Repository
public interface DemoDao extends JpaRepository<Demo, String> {
@Modifying
@Query("update Demo set userName = :userName, userAge = :userAge,userSex= :userSex where id = :id")
Integer updateById(@Param("id") String id, @Param("userName") String userName,
@Query(value="SELECT d.id,d.userName,d.userAge,d.userSex,IFNULL(u.uphone,'') as uphone from demo d LEFT JOIN user_table u ON d.userName=u.uname WHERE if(:#{#demo.userName} !='',d.userName=:#{#demo.userName},1=1) ",//d.userName=:#{#demo.userName}
countQuery = "SELECT count(*) FROM demo d left join user_table u ON d.userName=u.uname WHERE if(:#{#demo.userName} !='',d.userName=:#{#demo.userName},1=1)",nativeQuery=true)
Page<Map> findManyTable(@Param("demo") Demo demo, Pageable pageable);
@Query(value="SELECT new com.sncy.evaluateservice.entity.DemoUserMap(d,u) from Demo d LEFT JOIN UserTable u ON d.userName=u.uname WHERE d.userName=:#{#demo.userName}")
Page<DemoUserMap> findManyTableHQL(@Param("demo") Demo demo, Pageable pageable);
}
DAO层集成JpaRepository<Demo, String>后,泛型中的Demo是原本的实体类
1.单表 Demo查询的话,可以直接用findAll或者findOne来执行
2.多表对多表的情况,可以根据findManyTable方法,编写原生SQL来进行业务操作,这里返回的是Page
SELECT new com.sncy.evaluateservice.entity.DemoUserMap(d,u) from Demo d LEFT JOIN UserTable u ON d.userName=u.uname WHERE d.userName=:#{#demo.userName}")
HQL中 所有的表名均是类名,不是数据库表名,返回值DemoUserMap是需要单独生成一个实体类,包含需要查询两个表的实体类,可看下图二