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.remediatetheflag.global.persistence.HibernatePersistenceFacade.java

public Integer getFlagIdFromQuestionId(Integer idQuestion) {
    EntityManager em = getHibernateEntityManager();
    Integer id = null;/*from w  w w  .jav a  2s. c o m*/
    try {
        id = (Integer) em.createNativeQuery(
                "SELECT Flag_idFlag FROM flags_flagQuestions WHERE flagQuestionList_idFlagQuestion = :qst")
                .setParameter("qst", idQuestion).getSingleResult();
        em.close();
        return id;
    } catch (Exception e) {
        em.close();
        logger.error(e.getMessage());
        return id;
    }
}

From source file:com.remediatetheflag.global.persistence.HibernatePersistenceFacade.java

public void updateExerciseInstanceUsedHints(ExerciseInstance ei, FlagQuestionHint hint) {
    EntityManager em = getHibernateEntityManager();
    EntityTransaction et = em.getTransaction();
    et.begin();/* w w  w .j a  va  2s.  c o m*/
    try {
        em.createNativeQuery("INSERT INTO exerciseInstances_flagQuestionHints "
                + "(ExerciseInstance_idExerciseInstance,usedHints_idFlagQuestionHint) " + "VALUES (?,?)")
                .setParameter(1, ei.getIdExerciseInstance()).setParameter(2, hint.getId()).executeUpdate();
        et.commit();
        em.close();
    } catch (Exception e) {
        et.rollback();
        em.close();
        logger.error("Failed adding hint " + hint.getId() + " to exercise instance "
                + ei.getIdExerciseInstance() + " because of: \n" + e.getMessage());
    }

}

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

public List<String> getAllSearchableStrings() {
    log.info("getAllSearchableStrings...");
    List<String> allSearchableStrings = new ArrayList<String>();
    EntityManager entityManager = getEntityManager();
    try {//w w w.ja  v a 2  s.  co  m
        String sqlQuery = "select " + "ISNULL(TBLACCT.ACCOUNTNO, '') + ':' + "
                + "ISNULL(TBLACCT.PARCELNO, '') + ':' + "
                + "ISNULL(TBLPERSONSECURE.NAME1, '') + ' ' + ISNULL(TBLPERSONSECURE.NAME2, '') + ':' + "
                + "ISNULL(TBLACCT.BUSINESSNAME, '') + ':' + " + "ISNULL(TBLACCT.BUSINESSLICENSE, '') + ':' + "
                + "ISNULL(TBLACCTNBHD.NBHDCODE, '') + ':' + " + "ISNULL(TBLACCTNBHD.NBHDEXTENSION, '') + ':' + "
                + "ISNULL(TBLACCTPROPERTYADDRESS.STREETNO, '') + ' ' + ISNULL(TBLACCTPROPERTYADDRESS.STREETNAME, '') + ' ' + ISNULL(TBLACCTPROPERTYADDRESS.STREETTYPE, '') + ' ' + ISNULL(TBLACCTPROPERTYADDRESS.UNITNAME, '') + ':' + "
                + "ISNULL(TBLACCTPROPERTYADDRESS.PROPERTYCITY, '') + ':' + "
                + "ISNULL(SUBSTRING(TBLACCTPROPERTYADDRESS.PROPERTYZIPCODE, 1, 5), '') + ':' + "
                + "ISNULL(TBNSUBDIVISION.SUBNAME, '') " + "from encompass.TBLACCT TBLACCT "
                + "right outer join encompass.TBLACCTPROPERTYADDRESS TBLACCTPROPERTYADDRESS on TBLACCTPROPERTYADDRESS.ACCOUNTNO = TBLACCT.ACCOUNTNO "
                + "right outer join encompass.TBLACCTOWNERADDRESS TBLACCTOWNERADDRESS on TBLACCTOWNERADDRESS.ACCOUNTNO = TBLACCT.ACCOUNTNO "
                + "right outer join encompass.TBLPERSONSECURE TBLPERSONSECURE on TBLPERSONSECURE.PERSONCODE = TBLACCTOWNERADDRESS.PERSONCODE "
                + "right outer join encompass.TBLADDRESSSECURE TBLADDRESSSECURE on TBLADDRESSSECURE.ADDRESSCODE = TBLACCTOWNERADDRESS.ADDRESSCODE "
                + "right outer join encompass.TBLACCTNBHD TBLACCTNBHD on TBLACCTNBHD.ACCOUNTNO = TBLACCT.ACCOUNTNO "
                + "right outer join encompass.TBLACCTLEGAL TBLACCTLEGAL on TBLACCTLEGAL.ACCOUNTNO = TBLACCT.ACCOUNTNO "
                + "right outer join encompass.TBLSUBACCOUNT TBLSUBACCOUNT on TBLSUBACCOUNT.ACCOUNTNO = TBLACCT.ACCOUNTNO "
                + "right outer join encompass.TBNSUBDIVISION TBNSUBDIVISION on TBNSUBDIVISION.SUBNO = TBLSUBACCOUNT.SUBNO "
                + "where TBLACCT.verend = 99999999999 " + "and TBLADDRESSSECURE.verend = 99999999999 "
                + "and TBLPERSONSECURE.verend = 99999999999 " + "and TBLACCTOWNERADDRESS.verend = 99999999999 "
                + "and TBLACCTPROPERTYADDRESS.verend = 99999999999 " + "and TBLACCTNBHD.verend = 99999999999 "
                + "and TBLACCTLEGAL.verend = 99999999999 " + "and TBLSUBACCOUNT.verend = 99999999999 "
                + "order by TBLACCT.ACCOUNTNO desc, TBLACCT.PARCELNO desc";
        log.info("sqlQuery: " + sqlQuery);
        Query query = entityManager.createNativeQuery(sqlQuery);
        //query.setMaxResults(maxResults); //TODO This should pull all of them
        allSearchableStrings = query.getResultList();
        log.info("allSearchableStrings.size(): " + allSearchableStrings.size());
    } catch (Exception ex) {
        throw new RuntimeException(ex);
    } finally {
        entityManager.close();
    }
    return allSearchableStrings;
}

