SpringDataJPA原生sql查询方式的封装操作

工具类相关代码

使用到了apache的map2bean工具类 导入方法

<dependency>
    <groupId>commons-beanutils</groupId>
    <artifactId>commons-beanutils</artifactId>
    <version>1.9.3</version>
</dependency>
import org.apache.commons.beanutils.BeanUtils;
import java.util.Map;
/**
 * 将查询结果 map 封装成对应的javaBean,支持级联 ,但是属性不能重复
 * 对应的javaBean的属性名必须以小驼峰形式命名,否则无法填充数据
 */
public class Map2Bean {
    private Map2Bean() {
    }
    /**
     * 将 map 数据封装成javaBean
     *
     * @param map   Map类型数据
     * @param clazz 需要转换的JavaBean
     * @param <T>   泛型
     * @return JavaBean
     */
    public static <T> T convert(Map<String, Object> map, Class<T> clazz) {
        if (map == null || clazz == null) {
            return null;
        }
        T result = null;
        try {
            result = clazz.newInstance();
            BeanUtils.populate(result, map);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }
}
import java.io.Serializable;
import java.util.List;
/**
 * Page is the result of Model.paginate(......) or Db.paginate(......)
 */
public class Page<T> implements Serializable {
    private static final long serialVersionUID = -5395997221963176643L;
    private List<T> list;           // list result of this page
    private int pageNumber;             // page number
    private int pageSize = 10;            // result amount of this page
    private int totalPage;          // total page
    private int totalRow;           // total row
    public Page(int pageNumber) {
        this.pageNumber = pageNumber;
    }
    /**
     * Constructor.
     *
     * @param list       the list of paginate result
     * @param pageNumber the page number
     * @param pageSize   the page size
     * @param totalPage  the total page of paginate
     * @param totalRow   the total row of paginate
     */
    public Page(List<T> list, int pageNumber, int pageSize, int totalPage, int totalRow) {
        this.list = list;
        this.pageNumber = pageNumber;
        this.pageSize = pageSize;
        this.totalPage = totalPage;
        this.totalRow = totalRow;
    }
    public Page(int pageNumber, int pageSize) {
        this.pageNumber = pageNumber;
        this.pageSize = pageSize;
    }
    /**
     * Return list of this page.
     */
    public List<T> getList() {
        return list;
    }
    /**
     * Return page number.
     */
    public int getPageNumber() {
        return pageNumber;
    }
    /**
     * Return page size.
     */
    public int getPageSize() {
        return pageSize;
    }
    /**
     * Return total page.
     */
    public int getTotalPage() {
        totalPage = totalRow / pageSize;
        if (totalRow % pageSize > 0) {
            totalPage++;
        }
        return totalPage;
    }
    /**
     * Return total row.
     */
    public int getTotalRow() {
        return totalRow;
    }
    public boolean isFirstPage() {
        return pageNumber == 1;
    }
    public boolean isLastPage() {
        return pageNumber == totalPage;
    }
    public void setList(List<T> list) {
        this.list = list;
    }
    public void setPageNumber(int pageNumber) {
        this.pageNumber = pageNumber;
    }
    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }
    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }
    public void setTotalRow(int totalRow) {
        this.totalRow = totalRow;
    }
    @Override
    public String toString() {
        return "Page{" +
                "list=" + list +
                ", pageNumber=" + pageNumber +
                ", pageSize=" + pageSize +
                ", totalPage=" + totalPage +
                ", totalRow=" + totalRow +
                '}';
    }
}
import java.io.Serializable;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;
/**
 * Record
 */
