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.dell.asm.asmcore.asmmanager.db.DeviceGroupDAO.java

License:Open Source License

public List<BriefServerInfo> getAccessibleServers(final long userId, final List<String> serverRefIds) {
    try {/*  ww w  .  ja va  2  s .com*/
        return _dao.doWithSession(new BaseDAO.CallableWithSession<List<BriefServerInfo>>() {
            private StringBuilder appendServerRefIds(StringBuilder sql, Map<String, Object> keyValues) {
                if (serverRefIds != null && serverRefIds.size() > 0) {
                    int i = 0;
                    sql.append(" AND i.ref_id IN (");
                    for (String serverRefId : serverRefIds) {
                        String name = "ref" + i++;
                        sql.append(':').append(name).append(", ");
                        keyValues.put(name, serverRefId);
                    }
                    sql.deleteCharAt(sql.length() - 1);
                    sql.deleteCharAt(sql.length() - 1);
                    sql.append(")");
                }
                return sql;
            }

            @Override
            public List<BriefServerInfo> run(Session session) {
                Map<String, Object> keyValues = new HashMap<>();

                StringBuilder sql = new StringBuilder(GLOBAL_POOL_QUERY);
                if (userId == DBInit.SYSTEM_USER_ID) {
                    appendServerRefIds(sql, keyValues).append(" UNION ")
                            .append(SPECIFIC_POOL_SYSTEM_USER_QUERY);
                } else {
                    appendServerRefIds(sql, keyValues).append(" UNION ").append(SPECIFIC_POOL_QUERY);
                }
                appendServerRefIds(sql, keyValues).append(POOL_GROUP_BY_CLAUSE).append(POOL_ORDER_BY_CLAUSE);
                SQLQuery 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 " + serverRefIds + " for user " + userId, e);
                throw e;
            }
        });
    } catch (SQLException e) {
        throw new AsmManagerRuntimeException(e);
    }
}

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

License:Open Source License

/**
 * Returns the number of servers available in the pool.
 * //from  ww w.  j  a va  2s . c om
 * @param poolId the id of the pool whose server count will be returned.
 * @return the number of servers available in the pool.
 */
public int getNumberOfServersInPool(final String poolId) {
    int numberOfServersInPool = 0;
    if (poolId != null && poolId.trim().length() > 0) {
        // Initialize locals.
        Session session = null;
        Transaction tx = null;
        try {
            session = _dao._database.getNewSession();
            tx = session.beginTransaction();
            SQLQuery query = session.createSQLQuery(POOL_SERVERS_COUNT);
            query.setParameter("poolId", Long.valueOf(poolId));
            numberOfServersInPool = ((BigInteger) query.uniqueResult()).intValue();
            tx.commit();
        } catch (Exception e) {
            logger.warn("Caught exception during getNumberOfServersInPool: " + e);
            try {
                if (tx != null) {
                    tx.rollback();
                }
            } catch (Exception ex) {
                logger.warn("Unable to rollback transaction during getNumberOfServersInPool: " + ex);
            }
        } finally {
            try {
                if (session != null) {
                    session.close();
                }
            } catch (Exception ex) {
                logger.warn("Unable to close session during getNumberOfServersInPool: " + ex);
            }
        }
    }
    return numberOfServersInPool;
}

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

License:Open Source License

@SuppressWarnings("unchecked")
// NOTE[fcarta] - Its noted that concatenating a sql string leaves the query vulnerable to SQL
// injection attacks. In this case this method is private and will only be called with trusted
// UUIDs.//  ww  w.  ja v  a  2  s .c om
private List<String> getRefIdsOfDevicesByDeploymentIds(final List<String> deploymentIds) {
    final String deploymentIdsJoined = "'" + StringUtils.join(deploymentIds, "','") + "'";
    logger.info("Retrieving ref Ids of Devices for the given deploymentIds : " + deploymentIdsJoined);
    Session session = null;
    Transaction tx = null;

    List<String> refIds = ListUtils.EMPTY_LIST;
    try {
        session = _dao._database.getNewSession();
        tx = session.beginTransaction();
        final String sql =
                //"select device_id from deployment_to_device_map where deployment_id in ( :deploymentIds )";
                "select device_id from deployment_to_device_map where deployment_id in (" + deploymentIdsJoined
                        + ")";
        SQLQuery query = session.createSQLQuery(sql);
        //query.setParameter("deploymentIds", deploymentIdsJoined);
        refIds = query.list();
        tx.commit();
    } catch (Exception e) {
        logger.warn("Caught exception getting refIds of deployment ids " + deploymentIdsJoined, e);
        throw new AsmManagerInternalErrorException("Get Ref Ids by Deployment Ids", "DeviceInventoryDAO", e);
    } finally {
        try {
            if (session != null) {
                session.close();
            }
        } catch (Exception ex) {
            logger.warn("Unable to close session during ref Id retrieval: " + ex);
        }
    }

    return refIds;
}

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

