hql和ifnull应用两实例

hql应用：

public List<Map<String, Object>> getAllVehicleByPlate(String vehiclePlate,String userId,Long companyId) {

        String hql = "select new Map(" + "v.simCard as id,v.inNetworkTime as inNetworkTime,"
                + " v.plateNumber as text)"
                + " from Vehicle v , Fleet f ,Company c "
                + " where  v.fleetId = f.fleetId and v.companyId = c.companyId "
                + " and f.state='0' and c.delFlag='0' and v.plateNumber is not null "
                + " and v.plateNumber!='' and v.state='0' and v.plateNumber like '%"+vehiclePlate+"%'";
        if(StringUtils.hasText(userId)){
            hql += " and c.manager ='" + userId + "'";
        }
        if(companyId != null){
            hql += " and v.companyId =" + companyId;
        }
        return this.findByHQL(hql);
    }

应用ifnull：

rows_sql =
                " select a.news_id as newsId, a.title, a.release_date " +
                "as releaseDate, a.state, IFNULL(d.employee_name ,'单位管理员')" +
                "AS employeeName" +
                " from tb_b_news a, tb_c_system_user c left join tb_c_employee d " +
                "on c.employee_id = d.employee_id where a.news_type = '0' " +
                "and a.state = "+state+" and a.release_man = c.user_id" +
                " and a.release_man = '"+userId+"'";

rows_sql =
                " select a.news_id as newsId, a.title, a.release_date " +
                "as releaseDate, a.state, IFNULL(d.employee_name ,'单位管理员') as employeeName," +
                " (select e.news_sort_name from tb_b_news_sort e " +
                "where a.news_sort = e.news_sort_id ) as newsSort" +
                " from tb_b_news a, tb_c_system_user c " +
                "left join tb_c_employee d on c.employee_id = d.employee_id " +
                "where a.news_type = '"+newsType+"' and a.state = "+state+" and a.release_man = c.user_id" +
                " and a.release_man = '"+userId+"'";

修改前为以下：

rows_sql =
                " select a.news_id as newsId, a.title, a.release_date " +
                "as releaseDate, a.state, d.employee_name as employeeName," +
                " (select e.news_sort_name from tb_b_news_sort e " +
                "where a.news_sort = e.news_sort_id ) as newsSort" +
                " from tb_b_news a, tb_c_system_user c " +
                "left join tb_c_employee d on c.employee_id = d.employee_id " +
                "where a.news_type = '"+newsType+"' and a.state = "+state+" and a.release_man = c.user_id" +
                " and a.release_man = '"+userId+"'";