Java Code Examples for javax.persistence.EntityManager#createNativeQuery()

/**
 * CommonDAO 内部方法, NativeQuery
 * 
 * @param entityManager
 *            entityManager
 * @param sql
 *            sql
 * @param values
 *            values
 * @return Query对象
 */
private Query createNativeQuery(EntityManager entityManager, String sql, Class resultClass, boolean mapResult,
		Map<String, Object> values) {
	Query query = null;
	if (resultClass != null) {
		query = entityManager.createNativeQuery(sql, resultClass);
	} else {
		query = entityManager.createNativeQuery(sql);
	}
	// 封装为Map结果
	if (mapResult) {
		query.unwrap(org.hibernate.Query.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
	}

	if (values != null && values.size() > 0) {
		for (Entry<String, Object> e : values.entrySet()) {
			query.setParameter(e.getKey(), e.getValue());
		}
	}
	return query;
}
 

@Test
public void adHocNativeQuery() {
	log.info("... adHocNativeQuery ...");

	EntityManager em = emf.createEntityManager();
	em.getTransaction().begin();

	Query q = em.createNativeQuery("SELECT * FROM book b WHERE id = ?", Book.class);
	q.setParameter(1, 1);
	Book b = (Book) q.getSingleResult();
	Assert.assertTrue(b instanceof Book);
	Assert.assertEquals(new Long(1), ((Book)b).getId());
	
	em.getTransaction().commit();
	em.close();
}
 

private <T> T executeQuery( String sql, String peerId )
{
    EntityManager em = daoManager.getEntityManagerFactory().createEntityManager();

    try
    {
        Query q = em.createNativeQuery( sql );

        q.setParameter( 1, peerId );

        return ( T ) q.getSingleResult();
    }
    catch ( Exception e )
    {
        LOG.error( "Error to execute query: ", e );
    }
    finally
    {
        daoManager.closeEntityManager( em );
    }

    return null;
}
 

/**
 * CommonDAO 内部方法, NativeQuery
 * 
 * @param entityManager
 *            entityManager
 * @param sql
 *            sql
 * @param values
 *            values
 * @return Query对象
 */
private Query createNativeQuery(EntityManager entityManager, String sql, Class resultClass, boolean mapResult,
		Map<String, Object> values) {
	Query query = null;
	if (resultClass != null) {
		query = entityManager.createNativeQuery(sql, resultClass);
	} else {
		query = entityManager.createNativeQuery(sql);
	}
	// 封装为Map结果
	if (mapResult) {
		query.unwrap(org.hibernate.Query.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
	}

	if (values != null && values.size() > 0) {
		for (Entry<String, Object> e : values.entrySet()) {
			query.setParameter(e.getKey(), e.getValue());
		}
	}
	return query;
}
 

public static List<String[]> executeNativeQueryWithCastCheck(String statement, EntityManager em) {
    Query query = em.createNativeQuery(statement);
    List results = query.getResultList();

    if (results.isEmpty()) {
        return new ArrayList<>();
    }

    if (results.get(0) instanceof String) {
        return ((List<String>) results).stream().map(s -> new String[] { s }).collect(Collectors.toList());
    } else {
        return (List<String[]>) results;
    }
}
 

/**
 * @see io.apiman.manager.api.core.IStorageQuery#listPolicyDefinitions()
 */
@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();
    }
}
 

private void handle(final DeleteRestApiVOMsg msg){
    int ret = 1;
    int delete = 0;
    EntityManager mgr = getEntityManager();
    EntityTransaction tran = mgr.getTransaction();
    Long time = TimeUnit.MILLISECONDS.toSeconds(System.currentTimeMillis()) - TimeUnit.DAYS.toSeconds(msg.getRetentionDay());
    long start = System.currentTimeMillis();
    try {
        while (ret > 0) {
            String sql = String.format("delete from RestAPIVO where unix_timestamp(lastOpDate) <= %d limit 1000", time);
            tran.begin();
            Query query = mgr.createNativeQuery(sql);
            ret = query.executeUpdate();
            tran.commit();
            delete = delete + ret;
            if (delete == 0) {
                logger.debug("no RestApiVO history to clean");
                return;
            }
        }
        logger.debug(String.format("delete %d days ago RestApiVO history %d, cost %d ms", msg.getRetentionDay(), delete, System.currentTimeMillis() - start));
    } catch (Exception e) {
        tran.rollback();
        logger.warn(String.format("unable to delete RestApiVO history because %s", e));
    } finally {
        mgr.close();
    }
}
 