License:Open Source License

@SuppressWarnings("unchecked")
private List<String> getRefIdsOfDevicesByGroupIds(final List<String> ids) {
    final String idsJoined = "'" + StringUtils.join(ids, "','") + "'";
    Session session = null;
    Transaction tx;/*from   w ww  .ja v  a 2  s .c om*/

    List<String> refIds = ListUtils.EMPTY_LIST;
    try {
        session = _dao._database.getNewSession();
        tx = session.beginTransaction();
        final String sql;
        if (idsJoined.equals("'" + ServiceTemplateSettingIDs.SERVICE_TEMPLATE_SERVER_POOL_GLOBAL_ID + "'")) {
            sql = "select distinct devices_inventory_seq_id from groups_device_inventory";
        } else {
            sql = "select devices_inventory_seq_id from groups_device_inventory where groups_seq_id in ("
                    + idsJoined + ")";
        }
        SQLQuery query = session.createSQLQuery(sql);
        refIds = query.list();
        tx.commit();
    } catch (Exception e) {
        logger.warn("Caught exception getting refIds of groups ids " + idsJoined, e);
        throw new AsmManagerInternalErrorException("Get Ref Ids by Group Ids", "DeviceInventoryDAO", e);
    } finally {
        try {
            if (session != null) {
                session.close();
            }
        } catch (Exception ex) {
            logger.warn("Unable to close session during ref Id retrieval: " + ex);
        }
    }

    return refIds;
}

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

License:Open Source License

/**
 * Sets all of the isDefault values for the entire FirmwareRepositoryEntity table. 
 *///from w  w w  .java  2 s .co  m
public void updateAllIsDefault(boolean isDefault) {
    Session session = null;
    Transaction tx = null;

    try {
        session = dao.getNewSession();
        tx = session.beginTransaction();

        Query query = session.createSQLQuery("update firmware_repository set is_default = :isDefault");
        query.setParameter("isDefault", isDefault);

        int result = query.executeUpdate();

        tx.commit();
    } catch (Exception e) {
        logger.warn("Caught exception during updateAllIsDefault: " + e);
        try {
            if (tx != null) {
                tx.rollback();
            }
        } catch (Exception ex) {
            logger.warn("Unable to rollback transaction during updateAllIsDefault: " + ex);
        }
        throw new AsmManagerInternalErrorException("updateAllIsDefault", "FirmwareRepositoryDAO", e);
    } finally {
        dao.cleanupSession(session, "updateAllIsDefault");
    }
}

From source file:com.demo.impl.CausasDevolucionConceptoImpl.java

@Override
public List<CausasDevolucionConcepto> getCausaDevoConcepPorId(int idCausaDevolucion) {
    try {//from w  w  w  .  java 2 s.  c om
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction t = session.beginTransaction();
        List<CausasDevolucionConcepto> lista = session.createSQLQuery(
                "select * from causas_devolucion_concepto c where c.causas_devolucion_idcausas_devolucion = "
                        + idCausaDevolucion + ";")
                .addEntity(CausasDevolucionConcepto.class).list();
        t.commit();

        if (session.isOpen()) {
            session.close();
        }
        return lista;
    } catch (HibernateException he) {
        LogSistema.guardarlog(this.getClass().getName()
                + " Method: getCausaDevoConcepPorId, Excepcion HibernateException: " + he.getMessage());
        System.out.println("Error al traer las conceptos de devolucion");
        List<CausasDevolucionConcepto> lista = new ArrayList<>();
        return lista;
    }
}

From source file:com.demo.impl.CausasDevolucionConceptoImpl.java

