java - 使用Ebean，填充存储另一个相关实体属性的实体列表的属性值

Question

我将尝试直接用代码描述我的问题，并在之后说出我需要什么，请注意，我最小化了我的代码以解决我遇到的问题:

我从实体获取提要列表:

@Entity
public class Feed {

    @Id
    @Column
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @ManyToOne
    @JoinColumn(name = "event_id")
    private Event event;

    @ManyToOne
    @JoinColumn(name = "event_trigger_id")
    private EventTypeTrigger eventTrigger;
}

在这里，我试图让这个实体填充正确的count值。

@Entity
public class EventTrigger {

    @Id
    @Column
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @Column(nullable = false)
    private String title;

    @Transient
    private int count;
}

存储计数数据的 Feed 实体。

@Entity
public class EventTriggerFeed {
    @Id
    @Column
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;


    @Column(nullable = false)
    private int count;

    @ManyToOne
    @JoinColumn(name = "event_id", nullable = false)
    @JsonBackReference
    private Event event;

    @ManyToOne
    @JoinColumn(name = "event_trigger_id", nullable = false)
    @JsonBackReference
    private EventTrigger eventTrigger;
}

从我尝试调用的服务中:

public List<Feed> findUserFeeds(int userId) {
        return Ebean.find(Feed.class).findList();
    }

我可以像这样获得每个触发器的计数:

public int getCountFromEventTriggerFeed(int triggerId, int eventId) {
        return Ebean.find(EventTriggerFeed.class)
                .where().eq("eventTrigger.id",triggerId).eq("event.id",eventId)
                .findUnique().getCount();
    }

我只想获取 Feed 列表，其中包含 Event 信息和 EventTrigger 信息，并使用 count 属性填充来自 EventTriggerFeed 的计数值

我只是有点坚持完美的方式，没有那么多迭代，我正在考虑获取 Feed 行内每个 EventTrigger 的计数值，但这可能会导致我接到很多电话:

for (Feed feed : findUserFeeds(userId)) {
    int eventId = feed.getEvent().getId();
    int triggerId = feed.getEventTrigger().getId();
    feed.getEventTrigger().setCount(getCountFromEventTriggerFeed(triggerId, eventId))
}

但是你能想象如果我有大约 50 个提要吗？

有没有关于如何优化这个令人头疼的问题的想法？

Answer 1

我通过在 .sql 文件中创建 View 解决了这个问题，如下所示:

CREATE OR REPLACE VIEW `event_feed_summary_view` AS
  SELECT
    max(feed.trigger_count) * up.amount AS total_amount,
    feed.trigger_count,
    uf.feed_id,
    feed.event_id,
    feed.event_trigger_id,
    up.amount
  FROM event_trigger_feeds feed
    INNER JOIN event_type_triggers trig ON feed.event_trigger_id AND trig.id
    INNER JOIN user_feed uf ON uf.event_id = feed.event_id AND uf.event_trigger_id = feed.event_trigger_id
  GROUP BY uf.feed_id, feed.event_id, feed.event_trigger_id;

我将此代码放入 conf/db_views/create_views.sql

我在模块内部启动时调用了这个文件OnStartup:

@Singleton
public class OnStartup {

    @Inject
    public OnStartup(final Configuration configuration, final Environment environment, Database db) {


        // Create views
            Logger.info("Creating / Replacing the Tables views in database");
            InputStream is = environment.classLoader().getResourceAsStream("db_views/create_views.sql");
            String sql;
            try {
                sql = IOUtils.toString(is);
                if (sql != null) {
                    Connection conn = db.getConnection();
                    try {
                        Statement stmt = conn.createStatement();
                        try {
                            stmt.execute(sql);
                            Logger.info("Views Created");
                        } finally {
                            try {
                                stmt.close();
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                        }
                    } catch (SQLException e) {
                        Logger.error("Could create Statement ");
                        e.printStackTrace();
                    } finally {
                        try {
                            conn.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
            } catch (IOException e) {
                Logger.error("Could not find create view script");
                e.printStackTrace();
            }
    }
}

使用 Ebean，我使用 SqlQuery 得到结果:

public long getTotal(int feedId) {
    String sql = "select sum(total_amount) as total from event_feed_summary_view where feed_id = :feed_id;";
    SqlQuery sqlQuery = Ebean.createSqlQuery(sql);
    sqlQuery.setParameter("feed_id", feedId);
    SqlRow r = sqlQuery.findUnique();
    if (r != null && r.getLong("total") != null) {
        return r.getLong("total");
    }
    return 0;
}