在使用hibernate的entitymanager.createnativequery()方法执行原生sql查询时，hibernate会将数据库返回的原始数据类型映射为相应的java对象类型。例如，数据库中的varchar类型通常会映射为java的string，int或bigint会映射为integer或long，date或timestamp则会映射为java.util.date或java.sql.timestamp。需要注意的是，hibernate不会直接返回java.sql.jdbctype枚举，而是返回其对应的java对象。
createNativeQuery()的getResultList()方法根据查询的列数返回不同类型的列表：
由于原生查询的动态性，我们可能无法预先知道每个列的具体Java类型。在这种情况下，Java的instanceof运算符是判别运行时数据类型的有效工具。通过检查结果集中每个元素的类型，我们可以安全地进行类型转换和后续处理。
以下是处理原生SQL查询结果并判别数据类型的示例代码：
import jakarta.persistence.EntityManager;
import jakarta.persistence.Query;
import java.math.BigDecimal; // 可能返回的数字类型
import java.util.Date;
import java.util.List;
public class NativeQueryResultProcessor {
private final EntityManager em;
public NativeQueryResultProcessor(EntityManager em) {
this.em = em;
}
/**
* 执行原生SQL查询并处理结果的数据类型。
*
* @param sqlQuery 要执行的原生SQL语句。
*/
public void processNativeQueryResult(String sqlQuery) {
// 创建原生查询
Query query = em.createNativeQuery(sqlQuery);
// 执行查询并获取结果列表
List<?> results = query.getResultList();
if (results == null || results.isEmpty()) {
System.out.println("查询结果为空。");
return;
}
// 假设我们处理的是多列查询，返回 List<Object[]>
// 如果是单列查询，results.get(0) 将直接是 Object 类型
if (results.get(0) instanceof Object[]) {
System.out.println("处理多列查询结果 (List<Object[]>):");
for (Object row : results) {
Object[] columns = (Object[]) row;
System.out.print("行数据: [");
for (int i = 0; i < columns.length; i++) {
Object columnValue = columns[i];
String typeName = "未知类型";
String valueString = "null";
if (columnValue != null) {
if (columnValue instanceof String) {
typeName = "String";
valueString = (String) columnValue;
} else if (columnValue instanceof Integer) {
typeName = "Integer";
valueString = String.valueOf((Integer) columnValue);
} else if (columnValue instanceof Long) {
typeName = "Long";
valueString = String.valueOf((Long) columnValue);
} else if (columnValue instanceof Double) {
typeName = "Double";
valueString = String.valueOf((Double) columnValue);
} else if (columnValue instanceof Float) {
typeName = "Float";
valueString = String.valueOf((Float) columnValue);
} else if (columnValue instanceof BigDecimal) {
typeName = "BigDecimal"; // 数据库DECIMAL/NUMERIC可能映射为BigDecimal
valueString = ((BigDecimal) columnValue).toPlainString();
} else if (columnValue instanceof Date) {
typeName = "Date"; // java.util.Date 或其子类 java.sql.Timestamp
valueString = ((Date) columnValue).toString();
} else if (columnValue instanceof Boolean) {
typeName = "Boolean";
valueString = String.valueOf((Boolean) columnValue);
}
// 可以根据需要添加更多类型判断
else {
typeName = columnValue.getClass().getName();
valueString = columnValue.toString();
}
}
System.out.print(String.format("列%d: %s (类型: %s)", i + 1, valueString, typeName));
if (i < columns.length - 1) {
System.out.print(", ");
}
}
System.out.println("]");
}
} else {
// 处理单列查询，返回 List<Object>
System.out.println("处理单列查询结果 (List<Object>):");
for (Object value : results) {
String typeName = "未知类型";
String valueString = "null";
if (value != null) {
if (value instanceof String) {
typeName = "String";
valueString = (String) value;
} else if (value instanceof Number) { // 涵盖 Integer, Long, Double, Float, BigDecimal
typeName = value.getClass().getSimpleName();
valueString = String.valueOf(value);
// 如果需要特定数字类型，可以进一步判断
// Long longValue = ((Number) value).longValue();
} else if (value instanceof Date) {
typeName = "Date";
valueString = ((Date) value).toString();
}
// 添加其他类型判断
else {
typeName = value.getClass().getName();
valueString = value.toString();
}
}
System.out.println(String.format("值: %s (类型: %s)", valueString, typeName));
}
}
}
// 示例用法
public static void main(String[] args) {
// 假设em是一个已经配置好的EntityManager实例
// EntityManager em = Persistence.createEntityManagerFactory("your-persistence-unit").createEntityManager();
// NativeQueryResultProcessor processor = new NativeQueryResultProcessor(em);
// 模拟一个EntityManager，实际应用中应注入或获取真实的EntityManager
EntityManager mockEm = new MockEntityManager(); // 假设存在一个MockEntityManager
NativeQueryResultProcessor processor = new NativeQueryResultProcessor(mockEm);
// 示例1: 多列查询
System.out.println("--- 示例1: 多列查询 ---");
String multiColumnSql = "SELECT name, age, salary, hire_date, is_active FROM employees WHERE id < 3";
processor.processNativeQueryResult(multiColumnSql);
System.out.println("\n--- 示例2: 单列查询 ---");
// 示例2: 单列查询
String singleColumnSql = "SELECT name FROM employees WHERE id = 1";
processor.processNativeQueryResult(singleColumnSql);
System.out.println("\n--- 示例3: 包含NULL值的查询 ---");
// 示例3: 包含NULL值的查询
String nullValueSql = "SELECT name, null_column FROM employees WHERE id = 1"; // 假设null_column始终为NULL
processor.processNativeQueryResult(nullValueSql);
// em.close(); // 实际应用中需要关闭EntityManager
}
// 模拟EntityManager和Query，用于测试
static class MockEntityManager implements EntityManager {
// 简化实现，仅用于演示
@Override
public Query createNativeQuery(String sqlString) {
return new MockQuery(sqlString);
}
// 其他方法省略...
@Override
public void persist(Object entity) {}
@Override
public <T> T merge(T entity) { return null; }
@Override
public void remove(Object entity) {}
@Override
public <T> T find(Class<T> entityClass, Object primaryKey) { return null; }
@Override
public <T> T getReference(Class<T> entityClass, Object primaryKey) { return null; }
@Override
public void flush() {}
@Override
public void setFlushMode(jakarta.persistence.FlushModeType flushMode) {}
@Override
public jakarta.persistence.FlushModeType getFlushMode() { return null; }
@Override
public void lock(Object entity, jakarta.persistence.LockModeType lockMode) {}
@Override
public void lock(Object entity, jakarta.persistence.LockModeType lockMode, java.util.Map<String, Object> properties) {}
@Override
public void refresh(Object entity) {}
@Override
public void refresh(Object entity, java.util.Map<String, Object> properties) {}
@Override
public void refresh(Object entity, jakarta.persistence.LockModeType lockMode) {}
@Override
public void refresh(Object entity, jakarta.persistence.LockModeType lockMode, java.util.Map<String, Object> properties) {}
@Override
public void clear() {}
@Override
public boolean contains(Object entity) { return false; }
@Override
public jakarta.persistence.Query createQuery(String qlString) { return null; }
@Override
public <T> jakarta.persistence.TypedQuery<T> createQuery(String qlString, Class<T> resultClass) { return null; }
@Override
public jakarta.persistence.Query createNamedQuery(String name) { return null; }
@Override
public <T> jakarta.persistence.TypedQuery<T> createNamedQuery(String name, Class<T> resultClass) { return null; }
@Override
public jakarta.persistence.Query createNativeQuery(String sqlString, Class resultClass) { return null; }
@Override
public jakarta.persistence.Query createNativeQuery(String sqlString, String resultSetMapping) { return null; }
@Override
public jakarta.persistence.StoredProcedureQuery createNamedStoredProcedureQuery(String name) { return null; }
@Override
public jakarta.persistence.StoredProcedureQuery createStoredProcedureQuery(String procedureName) { return null; }
@Override
public jakarta.persistence.StoredProcedureQuery createStoredProcedureQuery(String procedureName, Class... resultClasses) { return null; }
@Override
public jakarta.persistence.StoredProcedureQuery createStoredProcedureQuery(String procedureName, String... resultSetMappings) { return null; }
@Override
public void joinTransaction() {}
@Override
public boolean isJoinedToTransaction() { return false; }
@Override
public <T> T unwrap(Class<T> cls) { return null; }
@Override
public Object getDelegate() { return null; }
@Override
public void close() {}
@Override
public boolean isOpen() { return false; }
@Override
public jakarta.persistence.EntityTransaction getTransaction() { return null; }
@Override
public jakarta.persistence.EntityManagerFactory getEntityManagerFactory() { return null; }
@Override
public java.util.Map<String, Object> getProperties() { return null; }
@Override
public jakarta.persistence.Query createQuery(jakarta.persistence.criteria.CriteriaQuery criteriaQuery) { return null; }
@Override
public jakarta.persistence.Query createQuery(jakarta.persistence.criteria.CriteriaUpdate updateQuery) { return null; }
@Override
public jakarta.persistence.Query createQuery(jakarta.persistence.criteria.CriteriaDelete deleteQuery) { return null; }
@Override
public jakarta.persistence.StoredProcedureQuery createStoredProcedureQuery(String procedureName, jakarta.persistence.ParameterMode... modes) { return null; }
@Override
public jakarta.persistence.StoredProcedureQuery createStoredProcedureQuery(String procedureName, String resultSetMapping, jakarta.persistence.ParameterMode... modes) { return null; }
@Override
public jakarta.persistence.StoredProcedureQuery createStoredProcedureQuery(String procedureName, Class resultClass, jakarta.persistence.ParameterMode... modes) { return null; }
@Override
public jakarta.persistence.EntityGraph<?> getEntityGraph(String graphName) { return null; }
@Override
public <T> jakarta.persistence.EntityGraph<T> createEntityGraph(Class<T> rootType) { return null; }
@Override
public jakarta.persistence.EntityGraph<?> createEntityGraph(String graphName) { return null; }
@Override
public jakarta.persistence.Cache retrieveCache() { return null; }
@Override
public void setProperty(String propertyName, Object value) {}
}
static class MockQuery implements Query {
private final String sql;
public MockQuery(String sql) {
this.sql = sql;
}
@Override
public List getResultList() {
// 根据SQL模拟返回不同结果
if (sql.contains("SELECT name, age, salary, hire_date, is_active FROM employees")) {
return List.of(
new Object[]{"Alice", 30, new BigDecimal("60000.50"), new Date(), true},
new Object[]{"Bob", 24, new BigDecimal("45000.00"), new Date(), false}
);
} else if (sql.contains("SELECT name FROM employees WHERE id = 1")) {
return List.of("Alice");
} else if (sql.contains("SELECT name, null_column FROM employees WHERE id = 1")) {
return List.of(new Object[]{"Alice", null});
}
return List.of();
}
// 其他方法省略...
@Override
public int executeUpdate() { return 0; }
@Override
public Query setMaxResults(int maxResult) { return this; }
@Override
public int getMaxResults() { return 0; }
@Override
public Query setFirstResult(int startPosition) { return this; }
@Override
public int getFirstResult() { return 0; }
@Override
public Query setHint(String hintName, Object value) { return this; }
@Override
public java.util.Map<String, Object> getHints() { return null; }
@Override
public <T> Query setParameter(jakarta.persistence.Parameter<T> param, T value) { return this; }
@Override
public Query setParameter(jakarta.persistence.Parameter<Calendar> param, Calendar value, jakarta.persistence.TemporalType temporalType) { return this; }
@Override
public Query setParameter(jakarta.persistence.Parameter<Date> param, Date value, jakarta.persistence.TemporalType temporalType) { return this; }
@Override
public Query setParameter(String name, Object value) { return this; }
@Override
public Query setParameter(String name, Calendar value, jakarta.persistence.TemporalType temporalType) { return this; }
@Override
public Query setParameter(String name, Date value, jakarta.persistence.TemporalType temporalType) { return this; }
@Override
public Query setParameter(int position, Object value) { return this; }
@Override
public Query setParameter(int position, Calendar value, jakarta.persistence.TemporalType temporalType) { return this; }
@Override
public Query setParameter(int position, Date value, jakarta.persistence.TemporalType temporalType) { return this; }
@Override
public java.util.Set<jakarta.persistence.Parameter<?>> getParameters() { return null; }
@Override
public jakarta.persistence.Parameter<?> getParameter(String name) { return null; }
@Override
public <T> jakarta.persistence.Parameter<T> getParameter(String name, Class<T> type) { return null; }
@Override
public jakarta.persistence.Parameter<?> getParameter(int position) { return null; }
@Override
public <T> jakarta.persistence.Parameter<T> getParameter(int position, Class<T> type) { return null; }
@Override
public boolean isBound(jakarta.persistence.Parameter<?> param) { return false; }
@Override
public <T> T getParameterValue(jakarta.persistence.Parameter<T> param) { return null; }
@Override
public Object getParameterValue(String name) { return null; }
@Override
public Object getParameterValue(int position) { return null; }
@Override
public Query setFlushMode(jakarta.persistence.FlushModeType flushMode) { return this; }
@Override
public jakarta.persistence.FlushModeType getFlushMode() { return null; }
@Override
public Query setLockMode(jakarta.persistence.LockModeType lockMode) { return this; }
@Override
public jakarta.persistence.LockModeType getLockMode() { return null; }
@Override
public <T> T unwrap(Class<T> cls) { return null; }
@Override
public jakarta.persistence.criteria.CriteriaQuery getCriteriaQuery() { return null; }
@Override
public jakarta.persistence.criteria.CriteriaUpdate getCriteriaUpdate() { return null; }
@Override
public jakarta.persistence.criteria.CriteriaDelete getCriteriaDelete() { return null; }
@Override
public java.util.stream.Stream getResultStream() { return null; }
@Override
public Object getSingleResult() { return null; }
}
}在上述代码中，我们首先判断results.get(0)的类型来区分单列查询和多列查询。然后，在循环遍历结果时，对每个列的值使用instanceof进行类型检查，并根据检查结果进行相应的处理。这使得代码能够灵活地适应不同数据类型的列。
以上就是Hibernate原生SQL查询结果数据类型判别与处理的详细内容，更多请关注php中文网其它相关文章！
每个人都需要一台速度更快、更稳定的 PC。随着时间的推移，垃圾文件、旧注册表数据和不必要的后台进程会占用资源并降低性能。幸运的是，许多工具可以让 Windows 保持平稳运行。
广告Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号
789
收藏
