JPA EntityManager createNativeQuery 多表联合查询出某几个字段并转化为对象

接（//www.greatytc.com/p/69fa02602904）

在有某天有这么一个简单的需求，一个页面有N种查询方式，这N种参数可能有也可能没有，

并且

·只要查询其中的某几个字段（表和表有关联）
·要有分页
·要查询分页后的统计数据（简单，分页后查出来计算也行）
·要查询出分页之前所有数据的统计数据 （这……）
·需要查询的几个字段和条件有些在一个不关联的第三方表里面……这个表的那个字段有时候是填这个表的id，有时候是其他表的 或者随机字符串数据。
不能直接做关联。

处理方法：拼接原生查询 然后用EntityManager的createNativeQuery 来这行原生查询。

public Query createNativeQuery(String sqlString, Class resultClass);

resultClass是返回的实体类，这个的定义特别重要，由于多个表构成的字段查询，还可能会有sum之类的字段，但又不是真正在数据库有对应的表。

比如下面的sql

  String sqlstart = "SELECT " +
                "    operate.id, " +
                "    usr.real_name, " +
                "    usr.identity, " +
                "    depart.title as depart_title, " +
                "    capital.change_balance, " +
                "    capital.change_donation, " +
                "    operate.deposit as change_deposit, " +
                "    operate.manager_name, " +
                "    operate.create_time ";
String sqlend =
                "FROM " +
                        "    log_card_operate operate LEFT JOIN log_card_capital capital ON operate.id = capital.trade_no, " +
                        "  user usr, " +
                        "    depart depart " +
                        "WHERE " +
                        "  depart.id = usr.depart_id " +
                        " AND usr.id= operate.user_id ";

对应的实体类（注意字段和查询的要一致，下划线会自动对应驼峰，如果有些字段要特殊化命名，可以在sql里起别名）

import lombok.Data;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Transient;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

@Data //lombok
@Entity //这个注解必备 
public class StatCardOpen implements Serializable {
    @Transient
    private static final long serialVersionUID = 1L;
    @Id //这个注解必备，必须有个id
    private Integer id;
    private String realName;
    private String identity;
    private String departTitle;
    private BigDecimal changeBalance;
    private BigDecimal changeDonation;
    private BigDecimal changeDeposit;
    private String managerName;
    private Date createTime;

}

然后就可以直接查询出所需要的数据：

  Query query = em.createNativeQuery(sqlstart + sqlend + sqlorder, StatCardOpen.class);
   List<StatCardOpen> resultList = query.getResultList();

完整例子：

