List of usage examples for org.hibernate Session createSQLQuery
@Override NativeQuery createSQLQuery(String queryString);
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; }