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.epam.training.taranovski.web.project.repository.implementation.VacancyRepositoryImplementation.java

@Override
public List<Employee> getAppropriateAvailableEmployees(Vacancy vacancy) {
    EntityManager em = entityManagerFactory.createEntityManager();
    List<Integer> list = null;
    List<Employee> list1 = new LinkedList<>();

    try {/*w  w  w. j  a  va2  s .  co m*/
        em.getTransaction().begin();

        Query query = em.createNativeQuery(
                "select \"employeeId\" from (select \"employeeId\", \"vacancyId\" as \"ide\", count(*) as cou from (select \"UserSkill\".\"employeeId\", \"VacancySkill\".\"vacancyId\", \"UserSkill\".\"experience\", \"VacancySkill\".\"experience\" from \"UserSkill\" join \"VacancySkill\" on \"UserSkill\".\"allSkillsId\" = \"VacancySkill\".\"allSkillsId\" where \"UserSkill\".\"experience\" >= \"VacancySkill\".\"experience\" and \"VacancySkill\".\"vacancyId\" = ?) group by \"employeeId\", \"vacancyId\") where cou = (select count(*) from \"VacancySkill\" where \"VacancySkill\".\"vacancyId\" = \"ide\")");
        query.setParameter(1, vacancy.getVacancyId());
        list = query.getResultList();
        if (list.isEmpty()) {
            list.add(0);
        }

        TypedQuery<Employee> query1 = em.createNamedQuery("Employee.findAmongIdsFreeEmployees", Employee.class);
        query1.setParameter("employeeIdList", list);
        list1 = query1.getResultList();

        em.getTransaction().commit();
    } catch (RuntimeException e) {
        Logger.getLogger(VacancyRepositoryImplementation.class.getName()).info(e);
    } finally {
        if (em.getTransaction().isActive()) {
            em.getTransaction().rollback();
        }
        em.close();
    }

    return list1;
}

---

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

@Transactional(readOnly = false)
public void saveOrUpdate(final TicketFilter filter) {
    if (filter.getId() == null) {
        this.jpaTemplate.persist(filter);
    } else {//ww w  .  j  a v a  2 s  . com
        this.jpaTemplate.execute(new JpaCallback<TicketFilter>() {
            public TicketFilter doInJpa(EntityManager em) throws PersistenceException {
                TicketFilterDAOJpa.this.deleteFilterItems(em, filter.getId());
                em.merge(filter);
                // WORKAROUND: Jest jaki problem z zapisaniem do bazy pl z dat
                // ustawionych na null, jeli wczeniej miay jak warto... (OpenJPA 1.2.2)
                Query updateQuery = em
                        .createNativeQuery("UPDATE ticket_filters SET begin_date=?1, end_date=?2 WHERE id=?3");
                updateQuery.setParameter(1, filter.getBeginDate());
                updateQuery.setParameter(2, filter.getEndDate());
                updateQuery.setParameter(3, filter.getId());
                updateQuery.executeUpdate();
                return null;
            }
        });
    }
}

---

From source file:fr.univlorraine.ecandidat.controllers.DemoController.java

/** Lance un script sql
 * @param script/*from w  w  w .  ja  v  a 2  s .  c  om*/
 */
@Transactional
private void launchSqlScript(String script) {
    EntityManager em = entityManagerFactoryEcandidat.createEntityManager();
    em.getTransaction().begin();
    try {
        final InputStream inputStream = this.getClass().getResourceAsStream("/db/demo/" + script);
        final InputStreamReader inputStreamReader = new InputStreamReader(inputStream);
        final BufferedReader bufferedReader = new BufferedReader(inputStreamReader);
        while (bufferedReader.ready()) {
            Query query = em.createNativeQuery(bufferedReader.readLine());
            query.executeUpdate();
        }

    } catch (Exception e) {
        em.getTransaction().rollback();
        em.close();
    }
    em.getTransaction().commit();
    em.close();
}

---

From source file:com.doculibre.constellio.services.FeaturedLinkServicesImpl.java