From source file:org.apache.ambari.server.state.cluster.ClusterTest.java

@Test
public void testDeleteServiceWithConfigHistory() throws Exception {
    createDefaultCluster();/*from w w  w  .j a  va 2s .co  m*/

    c1.addService("HDFS").persist();

    Config config1 = configFactory.createNew(c1, "hdfs-site", new HashMap<String, String>() {
        {
            put("a", "b");
        }
    }, new HashMap<String, Map<String, String>>());
    config1.setTag("version1");

    Config config2 = configFactory.createNew(c1, "core-site", new HashMap<String, String>() {
        {
            put("x", "y");
        }
    }, new HashMap<String, Map<String, String>>());
    config2.setTag("version2");

    config1.persist();
    c1.addConfig(config1);
    config2.persist();
    c1.addConfig(config2);

    Set<Config> configs = new HashSet<Config>();
    configs.add(config1);
    configs.add(config2);

    c1.addDesiredConfig("admin", configs);
    List<ServiceConfigVersionResponse> serviceConfigVersions = c1.getServiceConfigVersions();
    Assert.assertNotNull(serviceConfigVersions);
    // Single serviceConfigVersion for multiple configs
    Assert.assertEquals(1, serviceConfigVersions.size());
    Assert.assertEquals(Long.valueOf(1), serviceConfigVersions.get(0).getVersion());
    Assert.assertEquals(2, c1.getDesiredConfigs().size());
    Assert.assertEquals("version1", c1.getDesiredConfigByType("hdfs-site").getTag());
    Assert.assertEquals("version2", c1.getDesiredConfigByType("core-site").getTag());

    Map<String, Collection<ServiceConfigVersionResponse>> activeServiceConfigVersions = c1
            .getActiveServiceConfigVersions();
    Assert.assertEquals(1, activeServiceConfigVersions.size());

    c1.deleteService("HDFS");

    Assert.assertEquals(0, c1.getServices().size());
    Assert.assertEquals(0, c1.getServiceConfigVersions().size());

    EntityManager em = injector.getProvider(EntityManager.class).get();

    // ServiceConfig
    Assert.assertEquals(0, em.createQuery("SELECT serviceConfig from ServiceConfigEntity serviceConfig")
            .getResultList().size());
    // ClusterConfig
    Assert.assertEquals(2,
            em.createQuery("SELECT config from ClusterConfigEntity config").getResultList().size());
    // ClusterConfigMapping
    List<ClusterConfigMappingEntity> configMappingEntities = em
            .createQuery("SELECT configmapping from ClusterConfigMappingEntity configmapping",
                    ClusterConfigMappingEntity.class)
            .getResultList();

    Assert.assertEquals(2, configMappingEntities.size());

    for (ClusterConfigMappingEntity configMappingEntity : configMappingEntities) {
        if (StringUtils.equals(configMappingEntity.getType(), "core-site")) {
            assertEquals("core-site is not part of HDFS in test stack, should remain mapped to cluster", 1,
                    configMappingEntity.isSelected());
        }
        if (StringUtils.equals(configMappingEntity.getType(), "hdfs-site")) {
            assertEquals("hdfs-site should be unmapped from cluster when HDFS service is removed", 0,
                    configMappingEntity.isSelected());
        }
    }

    // ServiceConfigMapping
    Assert.assertEquals(0, em.createNativeQuery("SELECT * from serviceconfigmapping").getResultList().size());
}

