JPQL常见查询语句

1.根据字段名称判断是否存在记录：

public boolean classNameExist(String className, DictSchool dictSchool)
    {
        if (className == null)
        {
            return false;
        }
        String jpql = "select count(*) from DictClass dictClass where lower(dictClass.name) = lower(:className) and dictClass.dictSchool = :dictSchool";
        Long count = entityManager.createQuery(jpql, Long.class).setFlushMode(FlushModeType.COMMIT).setParameter(
                    "className", className).setParameter("dictSchool", dictSchool).getSingleResult();
        return count > 0;
    } 

2.根据字段查询List集合

  public List<DictClass> getClassesBySchool(DictSchool school)
    {
        if (school == null)
        {
            logger.info("DictClassDaoImpl getClassesBySchool: " + "school is null");
            return null;
        }
        try
        {
            String jpql = "select dictClass from DictClass dictClass where dictClass.dictSchool = :school";
            return entityManager.createQuery(jpql, DictClass.class).setFlushMode(FlushModeType.COMMIT).setParameter(
                    "school", school).getResultList();
        }
        catch (NoResultException e)
        {
            logger.error("DictClassDaoImpl getClassesBySchool: " + e.getMessage());
            return null;
        }
    }

3.查询在某日期区间的记录 日期格式换化 DATE_FORMAT

select count(*) from TeacherAskLeave teacherAskLeave where lower(TeacherAskLeave.leaveMember) = lower(:member) and  lower(:date) between DATE_FORMAT(TeacherAskLeave.leaveStartDate,'%Y-%m-%d') and DATE_FORMAT(TeacherAskLeave.leaveEndDate,'%Y-%m-%d');

select count(*) from TeacherAskLeave teacherAskLeave where lower(TeacherAskLeave.leaveMember) = lower(:member) and  lower(:date) >= (TeacherAskLeave.leaveStartDate) and lower(:date)< (TeacherAskLeave.leaveEndDate);