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.atsamour.habitatweave.controller.IndexServlet.java

License:Open Source License

private List<Object[]> getSleepMeasurement(Session session, String person_id, Date date1, Date date2) {
    Query query = session.createSQLQuery("select `date`, `type`, `value` from measurement "
            + "WHERE measurement.person_id=:pid AND type IN ( 'SleepLatency', 'TotalTimeDeepSleep', 'TotalTimeInBedButAwake', 'TotalTimeShallowSleep') "
            + "AND date BETWEEN :date1 AND :date2")//.addScalar("type", StringType.INSTANCE)
            //.addEntity(Date.class)
            .setString("pid", person_id).setDate("date1", date1).setDate("date2", date2);
    List<Object[]> result = query.list();
    return result;
}

From source file:com.atsamour.habitatweave.controller.IndexServlet.java

License:Open Source License

private List<Object[]> getActivityMeasurement(Session session, String person_id, Date date1, Date date2) {
    Query query = session.createSQLQuery(
            "SELECT `date`, SUM(measurement.value) AS value, HOUR(measurement.start_time) as start_time, "
                    + "`type` FROM measurement WHERE measurement.person_id=:pid "
                    + "AND type IN ( 'MovingIntensity' ) AND date BETWEEN :date1 AND :date2 "
                    + "GROUP BY date, HOUR(start_time)")
            //            "select `date`, `type`, SUM(measurement.value) AS value from measurement "
            //                    + "WHERE measurement.person_id=:pid AND type IN ( 'SleepScore' ) "
            //                    + "AND date BETWEEN :date1 AND :date2 GROUP BY date, HOUR(start_time)" )//.addScalar("type", StringType.INSTANCE)
            .setString("pid", person_id).setDate("date1", date1).setDate("date2", date2);
    List<Object[]> result = query.list();
    return result;
}

From source file:com.atsamour.habitatweave.controller.OptionsServlet.java

License:Open Source License

/**
 * Handles the HTTP <code>POST</code> method.
 *
 * @param request servlet request/*from  www .  jav  a2 s  . c  om*/
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    //request.setAttribute("message", "please give description and vendor name");
    // new room
    if (request.getParameter("path") != null && !request.getParameter("path").isEmpty()) {

        String path = request.getParameter("path");
        String user_id = getCurrentUserId();

        System.out.println(user_id);
        Session session = HibernateUtil.getSessionFactory().openSession();
        session.beginTransaction();

        try {
            java.util.Date date = new Date();
            Timestamp timestamp = new Timestamp(date.getTime());

            Query query = session
                    .createSQLQuery(
                            "INSERT INTO options (`user_id`, `path`, `time`) VALUES (:user_id, :path, :time)")
                    .setInteger("user_id", Integer.parseInt(user_id)).setString("path", path)
                    .setTimestamp("time", timestamp);
            query.executeUpdate();
        } finally {
            session.getTransaction().commit();
            request.setAttribute("message", "Path inserted successfully");
            request.setAttribute("alert", "success");
            if (session.isOpen()) {
                session.close();
            }
        }
        // new appliance
    } else {
        request.setAttribute("message", "Please fill form");
        request.setAttribute("alert", "warning");
    }

    // draw JSP
    try {
        request.getRequestDispatcher("/secure/options.jsp").include(request, response);
    } catch (ServletException e) {
        e.printStackTrace();
    }
}

From source file:com.atsamour.habitatweave.controller.SleepServlet.java

License:Open Source License

/**
 * Handles the HTTP <code>GET</code> method.
 *
 * @param request servlet request//from   www .j  a v a2s.co m
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");

    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();
    Query query = session.createSQLQuery(
            "SELECT `start_time` FROM measurement WHERE type = 'SleepLatency' ORDER BY start_time ASC LIMIT 1");

    List<Object> result = query.list();
    Date date = new Date();
    date = (java.sql.Timestamp) (result.get(0));
    request.getSession().setAttribute("minD", date);
    query = session.createSQLQuery(
            "SELECT `start_time` FROM measurement WHERE type = 'SleepLatency' ORDER BY start_time DESC LIMIT 1");
    result = query.list();
    date = (java.sql.Timestamp) (result.get(0));
    request.getSession().setAttribute("maxD", date);

    request.setAttribute("hide", "hide");
    if (session.isOpen()) {
        session.close();
    }
    RequestDispatcher rd = getServletContext().getRequestDispatcher("/secure/sleep.jsp");
    rd.forward(request, response);
}

From source file:com.atsamour.habitatweave.controller.SwitchPlugsServlet.java

License:Open Source License

/** 
 * Handles the HTTP <code>GET</code> method.
 * @param request servlet request/*from ww w  .  j a v a 2  s.c  o  m*/
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    response.setContentType("text/html");// read password hash and salt from db

    Session hiberSession = HibernateUtil.getSessionFactory().openSession();
    hiberSession.beginTransaction();
    String baseURL = "";

    try {
        ApplianceDAO applianceDAO = new ApplianceDAO(hiberSession);
        final List<Appliance> appliances;
        appliances = applianceDAO.getAppliancesByUserId(Integer.parseInt(getCurrentUserId()));

        RoomDAO roomDAO = new RoomDAO(hiberSession);
        final List<Room> rooms = roomDAO.getRoomsByUserId(Integer.parseInt(getCurrentUserId()));

        Query query = hiberSession
                .createSQLQuery("select `path` from options "
                        + "WHERE options.user_id= :pid ORDER BY time  DESC LIMIT 1")
                .setInteger("pid", Integer.parseInt(getCurrentUserId()));
        List<Object> result = query.list();

        baseURL = (String) result.get(0);

        HttpSession session = request.getSession();

        session.setAttribute("baseURL", baseURL);
        session.setAttribute("rooms", rooms);
        session.setAttribute("appliances", appliances);
        RequestDispatcher rd = getServletContext().getRequestDispatcher("/secure/switchplugs.jsp");
        rd.forward(request, response);
    } finally {
        hiberSession.getTransaction().commit();
        if (hiberSession.isOpen()) {
            hiberSession.close();
        }
    }
}

