Spring Data JPA 查询的四种方法

list<user> findbyname(string name);
           ─┬─   ──┬──
          引导词  条件部分

// 年龄最大的前3条
list<user> findtop3byage();

// 年龄最大的第1条（等价于 top1）
user findfirstbyage();

// 去重查询
list<user> finddistinctbylastnameandfirstname(string lastname, string firstname);

// 假设 user 有 address 类型的 address 属性，address 有 city 字段
list<user> findbyaddresscity(string city);

@entity
@table(name = "users")
public class user {
    @id @generatedvalue private integer id;
    private string name;
    private integer age;
    private boolean active;
    // getters/setters
}

public interface userrepository extends jparepository<user, integer> {
    // 等值查询
    list<user> findbyname(string name);
    // 模糊匹配（包含）
    list<user> findbynamecontaining(string infix);
    // 组合条件 + 排序
    list<user> findbynameandageorderbyagedesc(string name, int age);
    // 计数与存在判断
    long countbyname(string name);
    boolean existsbyname(string name);
    // 删除
    void deletebyname(string name);
}

public interface userrepository extends jparepository<user, long> {
    // jpql（操作实体）
    @query("select u from user u where u.age > ?1")
    list<user> findusersbyagegreaterthan(int age);
    
    // 原生 sql（操作表）
    @query(value = "select * from users where age > ?1", nativequery = true)
    list<user> findusersbyagegreaterthannative(int age);
}

@query("select u from user u where u.name = ?1 and u.age = ?2")
user findbynameandage(string name, int age);

@query("select u from user u where u.name = :name and u.age = :age")
user findbynameandage(@param("name") string name, @param("age") int age);

@query("select u from user u where u.age in :ages")
list<user> findbyagein(@param("ages") list<integer> ages);

// 隐式 join（通过属性路径）
@query("select u from user u where u.address.city = :city")
list<user> findbycity(@param("city") string city);

// 显式 join
@query("select u from user u join u.orders o where o.status = :status")
list<user> finduserswithorderstatus(@param("status") orderstatus status);

// 左外连接
@query("select u from user u left join u.orders o where o.total > :amount")
list<user> finduserswithlargeorders(@param("amount") bigdecimal amount);

// 查询年龄大于平均值的用户
@query("select u from user u where u.age > (select avg(age) from user)")
list<user> findusersolderthanaverage();

// exists 子查询
@query("select u from user u where exists (select 1 from order o where o.user = u and o.total > :amount)")
list<user> finduserswithorderabove(@param("amount") bigdecimal amount);

public interface usernameandage {
    string getname();
    int getage();
}

@query("select u.name as name, u.age as age from user u where u.id = :id")
usernameandage findusernameandagebyid(@param("id") long id);

public class userdto {
    private string name;
    private int age;
    public userdto(string name, int age) { this.name = name; this.age = age; }
    // getters
}

@query("select new com.example.dto.userdto(u.name, u.age) from user u where u.id = :id")
userdto finduserdtobyid(@param("id") long id);

@query("select u from user u where u.name in :names")
list<user> findbynamein(@param("names") collection<string> names);

// 引用实体名（多租户/动态表名）
@query("select u from #{#entityname} u where u.name = :name")
list<user> findbyentityname(@param("name") string name);

// 动态排序（慎用于原生sql）
@query("select u from user u order by #{#sortfield} #{#sortdirection}")
list<user> findallsorted(@param("sortfield") string field, @param("sortdirection") string direction);

@modifying
@query("update user u set u.active = false where u.lastlogindate < :cutoff")
int deactivateinactiveusers(@param("cutoff") localdatetime cutoff);

@modifying(clearautomatically = true)
@query("update user u set u.active = false where u.id = :id")
int deactivateuser(@param("id") long id);

@query("select u from user u where (:name is null or u.name = :name) and (:age is null or u.age = :age)")
list<user> searchusers(@param("name") string name, @param("age") integer age);

@query("select u, (u.age - (select avg(age) from user)) as agediff from user u where u.active = true order by agediff desc")
list<object[]> finduserswithagedifference();

@modifying(clearautomatically = true)
@query("update order o set o.status = :newstatus where o.status = :oldstatus and o.createddate < :cutoff")
int batchupdateorderstatus(@param("newstatus") orderstatus newstatus,
                           @param("oldstatus") orderstatus oldstatus,
                           @param("cutoff") localdatetime cutoff);

@query("select o from order o where function('date', o.createddate) = current_date")
list<order> findtodayorders();

@query("select u.department, count(u), avg(u.salary) from user u group by u.department having avg(u.salary) > :avg")
list<object[]> getdepartmentstats(@param("avg") double avg);

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.sql=debug
logging.level.org.hibernate.type.descriptor.sql.basicbinder=trace

public interface userrepository extends jparepository<user, long>, 
                                        jpaspecificationexecutor<user> {
}

@functionalinterface
public interface specification<t> {
    predicate topredicate(root<t> root, criteriaquery<?> query, criteriabuilder builder);
}

specification<user> agegreaterthan18 = (root, query, builder) -> 
    builder.greaterthan(root.get("age"), 18);

list<user> users = userrepository.findall(agegreaterthan18);

specification<user> spec = (root, query, builder) -> {
    predicate agebetween = builder.between(root.get("age"), 18, 30);
    predicate isactive = builder.istrue(root.get("active"));
    return builder.and(agebetween, isactive);
};
list<user> users = userrepository.findall(spec);

