Example usage for javax.persistence EntityManager createNativeQuery

List of usage examples for javax.persistence EntityManager createNativeQuery

Introduction

In this page you can find the example usage for javax.persistence EntityManager createNativeQuery.

Prototype

public Query createNativeQuery(String sqlString);

Source Link

Document

Create an instance of Query for executing a native SQL statement, e.g., for update or delete.

Usage

From source file:com.taobao.ad.easyschedule.dao.uresc.impl.JPAURescDAOImpl.java

@SuppressWarnings("unchecked")
@Override//from  ww w  .  j a va2  s . co  m
public List<Integer> findAllUserRescs(final String userName) {
    return (List<Integer>) super.getJpaTemplate().execute(new JpaCallback() {
        public Object doInJpa(EntityManager em) throws PersistenceException {
            return em.createNativeQuery(
                    "SELECT  rr.resc_id from es_u_resc_role rr where rr.role_id in ( select r.id from es_u_user u, es_u_user_role ur, es_u_role r where u.username=?1 and u.id=ur.user_id and r.id=ur.role_id)")
                    .setParameter(1, userName).getResultList();

        }
    });
}

From source file:de.berlios.jhelpdesk.dao.jpa.ArticleCategoryDAOJpa.java

@Transactional(readOnly = false)
public void moveDown(final Long categoryId) {
    this.jpaTemplate.execute(new JpaCallback() {
        public Object doInJpa(EntityManager em) throws PersistenceException {
            Query q = em.createNativeQuery("SELECT category_move_down(?1)");
            q.setParameter(1, categoryId);
            q.getSingleResult();//w ww  .  jav  a 2 s . c o m
            return null;
        }
    });
}

From source file:de.berlios.jhelpdesk.dao.jpa.ArticleCategoryDAOJpa.java

@Transactional(readOnly = false)
public void moveUp(final Long categoryId) {
    this.jpaTemplate.execute(new JpaCallback() {
        public Object doInJpa(EntityManager em) throws PersistenceException {
            Query q = em.createNativeQuery("SELECT category_move_up(?1)");
            q.setParameter(1, categoryId);
            q.getSingleResult();//from   w ww .  j  a v a  2s .co  m
            return null;
        }
    });
}

From source file:com.intuit.tank.dao.UpdateScriptGroupsTest.java

@Test(groups = { "manual" })
public void upgrade() {
    EntityManager em = dao.getEM();
    Query query = em.createNativeQuery(
            "select id, workload_id from script_group where test_plan_id is null order by position");
    @SuppressWarnings("unchecked")
    List<Object[]> results = query.getResultList();
    for (Object[] set : results) {
        for (Object o : set) {
            Integer id = (Integer) set[0];
            Integer workloadId = (Integer) set[1];
            System.out.print(o);//from  www. j a v a 2s  . c o m
            System.out.print(", ");
            if (workloadId != null) {
                updateScriptGroup(id, workloadId);
            } else {
                deleteScriptGroup(id);
            }
        }
        System.out.print('\n');
    }
    for (Workload w : workloadMap.values()) {
        workloadDao.saveOrUpdate(w);
    }
}

From source file:de.berlios.jhelpdesk.dao.jpa.TicketFilterDAOJpa.java

private void deleteFilterItems(EntityManager em, Long filterId) {
    for (String table : tables) {
        Query query = em
                .createNativeQuery("DELETE FROM ticket_filters_" + table + " WHERE ticket_filter_id=?1");
        query.setParameter(1, filterId);
        query.executeUpdate();//  w  w w.  ja  va 2 s.  c  o m
    }
}

From source file:Theatre.TheatreManager.java

