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:CitySearch.java

public City[] SearchNearestCities(double lat, double lng) {

    City[] cities = new City[5];
    Session sess = null;
    try {/*from  www.j a  v a2  s  .  co  m*/
        sess = NewHibernateUtil.currentSession();
        Transaction tx = sess.beginTransaction();
        //sql query to order the cities by the hypoteneus distance between given lat and lng
        // and then select the nearest 5 cities by using limit query
        String hql = "SELECT * FROM `Country`\n" + "\n" + "ORDER BY (SQRT(POWER((lat-" + String.valueOf(lat)
                + "),2)+" + "POWER((lng-" + String.valueOf(lng) + "),2))) \n" + "          limit 5;";
        Query query = sess.createSQLQuery(hql).addEntity(City.class);

        List results = query.list();
        System.out.println(String.valueOf(results.size()));
        cities = new City[5];
        for (int i = 0; i < results.size(); i++) {
            //adding results into the array to be returned
            cities[i] = ((City) results.get(i));
            System.out.println(((City) results.get(i)).country);
        }

        tx.commit();
        // sess.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return cities;

}

---

From source file:CitySearch.java

public City findCityByLocId(int locId) {
    Session sess = null;
    City city = new City();
    try {/*  w  w w  .  j a va2s .  c  o  m*/
        sess = NewHibernateUtil.currentSession();
        Transaction tx = sess.beginTransaction();
        //sql query to find the row corresponding to location id
        String hql = "select * FROM Country WHERE locId=" + String.valueOf(locId);
        Query query = sess.createSQLQuery(hql).addEntity(City.class);

        List results = query.list();
        System.out.println(String.valueOf(results.size()));
        for (int i = 0; i < results.size(); i++) {
            city = ((City) results.get(i));
            System.out.println(((City) results.get(i)).country);
        }

        tx.commit();
        //sess.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return city;
}

---

From source file:chart_demo.java

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods./*from w w w .j  av  a2 s  .  c  o m*/
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");
    PrintWriter out = response.getWriter();
    try {

        //            this file sends records to the chart output page

        List<AssetRecords> a = new ArrayList<>();
        SessionFactory sf = util.NewHibernateUtil.getSessionFactory();
        Session ss = sf.openSession();
        Transaction tr = ss.beginTransaction();
        int pid = 10;

        String s = "SELECT sum(Price),Year FROM the_asset_consultancy.AssetRecords where Assetid=" + pid
                + " group by Price,Year ;";

        SQLQuery query = ss.createSQLQuery(s);

        List<Object[]> lt = query.list();

        for (Object[] op : lt) {
            AssetRecords ar = new AssetRecords();
            ar.setPrice(op[0].toString());
            ar.setYear(op[1].toString());
            a.add(ar);

            out.println(a);

        }

        System.out.println("Size of array:" + a.size());
        request.setAttribute("al", a);
        RequestDispatcher rd = request.getRequestDispatcher("chart_demo_2.jsp");
        rd.forward(request, response);

    } catch (HibernateException he) {
        out.print(he.getMessage());
    }
}

---

From source file:GetPropDetailServ.java

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods./*from www  . j  a v a 2s .  co m*/
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");
    PrintWriter out = response.getWriter();
    try {

        SessionFactory sf = util.NewHibernateUtil.getSessionFactory();
        Session ss = sf.openSession();

        int pid = Integer.parseInt(request.getParameter("pid"));

        ArrayList<AssetRecords> a = new ArrayList<>();
        String s = "SELECT sum(Price),Year FROM the_asset_consultancy.AssetRecords where Assetid=" + pid
                + " group by Price,Year ;";

        SQLQuery query = ss.createSQLQuery(s);

        List<Object[]> lt = query.list();

        for (Object[] op : lt) {
            AssetRecords ar = new AssetRecords();
            ar.setPrice(op[0].toString());
            ar.setYear(op[1].toString());
            a.add(ar);

            out.println(a);

        }
        System.out.println("Size of array:" + a.size());
        request.setAttribute("al", a);

        Collections.sort(a, new Comparator<AssetRecords>() {

            @Override
            public int compare(AssetRecords o1, AssetRecords o2) {
                return o1.getYear().compareTo(o2.getYear());
            }
        });

        Criteria cr = ss.createCriteria(PropDetail.class);
        cr.add(Restrictions.eq("pId", pid));
        PropDetail pd = (PropDetail) cr.uniqueResult();

        System.out.println("property found:" + pd.getPId());

        Criteria cr1 = ss.createCriteria(PropFeedback.class);
        cr1.add(Restrictions.eq("pId", pd));

        ArrayList<PropFeedback> pfl = (ArrayList<PropFeedback>) cr1.list();

        request.setAttribute("pd", pd);

        if (!pfl.isEmpty()) {
            request.setAttribute("pfl", pfl);
        } else {
            System.out.println("No property feedback available");
        }

        //for the random agent display

        Criteria cr2 = ss.createCriteria(AgentDetail.class);

        ArrayList<AgentDetail> adl = (ArrayList<AgentDetail>) cr2.list();

        if (!adl.isEmpty()) {
            request.setAttribute("adl", adl);

        } else {
            System.out.println("random agent list empty");
        }

        RequestDispatcher rd = request.getRequestDispatcher("single.jsp");
        rd.forward(request, response);

    } catch (HibernateException e) {
        out.print(e.getMessage());
    }
}

---

From source file:StartProgram.java

