Example usage for org.hibernate SQLQuery setParameter

Introduction

In this page you can find the example usage for org.hibernate SQLQuery setParameter.

Prototype

@Override
    NativeQuery<T> setParameter(int position, Object val);

Source Link

Usage

From source file:com.football.site.db.DbHelper.java

public static ArrayList<FixturesRet> GetLeagueFixture(int leagueId) {
    ArrayList<FixturesRet> retList = new ArrayList<>();
    Transaction tran = null;/*  w  ww. j av  a2s. co m*/
    try {
        Session s = HibernateUtil.currentSession();
        tran = s.beginTransaction();
        SQLQuery q = s.createSQLQuery("SELECT * FROM fixtures_view WHERE legue_id=:leagueId");
        q.setParameter("leagueId", leagueId);
        List lst = q.list();
        FixturesRet item = null;
        Object[] objArr = null;
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
        for (int i = 0; i < lst.size(); i++) {
            objArr = (Object[]) lst.get(i);
            item = new FixturesRet();
            item.setFixtureId(Integer.parseInt(objArr[0].toString()));
            item.setLeagueId(Integer.parseInt(objArr[1].toString()));
            item.setMatchDate(df.parse(objArr[2].toString()));
            item.setStatus(objArr[3].toString());
            item.setMatchday(Integer.parseInt(objArr[4].toString()));
            item.setHomeTeamName(objArr[5].toString());
            item.setAwayTeamName(objArr[6].toString());
            item.setHomeTeamGoal(Integer.parseInt(objArr[7].toString()));
            item.setAwayTeamGoal(Integer.parseInt(objArr[8].toString()));
            retList.add(item);
        }
    } catch (Exception e) {
        HelperUtil.AddErrorLog(logger, e);
    } finally {
        tran.commit();
        HibernateUtil.closeSession();
    }
    return retList;
}

---

From source file:com.football.site.db.DbHelper.java

