Micronaut 程序访问 SQL Server

连接 SQL Server

为了连接 SQL Server，将添加一些库。

构建.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")

    // 添加库
    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)
    }
}

添加连接字符串

我可以将连接字符串添加到 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

“r2dbc”的属性用于 R2DBC，“datasources”用于 HikariCP。
添加属性后，我的应用程序将在启动时自动连接到 SQL Server。

使用环境变量

因为我不想直接在application.yml上写入连接SQL Server的用户名和密码，所以我想从环境变量中获取它们。

.bashrc

...
export SQL_NAME="sa"
export SQL_PASSWORD="PASSWORD"

应用程序.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:}

执行 SQL 查询

定义模型和存储库类

用户.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;
    }
}

用户存储库.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();
}

用户控制器.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();
    }
}

原始SQL

我可以通过“@Query”使用原始 SQL。

用户存储库.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);
}

用户控制器.java

    @Get("/name")
    public Flux<Users> getUsersByName(@QueryValue("userName") String userName) {
        // 因为模板不能使用“%”，所以我添加了“%”作为参数值
        String formattedName = String.format("%%%s%%", userName).toLowerCase();
        System.out.println(formattedName);
        return users.findUsersByName(formattedName);
    }
}