基于jpa实现查询（各类筛选，导出，分页，排序，合计），建议后台使用

最新推荐文章于 2024-12-16 20:59:50 发布

Years_of_sorrow

最新推荐文章于 2024-12-16 20:59:50 发布

阅读量485

点赞数 3

分类专栏： java-jpa java基础文章标签： java

本文链接：https://blog.csdn.net/Years_of_sorrow/article/details/136617071

版权

java基础同时被 2 个专栏收录

7 篇文章 0 订阅

订阅专栏

java-jpa

2 篇文章 0 订阅

订阅专栏

后台全套查询

1.创建jpa工具类（生成Querydsl）

（1）pom文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">


    <parent>
        <artifactId>***-parent</artifactId>
        <groupId>com.project.****</groupId>
        <version>2.0.0.0</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>qdslhelper</artifactId>


    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>


    <dependencies>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-sql</artifactId>
            <version>4.2.0</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>com.querydsl</groupId>
                <artifactId>querydsl-maven-plugin</artifactId>
                <version>4.2.0</version>
                <executions>
                    <execution>
                        <goals>
                            <goal>export</goal>
                        </goals>
                    </execution>
                </executions>

                <configuration>
                    <jdbcDriver>com.mysql.jdbc.Driver</jdbcDriver>
                    <jdbcUrl>jdbc:mysql://***/****</jdbcUrl>
                    <jdbcUser>***</jdbcUser>
                    <jdbcPassword>*****</jdbcPassword>
                    <packageName>com.nonobank.credit.domain</packageName>
                    <targetFolder>${project.basedir}/target/generated-sources/java</targetFolder>
                    <namePrefix>T</namePrefix>
                    <exportBeans>true</exportBeans>
                    <tableNamePattern>
                        "需要生产实体的名字"
                    </tableNamePattern>
                    <sourceFolder />
                </configuration>

                <dependencies>
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>8.0.15</version>
                    </dependency>
                </dependencies>
            </plugin>
        </plugins>
    </build>


</project>

2.打包后生成指定的Querydsl，放入自己的服务

3.jpa查询工具类（可自行进行扩展）

（1）排序，分页工具类

//排序，分页工具类

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;

public class PageAndSortReq {
    private Integer page;
    private Integer size;
    private List<Order> sort;

    public Pageable getPageable() {
        List<Sort.Order> sortOrders = new ArrayList<>();
        if (page == null) {
            page = 0;
        }
        if (size == null) {
            size = 20;
        }
        if (sort != null && sort.size() > 0) {
            for (Order order : sort) {
                sortOrders.add(new Sort.Order(Sort.Direction.valueOf(order.getDirection()), order.getProperty()));
            }
            return new PageRequest(page, size, new Sort(sortOrders));
        } else {
            return new PageRequest(page, size);
        }
    }

    public static class Order {
        private String direction;
        private String property;

        public String getDirection() {
            return direction;
        }

        public Order setDirection(String direction) {
            this.direction = direction;
            return this;
        }

        public String getProperty() {
            return property;
        }

        public Order setProperty(String property) {
            this.property = property;
            return this;
        }
    }

    public Integer getPage() {
        return page;
    }

    public PageAndSortReq setPage(Integer page) {
        this.page = page;
        return this;
    }

    public Integer getSize() {
        return size;
    }

    public PageAndSortReq setSize(Integer size) {
        this.size = size;
        return this;
    }

    public List<Order> getSort() {
        return sort;
    }

    public PageAndSortReq setSort(List<Order> sort) {
        this.sort = sort;
        return this;
    }
}

（2）基础查询条件封装

//筛选条件封装

import com.querydsl.core.types.Predicate;
import com.querydsl.core.types.dsl.BooleanExpression;

import java.util.function.Supplier;


public class QdslPredicateBuilder  {
    private BooleanExpression b;

    public QdslPredicateBuilder and(BooleanExpression expression) {
        if (b != null) {
            b = b.and(expression);
        } else {
            b = expression;
        }
        return this;
    }

    public QdslPredicateBuilder andIfNotNull(Object obj, Supplier<BooleanExpression> supplier) {
        if (obj != null) {
            if (b != null) {
                b = b.and(supplier.get());
            } else {
                b = supplier.get();
            }
        }
        return this;
    }

    public QdslPredicateBuilder orIfNotNull(Object obj, Supplier<BooleanExpression> supplier) {
        if (obj != null) {
            if (b != null) {
                b = b.or(supplier.get());
            } else {
                b = supplier.get();
            }
        }
        return this;
    }

    
    public QdslPredicateBuilder() {
    }
}

