List of usage examples for javax.persistence EntityManager createNativeQuery
public Query createNativeQuery(String sqlString);
Query
for executing a native SQL statement, e.g., for update or delete. 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; } }); }