@Override
public List<CausasDevolucionConcepto> getCausaDevoConcepEntreId(int id1, int id2) {
    try {/*from w  ww . ja  va  2 s . com*/
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction t = session.beginTransaction();
        List<CausasDevolucionConcepto> lista = session.createSQLQuery(
                "select * from causas_devolucion_concepto c where c.idcausas_devolucion_concepto >= " + id1
                        + " and c.idcausas_devolucion_concepto <= " + id2
                        + " union select * from causas_devolucion_concepto c where c.idcausas_devolucion_concepto =47")
                .addEntity(CausasDevolucionConcepto.class).list();
        t.commit();

        if (session.isOpen()) {
            session.close();
        }
        return lista;
    } catch (HibernateException he) {
        LogSistema.guardarlog(this.getClass().getName()
                + " Method: getCausaDevoConcepEntreId, Excepcion HibernateException: " + he.getMessage());
        System.out.println("Error al traer las conceptos de devolucion");
        List<CausasDevolucionConcepto> lista = new ArrayList<>();
        return lista;
    }
}

From source file:com.demo.impl.CausasDevolucionImpl.java

@Override
public List<CausasDevolucion> getCausasDevolucion() {
    try {/*from  ww  w  .  ja  v a  2s.c om*/
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction t = session.beginTransaction();
        List<CausasDevolucion> lista = session.createSQLQuery("select * from causas_devolucion;")
                .addEntity(CausasDevolucion.class).list();
        t.commit();

        if (session.isOpen()) {
            session.close();
        }
        return lista;
    } catch (HibernateException he) {
        LogSistema.guardarlog(this.getClass().getName()
                + " Method: getCausasDevolucion, Excepcion HibernateException: " + he.getMessage());
        System.out.println("Error al traer las causas de devolucion");
        List<CausasDevolucion> lista = new ArrayList<>();
        return lista;
    }
}

From source file:com.demo.impl.ConvenioImpl.java

@Override
public List<Convenio> conveniosPagos(int gestor_clv) {
    Session sess = HibernateUtil.getSessionFactory().openSession();
    Transaction tx = sess.beginTransaction();
    List<Convenio> convenios = new ArrayList<>();
    try {/* w  ww.j a va 2 s .  c o  m*/
        convenios = sess.createSQLQuery("select c.* from Convenio c, Pago p where \n"
                + "c.Cat_Gestores_Cat_Gestor_clv=" + gestor_clv + " and \n"
                + "c.idConvenio=p.Convenio_idConvenio1 and\n"
                + "p.Cat_Quincenas_Quincena_Clv=(select MAX(cq.Quincena_Clv) FROM Cat_Quincenas cq) and\n"
                + "p.Rev_Aprovado='Aprobado' GROUP by c.idConvenio;").addEntity(Convenio.class).list();
        tx.commit();
        if (sess.isOpen()) {
            sess.close();
        }

        if (convenios == null) {
            convenios = new ArrayList<>();
        }
        return convenios;
    } catch (HibernateException he) {
        LogSistema.guardarlog(this.getClass().getName()
                + " Method: conveniosPagos, Excepcion HibernateException: " + he.getMessage());
        return convenios;
    }
}

From source file:com.demo.impl.ConvenioImpl.java

@Override
public float totalPagosConv(Convenio conv) {
    Session sess = null;
    String consulta = "select sum(p.Aprobado_Corp) from convenio c join pago p where c.idConvenio = p.Convenio_idConvenio1 and c.idConvenio = "
            + conv.getIdConvenio() + " \n" + "and p.Aprobado = 'Aprobado';";

    float total = 0;
    try {/*from w ww  . j a v  a 2s  .  c  o m*/
        sess = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = sess.beginTransaction();
        System.out.println("CONVENIO ID" + conv.getIdConvenio());
        System.out.println("CONSULTA PAGOS = " + consulta);
        total = Float.parseFloat(sess.createSQLQuery(consulta).uniqueResult().toString());

        System.out.println("TOTAL = " + total);
        tx.commit();
        if (sess.isOpen()) {
            sess.close();
        }
        return total;
    } catch (HibernateException he) {
        LogSistema.guardarlog(this.getClass().getName()
                + " Method: totalPagosConv, Excepcion HibernateException: " + he.getMessage());
        return total;
    } catch (NullPointerException n) {
        LogSistema.guardarlog(this.getClass().getName()
                + " Method: totalPagosConv, Excepcion NullPointerException: " + n.getMessage());
        total = 0;
        return total;
    }

}