Spring Boot
构建,使用JHipster
生成业务代码,包含基础的增删改查代码PostgreSQL
作为业务数据库,使用自动生成的JPA
构建数据更新语查询 Specification<AreaConfig> specification = (Specification<AreaConfig>) (root, query, cb) ->
{
List<Predicate> predicateList = new ArrayList<>();
Join<AreaConfig, SmartIntersection> join = root.join("smartIntersection", JoinType.LEFT);
predicateList.add(cb.equal(join.get("id").as(Long.class), smartIntersection.getId()));
Predicate[] pre = new Predicate[predicateList.size()];
pre = predicateList.toArray(pre);
return query.where(pre).getRestriction();
};
return areaConfigRepository.findAll(specification);
SELECT
语句查询表中的数据时,PostgreSQL
不确保按照一定的顺序返回结果Repository
查询方法(如.findAll
)时传递参数指定Sort
,代码示例如下: Specification<GreenWaveRouteConfig> specification = (Specification<GreenWaveRouteConfig>) (root, query, cb) ->
{
List<Predicate> predicateList = new ArrayList<>();
Join<GreenWaveRouteConfig, GreenWaveRoute> join = root.join("greenWaveRoute", JoinType.LEFT);
predicateList.add(cb.equal(join.get("id").as(Long.class), routeId));
Predicate[] pre = new Predicate[predicateList.size()];
pre = predicateList.toArray(pre);
return query.where(pre).getRestriction();
};
return greenWaveRouteConfigRepository.findAll(specification, Sort.by("index"));
specification
里使用query.orderBy
指定排序 Specification<SignalManualControlLog> specification = (root, query, cb) ->
{
List<Predicate> predicateList = new ArrayList<>();
if (intersectionId != null) {
predicateList.add(cb.equal(root.get("intersectionId").as(Long.class), intersectionId));
}
//时间倒序
query.orderBy(cb.desc(root.get("opTime")));
Predicate[] pre = new Predicate[predicateList.size()];
pre = predicateList.toArray(pre);
return query.where(pre).getRestriction();
};
List<SignalManualControlLog> controlLogs = manualControlLogRepository.findAll(specification);
ORDER BY
子句,那么查询结果会按照该子句指定的排序规则返回。ORDER BY
子句明确指定排序规则。另外,你还可以考虑使用适当的索引或统计信息来帮助PostgreSQL优化查询执行计划,以确保结果按照预期的顺序返回。ORDER BY
子句中的ORDER BY ... USING
,以及SET
命令中的random_page_cost
等,可以用来影响PostgreSQL查询优化器的行为,以确保结果按照一定的顺序返回。ORDER BY
子句来明确指定排序规则,以确保结果的顺序是可预测的。JPA(Java Persistence API)是一种用于管理Java应用程序中持久化数据的API。它为开发人员提供了一种方便的方法来在数据库中存储、检索和管理对象。在使用JPA的过程中,开发人员经常需要执行各种类型的查询来检索数据。以下是一些常见的JPA查询介绍:
TypedQuery<Customer> query = entityManager.createQuery("SELECT c FROM Customer c WHERE c.age > 18", Customer.class);
List<Customer> customers = query.getResultList();
@NamedQuery(name="Customer.findAllAdults", query="SELECT c FROM Customer c WHERE c.age > 18")
public class Customer {
//...
}
TypedQuery<Customer> query = entityManager.createNamedQuery("Customer.findAllAdults", Customer.class);
List<Customer> customers = query.getResultList();
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Customer> query = cb.createQuery(Customer.class);
Root<Customer> root = query.from(Customer.class);
query.select(root).where(cb.greaterThan(root.get("age"), 18));
List<Customer> customers = entityManager.createQuery(query).getResultList();