From source file:edu.ucsb.eucalyptus.cloud.ws.WalrusManager.java

private void writeEvent(EntityManager em, final String eventType, final String account, final String opt,
        final String object_key, final Long size, final Boolean isFolder, final long syncid,
        final long lastModified, final String reqid, final int objseq) throws EucalyptusCloudException {

    Session sess = null;/*from  w  w w.j  a v a  2s. co  m*/
    try {
        sess = ((Session) em.getDelegate());
    } catch (Throwable t) {
        sess = null;
    }

    final java.sql.Timestamp mtime = new java.sql.Timestamp(lastModified);

    if ("webApp".equals(eventType)) {
        final String sql = "insert into web_opt_log(id,rectime,time,cssact,op,fpth,fsz,file_version,syncid,isfolder,rslt,inst_id, reqid) values(DEFAULT,CURRENT_TIMESTAMP AT TIME ZONE 'UTC +0',?,?,?,?,?,?,NULL,?,'succ','dummy',?)";
        if (sess != null) {
            sess.doWork(new Work() {
                public void execute(Connection connection) throws SQLException {
                    PreparedStatement stmt = null;
                    try {
                        stmt = connection.prepareStatement(sql);
                        stmt.setTimestamp(1, mtime);
                        stmt.setString(2, account);
                        stmt.setString(3, opt);
                        stmt.setString(4, object_key);
                        stmt.setLong(5, size);
                        stmt.setInt(6, objseq);
                        stmt.setBoolean(7, isFolder);
                        stmt.setString(8, reqid);
                        stmt.executeUpdate();
                    } finally {
                        if (stmt != null)
                            stmt.close();
                    }
                }
            });
        } else {
            em.createNativeQuery(sql).setParameter(1, mtime).setParameter(2, account).setParameter(3, opt)
                    .setParameter(4, object_key).setParameter(5, size).setParameter(6, objseq)
                    .setParameter(7, isFolder).setParameter(8, reqid).executeUpdate();
        }
    } else if ("mobileApp".equals(eventType)) {
    } else {
        final String sql = "insert into opt_log(id,rectime,time,cssact,op,fpth,fsz,file_version,syncid,isfolder,rslt,inst_id, reqid) values(DEFAULT,CURRENT_TIMESTAMP AT TIME ZONE 'UTC +0',?,?,?,?,?,?,?,?,'succ','dummy',?)";
        if (sess != null) {
            sess.doWork(new Work() {
                public void execute(Connection connection) throws SQLException {
                    PreparedStatement stmt = null;
                    try {
                        stmt = connection.prepareStatement(sql);
                        stmt.setTimestamp(1, mtime);
                        stmt.setString(2, account);
                        stmt.setString(3, opt);
                        stmt.setString(4, object_key);
                        stmt.setLong(5, size);
                        stmt.setInt(6, objseq);
                        stmt.setLong(7, syncid);
                        stmt.setBoolean(8, isFolder);
                        stmt.setString(9, reqid);
                        stmt.executeUpdate();
                    } finally {
                        if (stmt != null)
                            stmt.close();
                    }

                }
            }

            );
        } else {
            em.createNativeQuery(sql).setParameter(1, mtime).setParameter(2, account).setParameter(3, opt)
                    .setParameter(4, object_key).setParameter(5, size).setParameter(6, objseq)
                    .setParameter(7, syncid).setParameter(8, isFolder).setParameter(9, reqid).executeUpdate();
        }
    }
}