public class Record implements Serializable {
    private static final long serialVersionUID = 905784513600884082L;
    private Map<String, Object> columns = new HashMap<>();
    public Record() {
    }
    public Record(Map<String, Object> columns) {
        this.columns = columns;
    }
    public Map<String, Object> getColumns() {
        return columns;
    }
    public Record setColumns(Map<String, Object> columns) {
        this.getColumns().putAll(columns);
        return this;
    }
    public Record setColumns(Record record) {
        getColumns().putAll(record.getColumns());
        return this;
    }
    public Record remove(String column) {
        getColumns().remove(column);
        return this;
    }
    public Record remove(String... columns) {
        if (columns != null) {
            for (String c : columns) {
                this.getColumns().remove(c);
            }
        }
        return this;
    }
    public Record removeNullValueColumns() {
        for (java.util.Iterator<Map.Entry<String, Object>> it = getColumns().entrySet().iterator(); it.hasNext(); ) {
            Map.Entry<String, Object> e = it.next();
            if (e.getValue() == null) {
                it.remove();
            }
        }
        return this;
    }
    /**
     * Keep columns of this record and remove other columns.
     *
     * @param columns the column names of the record
     */
    public Record keep(String... columns) {
        if (columns != null && columns.length > 0) {
            Map<String, Object> newColumns = new HashMap<String, Object>(columns.length);    // getConfig().containerFactory.getColumnsMap();
            for (String c : columns) {
                if (this.getColumns().containsKey(c)) {    // prevent put null value to the newColumns
                    newColumns.put(c, this.getColumns().get(c));
                }
            }
            this.getColumns().clear();
            this.getColumns().putAll(newColumns);
        } else {
            this.getColumns().clear();
        }
        return this;
    }
    /**
     * Keep column of this record and remove other columns.
     *
     * @param column the column names of the record
     */
    public Record keep(String column) {
        if (getColumns().containsKey(column)) {    // prevent put null value to the newColumns
            Object keepIt = getColumns().get(column);
            getColumns().clear();
            getColumns().put(column, keepIt);
        } else {
            getColumns().clear();
        }
        return this;
    }
    public Record clear() {
        getColumns().clear();
        return this;
    }
    public Record set(String column, Object value) {
        getColumns().put(column, value);
        return this;
    }
    public <T> T get(String column) {
        return (T) getColumns().get(column);
    }
    public <T> T get(String column, Object defaultValue) {
        Object result = getColumns().get(column);
        return (T) (result != null ? result : defaultValue);
    }
    /**
     * Get column of mysql type: varchar, char, enum, set, text, tinytext, mediumtext, longtext
     */
    public String getStr(String column) {
        return (String) getColumns().get(column);
    }
    /**
     * Get column of mysql type: int, integer, tinyint(n) n > 1, smallint, mediumint
     */
    public Integer getInt(String column) {
        return (Integer) getColumns().get(column);
    }
    /**
     * Get column of mysql type: bigint
     */
    public Long getLong(String column) {
        return (Long) getColumns().get(column);
    }
    /**
     * Get column of mysql type: unsigned bigint
     */
    public java.math.BigInteger getBigInteger(String column) {
        return (java.math.BigInteger) getColumns().get(column);
    }
    /**
     * Get column of mysql type: date, year
     */
    public java.util.Date getDate(String column) {
        return (java.util.Date) getColumns().get(column);
    }
    /**
     * Get column of mysql type: time
     */
    public java.sql.Time getTime(String column) {
        return (java.sql.Time) getColumns().get(column);
    }
    /**
     * Get column of mysql type: timestamp, datetime
     */
    public java.sql.Timestamp getTimestamp(String column) {
        return (java.sql.Timestamp) getColumns().get(column);
    }
    /**
     * Get column of mysql type: real, double
     */
    public Double getDouble(String column) {
        return (Double) getColumns().get(column);
    }
    /**
     * Get column of mysql type: float
     */
    public Float getFloat(String column) {
        return (Float) getColumns().get(column);
    }
    /**
     * Get column of mysql type: bit, tinyint(1)
     */
    public Boolean getBoolean(String column) {
        return (Boolean) getColumns().get(column);
    }
    /**
     * Get column of mysql type: decimal, numeric
     */
    public java.math.BigDecimal getBigDecimal(String column) {
        return (java.math.BigDecimal) getColumns().get(column);
    }
    /**
     * Get column of mysql type: binary, varbinary, tinyblob, blob, mediumblob, longblob
     * I have not finished the test.
     */
    public byte[] getBytes(String column) {
        return (byte[]) getColumns().get(column);
    }
    /**
     * Get column of any type that extends from Number
     */
    public Number getNumber(String column) {
        return (Number) getColumns().get(column);
    }
    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append(super.toString()).append(" {");
        boolean first = true;
        for (Map.Entry<String, Object> e : getColumns().entrySet()) {
            if (first) {
                first = false;
            } else {
                sb.append(", ");
            }
            Object value = e.getValue();
            if (value != null) {
                value = value.toString();
            }
            sb.append(e.getKey()).append(":").append(value);
        }
        sb.append("}");
        return sb.toString();
    }
    @Override
    public boolean equals(Object o) {
        if (!(o instanceof Record)) {
            return false;
        }
        if (o == this) {
            return true;
        }
        return this.getColumns().equals(((Record) o).getColumns());
    }
    @Override
    public int hashCode() {
        return getColumns() == null ? 0 : getColumns().hashCode();
    }
    /**
     * Return column names of this record.
     */
    public String[] getColumnNames() {
        Set<String> attrNameSet = getColumns().keySet();
        return attrNameSet.toArray(new String[attrNameSet.size()]);
    }
    /**
     * Return column values of this record.
     */
    public Object[] getColumnValues() {
        java.util.Collection<Object> attrValueCollection = getColumns().values();
        return attrValueCollection.toArray(new Object[attrValueCollection.size()]);
    }
    /**
     * Return json string of this record.
     */
    public String toJson() {
        throw new UnsupportedOperationException("还未实现");
    }
}
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
/**
 * 作者:guoyzh
 * 时间:2019/8/20 12:53
 * 功能:使用jpa进行原生sql查询的工具类 使用AutoWrite注入即可使用
 */
