Example usage for org.hibernate Session createSQLQuery

List of usage examples for org.hibernate Session createSQLQuery

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. Session
  6. 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.court.controller.GuarantorsFxmlController.java

private List<Member> getAvailableGuarantors() {
    Session session = HibernateUtil.getSessionFactory().openSession();
    SQLQuery query = session
            .createSQLQuery("SELECT\n" + "    m.member_id AS memberId,\n" + "    m.full_name AS fullName,\n"
                    + "    wb.branch_name AS workingOffice,\n" + "    pb.branch_name AS payingOffice,\n"
                    + "    ml.member_loan_code AS loanCode,\n" + "    ml.granted_date AS grantedDate,\n"
                    + "    ml.loan_amount AS loanAmount,\n" + "    ml.loan_interest AS loanInterest,\n"
                    + "    ml.interest_per AS interestPer,\n" + "    ml.interest_method AS interMethod,\n"
                    + "    ml.loan_duration AS loanDuration,\n" + "    ml.duration_per AS lDurationPer,\n"
                    + "    ml.is_complete AS loanComplete\n" + "FROM\n" + "    court_loan.branch wb\n"
                    + "INNER JOIN\n" + "    court_loan.member m\n" + "ON\n" + "    (\n"
                    + "        wb.id = m.branch_id)\n" + "INNER JOIN\n" + "    court_loan.branch pb\n" + "ON\n"
                    + "    (\n" + "        m.pay_office_id = pb.id)\n" + "LEFT OUTER JOIN\n"
                    + "    court_loan.member_loan ml\n" + "ON\n" + "    (\n" + "        m.id = ml.member_id) ;")
            .addScalar("memberId", new StringType()).addScalar("fullName", new StringType())
            .addScalar("workingOffice", new StringType()).addScalar("payingOffice", new StringType())
            .addScalar("loanCode", new StringType()).addScalar("grantedDate", new DateType())
            .addScalar("loanAmount", new DoubleType()).addScalar("loanInterest", new DoubleType())
            .addScalar("interestPer", new StringType()).addScalar("interMethod", new StringType())
            .addScalar("loanDuration", new IntegerType()).addScalar("lDurationPer", new StringType())
            .addScalar("loanComplete", new BooleanType());

    List<Object[]> rows = query.list();
    List<Member> list = new ArrayList<>();
    for (Object[] row : rows) {
        Member m = new Member();
        m.setMemberId(row[0].toString());
        m.setFullName(row[1].toString());
        m.setBranch(new Branch(row[2].toString()));
        m.setPayOffice(new Branch(row[3].toString()));
        list.add(m);// ww  w  .j  a va2s  .  c  om
    }

    session.close();
    return list;
}

From source file:com.court.controller.MemberfxmlController.java

private boolean updateKotaLeftOfLoans(Session s, List<Integer> mbrLoan_codes) {

    Query query = s.createSQLQuery("UPDATE member_loan ml SET ml.kota_left= 0.0 WHERE ml.id in (:ml_ids) ;")
            .setParameterList("ml_ids", mbrLoan_codes);
    query.executeUpdate();// w  w  w  . j av  a2  s  . c  om

    return true;
}

From source file:com.court.handler.FxUtilsHandler.java

public static boolean hasPreviousSubscriptions(int memeberId) {
    Session session = HibernateUtil.getSessionFactory().openSession();
    Query query = session.createSQLQuery("SELECT\n" + "    m.member_id\n" + "FROM\n"
            + "    court_loan.member m\n" + "INNER JOIN\n" + "    court_loan.member_subscriptions ms\n" + "ON\n"
            + "    (\n" + "        m.id = ms.member_id)\n" + "INNER JOIN\n"
            + "    court_loan.subscription_pay sp\n" + "ON\n" + "    (\n"
            + "        ms.id = sp.member_subscriptions_id)\n" + "WHERE\n" + "    m.id = :mid ;");
    query.setParameter("mid", memeberId);
    boolean flag = query.list().isEmpty();
    session.close();//w  w  w . j  a  v  a 2s  . c o m
    return flag;
}

From source file:com.court.handler.FxUtilsHandler.java

public static boolean hasPreviousSubscriptions(int memeberId, Session session) {
    Query query = session.createSQLQuery("SELECT\n" + "    m.member_id\n" + "FROM\n"
            + "    court_loan.member m\n" + "INNER JOIN\n" + "    court_loan.member_subscriptions ms\n" + "ON\n"
            + "    (\n" + "        m.id = ms.member_id)\n" + "INNER JOIN\n"
            + "    court_loan.subscription_pay sp\n" + "ON\n" + "    (\n"
            + "        ms.id = sp.member_subscriptions_id)\n" + "WHERE\n" + "    m.id = :mid ;");
    query.setParameter("mid", memeberId);
    boolean flag = query.list().isEmpty();
    return flag;/*from  w  w w .j a  v a  2 s .c  o  m*/
}

From source file:com.cowsbulls.src.database.implementation.GameDAO.java

@Override
public List<Game> GetGames() {

    List<Game> games = new ArrayList<Game>();
    Session session = factory.openSession();
    try {//from   ww  w  . jav  a  2 s. c  o m
        session.beginTransaction();
        List<Integer> rows = session.createSQLQuery("select id from game").list();
        for (Integer id : rows) {
            games.add(GetGame(id));
        }

    } catch (org.hibernate.HibernateException he) {
        if (session.getTransaction().isActive())
            session.getTransaction().rollback();
    } finally {
        session.close();
    }
    return games;
}

From source file:com.cowsbulls.src.database.implementation.GameDAO.java

@Override
public List<Game> GetGamesByStatus(Integer status) {
    List<Game> games = new ArrayList<Game>();
    Session session = factory.openSession();
    try {/*w w  w. j  a  v a 2 s. c o  m*/
        session.beginTransaction();
        List<Integer> rows = session.createSQLQuery("select id from game where game_status = :status")
                .setParameter("status", status).list();
        for (Integer id : rows) {
            games.add(GetGame(id));
        }

    } catch (org.hibernate.HibernateException he) {
        if (session.getTransaction().isActive())
            session.getTransaction().rollback();
    } finally {
        session.close();
    }
    return games;
}

From source file:com.cowsbulls.src.database.implementation.PlayerDAO.java

@Override
public List<Player> GetPlayers() throws CowsBullsException {

    List<Player> players = new ArrayList<Player>();

    Session session = factory.openSession();
    try {/*from   w ww. j  a v  a 2s . c  om*/
        session.beginTransaction();
        List<Integer> rows = session.createSQLQuery("select id from player").list();
        session.getTransaction().commit();
        for (Integer id : rows) {
            players.add(GetPlayer(id));
        }
    } catch (org.hibernate.HibernateException he) {
        if (session.getTransaction().isActive())
            session.getTransaction().rollback();
        throw new CowsBullsException(CallbackConstants.BAD, he.getMessage());
    } finally {
        session.close();
    }
    return players;
}

From source file:com.cowsbulls.src.database.implementation.PlayerDAO.java

@Override
public List<Player> GetPlayers(int game_id) throws CowsBullsException {
    List<Player> players = new ArrayList<Player>();
    Session session = factory.openSession();
    try {/*from   w  ww .j  a v a 2 s.  c o  m*/
        session.beginTransaction();
        List<Integer> rows = session.createSQLQuery("select id from player where game_id = :game_id")
                .setParameter("game_id", game_id).list();
        session.getTransaction().commit();
        for (Integer id : rows) {
            players.add(GetPlayer(id));
        }
    } catch (CowsBullsException cbEx) {
        throw cbEx;
    } catch (org.hibernate.HibernateException he) {
        if (session.getTransaction().isActive())
            session.getTransaction().rollback();
        throw new CowsBullsException(CallbackConstants.BAD, he.getMessage());
    } finally {
        session.close();
    }
    return players;
}

From source file:com.cowsbulls.src.database.implementation.PlayerDAO.java

@Override
public List<Player> GetSearchingPlayers() throws CowsBullsException {
    List<Player> players = new ArrayList<Player>();
    Session session = factory.openSession();
    try {/* ww w .j  a v a2s  . c  o m*/
        session.beginTransaction();
        List<Integer> rows = session.createSQLQuery("select id from player where status = :status limit 1000")
                .setParameter("status", SQLConstants.PLAYER_STATUS_SEARCHING).list();
        session.getTransaction().commit();
        for (Integer id : rows) {
            players.add(GetPlayer(id));
        }
    } catch (CowsBullsException cbEx) {
        throw cbEx;
    } catch (org.hibernate.HibernateException he) {
        if (session.getTransaction().isActive())
            session.getTransaction().rollback();
        throw new CowsBullsException(CallbackConstants.BAD, he.getMessage());
    } finally {
        session.close();
    }
    return players;
}

From source file:com.creativity.repository.LancamentosFinanceiros.java

public BigDecimal valorAbertoTotal() {
    Session session = this.manager.unwrap(Session.class);
    org.hibernate.Query q = session
            .createSQLQuery("select sum(valor) from financeiro where statusFinanceiro = 'ABERTO'");
    return (BigDecimal) q.uniqueResult();
}