[Micronaut] Accessing SQL Server 3

Intro

I will try inserting and update in this time.

• Try SQL Server on Fedora
• [Micronaut] Accessing SQL Server 1
• [Micronaut] Accessing SQL Server 2

Inserting and updating data

In simple cases, I can override method like below.

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 micronaut.sample.posts.dto.SearchCategory;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

@R2dbcRepository(dialect = Dialect.SQL_SERVER) 
public interface CategoryRepository extends ReactiveStreamsCrudRepository<Category, Long> {
...
    // Insert
    @Override
    Mono<Category> save(Category category);

    // Update
    @Override
    Mono<Category> update(Category category);    
}

Because "save" and "update" don't differetiate whether the model class data is newly created or retrieved from the DB, So I must use them properly.

PostService.java

package micronaut.sample.posts;

import java.time.LocalDateTime;
import io.micronaut.transaction.annotation.Transactional;
import jakarta.inject.Singleton;
import micronaut.sample.posts.dto.SearchCategory;
import micronaut.sample.users.UserRepository;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

@Singleton
public class PostService {
    private final PostRepository posts;
    private final CategoryRepository categories;
    private final UserRepository users;
    public PostService(PostRepository posts,
        CategoryRepository categories,
        UserRepository users) {
        this.posts = posts;
        this.categories = categories;
        this.users = users;
    }
...
    public Mono<String> updateCategory(String categoryName) {
        return categories.findByName(categoryName)
            .flatMap(c -> {
                c.setLastUpdateDate(LocalDateTime.now());
                // Don't do this because this insert new "Category" data.
                // I should write "categories.update(c)";
                return categories.save(c);
            })
            .switchIfEmpty(Mono.defer(() -> {
                Category c = new Category();
                c.setName(categoryName);
                c.setLastUpdateDate(LocalDateTime.now());
                return categories.save(c);
            }))
            .map(c -> "OK");
    }
}

Insert data into multiple tables

Unlike Entity Framework Core, they don't automatically insert or update data into relational tables.
Therefore, each data must be inserted or updated in order.

PostService.java

package micronaut.sample.posts;

import java.time.LocalDateTime;
import io.micronaut.transaction.annotation.Transactional;
import jakarta.inject.Singleton;
import micronaut.sample.posts.dto.SearchCategory;
import micronaut.sample.users.UserRepository;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

@Singleton
public class PostService {
...
    public Mono<String> addSamplePost(String title, String contents, String categoryName) {
        return categories.findByName(categoryName)
            .map(c -> {
                Post newPost = new Post();
                newPost.setTitle(title);
                newPost.setContents(contents);
                newPost.setCategories(c);
                newPost.setLastUpdateDate(LocalDateTime.now());
                return newPost;
            })
            .switchIfEmpty(Mono.defer(() -> {
                Category c = new Category();
                c.setName(categoryName);
                c.setLastUpdateDate(LocalDateTime.now());
                // Insert new "Category" data
                return categories.save(c)
                  .map(newCategory -> {
                      Post newPost = new Post();
                      newPost.setTitle(title);
                      newPost.setContents(contents);
                      newPost.setCategories(newCategory);
                      newPost.setLastUpdateDate(LocalDateTime.now());
                      return newPost;
                  });
            }))
            .flatMap(p -> users.findById(1L).map(u -> {
                    p.setUsers(u);
                    return p;
            }))
            .flatMap(p -> 
                // Insert new "Post" data
                posts.save(p).map(pst -> "OK: " + pst.getId()));
    }
}

Transaction

When the program cases some exceptions on inserting and updating data, I want to rollback.
To do this, I can add a "@Transactional" annotation.

...
    // Add this
    @Transactional()
    public Mono<String> updateCategory(String categoryName) {
        return categories.findByName(categoryName)
            .flatMap(c -> {
                c.setLastUpdateDate(LocalDateTime.now());
                return categories.update(c);
            })
            .switchIfEmpty(Mono.defer(() -> {
                Category c = new Category();
                c.setName(categoryName);
                c.setLastUpdateDate(LocalDateTime.now());
                return categories.save(c);
            }))
            .flatMap(c -> {
                // Intentionally raise an exception to validate the rollback
                c.setName(null);
                c.setLastUpdateDate(LocalDateTime.now());
                return categories.update(c);
            })
            .map(c -> "OK");
            // Don't add this line
            // .onErrorResume(e -> Mono.just(e.getMessage()));
    }
}

In this code, I must not add "onErrorResume".
If I add it, rollback is not performed because exception occurrence cannot be detected.

• Micronaut R2DBC
• 3.5 Transactions - Micronaut Data