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