DEV Community

Masui Masanori
Masui Masanori

Posted on

1

[Micronaut] Accessing SQL Server 2

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
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode
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;
Enter fullscreen mode Exit fullscreen mode

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;
    }
}
Enter fullscreen mode Exit fullscreen mode

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;
    }
}
Enter fullscreen mode Exit fullscreen mode

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();   
}
Enter fullscreen mode Exit fullscreen mode

@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"
}]
Enter fullscreen mode Exit fullscreen mode

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();   
}
Enter fullscreen mode Exit fullscreen mode

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"
}]
Enter fullscreen mode Exit fullscreen mode

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;
    }
}
Enter fullscreen mode Exit fullscreen mode

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(); 
}
Enter fullscreen mode Exit fullscreen mode

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)
...
Enter fullscreen mode Exit fullscreen mode

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;
...
}
Enter fullscreen mode Exit fullscreen mode

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;
...
}
Enter fullscreen mode Exit fullscreen mode

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();
    }
}
Enter fullscreen mode Exit fullscreen mode

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();
    }
}
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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;
...
}
Enter fullscreen mode Exit fullscreen mode

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;
...
}
Enter fullscreen mode Exit fullscreen mode

@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);
}
Enter fullscreen mode Exit fullscreen mode

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]
Enter fullscreen mode Exit fullscreen mode

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;
    }
}
Enter fullscreen mode Exit fullscreen mode

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);   
}
Enter fullscreen mode Exit fullscreen mode

Top comments (0)

Image of Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more