Intro
I will get data from a table what has foreign keys in this time.
Can't execute "gradlew run" on Fedora
I get permission error when I execute "gradlew run" on Fedora.
So I change the permission like below.
sudo chmod +x ./gradlew
Foreign key
I add some new tables.
SQL
CREATE TABLE categories
(id bigint identity(1, 1) primary key,
name varchar(256) not null,
last_update_date datetimeoffset default current_timestamp AT TIME ZONE 'Tokyo Standard Time');
CREATE TABLE posts
(id bigint identity(1, 1) primary key,
title varchar(512) not null,
contents text,
users_id bigint not null,
categories_id bigint not null,
last_update_date datetimeoffset default current_timestamp AT TIME ZONE 'Tokyo Standard Time'
);
ALTER TABLE posts
ADD CONSTRAINT FK_posts_users FOREIGN KEY (users_id) REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE posts
ADD CONSTRAINT FK_posts_categories FOREIGN KEY (categories_id) REFERENCES categories(id)
ON DELETE CASCADE
ON UPDATE CASCADE;
Model classes
Category.java
package micronaut.sample.posts;
import java.time.LocalDateTime;
import io.micronaut.data.annotation.GeneratedValue;
import io.micronaut.data.annotation.Id;
import io.micronaut.data.annotation.MappedEntity;
import io.micronaut.data.annotation.MappedProperty;
import io.micronaut.data.model.DataType;
import io.micronaut.serde.annotation.Serdeable;
@Serdeable
@MappedEntity("categories")
public class Category {
@GeneratedValue
@Id
private Long id;
@MappedProperty(definition = "VARCHAR(256)", type = DataType.STRING)
private String name;
private LocalDateTime lastUpdateDate;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public LocalDateTime getLastUpdateDate() {
return lastUpdateDate;
}
public void setLastUpdateDate(LocalDateTime lastUpdateDate) {
this.lastUpdateDate = lastUpdateDate;
}
}
Category.java
package micronaut.sample.posts;
import java.time.LocalDateTime;
import io.micronaut.data.annotation.GeneratedValue;
import io.micronaut.data.annotation.Id;
import io.micronaut.data.annotation.MappedEntity;
import io.micronaut.data.annotation.MappedProperty;
import io.micronaut.data.model.DataType;
import io.micronaut.serde.annotation.Serdeable;
import micronaut.sample.users.User;
@Serdeable
@MappedEntity("posts")
public class Post {
@GeneratedValue
@Id
private Long id;
@MappedProperty(definition = "VARCHAR(512)", type = DataType.STRING)
private String title;
private String contents;
private User users;
private Category categories;
private LocalDateTime lastUpdateDate;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getContents() {
return contents;
}
public void setContents(String contents) {
this.contents = contents;
}
public User getUsers() {
return users;
}
public void setUsers(User users) {
this.users = users;
}
public Category getCategories() {
return categories;
}
public void setCategories(Category categories) {
this.categories = categories;
}
public LocalDateTime getLastUpdateDate() {
return lastUpdateDate;
}
public void setLastUpdateDate(LocalDateTime lastUpdateDate) {
this.lastUpdateDate = lastUpdateDate;
}
}
PostRepository.java
package micronaut.sample.posts;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.r2dbc.annotation.R2dbcRepository;
import io.micronaut.data.repository.reactive.ReactiveStreamsCrudRepository;
import jakarta.validation.constraints.NotNull;
import reactor.core.publisher.Flux;
@R2dbcRepository(dialect = Dialect.SQL_SERVER)
public interface PostRepository extends ReactiveStreamsCrudRepository<Post, Long> {
@Override
Flux<Post> findAll();
}
@Join
I still cannot get users and categories tables.
[{
id: 2,
title: "Hello",
contents: "World",
categories: {
id: 1
},
lastUpdateDate: "2024-01-19T01:45:40.077"
}]
To get them, I have to add "@Join" and join tables.
PostRepository.java
package micronaut.sample.posts;
import io.micronaut.data.annotation.Join;
...
@R2dbcRepository(dialect = Dialect.SQL_SERVER)
public interface PostRepository extends ReactiveStreamsCrudRepository<Post, Long> {
@Override
// Add "@Join" to connect to relational tables
@Join("users")
@Join("categories")
Flux<Post> findAll();
}
Results
[{
id: 1,
title: "Hello",
contents: "World",
users: {
name: "Masanori",
id: 1,
lastUpdateDate: "2023-12-16T18:36:20.187"
},
categories: {
id: 1,
name: "Programming",
lastUpdateDate: "2024-01-19T01:38:22.857"
},
lastUpdateDate: "2024-01-19T02:05:16.713"
}]
One to many
I will get an exception when I try getting "Category" data like below.
Category.java
...
@Serdeable
@MappedEntity("categories")
public class Category {
...
// Add this.
private List<Post> posts;
...
public List<Post> getPosts() {
return posts;
}
public void setPosts(List<Post> posts) {
this.posts = posts;
}
}
CategoryRepository.java
package micronaut.sample.posts;
import io.micronaut.data.annotation.Join;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.r2dbc.annotation.R2dbcRepository;
import io.micronaut.data.repository.reactive.ReactiveStreamsCrudRepository;
import reactor.core.publisher.Flux;
@R2dbcRepository(dialect = Dialect.SQL_SERVER)
public interface CategoryRepository extends ReactiveStreamsCrudRepository<Category, Long> {
@Override
@Join("posts")
Flux<Category> findAll();
}
Exception
...
21:56:09.881 [reactor-tcp-epoll-2] ERROR i.m.http.server.RouteExecutor - Unexpected error occurred: 列名 'posts' が無効です。
io.r2dbc.mssql.ExceptionFactory$MssqlBadGrammarException: 列名 'posts' が無効です。
at io.r2dbc.mssql.ExceptionFactory.createException(ExceptionFactory.java:93)
at io.r2dbc.mssql.DefaultMssqlResult.lambda$doMap$5(DefaultMssqlResult.java:229)
at reactor.core.publisher.FluxHandleFuseable$HandleFuseableSubscriber.onNext(FluxHandleFuseable.java:179)
at reactor.core.publisher.FluxWindowPredicate$WindowFlux.drainRegular(FluxWindowPredicate.java:670)
...
To resolve this, I should add "@Relation" annotations into "Category.java" and "Post.java".
Category.java
...
import io.micronaut.data.annotation.Relation;
import io.micronaut.data.model.DataType;
import io.micronaut.serde.annotation.Serdeable;
@Serdeable
@MappedEntity("categories")
public class Category {
...
// Add this.
@Relation(value = Relation.Kind.ONE_TO_MANY, mappedBy = "categories")
private List<Post> posts;
...
}
Post.java
...
import io.micronaut.data.annotation.Relation;
import io.micronaut.data.model.DataType;
import io.micronaut.serde.annotation.Serdeable;
import micronaut.sample.users.User;
@Serdeable
@MappedEntity("posts")
public class Post {
...
// Add this
@Relation(value = Relation.Kind.MANY_TO_ONE)
private Category categories;
...
}
JSON error
An exception occurs when returning "Flux" data obtained from the DB as response data.
PostService.java
package micronaut.sample.posts;
import java.time.LocalDateTime;
import io.micronaut.transaction.annotation.Transactional;
import jakarta.inject.Singleton;
import reactor.core.publisher.Flux;
@Singleton
public class PostService {
private final PostRepository posts;
private final CategoryRepository categories;
public PostService(PostRepository posts,
CategoryRepository categories) {
this.posts = posts;
this.categories = categories;
}
...
public Flux<Category> findAllCategories() {
return categories.findAll();
}
}
PostController.java
package micronaut.sample.posts;
import io.micronaut.http.annotation.Controller;
import io.micronaut.http.annotation.Get;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;
@Controller("/posts")
public class PostController {
private final PostService posts;
public PostController(PostService posts) {
this.posts = posts;
}
...
@Get("/categories/all")
public Flux<Category> findAllCategories() {
return posts.findAllCategories();
}
}
Exception
Internal Server Error: Error encoding object [micronaut.sample.posts.Category@6c965783] to JSON: Maximum depth exceeded while serializing.
The maximum nesting depth can be increased, if necessary, using the micronaut.serde.maximum-nesting-depth config property.
Two classes reference each other, and it causes an infinite loop when generating JSON values.
To resolve this problem, I can "@JsonManagedReference" and "@JsonBackReference" annotations.
Category.java
...
import io.micronaut.data.annotation.Relation;
import io.micronaut.data.model.DataType;
import io.micronaut.serde.annotation.Serdeable;
import com.fasterxml.jackson.annotation.JsonManagedReference;
@Serdeable
@MappedEntity("categories")
public class Category {
...
// Add this.
@JsonManagedReference
@Relation(value = Relation.Kind.ONE_TO_MANY, mappedBy = "categories")
private List<Post> posts;
...
}
Post.java
...
import io.micronaut.data.annotation.Relation;
import io.micronaut.data.model.DataType;
import io.micronaut.serde.annotation.Serdeable;
import micronaut.sample.users.User;
import com.fasterxml.jackson.annotation.JsonBackReference;
@Serdeable
@MappedEntity("posts")
public class Post {
...
// Add this
@JsonBackReference
@Relation(value = Relation.Kind.MANY_TO_ONE)
private Category categories;
...
}
- 4 Jackson Annotations - Micronaut Framework
- Jackson – Bidirectional Relationships - Baeldung
- [Java] エンティティのJSON化において、無限ループを防ぐ - Qiita
@Query
I can't add the "@Relation" annotation into methods what have "@Query" annotations.
CategoryRepository.java
package micronaut.sample.posts;
import io.micronaut.data.annotation.Join;
import io.micronaut.data.annotation.Query;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.r2dbc.annotation.R2dbcRepository;
import io.micronaut.data.repository.reactive.ReactiveStreamsCrudRepository;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;
@R2dbcRepository(dialect = Dialect.SQL_SERVER)
public interface CategoryRepository extends ReactiveStreamsCrudRepository<Category, Long> {
@Override
@Join("posts")
Flux<Category> findAll();
@Query("SELECT * FROM categories WHERE name = :categoryName")
// Don't do this
@Join("posts")
Flux<Category> findCategoriesByName(String categoryName);
}
Because the model class doesn't have "posts" table elements, I will get an exception like below.
Exception
Internal Server Error: [posts_id] does not exist in column names [name, id, last_update_date]
So I should create another class to set the values.
SearchCategory.java
package micronaut.sample.posts.dto;
import java.time.LocalDateTime;
import io.micronaut.serde.annotation.Serdeable;
import io.micronaut.data.annotation.Id;
import io.micronaut.data.annotation.MappedEntity;
import io.micronaut.data.annotation.MappedProperty;
import io.micronaut.data.model.DataType;
@Serdeable
@MappedEntity
public class SearchCategory {
@Id
private Long categoryId;
@MappedProperty(definition = "VARCHAR(256)", type = DataType.STRING)
private String categoryName;
private Long postId;
@MappedProperty(definition = "VARCHAR(512)", type = DataType.STRING)
private String title;
private String contents;
private LocalDateTime lastUpdateDate;
public Long getCategoryId() {
return categoryId;
}
public void setCategoryId(Long categoryId) {
this.categoryId = categoryId;
}
public String getCategoryName() {
return categoryName;
}
public void setCategoryName(String categoryName) {
this.categoryName = categoryName;
}
public Long getPostId() {
return postId;
}
public void setPostId(Long postId) {
this.postId = postId;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getContents() {
return contents;
}
public void setContents(String contents) {
this.contents = contents;
}
public LocalDateTime getLastUpdateDate() {
return lastUpdateDate;
}
public void setLastUpdateDate(LocalDateTime lastUpdateDate) {
this.lastUpdateDate = lastUpdateDate;
}
}
CategoryRepository.java
...
@R2dbcRepository(dialect = Dialect.SQL_SERVER)
public interface CategoryRepository extends ReactiveStreamsCrudRepository<Category, Long> {
...
@Query("""
SELECT ctg.id as category_id, ctg.name as category_name, pst.id as post_id, pst.title, pst.contents, pst.last_update_date FROM categories ctg
INNER JOIN posts pst ON ctg.id = pst.categories_id
WHERE ctg.name = :categoryName
""")
Flux<SearchCategory> findCategoriesByName(String categoryName);
}
Top comments (0)