@Component
public class SqlUtils {
    @Autowired
    @PersistenceContext
    private EntityManager entityManager;
    public SqlUtils(EntityManager entityManager) {
        this.entityManager = entityManager;
    }
    public SqlUtils() {
    }
    public void setEntityManager(EntityManager entityManager) {
        this.entityManager = entityManager;
    }
    /**
     * 返回查询的一个Record,没有则为null
     */
    public Record findFirst(String sql, Object... params) {
        return findFirst(sql, Record.class, params);
    }
    public Record findFirst(String sql, Map<String, Object> searchMap) {
        return findFirst(sql, Record.class, searchMap);
    }
    /**
     * 返回查询的一个实体,没有则为null
     */
    public <T> T findFirst(String sql, Class<T> clazz, Object... params) {
        List<T> ts = find(sql, clazz, params);
        return (ts == null || ts.size() == 0) ? null : ts.get(0);
    }
    public <T> T findFirst(String sql, Class<T> clazz, Map<String, Object> searchMap) {
        List<T> ts = find(sql, clazz, searchMap);
        return (ts == null || ts.size() == 0) ? null : ts.get(0);
    }
    public List<Record> find(String sql, Object... params) {
        return find(sql, Record.class, params);
    }
    public List<Record> find(String sql, Map<String, Object> searchMap) {
        return find(sql, Record.class, searchMap);
    }
    public List<Record> find(String sql) {
        return find(sql, Record.class, (Map<String, Object>) null);
    }
    /**
     * 查询列表
     *
     * @param sql    native sql语句,可以包含?
     * @param clazz  返回的类型,可以是JavaBean,可以是Record
     * @param params 参数列表
     * @param <T>    泛型
     * @return 查询列表结果
     */
    public <T> List<T> find(String sql, Class<T> clazz, Object... params) {
        Session session = entityManager.unwrap(Session.class);
        org.hibernate.Query query = session.createSQLQuery(sql);
        //0-Based
        for (int i = 0; i < params.length; i++) {
            query.setParameter(i, params[i]);
        }
        List list = getList(query, clazz);
        return list;
    }
    /**
     * 查询列表
     *
     * @param sql       native sql语句,可以包含 :具名参数
     * @param clazz     返回的类型,可以是JavaBean,可以是Record
     * @param searchMap 具名参数列表
     * @param <T>       泛型
     * @return 查询列表结果
     */
    public <T> List<T> find(String sql, Class<T> clazz, Map<String, Object> searchMap) {
        Session session = entityManager.unwrap(Session.class);
        org.hibernate.Query query = session.createSQLQuery(sql);
        if (null != searchMap) {
            searchMap.forEach(query::setParameter);
        }
        List list = getList(query, clazz);
        return list;
    }
    /**
     * ----------------------------------------------record-positioned-parameter---------------------------------------------------
     */
    public Page<Record> paginate(String nativeSQL, int pageNumber, int pageSize, Object... params) {
        String nativeCountSQL = getCountSQL(nativeSQL);
        return paginate(null, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, params);
    }
    public Page<Record> paginate(String nativeSQL, Boolean isGroupBySql, int pageNumber, int pageSize, Object... params) {
        String nativeCountSQL = getCountSQL(nativeSQL);
        return paginate(isGroupBySql, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, params);
    }
    public Page<Record> paginate(String nativeSQL, String nativeCountSQL, int pageNumber, int pageSize, Object... params) {
        return paginate(null, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, params);
    }
    public Page<Record> paginate(Boolean isGroupBySql, String nativeSQL, String nativeCountSQL, int pageNumber, int pageSize, Object... params) {
        return paginate(isGroupBySql, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, params);
    }
    /**
     * ----------------------------------------------record-maped-parameter---------------------------------------------------
     */
    public Page<Record> paginate(String nativeSQL, int pageNumber, int pageSize, Map<String, Object> searchMap) {
        String nativeCountSQL = getCountSQL(nativeSQL);
        return paginate(null, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, searchMap);
    }
    public Page<Record> paginate(String nativeSQL, Boolean isGroupBySql, int pageNumber, int pageSize, Map<String, Object> searchMap) {
        String nativeCountSQL = getCountSQL(nativeSQL);
        return paginate(isGroupBySql, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, searchMap);
    }
    public Page<Record> paginate(String nativeSQL, String nativeCountSQL, int pageNumber, int pageSize, Map<String, Object> searchMap) {
        return paginate(null, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, searchMap);
    }
    public Page<Record> paginate(Boolean isGroupBySql, String nativeSQL, String nativeCountSQL, int pageNumber, int pageSize, Map<String, Object> searchMap) {
        return paginate(isGroupBySql, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, searchMap);
    }
    /**
     * ----------------------------------------------JavaBean-positioned-parameter---------------------------------------------------
     */
    public <T> Page<T> paginate(Boolean isGroupBySql, String nativeSQL, Class<T> clazz, int pageNumber, int pageSize, Object... params) {
        String nativeCountSQL = getCountSQL(nativeSQL);
        return paginate(isGroupBySql, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, params);
    }
    public <T> Page<T> paginate(String nativeSQL, String nativeCountSQL, Class<T> clazz, int pageNumber, int pageSize, Object... params) {
        return paginate(null, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, params);
    }
    public <T> Page<T> paginate(String nativeSQL, Class<T> clazz, int pageNumber, int pageSize, String... params) {
        String nativeCountSQL = getCountSQL(nativeSQL);
        return paginate(null, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, params);
    }
    /**
     * ----------------------------------------------JavaBean-maped-parameter---------------------------------------------------
     */
    public <T> Page<T> paginate(String nativeSQL, Class<T> clazz, int pageNumber, int pageSize, Map<String, Object> searchMap) {
        String nativeCountSQL = getCountSQL(nativeSQL);
        return paginate(null, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, searchMap);
    }
    public <T> Page<T> paginate(Boolean isGroupBySql, String nativeSQL, Class<T> clazz, int pageNumber, int pageSize, Map<String, Object> searchMap) {
        String nativeCountSQL = getCountSQL(nativeSQL);
        return paginate(isGroupBySql, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, searchMap);
    }
    public <T> Page<T> paginate(String nativeSQL, String nativeCountSQL, Class<T> clazz, int pageNumber, int pageSize, Map<String, Object> searchMap) {
        return paginate(null, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, searchMap);
    }
    /**
     * @param pageNumber     pageNumber
     * @param pageSize       pageSize
     * @param isGroupBySql   是否包含Group by语句,影响总行数
     * @param nativeSQL      原生SQL语句 {@see QueryHelper}
     * @param nativeCountSQL 原生求总行数的SQL语句 {@see QueryHelper}
     * @param clazz          JavaBean风格的DTO或者Record,需要用别名跟JavaBean对应
     * @param <T>            返回JavaBean风格的DTO或者Record
     * @param params         按照顺序给条件
     */
    public <T> Page<T> paginate(Boolean isGroupBySql, String nativeSQL, String nativeCountSQL, Class<T> clazz, int pageNumber, int pageSize, Object... params) {
        if (pageNumber < 1 || pageSize < 1) {
            throw new IllegalArgumentException("pageNumber and pageSize must more than 0");
        }
        Query countQuery = entityManager.createNativeQuery(nativeCountSQL);
        //坑死人,1-Based
        for (int i = 1; i <= params.length; i++) {
            countQuery.setParameter(i, params[i - 1]);
        }
        List countQueryResultList = countQuery.getResultList();
        int size = countQueryResultList.size();
        if (isGroupBySql == null) {
            isGroupBySql = size > 1;
        }
        long totalRow;
        if (isGroupBySql) {
            totalRow = size;
        } else {
            totalRow = (size > 0) ? ((Number) countQueryResultList.get(0)).longValue() : 0;
        }
        if (totalRow == 0) {
            return new Page<>(new ArrayList<>(0), pageNumber, pageSize, 0, 0);
        }
        int totalPage = (int) (totalRow / pageSize);
        if (totalRow % pageSize != 0) {
            totalPage++;
        }
        if (pageNumber > totalPage) {
            return new Page<>(new ArrayList<>(0), pageNumber, pageSize, totalPage, (int) totalRow);
        }
        Session session = entityManager.unwrap(Session.class);
        int offset = pageSize * (pageNumber - 1);
        org.hibernate.Query query = session.createSQLQuery(nativeSQL).setFirstResult(offset).setMaxResults(pageSize);
        //坑死人,0-Based
        for (int i = 0; i < params.length; i++) {
            query.setParameter(i, params[i]);
        }
        final List list = getList(query, clazz);
        return new Page<T>(list, pageNumber, pageSize, totalPage, (int) totalRow);
    }
    /**
     * @param pageNumber     pageNumber
     * @param pageSize       pageSize
     * @param isGroupBySql   是否包含Group by语句,影响总行数
     * @param nativeSQL      原生SQL语句 {@see QueryHelper}
     * @param nativeCountSQL 原生求总行数的SQL语句 {@see QueryHelper}
     * @param clazz          JavaBean风格的DTO或者Record,需要用别名跟JavaBean对应
     * @param <T>            返回JavaBean风格的DTO或者Record
     * @param searchMap      k-v条件
     */
    public <T> Page<T> paginate(Boolean isGroupBySql, String nativeSQL, String nativeCountSQL, Class<T> clazz, int pageNumber, int pageSize, Map<String, Object> searchMap) {
        if (pageNumber < 1 || pageSize < 1) {
            throw new IllegalArgumentException("pageNumber and pageSize must more than 0");
        }
        Query countQuery = entityManager.createNativeQuery(nativeCountSQL);
        if (null != searchMap) {
            searchMap.forEach(countQuery::setParameter);
        }
        List countQueryResultList = countQuery.getResultList();
        int size = countQueryResultList.size();
        if (isGroupBySql == null) {
            isGroupBySql = size > 1;
        }
        long totalRow;
        if (isGroupBySql) {
            totalRow = size;
        } else {
            totalRow = (size > 0) ? ((Number) countQueryResultList.get(0)).longValue() : 0;
        }
        if (totalRow == 0) {
            return new Page<>(new ArrayList<>(0), pageNumber, pageSize, 0, 0);
        }
        int totalPage = (int) (totalRow / pageSize);
        if (totalRow % pageSize != 0) {
            totalPage++;
        }
        if (pageNumber > totalPage) {
            return new Page<>(new ArrayList<>(0), pageNumber, pageSize, totalPage, (int) totalRow);
        }
        Session session = entityManager.unwrap(Session.class);
        int offset = pageSize * (pageNumber - 1);
        org.hibernate.Query query = session.createSQLQuery(nativeSQL).setFirstResult(offset).setMaxResults(pageSize);
        if (null != searchMap) {
            searchMap.forEach(query::setParameter);
        }
        final List list = getList(query, clazz);
        return new Page<T>(list, pageNumber, pageSize, totalPage, (int) totalRow);
    }
    private <T> List getList(org.hibernate.Query query, Class<T> clazz) {
        final List list;
        //Object[].class
        if (Object[].class == clazz) {
            return query.list();
        }
        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List mapList = query.list();
        list = new ArrayList(mapList.size());
        mapList.forEach(map -> {
            Map<String, Object> tmp = (Map<String, Object>) map;
            //Record.class
            if (Record.class == clazz) {
                list.add(new Record(tmp));
                //Map及子类
            } else if (Map.class.isAssignableFrom(clazz)) {
                list.add(tmp);
                //JavaBean风格
            } else {
                list.add(Map2Bean.convert(tmp, clazz));
            }
        });
        return list;
    }
    /*private <T> List getList(org.hibernate.Query query, Class<T> clazz) {
        final List list;
        if(Record.class == clazz){
            //返回Record
            query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
            List mapList = query.list();
            list = new ArrayList(mapList.size());
            mapList.forEach(map->{
                Map<String , Object> tmp = (Map<String , Object>) map;
                list.add(new Record(tmp));
            });
        }else {
            //返回JavaBean
            //只能返回简单的Javabean,不具备级联特性
            query.setResultTransformer(Transformers.aliasToBean(clazz));
            list = query.list();
        }
        return list;
    }*/
    private String getCountSQL(String sql) {
        String countSQL = "SELECT COUNT(*) AS totalRow " + sql.substring(sql.toUpperCase().indexOf("FROM"));
        return replaceOrderBy(countSQL);
    }
    protected static class Holder {
        private static final Pattern ORDER_BY_PATTERN = Pattern.compile(
                "order\\s+by\\s+[^,\\s]+(\\s+asc|\\s+desc)?(\\s*,\\s*[^,\\s]+(\\s+asc|\\s+desc)?)*",
                Pattern.CASE_INSENSITIVE | Pattern.MULTILINE);
    }
    public String replaceOrderBy(String sql) {
        return Holder.ORDER_BY_PATTERN.matcher(sql).replaceAll("");
    }
}

