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:org.apache.oozie.tools.OozieDBImportCLI.java

private static void checkDBVersion(EntityManager entityManager, ZipFile zipFile) throws IOException {
    try {/*from w ww.  ja  v a  2  s . c  om*/
        String currentDBVersion = (String) entityManager
                .createNativeQuery("select data from OOZIE_SYS where name='db.version'").getSingleResult();
        String dumpDBVersion = null;
        ZipEntry entry = zipFile.getEntry(OOZIEDB_SYS_INFO_JSON);
        BufferedReader reader = new BufferedReader(
                new InputStreamReader(zipFile.getInputStream(entry), "UTF-8"));
        String line;
        Gson gson = new Gson();
        while ((line = reader.readLine()) != null) {
            List<String> info = gson.fromJson(line, List.class);
            if (info.size() > 1 && "db.version".equals(info.get(0))) {
                dumpDBVersion = info.get(1);
            }
        }
        reader.close();
        if (currentDBVersion.equals(dumpDBVersion)) {
            System.out.println("Loading to Oozie database version " + currentDBVersion);
        } else {
            System.err.println("ERROR Oozie database version mismatch.");
            System.err.println("Oozie DB version:\t" + currentDBVersion);
            System.err.println("Dump DB version:\t" + dumpDBVersion);
            System.exit(1);
        }
    } catch (Exception e) {
        System.err.println();
        System.err.println("Error during DB version check: " + e.getMessage());
        System.err.println();
        System.err.println("Stack trace for the error was (for debug purposes):");
        System.err.println("--------------------------------------");
        e.printStackTrace(System.err);
        System.err.println("--------------------------------------");
        System.err.println();
    }
}

From source file:org.eclipse.jubula.client.core.persistence.TestResultPM.java

/**
 * execute delete-test-result of summary without commit
 * @param session Session//  ww  w  .  java2s  .c  om
 * @param resultId id of testresult-summary-entry, or <code>null</code> if 
 *                 all test results should be deleted.
 */
public static final void executeDeleteTestresultOfSummary(EntityManager session, Long resultId) {
    boolean isDeleteAll = resultId == null;

    //delete parameter details of test results
    String paramQueryBaseString = "delete from PARAMETER_DETAILS"; //$NON-NLS-1$
    if (isDeleteAll) {
        session.createNativeQuery(paramQueryBaseString).executeUpdate();
    } else {
        Query paramQuery = session.createNativeQuery(paramQueryBaseString
                + " where FK_TESTRESULT in (select ID from TESTRESULT where INTERNAL_TESTRUN_ID = #summaryId)"); //$NON-NLS-1$
        paramQuery.setParameter("summaryId", resultId); //$NON-NLS-1$
        paramQuery.executeUpdate();
    }

    //delete test result details
    StringBuilder resultQueryBuilder = new StringBuilder();
    resultQueryBuilder.append("delete from TestResultPO testResult"); //$NON-NLS-1$
    if (!isDeleteAll) {
        resultQueryBuilder.append(" where testResult.internalTestResultSummaryID = :id"); //$NON-NLS-1$
    }
    Query resultQuery = session.createQuery(resultQueryBuilder.toString());
    if (!isDeleteAll) {
        resultQuery.setParameter("id", resultId); //$NON-NLS-1$
    }
    resultQuery.executeUpdate();

}

From source file:com.github.gekoh.yagen.util.MappingUtils.java

public static List<String> getTableSequence(EntityManager oracleEm, boolean topDown) {
    return oracleEm.createNativeQuery("SELECT table_name\n" + "FROM (\n" + "  SELECT table_name, "
            + (topDown ? "max" : "min") + "(lvl) lvl\n" + "  FROM (\n" + "    WITH \n" + "    fk AS (\n"
            + "      SELECT uc.table_name, urc.table_name ref_table_name\n" + "      FROM\n"
            + "        user_constraints uc\n"
            + "        JOIN user_constraints urc ON uc.r_constraint_name=urc.constraint_name\n"
            + "      WHERE\n" + "        uc.table_name<>urc.table_name\n" + "    ),\n" + "    fks AS (\n"
            + "      SELECT table_name, ref_table_name\n" + "      FROM\n" + "        fk\n"
            + "      UNION ALL\n" + "      SELECT table_name, null ref_table_name\n" + "      FROM tabs\n"
            + "      WHERE\n" + "        table_name NOT IN (SELECT table_name FROM fk)\n"
            + "        AND table_name NOT LIKE 'IMP\\_%' ESCAPE '\\'\n" + "    )\n" + "    SELECT\n"
            + "      table_name, ref_table_name, level lvl\n" + "    FROM\n" + "      fks\n"
            + "    CONNECT BY NOCYCLE PRIOR table_name=ref_table_name\n"
            + "    START WITH ref_table_name IS null\n" + "  )\n" + "  GROUP BY table_name\n" + ")\n"
            + "ORDER BY lvl " + (topDown ? "asc" : "desc")).getResultList();
}

From source file:org.apache.oozie.tools.OozieDBExportCLI.java

