jpa写原生sql-EntityManager

废话不多说

package com.meeno.trainsys.meeting.service;

import com.google.common.collect.Lists;
import com.meeno.framework.constants.Constants;
import com.meeno.framework.page.utils.PageUtils;
import com.meeno.framework.util.Constant;
import com.meeno.framework.util.DateUtils;
import com.meeno.framework.util.ErrEnum;
import com.meeno.framework.util.MeenoAssert;
import com.meeno.trainsys.employee.entity.Employee;
import com.meeno.trainsys.employee.repository.EmployeeRepository;
import com.meeno.trainsys.meeting.entity.Meeting;
import com.meeno.trainsys.meeting.entity.SignRecord;
import com.meeno.trainsys.meeting.repository.MeetingRepository;
import com.meeno.trainsys.meeting.repository.SignRecordRepository;
import com.meeno.trainsys.meeting.view.SignRecordView;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;
import java.util.Map;

/**
 * @description: 签到ServiceImpl
 * @author: Wzq
 * @create: 2020-01-03 11:24
 */
@Service
@Transactional(rollbackFor = Exception.class)
public class SignRecordServiceImpl implements  SignRecordService {

    @PersistenceContext
    EntityManager entityManager;


    @Override
    public Object findMeetingSignRecoredPage(Long meetingId, Integer pageIndex, Integer pageSize) {

        MeenoAssert.notNull(meetingId, ErrEnum.MEETING_ID_IS_EMPTY);
        Meeting meeting = this.meetingRepository.getOne(meetingId);
        MeenoAssert.notNull(meeting,ErrEnum.MEETING_IS_NOT_EXIST);

        Pageable pageable = PageUtils.getPageable(pageIndex,pageSize);

        StringBuilder sql = new StringBuilder();
        sql.append(" select ep.name,MAX(r.sign) from mnt_emp_rel_meeting as e  ");
        sql.append(" left join mnt_sign_record as r on(r.employee_id=e.employee_id)  ");
        sql.append(" left join mnt_employee as ep on(ep.id = e.employee_id) ");
        sql.append(" where e.meeting_id = ? ");
        sql.append(" order by  r.sign desc,r.create_date asc ");


        Query query = entityManager.createNativeQuery(sql.toString());

        query.setFirstResult(pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());
        query.setParameter(1,meetingId);

        List<Object[]> list = query.getResultList();
        List<SignRecordView> listView = Lists.newArrayList();

        if(list != null && !list.isEmpty()){
            for (Object[] objects : list) {
                String empName = objects[0]==null?null:objects[0].toString();
                Integer sign = objects[1]==null?Constants.SIGN_RECORD_NO:Integer.parseInt(objects[1].toString());
                SignRecordView view = new SignRecordView();
                view.setEmployeeName(empName);
                view.setSign(sign);
                listView.add(view);
            }
        }

        //count
        StringBuilder countSql = new StringBuilder();
        countSql.append(" select count(distinct e.id) from mnt_emp_rel_meeting as e  ");
        countSql.append(" left join mnt_sign_record as r on(r.employee_id=e.employee_id)  ");
        countSql.append(" left join mnt_employee as ep on(ep.id = e.employee_id) ");
        countSql.append(" where e.meeting_id = ? ");
        countSql.append(" order by  r.sign desc,r.create_date asc ");

        Query countQuery = entityManager.createNativeQuery(countSql.toString());
        countQuery.setParameter(1,meetingId);
        Object singleResult = countQuery.getResultList().get(0);
        Integer count = singleResult==null?0:Integer.valueOf(singleResult.toString());


        Map<String, Object> resultPage = PageUtils.getResultPage(count, listView);

        return resultPage;
    }

}