Vert.x操作PostgreSql数据库

Reactive PostgreSQL客户端是PostgreSQL的客户端，具有直接的API，专注于可伸缩性和低开销。

客户端是反应性的并且是非阻塞的，从而允许通过单个线程处理许多数据库连接。

1、引入依赖

<dependency>
  <groupId>io.vertx</groupId>
  <artifactId>vertx-pg-client</artifactId>
  <version>4.0.3</version>
</dependency>

2、编写PgSqlVerticle程序

package vertx;

import io.vertx.core.AbstractVerticle;
import io.vertx.core.Promise;
import io.vertx.core.json.JsonObject;
import io.vertx.ext.web.Router;
import io.vertx.pgclient.PgConnectOptions;
import io.vertx.pgclient.PgPool;
import io.vertx.sqlclient.PoolOptions;
import io.vertx.sqlclient.SqlConnection;
import io.vertx.sqlclient.Tuple;

import java.util.ArrayList;

public class PgSqlVerticle  extends AbstractVerticle {

  //声明Router
  Router router;

  //第一步 配置连接参数
  PgConnectOptions connectOptions = new PgConnectOptions()
    .setPort(54321)
    .setHost("192.168.1.59")
    .setDatabase("postgres")
    .setUser("postgres")
    .setPassword("password");

  //第二步 配置连接池 Pool options
  PoolOptions poolOptions = new PoolOptions()
    .setMaxSize(5);

  //第三步 Create the client pool
  PgPool client;

  @Override
  public void start(Promise<Void> startPromise) throws Exception {

    client = PgPool.pool(vertx, connectOptions, poolOptions);

    //初始化Router
    router = Router.router(vertx);

    //配置Router解析url
    router.route("/").handler(
      req -> {
        req.response()
          .putHeader("content-type", "text/plain")
          .end("Hello from Vert.x!");
      }
    );

    //配置Router解析url
    router.route("/test/list").handler(
      req -> {
        var page = (Integer.valueOf(req.request().getParam("page")) - 1) * 10;
        var size = Integer.valueOf(req.request().getParam("size"));
        //Get a connection from the pool
        client.getConnection(ar1 -> {
          if(ar1.succeeded()){
            System.out.println("Connected");

            //Obtain our connection
            SqlConnection conn = ar1.result();

            //All operations execute on the same connection
            //注意：postgreSql中占位符用的是$符号，与Mysql中不同
            conn
              .preparedQuery("select id, name, age, info from person limit $2 offset $1")
              .execute(Tuple.of(page, size), ar2 -> {
                //Release the connection to the pool
                conn.close();

                if(ar2.succeeded()){
                  var list = new ArrayList<JsonObject>();
                  ar2.result().forEach(item -> {
                    var json = new JsonObject();
                    json.put("id", item.getValue("id"));
                    json.put("name", item.getValue("name"));
                    json.put("age", item.getValue("age"));
                    json.put("info", item.getValue("info"));
                    list.add(json);
                  });
                  req.response()
                    .putHeader("content-type", "application/json")
                    .end(list.toString());
                }else{
                  req.response()
                    .putHeader("content-type", "text/plain")
                    .end(ar2.cause().toString());
                }
              });
          }else{
            System.out.println("Could not connect:" + ar1.cause().getMessage());
          }
        });
      }
    );

    //将Router与vertx HttpServer 绑定
    vertx.createHttpServer().requestHandler(router).listen(8888, http -> {
      if (http.succeeded()) {
        startPromise.complete();
        System.out.println("HTTP server started on port 8888");
      } else {
        startPromise.fail(http.cause());
      }
    });
  }
}

3、启动测试