JPA 多表分页查询

第一次接触到使用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{

	
}