[Micronaut] Accessing SQL Server 1
Tags: java, micronaut
Published: 2024-01-14 02:43:05

## Intro
I will try accessing SQL Server from my Micronaut application.

* [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)

## Connecting SQL Server
To connect SQL Server, I will add some libraries.

#### build.gradle.kts
```java
plugins {
    id("com.github.johnrengelman.shadow") version "8.1.1"
    id("io.micronaut.application") version "4.2.1"
    id("io.micronaut.aot") version "4.2.1"
}
version = "0.1"
group = "micronaut.sample"

repositories {
    mavenCentral()
}
dependencies {
    annotationProcessor("io.micronaut.data:micronaut-data-processor")
    annotationProcessor("io.micronaut:micronaut-http-validation")
    annotationProcessor("io.micronaut.serde:micronaut-serde-processor")
    implementation("io.micronaut.serde:micronaut-serde-jackson")
    implementation("io.micronaut.views:micronaut-views-thymeleaf")
    compileOnly("io.micronaut:micronaut-http-client")
    runtimeOnly("org.yaml:snakeyaml")
    runtimeOnly("ch.qos.logback:logback-classic")
    testImplementation("io.micronaut:micronaut-http-client")

    // add libraries
    implementation("io.micronaut.data:micronaut-data-r2dbc")
    implementation("io.micronaut.reactor:micronaut-reactor")
    implementation("io.micronaut.reactor:micronaut-reactor-http-client")
    implementation("io.micronaut.sql:micronaut-jdbc-hikari")
    runtimeOnly("io.r2dbc:r2dbc-mssql")
    runtimeOnly("com.microsoft.sqlserver:mssql-jdbc")
}
application {
    mainClass.set("micronaut.sample.Application")
}
java {
    sourceCompatibility = JavaVersion.toVersion("17")
    targetCompatibility = JavaVersion.toVersion("17")
}
graalvmNative.toolchainDetection.set(false)
micronaut {
    runtime("netty")
    testRuntime("junit5")
    processing {
        incremental(true)
        annotations("micronaut.sample.*")
    }
    aot {
        optimizeServiceLoading.set(false)
        convertYamlToJava.set(false)
        precomputeOperations.set(true)
        cacheEnvironment.set(true)
        optimizeClassLoading.set(true)
        deduceEnvironment.set(true)
        optimizeNetty.set(true)
    }
}
```

### Adding connecting strings
I can add connecting strings into application.yml.

#### application.yml
```yml
micronaut:
  application:
    name: micronaut-sample
  router:
    static-resources:
      default:
        enabled: true
        paths: classpath:static
datasources:
  default:
    db-type: mssql
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    url: jdbc:sqlserver://localhost:1433;encrypt=false;
    name: master
    username: sa
    password: PASSWORD
r2dbc:
  datasources:
    default:
      dialect: SQL_SERVER
      db-type: mssql
      url: r2dbc:mssql://localhost:1433/master
      username: sa
      password: PASSWORD
```

The properties of "r2dbc" are for R2DBC and "datasources" are for HikariCP.
After adding the properties, my application will automatically connect to SQL Server on startup.

### Using environment variables
Because I don't want to write username and password to connect SQL Server on application.yml directly, I want to get them from the environment variables.

#### .bashrc
```
...
export SQL_NAME="sa"
export SQL_PASSWORD="PASSWORD"
```

#### application.yml
```yml
...
datasources:
  default:
    db-type: mssql
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    url: jdbc:sqlserver://localhost:1433;encrypt=false;
    name: master
    
    # ${Key:default value}
    username: ${SQL_NAME:sample}
    # Even if the default value isn't set, ":" is required
    password: ${SQL_PASSWORD:}

r2dbc:
  datasources:
    default:
      dialect: SQL_SERVER
      db-type: mssql
      url: r2dbc:mssql://localhost:1433/master
      username: ${SQL_NAME:sample}
      password: ${SQL_PASSWORD:}
```

## Executing SQL queries
### Defining model and repository classes
#### Users.java
```java
package micronaut.sample.users;

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.serde.annotation.Serdeable;

@Serdeable
@MappedEntity
public class Users {
    @GeneratedValue
    @Id
    private Long id;
    private final String name;
    private LocalDateTime lastUpdateDate;

    public Users(String name) {
        this.name = name;
    }
    public String getName() {
        return name;
    }
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public LocalDateTime getLastUpdateDate() {
        return lastUpdateDate;
    }
    public void setLastUpdateDate(LocalDateTime lastUpdateDate) {
        this.lastUpdateDate = lastUpdateDate;
    }
}
```

#### UserRepository.java
```java
package micronaut.sample.users;

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;
import reactor.core.publisher.Mono;

@R2dbcRepository(dialect = Dialect.SQL_SERVER) 
public interface UserRepository extends ReactiveStreamsCrudRepository<Users, Long> {
    @Override
    Mono<Users> findById(@NotNull Long aLong); 

    @Override
    Flux<Users> findAll();
}
```

#### UserController.java
```java
package micronaut.sample.users;

import io.micronaut.http.annotation.Controller;
import io.micronaut.http.annotation.Get;
import io.micronaut.http.annotation.QueryValue;
import reactor.core.publisher.Flux;

@Controller("/users")
public class UserController {
    private final UserRepository users;
    public UserController(UserRepository users) {
        this.users = users;
    }
    @Get("/all")
    public Flux<Users> getAllUsers() {
        return users.findAll();
    }
}
```

### Raw SQL
I can use Raw SQL by "@Query".

#### UserRepository.java
```java
package micronaut.sample.users;

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 jakarta.validation.constraints.NotNull;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

@R2dbcRepository(dialect = Dialect.SQL_SERVER) 
public interface UserRepository extends ReactiveStreamsCrudRepository<Users, Long> {
...
    @Query("SELECT * FROM users WHERE LOWER(name) LIKE :userName")
    Flux<Users> findUsersByName(@NotNull String userName);
}
```

#### UserController.java
```java
...
    @Get("/name")
    public Flux<Users> getUsersByName(@QueryValue("userName") String userName) {
        // Because the template can't use "%", I add "%" as argument value.
        String formattedName = String.format("%%%s%%", userName).toLowerCase();
        System.out.println(formattedName);
        return users.findUsersByName(formattedName);
    }
}
```

## Resources
* [Access a database with Micronaut Data R2DBC](https://guides.micronaut.io/latest/micronaut-data-r2dbc-repository-gradle-kotlin.html)
* [Micronaut R2DBC](https://micronaut-projects.github.io/micronaut-r2dbc/1.0.0.M2/guide/index.html)
* [Micronaut Data](https://micronaut-projects.github.io/micronaut-data/latest/guide/)
* [Drivers - R2DBC](https://r2dbc.io/drivers/)
* [4.2 Externalized Configuration with PropertySources - Micronaut Framework](https://docs.micronaut.io/latest/guide/index.html#propertySource)