/**
 * @see io.apiman.manager.api.core.IStorageQuery#listPlugins()
 */
@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();
    }
}
 

public static Map<String, Integer> getSortedMapForMultiRef(EntityManager entityManager, AdmCiTypeAttr attr, DynamicEntityMeta multRefMeta) {
    Map<String, Integer> sortMap = new HashMap<>();
    String joinTable = attr.retrieveJoinTalbeName();
    String querySql = "select id,from_guid,to_guid, seq_no from " + joinTable;
    Query query = entityManager.createNativeQuery(querySql, multRefMeta.getEntityClazz());
    List results = query.getResultList();

    for (Object bean : results) {
        BeanMap beanMap = new BeanMap(bean);
        sortMap.put((String) beanMap.get("to_guid"), (Integer) beanMap.get("seq_no"));
    }
    return sortMap;
}
 

public static void updateSeqNoForMultiReference(EntityManager entityManager, String curGuid, String joinTable, List<String> refGuids, int i, String refGuid) {
    StringBuilder updateSeqSqlBuilder = new StringBuilder();
    updateSeqSqlBuilder.append("update ").append(joinTable).append(" set seq_no = ").append(i + 1).append(" where from_guid = '").append(curGuid).append("' and ").append(" to_guid = '").append(refGuid).append("'");

    Query query = entityManager.createNativeQuery(updateSeqSqlBuilder.toString());
    int updateCount = query.executeUpdate();
    if (updateCount != 1) {
        throw new ServiceException(String.format("Failed to update seq_no for mult reference [from_guid:%s,to_guid:%s]", curGuid, refGuids.get(i)));
    }
}
 

/**
 * Gets the partitions.
 * @param tableId table id
 * @param partitionIds partition names
 * @param partitionParts parts
 * @param dateCreatedSqlCriteria criteria
 * @param sort sort
 * @param pageable pageable
 * @return list of partitions
 */
public List<Partition> getPartitions(final Long tableId, final List<String> partitionIds,
    final Iterable<String> partitionParts, final String dateCreatedSqlCriteria,
    final Sort sort, final Pageable pageable) {
    // Create the sql
    final StringBuilder queryBuilder = new StringBuilder(SQL_GET_PARTITIONS);
    if (partitionIds != null && !partitionIds.isEmpty()) {
        queryBuilder.append(" and p.name in ('")
            .append(Joiner.on("','").skipNulls().join(partitionIds))
            .append("')");
    }
    if (partitionParts != null) {
        for (String singlePartitionExpr : partitionParts) {
            queryBuilder.append(" and p.name like '%").append(singlePartitionExpr).append("%'");
        }
    }
    if (!Strings.isNullOrEmpty(dateCreatedSqlCriteria)) {
        queryBuilder.append(" and ").append(dateCreatedSqlCriteria);
    }
    if (sort != null && sort.hasSort()) {
        queryBuilder.append(" order by ").append(sort.getSortBy()).append(" ").append(sort.getOrder().name());
    }
    if (pageable != null && pageable.isPageable()) {
        queryBuilder.append(" limit ").append(pageable.getOffset()).append(',').append(pageable.getLimit());
    }
    // entityManager
    final EntityManager entityManager = em.get();
    final Query pQuery = entityManager.createNativeQuery(queryBuilder.toString(), Partition.class);
    pQuery.setParameter("tableId", tableId);
    return pQuery.getResultList();
}
 

/**
 * @see io.apiman.manager.api.core.IStorageQuery#listPluginPolicyDefs(java.lang.Long)
 */
@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();
    }
}
 

/**
 * Migrate the properties.
 */
