当我们使用方法名称很难,达到预期的查询结果,就可以使用@Query进行查询,@Query是一种添加自定义查询的便利方式
(方法名称查询见http://blog.csdn.net/niugang0920/article/details/79426521)
使用@Query注解,使用注解有两种方式,一种是JPQL的SQL语言方式,一种是原生SQL的语言.
1.User.java 实体类
package com.niugang.entity;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
//javax.persistence.Entity
@Entity
@Table(name="user")
public class User {
/**
*mysql 自定生成主键
*指示持久性提供者必须分配使用数据库标识列的实体的主键
*/
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="id" ,insertable=false,updatable=false)
private Integer id;
/**
* @Column中的name默认和字段名相同
*/
@Column(name = "name")
private String name;
@Column(name = "age")
private Integer age;
@Column(name = "phone")
private String phone;
@Column(name = "password")
private String password;
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", age=" + age + ", phone=" + phone + ", password=" + password
+ "]";
}
}
2.UserDao.java
package com.niugang.dao;
import java.util.List;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import com.niugang.entity.User;
/**
* JpaRepository springboot已经自动配置了,也已经注入数据源了
*
* @author niugang
*
*/
@Repository
public interface UserDao extends JpaRepository<User, Integer> {
/**
* ?加数字表示占位符,?1代表在方法参数里的第一个参数,区别于其他的index,这里从1开始
*/
// select * from User where name = ?1 注意不能写成*
@Query(value = "select u from User u where u.name = ?1")
User findUserByName1(String name);
/**
* =:加上变量名,这里是与方法参数中有@Param的值匹配的,而不是与实际参数匹配的 和上面第一个原理是一样的
*/
@Query(value = "select u from User u where u.name = :name")
User findUserByName2(@Param("name") String username);
/**
* 通过原生sql 进行查询
* 开启nativeQuery=true,在value里可以用原生SQL语句完成查询
*/
@Query(nativeQuery = true, value = "select * from user u where u.name = :name")
User findUserByNativeSQL(@Param("name") String username);
/**
* 模糊查询 这里的%只能放在占位的前面,后面不行
*/
@Query(value = "select u from User u where u.name like %?1% ")
List<User> findUserLike(String name);
/**
* 调条件分页查询 Pageable:spring-data-jpa自带接口
*
* @param name
* :查询条件name
* @param page:分页对象
* @return
*/
@Query(value = "select u from User u where u.name like %?1% ")
Page<User> findUserLikeByPage(String name, Pageable page);
/**
* @Query查询不支持,排序并且分页
* 源码分析
* if (hasParameterOfType(method, Pageable.class)) {
if (!isStreamQuery()) {
assertReturnTypeAssignable(method, Slice.class, Page.class, List.class);
}
if (hasParameterOfType(method, Sort.class)) {
throw new IllegalStateException(String.format("Method must not have Pageable *and* Sort parameter. "
+ "Use sorting capabilities on Pageble instead! Offending method: %s", method.toString()));
}
}
*
* @param sort :排序对象 org.springframework.data.domain.Sort
* @return
*/
@Query(value = "select u from User u")
List<User> findUserLikeBySort(Sort sort);
}
3. findUserLikeByPage(String name, Pageable page)接口解释:
public Page<User> findUserByPage(String name) {
PageRequest pageRequest = new PageRequest(0, 4);
return userDao.findUserLikeByPage(name, pageRequest);
}
4.排序测试代码
/**
* 单条件排序
*/
public List<User> findListSortSingleCondition(){
//id升序查询
Sort sort = new Sort(Sort.Direction.ASC,"id");
return userDao.findUserLikeBySort(sort);
}
/**
* 多条件排序
*/
public List<User> findListSortMultiCondition(){
List<Order> orders=new ArrayList<Order>();
Order orderId = new Sort.Order(Sort.Direction.DESC,"id");
Order orderAge = new Sort.Order(Sort.Direction.DESC,"age");
orders.add(orderId);
orders.add(orderAge);
Sort sort = new Sort(orders);
return userDao.findUserLikeBySort(sort);
}
微信公众号:
JAVA程序猿成长之路
分享资源,记录程序猿成长点滴。专注于Java,Spring,SpringBoot,SpringCloud,分布式,微服务。