public void SearchByLocId(int locId) {
    Session sess = null;
    City city = new City();
    try {// ww  w. jav  a2 s. co m
        sess = NewHibernateUtil.currentSession();
        Transaction tx = sess.beginTransaction();
        //sql statement to select all the rows
        String hql = "select * FROM Country";
        Query query = sess.createSQLQuery(hql).addEntity(City.class);

        List results = query.list();
        if (results.size() > 0) {
            CatalogueAdder adder = new CatalogueAdder();
            //start reading from csv and adding data into MYSQL
            adder.startProgram();
            CitySearch instance = new CitySearch();
            instance.findCityByLocId(locId);
        } else {
            CitySearch instance = new CitySearch();
            instance.findCityByLocId(locId);
        }
        tx.commit();

    } catch (Exception e) {
        e.printStackTrace();
    }

}

---

From source file:StartProgram.java

public void SearchNearestCities(double lat, double lng) {

    Session sess = null;
    City city = new City();
    try {//w w w  .java 2s .c  o  m
        sess = NewHibernateUtil.currentSession();
        Transaction tx = sess.beginTransaction();
        //sql statement to select all the rows
        String hql = "select * FROM Country";
        Query query = sess.createSQLQuery(hql).addEntity(City.class);

        List results = query.list();
        if (results.size() > 0) {
            CatalogueAdder adder = new CatalogueAdder();
            //start reading from csv and adding data into MYSQL
            adder.startProgram();
            CitySearch instance = new CitySearch();
            instance.SearchNearestCities(lat, lng);
        } else {
            CitySearch instance = new CitySearch();
            instance.SearchNearestCities(lat, lng);
        }
        tx.commit();

    } catch (Exception e) {
        e.printStackTrace();
    }
}

---

From source file:abd.p1.bd.UsuarioDAO.java

public void insertarUsuario(Usuario user) {
    String sql = "INSERT INTO usuarios ( correo , contrasea , genero , busca , fecha_nac , "
            + "foto , descripcion , aficiones, amigos , completa , recibidos , enviados )" + " VALUES ("
            + user.getCorreo() + user.getContrasena() + user.getGenero() + user.getBusca() + user.getFecha_nac()
            + user.getFoto() + user.getDescripcion() + user.getAficiones() + user.getAmigos()
            + user.getCompleta() + user.getRecibidos() + user.getEnviados() + " )";

    Session session = sf.openSession();
    Query query = session.createSQLQuery(sql);
    int result = query.executeUpdate();
}

---

From source file:administracion.actions.GestorOperacionesDatosRestaurante.java

public Restaurante obtenerDatosRestaurante() {
    Session sesion = null;
    Restaurante restaurante = null;/*from w  ww .  ja v a 2 s  .c om*/
    try {
        HibernateUtil hb = new HibernateUtil();
        sesion = hb.getSessionFactory().openSession();
        sesion.beginTransaction();
        String sql = "SELECT cif,nombre_local,direccion,telefono FROM datos_local";
        SQLQuery query = sesion.createSQLQuery(sql).addEntity(Restaurante.class);
        List<Restaurante> lista = query.list();
        sesion.getTransaction().commit();
        restaurante = lista.get(0);
    } catch (HibernateException e) {
        System.out.println("Error en la conexion con la base de datos: " + e);
        throw e;
    } catch (Exception e) {
        System.out.println("Error obtener datos restaurante: " + e);
    } finally {
        if (sesion != null) {
            sesion.close();
        }
    }

    return restaurante;
}

---

From source file:administracion.actions.GestorOperacionesDatosRestaurante.java

public List<Impuesto> obtenerImpuestos() {
    Session sesion = null;
    List<Impuesto> lista = null;
    try {//  w  w w . j  ava 2 s  . c  om
        HibernateUtil hb = new HibernateUtil();
        sesion = hb.getSessionFactory().openSession();
        sesion.beginTransaction();
        String sql = "SELECT nombre_impuesto,id_impuesto,valor FROM impuesto WHERE id_impuesto IN (SELECT MAX(id_impuesto) FROM impuesto GROUP BY nombre_impuesto) and valor > 0 ORDER BY nombre_impuesto";
        SQLQuery query = sesion.createSQLQuery(sql).addEntity(Impuesto.class);
        lista = query.list();
        sesion.getTransaction().commit();
    } catch (HibernateException e) {
        System.out.println("Error en la conexion con la base de datos: " + e);
        throw e;
    } catch (Exception e) {
        System.out.println("Error obtener impuestos: " + e);
    } finally {
        if (sesion != null) {
            sesion.close();
        }
    }

    return lista;
}

---

From source file:administracion.actions.GestorOperacionesDatosRestaurante.java

public int obtenerNumeroMesas() {
    Session sesion = null;
    int numeroMesa = 0;
    try {//from ww  w .j a  va 2s .c o  m
        HibernateUtil hb = new HibernateUtil();
        sesion = hb.getSessionFactory().openSession();
        sesion.beginTransaction();
        String sql = "SELECT MAX(numero_mesa) AS numero_mesa,estado_mesa,activo FROM mesa WHERE activo = true"; //Nos interesa el numero de mesas activas
        SQLQuery query = sesion.createSQLQuery(sql).addEntity(Mesa.class);
        List<Mesa> lista = query.list();
        numeroMesa = lista.get(0).getNumero();
        sesion.getTransaction().commit();
    } catch (HibernateException e) {
        System.out.println("Error en la conexion con la base de datos: " + e);
        throw e;
    } catch (Exception e) {
        System.out.println("Error obtener numero de mesas: " + e);
    } finally {
        if (sesion != null) {
            sesion.close();
        }
    }

    return numeroMesa;
}

---

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