第一次接触到使用JPA + hibernate 实现分页查询,在此记录一下,以备以后参考 。java代码仅实现简单的分页查询,更复杂的SQL通过视图实现。
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Component;
import com.toyota.jpa.entity.VehicleUnionCarLogin;
@Component
public interface SpeciRepository extends JpaSpecificationExecutor<VehicleUnionCarLogin>,JpaRepository<VehicleUnionCarLogin, Long>{
}
Dao层接口
import org.springframework.data.rest.core.annotation.RepositoryRestResource;
import com.toyota.jpa.entity.Voltage;
@RepositoryRestResource
public interface VoltageDao extends BaseRestDao<Voltage, Long> {
}
Service层
import java.util.Map;
import org.springframework.data.domain.Page;
import com.toyota.jpa.entity.VehicleUnionCarLogin;
public interface VoltageOageService {
public Page<VehicleUnionCarLogin> findByPageAndParams(final Map<String, String> conditions);
}
Service实现类
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.Map;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Path;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.toyota.jpa.dao.SpeciRepository;
import com.toyota.jpa.entity.VehicleUnionCarLogin;
import com.toyota.service.VoltageOageService;
@Service
@Transactional
public class VoltageOageServiceImpl implements VoltageOageService{
@Autowired
private SpeciRepository speciRepository;
@Override
public Page<VehicleUnionCarLogin> findByPageAndParams(Map<String, String> conditions) {
//当前页
Integer pageNumber = Integer.valueOf(conditions.get("currentPage"));
//页面条数
Integer pageSize = Integer.valueOf(conditions.get("pageSize"));
@SuppressWarnings("deprecation")
Pageable pageable = new PageRequest(pageNumber - 1, pageSize); //分页信息
Specification<VehicleUnionCarLogin> spec = new Specification<VehicleUnionCarLogin>() { //查询条件构造
/**
*
*/
private static final long serialVersionUID = 1L;
@Override
public Predicate toPredicate(Root<VehicleUnionCarLogin> root, CriteriaQuery<?> query,CriteriaBuilder cb) {
/*开始时间*/
String dateTimeFrom = conditions.get("dateTimeFrom");
/*结束时间*/
String dateTimeTo = conditions.get("dateTimeTo");
String vinProgram = conditions.get("vin");
Path<String> vin = root.get("vin");
Path<LocalDateTime> dataTime = root.get("dataTime");
//字符串转LocalDateTime时间
DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
/*判断VIN是否为空*/
if(vinProgram == null || vinProgram.isEmpty()) {
vinProgram = "";
}
Predicate p1 = cb.like(vin, "%"+vinProgram+"%");
/*判端结束始时间*/
if(dateTimeTo == null ||dateTimeTo.isEmpty()) {
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
dateTimeTo = format.format(new Date());
}else {
dateTimeTo = dateTimeTo.replace("T", " ");
}
/*结束时间默认为当前时间*/
if(dateTimeFrom == null || dateTimeFrom.isEmpty() ) {
LocalDateTime dataTo = LocalDateTime.parse(dateTimeTo, df);
Predicate p3 = cb.lessThanOrEqualTo(dataTime, dataTo);
Predicate p = cb.and(p1,p3);
return p;
}else {
LocalDateTime dataFrom = LocalDateTime.parse(dateTimeFrom.replace("T", " "), df);
LocalDateTime dataTo = LocalDateTime.parse(dateTimeTo, df);
Predicate p2 = cb.greaterThanOrEqualTo(dataTime, dataFrom);
Predicate p3 = cb.lessThanOrEqualTo(dataTime, dataTo);
Predicate p = cb.and(p1,p2,p3);
return p;
}
}
};
return speciRepository.findAll(spec, pageable);
}
}
Controller层
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import com.toyota.jpa.entity.VehicleUnionCarLogin;
import com.toyota.service.VoltageOageService;
import net.sf.json.JSONArray;
@RestController
public class VehicleController {
@PersistenceContext
private EntityManager entityManager;
@Autowired
private VoltageOageService voltageOageService;
@GetMapping("/vehicles/listWithLogin")
public ResponseEntity<?> listWithLogin(@RequestParam Map<String, String> conditions) {
Date date= new Date();
Page<VehicleUnionCarLogin> pages = voltageOageService.findByPageAndParams(conditions);
List<VehicleUnionCarLogin> resultList = new ArrayList<VehicleUnionCarLogin>();
resultList = pages.getContent();
JSONArray jsonArray = new JSONArray();
for(VehicleUnionCarLogin tmp : resultList) {
JSONArray jsonArray2 = new JSONArray();
jsonArray2.add(tmp.getVin());
jsonArray2.add(tmp.getDataTime().toString());
jsonArray.add(jsonArray2);
}
// 返回值
Map<String, Object> returnValue = new HashMap<String, Object>();
Date date2 = new Date();
System.out.println("耗时:" + Math.subtractExact(date2.getTime(), date.getTime()));
returnValue.put("dataList", jsonArray);
//数据总条数
returnValue.put("count", pages.getTotalElements());
returnValue.put("msg", "");
returnValue.put("msgType", "2");
returnValue.put("resCode", 0);
return ResponseEntity.ok(returnValue);
}
}
Entity类
import javax.persistence.Entity;
import javax.persistence.Index;
import javax.persistence.Table;
import lombok.Data;
import lombok.EqualsAndHashCode;
@Entity
@Table(indexes = { @Index(columnList = "vin,dataTime") })
@Data
@EqualsAndHashCode(callSuper = true)
public class VehicleUnionCarLogin extends MsgBaseEntity{
}