（3）基础查询方法封装

//查询方法封装

import com.google.common.base.CaseFormat;
import com.querydsl.core.types.Order;
import com.querydsl.core.types.OrderSpecifier;
import com.querydsl.core.types.Predicate;
import com.querydsl.core.types.dsl.NumberPath;
import com.querydsl.core.types.dsl.PathBuilder;
import com.querydsl.sql.RelationalPath;
import com.querydsl.sql.SQLQuery;
import com.querydsl.sql.SQLQueryFactory;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.repository.support.PageableExecutionUtils;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Component
public class QdslQueryFactory {
    @Autowired
    private SQLQueryFactory sqlQueryFactory;


    @Transactional(readOnly = true)
    public <T> Page<T> findAll(RelationalPath<T> table, Predicate predicate, Pageable pageable) {
        SQLQuery<T> countQuery = sqlQueryFactory.selectFrom(table).where(predicate);
        SQLQuery<T> sqlQuery = sqlQueryFactory.selectFrom(table).where(predicate);

        sqlQuery.offset(pageable.getOffset());
        sqlQuery.limit(pageable.getPageSize());
        if (pageable.getSort() != null) {
            for (Sort.Order order : pageable.getSort()) {
                PathBuilder pathBuilder = new PathBuilder(sqlQuery.getType(), table.getTableName());
                sqlQuery.orderBy(new OrderSpecifier(Order.valueOf(order.getDirection().name())
                        , pathBuilder.get(upperCharToUnderLine(order.getProperty()))));
            }                              //上面这段修改是为了跟数据库字段的匹配规则相对应，如果数据库字段变化，这里需要进行相应的变化或者不变
        }
        return PageableExecutionUtils.getPage(sqlQuery.fetch(), pageable, () -> countQuery.fetchCount());
    }

    @Transactional(readOnly = true)
    public <T> List<T> findAllList(RelationalPath<T> table, Predicate predicate,String orderBy) {
        SQLQuery<T> sqlQuery = sqlQueryFactory.selectFrom(table).where(predicate);
        if(StringUtils.isNotBlank(orderBy)){
            PathBuilder pathBuilder = new PathBuilder(sqlQuery.getType(), table.getTableName());
            sqlQuery.orderBy(new OrderSpecifier(Order.valueOf("DESC"), pathBuilder.get(upperCharToUnderLine(orderBy))));
        }                                                                               //上面这段修改是为了跟数据库字段的匹配规则相对应，如果数据库字段变化，这里需要进行相应的变化或者不变
        return sqlQuery.fetch();
    }

    /**
     * 支持正序/倒序排序，支持多个字段排序
     * @param table
     * @param predicate
     * @param pageable
     * @param <T>
     * @return
     */
    @Transactional(readOnly = true)
    public <T> List<T> findAllListMultipleOrderBy(RelationalPath<T> table, Predicate predicate, Pageable pageable) {
        SQLQuery<T> sqlQuery = sqlQueryFactory.selectFrom(table).where(predicate);
        if (pageable.getSort() != null) {
            for (Sort.Order order : pageable.getSort()) {
                PathBuilder pathBuilder = new PathBuilder(sqlQuery.getType(), table.getTableName());
                sqlQuery.orderBy(new OrderSpecifier(Order.valueOf(order.getDirection().name())
                        , pathBuilder.get(upperCharToUnderLine(order.getProperty()))));
            }                              //上面这段修改是为了跟数据库字段的匹配规则相对应，如果数据库字段变化，这里需要进行相应的变化或者不变
        }                                                                           //上面这段修改是为了跟数据库字段的匹配规则相对应，如果数据库字段变化，这里需要进行相应的变化或者不变
        return sqlQuery.fetch();
    }


 



    /**
     * 字符串大写字母转下划线
     * @param param
     * @return
     */
    public static String upperCharToUnderLine(String param) {
        param = CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, param);
        return param;
    }

    /**
     * 字符串下划线转大写字母
     * @param param
     * @return
     */
    public static String UnderLineToUpperChar(String param) {
        param = CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, param);
        return param;
    }



}

（4）配置引入

import com.querydsl.sql.MySQLTemplates;
import com.querydsl.sql.SQLQueryFactory;
import com.querydsl.sql.SQLTemplates;
import com.querydsl.sql.spring.SpringConnectionProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.inject.Provider;
import javax.sql.DataSource;
import java.sql.Connection;

