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.demo.impl.VirtCreditoLineasImpl.java

@Override
public List<VirtCreditoSfLtNtCt> conservados() {
    try {//from  w  w w .jav a  2s  .  co  m
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction t = session.beginTransaction();
        String consulta = "select vct.* from Virt_Credito_SF_LT_NT_CT vct where \n"
                + "vct.CreditosII in (select ct.CreditosII from Credito_SF_LT_NT_CT ct where ct.Cat_Subestatus_Cuenta_Subestatus_Clv=5) order by vct.CreditosII;";
        System.out.println("CONSULTA CONSERVADOS VIRTUALES : " + consulta);
        List<VirtCreditoSfLtNtCt> lista = session.createSQLQuery(consulta).addEntity(VirtCreditoSfLtNtCt.class)
                .list();
        t.commit();
        return lista;
    } catch (HibernateException he) {
        LogSistema
                .guardarlog(this.getClass().getName() + " Method: conservados, Exception: " + he.getMessage());
        List<VirtCreditoSfLtNtCt> lista = new ArrayList<>();
        return lista;
    }
}

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

@Override
public List<VirtCreditoSfLtNtCt> regresan() {
    try {/*w ww.j  a  va  2 s. c  om*/
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction t = session.beginTransaction();
        String consulta = "select vct.* from Virt_Credito_SF_LT_NT_CT vct where \n"
                + "vct.CreditosII in (select ct.CreditosII from Credito_SF_LT_NT_CT ct where ct.Cat_Subestatus_Cuenta_Subestatus_Clv!=5);";
        List<VirtCreditoSfLtNtCt> lista = session.createSQLQuery(consulta).addEntity(VirtCreditoSfLtNtCt.class)
                .list();
        t.commit();
        return lista;
    } catch (HibernateException he) {
        LogSistema.guardarlog(this.getClass().getName() + " Method: regresan, Exception: " + he.getMessage());
        List<VirtCreditoSfLtNtCt> lista = new ArrayList<>();
        return lista;
    }
}

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

@Override
public List<VirtCreditoSfLtNtCt> nuevosCreditos() {
    try {/*  w  w  w .  j a va2s .  c om*/
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction t = session.beginTransaction();
        String consulta = "select vct.* from Virt_Credito_SF_LT_NT_CT vct, Virt_Datos_primarios vdp where \n"
                + "vct.CreditosII not  in (select ct.CreditosII from Credito_SF_LT_NT_CT ct) and \n"
                + "vct.Virt_Datos_primarios_Folio=vdp.Folio and\n"
                + "vdp.Deudor in (select dp.Deudor from Datos_primarios dp);";
        List<VirtCreditoSfLtNtCt> lista = session.createSQLQuery(consulta).addEntity(VirtCreditoSfLtNtCt.class)
                .list();
        t.commit();
        return lista;
    } catch (HibernateException he) {
        LogSistema.guardarlog(
                this.getClass().getName() + " Method: nuevosCreditos, Exception: " + he.getMessage());
        List<VirtCreditoSfLtNtCt> lista = new ArrayList<>();
        return lista;
    }
}

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

@Override
public List<VirtCreditoSfLtNtCt> nuevosTotales() {
    try {/*  ww  w .j a v a 2 s  . c  o  m*/
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction t = session.beginTransaction();
        String consulta = "select vct.* from Virt_Credito_SF_LT_NT_CT vct, Virt_Datos_primarios vdp where \n"
                + "vct.CreditosII not  in (select ct.CreditosII from Credito_SF_LT_NT_CT ct) and \n"
                + "vct.Virt_Datos_primarios_Folio=vdp.Folio and\n"
                + "vdp.Deudor not in (select dp.Deudor from Datos_primarios dp);";
        List<VirtCreditoSfLtNtCt> lista = session.createSQLQuery(consulta).addEntity(VirtCreditoSfLtNtCt.class)
                .list();
        t.commit();
        return lista;
    } catch (HibernateException he) {
        LogSistema.guardarlog(
                this.getClass().getName() + " Method: nuevosTotales, Exception: " + he.getMessage());
        List<VirtCreditoSfLtNtCt> lista = new ArrayList<>();
        return lista;
    }
}

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