public static LeagueTableRet GetLeagueTable(int leagueId) {
    LeagueTableRet retList = new LeagueTableRet();
    Transaction tran = null;/*from   ww w. java2  s  .  c om*/
    try {
        Session s = HibernateUtil.currentSession();
        tran = s.beginTransaction();
        Criteria cr = s.createCriteria(Leaguetable.class);
        cr.add(Restrictions.eq("leagueId", leagueId));
        Leaguetable ligTable = (Leaguetable) cr.uniqueResult();

        cr = s.createCriteria(LeagueTableRows.class);
        cr.add(Restrictions.eq("leagueTableId", ligTable.getRecordId()));
        ArrayList<LeagueTableRows> dbRowList = (ArrayList<LeagueTableRows>) cr.list();

        ArrayList<LeagueTableRowsRet> rowList = new ArrayList<>();
        LeagueTableRowsRet item = null;
        LeagueTableRows dbItem = null;
        ArrayList<LeagueTableTeamStatistics> statisticList = null;
        for (Iterator<LeagueTableRows> i = dbRowList.iterator(); i.hasNext();) {
            dbItem = i.next();
            cr = s.createCriteria(LeagueTableTeamStatistics.class);
            cr.add(Restrictions.eq("leagueTableRowId", dbItem.getRecordId()));
            statisticList = (ArrayList<LeagueTableTeamStatistics>) cr.list();
            item = new LeagueTableRowsRet();
            item.setRow(dbItem);
            if (statisticList.size() > 0) {
                if (statisticList.get(0).getStatisticOwn().equals("HOME")) {
                    item.setHomeInfo(statisticList.get(0));
                    item.setAwayInfo(statisticList.get(1));
                } else {
                    item.setHomeInfo(statisticList.get(1));
                    item.setAwayInfo(statisticList.get(0));
                }
            }
            rowList.add(item);
        }
        retList.setLeagueTable(ligTable);
        retList.setRowsList(rowList);

        SQLQuery q = s.createSQLQuery("SELECT * FROM league_table_home WHERE league_id=:leagueId");
        q.setParameter("leagueId", leagueId);
        List lst = q.list();
        Object[] objArr = null;
        ArrayList<LeagueTableRowsRet> rowListHome = new ArrayList<>();
        for (int i = 0; i < lst.size(); i++) {
            objArr = (Object[]) lst.get(i);
            item = new LeagueTableRowsRet();
            dbItem = new LeagueTableRows();
            dbItem.setPosition(String.valueOf(i + 1));
            dbItem.setLeagueTableId(Integer.parseInt(objArr[0].toString()));
            dbItem.setTeamName(objArr[2].toString());
            dbItem.setPlayedGames(Integer.parseInt(objArr[3].toString()));
            dbItem.setGoals(Integer.parseInt(objArr[4].toString()));
            dbItem.setGoalsAgainst(Integer.parseInt(objArr[5].toString()));
            dbItem.setGoalDifference(Integer.parseInt(objArr[6].toString()));
            dbItem.setWins(Integer.parseInt(objArr[7].toString()));
            dbItem.setDraws(Integer.parseInt(objArr[8].toString()));
            dbItem.setLosses(Integer.parseInt(objArr[9].toString()));
            dbItem.setPoints(Integer.parseInt(objArr[10].toString()));
            dbItem.setCrestUrl(objArr[11].toString());
            dbItem.setCrestPicture((byte[]) objArr[12]);
            dbItem.setRecordId(0);
            item.setRow(dbItem);
            rowListHome.add(item);
        }
        retList.setRowsListHome(rowListHome);

        q = s.createSQLQuery("SELECT * FROM league_table_away WHERE league_id=:leagueId");
        q.setParameter("leagueId", leagueId);
        lst = q.list();
        ArrayList<LeagueTableRowsRet> rowListAway = new ArrayList<>();
        for (int i = 0; i < lst.size(); i++) {
            objArr = (Object[]) lst.get(i);
            item = new LeagueTableRowsRet();
            dbItem = new LeagueTableRows();
            dbItem.setPosition(String.valueOf(i + 1));
            dbItem.setLeagueTableId(Integer.parseInt(objArr[0].toString()));
            dbItem.setTeamName(objArr[2].toString());
            dbItem.setPlayedGames(Integer.parseInt(objArr[3].toString()));
            dbItem.setGoals(Integer.parseInt(objArr[4].toString()));
            dbItem.setGoalsAgainst(Integer.parseInt(objArr[5].toString()));
            dbItem.setGoalDifference(Integer.parseInt(objArr[6].toString()));
            dbItem.setWins(Integer.parseInt(objArr[7].toString()));
            dbItem.setDraws(Integer.parseInt(objArr[8].toString()));
            dbItem.setLosses(Integer.parseInt(objArr[9].toString()));
            dbItem.setPoints(Integer.parseInt(objArr[10].toString()));
            dbItem.setCrestUrl(objArr[11].toString());
            dbItem.setCrestPicture((byte[]) objArr[12]);
            dbItem.setRecordId(0);
            item.setRow(dbItem);
            rowListAway.add(item);
        }
        retList.setRowsListAway(rowListAway);
    } catch (Exception e) {
        HelperUtil.AddErrorLog(logger, e);
    } finally {
        tran.commit();
        HibernateUtil.closeSession();
    }
    return retList;
}

---

From source file:com.football.site.db.DbHelper.java

public static ArrayList<Players> GetTeamPlayers(int teamId) {
    ArrayList<Players> retList = new ArrayList<>();
    Transaction tran = null;/*from ww w.  ja va 2  s.c  o  m*/
    try {
        Session s = HibernateUtil.currentSession();
        tran = s.beginTransaction();
        SQLQuery q = s.createSQLQuery("select * from players_view where teams_id = :teams_id");
        q.setParameter("teams_id", teamId);
        List lst = q.list();
        Object[] objArr = null;
        Players item = null;
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
        for (int i = 0; i < lst.size(); i++) {
            objArr = (Object[]) lst.get(i);
            item = new Players();
            item.setRecordId(Integer.parseInt(objArr[0].toString()));
            item.setTeamsId(Integer.parseInt(objArr[1].toString()));
            item.setName(objArr[2].toString());
            item.setPosition(objArr[3].toString());
            item.setJerseyNumber(Integer.parseInt(objArr[4].toString()));
            item.setDateOfBirth(df.parse(objArr[5].toString()));
            item.setNationality(objArr[6].toString());
            item.setContractUntil(df.parse(objArr[7].toString()));
            item.setMarketValue(objArr[8].toString());
            item.setMarketValueNumber(Double.parseDouble(objArr[9].toString()));
            item.setMarketValueMoneyType(objArr[10].toString());
            retList.add(item);
        }
    } catch (Exception e) {
        HelperUtil.AddErrorLog(logger, e);
    } finally {
        tran.commit();
        HibernateUtil.closeSession();
    }
    return retList;
}

---

From source file:com.football.site.db.DbHelper.java

