Example usage for org.hibernate Session createSQLQuery

Introduction

In this page you can find the example usage for org.hibernate Session createSQLQuery.

Prototype

@Override
    NativeQuery createSQLQuery(String queryString);

Source Link

Usage

From source file:com.femsa.mkt.dao.CobHistoricoDao.java

public boolean boorrarTabla() {
    HibernateUtil hibernateUtil = new HibernateUtil();
    SessionFactory sessionFactory = hibernateUtil.getSessionFactory();
    Session session = sessionFactory.openSession();
    boolean flagOk = true;
    Query query = session.createSQLQuery("TRUNCATE TABLE MKT_COB_ST_HIS_AGRUPACION");
    try {/*from w w w.  ja v a  2  s .  c om*/
        session.beginTransaction();
        query.executeUpdate();
        session.getTransaction().commit();
    } catch (Exception e) {
        if (session.getTransaction().isActive()) {
            session.getTransaction().rollback();
        }
        flagOk = false;
    } finally {
        session.flush();
        session.clear();
        session.close();
        hibernateUtil.closeSessionFactory();
    }
    return flagOk;

}

---

From source file:com.femsa.mkt.dao.CobHistoricoDao.java

public boolean EjecutaEtl() {
    HibernateUtil hibernateUtil = new HibernateUtil();
    SessionFactory sessionFactory = hibernateUtil.getSessionFactory();
    Session session = sessionFactory.openSession();
    boolean flagOk = true;
    Query query = session.createSQLQuery("TRUNCATE TABLE MKT_COB_ST_HIS_AGRUPACION");
    try {// ww w .j a v a 2 s . c  om
        session.beginTransaction();
        query.executeUpdate();
        session.getTransaction().commit();
    } catch (Exception e) {
        if (session.getTransaction().isActive()) {
            session.getTransaction().rollback();
        }
        flagOk = false;
    } finally {
        session.flush();
        session.clear();
        session.close();
        hibernateUtil.closeSessionFactory();
    }
    return flagOk;

}

---

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

public static boolean ExecuteScript(String sql) {
    boolean retVal = false;
    Transaction tran = null;/*  www.ja v  a 2  s .c om*/
    try {
        Session s = HibernateUtil.currentSession();
        tran = s.beginTransaction();
        s.createSQLQuery(sql).executeUpdate();
        tran.commit();
        retVal = true;
    } catch (Exception e) {
        if (tran != null) {
            tran.rollback();
        }
        HelperUtil.AddErrorLog(logger, e);
    } finally {
        HibernateUtil.closeSession();
    }
    return retVal;
}

---

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

public static boolean TruncateAllTables() {
    boolean retVal = false;
    Transaction tran = null;/*from   www.j a v a2  s. c o  m*/
    try {
        Session s = HibernateUtil.currentSession();
        tran = s.beginTransaction();
        s.createSQLQuery("truncate table fixtures_team").executeUpdate();
        s.createSQLQuery("truncate table fixtures_team_match_result").executeUpdate();
        s.createSQLQuery("truncate table fixtures").executeUpdate();
        s.createSQLQuery("truncate table fixtures_match_result").executeUpdate();
        s.createSQLQuery("truncate table league_table_rows").executeUpdate();
        s.createSQLQuery("truncate table league_table_team_statistics").executeUpdate();
        s.createSQLQuery("truncate table leagues").executeUpdate();
        s.createSQLQuery("truncate table leaguetable").executeUpdate();
        s.createSQLQuery("truncate table teams").executeUpdate();
        s.createSQLQuery("truncate table players").executeUpdate();
        tran.commit();
        retVal = true;
    } catch (Exception e) {
        if (tran != null) {
            tran.rollback();
        }
        HelperUtil.AddErrorLog(logger, e);
    } finally {
        HibernateUtil.closeSession();
    }
    return retVal;

}

---

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

public static ArrayList<FixturesRet> GetLeagueFixture(int leagueId) {
    ArrayList<FixturesRet> retList = new ArrayList<>();
    Transaction tran = null;/*from  ww  w .java  2 s.  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;/*  w ww. ja v  a 2s . c  o m*/
    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 w ww  .  ja  v  a2  s .  co  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;/*  w w  w.ja  v a  2s  .  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;/* w  w w  .  j  ava  2s .  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.ClientDaoImpl.java

License:Apache License

@SuppressWarnings("resource")
@Override//from w  ww.j av  a 2s  .  c om
public int clientCount() {
    String sql = "select count(*) as count from oauth_client_details";
    Session session = getEntityManager().unwrap(Session.class);

    Integer count = (Integer) session.createSQLQuery(sql).addScalar("count", IntegerType.INSTANCE)
            .uniqueResult();
    return count.intValue();
}

---

• «
• 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
• »