org.hibernate.SQLQuery.setParameterList()

Here are the examples of the java api org.hibernate.SQLQuery.setParameterList() taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.

1 Examples 7

5 Source : ParticipationService.java
with European Union Public License 1.1
from EUSurvey

public List<Invitation> getInvitations(User user, SqlPagination paging, String survey, String surveystatus, Date expiryStart, Date expiryEnd, Date startInv, Date endInv) {
    Session session = sessionFactory.getCurrentSession();
    String where = "";
    if (survey != null) {
        where = " s.replacedLE like :survey AND ";
    }
    if (surveystatus != null) {
        if (surveystatus.equalsIgnoreCase("Published")) {
            where += " s.ACTIVE = 1 AND ";
        } else {
            where += " s.ACTIVE = 0 AND ";
        }
    }
    if (expiryStart != null) {
        where += " s.SURVEY_START_DATE >= :start AND ";
    }
    if (expiryEnd != null) {
        where += " s.SURVEY_END_DATE < :end AND ";
    }
    if (startInv != null) {
        where += " i.ATTENDEE_INVITED >= :startinv AND ";
    }
    if (endInv != null) {
        where += " i.ATTENDEE_INVITED < :endinv AND ";
    }
    StringBuilder sql = new StringBuilder();
    sql.append("SELECT d.INVITATION_ID FROM ( SELECT i.INVITATION_ID, i.ATTENDEE_INVITED FROM INVITATIONS i JOIN PARTICIPANTS p ON p.PARTICIPATION_ID = i.PARTICIPATIONGROUP_ID JOIN ATTENDEE a ON i.ATTENDEE_ID = a.ATTENDEE_ID JOIN SURVEYS s ON s.SURVEY_ID = p.PARTICIPATION_SURVEY_ID  WHERE ");
    sql.append(where).append("p.PARTICIPATION_TYPE = 1 AND a.ATTENDEE_EMAIL = :email AND i.ATTENDEE_ANSWERS = 0");
    sql.append(" UNION SELECT i.INVITATION_ID, i.ATTENDEE_INVITED FROM INVITATIONS i JOIN PARTICIPANTS p ON p.PARTICIPATION_ID = i.PARTICIPATIONGROUP_ID JOIN ECASUSERS a ON i.ATTENDEE_ID = a.USER_ID JOIN SURVEYS s ON s.SURVEY_ID = p.PARTICIPATION_SURVEY_ID  WHERE ");
    sql.append(where).append("p.PARTICIPATION_TYPE = 2 AND a.USER_EMAIL = :email AND i.ATTENDEE_ANSWERS = 0 ) AS d ORDER BY d.ATTENDEE_INVITED DESC");
    SQLQuery query = null;
    if (user.getOtherEmail() != null && user.getOtherEmail().length() > 0) {
        List<String> allemails = user.getAllEmailAddresses();
        String ssql = sql.toString();
        ssql = ssql.replaceAll("= :email", "IN (:emails)");
        query = session.createSQLQuery(ssql);
        query.setParameterList("emails", allemails);
    } else {
        query = session.createSQLQuery(sql.toString());
        query.setString(Constants.EMAIL, user.getEmail());
    }
    query.setFirstResult(paging.getFirstResult()).setMaxResults(paging.getMaxResult());
    if (survey != null) {
        query.setString(Constants.SURVEY, "%" + survey + "%");
    }
    if (expiryStart != null) {
        query.setDate("start", expiryStart);
    }
    if (expiryEnd != null) {
        Calendar cal = Calendar.getInstance();
        cal.setTime(expiryEnd);
        cal.add(Calendar.DAY_OF_YEAR, 1);
        query.setDate("end", cal.getTime());
    }
    if (startInv != null) {
        query.setDate("startinv", startInv);
    }
    if (endInv != null) {
        Calendar cal = Calendar.getInstance();
        cal.setTime(endInv);
        cal.add(Calendar.DAY_OF_YEAR, 1);
        query.setDate("endinv", cal.getTime());
    }
    List<Invitation> result = new ArrayList<>();
    for (Object o : query.list()) {
        int id = ConversionTools.getValue(o);
        Invitation item = (Invitation) session.get(Invitation.clreplaced, id);
        result.add(item);
    }
    return result;
}