From source file:com.atsamour.habitatweave.util.ScheduledWorks.java

License:Open Source License

private void executeWork(WorkToDo w) {
    String baseURL = "";

    Session session = HibernateUtil.getSessionFactory().openSession();
    session.beginTransaction();// w  w w.jav a  2  s.  co m
    try {
        //retrives the user's last inserted aWESoME path
        Query query = session
                .createSQLQuery("select `path` from options "
                        + "WHERE options.user_id= :pid ORDER BY time  DESC LIMIT 1")
                .setInteger("pid", Integer.parseInt(getCurrentUserId()));
        List<Object> result = query.list();
        baseURL = (String) result.get(0);

    } finally {
        session.getTransaction().commit();
        if (session.isOpen()) {
            session.close();
        }
    }

    URL url;
    HttpURLConnection conn;
    BufferedReader rd;
    String line;
    String result = "";
    try {
        url = new URL(baseURL + w.getCommand());
        conn = (HttpURLConnection) url.openConnection();
        conn.setRequestMethod("GET");
        rd = new BufferedReader(new InputStreamReader(conn.getInputStream()));
        while ((line = rd.readLine()) != null) {
            result += line;
        }
        rd.close();
        System.out.println(result);
    } catch (IOException ex) {
        Logger.getLogger(ScheduledWorks.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:com.auction.db.HibernateUtil.java

synchronized public static Session getSession() {
    //getTransaction().getStatus() != TransactionStatus.ACTIVE
    for (int i = 0; i < sessions.size(); i++) {
        if (sessions.get(i).getTransaction().getLocalStatus() != LocalStatus.ACTIVE) {
            Session tempSession = sessions.get(i);
            try {
                //a dummy call to check whether session is valid or not
                tempSession.beginTransaction();
                Query query = tempSession.createSQLQuery("select * from roles");
                List<Role> roles = query.list();
                tempSession.getTransaction().commit();
                return tempSession;
            } catch (Exception ex) {
                //if there is exception then we will use next session in array list
            }/* w  w  w . ja va  2  s.  c o m*/
            //return sessions.get(i);
        }
    }
    Session session = sessionFactory.openSession();
    sessions.add(session);
    return session;
}

From source file:com.autentia.wuija.persistence.impl.hibernate.HibernateDao.java

License:Open Source License

@Override
@SuppressWarnings("unchecked")
public <T> List<T> findByNativeQueryTransformer(final Class<T> transformerClass, final String queryString,
        final int firstResult, final int maxResults, final Map values) {
    final List<T> list = getHibernateTemplate().executeFind(new HibernateCallback() {

        @Override/*from  w w  w  . jav  a2 s.  c  o m*/
        public Object doInHibernate(Session session) throws HibernateException {
            final SQLQuery query = session.createSQLQuery(queryString);
            query.setResultTransformer(Transformers.aliasToBean(transformerClass));
            setPagination(query, firstResult, maxResults);
            setNamedParameters(values, query);
            return query.list();
        }
    });
    traceResults(list);
    return list;
}

From source file:com.autentia.wuija.persistence.impl.hibernate.HibernateDao.java

License:Open Source License

@Override
public void updateByNativeSQL(final String queryString, final Object... values) {
    getHibernateTemplate().execute(new HibernateCallback() {

        @Override/*from  w  w  w .ja  v  a2  s  .com*/
        public Object doInHibernate(Session session) throws HibernateException {
            final SQLQuery query = session.createSQLQuery(queryString);
            for (int i = 0; i < values.length; i++) {
                query.setParameter(i, values[i]);
            }
            return query.executeUpdate();
        }
    });
}

From source file:com.autentia.wuija.persistence.impl.hibernate.HibernateDao.java

License:Open Source License

@Override
public <T> List<T> findByNativeQueryTransformerWithListParametersSupport(final Class<T> transformerClass,
        final String sqlQueryString, final Map values, final int firstResult, final int maxResults) {

    @SuppressWarnings("unchecked")
    final List<T> list = getHibernateTemplate().executeFind(new HibernateCallback() {

        @Override//w w w .  j av  a2s . c  om
        public Object doInHibernate(Session session) throws HibernateException {
            final SQLQuery query = session.createSQLQuery(sqlQueryString);
            query.setResultTransformer(Transformers.aliasToBean(transformerClass));
            setPagination(query, firstResult, maxResults);
            setNamedParametersWithListSupport(values, query);
            return query.list();
        }
    });
    traceResults(list);
    return list;
}