//方法名称命名规则
List findByUsernameIs(String string);
List findByUsernameLike(String string);
List findByUsernameAndUserageGreaterThanEqual(String name,Integer age);
//使用@Query注解查询
@Query(value="from Users where username = ?")
List<Users> queryUserByNameUseJPQL(String name);
@Query("from Users where username like ?")
List<Users> queryUserByLikeNameUseJPQL(String name);
@Query("from Users where username = ? and userage >= ?")
List<Users> queryUserByNameAndAge(String name,Integer age);
//使用@Query注解查询SQL
//nativeQuery:默认的是false.表示不开启sql查询。是否对value中的语句做转义。
@Query(value="select * from t_users where username = ?",nativeQuery=true)
List<Users> queryUserByNameUseSQL(String name);
@Query(value="select * from t_users where username like ?",nativeQuery=true)
List<Users> queryUserByLikeNameUseSQL(String name);
@Query(value="select * from t_users where username = ? and userage >= ?",nativeQuery=true)
List<Users> queryUserByNameAndAgeUseSQL(String name,Integer age);
@Query("update Users set userage = ? where userid = ?")
@Modifying //@Modifying当前语句是一个更新语句
void updateUserAgeById(Integer age,Integer id);
带参数查询写法
/**
* 门店私教核销订单
* @param orderId
* @param completeTime
* @return
*/
@Transactional
@Modifying
@Query(value = "update t_course_order set order_status = 2,complete_time = (:completeTime) where order_id = (:orderId) and order_status = 1 ",nativeQuery=true)
int hexOrder(@Param("orderId")String orderId, @Param("completeTime")Date completeTime);
JPA分页demo 带条件搜索、按照创建时间降序排序
@PostMapping("get-members")
public HttpResult getMembers(@RequestBody PageDto pageDto){
String province = (String) pageDto.getParam("province");
String city = (String) pageDto.getParam("city");
String county = (String) pageDto.getParam("county");
PageRequest request = PageRequest.of(pageDto.getPage()-1, pageDto.getPageSize(), Sort.by("createTime").descending());
GkNeed condition1 = new GkNeed();
if (StringUtils.isNotEmpty(province) && !province.equals("选择省")){
condition1.setProvince(province);
}
if (StringUtils.isNotEmpty(city) && !city.equals("选择市")){
condition1.setCity(city);
}
if (StringUtils.isNotEmpty(county) && !county.equals("选择区/县")){
condition1.setCounty(county);
}
Example<GkNeed> example = Example.of(condition1);
Page<GkNeed> productInfoPage = gkNeedRepository.findAll(example,request);
Map<String, Object> map = new HashMap<>();
map.put("page",pageDto.getPage());
map.put("pageSize",pageDto.getPageSize());
map.put("total",productInfoPage.getTotalElements());
map.put("pages",productInfoPage.getTotalPages());
map.put("record",productInfoPage.getContent());
return HttpResult.ok(map);
}