public List getTheatreByLocation(float latitude, float longitude, int radius) {
    EntityManager em = emFactory.createEntityManager();
    Query query = em.createNativeQuery(
            "Select u.id, u.name, u.address, u.city, u.state, u.zipcode,(ACOS(SIN(RADIANS(u.latitude))*SIN(RADIANS("
                    + latitude + ")) + COS(RADIANS(u.latitude))*COS(RADIANS(" + latitude
                    + "))*COS(RADIANS(u.longitude)-RADIANS(" + longitude
                    + "))))*3959 as distance from Theatre u where (ACOS(SIN(RADIANS(u.latitude))*SIN(RADIANS("
                    + latitude + ")) + COS(RADIANS(u.latitude))*COS(RADIANS(" + latitude
                    + "))*COS(RADIANS(u.longitude)-RADIANS(" + longitude + "))))*3959<" + radius
                    + " order by distance");
    List<Object[]> result = query.getResultList();
    em.close();/*from www . j a va 2s.  co m*/
    return result;
}

From source file:com.taobao.ad.easyschedule.dao.urole.impl.JPAURoleDAOImpl.java

@SuppressWarnings("unchecked")
@Override/* w  w w . j  a v  a  2 s .  c  o m*/
public List<URoleDO> findURoles(final String userName) {
    return (List<URoleDO>) super.getJpaTemplate().execute(new JpaCallback() {
        public Object doInJpa(EntityManager em) throws PersistenceException {
            List<URoleDO> list = null;
            List<Object> listArray = em.createNativeQuery(
                    " select  r.id as id, r.descn as descn from es_u_user u,es_u_user_role ur, es_u_role r where u.username=?1 and u.id=ur.user_id and r.id=ur.role_id")
                    .setParameter(1, userName).getResultList();
            for (int i = 0; i < listArray.size(); i++) {
                Object[] temp = (Object[]) listArray.get(i);
                URoleDO uRoleDO = new URoleDO();
                uRoleDO.setId(Long.valueOf(temp[0].toString()));
                uRoleDO.setDescn(temp[1].toString());
                list = new ArrayList<URoleDO>(listArray.size());
                list.add(uRoleDO);
            }
            return list;
        }
    });
}

From source file:pl.datamatica.traccar.api.fcm.Daemon.java

public void clearInactiveSessions(EntityManager em, User user) {
    Set<String> activeSessions = new HashSet<>(
            em.createNativeQuery("SELECT sessionId from JettySessions").getResultList());
    List<UserSession> validSessions = new ArrayList<>();
    for (UserSession s : user.getSessions()) {
        if (activeSessions.contains(s.getSessionId()))
            validSessions.add(s);/*from  w  w  w  .  j  a v  a 2s .  c o m*/
    }
    user.setSessions(validSessions);
}

From source file:com.webbfontaine.valuewebb.irms.impl.data.AttachedDocDataSource.java

@Override
public InputStream getInputStream() throws IOException {
    EntityManager entityManager = Utils.createEntityManager();
    try {// ww w  .  ja v  a 2 s.c o m
        // We should always be able to find attached doc for already persisted TT
        Blob data = (Blob) entityManager.createNativeQuery(SELECT_QUERY).setParameter(1, docId)
                .getSingleResult();

        return stream(data);
    } catch (Exception e) {
        throw Throwables.propagate(e);
    } finally {
        entityManager.close();
    }
}

From source file:de.berlios.jhelpdesk.dao.jpa.TicketCategoryDAOJpa.java

@Transactional(readOnly = false)
public void insertCategory(final TicketCategory category, final TicketCategory parent) {
    final long nodeCount = getNodeCount();
    this.jpaTemplate.execute(new JpaCallback() {
        public Object doInJpa(EntityManager em) throws PersistenceException {
            Query q1 = em.createNativeQuery(
                    "UPDATE ticket_category SET t_right=t_right+2 WHERE t_right>=? AND t_right<=?");
            q1.setParameter(1, parent.getRight());
            q1.setParameter(2, nodeCount * 2);
            q1.executeUpdate();//  w  w w. ja  v a  2  s .co  m

            Query q2 = em.createNativeQuery(
                    "UPDATE ticket_category SET t_left=t_left+2 WHERE t_left>? AND t_left<?");
            q2.setParameter(1, parent.getRight());
            q2.setParameter(2, (nodeCount + 1) * 2);
            q2.executeUpdate();

            category.setLeft(parent.getRight());
            category.setRight(parent.getRight() + 1);
            category.setDepth(parent.getDepth() + 1);

            em.persist(category);
            return null;
        }
    });
}