DEV Community

Masui Masanori
Masui Masanori

Posted on • Updated on

[Micronaut] Accessing SQL Server 1

Intro

I will try accessing SQL Server from my Micronaut application.

Connecting SQL Server

To connect SQL Server, I will add some libraries.

build.gradle.kts

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

Adding connecting strings

I can add connecting strings into application.yml.

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

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

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

Executing SQL queries

Defining model and repository classes

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

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

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

Raw SQL

I can use Raw SQL by "@Query".

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

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

Resources

Top comments (0)