Example usage for javax.persistence EntityManager createNativeQuery

List of usage examples for javax.persistence EntityManager createNativeQuery

  1. HOME
  2. Java
  3. javax
  4. javax.persistence.*
  5. EntityManager
  6. 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.reportjobrt.impl.JPAReportJobRtDAOImpl.java

@SuppressWarnings("unchecked")
@Override// www . j  a  v  a  2  s  .  c  om
public List<ReportJobRtDO> findReportJobRtPageList(final ReportJobRtDO reportJobRt) {
    reportJobRt.setTotalItem(((BigInteger) this.getJpaTemplate().execute(new JpaCallback() {

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

            return em.createNativeQuery(
                    "select count(distinct t.JOB_NAME ,t.JOB_GROUP) from  es_report_job_rt t where t.create_time<=?1 and t.create_time>=?2")
                    .setParameter(1, reportJobRt.getQueryEndTime())
                    .setParameter(2, reportJobRt.getQueryStartTime()).getSingleResult();

        }
    })).intValue());

    List<Object[]> result = (List<Object[]>) super.getJpaTemplate().execute(new JpaCallback() {

        @Override
        public Object doInJpa(EntityManager em) throws PersistenceException {
            return em.createNativeQuery(
                    "SELECT *,a.successNum*100/a.totalNum AS successRate FROM ( SELECT  AVG(rt) AS rt,COUNT(*) AS totalNum,job_group,job_name,COUNT(CASE WHEN STATUS = 2 THEN '1' ELSE NULL END) AS successNum, COUNT(CASE WHEN STATUS > 2 THEN 'x' ELSE NULL END) AS errorNum FROM es_report_job_rt where create_time<=?1 and create_time>=?2 GROUP BY job_name,job_group  ) a ORDER BY a.job_group,successRate "
                            + (reportJobRt.isSuccessRateOrderBy() ? "desc" : ""))
                    .setParameter(1, reportJobRt.getQueryEndTime())
                    .setParameter(2, reportJobRt.getQueryStartTime()).setFirstResult(reportJobRt.getStartRow())
                    .setMaxResults(reportJobRt.getPerPageSize()).getResultList();
        }
    });

    List<ReportJobRtDO> rtList = new ArrayList<ReportJobRtDO>(result.size());

    for (Object[] o : result) {
        ReportJobRtDO rt = new ReportJobRtDO();
        if (o[0] != null) {
            rt.setShowRt(new BigDecimal(o[0].toString()).setScale(2, RoundingMode.HALF_DOWN));
        }
        rt.setTotalNum(((BigInteger) o[1]).intValue());
        rt.setJobGroup(String.valueOf(o[2]));
        rt.setJobName(String.valueOf(o[3]));
        rt.setSuccessNum(Integer.valueOf(o[4].toString()));
        rt.setErrorNum(Integer.valueOf(o[5].toString()));
        rt.setSuccessRate(new BigDecimal(o[6].toString()).setScale(2, RoundingMode.HALF_DOWN));
        rt.setErrorRate(new BigDecimal(100).subtract(rt.getSuccessRate()).setScale(2, RoundingMode.HALF_DOWN));
        rtList.add(rt);

    }
    return rtList;

}

From source file:org.dragoneronca.nlp.wol.domain.WolDomainContext.java

private void createIndices(EntityManager entityManager) {
    entityManager.getTransaction().begin();

    Query q1 = entityManager.createNativeQuery(
            "CREATE UNIQUE INDEX IF NOT EXISTS SENSESET_UNIQUE ON SenseSet (word, " + "language, POS)");
    q1.executeUpdate();/*from  w w w . ja v a  2 s  .c o  m*/
    Query q2 = entityManager.createNativeQuery(
            "CREATE UNIQUE INDEX IF NOT EXISTS SENSE_UNIQUE ON Sense (word, language, " + "POS, number)");
    q2.executeUpdate();
    /*Query q3 = entityManager.createNativeQuery(
        "CREATE UNIQUE INDEX IF NOT EXISTS SENSERELATION_UNIQUE ON SenseRelation (id," +
        " DTYPE)");
    q3.executeUpdate();
    Query q4 = entityManager.createNativeQuery(
        "CREATE INDEX IF NOT EXISTS SENSERELATION_DTYPE ON SenseRelation (DTYPE)");
    q4.executeUpdate();
    Query q5 = entityManager.createNativeQuery(
        "CREATE UNIQUE INDEX IF NOT EXISTS SENSESET_SENSERELATION_DERIVEDTERMS_ID ON " +
        "SenseSet_SenseRelation (SenseSet_id, DerivedTerms_id)");
    q5.executeUpdate();
    Query q6 = entityManager.createNativeQuery(
        "CREATE UNIQUE INDEX IF NOT EXISTS SENSESET_SENSERELATION_RELATEDTERMS_ID ON " +
        "SenseSet_SenseRelation (SenseSet_id, RelatedTerms_id)");
    q6.executeUpdate();
    Query q7 = entityManager.createNativeQuery(
        "CREATE UNIQUE INDEX IF NOT EXISTS SENSESET_SENSERELATION_SYNONYMS_ID ON " +
        "SenseSet_SenseRelation (SenseSet_id, Synonyms_id)");
    q7.executeUpdate();
    Query q8 = entityManager.createNativeQuery(
        "CREATE UNIQUE INDEX IF NOT EXISTS SENSESET_SENSERELATION_TRANSLATIONS_ID ON " +
        "SenseSet_SenseRelation (SenseSet_id, Translations_id)");
    q8.executeUpdate();*/

    entityManager.getTransaction().commit();
}