@SuppressWarnings("rawtypes")
private static void migrateProperties(EntityManager em) {
    
    em.getTransaction().begin();
    Query propertyCountQuery = em.createNativeQuery("SELECT COUNT(MESSAGE_ID) FROM MESSAGE_PROPERTY", Integer.class);
    Integer propertyCount = (Integer) propertyCountQuery.getResultList().get(0);
    System.out.println("Number of headers=" + propertyCount);

    JpaMigrateQuery.executeUpdate(em, "ALTER TABLE PROPERTY ADD COLUMN MESSAGE_ID BIGINT");

    Query propertyQuery = em.createNativeQuery("SELECT MESSAGE_ID, PROPERTIES_ID FROM MESSAGE_PROPERTY");
    em.getTransaction().commit();

    DelegatingResultList propertyNameList = (DelegatingResultList) propertyQuery.getResultList();
    ResultList rl = propertyNameList.getDelegate();
    for (int i = 0; i < rl.size(); i++) {
        Object[] results = (Object[]) rl.get(i);
        Long messageId = (Long) results[0];
        Long propertyId = (Long) results[1];
        em.getTransaction().begin();
        Query update = em.createNativeQuery("UPDATE PROPERTY SET MESSAGE_ID = ? WHERE ID = ?");
        update.setParameter(1, messageId);
        update.setParameter(2, propertyId);
        int result = update.executeUpdate();
        System.out.printf("ExecuteUpdate returned a result=" + result + " for property %d of %d\n", i + 1, propertyCount);     
        em.getTransaction().commit();
    }
    
    em.getTransaction().begin();
    System.out.println("Creating index.");
    JpaMigrateQuery.executeUpdate(em, "CREATE INDEX SQL100727182411780 ON PROPERTY(MESSAGE_ID)");
    em.getTransaction().commit();
    
    em.getTransaction().begin();
    System.out.println("Creating foreign key.");
    JpaMigrateQuery.executeUpdate(em, "ALTER TABLE PROPERTY ADD CONSTRAINT SQL100727182411780 FOREIGN KEY (MESSAGE_ID) REFERENCES MESSAGE(ID)");
    em.getTransaction().commit();

    em.getTransaction().begin();
    System.out.println("Dropping table.");
    JpaMigrateQuery.executeUpdate(em, "DROP TABLE MESSAGE_PROPERTY");
    em.getTransaction().commit();
    
}
 

static public void disableH2Statistics(EntityManager em) {
    Query query = em.createNativeQuery("SET QUERY_STATISTICS False");
    query.executeUpdate();
}
 

protected int countRowsInTable(EntityManager em, String tableName) {
	Query query = em.createNativeQuery("SELECT COUNT(0) FROM " + tableName);
	return ((Number) query.getSingleResult()).intValue();
}
 

protected int countRowsInTable(EntityManager em, String tableName) {
	Query query = em.createNativeQuery("SELECT COUNT(0) FROM " + tableName);
	return ((Number) query.getSingleResult()).intValue();
}
 

public static <T> List<T> executeNativeQueryGeneric(String statement, String mapping, EntityManager em) {
    Query query = em.createNativeQuery(statement, mapping);
    return query.getResultList();
}
 

@Test
@DisableLikeWithLeadingWildcard
public void execute_select_who_started_with_like_wildcard() {

    EntityManager em = emf.createEntityManager();

    Query nativeQuery = em.createNativeQuery("SELECT * FROM Book b WHERE b.title LIKE  '%Ja'");

    nativeQuery.getResultList();

}
 

/**
 * 创建本地查询
 * @param sqlString 本地SQL查询字符串
 * @param resultSetMapping 结果集映射名称
 * @param entityManager 实体类管理器
 * @return Query
 */
public static Query nativeQuery(String sqlString, String resultSetMapping, EntityManager entityManager) {
	return entityManager.createNativeQuery(sqlString, resultSetMapping);
}
 

/**
 * 创建本地查询
 * @param sqlString 本地SQL查询字符串
 * @param entityManager 实体类管理器
 * @return Query
 */
public static Query nativeQuery(String sqlString, EntityManager entityManager) {
	return entityManager.createNativeQuery(sqlString);
}