private static void queryAllDBTables(String filename) throws StoreException, IOException {

    EntityManager manager = null;
    ZipOutputStream zos = null;//  www.  ja  va2 s. c  o  m
    File file = null;
    try {
        file = new File(filename);
        zos = new ZipOutputStream(new FileOutputStream(file));
        zos.setLevel(1);
        manager = Services.get().get(JPAService.class).getEntityManager();
        manager.setFlushMode(FlushModeType.COMMIT);

        int infoSize = exportTableToJSON(manager.createNativeQuery(GET_DB_VERSION), zos, OOZIEDB_SYS_INFO_JSON);
        System.out.println(infoSize + " rows exported from OOZIE_SYS");

        int wfjSize = exportTableToJSON(manager.createQuery(GET_WORKFLOW_JOBS), zos, OOZIEDB_WF_JSON);
        System.out.println(wfjSize + " rows exported from WF_JOBS");

        int wfaSize = exportTableToJSON(manager.createQuery(GET_WORKFLOW_ACTIONS), zos, OOZIEDB_AC_JSON);
        System.out.println(wfaSize + " rows exported from WF_ACTIONS");

        int cojSize = exportTableToJSON(manager.createQuery(GET_COORD_JOBS), zos, OOZIEDB_CJ_JSON);
        System.out.println(cojSize + " rows exported from COORD_JOBS");

        int coaSize = exportTableToJSON(manager.createQuery(GET_COORD_ACTIONS), zos, OOZIEDB_CA_JSON);
        System.out.println(coaSize + " rows exported from COORD_ACTIONS");

        int bnjSize = exportTableToJSON(manager.createQuery(GET_BUNDLE_JOBS), zos, OOZIEDB_BNJ_JSON);
        System.out.println(bnjSize + " rows exported from BUNDLE_JOBS");

        int bnaSize = exportTableToJSON(manager.createQuery(GET_BUNDLE_ACIONS), zos, OOZIEDB_BNA_JSON);
        System.out.println(bnaSize + " rows exported from BUNDLE_ACTIONS");

        int slaRegSize = exportTableToJSON(manager.createQuery(GET_SLA_REGISTRATIONS), zos,
                OOZIEDB_SLAREG_JSON);
        System.out.println(slaRegSize + " rows exported from SLA_REGISTRATION");

        int ssSize = exportTableToJSON(manager.createQuery(GET_SLA_SUMMARYS), zos, OOZIEDB_SLASUM_JSON);
        System.out.println(ssSize + " rows exported from SLA_SUMMARY");

    } catch (Exception e) {
        System.err.println("Error during dump creation: " + e.getMessage());
        System.err.println();
        e.printStackTrace(System.err);
        System.err.println();
        if (file != null) {
            file.delete();
        }
        System.exit(1);
    } finally {
        IOUtils.closeSafely(zos);
        if (manager != null) {
            manager.close();
        }
    }
}

From source file:org.esupportail.pay.domain.PayEvt.java

public static List<Object[]> findNbParticipantsByEvt() {
    EntityManager em = PayEvt.entityManager();
    String sql = "SELECT mail_subject, COUNT(evt) AS count, pay_evt.id FROM pay_transaction_log, pay_evt_montant, pay_evt "
            + "WHERE pay_transaction_log.pay_evt_montant = pay_evt_montant.id AND pay_evt_montant.evt = pay_evt.id  "
            + "GROUP BY pay_evt.id, pay_evt.mail_subject ORDER BY pay_evt.id DESC";
    Query q = em.createNativeQuery(sql);
    return q.getResultList();
}

From source file:org.esupportail.pay.domain.PayEvt.java

public static List<Object[]> findSumMontantGroupByEvt() {
    EntityManager em = PayEvt.entityManager();
    String sql = "SELECT mail_subject, SUM(cast(montant AS INTEGER)/100), pay_evt.id FROM pay_transaction_log, pay_evt_montant, pay_evt "
            + "WHERE pay_transaction_log.pay_evt_montant = pay_evt_montant.id AND pay_evt_montant.evt = pay_evt.id  "
            + "GROUP BY pay_evt.id, pay_evt.mail_subject ORDER BY pay_evt.id DESC";
    Query q = em.createNativeQuery(sql);
    return q.getResultList();
}

From source file:com.taobao.ad.easyschedule.dao.datatracking.impl.JPADataTrackingJobDAOImpl.java

public Double getDataTrackingData(final String trackingsql) {
    Object obj = this.getJpaTemplate().execute(new JpaCallback() {
        @Override//  w ww .j  a  va 2  s  .  c o m
        public Object doInJpa(EntityManager em) throws PersistenceException {
            return em.createNativeQuery(trackingsql).getSingleResult();
        }
    });
    return new Double(obj.toString());
}

From source file:eu.europa.ec.fisheries.uvms.spatial.service.dao.OracleUtilsDao.java

public OracleUtilsDao(EntityManager em) {
    this.em = em;
    mapDefaultSridToEpsg = em.createNativeQuery(MAP_DEFAULT_SRID_TO_EPSG);
    mapDefaultSridToEpsg.setMaxResults(1);
    mapEpsgToDefaultSrid = em.createNamedQuery(MAP_EPSG_TO_DEFAULT_SRID);
    mapEpsgToDefaultSrid.setMaxResults(1);
}

From source file:uk.ac.ebi.intact.editor.services.admin.HqlQueryService.java

@Transactional(value = "jamiTransactionManager", readOnly = true, propagation = Propagation.REQUIRED)
public <T> List<T[]> runNativeQuery(int maxResults, String sqlQuery) {
    maxResults = Math.min(maxResults, MAX_RESULTS);

    EntityManager em = getIntactDao().getEntityManager();
    Query query = em.createNativeQuery(sqlQuery);
    query.setMaxResults(maxResults);/*from  w  ww . j  av a2  s .  c o  m*/

    return query.getResultList();
}

From source file:fr.desbureaux.repository.ActivitiRepository.java

public String getActiveActiviti() {
    EntityManager em = this.entityManagerFactory.createEntityManager();
    try {/*from w  w w  . ja v  a2  s  . c  o  m*/
        Query query = em.createNativeQuery("select act_id_ from act_ru_execution");
        return (String) query.getSingleResult();
    } catch (NoResultException nre) {
        return "none";
    } finally {
        if (em != null) {
            em.close();
        }
    }
}