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:org.kuali.student.common.util.jpa.LoadSqlListener.java

private void process(String entityKey, String sqlFileName) {
    EntityManagerFactory emf = EntityManagerFactoryUtils.findEntityManagerFactory(applicationContext,
            entityKey);/*w  w  w.j  av a  2s . c o  m*/
    EntityManager em = SharedEntityManagerCreator.createSharedEntityManager(emf);

    File sqlFile;
    BufferedReader in;
    try {
        if (sqlFileName.startsWith("classpath:")) {
            sqlFile = new ClassPathResource(sqlFileName.substring("classpath:".length())).getFile();
        } else {
            sqlFile = new File(sqlFileName);
        }
        in = new BufferedReader(new FileReader(sqlFile));
    } catch (Exception e) {
        throw new RuntimeException(e);
    }

    String ln = "";

    TransactionDefinition txDefinition = new DefaultTransactionDefinition();
    TransactionStatus txStatus = jtaTxManager.getTransaction(txDefinition);

    try {
        while ((ln = in.readLine()) != null) {
            if (!ln.startsWith("/") && !ln.startsWith("--") && StringUtils.isNotBlank(ln)) {
                ln = ln.replaceFirst("[;/]\\s*$", "");
                em.createNativeQuery(ln).executeUpdate();
            }
        }
        jtaTxManager.commit(txStatus);
    } catch (Exception e) {
        logger.error("Error loading sql file " + sqlFileName + ". Failing statement was '" + ln + "'", e);
        jtaTxManager.rollback(txStatus);
    } finally {
        try {
            in.close();
        } catch (IOException e) {
            logger.error("IO Stream closed " + e);
        }
    }
}

---

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

/**
 * Update internal db statistics after mass changes in the db.
 * This is often needed to help the query optimizer doing the right things.
 *///from   w  w  w.j  a  v a2 s. c  om
public void updateDbStatistics() {
    String cmd = dbConnectionInfo.getStatisticsCommand();
    if (cmd != null) {
        EntityManager em = openSession();
        try {
            Query q = em.createNativeQuery(cmd);
            EntityTransaction tx = getTransaction(em);
            q.executeUpdate();
            commitTransaction(em, tx);
        } catch (Throwable t) {
            log.error("Updating DB statistics failed. This isn't critical,  but will degrade performance.", t); //$NON-NLS-1$
        } finally {
            dropSession(em);
        }
    }
}

---

From source file:com.formkiq.web.AbstractIntegrationTest.java

/**
 * Migrate System Properties from migration scripts.
 * @param em {@link EntityManager}//from w  w w.  jav a 2 s  .c  o  m
 */
