Home | 简体中文 | 繁体中文 | 杂文 | Github | 知乎专栏 | Facebook | Linkedin | Youtube | 打赏(Donations) | About
43.8. R2DBC
上一页 第 43 章 WebFlux framework 下一页
知乎专栏

43.8. R2DBC

43.8.1. MySQL

43.8.1.1. 

			
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xmlns="http://maven.apache.org/POM/4.0.0"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>cn.netkiller</groupId>
    <artifactId>cordyceps</artifactId>
    <version>1.0.0</version>
    <repositories>
        <repository>
            <id>alimaven</id>
            <name>aliyun maven</name>
            <url>https://maven.aliyun.com/nexus/content/groups/public/</url>
            <releases>
                <enabled>true</enabled>
            </releases>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
        </repository>
    </repositories>
    <pluginRepositories>
        <pluginRepository>
            <id>aliyun-repos</id>
            <url>https://maven.aliyun.com/repository/public</url>
            <releases>
                <enabled>true</enabled>
            </releases>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
        </pluginRepository>
    </pluginRepositories>
    <properties>
        <java.version>25</java.version>
        <maven.compiler.source>${java.version}</maven.compiler.source>
        <maven.compiler.target>${java.version}</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <spring-statemachine.version>4.0.1</spring-statemachine.version>
        <docker.registry>http://localhost/registry</docker.registry>
    </properties>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>4.0.3</version>
        <relativePath/>
    </parent>
    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-webflux</artifactId>
        </dependency>
  
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-r2dbc</artifactId>
        </dependency>
        <!-- a R2DBC driver -->
        <!-- https://mvnrepository.com/artifact/io.asyncer/r2dbc-mysql -->
        <dependency>
            <groupId>io.asyncer</groupId>
            <artifactId>r2dbc-mysql</artifactId>
            <version>1.4.1</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.42</version>
            <scope>provided</scope>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.google.code.gson/gson -->
        <dependency>
            <groupId>com.google.code.gson</groupId>
            <artifactId>gson</artifactId>
            <version>2.13.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.aliyun.oss/aliyun-sdk-oss -->
        <dependency>
            <groupId>com.aliyun.oss</groupId>
            <artifactId>aliyun-sdk-oss</artifactId>
            <version>3.18.3</version>
        </dependency>

        <!-- 阿里云短信服务依赖 -->
        <dependency>
            <groupId>com.aliyun</groupId>
            <artifactId>aliyun-java-sdk-dysmsapi</artifactId>
            <version>1.1.0</version>
        </dependency>

        <dependency>
            <groupId>com.auth0</groupId>
            <artifactId>java-jwt</artifactId>
            <version>4.5.0</version>
        </dependency>

        <dependency>
            <groupId>io.netty</groupId>
            <artifactId>netty-resolver-dns-native-macos</artifactId>
            <!--            <version>4.1.76.Final</version>-->
            <classifier>osx-aarch_64</classifier>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <mainClass>cn.netkiller.Application</mainClass>
                </configuration>
            </plugin>
            <plugin>
                <artifactId>maven-surefire-plugin</artifactId>
                <configuration>
                    <skip>true</skip>
                </configuration>
            </plugin>


            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <executions>
                    <execution>
                        <id>default-compile</id>
                        <phase>none</phase>
                    </execution>
                    <execution>
                        <id>default-testCompile</id>
                        <phase>none</phase>
                    </execution>
                    <execution>
                        <id>compile</id>
                        <phase>compile</phase>
                        <goals>
                            <goal>compile</goal>
                        </goals>
                    </execution>
                    <execution>
                        <id>testCompile</id>
                        <phase>test-compile</phase>
                        <goals>
                            <goal>testCompile</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <release>${java.version}</release>
                    <annotationProcessorPaths>
                        <path>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                            <version>1.18.42</version>
                        </path>
                    </annotationProcessorPaths>
                </configuration>
            </plugin>

            <plugin>
                <groupId>com.spotify</groupId>
                <artifactId>docker-maven-plugin</artifactId>
                <version>1.2.2</version>
                <configuration>
                    <imageName>netkiller/${project.artifactId}</imageName>
                    <baseImage>openjdk:25</baseImage>
                    <maintainer>netkiller@msn.com</maintainer>
                    <volumes>/tmp</volumes>
                    <workdir>/srv</workdir>
                    <exposes>8080</exposes>
                    <env>
                        <JAVA_OPTS>-server -Xms128m -Xmx2048m</JAVA_OPTS>
                    </env>
                    <entryPoint>["sh", "-c", "java ${JAVA_OPTS} -jar /srv/${project.build.finalName}.jar
                        ${SPRING_OPTS}"]
                    </entryPoint>
                    <resources>
                        <resource>
                            <targetPath>/srv</targetPath>
                            <directory>${project.build.directory}</directory>
                            <include>${project.build.finalName}.jar</include>
                        </resource>
                    </resources>
                    <image>netkiller/${project.artifactId}</image>
                    <newName>netkiller/${project.artifactId}:${project.version}</newName>
                    <!-- <serverId>docker-hub</serverId> -->
                    <registryUrl>http://${docker.registry}/v2/</registryUrl>
                    <imageTags>
                        <imageTag>log4j2</imageTag>
                        <!-- <imageTag>undertow</imageTag> <imageTag>tomcat</imageTag> <imageTag>${project.version}</imageTag> <imageTag>latest</imageTag> -->
                    </imageTags>
                </configuration>
            </plugin>

        </plugins>
    </build>
</project>
数据库配置
				
spring.r2dbc.url=r2dbc:mysql://localhost:3306/beta?useUnicode=true&characterEncoding=UTF-8
spring.r2dbc.username=root
spring.r2dbc.password=chen
启动文件
				
package cn.netkiller;

import jakarta.annotation.PostConstruct;
import jakarta.annotation.PreDestroy;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.r2dbc.repository.config.EnableR2dbcRepositories;

@SpringBootApplication
@EnableR2dbcRepositories
@EnableAutoConfiguration
public class Application {
    private static final Logger logger = LoggerFactory.getLogger(Application.class);

    @Value("${spring.application.name}")
    public String name;

    static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

    @PostConstruct
    public void init() {
        logger.info(String.format("==================== %s 系统启动 ====================", name));
    }

    @PreDestroy
    public void destroy() {
        logger.info(String.format("==================== %s 系统销毁 ====================", name));
    }
}
仓库
				
package cn.netkiller.domain;

import lombok.Data;
import org.springframework.data.annotation.Id;
import org.springframework.data.relational.core.mapping.Table;

import java.io.Serial;
import java.io.Serializable;
import java.time.LocalDateTime;

@Table()
@Data
public class Device implements Serializable {
    @Serial
    public static final long serialVersionUID = 1L;

    @Id
    private Integer id;
    private String androidId;
    private String model;
    private String fireware;
    private String version;
    private String brand;
    private String cpu;
    private String device;
    private String display;
    private String fingerprint;
    private String hardware;
    private String manufacturer;
    private String product;
    //    private LocalDateTime expired;
    private String status;
    private String description;
    private LocalDateTime ctime;
    private LocalDateTime mtime;

}				
				
				
				
package cn.netkiller.repository;

import cn.netkiller.domain.Device;
import org.springframework.data.repository.reactive.ReactiveCrudRepository;
import org.springframework.stereotype.Repository;
import reactor.core.publisher.Mono;

@Repository
public interface DeviceRepository extends ReactiveCrudRepository<Device, Integer> {
    Mono<Device> findByAndroidId(String androidId);

}
控制器中调用
			
    @GetMapping("count")
    public JsonResponse count() {
        long number = deviceRepository.count().block();
        return JsonResponse.builder().status(true).code(String.valueOf(Code.SUCCESS)).reason("成功").data(number).build();
    }


    @GetMapping("query/{androidId}")
    public JsonResponse query(@PathVariable("androidId") String androidId) {
        Mono<Device> mono = deviceRepository.findByAndroidId(androidId);
        return JsonResponse.data(mono.block());
    }

43.8.2. R2dbcEntityTemplate

通过注解跳过 Token 验证

		
package cn.netkiller.controller;

import cn.netkiller.annotation.TokenPass;
import cn.netkiller.component.JwtTokenComponent;
import cn.netkiller.domain.Device;
import lombok.extern.slf4j.Slf4j;
import org.reactivestreams.Publisher;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.r2dbc.core.R2dbcEntityTemplate;
import org.springframework.data.relational.core.query.Criteria;
import org.springframework.data.relational.core.query.Query;
import org.springframework.web.bind.annotation.*;
import reactor.core.publisher.Mono;

import java.util.Map;

@RestController
@Slf4j
@TokenPass
@CrossOrigin(origins = "*", maxAge = 3600)
@RequestMapping("/")
public class HomeController {

    private final R2dbcEntityTemplate template;

    @Autowired
    private JwtTokenComponent jwtTokenComponent;

    public HomeController(R2dbcEntityTemplate template) {
        this.template = template;
    }

    @GetMapping()
    public String index() {
        return "OK";
    }

    @GetMapping("/ping")
    @TokenPass
    public Mono<String> ping() {
        return Mono.just("pong");
    }

    @PostMapping("test")
    public Mono<Device> test(@RequestParam("id") String id) {
        return template.selectOne(
                Query.query(Criteria.where("id").is(id)),
                Device.class
        );
    }

}

43.8.3. MySQL Point 数据类型

数据库结构

		
CREATE TABLE `geo` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `android_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '设备ID',
  `location` point NOT NULL COMMENT '定位点，longitude 经度，latitude 纬度',
  `ctime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `province` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '省份',
  `city` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '城市',
  `district` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '县',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  SPATIAL KEY `location` (`location`) COMMENT '定位索引'
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb3 COMMENT='GEO(Geostationary Earth Orbit)'

数据类

		
package cn.netkiller.cordyceps.record;

public record GeoMySQL(
        Integer id,
        String androidId,
        Double longitude,
        Double latitude) {
}

仓库

		
package cn.netkiller.cordyceps.repository;

import cn.netkiller.cordyceps.domain.Geo;
import cn.netkiller.cordyceps.record.GeoMySQL;
import org.springframework.data.r2dbc.repository.Modifying;
import org.springframework.data.r2dbc.repository.Query;
import org.springframework.data.repository.reactive.ReactiveCrudRepository;
import org.springframework.stereotype.Repository;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

@Repository
public interface GeoRepository extends ReactiveCrudRepository<Geo, Integer> {
    //    Flux<Geo> findByAndroidId(String androidId);
    @Modifying
    @Query(value = "INSERT INTO Geo(android_id, location) VALUES (:androidId, POINT(:lng, :lat))")
    Mono<Integer> saveGeo(String androidId, double lng, double lat);

    @Query(value = "SELECT id, android_id, " +
            "ST_X(location) AS longitude, " +
            "ST_Y(location) AS latitude " +
            "FROM Geo WHERE android_id = :androidId")
    Flux<GeoMySQL> findByAndroidId(String androidId);
}

控制器

		
    @GetMapping("location/{androidId}")
    public JsonResponse<Object> location(@PathVariable("androidId") String androidId) {

        Flux<GeoMySQL> flux = geoRepository.findByAndroidId(androidId);
        return JsonResponse.builder().status(true).code(String.valueOf(Code.SUCCESS)).reason("激活").data(flux.collectList().block()).build();
        
    }

    @PostMapping("location/{androidId}")
    public JsonResponse insert(@PathVariable("androidId") String androidId, @RequestBody GeoMySQL geo) {
        log.info(geo.toString());

        int count = geoRepository.saveGeo(androidId, geo.longitude(), geo.latitude()).block();

        return JsonResponse.builder().status(true).code(String.valueOf(Code.SUCCESS)).reason("提交成功").data(count).build();

    }

Rest 测试

		
### 获取定位信息
GET {{host}}/device/location/test
Authorization: Bearer {{token}}

### 提交定位信息
POST {{host}}/device/location/test
Authorization: Bearer {{token}}
Content-Type: application/json

{
  "longitude": 133.3333,
  "latitude": 144.4444
}
上一页 上一级 下一页
43.7. Webflux 安全 起始页 43.9. Webflux Redis