From source file:com.bitplan.vzjava.TestVZJPA.java

/**
 * test importing power VAlues in XML Format
 * //  w w w .j  a  v a  2  s . com
 * @throws Exception
 */
// https://github.com/WolfgangFahl/com.bitplan.vzjava/issues/5
@Test
public void testImportXml() throws Exception {
    // open the testdatabase
    VZDB vzdb = new VZDB("demo");
    // get the power values from the XML file
    File powerValueXmlFile = new File("src/test/data/vzdb/powervalues.xml");
    String xml = FileUtils.readFileToString(powerValueXmlFile);
    PowerValueManagerDao pvm = (PowerValueManagerDao) PowerValueManagerDao.getFactoryStatic().fromXML(xml);
    List<PowerValue> powerValues = pvm.getElements();
    // there should be 74669 power values in this test set
    assertEquals("xml import should have new Color(0x of records", 74669, powerValues.size());
    // delete existing data from the test database
    EntityManager em = vzdb.getEntityManager();
    em.getTransaction().begin();
    Query dquery = em.createNativeQuery("delete from data");
    dquery.executeUpdate();
    em.getTransaction().commit();

    String from = "2017-01-31 20:00:00";
    String to = "2017-03-24 14:00:00";
    em.getTransaction().begin();
    for (PowerValue powerValue : powerValues) {
        em.persist(powerValue);
    }
    em.getTransaction().commit();
    int channel = 4;
    ChannelMode channelMode = ChannelMode.Power;
    pvm.setVzdb(vzdb);
    List<PowerValue> dbPowerValues = pvm.get(from, to, channel, channelMode);
    assertTrue(String.format("database should have more than 74400 of imported records but has %5d",
            dbPowerValues.size()), dbPowerValues.size() > 74400);
}

From source file:com.easyjf.core.dao.impl.GenericDAOImpl.java

public int executeNativeSQL(final String nnq) {
    Object ret = this.getJpaTemplate().execute(new JpaCallback() {

        public Object doInJpa(EntityManager em) throws PersistenceException {
            Query query = em.createNativeQuery(nnq);
            return query.executeUpdate();
        }/*from   ww w . ja va  2 s .  c o m*/
    });
    return (Integer) ret;
}

From source file:org.opennaas.core.security.acl.ACLManager.java

private void executeSqlQuery(String sqlQuery) {
    log.debug("Executing SQL query: [ " + sqlQuery + " ]");
    EntityManager em = securityRepository.getEntityManager();
    EntityTransaction et = em.getTransaction();
    et.begin();/*from  w w w. jav a  2  s.co m*/
    try {
        em.createNativeQuery(sqlQuery).executeUpdate();
        em.flush();
        et.commit();
        log.debug("SQL query executed.");
    } catch (Exception e) {
        log.error("Error executing SQL query, rollbacking", e);
        et.rollback();
    }
}

From source file:com.easyjf.core.dao.impl.GenericDAOImpl.java

public List executeNativeQuery(final String nnq, final Object[] params, final int begin, final int max) {
    List ret = (List) this.getJpaTemplate().execute(new JpaCallback() {

        public Object doInJpa(EntityManager em) throws PersistenceException {
            Query query = em.createNativeQuery(nnq);
            int parameterIndex = 1;
            if (params != null && params.length > 0) {
                for (Object obj : params) {
                    query.setParameter(parameterIndex++, obj);
                }//from w  w w . j a  va 2  s  .  c o  m
            }
            if (begin >= 0 && max > 0) {
                query.setFirstResult(begin);
                query.setMaxResults(max);
            }
            return query.getResultList();
        }
    });
    if (ret != null && ret.size() >= 0) {
        return ret;
    } else {
        return new ArrayList();
    }
}

From source file:com.doculibre.constellio.wicket.pages.SQLPage.java