@Override
public List<Visitadatos> obtenerVisitasDatos() {
    List<Visitadatos> visDatos = new ArrayList();
    System.out.println("entro al metodo ibtener visitadatos");

    try {//from   w  ww  .  ja  v  a2s  .c  o  m
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = session.beginTransaction();
        visDatos = session.createSQLQuery("select * from visitadatos a where a.Estatus != 'REALIZADO';")
                .addEntity(Visitadatos.class).list();
        tx.commit();
    } catch (HibernateException j) {
        LogSistema.guardarlog(
                this.getClass().getName() + " Method: obtenerVisitasDatos, Exception: " + j.getMessage());
    }
    return visDatos;
}

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

@Override
public List<Visitadatos> obtenerVisitasDatosTodos() {
    List<Visitadatos> visDatos = new ArrayList();
    System.out.println("entro al metodo ibtener visitadatos");

    try {//from w ww .  j  a v a 2  s. c  o m
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = session.beginTransaction();
        visDatos = session.createSQLQuery("select * from visitadatos;").addEntity(Visitadatos.class).list();
        tx.commit();
    } catch (HibernateException j) {
        System.out.println("Error obtenerVisitaDatos");
        LogSistema.guardarlog(
                this.getClass().getName() + " Method: obtenerVisitasDatosTodos, Exception: " + j.getMessage());
    }
    return visDatos;
}

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

@Override
public List<Visitadatos> obtenerVisitaDatosFecha(Date fecha) {
    List<Visitadatos> visDatos = new ArrayList();
    Calendar fech = new GregorianCalendar();
    fech.setTime(fecha);/*w w  w.  j av  a2  s  . c o  m*/
    fech.set(Calendar.HOUR, 23);
    fech.set(Calendar.MINUTE, 59);
    fech.set(Calendar.SECOND, 59);
    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    String fecha1 = dateToMySQLDate(fecha);
    String fecha2 = dateFormat.format(fech.getTime());

    System.out.println("entro al metodo ibtener visitadatos");
    String consulta = "select * from visitadatos a where a.fechaVisita between '" + fecha1 + "' and '" + fecha2
            + "';";
    System.out.println("CONSULTA VISISTA DATOS POR FECHA: " + consulta);

    try {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = session.beginTransaction();
        visDatos = session.createSQLQuery(consulta).addEntity(Visitadatos.class).list();
        tx.commit();
    } catch (HibernateException j) {
        System.out.println("Error obtenerVisitaDatosFecha");
        LogSistema.guardarlog(
                this.getClass().getName() + " Method: obtenerVisitaDatosFecha, Exception: " + j.getMessage());
    }
    return visDatos;
}

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

@Override
public List<Visitadatos> fitrarVisitaDatos(Visitadatos vis) {
    List<Visitadatos> visDatos = new ArrayList();
    System.out.println("entro al metodo filtrarVisitaDatos");

    System.out.println("Gestor = " + vis.getGestorImpresion());
    System.out.println("Stauts = " + vis.getEstatus());
    System.out.println("Tipo = " + vis.getTipo());
    System.out.println("Inicio = " + vis.getFechaVisita());
    System.out.println("Fin = " + vis.getFechaImpresion());
    System.out.println("Corredor = " + vis.getCorredor().getIdCorredor());
    System.out.println("Cp = " + vis.getCp());

    String consulta = "select * from visitadatos a where ";

    if (!vis.getGestorImpresion().equals("TODOS")) {
        consulta = consulta + "a.gestorImpresion ='" + vis.getGestorImpresion() + "' and ";
    }/* w  w w  . j a v a 2  s .  c o  m*/

    if (!vis.getEstatus().equals("TODOS")) {
        consulta = consulta + "a.Estatus='" + vis.getEstatus() + "' and ";
    }

    if (vis.getCorredor().getIdCorredor() != 11) {
        consulta = consulta + "a.Corredor_idCorredor = " + vis.getCorredor().getIdCorredor() + " and ";
    }

    if (!vis.getTipo().equals("TODOS")) {
        consulta = consulta + "a.tipo='" + vis.getTipo() + "' and ";
    }

    if (!vis.getCp().equals("TODOS")) {
        consulta = consulta + "a.cp='" + vis.getCp() + "' and ";
    }

    if (vis.getFechaVisita() != null || vis.getFechaImpresion() != null) {
        String fechaIni = dateToMySQLDate(vis.getFechaVisita());
        String fechaFin = dateToMySQLDate(vis.getFechaImpresion());
        consulta = consulta + "a.fechaImpresion between '" + fechaIni + "' and '" + fechaFin + "';";
    } else {
        String fechaIni = "2013-01-01 00:00:00";
        String fechaFin = "2050-01-01 00:00:00";
        consulta = consulta + "a.fechaImpresion between '" + fechaIni + "' and '" + fechaFin + "';";
    }

    System.out.println("CONSULTA FILTRO: " + consulta);
    try {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = session.beginTransaction();

        visDatos = session.createSQLQuery(consulta).addEntity(Visitadatos.class).list();
        System.out.println(consulta);
        tx.commit();
    } catch (HibernateException j) {
        LogSistema.guardarlog(
                this.getClass().getName() + " Method: fitrarVisitaDatos, Exception: " + j.getMessage());
    }
    return visDatos;

}

