[Micronaut] Accessing SQL Server 2
Tags: micronaut, java
Published: 2024-01-19 14:20:48

## Intro
I will get data from a table what has foreign keys in this time. 

* [[Micronaut] Accessing SQL Server 1](https://dev.to/masanori_msl/mictonaut-accessing-sql-server-1-1b3a)
* [Try Micronaut](https://dev.to/masanori_msl/try-micronaut-41o4)
* [Try SQL Server on Fedora](https://dev.to/masanori_msl/try-sql-server-on-fedora-4dhi)

## 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
```

* [gradlew: Permission Denied - Stack Overflow](https://stackoverflow.com/questions/17668265/gradlew-permission-denied)

## Foreign key
I add some new tables.

### SQL
```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');
```

```sql
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
```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
```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
```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.

```json
[{
    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
```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
```json
[{
    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"
}]
```
* [Micronaut Data](https://micronaut-projects.github.io/micronaut-data/latest/guide/index.html)
* [micronaut-data/doc-examples/r2dbc-example-java - GitHub](https://github.com/micronaut-projects/micronaut-data/tree/master/doc-examples/r2dbc-example-java)

### One to many
I will get an exception when I try getting "Category" data like below.

#### Category.java
```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
```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
```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
```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;
...
}
```

* [6.5 Mapping Entities - Micronaut Data](https://micronaut-projects.github.io/micronaut-data/latest/guide/#sqlMapping)
* [Micronaut JDBC Many to Many relationship - StackOverflow](https://stackoverflow.com/questions/76532241/micronaut-jdbc-many-to-many-relationship)

### JSON error
An exception occurs when returning "Flux<Category>" data obtained from the DB as response data.

#### PostService.java
```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
```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
```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
```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](https://micronaut-projects.github.io/micronaut-serialization/latest/guide/#jacksonAnnotations)
* [Jackson – Bidirectional Relationships - Baeldung](https://www.baeldung.com/jackson-bidirectional-relationships-and-infinite-recursion)
* [[Java] エンティティのJSON化において、無限ループを防ぐ - Qiita](https://qiita.com/sengoku/items/56bc6319759fee6d15e3)


### @Query
I can't add the "@Relation" annotation into methods what have "@Query" annotations.

#### CategoryRepository.java
```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
```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
```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);   
}
```