Example usage 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;
        }
    });
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• »