From source file:edu.ucsb.eucalyptus.cloud.ws.WalrusManager.java

private void doRenameEvent(EntityManager em, final String account, final String bucketName,
        final String object_key, final String renameTo) throws EucalyptusCloudException {

    Session sess = null;/*from  w  ww .  j  av a 2 s.  co  m*/
    try {
        sess = ((Session) em.getDelegate());
    } catch (Throwable t) {
        sess = null;
    }

    final String sql = "UPDATE objects SET object_key=regexp_replace(object_key, ?, ?, 'q') "
            + "where bucket_name = ? and owner_id = ? and (object_key LIKE ? or object_key = ?)";

    final String like_key = object_key.replaceAll("%", "\\%").replaceAll("_", "\\_");
    if (sess != null) {
        sess.doWork(new Work() {
            public void execute(Connection connection) throws SQLException {
                PreparedStatement stmt = null;
                try {
                    stmt = connection.prepareStatement(sql);
                    stmt.setString(1, object_key);
                    stmt.setString(2, renameTo);
                    stmt.setString(3, bucketName);
                    stmt.setString(4, account);
                    //stmt.setString(5, "^" + object_key + "/");
                    stmt.setString(5, like_key + "/%");
                    stmt.setString(6, object_key);
                    int exR = stmt.executeUpdate();
                    if (exR == 0) {
                        throw new SQLException("CssError", account + ":" + bucketName + ":" + object_key + "***"
                                + "no objects are renamed");
                    }
                } finally {
                    try {
                        if (stmt != null)
                            stmt.close();
                    } catch (Throwable t) {
                        /*NOP*/}
                }
            }
        });
    } else {
        int exR = em.createNativeQuery(sql).setParameter(1, object_key).setParameter(2, renameTo)
                .setParameter(3, bucketName).setParameter(4, account)
                //.setParameter(5, "^" + object_key + "/")
                .setParameter(5, like_key + "/%").setParameter(6, object_key).executeUpdate();
        if (exR == 0) {
            throw new AccessDeniedException("CssError",
                    account + ":" + bucketName + ":" + object_key + "***" + "no objects are renamed", true);
        }
    }
}

From source file:edu.ucsb.eucalyptus.cloud.ws.WalrusManager.java

