下面列出了javax.persistence.EntityManager#createNativeQuery ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
/**
* 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;
}
/**
* @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();
}
}
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;
}
}
/**
* 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();
}
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)));
}
}
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;
}
/**
* @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();
}
}
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#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();
}
}
protected int countRowsInTable(EntityManager em, String tableName) {
Query query = em.createNativeQuery("SELECT COUNT(0) FROM " + tableName);
return ((Number) query.getSingleResult()).intValue();
}
/**
* 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();
}
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);
}