由于在项目中添加新的业务,该项目使用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