public static ArrayList<FixturesRet> GetTeamFixture(int teamId) {
    ArrayList<FixturesRet> retList = new ArrayList<>();
    Transaction tran = null;/*from  w w w . j  ava 2  s . co m*/
    try {
        Session s = HibernateUtil.currentSession();
        tran = s.beginTransaction();
        SQLQuery q = s.createSQLQuery("SELECT * FROM fixtures_team_view WHERE team_id=:teamId");
        q.setParameter("teamId", teamId);
        List lst = q.list();
        FixturesRet item = null;
        Object[] objArr = null;
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
        for (int i = 0; i < lst.size(); i++) {
            objArr = (Object[]) lst.get(i);
            item = new FixturesRet();
            item.setFixtureId(Integer.parseInt(objArr[0].toString()));
            item.setLeagueId(Integer.parseInt(objArr[1].toString()));
            item.setMatchDate(df.parse(objArr[2].toString()));
            item.setStatus(objArr[3].toString());
            item.setMatchday(Integer.parseInt(objArr[4].toString()));
            item.setHomeTeamName(objArr[5].toString());
            item.setAwayTeamName(objArr[6].toString());
            item.setHomeTeamGoal(Integer.parseInt(objArr[7].toString()));
            item.setAwayTeamGoal(Integer.parseInt(objArr[8].toString()));
            retList.add(item);
        }
    } catch (Exception e) {
        HelperUtil.AddErrorLog(logger, e);
    } finally {
        tran.commit();
        HibernateUtil.closeSession();
    }
    return retList;
}

---

From source file:com.football.site.db.DbHelper.java

public static boolean IsLeagueTemasInsertBefore(int leagueId, int year) {
    boolean retVal = false;
    Transaction tran = null;//from   ww w  .  j  a v a 2  s  .  co  m
    try {
        Session s = HibernateUtil.currentSession();
        tran = s.beginTransaction();
        SQLQuery q = s
                .createSQLQuery("SELECT * FROM league_teams_info WHERE league_id=:leagueId AND yearyer=:year");
        q.setParameter("teamId", leagueId);
        q.setParameter("year", year);
        List lst = q.list();
        retVal = lst.size() > 0;
    } catch (Exception e) {
        HelperUtil.AddErrorLog(logger, e);
    } finally {
        if (null != tran) {
            tran.commit();
        }
        HibernateUtil.closeSession();
    }
    return retVal;
}

---

From source file:com.formkiq.core.dao.QueueDaoImpl.java

License:Apache License

@SuppressWarnings("resource")
@Override//from w  ww .  j  a v a  2  s .c om
public int delete(final Map<String, String> messageValues) {

    StringBuilder sb = new StringBuilder("delete from queue_messages where ");

    List<String> keys = new ArrayList<>(messageValues.keySet());

    for (int i = 0; i < keys.size(); i++) {

        if (i > 0) {
            sb.append(" and ");
        }

        String key = keys.get(i);
        sb.append(" message #>> '{" + key + "}'=:value" + i);
    }

    Session session = getEntityManager().unwrap(Session.class);

    SQLQuery q = session.createSQLQuery(sb.toString());

    for (int i = 0; i < keys.size(); i++) {
        String key = keys.get(i);
        String value = messageValues.get(key);
        q.setParameter("value" + i, value);
    }

    return q.executeUpdate();
}

---

From source file:com.g3.framework.orm.SimpleHibernateDao.java

License:Apache License

public SQLQuery createSQLQuery(String sql, Map<String, Object> parameters) {
    sql = sql.toUpperCase();//from   w  w w.  j  a va 2s .  com
    Assert.hasText(sql, "queryString?");
    SQLQuery query = getSession().createSQLQuery(sql);
    if (parameters != null) {
        for (String key : parameters.keySet()) {
            Object value = parameters.get(key);
            //System.out.println(key + "<::>" + value);
            if (value instanceof Collection) {
                query.setParameterList(key.toUpperCase(), (Collection) value);
            } else if (value instanceof Integer) {
                query.setInteger(key.toUpperCase(), ((Integer) value).intValue());
            } else {
                query.setParameter(key.toUpperCase(), value);
            }
        }
    }
    return query;
}

---

From source file:com.globalsight.calendar.CalendarManagerLocal.java

License:Apache License

/**
 * @see CalendarManager.removeScheduledActivities(long);
 *//*from   ww w  . j ava 2 s  .co m*/