From source file:com.denimgroup.threadfix.data.dao.hibernate.HibernateFindingDao.java

License:Mozilla Public License

@Override
@SuppressWarnings("unchecked")
public List<String> retrieveByHint(String hint, Integer appId) {
    Session currentSession = sessionFactory.getCurrentSession();
    Integer channelTypeId = (Integer) currentSession
            .createQuery("select id from ChannelType where name = 'Manual'").uniqueResult();
    if (channelTypeId == null) {
        assert false : "ThreadFix was unable to find the manual channel. This indicates an incomplete database connection.";
        return null;
    }//from w  w w .  ja va 2s. c  o  m
    Integer applicationChannelId = (Integer) (currentSession.createQuery(
            "select id from ApplicationChannel where applicationId = :appId and channelTypeId = :channelTypeId")
            .setInteger("appId", appId).setInteger("channelTypeId", channelTypeId).uniqueResult());
    if (applicationChannelId == null)
        return null;
    Integer scanId = (Integer) currentSession.createQuery(
            "select id from Scan where applicationId = :appId and applicationChannelId = :applicationChannelId")
            .setInteger("appId", appId).setInteger("applicationChannelId", applicationChannelId).uniqueResult();
    if (scanId == null)
        return null;
    return currentSession
            .createSQLQuery("select distinct(path) from SurfaceLocation where id in "
                    + "(select surfaceLocationId from Finding where scanId = :scanId) and path like "
                    + ":hint order by path")
            .setString("hint", "%" + hint + "%").setInteger("scanId", scanId).list();
}

From source file:com.denimgroup.threadfix.data.dao.hibernate.HibernateFindingDao.java

License:Mozilla Public License

@SuppressWarnings("unchecked")
@Override//from  w  ww.j  a  va2 s  . c  o m
public List<String> retrieveManualUrls(Integer appId) {
    Session currentSession = sessionFactory.getCurrentSession();
    Integer channelTypeId = (Integer) currentSession
            .createQuery("select id from ChannelType where name = 'Manual'").uniqueResult();
    if (channelTypeId == null)
        return null;
    Integer applicationChannelId = (Integer) (currentSession.createQuery(
            "select id from ApplicationChannel where applicationId = :appId and channelTypeId = :channelTypeId")
            .setInteger("appId", appId).setInteger("channelTypeId", channelTypeId).uniqueResult());
    if (applicationChannelId == null)
        return null;
    Integer scanId = (Integer) currentSession.createQuery(
            "select id from Scan where applicationId = :appId and applicationChannelId = :applicationChannelId")
            .setInteger("appId", appId).setInteger("applicationChannelId", applicationChannelId).uniqueResult();
    if (scanId == null)
        return null;
    return currentSession
            .createSQLQuery("select distinct(path) from SurfaceLocation where id in "
                    + "(select surfaceLocationId from Finding where scanId = :scanId) " + "order by path")
            .setInteger("scanId", scanId).list();
}