- 關(guān)于JPA寫原生SQL一直很有爭議,大部分說JPA不適合寫SQL,或者對于SQL的支持很垃圾,但是在我使用JPA的過程中,發(fā)現(xiàn)JPA寫SQL其實(shí)還可以,那么咱們就討論下JPA原生SQL的寫法.
關(guān)于JPA原生SQL是采用entityManager類進(jìn)行查詢,當(dāng)前查詢可以返回Map<Object,Object>對象,當(dāng)然也可以返回List<T>對象.
定義原生接口
BaseDao.java
/**
* 這里為了演示JPA原生sql,就先定義個空接口, 如果運(yùn)用到項(xiàng)目中,這里可以定義
* 一些公共的查詢方法,如簡單的單表增刪改查
* 如: 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的具體實(shí)現(xiàn).關(guān)于調(diào)用,請看下面的例子
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);
}
}
返回的實(shí)體類對象
@Data
public class DataSourceModel {
private String id;
/**
* 數(shù)據(jù)源名稱
*/
private String dataSourceName;
/**
* gis數(shù)據(jù)源內(nèi)容,這個就是URL地址
*/
private String dataSourceContent;
/**
* 備注信息，簡介
*/
private String remark;
/**
* 創(chuàng)建人
*/
private String createBy;
/**
* 更新人
@OneToOne private User updateBy;*/
/**
* 字體地址
*/
private String fontPath;
/**
* 雪碧圖地址
*/
private String spritePath;
/**
* 數(shù)據(jù)源狀態(tài) private:私有
* <p>
* public : 公開
*/
private String dataSourceState;
/**
* 數(shù)據(jù)清單，文件路徑
*/
private String dataListPath;
/**
* 地圖縮略圖，文件路徑
*/
private String mapThumbnailPath;
/**
* 數(shù)據(jù)清單，文件名稱
*/
private String dataListName;
/**
* 地圖縮略圖，圖片名稱 申請
*/
private String mapThumbnailName;
/**
* 圖層總數(shù)
*/
private String layersNum;
/**
* 背景色，用戶前端展示
*/
private String backGroundColor;
/**
* 區(qū)域地址
*/
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返回實(shí)體類的調(diào)用方式,是不是很簡單呢. 注意:JPA返回對象屬性時,會有較高的要求,一般int類型時,jpa會返回biginteger,等等,有些屬性需要注意,不過報錯時,jpa會給出友好的提示,修改屬性就好了
對了,關(guān)于BaseDaoImpl.java的QueryConditionVo類,這里沒有給出,請翻閱我之前的文章JPA表達(dá)式(一)文章,里面有具體寫法
歡迎小伙伴留言.