private void writeRenameEvent(EntityManager em, final String eventType, final String account,
        final String bucketName, final String object_key, final String renameTo, final long syncid,
        final String reqid, final int objseq) throws EucalyptusCloudException {

    Session sess = null;//  w w  w.  j a v  a  2  s  .co  m
    try {
        sess = ((Session) em.getDelegate());
    } catch (Throwable t) {
        sess = null;
    }

    if ("webApp".equals(eventType)) {
        final String sql = "insert into web_opt_log(rectime,time,cssact,op,fpth,fsz,file_version,syncid,isfolder,rslt,inst_id,reqid)"
                + "select ?,to_timestamp(coalesce(value::bigint,0)/1000),?,'Rename',?,size,?,?,content_type='application/x-directory','succ','dummy',? from objects "
                + "obj left join metadata mtd on obj.object_name=mtd.object_id and mtd.\"name\"='mtime' "
                + "where object_key = ? AND bucket_name = ? AND owner_id = ? limit 1";

        if (sess != null) {
            sess.doWork(new Work() {
                public void execute(Connection connection) throws SQLException {
                    PreparedStatement stmt = null;
                    try {
                        stmt = connection.prepareStatement(sql);
                        Calendar c = Calendar.getInstance();
                        stmt.setTimestamp(1,
                                new Timestamp(c.getTimeInMillis() - c.getTimeZone().getRawOffset()));
                        stmt.setString(2, account);
                        stmt.setString(3, object_key + "||" + renameTo);
                        stmt.setInt(4, objseq);
                        stmt.setLong(5, syncid);
                        stmt.setString(6, reqid);
                        stmt.setString(7, renameTo);
                        stmt.setString(8, bucketName);
                        stmt.setString(9, account);
                        stmt.executeUpdate();
                    } finally {
                        try {
                            if (stmt != null)
                                stmt.close();
                        } catch (Throwable t) {
                            /*NOP*/}
                    }
                }
            });
        } else {
            Calendar c = Calendar.getInstance();
            em.createNativeQuery(sql)
                    .setParameter(1, new Timestamp(c.getTimeInMillis() - c.getTimeZone().getRawOffset()))
                    .setParameter(2, account).setParameter(3, object_key + "||" + renameTo)
                    .setParameter(4, objseq).setParameter(5, syncid).setParameter(6, reqid)
                    .setParameter(7, renameTo).setParameter(8, bucketName).setParameter(9, account)
                    .executeUpdate();
        }

    } else if ("mobileApp".equals(eventType)) {
    } else {

        final String sql = "insert into opt_log(rectime,time,cssact,op,fpth,fsz,file_version,syncid,isfolder,rslt,inst_id,reqid) "
                + "select ?,to_timestamp(coalesce(value::bigint,0)/1000),?,'Rename',?,size,?,?,content_type='application/x-directory','succ','dummy',? from objects "
                + "obj left join metadata mtd on obj.object_name=mtd.object_id and mtd.\"name\"='mtime' "
                + "where object_key = ? AND bucket_name = ? AND owner_id = ? limit 1";

        if (sess != null) {
            sess.doWork(new Work() {
                public void execute(Connection connection) throws SQLException {
                    PreparedStatement stmt = null;
                    try {
                        stmt = connection.prepareStatement(sql);
                        Calendar c = Calendar.getInstance();
                        stmt.setTimestamp(1,
                                new Timestamp(c.getTimeInMillis() - c.getTimeZone().getRawOffset()));
                        stmt.setString(2, account);
                        stmt.setString(3, object_key + "||" + renameTo);
                        stmt.setInt(4, objseq);
                        stmt.setLong(5, syncid);
                        stmt.setString(6, reqid);
                        stmt.setString(7, renameTo);
                        stmt.setString(8, bucketName);
                        stmt.setString(9, account);
                        stmt.executeUpdate();
                    } finally {
                        try {
                            if (stmt != null)
                                stmt.close();
                        } catch (Throwable t) {
                            /*NOP*/}
                    }
                }
            });
        } else {
            Calendar c = Calendar.getInstance();
            em.createNativeQuery(sql)
                    .setParameter(1, new Timestamp(c.getTimeInMillis() - c.getTimeZone().getRawOffset()))
                    .setParameter(2, account).setParameter(3, object_key + "||" + renameTo)
                    .setParameter(4, objseq).setParameter(5, syncid).setParameter(6, reqid)
                    .setParameter(7, renameTo).setParameter(8, bucketName).setParameter(9, account)
                    .executeUpdate();
        }
    }
}