Masui Masanori

Posted on Jan 19

[Micronaut] Accessing SQL Server 2

#micronaut #java

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

gradlew: Permission Denied - Stack Overflow

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;
...
}

@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);   
}

DEV Community

[Micronaut] Accessing SQL Server 2

Intro

Can't execute "gradlew run" on Fedora

Foreign key

SQL

Model classes

Category.java

Category.java

PostRepository.java

@Join

PostRepository.java

Results

One to many

Category.java

CategoryRepository.java

Exception

Category.java

Post.java

JSON error

PostService.java

PostController.java

Exception

Category.java

Post.java

@Query

CategoryRepository.java

Exception

SearchCategory.java

CategoryRepository.java

Top comments (0)

Read next

How to export images from charts?

Meditation Made Simple: Find Inner Peace with Generative AI 🌿

Maintaining personal growth and mental health: habits and ideas to be aware of during job hunting (Bite-size Article)

TURBO Rails