private void migrateSystemProperties(final EntityManager em) {

    this.systemDao.save(new SystemProperty("version", "0.0"));
    this.systemDao.save(new SystemProperty("inviteonly", "true"));
    this.systemDao.save(new SystemProperty("assetservice_db", "true"));
    this.systemDao.save(new SystemProperty("assetservice_s3", "false"));

    em.flush();

    try {
        List<String> sqls = getDBSystemPropertiesMigrationScripts();
        for (String sql : sqls) {
            em.createNativeQuery(sql).executeUpdate();
        }
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

---

From source file:us.co.douglas.assessor.dao.AccountDAOImpl.java

public List<String> getAllPropertyAddresses() {
    log.info("getAllPropertyAddresses...");
    List<String> allPropertyAddresses = new ArrayList<String>();
    EntityManager entityManager = getEntityManager();
    try {//from  w w  w.java2 s . c o  m
        String sqlQuery = "select " + "ISNULL(TBLACCT.ACCOUNTNO, '') + "
                + "' ' + ISNULL(TBLACCT.PARCELNO, '') + " + "' ' + ISNULL(TBLACCT.BUSINESSNAME, '') + "
                + "' ' + ISNULL(TBLACCT.BUSINESSLICENSE, '') + "
                + "' ' + ISNULL(TBLACCTPROPERTYADDRESS.STREETNO, '') + "
                + "' ' + ISNULL(TBLACCTPROPERTYADDRESS.UNITNAME, '') + "
                + "' ' + ISNULL(TBLACCTPROPERTYADDRESS.STREETTYPE, '') + "
                + "' ' + ISNULL(TBLACCTPROPERTYADDRESS.STREETNAME, '') + "
                + "' ' + ISNULL(SUBSTRING(TBLACCTPROPERTYADDRESS.PROPERTYZIPCODE, 1, 5), '') + "
                + "' ' + ISNULL(TBLACCTPROPERTYADDRESS.PROPERTYCITY, '') " + "from "
                + "encompass.TBLACCT TBLACCT "
                + "join encompass.TBLACCTPROPERTYADDRESS TBLACCTPROPERTYADDRESS on TBLACCTPROPERTYADDRESS.ACCOUNTNO = TBLACCT.ACCOUNTNO "
                + "where TBLACCT.verend = 99999999999 and TBLACCTPROPERTYADDRESS.verend = 99999999999 order by TBLACCT.ACCOUNTNO desc ";
        log.info("sqlQuery: " + sqlQuery);
        Query query = entityManager.createNativeQuery(sqlQuery);
        query.setMaxResults(maxResults);
        allPropertyAddresses = query.getResultList();
    } catch (Exception ex) {
        throw new RuntimeException(ex);
    } finally {
        entityManager.close();
    }
    return allPropertyAddresses;
}

---

From source file:org.apache.ambari.server.upgrade.UpgradeCatalog210.java

private void updateAlertDefinitionEntitySource(final String alertName, final String source,
        final String newHash) {
    executeInTransaction(new Runnable() {
        @Override/*from   w  w w .  ja  v a  2s .com*/
        public void run() {
            EntityManager em = getEntityManagerProvider().get();
            Query nativeQuery = em.createNativeQuery(
                    "UPDATE alert_definition SET alert_source=?1, hash=?2 WHERE " + "definition_name=?3");
            nativeQuery.setParameter(1, source);
            nativeQuery.setParameter(2, newHash);
            nativeQuery.setParameter(3, alertName);
            nativeQuery.executeUpdate();
        }
    });
}

---

From source file:io.apiman.manager.api.jpa.JpaStorage.java

/**
 * @see io.apiman.manager.api.core.IStorageQuery#listGateways()
 */// www .  j  av a 2  s  .  co m
@Override
public List<GatewaySummaryBean> listGateways() throws StorageException {
    beginTx();
    try {
        EntityManager entityManager = getActiveEntityManager();

        String sql = "SELECT g.id, g.name, g.description, g.type" + "  FROM gateways g"
                + " ORDER BY g.name ASC";
        Query query = entityManager.createNativeQuery(sql);

        List<Object[]> rows = query.getResultList();
        List<GatewaySummaryBean> gateways = new ArrayList<>(rows.size());
        for (Object[] row : rows) {
            GatewaySummaryBean gateway = new GatewaySummaryBean();
            gateway.setId(String.valueOf(row[0]));
            gateway.setName(String.valueOf(row[1]));
            gateway.setDescription(String.valueOf(row[2]));
            gateway.setType(GatewayType.valueOf(String.valueOf(row[3])));
            gateways.add(gateway);
        }
        return gateways;
    } catch (Throwable t) {
        logger.error(t.getMessage(), t);
        throw new StorageException(t);
    } finally {
        rollbackTx();
    }
}

---

From source file:io.apiman.manager.api.jpa.JpaStorage.java

/**
 * @see io.apiman.manager.api.core.IStorageQuery#listPluginPolicyDefs(java.lang.Long)
 *//*from  ww  w. j  av  a  2  s  . co  m*/
@Override
public List<PolicyDefinitionSummaryBean> listPluginPolicyDefs(Long pluginId) throws StorageException {
    beginTx();
    try {
        EntityManager entityManager = getActiveEntityManager();

        String sql = "SELECT pd.id, pd.policy_impl, pd.name, pd.description, pd.icon, pd.plugin_id, pd.form_type"
                + "  FROM policydefs pd" + " WHERE pd.plugin_id = ?" + " ORDER BY pd.name ASC";
        Query query = entityManager.createNativeQuery(sql);
        query.setParameter(1, pluginId);

        List<Object[]> rows = query.getResultList();
        List<PolicyDefinitionSummaryBean> beans = new ArrayList<>(rows.size());
        for (Object[] row : rows) {
            PolicyDefinitionSummaryBean bean = new PolicyDefinitionSummaryBean();
            bean.setId(String.valueOf(row[0]));
            bean.setPolicyImpl(String.valueOf(row[1]));
            bean.setName(String.valueOf(row[2]));
            bean.setDescription(String.valueOf(row[3]));
            bean.setIcon(String.valueOf(row[4]));
            if (row[5] != null) {
                bean.setPluginId(((Number) row[5]).longValue());
            }
            if (row[6] != null) {
                bean.setFormType(PolicyFormType.valueOf(String.valueOf(row[6])));
            }
            beans.add(bean);
        }
        return beans;
    } catch (Throwable t) {
        logger.error(t.getMessage(), t);
        throw new StorageException(t);
    } finally {
        rollbackTx();
    }
}

---

From source file:io.apiman.manager.api.jpa.JpaStorage.java

/**
 * @see io.apiman.manager.api.core.IStorage#getPlugin(java.lang.String, java.lang.String)
 *//*from  w w w .ja v a2  s  .co  m*/
@Override
public PluginBean getPlugin(String groupId, String artifactId) throws StorageException {
    try {
        EntityManager entityManager = getActiveEntityManager();

        String sql = "SELECT p.id, p.artifact_id, p.group_id, p.version, p.classifier, p.type, p.name, p.description, p.created_by, p.created_on, p.deleted"
                + "  FROM plugins p" + " WHERE p.group_id = ? AND p.artifact_id = ?";
        Query query = entityManager.createNativeQuery(sql);
        query.setParameter(1, groupId);
        query.setParameter(2, artifactId);
        List<Object[]> rows = query.getResultList();
        if (!rows.isEmpty()) {
            Object[] row = rows.get(0);
            PluginBean plugin = new PluginBean();
            plugin.setId(((Number) row[0]).longValue());
            plugin.setArtifactId(String.valueOf(row[1]));
            plugin.setGroupId(String.valueOf(row[2]));
            plugin.setVersion(String.valueOf(row[3]));
            plugin.setClassifier((String) row[4]);
            plugin.setType((String) row[5]);
            plugin.setName(String.valueOf(row[6]));
            plugin.setDescription(String.valueOf(row[7]));
            plugin.setCreatedBy(String.valueOf(row[8]));
            plugin.setCreatedOn((Date) row[9]);
            plugin.setDeleted((Boolean) row[10]);
            return plugin;
        } else {
            return null;
        }
    } catch (Throwable t) {
        logger.error(t.getMessage(), t);
        throw new StorageException(t);
    }
}

---

From source file:io.apiman.manager.api.jpa.JpaStorage.java

/**
 * @see io.apiman.manager.api.core.IStorageQuery#listPolicyDefinitions()
 *///from   w  w  w.j av  a 2 s. c o m
@Override
public List<PolicyDefinitionSummaryBean> listPolicyDefinitions() throws StorageException {
    beginTx();
    try {
        EntityManager entityManager = getActiveEntityManager();

        String sql = "SELECT pd.id, pd.policy_impl, pd.name, pd.description, pd.icon, pd.plugin_id, pd.form_type"
                + "  FROM policydefs pd" + " WHERE pd.deleted IS NULL OR pd.deleted = 0"
                + " ORDER BY pd.name ASC";
        Query query = entityManager.createNativeQuery(sql);

        List<Object[]> rows = query.getResultList();
        List<PolicyDefinitionSummaryBean> rval = new ArrayList<>(rows.size());
        for (Object[] row : rows) {
            PolicyDefinitionSummaryBean bean = new PolicyDefinitionSummaryBean();
            bean.setId(String.valueOf(row[0]));
            bean.setPolicyImpl(String.valueOf(row[1]));
            bean.setName(String.valueOf(row[2]));
            bean.setDescription(String.valueOf(row[3]));
            bean.setIcon(String.valueOf(row[4]));
            if (row[5] != null) {
                bean.setPluginId(((Number) row[5]).longValue());
            }
            if (row[6] != null) {
                bean.setFormType(PolicyFormType.valueOf(String.valueOf(row[6])));
            }
            rval.add(bean);
        }
        return rval;
    } catch (Throwable t) {
        logger.error(t.getMessage(), t);
        throw new StorageException(t);
    } finally {
        rollbackTx();
    }
}

---

From source file:io.apiman.manager.api.jpa.JpaStorage.java

/**
 * @see io.apiman.manager.api.core.IStorageQuery#listPlugins()
 *//*  w  w  w . ja v  a 2 s .  co m*/
@Override
public List<PluginSummaryBean> listPlugins() throws StorageException {
    beginTx();
    try {
        EntityManager entityManager = getActiveEntityManager();

        String sql = "SELECT p.id, p.artifact_id, p.group_id, p.version, p.classifier, p.type, p.name, p.description, p.created_by, p.created_on"
                + "  FROM plugins p" + " WHERE p.deleted IS NULL OR p.deleted = 0" + " ORDER BY p.name ASC";
        Query query = entityManager.createNativeQuery(sql);

        List<Object[]> rows = query.getResultList();
        List<PluginSummaryBean> plugins = new ArrayList<>(rows.size());
        for (Object[] row : rows) {
            PluginSummaryBean plugin = new PluginSummaryBean();
            plugin.setId(((Number) row[0]).longValue());
            plugin.setArtifactId(String.valueOf(row[1]));
            plugin.setGroupId(String.valueOf(row[2]));
            plugin.setVersion(String.valueOf(row[3]));
            plugin.setClassifier((String) row[4]);
            plugin.setType((String) row[5]);
            plugin.setName(String.valueOf(row[6]));
            plugin.setDescription(String.valueOf(row[7]));
            plugin.setCreatedBy(String.valueOf(row[8]));
            plugin.setCreatedOn((Date) row[9]);
            plugins.add(plugin);
        }
        return plugins;
    } catch (Throwable t) {
        logger.error(t.getMessage(), t);
        throw new StorageException(t);
    } finally {
        rollbackTx();
    }
}

---

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