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)
}
}
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
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: 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
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
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
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
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
...
@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);
}
}
Top comments (0)