public Map<String, Object> openstat(int pageNum, int pageSize, Map<String, Object> params
    ) {

        Map<String, Object> resultMap = new HashMap<>();
        params = MapUtil.filterNullHashMapVal(params);
        Integer storeId = params.containsKey("storeId") ? Integer.parseInt(params.get("storeId").toString()) : 0;
        //增减原因 资金变动类型：1-充值、2-发放补贴、3-现金取款、4-补贴取款……
        Integer type = params.containsKey("type") ? Integer.parseInt(params.get("type").toString()) : 0;
        Integer managerId = params.containsKey("managerId") ? Integer.parseInt(params.get("managerId").toString()) : 0;
        Integer departId = params.containsKey("departId") ? Integer.parseInt(params.get("departId").toString()) : 0;
        Date beginTime = params.containsKey("beginTime") ? DateUtils.String2Date(params.get("beginTime").toString(), "yyyy-MM-dd HH:mm:ss") : null;
        Date endTime = params.containsKey("endTime") ? DateUtils.String2Date(params.get("endTime").toString(), "yyyy-MM-dd HH:mm:ss") : null;
        String keyword = params.containsKey("keyword") ? params.get("keyword").toString() : null;

        String sqlstart = "SELECT " +
                "    operate.id, " +
                "    usr.real_name, " +
                "    usr.identity, " +
                "    depart.title as depart_title, " +
                "    capital.change_balance, " +
                "    capital.change_donation, " +
                "    operate.deposit as change_deposit, " +
                "    operate.manager_name, " +
                "    operate.create_time ";
        String sqlstartCount = "SELECT operate.id,count(operate.id) as total," +
                " sum(capital.change_balance) as all_change_balance," +
                "sum(capital.change_donation) as all_change_donation," +
                "sum(operate.deposit) as all_change_deposit ";
        String sqlend =
                "FROM " +
                        "    log_card_operate operate LEFT JOIN log_card_capital capital ON operate.id = capital.trade_no, " +
                        "  user usr, " +
                        "    depart depart " +
                        "WHERE " +
                        "  depart.id = usr.depart_id " +
                        " AND usr.id= operate.user_id ";

        String sqlorder = "  order by operate.create_time DESC ";


        if (storeId != null && storeId > 0) {

            sqlend += " AND operate.store_id=:storeId";
        }
        if (type != null && type > 0) {
            sqlend += " AND operate.type=:type";
        }
        if (managerId != null && managerId > 0) {
            sqlend += " AND operate.manager_id=:managerId";
        }
        if (beginTime != null) {
            sqlend += " AND operate.create_time >= :beginTime";
        }
        if (endTime != null) {
            sqlend += " AND operate.create_time <= :endTime";
        }
        if (departId != null && departId > 0) {
            sqlend += " AND depart.id = :departId";
        }

        if (keyword != null && !keyword.isEmpty()) {

            sqlend += " and (usr.real_name like '%:keyword1%' or usr.identity like '%:keyword2%') ";
        }

        Query query = em.createNativeQuery(sqlstart + sqlend + sqlorder, StatCardOpen.class);
        Query queryCount = em.createNativeQuery(sqlstartCount + sqlend, StatCardOpenCount.class);

        if (storeId != null && storeId > 0) {
            query.setParameter("storeId", storeId);
            queryCount.setParameter("storeId", storeId);
        }
        if (type != null && type > 0) {
            query.setParameter("type", type);
            queryCount.setParameter("type", type);
        }
        if (managerId != null && managerId > 0) {
            query.setParameter("managerId", managerId);
            queryCount.setParameter("managerId", managerId);
        }
        if (beginTime != null) {
            query.setParameter("beginTime", beginTime);
            queryCount.setParameter("beginTime", beginTime);
        }
        if (endTime != null) {
            query.setParameter("endTime", endTime);
            queryCount.setParameter("endTime", endTime);
        }
        if (departId != null && departId > 0) {
            query.setParameter("departId", departId);
            queryCount.setParameter("departId", departId);
        }
        if (keyword != null && !keyword.isEmpty()) {
            query.setParameter("keyword1", keyword);
            query.setParameter("keyword2", keyword);

            queryCount.setParameter("keyword1", keyword);
            queryCount.setParameter("keyword2", keyword);
        }


        query.setFirstResult(pageNum * pageSize);
        query.setMaxResults(pageSize);

        List<StatCardOpen> resultList = query.getResultList();
        DebugPrint.json("resultList", resultList);
        BigDecimal sumChangeBalance = BigDecimal.ZERO;
        BigDecimal sumChangeDonation = BigDecimal.ZERO;
        BigDecimal sumChangeDeposit = BigDecimal.ZERO;

        List<StatCardOpenDTO> dtoList = new ArrayList<>();
        for (StatCardOpen statCardOpen :
                resultList) {
            if (statCardOpen != null) {
                DebugPrint.json("statCardOpen", statCardOpen);
                StatCardOpenDTO cardOpenDTO = new StatCardOpenDTO();
                BeanUtils.copyProperties(statCardOpen, cardOpenDTO);
                dtoList.add(cardOpenDTO);
                sumChangeBalance = sumChangeBalance.add(statCardOpen.getChangeBalance());
                sumChangeDonation = sumChangeDonation.add(statCardOpen.getChangeDonation());
                sumChangeDeposit = sumChangeDeposit.add(statCardOpen.getChangeDeposit());
            }


        }

        resultMap.put("sumChangeBalance", sumChangeBalance);
        resultMap.put("sumChangeDeposit", sumChangeDeposit);
        resultMap.put("sumChangeDonation", sumChangeDonation);
        resultMap.put("list", dtoList);
        resultMap.put("page", pageNum);
        resultMap.put("pageSize", pageSize);
        resultMap.put("number", dtoList.size());

        List<StatCardOpenCount> countList = queryCount.getResultList();
        if (countList != null && countList.size() > 0) {

            StatCardOpenCount statCardOpenCount = countList.get(0);
            if (statCardOpenCount == null) {
                resultMap.put("totle", 0);
                resultMap.put("allChangeBalance", 0);
                resultMap.put("allChangeDonation", 0);
                resultMap.put("allChangeDeposit", 0);
            } else {
                resultMap.put("totle", statCardOpenCount.getTotal());
                resultMap.put("allChangeBalance", statCardOpenCount.getAllChangeBalance());
                resultMap.put("allChangeDonation", statCardOpenCount.getAllChangeDonation());
                resultMap.put("allChangeDeposit", statCardOpenCount.getAllChangeDeposit());
            }

        }

        DebugPrint.json("resultMap", resultMap);

        return resultMap;
    }
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 216,651评论 6赞 501
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,468评论 3赞 392
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 162,931评论 0赞 353
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,218评论 1赞 292
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,234评论 6赞 388
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,198评论 1赞 299
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,084评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,926评论 0赞 274
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,341评论 1赞 311
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,563评论 2赞 333
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,731评论 1赞 348
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,430评论 5赞 343
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,036评论 3赞 326
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,676评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,829评论 1赞 269
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,743评论 2赞 368
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,629评论 2赞 354