[Micronaut] Accessing SQL Server 3
Tags: java, micronaut
Published: 2024-01-21 17:05:37

## Intro
I will try inserting and update in this time.

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

## Inserting and updating data
In simple cases, I can override method like below.

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

#### 
```java
...
    // 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](https://micronaut-projects.github.io/micronaut-r2dbc/1.0.x/guide/)
* [3.5 Transactions - Micronaut Data](https://micronaut-projects.github.io/micronaut-data/latest/guide/#transactions)