代码中调用

@Autowired
SqlUtils mSqlUtils;
。。。
@Transactional
@ApiOperation("测试")
@PostMapping("/get1")
public Result get1() {
    HashMap<String, Object> map = new HashMap<>();
    map.put("id", "SPA0000001");
    TestResp record = mSqlUtils.findFirst("select * from st_PkgActvty where id = :id", TestResp.class, map);
    return ResultGenerator.genSuccessResult(record);
}

Spring data jpa@query使用原生SQl,需要注意的坑

根据代码来解说:

@Query(value = "select bill.id_ as id, bill.created_date as date, bill.no, lawyer_case .case_no as caseNo, " +
            "lawyer_case .case_name as caseName, customer.no as customerNo, customer.cn_name as customerName, " +
            "bill.total_expense_after_tax, bill.collected_money, bill.book_ticket_amount, bill.version " +
            "e1.name as creator, bill.status" +
            "from bill " +
            "left join lawyer_case on lawyer_case .case_no=bill.case_no " +
            "left join customer on customer.no=bill.customer_no " +
            "left join employee e1 on e1.id_=bill.creator " +
            "where IF (?1!='', customer_no=?1, 1=1) " +
            "and   IF (?2!='', case_no=?2, 1=1) " +
            "and   IF (?3!='', status=?3, 1=1) " +
            "and   IF (?4!='', creator'%',?4,'%')), 1=1) " +
            "and   create_by=?5 " +
            "ORDER BY ?#{#pageable} ",
            countQuery = "select count(*) " +
                    "from bill " +
                    "left join lawyer_case on lawyer_case .case_no=bill.case_no " +
                    "left join customer on customer.no=bill.customer_no " +
                    "left join employee e1 on e1.id_=bill.creator " +
                    "where IF (?1!='', customer_no=?1, 1=1) " +
                    "and   IF (?2!='', case_no=?2, 1=1) " +
                    "and   IF (?3!='', status=?3, 1=1) " +
                    "and   IF (?4!='', creator'%',?4,'%')), 1=1) " +
                    "and   create_by=?5 "+
                    "ORDER BY ?#{#pageable} ",
            nativeQuery = true)
    Page<Object[]> findAllBill(String customerNo, String caseNo, Integer status, String creator,
                               String createBy, Pageable pageable);

