Vert.x MySQLClient體驗

文章背景

• 消除回撥地獄
• 先查詢學生(包含學校的Id)，然後使用學校的ID，去學校表查詢學校的詳細資訊

1. DbVerticle.java

import com.xiaoniu.db.entity.User;
import com.xiaoniu.db.utils.Utils;
import io.vertx.core.AbstractVerticle;
import io.vertx.core.Future;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import io.vertx.ext.asyncsql.MySQLClient;
import io.vertx.ext.sql.SQLClient;
import io.vertx.ext.sql.SQLConnection;
import io.vertx.ext.sql.SQLRowStream;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by sweet on 2017/9/13.
 */
public class DbVerticle extends AbstractVerticle {

  private String sql = "SELECT id, name FROM t_school WHERE id IN (";

  private SQLClient sqlClient;

  public static void main(String[] args) {
    Runner.runExample(DbVerticle.class);
  }

  @Override
  public void start(Future<Void> startFuture) throws Exception {
    JsonObject c = new JsonObject();
    c.put("username", "root").put("password", "main")
      .put("host","127.0.0.1").put("database", "sqldemo2");

    sqlClient = MySQLClient.createShared(vertx, c);

    Future<SQLConnection> sqlConnectionFuture = Future.future();
    sqlClient.getConnection(sqlConnectionFuture);

    sqlConnectionFuture.setHandler(connection -> {
      if (connection.succeeded()) {
        SQLConnection conn = connection.result();

        Future<SQLRowStream> streamFuture1 = Future.future();
        Future<List<User>> streamFuture2 = Future.future();
        Future<List<JsonArray>> future = Future.future();

        conn.queryStream("SELECT id, name, t_school_id FROM t_user", streamFuture1);

        streamFuture1.compose(sqlRowStream -> {
          List<User> users = new ArrayList<>();

          sqlRowStream.handler(jsonArray -> {
            System.out.println(jsonArray);
            users.add(new User(jsonArray));
          });

          System.out.println("user size: " + users.size());
          streamFuture2.complete(users);

        }, streamFuture2).compose(users -> {
          List<JsonArray> list = new ArrayList<>();

          JsonArray collect = users.parallelStream()
                  .map(User::getSchoolId)
                  .collect(JsonArray::new, JsonArray::add, JsonArray::addAll);

          String sql2 = sql += Utils.placeholder("?", collect.size(), ", ") + ")";

          conn.queryStreamWithParams(sql2, collect, schoolResult -> {
            if (schoolResult.failed()){
              schoolResult.cause().printStackTrace();
              return;
            }
            schoolResult.result().handler(jsonArray1 -> {
              if (jsonArray1 != null && jsonArray1.size() > 0)
                list.add(jsonArray1);
            });

            future.complete(list);
          });
        }, future);

        future.setHandler(list -> {
          conn.close(); // 關閉流
          if (list.failed()) {
            list.cause().printStackTrace();
            return;
          }
          System.out.println("-----");
          list.result().forEach(System.out::println);
        });

      } else {
        connection.cause().printStackTrace();
        System.err.println(connection.cause().getMessage());
      }
    });
    startFuture.complete();
  }
}
複製程式碼

2. Utils.java

public class Utils {

  public static void main(String[] args) {
    System.out.println(placeholder("?", 5, ","));
  }
  
  private Utils(){}

  /**
   * 生成佔位符
   * (?, 5, ",")
   * ?,?,?,?,?
   * @param symbol ？
   * @param count  出現個數
   * @param delimiter 分割符
   * @return
   */
  public static String placeholder(String symbol, Integer count, String delimiter) {
    String[] s = new String[count];
    for (int i = 0; i < count; i++) {
      s[i] = symbol;
    }
    return String.join(delimiter, s);
  }
}
複製程式碼

3. User.java

public class User {

  private String id;
  private String name;
  private String schoolId;

  public User() {
  }

  public User(String id, String name) {
    this.id = id;
    this.name = name;
  }

  public User(JsonArray jsonArray) {
    this.id = jsonArray.getString(0);
    this.name = jsonArray.getString(1);
    this.schoolId = jsonArray.getString(2);
  }

  @Override
  public String toString() {
    return "User{" +
            "id='" + id + '\'' +
            ", name='" + name + '\'' +
            ", schoolId='" + schoolId + '\'' +
            '}';
  }
// ....
}
複製程式碼

4. pom.xml

    <dependencies>
        <dependency>
            <groupId>io.vertx</groupId>
            <artifactId>vertx-core</artifactId>
            <version>3.4.2</version>
        </dependency>

        <dependency>
            <groupId>io.vertx</groupId>
            <artifactId>vertx-mysql-postgresql-client</artifactId>
            <version>3.4.2</version>
        </dependency>

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.25</version>
        </dependency>

        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>slf4j-impl</artifactId>
            <version>RELEASE</version>
        </dependency>
    </dependencies>
複製程式碼

5. Runner.java

這個檔案是官方提供的 方便在 IDE中執行的類，https://github.com/vert-x3/vertx-examples/blob/master/core-examples/src/main/java/io/vertx/example/util/Runner.java。