@Override
public FeaturedLink suggest(String text, RecordCollection collection) {
    FeaturedLink suggestion;//from  w w w .  j ava2s .c  o  m
    EntityManager entityManager = ConstellioPersistenceContext.getCurrentEntityManager();

    String analyzedText;
    try {
        analyzedText = AnalyzerUtils.analyze(text, collection);
    } catch (Exception e) {
        analyzedText = null;
    }

    if (StringUtils.isNotBlank(analyzedText)) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT FL.id FROM FeaturedLink FL, FeaturedLink_KeywordsAnalyzed FLK, RecordCollection RC");
        sql.append(" WHERE FLK.FeaturedLink_id=FL.id AND FL.RecordCollection_id=RC.id");
        sql.append(" AND RC.id=? AND FLK.keyword=?");

        Query sqlQuery = entityManager.createNativeQuery(sql.toString());
        sqlQuery.setMaxResults(1);
        sqlQuery.setParameter(1, collection.getId());
        sqlQuery.setParameter(2, analyzedText);

        try {
            BigInteger featuredLinkId = (BigInteger) sqlQuery.getSingleResult();
            suggestion = get(featuredLinkId.longValue());
        } catch (NoResultException e) {
            suggestion = null;
        }
    } else {
        suggestion = null;
    }
    return suggestion;
}

---

From source file:com.doculibre.constellio.services.SynonymServicesImpl.java

@SuppressWarnings("unchecked")
@Override/* w  ww  .  j a va 2s  .  c om*/
public List<String> getSynonyms(String text, String collectionName) {
    EntityManager entityManager = ConstellioPersistenceContext.getCurrentEntityManager();
    StringBuffer sql = new StringBuffer();
    sql.append("SELECT SLS.synonym FROM SynonymList_Synonyms SLS, SynonymList SL, RecordCollection RC");
    sql.append("  WHERE SLS.synonymList_id=SL.id AND SL.recordCollection_id=RC.id");
    sql.append(" AND RC.id = (");
    sql.append(
            "   SELECT SL2.recordCollection_id FROM SynonymList_Synonyms SLS2, SynonymList SL2, RecordCollection RC2");
    sql.append("   WHERE SLS2.synonymList_id=SL2.id AND SL2.recordCollection_id=RC2.id AND SL2.id = SL.id");
    sql.append("   AND RC.name=? AND SLS2.synonym=?");
    sql.append(" )");

    Query sqlQuery = entityManager.createNativeQuery(sql.toString());
    sqlQuery.setParameter(1, collectionName);
    //      RecordCollection collection = ConstellioSpringUtils.getRecordCollectionServices().get(collectionName);
    //      Locale locale = collection.getLocales();
    sqlQuery.setParameter(2, text.toLowerCase());

    List<String> synonyms = sqlQuery.getResultList();
    return synonyms;
}

---

From source file:com.adeptj.modules.data.jpa.core.AbstractJpaRepository.java

@Override
public Number count(String query, QueryType type) {
    EntityManager em = JpaUtil.createEntityManager(this.getEntityManagerFactory());
    try {/*w  w w . j  a  v  a 2s.  c  o  m*/
        switch (type) {
        case JPA:
            return (Number) em.createQuery(query).getSingleResult();
        case NATIVE:
            return (Number) em.createNativeQuery(query).getSingleResult();
        default:
            throw new IllegalStateException("Invalid QueryType!!");
        }
    } catch (Exception ex) { // NOSONAR
        throw new JpaException(ex);
    } finally {
        JpaUtil.closeEntityManager(em);
    }
}

---

From source file:com.ge.apm.service.data.DataService.java