需要注意的方法有以下几点:

1、From 不支持重命名.

2、返回的是一个page<Object[]>,数组中只保存了数据,没有对应的key,只能根据返回数据的顺序,依次注入到DTO中。

3、对于使用分页,需要:“ORDER BY ?#{#pageable}”,可以直接传入一个pageable对象,会自动解析。

4、注意格式问题,很多时候就是换行的时候,没有空格。

5、仔细对应数据库中表字段,很多时候报某个字段找不到,就是因为字段名写错,和数据库中对应不上。

6、这是解决使用微服务,大量的数据都需要远程调用,会降低程序的性能。

7、使用Pageabel作为参数的时候,去进行分页。刚开始的时候,觉得还是一个可行的办法,但是得注意的时候,当需要排序的时候,是无法加入sort字段的。 会一直报错left*。

8、针对7的解决方案,把原生SQL的数据查询和countQuery分成两个查询方法。

得到count,然后进行判断,若是等于0,则直接返回空集合;反之,则取获取数据。 需要自己进行分页计算,传入正确的pageNumber和pageSize。

大部分系统都是按照修改时间进行降序排序。 所以,order by可以写死。

然后pageNumber和pageSize动态传入。 pageNumber的算法= (pageNumber - 1) * pageSize, 前提是PageNumber是从1开始,若0,则pageNumber=pageNumber * PageSize; 这样就可以保证数据的正确。