@Configuration
public class QdslSqlConfiguration {

    @Bean
    @Autowired
    public SQLQueryFactory queryFactory(DataSource dataSource) {
        SQLTemplates templates = MySQLTemplates.builder().build(); //change to your Templates
        com.querydsl.sql.Configuration configuration = new com.querydsl.sql.Configuration(templates);
        Provider<Connection> provider = new SpringConnectionProvider(dataSource);
        return new SQLQueryFactory(configuration, provider);
    }

}

4.查询



@Service
public class FirmDealQueryServiceImpl {


    @Autowired
    private QdslQueryFactory qdsl;

    @Autowired
    private PredicateServiceImpl predicateService;


    public Page<ViewTqQueryFirmDealDetail> getFirmDealDetail(TradingQueryAccountFundsReq.Deal req) {
        TViewTqTradeOrderH data = TViewTqTradeOrderH.viewTqTradeOrderH;
        Predicate predicate= predicateService.getFirmDealDetailPredicate(data,req);
        Page<ViewTqQueryFirmDealDetail> page = qdsl.findAll(data, predicate, req.getPageable());
        return page;

  }

（1）查询条件封装

//封装查询工具类

@Service
@Slf4j
public class PredicateServiceImpl {

    @Autowired
    JPAQueryFactory queryFactory;


    @Autowired
    private FirmServiceImpl firmService;

    public Predicate getFirmDealDetailPredicate(TViewTqTradeOrderH data, TradingQueryAccountFundsReq.Deal req){
        QdslPredicateBuilder qdslPredicateBuilder = new ParticpantQdslPredicateBuilder().particpant(req,data);

        if(null!=req.getSize()&&req.getSize().equals(0)){
            req.setSize(9999);
        }

        Predicate predicate = qdslPredicateBuilder
//模糊查询
                .andIfNotNull(req.getRealName(),()->data.realName.like(req.getRealName()+"%"))
//精确查询
                .andIfNotNull(req.getCellphone(), () -> data.irreversiblePhone.eq(finalMd5Code))
//区间查询
                .andIfNotNull(req.getStarTime(), () -> data.tradeDay.goe(req.getStarTime()))
                .andIfNotNull(req.getEndTime(), () -> data.tradeDay.loe(req.getEndTime()))
//范围查询
                .andIfNotNull(req.getFm1FirmIdList(),() -> 
data.fm1FirmId.in(req.getFm1FirmIdList()))
                .toPredicate();
        return predicate;
    }


}

5.导出

  public void getDealDetailExport(HttpServletResponse response, TradingQueryAccountFundsReq.Deal req) {
        TViewTqTradeOrderH data = TViewTqTradeOrderH.viewTqTradeOrderH;
        Predicate predicate= predicateService.getFirmDealDetailPredicate(data,req);

        List<ViewTqQueryFirmDealDetail> allListMultipleOrderBy = qdsl.findAllListMultipleOrderBy(data, predicate, req.getPageable());
        List<ExcelExportBean> exportBeanList = new ArrayList<>();
        exportBeanList.add(
                new ExcelExportBean()
                        .setCN(req.getCn())
                        .setEN(req.getEn())
                        .setSheetName("****")
                        .setDataList(allListMultipleOrderBy)
        );
        ExcelExportUtilFinal.exportExcel(exportBeanList, "****.xlsx", response);
    }

（1）导出工具类


public class ExcelExportBean {
    private String CN;
    private String EN;
    private List dataList;
    private String sheetName;

    public String getCN() {
        return CN;
    }

    public ExcelExportBean setCN(String CN) {
        this.CN = CN;
        return this;
    }

    public String getEN() {
        return EN;
    }

    public ExcelExportBean setEN(String EN) {
        this.EN = EN;
        return this;
    }

    public List getDataList() {
        return dataList;
    }

    public ExcelExportBean setDataList(List dataList) {
        this.dataList = dataList;
        return this;
    }

    public String getSheetName() {
        return sheetName;
    }

