由于在项目中添加新的业务,该项目使用jpa 但是表结构不是这个项目产生,所以使用jpa实体管理器(EntityManager)封装了一个通用的Dao
sql封装类
package com.ronnie.data.utils;
import org.springframework.util.StringUtils;
/**
* @Description:
* @Author: rongyu
* @CreateDate: 2018/8/30$ 11:49$
* @Remark:
*/
public class NativeSqlBuilder {
private final static String BLANK = " ";
private final static String COMMA = ",";
private final static String DEFAULT_SELECT_FIELD = "*";
private final static String DEFAULT_ORDER = "id DESC";
private String tableName;
private StringBuffer fields = new StringBuffer();
private StringBuffer conditions = new StringBuffer("1=1 ");
private StringBuffer sorts = new StringBuffer();
// 表名
public NativeSqlBuilder(String tableName) {
this.tableName = tableName;
}
// 表字段
public NativeSqlBuilder addField(String field) {
if (!StringUtils.isEmpty(fields)) {
fields.append(COMMA + field);
} else {
fields.append(field);
}
return this;
}
// 条件
public NativeSqlBuilder addCondition(String condition) {
conditions.append(condition + BLANK);
return this;
}
// 排序
public NativeSqlBuilder addSort(String sort) {
if (!StringUtils.isEmpty(sorts)) {
sorts.append(COMMA + sort);
} else {
sorts.append(sort);
}
return this;
}
public static NativeSqlBuilder builder(String tableName) {
return new NativeSqlBuilder(tableName);
}
public NativeSql build() {
return NativeSql.builder()
.tableName(tableName)
.conditions(conditions.toString())
.fields(!StringUtils.isEmpty(fields.toString()) ? fields.toString() : DEFAULT_SELECT_FIELD)
.sorts(!StringUtils.isEmpty(sorts.toString()) ? sorts.toString() : DEFAULT_ORDER)
.build();
}
}
通用Dao接受对象
@Data
@Builder
public class NativeSql {
private String tableName;
private String fields;
private String conditions;
private String sorts;
}
package com.ronnie.data.utils;
import lombok.Data;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import java.util.ArrayList;
import java.util.List;
/**
* rongyu
* 分页接受参数
*/
@Data
public class PageModel {
Integer pageNo = 1;
Integer pageSize = 10;
List<Sort.Direction> direction;
List<String> property;
public void setSort() {
if (property == null || property.size() == 0) {
List<String> list = new ArrayList<>();
list.add("id");
List<Sort.Direction> directions = new ArrayList<>();
directions.add(Sort.Direction.DESC);
property = list;
direction = directions;
}
}
private Sort getSort() {
List<Sort.Order> orders = null;
setSort();
if (direction.size() == property.size()) {
orders = new ArrayList<>();
int length = direction.size();
for (int i = 0; i < length; i++) {
orders.add(new Sort.Order(direction.get(i), property.get(i)));
}
}
return new Sort(orders);
}
public Pageable getPageable() {
Sort sort = getSort();
if (sort == null)
return new PageRequest(pageNo - 1, pageSize);
return new PageRequest(pageNo - 1, pageSize, sort);
}
}
通用Dao(接口)
package com.ronnie.data.dao.base;
import com.alibaba.fastjson.JSONObject;
import com.ronnie.data.utils.NativeSql;
import com.ronnie.data.utils.PageModel;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.ResultTransformer;
import org.hibernate.transform.Transformers;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.lang.reflect.ParameterizedType;
import java.math.BigInteger;
import java.util.List;
import java.util.Map;
/**
* @Description:
* @Author: rongyu
* @CreateDate: 2018/8/31$ 10:14$
* @Remark:
*/
public interface BaseEntityManagerDao {
/**
* 设置实体管理器
*
* @return
*/
EntityManager getEntityManager();
/**
* 分页查询
*
* @param nativeSql
* @param pageModel
* @return
*/
default Page createNativePageQuery(NativeSql nativeSql, PageModel pageModel) {
String countSql = "SELECT COUNT(1) FROM " + nativeSql.getTableName() + " WHERE " + nativeSql.getConditions();
StringBuilder sql = new StringBuilder("SELECT ");
sql.append(nativeSql.getFields())
.append(" FROM ")
.append(nativeSql.getTableName())
.append(" WHERE ")
.append(nativeSql.getConditions());
Query countQuery = getEntityManager().createNativeQuery(countSql);
long count = ((BigInteger) countQuery.getSingleResult()).longValue();
if (pageModel.getProperty() != null && pageModel.getProperty().size() > 0 && pageModel.getDirection().size() == pageModel.getProperty().size()) {
sql.append("ORDER BY");
for (int i = 0; i < pageModel.getProperty().size(); i++) {
sql.append(" " + pageModel.getProperty().get(i) + " " + pageModel.getDirection().get(i) + " ");
if (i < pageModel.getProperty().size() - 1) {
sql.append(",");
}
}
}
sql.append(" limit " + pageModel.getPageSize() * (pageModel.getPageNo() - 1) + " , " + pageModel.getPageSize());
javax.persistence.Query query2 = getEntityManager().createNativeQuery(sql.toString());
//query2.unwrap(NativeQuery.class).addEntity(Map.class).getResultList();
query2.unwrap(NativeQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
List list = query2.getResultList();
return new PageImpl<>(list, pageModel.getPageable(), count);
}
/**
* 查询
*
* @param nativeSql
* @return
*/
default List<Map> nativeQueryAll(NativeSql nativeSql) {
String sql = getSimpleSqlStringBuilder(nativeSql).toString();
Query query = getEntityManager().createNativeQuery(sql);
query.unwrap(NativeQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
List<Map> list = (List<Map>) query.getResultList();
return list;
}
/**
* 查询
*
* @param nativeSql
* @return
*/
default Object nativeQueryOne(NativeSql nativeSql) {
String sql = getSimpleSqlStringBuilder(nativeSql).append(" LIMIT 1 ").toString();
Query query = getEntityManager().createNativeQuery(sql);
query.unwrap(NativeQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
Object o = query.getSingleResult();
return o;
}
default StringBuilder getSimpleSqlStringBuilder(NativeSql nativeSql) {
StringBuilder sql = new StringBuilder("SELECT ");
return sql.append(nativeSql.getFields())
.append(" FROM ")
.append(nativeSql.getTableName())
.append(" WHERE ")
.append(nativeSql.getConditions())
.append(" ORDER BY ")
.append(nativeSql.getSorts());
}
}
使用步骤
一个实现类
public interface ExEntrustDao extends BaseEntityManagerDao {
}
@Repository
public class ExEntrustDaoImpl implements ExEntrustDao {
private final static String TABLE_NAME = "ex_entrust";
@Autowired
@PersistenceContext
private EntityManager em;
@Override
public EntityManager getEntityManager() {
return this.em;
}
}
public interface ExEntrustService {
ExEntrust findOne(NativeSql nativeSql);
}
@Service
public class ExEntrustServiceImpl implements ExEntrustService {
@Autowired
private ExEntrustDao dao;
@Override
public ExEntrust findOne(NativeSql nativeSql) {
ExEntrust e = null;
Object o = dao.nativeQueryOne(nativeSql);
if (o != null) {
e = JSONObject.parseObject(JSONObject.toJSONString(o), ExEntrust.class);
}
return e;
}
}
调用
@Test
public void exEntrustService() {
NativeSql nativeSql = NativeSqlBuilder
.builder("ex_entrust")
.addCondition("and id = 20522932")
.build();
ExEntrust one = exEntrustService.findOne(nativeSql);
log.info("");
}
github https://github.com/ronnierry/spring-boot-data/tree/master/spring-data-jpa
QQ群 : 869658872