/**
* pageInfos: 转换之后的数据。
* pageable:传入的pageable.
* totalPage: 第一条SQL算好的返回值。
* 这样就可以统一的返回各种pageDTO。
*/
private Page<T> convertForPage(List<T> pageInfos, Pageable pageable, Integer totalPage) {
        return new PageImpl<>(pageInfos, pageable, totalPage);
    }

以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。

时间: 2021-06-11

spring data jpa 查询自定义字段,转换为自定义实体方式

目标:查询数据库中的字段,然后转换成 JSON 格式的数据,返回前台. 环境:idea 2016.3.4, jdk 1.8, mysql 5.6, spring-boot 1.5.2 背景:首先建立 entity 映射数据库(非专业 java 不知道这怎么说) @Entity @Table(name = "user") public class User { @Id @GeneratedValue(strategy = GenerationType.AUTO) private Long

Spring Data Jpa的四种查询方式详解

这篇文章主要介绍了Spring Data Jpa的四种查询方式详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下 一.调用接口的方式 1.基本介绍 通过调用接口里的方法查询,需要我们自定义的接口继承Spring Data Jpa规定的接口 public interface UserDao extends JpaRepository<User, Integer>, JpaSpecificationExecutor<User> 使用这

解决spring data jpa 批量保存更新的问题

spring data jpa 批量保存更新问题 使用jpa批量保存时,看日志发现是一条一条打印的,然后去看了下源码,果然是循环调用的单个保存(巨坑啊) 经查询jpa是可以实现批量保存更新的,具体设置如下: spring.jpa.properties.hibernate.jdbc.batch_size=500 spring.jpa.properties.hibernate.jdbc.batch_versioned_data=true spring.jpa.properties.hibernate

spring data jpa如何只查询实体部分字段

需求 现在有一张article表,用来储存文章,对应的实体类如下: package com.qianyucc.blog.model; import lombok.*; import javax.persistence.*; /** * @author lijing * @date 2019-08-05 14:28 * @description 文章 */ @Data @Entity @Table(name = "article") public class Article { @Id

Spring Data JPA 简单查询--方法定义规则(详解)

一.常用规则速查 1 And 并且 2 Or   或 3 Is,Equals 等于 4 Between   两者之间 5 LessThan 小于 6 LessThanEqual   小于等于 7 GreaterThan 大于 8 GreaterThanEqual   大于等于 9 After 之后(时间) > 10 Before 之前(时间) < 11 IsNull 等于Null 12 IsNotNull,NotNull 不等于Null 13 Like 模糊查询.查询件中需要自己加 % 14

详解Spring Data Jpa 模糊查询的正确用法

模糊查询 Spring Data Jpa的使用可以减少开发者对sql语句的编写,甚至完全不需要编写sql语句.但是,开发过程中总会遇到各种复杂的场景以及大大小小的坑. 今天项目中某个功能模块需要用到模糊查询.原生sql中模糊查询关键字'Like',而Spring Data Jpa的Repository接口中恰恰也有实体字段对应的Like.但是,如果直接使用它,那么恭喜你,你幸运地掉坑了. Spring Data Jpa 模糊查询正确用法 首先,我们先创建一个实体用来存储我们的数据 /** * 实

spring data jpa分页查询示例代码

最近项目上用就hibernate+spring data jpa,一开始感觉还不错,但是随着对业务的复杂,要求处理一些复杂的sql,就顺便研究了下,把结果记录下,以便日后查看.用到Specification,需要继承JpaSpecificationExecutor接口.(下面代码有的分页从0开始作为第一页,有的从1开始作为作为第一页,我也忘记,请自己测试) DAO层: import java.util.List; import org.springframework.data.domain.Pa

Spring Boot Actuator监控的简单使用方法示例代码详解

Spring Boot Actuator帮助我们实现了许多中间件比如mysql.es.redis.mq等中间件的健康指示器. 通过 Spring Boot 的自动配置,这些指示器会自动生效.当这些组件有问题的时候,HealthIndicator 会返回 DOWN 或 OUT_OF_SERVICE 状态,health 端点 HTTP 响应状态码也会变为 503,我们可以以此来配置程序健康状态监控报警. 使用步骤也非常简单,这里演示的是线程池的监控.模拟线程池满了状态下将HealthInicator

使用Spring Data JPA的坑点记录总结

前言 Spring-data-jpa的基本介绍:JPA诞生的缘由是为了整合第三方ORM框架,建立一种标准的方式,百度百科说是JDK为了实现ORM的天下归一,目前也是在按照这个方向发展,但是还没能完全实现.在ORM框架中,Hibernate是一支很大的部队,使用很广泛,也很方便,能力也很强,同时Hibernate也是和JPA整合的比较良好,我们可以认为JPA是标准,事实上也是,JPA几乎都是接口,实现都是Hibernate在做,宏观上面看,在JPA的统一之下Hibernate很良好的运行. 最近在

spring Data jpa简介_动力节点Java学院整理

前言 自 JPA 伴随 Java EE 5 发布以来,受到了各大厂商及开源社区的追捧,各种商用的和开源的 JPA 框架如雨后春笋般出现,为开发者提供了丰富的选择.它一改之前 EJB 2.x 中实体 Bean 笨重且难以使用的形象,充分吸收了在开源社区已经相对成熟的 ORM 思想.另外,它并不依赖于 EJB 容器,可以作为一个独立的持久层技术而存在.目前比较成熟的 JPA 框架主要包括 Jboss 的 Hibernate EntityManager.Oracle 捐献给 Eclipse 社区的 E

spring data jpa使用详解(推荐)

使用Spring data JPA开发已经有一段时间了,这期间学习了一些东西,也遇到了一些问题,在这里和大家分享一下. 前言: Spring data简介: Spring Data是一个用于简化数据库访问,并支持云服务的开源框架.其主要目标是使得对数据的访问变得方便快捷,并支持map-reduce框架和云计算数据服务. Spring Data 包含多个子项目: Commons - 提供共享的基础框架,适合各个子项目使用,支持跨数据库持久化 JPA - 简化创建 JPA 数据访问层和跨存储的持久层

Spring Data JPA实现动态查询的两种方法

前言 一般在写业务接口的过程中,很有可能需要实现可以动态组合各种查询条件的接口.如果我们根据一种查询条件组合一个方法的做法来写,那么将会有大量方法存在,繁琐,维护起来相当困难.想要实现动态查询,其实就是要实现拼接SQL语句.无论实现如何复杂,基本都是包括select的字段,from或者join的表,where或者having的条件.在Spring Data JPA有两种方法可以实现查询条件的动态查询,两种方法都用到了Criteria API. Criteria API 这套API可用于构建对数据

Spring Data JPA进行数据分页与排序的方法

一.导读 如果一次性加载成千上万的列表数据,在网页上显示将十分的耗时,用户体验不好.所以处理较大数据查询结果展现的时候,分页查询是必不可少的.分页查询必然伴随着一定的排序规则,否则分页数据的状态很难控制,导致用户可能在不同的页看到同一条数据.那么,本文的主要内容就是给大家介绍一下,如何使用Spring Data JPA进行分页与排序. 二.实体定义 我们使用一个简单的实体定义:Article(文章) @Data @AllArgsConstructor @NoArgsConstructor @Bu