public SQLPage() {
    super();//from  ww w .j  a v a 2  s . c  o  m
    ConstellioSession session = ConstellioSession.get();
    ConstellioUser user = session.getUser();
    boolean redirect;
    if (user == null) {
        redirect = true;
    } else if (user.isAdmin()) {
        redirect = false;
    } else {
        redirect = true;
        RecordCollectionServices collectionServices = ConstellioSpringUtils.getRecordCollectionServices();
        for (RecordCollection collection : collectionServices.list()) {
            if (user.hasCollaborationPermission(collection) || user.hasAdminPermission(collection)) {
                redirect = false;
                break;
            }
        }
    }
    if (redirect) {
        setResponsePage(getApplication().getHomePage());
    } else {
        form = new Form("form");
        feedbackPanel = new FeedbackPanel("feedback");
        textArea = new TextArea("query", queryModel);
        submitButton = new Button("submitButton") {
            @Override
            public void onSubmit() {
                String sql = (String) queryModel.getObject();
                if (StringUtils.isNotBlank(sql)) {
                    EntityManager entityManager = ConstellioPersistenceContext.getCurrentEntityManager();
                    if (!entityManager.getTransaction().isActive()) {
                        entityManager.getTransaction().begin();
                    }
                    Query sqlQuery = entityManager.createNativeQuery(sql);
                    try {
                        int rowCount = sqlQuery.executeUpdate();
                        entityManager.getTransaction().commit();
                        info(rowCount + " " + getLocalizer().getString("affectedRows", this));
                    } catch (Exception e) {
                        String stack = ExceptionUtils.getFullStackTrace(e);
                        error(stack);
                    }
                }
            }
        };

        add(form);
        form.add(feedbackPanel);
        form.add(textArea);
        form.add(submitButton);
    }
}

From source file:de.iai.ilcd.webgui.controller.ConfigurationBean.java

/**
 * Use this constructor if not in the context of JSF; i.e. initialize context path by yourself
 * /*from   w w  w  .jav a2  s  .  c o m*/
 * @param contextPath
 *            the contextPath of application
 */
public ConfigurationBean(String contextPath) {
    this.contextPath = contextPath;

    this.templatePath = this.buildTemplatePath();

    this.themeName = conf.getString("theme");
    if (!this.getTemplatePath().endsWith("default")) {
        this.themeName = "none";
    }

    this.applicationTitle = conf.getString("title");
    this.appVersion = ConfigurationService.INSTANCE.getAppConfig().getString("version.tag");

    this.baseUri = ConfigurationService.INSTANCE.getBaseURI();
    this.registrationActivated = conf.getBoolean("user.registration.activated", false);
    this.selfActivation = conf.getBoolean("user.registration.selfActivation", false);

    String logoPath = conf.getString("logo");
    if (logoPath == null || logoPath.toLowerCase().trim().equals("false")) {
        logoPath = null;
    } else {
        logoPath = logoPath.replace("%contextPath%", this.contextPath);
    }
    this.logoPath = logoPath;

    // Config from DB for default stock:
    try {
        EntityManager em = PersistenceUtil.getEntityManager();
        Object[] confData = (Object[]) em.createNativeQuery(
                "SELECT `default_datastock_id`,`default_datastock_is_root` FROM `configuration` LIMIT 1")
                .getSingleResult();

        if (confData[0] instanceof Long) {
            this.defaultDataStockId = (Long) confData[0];
        }
        if (confData[1] instanceof Boolean) {
            this.defaultDataStockRoot = (Boolean) confData[1];
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

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

@Transactional(readOnly = false)
public void deleteCategory(final TicketCategory category) {
    this.jpaTemplate.execute(new JpaCallback() {
        public Object doInJpa(EntityManager em) throws PersistenceException {
            if (category.hasChildNodes()) {
                deleteChildNodes(category);
                category.setRight(category.getLeft() + 1);
            }/*from  www  . j  av  a2s  . c  o  m*/
            Query q1 = em.createNativeQuery(
                    "UPDATE ticket_category SET t_left=t_left-2 WHERE t_left>?1 AND t_left<?2");
            q1.setParameter(1, category.getRight());
            q1.setParameter(2, ((getNodeCount() * 2) + 1));
            q1.executeUpdate();

            Query q2 = em.createNativeQuery(
                    "UPDATE ticket_category SET t_right=t_right-2 WHERE t_right>=?1 AND t_right<=?2");
            q2.setParameter(1, category.getRight());
            q2.setParameter(2, (getNodeCount() * 2));
            q2.executeUpdate();

            Query q3 = em.createNativeQuery("DELETE FROM ticket_category WHERE category_id=?1");
            q3.setParameter(1, category.getTicketCategoryId());
            q3.executeUpdate();

            return null;
        }
    });
}

From source file:com.epam.training.taranovski.web.project.repository.implementation.EmployeeRepositoryImplementation.java

@Override
public List<Vacancy> getAvailableActiveVacancies(Employee employee) {
    EntityManager em = entityManagerFactory.createEntityManager();
    List<Integer> list = null;
    List<Vacancy> list1 = null;

    try {//from w  w  w  .  j  a v a 2s  .c om
        em.getTransaction().begin();

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

        TypedQuery<Vacancy> query1 = em.createNamedQuery("Vacancy.findActiveVacanciesByIds", Vacancy.class);
        query1.setParameter("vacancyIdList", list);
        list1 = query1.getResultList();

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

    return list1;
}