- 关于JPA写原生SQL一直很有争议,大部分说JPA不适合写SQL,或者对于SQL的支持很垃圾,但是在我使用JPA的过程中,发现JPA写SQL其实还可以,那么咱们就讨论下JPA原生SQL的写法.
关于JPA原生SQL是采用entityManager类进行查询,当前查询可以返回Map<Object,Object>对象,当然也可以返回List<T>对象.
定义原生接口
BaseDao.java
/**
* 这里为了演示JPA原生sql,就先定义个空接口, 如果运用到项目中,这里可以定义
* 一些公共的查询方法,如简单的单表增删改查
* 如: public T select(String id);
* public List<T> selectT(String id);
* .....
**/
public interface BaseDao<T> {
}
BaseDaoIml.java
@Repository
public class BaseDaoImpl<T> implements BaseDao<T> {
@PersistenceContext
private EntityManager entityManager;
private Class<?> clz;
public Class<?> getClz() {
if (clz == null) {
// 获取泛型的Class对象
clz = ((Class<?>) (((ParameterizedType) (this.getClass().getGenericSuperclass())).getActualTypeArguments()[0]));
}
return clz;
}
public List<Object[]> listBySQL(String sql) {
return null;
}
public Integer getCount(String sql, Map<String, Object> maps) {
Query query = entityManager.createNativeQuery(sql);
setAliasParameter(query, maps);
BigInteger count = new BigInteger("0");
try {
count = (BigInteger) query.getSingleResult();
} catch (NoResultException ex) {
}
return count.intValue();
}
public List<Object[]> executeObject(String queryStr) {
Query query = entityManager.createNativeQuery(queryStr);
List<Object[]> resultList = query.getResultList();
return resultList;
}
public Page<T> executeNativeQuery(String queryStr, QueryConditionVo queryConditionVo) {
return executeNativeQuery(queryStr, queryConditionVo, null);
}
public T executeNativeQueryObject(String querySQL) {
return executeNativeQueryObject(querySQL, null);
}
public List<T> executeNativeQuery(String sql, Object... params) {
Query query = entityManager.createNativeQuery(sql);
setParameter(query, params);
query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(getClz()));
List<T> resultList = query.getResultList();
return resultList;
}
public List<T> executeNativeQuery(String sql, Map<String, Object> alias) {
Query query = entityManager.createNativeQuery(sql);
setAliasParameter(query, alias);
query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(getClz()));
List<T> resultList = query.getResultList();
return resultList;
}
public T executeNativeQueryObject(String querySQL, Object... param) {
Query query = entityManager.createNativeQuery(querySQL);
query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(getClz()));
setParameter(query, param);
return (T) query.getSingleResult();
}
public <N extends Object> Page<T> executeNativeQuery(String queryStr, QueryConditionVo queryConditionVo, Map<String, Object> maps) {
return executeNativeQuery(queryStr, queryConditionVo, maps, null);
}
public <N extends Object> Page<T> executeNativeQuery(String queryStr, QueryConditionVo queryConditionVo, Map<String, Object> alias, Object... params) {
String cq = getCountHql(queryStr);
queryStr = initSort(queryStr, queryConditionVo);
Query query = entityManager.createNativeQuery(queryStr);
Query cquery = entityManager.createNativeQuery(cq);
query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(getClz()));
setAliasParameter(query, alias);
setAliasParameter(cquery, alias);
setParameter(query, params);
setParameter(cquery, params);
BigInteger total = new BigInteger("0");
try {
total = (BigInteger) cquery.getSingleResult();
} catch (NoResultException ex) {
}
int page = queryConditionVo.getPagination().getPage();
int size = queryConditionVo.getPagination().getSize();
query.setFirstResult(page * size);
query.setMaxResults(size);
List resultList = query.getResultList();
Pageable pageable = new PageRequest(page, size);
return new PageImpl<T>(resultList, pageable, total.longValue());
}
private void setParameter(Query query, Object[] args) {
if (args != null && args.length > 0) {
int index = 1;
for (Object arg : args) {
if (arg == null) {
continue;
}
query.setParameter(index++, arg);
}
}
}
private void setAliasParameter(Query query, Map<String, Object> alias) {
if (alias != null) {
Set<String> keys = alias.keySet();
for (String key : keys) {
Object val = alias.get(key);
if (val instanceof Collection) {
// 查询条件是列表
query.setParameter(key, (Collection) val);
} else {
query.setParameter(key, val);
}
}
}
}
private String initSort(String hql, QueryConditionVo vo) {
if (vo != null && null != vo.getSort() && vo.getSort().size() > 0) {
Map<String, String> map = vo.getSort();
for (String key : map.keySet()) {
hql += " order by " + key + " " + map.get(key);
}
}
return hql;
}
protected String getCountHql(String hql) {
String e = hql.substring(hql.toLowerCase().indexOf("from"));
String c = "select ifnull(count(*),0) " + e;
return c;
}
public Integer findBySql(String sql, Object... args) {
Query query = entityManager.createNativeQuery(sql);
int i = 0;
for (Object arg : args) {
query.setParameter(++i, arg);
}
return query.getResultList().size();
}
public Integer findBySql(String sql, Map<String, Object> args) {
Query query = entityManager.createNativeQuery(sql);
setAliasParameter(query, args);
return query.getResultList().size();
}
}
以上就是原生SQL的具体实现.关于调用,请看下面的例子
public interface IDataSourceCustomRepository extends BaseDao<DataSourceModel> {
Page<DataSourceModel> findDataSourceByAdmin(QueryConditionVo queryConditionVo, User user);
Page<DataSourceModel> findDataSourceByGuest(QueryConditionVo queryConditionVo, User user);
}
@Repository
public class DataSourceCustomRepository extends BaseDaoImpl<DataSourceModel> implements IDataSourceCustomRepository {
@Override
public Page<DataSourceModel> findDataSourceByAdmin(QueryConditionVo queryConditionVo, User user) {
StringBuilder sql = new StringBuilder("SELECT T.* FROM ( SELECT " +
" mds.id, " +
" DATE_FORMAT(mds.gmt_create,'%Y-%m-%d %H:%i:%s') AS gmtCreate, " +
" mds.back_ground_color AS backGroundColor, " +
" mds.data_list_name AS dataListName, " +
" mds.data_list_path AS dataListPath, " +
" mds.data_source_content AS dataSourceContent, " +
" mds.data_source_name AS dataSourceName, " +
" if(mds.data_source_state = 'PRIVATE','PUBLIC','PUBLIC')AS dataSourceState, " +
" IF( 1, 'ADMIN' ,'ADMIN') AS userState," +
" mds.font_path AS fontPath, " +
" mds.layers_num AS layersNum, " +
" mds.map_thumbnail_name AS mapThumbnailName, " +
" mds.map_thumbnail_path AS mapThumbnailPath, " +
" mds.remark AS remark, " +
" mds.province AS province, " +
" mds.city AS city, " +
" mds.area AS area, " +
" mds.create_by_id AS createBy ," +
" mds.is_deleted AS isDeleted, " +
" mds.address AS address," +
" mds.resource_state AS resourceState," +
" (select COUNT(*) from m_project_datasource where mds.id=datasource_id) projectCount " +
" FROM " +
" m_data_source AS mds ) AS T WHERE 1=1 and T.isDeleted='N' ");
SearchDataSourceModel searchDataSourceModel = JSON.parseObject(queryConditionVo.getParams(), SearchDataSourceModel.class);
Map<String, Object> map = Maps.newHashMap();
return getDataSourceModels(queryConditionVo, searchDataSourceModel, sql, map);
}
@Transactional
public Page<DataSourceModel> findDataSourceByGuest(QueryConditionVo queryConditionVo, User user) {
SearchDataSourceModel searchDataSourceModel = JSON.parseObject(queryConditionVo.getParams(), SearchDataSourceModel.class);
StringBuilder sql = new StringBuilder("SELECT T.* FROM ( SELECT " +
" mds.id, " +
" DATE_FORMAT(mds.gmt_create,'%Y-%m-%d %H:%i:%s') AS gmtCreate, " +
" mds.back_ground_color AS backGroundColor, " +
" mds.data_list_name AS dataListName, " +
" mds.data_list_path AS dataListPath, " +
" mds.data_source_content AS dataSourceContent, " +
" mds.data_source_name AS dataSourceName, " +
"IF " +
" ( " +
" mdsp.application_enum IS NULL, " +
" mds.data_source_state, " +
" mdsp.application_enum " +
" ) AS dataSourceState, " +
" mds.font_path AS fontPath, " +
" mds.layers_num AS layersNum, " +
" mds.map_thumbnail_name AS mapThumbnailName, " +
" mds.map_thumbnail_path AS mapThumbnailPath, " +
" mds.remark AS remark, " +
" mds.province AS province, " +
" mds.city AS city, " +
" mds.area AS area, " +
" mds.create_by_id AS createBy ," +
" mds.is_deleted AS isDeleted, " +
" mds.address AS address," +
" mds.resource_state AS resourceState," +
" (select COUNT(*) from m_project_datasource where mds.id=datasource_id) projectCount " +
" FROM " +
" m_data_source AS mds " +
" LEFT JOIN ( SELECT * FROM m_data_source_application WHERE application_user =:userId AND apply_current_enum='CURRENT_APPLY') AS mdsp ON mds.id = mdsp.data_source_id " +
" WHERE mds.is_deleted = 'N' ) AS T WHERE 1=1 and T.isDeleted='N' ");
Map<String, Object> map = Maps.newHashMap();
map.put("userId", user.getId());
return getDataSourceModels(queryConditionVo, searchDataSourceModel, sql, map);
}
private Page<DataSourceModel> getDataSourceModels(QueryConditionVo queryConditionVo, SearchDataSourceModel searchDataSourceModel, StringBuilder sql, Map<String, Object> map) {
if (StringUtils.isNotEmpty(searchDataSourceModel.getDataSourceName())) {
sql.append(" and T.dataSourceName LIKE :dataSourceName");
map.put("dataSourceName", "%" + searchDataSourceModel.getDataSourceName() + "%");
}
if (!ObjectUtils.isEmpty(searchDataSourceModel.getGmtCreateStart())) {
sql.append(" and DATE_FORMAT(T.gmtCreate,'%Y-%m-%d') >= :gmtCreate");
map.put("gmtCreate", searchDataSourceModel.getGmtCreateStart());
}
if (!ObjectUtils.isEmpty(searchDataSourceModel.getGmtCreateEnd())) {
sql.append(" and DATE_FORMAT(T.gmtCreate,'%Y-%m-%d') <= :gmtCreate");
map.put("gmtCreate", searchDataSourceModel.getGmtCreateEnd());
}
if (!ObjectUtils.isEmpty(searchDataSourceModel.getSourceState())) {
sql.append(" and T.dataSourceState IN(:dataSourceState)");
map.put("dataSourceState", searchDataSourceModel.getSourceState());
}
if (!ObjectUtils.isEmpty(searchDataSourceModel.getProvince())) {
sql.append(" and T.province =:province");
map.put("province", searchDataSourceModel.getProvince());
}
if (!ObjectUtils.isEmpty(searchDataSourceModel.getCity())) {
sql.append(" and T.city = :city");
map.put("city", searchDataSourceModel.getCity());
}
if (!ObjectUtils.isEmpty(searchDataSourceModel.getArea())) {
sql.append(" and T.area = :area");
map.put("area", searchDataSourceModel.getArea());
}
if (!ObjectUtils.isEmpty(searchDataSourceModel.getResourceState())) {
sql.append(" and T.resourceState = :resourceState");
map.put("resourceState", searchDataSourceModel.getResourceState());
}
if (!ObjectUtils.isEmpty(searchDataSourceModel.getUseState()) && searchDataSourceModel.getUseState().equals(UseState.USE)) {
sql.append(" and T.projectCount > 0");
}
if (!ObjectUtils.isEmpty(searchDataSourceModel.getUseState()) && searchDataSourceModel.getUseState().equals(UseState.NOT_USE)) {
sql.append(" and T.projectCount = 0");
}
return super.executeNativeQuery(sql.toString(), queryConditionVo, map);
}
}
返回的实体类对象
@Data
public class DataSourceModel {
private String id;
/**
* 数据源名称
*/
private String dataSourceName;
/**
* gis数据源内容,这个就是URL地址
*/
private String dataSourceContent;
/**
* 备注信息，简介
*/
private String remark;
/**
* 创建人
*/
private String createBy;
/**
* 更新人
@OneToOne private User updateBy;*/
/**
* 字体地址
*/
private String fontPath;
/**
* 雪碧图地址
*/
private String spritePath;
/**
* 数据源状态 private:私有
* <p>
* public : 公开
*/
private String dataSourceState;
/**
* 数据清单，文件路径
*/
private String dataListPath;
/**
* 地图缩略图，文件路径
*/
private String mapThumbnailPath;
/**
* 数据清单，文件名称
*/
private String dataListName;
/**
* 地图缩略图，图片名称 申请
*/
private String mapThumbnailName;
/**
* 图层总数
*/
private String layersNum;
/**
* 背景色，用户前端展示
*/
private String backGroundColor;
/**
* 区域地址
*/
private String province;
private String city;
private String area;
private String gmtCreate;
private String userState = UserState.GUEST.getName();
private String isDeleted;
private BigInteger projectCount;
private String address;
private String resourceState;
}
以上就是原生SQL返回实体类的调用方式,是不是很简单呢. 注意:JPA返回对象属性时,会有较高的要求,一般int类型时,jpa会返回biginteger,等等,有些属性需要注意,不过报错时,jpa会给出友好的提示,修改属性就好了
对了,关于BaseDaoImpl.java的QueryConditionVo类,这里没有给出,请翻阅我之前的文章JPA表达式(一)文章,里面有具体写法
欢迎小伙伴留言.
