[Micronaut] Accessing SQL Server 1

#java #micronaut

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 librariesimplementation("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

micronaut:application:name:micronaut-samplerouter:static-resources:default:enabled:truepaths:classpath:staticdatasources:default:db-type:mssqldriver-class-name:com.microsoft.sqlserver.jdbc.SQLServerDriverurl:jdbc:sqlserver://localhost:1433;encrypt=false;name:masterusername:sapassword:PASSWORDr2dbc:datasources:default:dialect:SQL_SERVERdb-type:mssqlurl:r2dbc:mssql://localhost:1433/masterusername:sapassword: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

...datasources:default:db-type:mssqldriver-class-name:com.microsoft.sqlserver.jdbc.SQLServerDriverurl:jdbc:sqlserver://localhost:1433;encrypt=false;name:master# ${Key:default value}username:${SQL_NAME:sample}# Even if the default value isn't set, ":" is requiredpassword:${SQL_PASSWORD:}r2dbc:datasources:default:dialect:SQL_SERVERdb-type:mssqlurl:r2dbc:mssql://localhost:1433/masterusername:${SQL_NAME:sample}password:${SQL_PASSWORD:}

Executing SQL queries

Defining model and repository classes

Users.java

packagemicronaut.sample.users;importjava.time.LocalDateTime;importio.micronaut.data.annotation.GeneratedValue;importio.micronaut.data.annotation.Id;importio.micronaut.data.annotation.MappedEntity;importio.micronaut.serde.annotation.Serdeable;@Serdeable@MappedEntitypublicclassUsers{@GeneratedValue@IdprivateLongid;privatefinalStringname;privateLocalDateTimelastUpdateDate;publicUsers(Stringname){this.name=name;}publicStringgetName(){returnname;}publicLonggetId(){returnid;}publicvoidsetId(Longid){this.id=id;}publicLocalDateTimegetLastUpdateDate(){returnlastUpdateDate;}publicvoidsetLastUpdateDate(LocalDateTimelastUpdateDate){this.lastUpdateDate=lastUpdateDate;}}

UserRepository.java

packagemicronaut.sample.users;importio.micronaut.data.model.query.builder.sql.Dialect;importio.micronaut.data.r2dbc.annotation.R2dbcRepository;importio.micronaut.data.repository.reactive.ReactiveStreamsCrudRepository;importjakarta.validation.constraints.NotNull;importreactor.core.publisher.Flux;importreactor.core.publisher.Mono;@R2dbcRepository(dialect=Dialect.SQL_SERVER)publicinterfaceUserRepositoryextendsReactiveStreamsCrudRepository<Users,Long>{@OverrideMono<Users>findById(@NotNullLongaLong);@OverrideFlux<Users>findAll();}

UserController.java

packagemicronaut.sample.users;importio.micronaut.http.annotation.Controller;importio.micronaut.http.annotation.Get;importio.micronaut.http.annotation.QueryValue;importreactor.core.publisher.Flux;@Controller("/users")publicclassUserController{privatefinalUserRepositoryusers;publicUserController(UserRepositoryusers){this.users=users;}@Get("/all")publicFlux<Users>getAllUsers(){returnusers.findAll();}}

Raw SQL

I can use Raw SQL by "@Query".

UserRepository.java

packagemicronaut.sample.users;importio.micronaut.data.annotation.Query;importio.micronaut.data.model.query.builder.sql.Dialect;importio.micronaut.data.r2dbc.annotation.R2dbcRepository;importio.micronaut.data.repository.reactive.ReactiveStreamsCrudRepository;importjakarta.validation.constraints.NotNull;importreactor.core.publisher.Flux;importreactor.core.publisher.Mono;@R2dbcRepository(dialect=Dialect.SQL_SERVER)publicinterfaceUserRepositoryextendsReactiveStreamsCrudRepository<Users,Long>{...@Query("SELECT * FROM users WHERE LOWER(name) LIKE :userName")Flux<Users>findUsersByName(@NotNullStringuserName);}

UserController.java

...@Get("/name")publicFlux<Users>getUsersByName(@QueryValue("userName")StringuserName){// Because the template can't use "%", I add "%" as argument value.StringformattedName=String.format("%%%s%%",userName).toLowerCase();System.out.println(formattedName);returnusers.findUsersByName(formattedName);}}