|
|
jpa查询进阶-查询结果对象封装
jpa单表数据封装对象很简单，但是如何连表查询时将返回数据封装成自己要的对象呢
一、使用@Query+原生sql查询
@Query(value = "select a.org_id,g.org_name,a.id,a.station_name,a.station_no,t.station_type,t.update_by,t.update_time " +
"from b_station a left join b_station_manage m on a.id=m.station_id " +
"left join b_org_station_attributes t on t.station_id=a.id and t.org_id= :#{#condition.orgId} " +
"left join b_organization g on a.org_id=g.id where a.org_id in :orgIdList and m.service_status=1 " +
"and IF(:#{#condition.stationName} != '',a.station_name like concat('%',:#{#condition.stationName},'%'),1=1 ) " +
"and IF(:#{#condition.stationType} !='',t.station_type= :#{#condition.stationType},1=1) ",
countQuery = "select count(*) " +
"from b_station a left join b_station_manage m on a.id=m.station_id " +
"left join b_org_station_attributes t on t.station_id=a.id and t.org_id= :#{#condition.orgId} " +
"left join b_organization g on a.org_id=g.id where a.org_id in :orgIdList and m.service_status=1 " +
"and if(:#{#condition.stationName} !='',a.station_name like concat('%',:#{#condition.stationName},'%'),1=1 ) " +
"and if(:#{#condition.stationType} !='',t.station_type= :#{#condition.stationType},1=1) ",
nativeQuery = true)
Page<Object[]> findAllByOrgId(@Param("orgIdList") List<Long> orgIdList, @Param("condition") StationAttributeCondition condition, @Param("pageable") Pageable pageable);
1.分页条件不需要拼在sql中，jpa会自动拼接。
2.使用if函数判断查询条件是否为空，jpa中''表示的就是空，效果等价于null，空字符串。
原生sql查询出来，分页用Page<Object[]>接收，不分页用List<Object[]>接收，麻烦的是接收到的对象不是我们想要的对象，我们还要将接收结果转换成我们想要的实体类。工具方法如下：
@Slf4j
public class convertEntityUtils{
* 将数组数据转换为实体类
* 此处数组元素的顺序必须与实体类构造函数中的属性顺序一致
* @param list 数组对象集合
* @param clazz 实体类
* @param <T> 实体类
* @param model 实例化的实体类
* @return 实体类集合
public static <T> List<T> castEntity(List<Object[]> list, Class<T> clazz, Object model) {
List<T> returnList = new ArrayList<T>();
if (list.isEmpty()) {
return returnList;
//获取每个数组集合的元素个数
Object[] co = list.get(0);
//获取当前实体类的属性名、属性值、属性类别
List<Map<String, Object>> attributeInfoList = getFiledsInfo(model);
//创建属性类别数组
Class[] c2 = new Class[attributeInfoList.size()];
//如果数组集合元素个数与实体类属性个数不一致则发生错误
if (attributeInfoList.size() != co.length) {
return returnList;
//确定构造方法
for (int i = 0; i < attributeInfoList.size(); i++) {
c2[i] = (Class) attributeInfoList.get(i).get("type");
try {
for (Object[] o : list) {
Constructor<T> constructor = clazz.getConstructor(c2);
T t = constructor.newInstance(o);
returnList.add(t);
} catch (Exception ex) {
log.error("实体数据转化为实体类发生异常：异常信息：{}", ex.getMessage());
return returnList;
return returnList;
* 根据属性名获取属性值
* @param fieldName 属性名
* @param modle 实体类
* @return 属性值
private static Object getFieldValueByName(String fieldName, Object modle) {
try {
String firstLetter = fieldName.substring(0, 1).toUpperCase();
String getter = "get" + firstLetter + fieldName.substring(1);
Method method = modle.getClass().getMethod(getter);
return method.invoke(modle);
} catch (Exception e) {
return null;
* 获取属性类型(type)，属性名(name)，属性值(value)的map组成的list
* @param model 实体类
* @return list集合
private static List<Map<String, Object>> getFiledsInfo(Object model) {
Field[] fields = model.getClass().getDeclaredFields();
List<Map<String, Object>> list = new ArrayList<>(fields.length);
Map<String, Object> infoMap;
for (Field field : fields) {
infoMap = new HashMap<>(3);
infoMap.put("type", field.getType());
infoMap.put("name", field.getName());
infoMap.put("value", getFieldValueByName(field.getName(), model));
list.add(infoMap);
return list;
需要注意的是，我们数据库是bigint类型的，实体类中写的long
类型，jpa返回的实际数据类型是BigInteger类型，如果字段返回是封装类型，使用该工具类会转换失败，方法就是把需要转换成的实体类的long改为BigInteger。
二、使用@Query+jpa 查询方式
返回map
@Query(value = "select new map(u.userName, ui.name, ui.gender, ui.description) from UserInfo ui, User u where u.id = ui.userId")
public List<Map<String, Object>> getCustomField();
@Query(value = "select new com.xx.xx.xx.xx.dto.UserInfo(u.userName, ui.name, ui.gender, ui.description) from UserInfo ui, User u where u.id = ui.userId")
public UserInfo getCustomInfo();
new com.xx.xx.xx.xx.dto.UserInfo，UserInfo类路径，表示查询的数据封装成UserInfo对象；
(u.userName, ui.name, ui.gender, ui.description) ，查询字段要和UserInfo中的字段名和顺序一致，避免出错。
三、使用jpa底层方法-EntityManager
这种如果方式，分页和条件查询都要自己进行sql拼接，不过好处是可以选择你想查询的部分字段进行封装对象。
以如下分页查询为例
Pagination<UserInfoDTO> userDtoPagination = new Pagination<>();
userDtoPagination .setCurrentPage(pageParam.getCurrentPage());
userDtoPagination .setPageSize(pageParam.getPageSize());
//sql拼接 sql换行时注意SQL间的空格，
StringBuilder sql = new StringBuilder("select count(id) from" +
" user_info where user_status=3 and id= :userId")
.append(" and is_normal=1").append(" and register_time between :beginTime and :endTime");
Query query = entityManager.createNativeQuery(sql.toString());
//参数设置
query.setParameter("userId", userId);
query.setParameter("beginTime", beginTime);
query.setParameter("endTime", endTime);
// 因为查询数量返回结果只有一个，用getSingleResult方法获取就可
int count = ((BigInteger) query.getSingleResult()).intValue();
if (count == 0) {
userDtoPagination .setRecords(Collections.emptyList());
userDtoPagination .setTotalRecord(0L);
userDtoPagination .setTotalPage(0);
return userDtoPagination ;
int startIndex = (pageParam.getCurrentPage() - 1) *pageParam.getPageSize();
sql = new StringBuilder()
.append("select user.user_no userNo,user.apply_time applyTime,user.account account from user_info user" +
"where user.account_status=3 and user._id= :userId")
.append(" and is_normal=1")
.append(" and register_time between :beginTime and :endTime")
.append("limit")
.append(startIndex).append(",")
.append(pageParam.getPageSize());
query = entityManager.createNativeQuery(sql.toString());
query.setParameter("userId", userId);
query.setParameter("beginTime", beginTime);
query.setParameter("endTime", endTime);
// 对查询返回数据封装成对象，注意addScalar中的字段名要与封装对象字段名一致，也要与sql查询出的字段名一致。
//比如UserInfo类中字段名叫username，则sql中
//select username from ..，.addScalar("username", StringType.STRING)
query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(UserInfoDTO.class))
.addScalar("userNo", StringType.INSTANCE)
.addScalar("applyTime", LocalDateTimeType.INSTANCE)
.addScalar("account", STRING.INSTANCE);
List<UserInfoDTO> resultList = (List<UserInfoDTO>) query.getResultList();
userDtoPagination.setRecords(resultList);
userDtoPagination.setTotalRecord((long) count);
int totalPage = count % pageParam.getPageSize() == 0 ? count / pageParam.getPageSize() :
count / pageParam.getPageSize() + 1;
orderDtoPagination.setTotalPage(totalPage);
return userDtoPagination;
四.网上看到一个jpa底层方法实现查询的工具类，感觉挺好，分享给大家
import lombok.extern.slf4j.Slf4j;
import org.hibernate.Session;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;
* 作者：guoyzh
* 时间：2019/8/20 12:53
* 功能：使用jpa进行原生sql查询的工具类 使用AutoWrite注入即可使用
@Slf4j
@SuppressWarnings("unchecked")
@Component
public class SqlUtils {
@Autowired
@PersistenceContext
private EntityManager entityManager;
public SqlUtils(EntityManager entityManager) {
this.entityManager = entityManager;
public SqlUtils() {
public void setEntityManager(EntityManager entityManager) {
this.entityManager = entityManager;
* 方法描述: 删除操作
* 非逻辑删除暂时这个方法
* @param sql sql语句
* @param condition 删除/修改操作
* @return int
* @author wqf
* @date 2021/4/1 14:06
public int modifyByCondition(String sql, Map<String, Object> condition) {
if (null != condition) {
for (Map.Entry<String, Object> entry : condition.entrySet()) {
sql = sql.replace(":" + entry.getKey(), entry.getValue() + "");
Query query = entityManager.createQuery(sql);
return query.executeUpdate();
* 返回查询的一个Record，没有则为null
public Record findFirst(String sql, Object... params) {
return findFirst(sql, Record.class, params);
public Record findFirst(String sql, Map<String, Object> searchMap) {
return findFirst(sql, Record.class, searchMap);
* 返回查询的一个实体，没有则为null
public <T> T findFirst(String sql, Class<T> clazz, Object... params) {
List<T> ts = find(sql, clazz, params);
return (ts == null || ts.size() == 0) ? null : ts.get(0);
public <T> T findFirst(String sql, Class<T> clazz, Map<String, Object> searchMap) {
List<T> ts = find(sql, clazz, searchMap);
return (ts == null || ts.size() == 0) ? null : ts.get(0);
public List<Record> find(String sql, Object... params) {
return find(sql, Record.class, params);
public List<Record> find(String sql, Map<String, Object> searchMap) {
return find(sql, Record.class, searchMap);
public List<Record> find(String sql) {
return find(sql, Record.class, (Map<String, Object>) null);
* 查询列表
* @param sql native sql语句，可以包含？
* @param clazz 返回的类型，可以是JavaBean，可以是Record
* @param params 参数列表
* @param <T> 泛型
* @return 查询列表结果
public <T> List<T> find(String sql, Class<T> clazz, Object... params) {
Session session = entityManager.unwrap(Session.class);
Query query = session.createSQLQuery(sql);
for (int i = 0; i < params.length; i++) {
query.setParameter(i, params[i]);
return getList(query, clazz);
* 查询列表
* @param sql native sql语句，可以包含 :具名参数
* @param clazz 返回的类型，可以是JavaBean，可以是Record
* @param searchMap 具名参数列表
* @param <T> 泛型
* @return 查询列表结果
public <T> List<T> find(String sql, Class<T> clazz, Map<String, Object> searchMap) {
Query query = entityManager.createNativeQuery(sql);
if (null != searchMap) {
searchMap.forEach(query::setParameter);
return getList(query, clazz);
* ----------------------------------------------record-positioned-parameter---------------------------------------------------
public Page<Record> paginate(String nativeSQL, int pageNumber, int pageSize, Object... params) {
String nativeCountSQL = getCountSQL(nativeSQL);
return paginate(null, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, params);
public Page<Record> paginate(String nativeSQL, Boolean isGroupBySql, int pageNumber, int pageSize, Object... params) {
String nativeCountSQL = getCountSQL(nativeSQL);
return paginate(isGroupBySql, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, params);
public Page<Record> paginate(String nativeSQL, String nativeCountSQL, int pageNumber, int pageSize, Object... params) {
return paginate(null, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, params);
public Page<Record> paginate(Boolean isGroupBySql, String nativeSQL, String nativeCountSQL, int pageNumber, int pageSize, Object... params) {
return paginate(isGroupBySql, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, params);
* ----------------------------------------------record-maped-parameter---------------------------------------------------
public Page<Record> paginate(String nativeSQL, int pageNumber, int pageSize, Map<String, Object> searchMap) {
String nativeCountSQL = getCountSQL(nativeSQL);
return paginate(null, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, searchMap);
public Page<Record> paginate(String nativeSQL, Boolean isGroupBySql, int pageNumber, int pageSize, Map<String, Object> searchMap) {
String nativeCountSQL = getCountSQL(nativeSQL);
return paginate(isGroupBySql, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, searchMap);
public Page<Record> paginate(String nativeSQL, String nativeCountSQL, int pageNumber, int pageSize, Map<String, Object> searchMap) {
return paginate(null, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, searchMap);
public Page<Record> paginate(Boolean isGroupBySql, String nativeSQL, String nativeCountSQL, int pageNumber, int pageSize, Map<String, Object> searchMap) {
return paginate(isGroupBySql, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, searchMap);
* ----------------------------------------------JavaBean-positioned-parameter---------------------------------------------------
public <T> Page<T> paginate(Boolean isGroupBySql, String nativeSQL, Class<T> clazz, int pageNumber, int pageSize, Object... params) {
String nativeCountSQL = getCountSQL(nativeSQL);
return paginate(isGroupBySql, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, params);
public <T> Page<T> paginate(String nativeSQL, String nativeCountSQL, Class<T> clazz, int pageNumber, int pageSize, Object... params) {
return paginate(null, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, params);
public <T> Page<T> paginate(String nativeSQL, Class<T> clazz, int pageNumber, int pageSize, String... params) {
String nativeCountSQL = getCountSQL(nativeSQL);
return paginate(null, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, params);
* ----------------------------------------------JavaBean-maped-parameter---------------------------------------------------
public <T> Page<T> paginate(String nativeSQL, Class<T> clazz, int pageNumber, int pageSize, Map<String, Object> searchMap) {
String nativeCountSQL = getCountSQL(nativeSQL);
return paginate(null, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, searchMap);
public <T> Page<T> paginate(Boolean isGroupBySql, String nativeSQL, Class<T> clazz, int pageNumber, int pageSize, Map<String, Object> searchMap) {
String nativeCountSQL = getCountSQL(nativeSQL);
return paginate(isGroupBySql, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, searchMap);
public <T> Page<T> paginate(String nativeSQL, String nativeCountSQL, Class<T> clazz, int pageNumber, int pageSize, Map<String, Object> searchMap) {
return paginate(null, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, searchMap);
* @param pageNumber pageNumber
* @param pageSize pageSize
* @param isGroupBySql 是否包含Group by语句，影响总行数
* @param nativeSQL 原生SQL语句 {@see QueryHelper}
* @param nativeCountSQL 原生求总行数的SQL语句 {@see QueryHelper}
* @param clazz JavaBean风格的DTO或者Record，需要用别名跟JavaBean对应
* @param <T> 返回JavaBean风格的DTO或者Record
* @param params 按照顺序给条件
public <T> Page<T> paginate(Boolean isGroupBySql, String nativeSQL, String nativeCountSQL, Class<T> clazz, int pageNumber, int pageSize, Object... params) {
if (pageNumber < 1 || pageSize < 1) {
throw new IllegalArgumentException("pageNumber and pageSize must more than 0");
Query countQuery = entityManager.createNativeQuery(nativeCountSQL);
//坑死人，1-Based
for (int i = 1; i <= params.length; i++) {
countQuery.setParameter(i, params[i - 1]);
List countQueryResultList = countQuery.getResultList();
int size = countQueryResultList.size();
if (isGroupBySql == null) {
isGroupBySql = size > 1;
long totalRow;
if (isGroupBySql) {
totalRow = size;
} else {
totalRow = (size > 0) ? ((Number) countQueryResultList.get(0)).longValue() : 0;
if (totalRow == 0) {
return new Page<>(new ArrayList<>(0), pageNumber, pageSize, 0, 0);
int totalPage = (int) (totalRow / pageSize);
if (totalRow % pageSize != 0) {
totalPage++;
if (pageNumber > totalPage) {
return new Page<>(new ArrayList<>(0), pageNumber, pageSize, totalPage, (int) totalRow);
Session session = entityManager.unwrap(Session.class);
int offset = pageSize * (pageNumber - 1);
Query query = session.createSQLQuery(nativeSQL).setFirstResult(offset).setMaxResults(pageSize);
//坑死人，0-Based
for (int i = 0; i < params.length; i++) {
query.setParameter(i, params[i]);
final List list = getList(query, clazz);
return new Page<T>(list, pageNumber, pageSize, totalPage, (int) totalRow);
* @param pageNumber pageNumber
* @param pageSize pageSize
* @param isGroupBySql 是否包含Group by语句，影响总行数
* @param nativeSQL 原生SQL语句 {@see QueryHelper}
* @param nativeCountSQL 原生求总行数的SQL语句 {@see QueryHelper}
* @param clazz JavaBean风格的DTO或者Record，需要用别名跟JavaBean对应
* @param <T> 返回JavaBean风格的DTO或者Record
* @param searchMap k-v条件
public <T> Page<T> paginate(Boolean isGroupBySql, String nativeSQL, String nativeCountSQL, Class<T> clazz, int pageNumber, int pageSize, Map<String, Object> searchMap) {
if (pageNumber < 1 || pageSize < 1) {
throw new IllegalArgumentException("pageNumber and pageSize must more than 0");
Query countQuery = entityManager.createNativeQuery(nativeCountSQL);
if (null != searchMap) {
searchMap.forEach(countQuery::setParameter);
List countQueryResultList = countQuery.getResultList();
int size = countQueryResultList.size();
if (isGroupBySql == null) {
isGroupBySql = size > 1;
long totalRow;
if (isGroupBySql) {
totalRow = size;
} else {
totalRow = (size > 0) ? ((Number) countQueryResultList.get(0)).longValue() : 0;
if (totalRow == 0) {
return new Page<>(new ArrayList<>(0), pageNumber, pageSize, 0, 0);
int totalPage = (int) (totalRow / pageSize);
if (totalRow % pageSize != 0) {
totalPage++;
if (pageNumber > totalPage) {
return new Page<>(new ArrayList<>(0), pageNumber, pageSize, totalPage, (int) totalRow);
Session session = entityManager.unwrap(Session.class);
int offset = pageSize * (pageNumber - 1);
Query query = session.createSQLQuery(nativeSQL).setFirstResult(offset).setMaxResults(pageSize);
if (null != searchMap) {
searchMap.forEach(query::setParameter);
final List list = getList(query, clazz);
return new Page<T>(list, pageNumber, pageSize, totalPage, (int) totalRow);
private <T> List getList(Query query, Class<T> clazz) {
final List list;
if (Object[].class == clazz) {
return query.getResultList();
query.unwrap(NativeQueryImpl.class)
.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
List mapList = query.getResultList();
list = new ArrayList(mapList.size());
try {
for (Object map : mapList) {
Map<String, Object> tmp = (Map<String, Object>) map;
//Record.class
if (Record.class == clazz) {
list.add(new Record(tmp));
//Map及子类
} else if (Map.class.isAssignableFrom(clazz)) {
list.add(tmp);
//JavaBean风格
} else {
list.add(Map2Bean.mapToBean(tmp, clazz));
} catch (Exception e) {
log.info("对象转换失败=====>>>", e);
return list;
private String getCountSQL(String sql) {
String countSQL = "SELECT COUNT(*) AS totalRow " + sql.substring(sql.toUpperCase().indexOf("FROM"));
return replaceOrderBy(countSQL);
protected static class Holder {
private static final Pattern ORDER_BY_PATTERN = Pattern.compile(
"order\\s+by\\s+[^,\\s]+(\\s+asc|\\s+desc)?(\\s*,\\s*[^,\\s]+(\\s+asc|\\s+desc)?)*",
Pattern.CASE_INSENSITIVE | Pattern.MULTILINE);
public String replaceOrderBy(String sql) {
return Holder.ORDER_BY_PATTERN.matcher(sql).replaceAll("");
import java.io.Serializable;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;
* Record
public class Record implements Serializable {
private static final long serialVersionUID = 905784513600884082L;
private Map<String, Object> columns = new HashMap<>();
public Record() {
public Record(Map<String, Object> columns) {
this.columns = columns;
public Map<String, Object> getColumns() {
return columns;
public Record setColumns(Map<String, Object> columns) {
this.getColumns().putAll(columns);
return this;
public Record setColumns(Record record) {
getColumns().putAll(record.getColumns());
return this;
public Record remove(String column) {
getColumns().remove(column);
return this;
public Record remove(String... columns) {
if (columns != null) {
for (String c : columns) {
this.getColumns().remove(c);
return this;
public Record removeNullValueColumns() {
for (java.util.Iterator<Map.Entry<String, Object>> it = getColumns().entrySet().iterator(); it.hasNext(); ) {
Map.Entry<String, Object> e = it.next();
if (e.getValue() == null) {
it.remove();
return this;
* Keep columns of this record and remove other columns.
* @param columns the column names of the record
public Record keep(String... columns) {
if (columns != null && columns.length > 0) {
Map<String, Object> newColumns = new HashMap<String, Object>(columns.length);
for (String c : columns) {
if (this.getColumns().containsKey(c)) {
newColumns.put(c, this.getColumns().get(c));
this.getColumns().clear();
this.getColumns().putAll(newColumns);
} else {
this.getColumns().clear();
return this;
* Keep column of this record and remove other columns.
* @param column the column names of the record
public Record keep(String column) {
if (getColumns().containsKey(column)) {
Object keepIt = getColumns().get(column);
getColumns().clear();
getColumns().put(column, keepIt);
} else {
getColumns().clear();
return this;
public Record clear() {
getColumns().clear();
return this;
public Record set(String column, Object value) {
getColumns().put(column, value);
return this;
public <T> T get(String column) {
return (T) getColumns().get(column);
public <T> T get(String column, Object defaultValue) {
Object result = getColumns().get(column);
return (T) (result != null ? result : defaultValue);
* Get column of mysql type: varchar, char, enum, set, text, tinytext, mediumtext, longtext
public String getStr(String column) {
return (String) getColumns().get(column);
* Get column of mysql type: int, integer, tinyint(n) n > 1, smallint, mediumint
public Integer getInt(String column) {
return (Integer) getColumns().get(column);
* Get column of mysql type: bigint
public Long getLong(String column) {
return (Long) getColumns().get(column);
* Get column of mysql type: unsigned bigint
public java.math.BigInteger getBigInteger(String column) {
return (java.math.BigInteger) getColumns().get(column);
* Get column of mysql type: date, year
public java.util.Date getDate(String column) {
return (java.util.Date) getColumns().get(column);
* Get column of mysql type: time
public java.sql.Time getTime(String column) {
return (java.sql.Time) getColumns().get(column);
* Get column of mysql type: timestamp, datetime
public java.sql.Timestamp getTimestamp(String column) {
return (java.sql.Timestamp) getColumns().get(column);
* Get column of mysql type: real, double
public Double getDouble(String column) {
return (Double) getColumns().get(column);
* Get column of mysql type: float
public Float getFloat(String column) {
return (Float) getColumns().get(column);
* Get column of mysql type: bit, tinyint(1)
public Boolean getBoolean(String column) {
return (Boolean) getColumns().get(column);
* Get column of mysql type: decimal, numeric
public java.math.BigDecimal getBigDecimal(String column) {
return (java.math.BigDecimal) getColumns().get(column);
* Get column of mysql type: binary, varbinary, tinyblob, blob, mediumblob, longblob
* I have not finished the test.
public byte[] getBytes(String column) {
return (byte[]) getColumns().get(column);
* Get column of any type that extends from Number
public Number getNumber(String column) {
return (Number) getColumns().get(column);
@Override
public String toString() {
StringBuilder sb = new StringBuilder();
sb.append(super.toString()).append(" {");
boolean first = true;
for (Map.Entry<String, Object> e : getColumns().entrySet()) {
if (first) {
first = false;
} else {
sb.append(", ");
Object value = e.getValue();
if (value != null) {
value = value.toString();
sb.append(e.getKey()).append(":").append(value);
sb.append("}");
return sb.toString();
@Override
public boolean equals(Object o) {
if (!(o instanceof Record)) {
return false;
if (o == this) {
return true;
return this.getColumns().equals(((Record) o).getColumns());
@Override
public int hashCode() {
return getColumns() == null ? 0 : getColumns().hashCode();
* Return column names of this record.
public String[] getColumnNames() {
Set<String> attrNameSet = getColumns().keySet();
return attrNameSet.toArray(new String[attrNameSet.size()]);
* Return column values of this record.
public Object[] getColumnValues() {
java.util.Collection<Object> attrValueCollection = getColumns().values();
return attrValueCollection.toArray(new Object[attrValueCollection.size()]);
* Return json string of this record.
public String toJson() {
throw new UnsupportedOperationException("还未实现");
import java.io.Serializable;
import java.util.List;
* Page is the authResult of Model.paginate(......) or Db.paginate(......)
public class Page<T> implements Serializable {
private static final long serialVersionUID = -5395997221963176643L;
private List<T> list; // list authResult of this page
private int pageNumber; // page number
private int pageSize = 10; // authResult amount of this page
private int totalPage; // total page
private int totalRow; // total row
public Page(int pageNumber) {
this.pageNumber = pageNumber;
* Constructor.
* @param list the list of paginate authResult
* @param pageNumber the page number
* @param pageSize the page size
* @param totalPage the total page of paginate
* @param totalRow the total row of paginate
public Page(List<T> list, int pageNumber, int pageSize, int totalPage, int totalRow) {
this.list = list;
this.pageNumber = pageNumber;
this.pageSize = pageSize;
this.totalPage = totalPage;
this.totalRow = totalRow;
public Page(int pageNumber, int pageSize) {
this.pageNumber = pageNumber;
this.pageSize = pageSize;
* Return list of this page.
public List<T> getList() {
return list;
* Return page number.
public int getPageNumber() {
return pageNumber;
* Return page size.
public int getPageSize() {
return pageSize;
* Return total page.
public int getTotalPage() {
totalPage = totalRow / pageSize;
if (totalRow % pageSize > 0) {
totalPage++;
return totalPage;
* Return total row.
public int getTotalRow() {
return totalRow;
public boolean isFirstPage() {
return pageNumber == 1;
public boolean isLastPage() {
return pageNumber == totalPage;
public void setList(List<T> list) {
this.list = list;
public void setPageNumber(int pageNumber) {
this.pageNumber = pageNumber;
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
public void setTotalRow(int totalRow) {
this.totalRow = totalRow;
@Override
public String toString() {
return "Page{" +
"list=" + list +
", pageNumber=" + pageNumber +
", pageSize=" + pageSize +
", totalPage=" + totalPage +
", totalRow=" + totalRow +
map转对象工具类
import java.lang.reflect.Field;
import java.util.Map;
* 将查询结果 map 封装成对应的javaBean，支持级联 ，但是属性不能重复
* 对应的javaBean的属性名必须以小驼峰形式命名，否则无法填充数据
public class Map2Bean {
private Map2Bean() {
* 利用反射将map集合封装成bean对象
* @param map 数据
* @param clazz 对象class
* @return
public static <T> T mapToBean(Map<String, Object> map, Class<?> clazz) throws Exception {
Object obj = clazz.newInstance();
if (map != null && !map.isEmpty()) {
map.size();
for (Map.Entry<String, Object> entry : map.entrySet()) {
// 属性名
String propertyName = entry.getKey();
// 属性值
Object value = entry.getValue();
String setMethodName = "set" + propertyName.substring(0, 1).toUpperCase() + propertyName.substring(1);
//获取和map的key匹配的属性名称
Field field = getClassField(clazz, propertyName);
if (field == null) {
continue;
Class<?> fieldTypeClass = field.getType();
value = convertValType(value, fieldTypeClass);
try {
clazz.getMethod(setMethodName, field.getType()).invoke(obj, value);
} catch (NoSuchMethodException e) {
e.printStackTrace();
return (T) obj;
* 根据给定对象类匹配对象中的特定字段
* @param clazz 字段类型
* @param fieldName 字段名称
* @return
private static Field getClassField(Class<?> clazz, String fieldName) {
if (Object.class.getName().equals(clazz.getName())) {
return null;
Field[] declaredFields = clazz.getDeclaredFields();
for (Field field : declaredFields) {
if (field.getName().equals(fieldName)) {
return field;
Class<?> superClass = clazz.getSuperclass(); //如果该类还有父类，将父类对象中的字段也取出
if (superClass != null) { //递归获取
return getClassField(superClass, fieldName);
return null;
* 将map的value值转为实体类中字段类型匹配的方法
* @param value 值
* @param fieldTypeClass 字段类型
* @return
private static Object convertValType(Object value, Class<?> fieldTypeClass) {
Object retVal = null;
if (Long.class.getName().equals(fieldTypeClass.getName())
|| long.class.getName().equals(fieldTypeClass.getName())) {
retVal = Long.parseLong(value.toString());
} else if (Integer.class.getName().equals(fieldTypeClass.getName())
|| int.class.getName().equals(fieldTypeClass.getName())) {
retVal = Integer.parseInt(value.toString());
} else if (Float.class.getName().equals(fieldTypeClass.getName())
|| float.class.getName().equals(fieldTypeClass.getName())) {
retVal = Float.parseFloat(value.toString());
} else if (Double.class.getName().equals(fieldTypeClass.getName())
|| double.class.getName().equals(fieldTypeClass.getName())) {
retVal = Double.parseDouble(value.toString());
} else {
retVal = value;
return retVal;
五、总结 ：jpa 的宗旨是减少原生sql，所以开发中尽量少用，因此这对数据库表的设计要求很高，要设计的合理。如果项目中遇到查询只需要关联一两个表的，还是分别查询出数据，然后将数据拼接较好。