Lazy loading and prefetch#

A list often has something you want to be able to return in detail, but frequently do not. AsyncData / AsyncList issue the SQL only at the moment the value is referenced.

protected List<Data> load () {

	return BlogExample.db().selectList(
		SQL.select()
			.from(Comment.instance())
			.where(Comment.post_id.eq(postId))
			.orderBy(Comment.created_at)
	);

}

Data post = ...;
post.put("comments", new CommentList(post.getLong(Post.id)));

// no SQL up to here
post.getJsonString();   // ← here it fires

What you implement#

AsyncData behaves as a Data and AsyncList as a List, so once loaded they are ordinary data.

What you can look at without triggering a load#

list.isLoaded()       // already loaded?
list.isLoadFailed()   // did it fail?
list.loadedValues()   // only the values already held
list.loadedSize()     // only the count already held

toString() does not trigger a load either. One log line must not fire SQL down every branch of the tree, and it does not.

hashCode() / equals() look at nothing but hashKey(). Comparing two of them fires no SQL.

When a load fails#

No exception is thrown. It logs and moves on.

A failure still counts as "loaded". Otherwise every reference would keep re-issuing a query that is going to fail. You can tell it failed from isLoadFailed().

When it becomes N+1#

Hang one off every row of a list, turn the whole thing into JSON, and 1 + N queries fly.

fetch 20 posts             1 query
  comments for post 1      1 query
  comments for post 2      1 query
  ...                      ×20

Prefetch is what turns this into two.

Prefetch#

Write what load() does, with an IN clause.

protected Map<Object, List<Data>> loadBatch (List<Object> ids) {

	List<Data> rows = BlogExample.db().selectList(
		SQL.select()
			.from(Comment.instance())
			.where(Comment.post_id.in(ids))
			.orderBy(Comment.post_id, Comment.created_at)
	);

	if (rows == null) {
		// 引けなかった。1つも読み込み済みにしないよう、例外にして個別読みへ落とす
		throw new IllegalStateException("コメントを引けませんでした: " + BlogExample.db().getError());
	}

	Map<Object, List<Data>> byPost = new LinkedHashMap<>();

	for (Data row : rows) {
		byPost
			.computeIfAbsent(row.getLong(Comment.post_id), key -> new ArrayList<>())
			.add(row);
	}

	return byPost;

}

Along with it, declare the unit you group by.

@Override
public String batchKey () { return "CommentList"; }

@Override
public Object batchId () { return postId; }

Unloaded branches that share a batchKey are rolled into one loadBatch call. ids arrives holding the batchId of the other branches too.

Keep load() and loadBatch() side by side in the same file. Fix one and not the other and the results diverge. That is the reason they are not kept apart.

Running it#

get("/posts/with-comments", context -> {

	List<Data> posts = new ArrayList<>();

	for (Data row : BlogApp.listPosts()) {
		posts.add(BlogApp.withComments(row));
	}

	context.response().json("posts", posts);

	/*
	 * ここまでで飛んだ SQL は記事の1本だけ。
	 * これを呼ばずに JSON にすると、記事の数だけコメントの SQL が飛ぶ。
	 */
	AsyncPrefetch.run(context.response());

});

AsyncPrefetch.run(...) walks the tree, collects the unloaded branches that have a batchKey, and fills them. The walk itself fires no SQL (it does not look inside an unloaded branch).

You can also have it run automatically, right before the response is sent.

async {
	prefetch {
		on_response = true
		max_depth   = 5
	}
}

The default is false, so that no hidden I/O is created and so that turning it on does not change behaviour.

Prefetch or no prefetch, the result is the same#

The only thing that changes is the number of queries.

• A batchId that loadBatch did not return is treated exactly as if fetching it individually had returned no rows
• If loadBatch throws, not one member of that group is marked loaded. Each is then loaded individually with load(), so the result is unchanged (only the speed goes back)
• A class with no loadBatch is loaded individually, as before

How it terminates#

Filling branches grows new ones, so it repeats until no more grow. Three things stop it from spinning forever.

Choosing table nesting on or off, per JSON#

SELECT results are nested under the table name.

{"post": {"id": 1, "title": "hello"}}

Whether setData flattens that (flattenTable) or keeps it as-is (extractTableData) used to be fixed the moment you wrote the class. One AsyncData could emit one shape, and only one.

Want the admin API nested and the shop API flat, and you end up writing two classes with the same content. Fix one and you will not notice the other.

The shape is chosen when you produce the JSON.

data.getJsonString(TableNest.ON);    // {"post": {"id": 1}, "comments": [...]}
data.getJsonString(TableNest.OFF);   // {"id": 1, "comments": [...]}

You can set it per response, too.

path("/admin", () -> {
	before(context -> context.response().tableNest(TableNest.ON));
	get("/posts/{id}", context -> context.response().json("post", new PostData(id)));
});

path("/shop", () -> {
	before(context -> context.response().tableNest(TableNest.OFF));
	get("/posts/{id}", context -> context.response().json("post", new PostData(id)));
});

Both are returning the same class.

How does it know the shape?#

AsyncData / AsyncList hold on to the raw data that load() returned. That raw data knows which key belongs to which column of which table.

{"post": {"id": 1, "title": "..."}}
  ↑ table name   ↑ column name

So whether you wrote setData with flattenTable or with extractTableData, it can be reassembled into either shape. There is nothing to add on the implementation side.

Anything that is not a column does not move#

Children added in setRelationData (AsyncData / AsyncList) and computed values are not columns of the raw data, so they always stay at the top level.

{"post": {"id": 1, "title": "..."}, "comments": [...]}