public class userspecs {
    public static specification<user> agegreaterthan(int age) {
        return (root, query, builder) -> builder.greaterthan(root.get("age"), age);
    }
    public static specification<user> isactive() {
        return (root, query, builder) -> builder.istrue(root.get("active"));
    }
    public static specification<user> namecontains(string keyword) {
        return (root, query, builder) -> 
            builder.like(root.get("name"), "%" + keyword + "%");
    }
}

list<user> users = userrepository.findall(
    specification.where(userspecs.agegreaterthan(18))
                  .and(userspecs.isactive())
                  .and(userspecs.namecontains("张"))
);

specification<user> agegreaterthan18 = (root, query, builder) -> 
    builder.greaterthan(root.get(user_.age), 18); // 编译期安全！

// 创建探针（null 属性默认忽略）
user probe = new user();
probe.setname("张三");
probe.setage(25);

example<user> example = example.of(probe);
list<user> results = userrepository.findall(example);

examplematcher matcher = examplematcher.matching()
    .withignorepaths("id", "createdat")          // 忽略这些字段
    .withstringmatcher(stringmatcher.containing) // 全局包含匹配
    .withignorecase()                            // 全局忽略大小写
    .withmatcher("email", match -> match.endswith())
    .withmatcher("name", match -> match.startswith().ignorecase());

example<user> example = example.of(probe, matcher);
list<user> results = userrepository.findall(example);

user probe = new user();
probe.setactive(true);

example<user> example = example.of(probe);

list<user> results = userrepository.findby(example, 
    query -> query
        .sortby(sort.by("age").descending())
        .page(pagerequest.of(0, 10))
        .stream()
        .collect(collectors.tolist())
);

// 创建分页请求：第0页，每页10条，按年龄降序
pageable pageable = pagerequest.of(0, 10, sort.by("age").descending());

// 多字段排序
pageable pageable = pagerequest.of(0, 10, sort.by("age").descending().and(sort.by("id").ascending()));

public interface userrepository extends jparepository<user, long> {

    // 返回 page（含总记录数）
    page<user> findbynamecontaining(string keyword, pageable pageable);
    
    // 返回 slice（不含总记录数，性能更高）
    slice<user> findbyagegreaterthan(int age, pageable pageable);
    
    // 结合排序（pageable 中已包含排序，无需单独加 orderby）
    page<user> findbyactivetrue(pageable pageable);
}

// 查询姓名包含"张"的用户，按年龄降序分页
pageable pageable = pagerequest.of(0, 10, sort.by(sort.direction.desc, "age"));
page<user> page = userrepository.findbynamecontaining("张", pageable);

system.out.println("总记录数：" + page.gettotalelements());
system.out.println("总页数：" + page.gettotalpages());
system.out.println("当前页数据：" + page.getcontent());

@query("select u from user u where u.age > :age")
page<user> findbyagegreaterthan(@param("age") int age, pageable pageable);

@query(value = "select u from user u left join u.orders o where o.total > :amount",
       countquery = "select count(u) from user u where exists (select 1 from order o where o.user = u and o.total > :amount)")
page<user> finduserswithlargeorders(@param("amount") bigdecimal amount, pageable pageable);

@query(value = "select * from users where age > ?1 order by id",
       countquery = "select count(*) from users where age > ?1",
       nativequery = true)
page<user> findusersbyagegreaterthannative(int age, pageable pageable);

public interface userrepository extends jparepository<user, long>, 
                                        jpaspecificationexecutor<user> {
    // 无需额外定义方法，父接口已提供
}

// 构建动态条件
specification<user> spec = (root, query, builder) -> {
    predicate agepredicate = builder.between(root.get("age"), 18, 30);
    predicate activepredicate = builder.istrue(root.get("active"));
    return builder.and(agepredicate, activepredicate);
};

// 分页 + 排序
pageable pageable = pagerequest.of(0, 10, sort.by("age").descending());
page<user> page = userrepository.findall(spec, pageable);

specification<user> spec = (root, query, builder) -> 
    builder.greaterthan(root.get(user_.age), 18);

page<user> page = userrepository.findall(spec, pagerequest.of(0, 10, sort.by(user_.age).descending()));

public interface userrepository extends jparepository<user, long> {
    // 继承自 querybyexampleexecutor，无需额外定义
}

// 构建探针
user probe = new user();
probe.setname("张");
probe.setactive(true);

// 构建匹配器
examplematcher matcher = examplematcher.matching()
    .withstringmatcher(stringmatcher.containing)
    .withignorepaths("id", "createdat");

example<user> example = example.of(probe, matcher);

// 分页查询
pageable pageable = pagerequest.of(0, 10, sort.by("age").ascending());
page<user> page = userrepository.findall(example, pageable);

// 获取 list
list<user> results = userrepository.findby(example, 
    query -> query
        .sortby(sort.by("age").descending())
        .page(pagerequest.of(0, 10))
        .stream()
        .collect(collectors.tolist())
);

// 若需要完整 page 对象
page<user> page = userrepository.findby(example, 
    query -> query
        .sortby(sort.by("age").descending())
        .page(pagerequest.of(0, 10))
);

// 安全且优雅的排序构建
sort sort = sort.by("age").descending()
                .and(sort.by("id").ascending());

public interface userrepository extends jparepository<user, long>,
                                       jpaspecificationexecutor<user> {
    
    // 简单查询 → 派生
    list<user> findbyname(string name);
    
    // 复杂关联 → @query
    @query("select u from user u join u.orders o where o.total > :amount")
    list<user> finduserswithordertotalgreaterthan(@param("amount") bigdecimal amount);
    
    // 动态多条件 → specification（由外部调用）
    // 由 service 层构建 specification 传入 findall(specification)
    
    // 快速原型 → qbe（由外部调用）
    // 由 service 层构建 example 传入 findall(example)
}