public Object postDirectData(String tablename, List<Map> list) {
    if (tablename == null)
        return "{\"code\":\"1\",\"msg\":\"please input table_name\"}";
    if (list == null || list.isEmpty())
        return "{\"code\":\"1\",\"msg\":\"no data post\"}";
    String tableName = tablename.toLowerCase();
    String talbeClassName = "com.ge.apm.domain." + tabelNameToClassName(tableName);
    Class<?> table = getDao(talbeClassName);
    Map<String, String> fields = getFields(table);

    EntityManagerFactory emf = WebUtil.getBean(EntityManagerFactory.class);
    EntityManager em = emf.createEntityManager();
    int fortimes = list.size() / 50 + 1;//
    for (int j = 0; j < fortimes; j++) {
        List<Map> subList = list.subList(j * 50, (j + 1) * 50 < list.size() ? (j + 1) * 50 : list.size());

        em.getTransaction().begin();/*  w  ww.j a  v a  2s.c o  m*/
        Query query = null;
        for (int i = 0; i < subList.size(); i++) {
            Map<String, Object> map = subList.get(i);
            String outColumnStr = "insert into " + tableName + " (";
            String outValueStr = ") values(";
            String[] strs = insertColumn(fields, map);
            outColumnStr += strs[0];
            outValueStr += strs[1];
            String sql = outColumnStr + outValueStr + ")";
            query = em.createNativeQuery(sql);
            query.executeUpdate();
        }
        try {
            em.getTransaction().commit();
        } catch (Exception ex) {
            em.getTransaction().rollback();
            Logger.getLogger(DataGetAndPushController.class.getName()).log(Level.SEVERE, null, ex);
            em.close();
            return "{\"code\":\"1\",\"msg\":\"save failed\"}";//
        }
    }
    if (em != null) {
        em.close();
    }
    return "{\"code\":\"0\",\"msg\":\"save success\"}";//?
}

---

From source file:com.xidu.framework.common.dao.impl.BaseDaoImpl.java

/**
 * //from   w ww  . jav a2  s . co m
 * {@inheritDoc} overridden:
 * 
 * @Date : 2011-3-28
 * @see com.xidu.framework.common.dao.IBaseDao#countByNativeQuery(java.lang.String)
 * 
 */
@Override
public int countByNativeQuery(final String nativeQuerySql) {

    return getJpaTemplate().execute(new JpaCallback<Integer>() {
        @Override
        public Integer doInJpa(EntityManager em) throws PersistenceException {
            Object countObj = em.createNativeQuery(nativeQuerySql).getSingleResult();
            return Integer.parseInt(countObj.toString());
        }

    });
}

---

From source file:com.xidu.framework.common.dao.impl.BaseDaoImpl.java

/**
 * //from  w w  w .  ja v a2  s.  c  o  m
 * {@inheritDoc} overridden:
 * 
 * @Date : 2011-3-28
 * @see com.xidu.framework.common.dao.IBaseDao#countByNativeQueryWithMap(java.lang.String,
 *      java.util.Map)
 * 
 */
@Override
public int countByNativeQueryWithMap(final String nativeQuerySql, final Map<String, ?> paraMap) {

    return getJpaTemplate().execute(new JpaCallback<Integer>() {

        @Override
        public Integer doInJpa(EntityManager em) throws PersistenceException {

            Query nativeQuery = em.createNativeQuery(nativeQuerySql);
            prepareQueryParam(nativeQuery, paraMap);

            Object countObj = nativeQuery.getSingleResult();
            return Integer.parseInt(countObj.toString());
        }

    });
}

---

From source file:com.xidu.framework.common.dao.impl.BaseDaoImpl.java

/**
 * /*from ww  w . j a va 2s.  c  o m*/
 * {@inheritDoc} overridden:
 * 
 * @Date : 2011-3-28
 * @see com.xidu.framework.common.dao.IBaseDao#countByNativeQueryWithVaParam(java.lang.String,
 *      java.lang.Object[])
 * 
 */
@Override
public int countByNativeQueryWithVaParam(final String nativeQuerySql, final Object... params) {

    return getJpaTemplate().execute(new JpaCallback<Integer>() {

        @Override
        public Integer doInJpa(EntityManager em) throws PersistenceException {

            Query nativeQuery = em.createNativeQuery(nativeQuerySql);
            prepareQueryParam(nativeQuery, params);

            Object countObj = nativeQuery.getSingleResult();
            return Integer.parseInt(countObj.toString());
        }

    });
}

---

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