public void removeScheduledActivities(long p_taskId) throws RemoteException, CalendarManagerException {
    String ownerId = "";

    Session session = HibernateUtil.getSession();

    try {
        String sql = "select r.* from RESERVED_TIME r, USER_CALENDAR u "
                + " where r.TASK_ID = :TASK_ID and r.USER_CALENDAR_ID = u.ID ";

        SQLQuery query = session.createSQLQuery(sql).addEntity(ReservedTime.class);
        query.setParameter("TASK_ID", new Long(p_taskId));

        Object[] rts = query.list().toArray();

        HashMap map = new HashMap(4);

        for (int i = 0; i < rts.length; i++) {
            ReservedTime rt = (ReservedTime) rts[i];

            Long calId = rt.getUserFluxCalendar().getIdAsLong();
            UserFluxCalendar cal = (UserFluxCalendar) map.get(calId);
            if (cal == null) {
                cal = rt.getUserFluxCalendar();
                map.put(calId, cal);
            }

            ownerId = cal.getOwnerUserId();

            cal.getCollectionByType(rt.getType()).remove(rt);

            HibernateUtil.saveOrUpdate(cal);
        }

        HibernateUtil.delete(Arrays.asList(rts));
    } catch (Exception e) {
        String[] args = { ownerId, String.valueOf(p_taskId) };
        throw new CalendarManagerException(CalendarManagerException.MSG_REMOVE_RESERVED_TIME_FAILED, args, e);
    }
}

---

From source file:com.globalsight.calendar.CalendarManagerLocal.java

License:Apache License

private Collection getAllCalendarsByHolidayId(Session session, long p_holidayId)
        throws CalendarManagerException {
    try {//www  .  ja  va 2s .com
        String sql = " select c.* from CALENDAR c, CALENDAR_HOLIDAY ch "
                + " where ch.HOLIDAY_ID = ? and c.ID = ch.CALENDAR_ID ";

        String currentCompanyId = CompanyThreadLocal.getInstance().getValue();
        if (CompanyWrapper.SUPER_COMPANY_ID.equals(currentCompanyId)) {
            SQLQuery query = session.createSQLQuery(sql).addEntity(FluxCalendar.class);
            query.setParameter(0, new Long(p_holidayId));
            return query.list();
        } else {
            sql += " and c.COMPANY_ID = ? ";
            SQLQuery query = session.createSQLQuery(sql).addEntity(FluxCalendar.class);
            query.setParameter(0, new Long(p_holidayId));
            query.setParameter(1, Long.parseLong(currentCompanyId));
            return query.list();
        }
    } catch (Exception e) {
        String[] arg = { String.valueOf(p_holidayId) };
        throw new CalendarManagerException(CalendarManagerException.MSG_GET_CALENDARS_BY_HOLIDAY_ID_FAILED, arg,
                null);
    }
}

---

From source file:com.globalsight.everest.taskmanager.TaskManagerLocal.java

License:Apache License

/**
 * @see TaskManager.removeUserAsTaskAcceptor(String)
 *//*from ww  w  .  j  ava  2s  . co m*/
public void removeUserAsTaskAcceptor(String p_userId) throws RemoteException, TaskException {
    // first reassign the user
    try {
        ServerProxy.getWorkflowServer().reassignUserActivitiesToPm(p_userId);
    } catch (Exception e) {
        CATEGORY.error(
                "Failed to reassign the user activities of user " + p_userId + " to the Project Manager.", e);
        return;
    }

    // now reset the acceptor, accepted_date, and task state
    // for all accepted task by this user
    Session session = HibernateUtil.getSession();
    Transaction tx = session.beginTransaction();
    try {
        SQLQuery query = session.createSQLQuery(TaskDescriptorModifier.ACCEPTED_TASK_BY_USER_ID_SQL);
        query.addEntity(TaskImpl.class);
        query.setParameter(TaskDescriptorModifier.TASK_USER_ID_ARG, p_userId);
        Collection col = query.list();

        if (col == null) {
            return;
        }

        Object[] tasks = col.toArray();

        for (int i = 0, size = tasks.length; i < size; i++) {
            Task task = (Task) tasks[i];
            task.setAcceptor(null);
            task.setAcceptedDate(null);
            task.setState(Task.STATE_ACTIVE);
            session.update(task);
        }
        tx.commit();
    } catch (Exception e) {
        tx.rollback();
        CATEGORY.error("Failed to reset task acceptor and state for the removed user " + p_userId + ".", e);
        return;
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »