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.creativity.repository.LancamentosFinanceiros.java

public BigDecimal valorPagoTotal() {
    Session session = this.manager.unwrap(Session.class);
    org.hibernate.Query q = session.createSQLQuery("select sum(valorTotalRecebido) from financeiro");
    return (BigDecimal) q.uniqueResult();
}

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

public BigDecimal valorPagoDiaTotal() {
    Session session = this.manager.unwrap(Session.class);
    org.hibernate.Query q = session.createSQLQuery(
            "SELECT sum(valorPagamento) from baixafinanceiro where dataPagamento = current_date();");
    return (BigDecimal) q.uniqueResult();
}

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

public BigDecimal valorPagoMesTotal() {
    Session session = this.manager.unwrap(Session.class);
    org.hibernate.Query q = session.createSQLQuery(
            "SELECT sum(valorPagamento) from baixafinanceiro where month(dataPagamento) = month( current_date());");
    return (BigDecimal) q.uniqueResult();
}

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

public BigDecimal valorPagoAnoTotal() {
    Session session = this.manager.unwrap(Session.class);
    org.hibernate.Query q = session.createSQLQuery(
            "SELECT sum(valorPagamento) from baixafinanceiro where year(dataPagamento) = year( current_date());");
    return (BigDecimal) q.uniqueResult();
}

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

public BigDecimal valorSaldoTotal() {
    Session session = this.manager.unwrap(Session.class);
    org.hibernate.Query q = session.createSQLQuery("select   \n"
            + "((select sum(valor) from financeiro where statusFinanceiro = 'ABERTO')\n"
            + "- (select sum(valorTotalRecebido) from financeiro where statusFinanceiro = 'PAGO')) from financeiro\n"
            + "group by valor");
    return (BigDecimal) q.uniqueResult();
}

From source file:com.datacentre.model.dao.impl.CardDaoImpl.java

@Override
public void add(RegistrationCard card) {
    card.setDateRegistration(Calendar.getInstance().getTime());
    Session session = sessionFactory.openSession();

    ValidatorFactory vf = Validation.buildDefaultValidatorFactory();
    Validator validator = vf.getValidator();

    try {//w w w .j av  a 2s.c  om
        validate(card, validator);
    } catch (ValidationException validException) {
        return;
    }

    session.beginTransaction();
    SQLQuery insertQuery = session.createSQLQuery(
            "" + "INSERT INTO registration_card(mobile,full_name,age,date_registration)VALUES(?,?,?,?)");
    insertQuery.setParameter(0, card.getMobile());
    insertQuery.setParameter(1, card.getFullName());
    insertQuery.setParameter(2, card.getAge());
    insertQuery.setParameter(3, card.getDateRegistration());
    insertQuery.executeUpdate();
    session.getTransaction().commit();
}

From source file:com.datacentre.model.dao.impl.CardDaoImpl.java

@Override
public void edit(RegistrationCard card) {
    Session session = sessionFactory.openSession();
    session.beginTransaction();/*from   w w w  .  j  av a 2  s.  c  o m*/
    SQLQuery updateQuery = session
            .createSQLQuery("" + "UPDATE registration_card SET full_name = '" + card.getFullName() + "'"
                    + ", age = " + card.getAge() + " WHERE mobile = '" + card.getMobile() + "'");
    updateQuery.executeUpdate();
    session.getTransaction().commit();
}

From source file:com.datacentre.model.dao.impl.CardDaoImpl.java

@Override
public void delete(RegistrationCard card) {
    Session session = sessionFactory.openSession();
    session.beginTransaction();/*from   ww  w . j  a v  a 2s.c  o m*/
    SQLQuery deleteQuery = session
            .createSQLQuery("" + "DELETE FROM registration_card WHERE mobile = '" + card.getMobile() + "'");
    deleteQuery.executeUpdate();
    session.getTransaction().commit();
}

From source file:com.dell.asm.asmcore.asmmanager.db.DeviceGroupDAO.java

License:Open Source License

/**
 * Helper method for deleting Group Users Association
 * // w  ww  .j  a  va2s.co m
 * @param userSeqIds - user's id to be deleted
 * 
 * @throws AsmManagerCheckedException
 *
 */
// If there is a change in groups_users table structure, then this method should be updated accordingly
public void deleteGroupUsersAssociation(Set<Long> userSeqIds) throws AsmManagerCheckedException {

    if (userSeqIds == null || userSeqIds.size() <= 0)
        return;

    // Initialize locals.
    Session session = null;
    Transaction tx = null;
    List<BigInteger> userIds = new ArrayList<>();

    for (Long id : userSeqIds) {
        userIds.add(new BigInteger(String.valueOf(id)));
    }

    // Save the job history in the db.
    try {
        session = _dao._database.getNewSession();
        tx = session.beginTransaction();

        // delete the Group Users Association.
        String sql = "delete from groups_users WHERE user_seq_id IN (:userSeqIds)";
        Query query = session.createSQLQuery(sql);
        query.setParameterList("userSeqIds", userIds);
        query.executeUpdate();
        tx.commit();

    } catch (Exception e) {
        logger.warn("Caught exception during deleting Group Users association: " + e);
        try {
            if (tx != null) {
                tx.rollback();
            }
        } catch (Exception ex) {
            logger.warn("Unable to rollback transaction during deleting Group Users association: " + ex);
        }

    } finally {
        try {
            if (session != null) {
                session.close();
            }
        } catch (Exception ex) {
            logger.warn("Unable to close session during deleting Group Users association: " + ex);
        }
    }
}

From source file:com.dell.asm.asmcore.asmmanager.db.DeviceGroupDAO.java

License:Open Source License

public List<BriefServerInfo> getAccessiblePoolServers(final long userId, final String poolId) {
    try {/*from www. j  av  a  2  s.c o  m*/
        return _dao.doWithSession(new BaseDAO.CallableWithSession<List<BriefServerInfo>>() {
            @Override
            public List<BriefServerInfo> run(Session session) {
                Map<String, Object> keyValues = new HashMap<>();

                SQLQuery query;
                if (ServiceTemplateSettingIDs.SERVICE_TEMPLATE_SERVER_POOL_GLOBAL_ID.equals(poolId)) {
                    query = session
                            .createSQLQuery(GLOBAL_POOL_QUERY + POOL_GROUP_BY_CLAUSE + POOL_ORDER_BY_CLAUSE);
                } else if (ServiceTemplateSettingIDs.SERVICE_TEMPLATE_SERVER_POOL_ALL_ID.equals(poolId)
                        || poolId == null) {

                    // system user has access to all servers in the pool
                    if (userId == DBInit.SYSTEM_USER_ID) {
                        query = session
                                .createSQLQuery(GLOBAL_POOL_QUERY + " UNION " + SPECIFIC_POOL_SYSTEM_USER_QUERY
                                        + POOL_GROUP_BY_CLAUSE + POOL_ORDER_BY_CLAUSE);
                    } else {
                        query = session.createSQLQuery(GLOBAL_POOL_QUERY + " UNION " + SPECIFIC_POOL_QUERY
                                + POOL_GROUP_BY_CLAUSE + POOL_ORDER_BY_CLAUSE);
                        query.setParameter("userId", userId);
                    }
                } else {
                    StringBuilder sql;
                    if (userId == DBInit.SYSTEM_USER_ID) {
                        sql = new StringBuilder(SPECIFIC_POOL_SYSTEM_USER_QUERY + " AND g.seq_id = :poolId");
                    } else {
                        sql = new StringBuilder(SPECIFIC_POOL_QUERY + " AND g.seq_id = :poolId");
                    }
                    keyValues.put("poolId", Long.valueOf(poolId));

                    sql.append(POOL_GROUP_BY_CLAUSE);
                    sql.append(POOL_ORDER_BY_CLAUSE);

                    query = session.createSQLQuery(sql.toString());
                    if (userId != DBInit.SYSTEM_USER_ID)
                        query.setParameter("userId", userId);
                }

                for (Map.Entry<String, Object> entry : keyValues.entrySet()) {
                    query.setParameter(entry.getKey(), entry.getValue());
                }

                return buildBriefServerInfoList(query.list());
            }

            @Override
            public List<BriefServerInfo> failed(SQLException e) throws SQLException {
                logger.error("Failed to look up servers from pool " + poolId + " for user " + userId, e);
                throw e;
            }
        });
    } catch (SQLException e) {
        throw new AsmManagerRuntimeException(e);
    }
}