    public ExcelExportBean setSheetName(String sheetName) {
        this.sheetName = sheetName;
        return this;
    }
}

（2）导出方法封装


//导出方法封装

public class ExcelExportUtilFinal {
    public static void exportExcel(List<ExcelExportBean> list, String fileName, HttpServletResponse response) {
        SXSSFWorkbook wb = new SXSSFWorkbook();
        String strDateFormat = "yyyy-MM-dd HH:mm:ss";
        SimpleDateFormat sdf = new SimpleDateFormat(strDateFormat);
        for(ExcelExportBean bean:list) {
            //中文表头
            String[] CNarr = strToArr(bean.getCN());
            //英文元素
            String[] ENarr = strToArr(bean.getEN());

            SXSSFSheet sheet;
            if(StringUtils.isNotBlank(bean.getSheetName())){
                sheet = wb.createSheet(bean.getSheetName());
            }else{
                sheet = wb.createSheet();
            }

            //创建一个表头行
            SXSSFRow headRow = sheet.createRow(0);
            //放入对应的数值
            for (int i = 0; i < CNarr.length; i++) {
                //放入表头
                rowCreateCell(headRow, i).setCellValue(CNarr[i]);
            }
            List dataList = bean.getDataList();
            for (int j = 0; j < dataList.size(); j++) {
                Map<String, Object> map = getKeyAndValue(dataList.get(j));
                SXSSFRow thisRow = sheet.createRow(j + 1);
                for (int k = 0; k < ENarr.length; k++) {
                    Object value = map.get(ENarr[k]);
                    SXSSFCell thisCell = rowCreateCell(thisRow, k);
                    if (value != null) {
                        if (value instanceof BigDecimal) {
                            thisCell.setCellValue(((BigDecimal) value).doubleValue());
                        } else if (value instanceof Integer) {
                            thisCell.setCellValue(((Integer) value).intValue());
                        } else if (value instanceof Timestamp) {
                            String timeStr=value
                                    .toString()
                                    .substring(0, value.toString().indexOf("."));
                            thisCell.setCellValue(timeStr);
                        }
                        else {
                            thisCell.setCellValue(value.toString());
                        }
                    }
                }
            }
        }
        ByteArrayOutputStream output = new ByteArrayOutputStream();
        try {
            wb.write(output);
            response.setContentType(String.format("%s;charset=utf-8", "application/x"));
            response.setHeader("Content-Disposition", "attachment;filename=" +
                    new String(fileName.getBytes("utf-8"), "iso8859-1"));
            response.setHeader("Content-Length", String.valueOf(output.toByteArray().length));
            response.getOutputStream().write(output.toByteArray());
        } catch (Exception e) {
            e.printStackTrace();
        }


    }

    private static SXSSFCell rowCreateCell(SXSSFRow row, int index) {
        return row.createCell(index);
    }
    private static String[] strToArr(String str) {
        return str.split(",");
    }
    public static Map<String, Object> getKeyAndValue(Object obj) {
        Map<String, Object> map = new HashMap<>();
        // 得到类对象
        Class userCla = (Class) obj.getClass();
        /* 得到类中的所有属性集合 */
        String strDateFormat = "yyyy-MM-dd HH:mm:ss";
        SimpleDateFormat sdf = new SimpleDateFormat(strDateFormat);
        for (; userCla != Object.class; userCla = userCla.getSuperclass()) {//获取本身和父级对象
            Field[] fs = userCla.getDeclaredFields();//获取所有私有字段
            for (int i = 0; i < fs.length; i++) {
                Field f = fs[i];
                f.setAccessible(true); // 设置些属性是可以访问的
                Object val = new Object();
                try {
                    val = f.get(obj);
                    // 得到此属性的值
                    if (f.getName().contains("Time")) {
                        map.put(f.getName(),sdf.format(val));
                    }

                    if(f.getName().equals("orderStatus")){
                        if(val.equals(1)){
                            map.put(f.getName(), "****");
                        }else{
                            map.put(f.getName(), "***");
                        }
                    }else{
                        map.put(f.getName(), val);
                    }
                } catch (IllegalArgumentException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            }
        }


        return map;
    }

}

6.合计

    public TqTradeOrderH getDetailDealSum(Integer firmId, TradingQueryAccountFundsReq.Deal req) {

        TViewTqTradeOrderH data = TViewTqTradeOrderH.viewTqTradeOrderH;
        Predicate predicate= predicateService.getFirmDealDetailPredicate(data,req);
        TqTradeOrderH orderH = queryFactory
                .select(Projections.constructor(TqTradeOrderH.class,
                        data.quantity.sum(),
                        data.totalPrice.sum(),
                        data.closePl.sum(),
                        data.margin.sum(),
                        data.fee.sum(),
                        data.tqTip.sum(),
                        data.stampFee.sum(),
                        data.zrchj.sum()))
                .from(data)
                .where(predicate)
                .fetchOne();
        logger.info("tuple result:{}",orderH);
        return orderH;
    }