Vert.x Future 解決複雜查詢

sweetmain發表於2017-12-13

封裝了一下資料庫操作，可以鏈式操作

  • BaseVerticle
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 java.util.List;

/**
 * Created by sweet on 2017/11/16.
 * ---------------------------
 */
public abstract class BaseVerticle extends AbstractVerticle {

  protected SQLClient client;

  @Override
  public void start() throws Exception {
    JsonObject mysqlConfig = new JsonObject().put("host", "127.0.0.1")
            .put("port", 3306).put("maxPollSize", 10)
            .put("username", "root").put("password", "xxx")
            .put("database", "vertxdemo");

    client = MySQLClient.createShared(vertx, mysqlConfig);
  }

  protected Future<SQLConnection> getConnection() {
    Future<SQLConnection> future = Future.future();
    client.getConnection(future);
    return future;
  }
  protected Future<Void> closeConnection(SQLConnection connection) {
    Future<Void> future = Future.future();
    connection.close(future);
    return future;
  }

  // 開啟事務
  protected Future<SQLConnection> beginTx() {
    Future<SQLConnection> future = Future.future();
    getConnection().setHandler(r -> {
      if (r.failed()){
        future.fail(r.cause());
        return;
      } else {
        r.result().setAutoCommit(false, tx -> {
          if (tx.failed()) {
            future.fail(tx.cause());
          } else {
            future.complete(r.result());
          }
        });
      }
    });
    return future;
  }
  // 回滾
  protected Future<Void> rollback(SQLConnection connection) {
    Future<Void> future = Future.future();
    connection.rollback(future);
    return future;
  }

  protected Future<List<JsonObject>> queryWithParams(SQLConnection connection, String sql, JsonArray params) {
    Future<List<JsonObject>> future = Future.future();
    connection.queryWithParams(sql, params, r -> {
      connection.close(); // 如果要多查詢條件鏈式操作， 需要去掉 這行程式碼，自己手動獲取connection, 最後手動關閉 connection
      if (r.failed()) {
        future.fail(r.cause());
        return;
      }
      future.complete(r.result().getRows());
    });
    return future;
  }
  protected Future<List<JsonObject>> query(SQLConnection connection, String sql) {
    Future<List<JsonObject>> future = Future.future();
    connection.query(sql, r -> {
      if (r.failed()) {
        future.fail(r.cause());
        return;
      }
      future.complete(r.result().getRows());
    });
    return future;
  }

  protected Future<Boolean> insertWithParams(SQLConnection connection, String sql, JsonArray params) {
    Future<Boolean> future =  Future.future();
    connection.updateWithParams(sql, params, r -> {
      if (r.failed()){
        future.fail(r.cause());
        return;
      }
      future.complete(r.result().getUpdated() == 1 ? true : false);
    });
    return future;
  }
  protected Future<Boolean> insert(SQLConnection connection, String sql) {
    Future<Boolean> future =  Future.future();
    connection.update(sql, r -> {
      if (r.failed()){
        future.fail(r.cause());
        return;
      }
      future.complete(r.result().getUpdated() == 1 ? true : false);
    });
    return future;
  }

  protected Future<Boolean> updateWithParams(SQLConnection connection, String sql, JsonArray params) {
    return insertWithParams(connection, sql, params);
  }
  protected Future<Boolean> update(SQLConnection connection, String sql) {
    return insert(connection, sql);
  }

  protected Future<Boolean> deleteWithParams(SQLConnection connection, String sql, JsonArray params) {
    return insertWithParams(connection, sql, params);
  }
  protected Future<Boolean> delete(SQLConnection connection, String sql) {
    return insert(connection, sql);
  }

}
複製程式碼
  • RestVerticle
import com.xiaoniu.db.BaseVerticle;
import io.vertx.core.Vertx;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import io.vertx.ext.sql.SQLConnection;
import io.vertx.ext.web.Router;
import io.vertx.ext.web.RoutingContext;
import io.vertx.ext.web.handler.BodyHandler;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

/**
 * Created by sweet on 2017/11/16.
 * ---------------------------
 */
public class RestVerticle extends BaseVerticle {

  public static void main(String[] args) {
    Vertx.vertx().deployVerticle(RestVerticle.class.getName());
  }

  private static final Logger logger = LogManager.getLogger(RestVerticle.class);

  @Override
  public void start() throws Exception {
    super.start();
    Router router = Router.router(vertx);

    router.route().handler(BodyHandler.create());
    router.get("/user/:userId").handler(this::getUserById);
    router.put("/user/:userId").handler(this::updateUserById);
    router.post("/user").handler(this::createUser);
    router.get("/users").handler(this::getUserList);
    router.delete("/user/:userId").handler(this::deleteUserById);

    router.get("/complexQuery").handler(this::complexQuery);

    vertx.createHttpServer().requestHandler(router::accept).listen(8080, server -> {
      if (server.failed()) {
        logger.error("HttpServer 啟動失敗, " + server.cause().getMessage());
        return;
      }
      logger.info("HttpServer 啟動成功, 埠 8080");
    });
  }

  private void complexQuery(RoutingContext routingContext) {
    JsonObject queryJson = routingContext.getBodyAsJson();
    Integer id = queryJson.getInteger("id");
    Integer age = queryJson.getInteger("age");

    getConnection().setHandler(conn -> {
      if (conn.failed()) {
        conn.cause().printStackTrace();
        logger.error("獲取連線失敗, " + conn.cause().getMessage());
        return;
      }
      String sql = "SELECT * FROM t_user WHERE id = ? AND age = ?";
      String sql1 = "SELECT * FROM t_phone WHERE uid = ?";
      String sql2 = "SELECT * FROM t_address WHERE phoneId = ?";
      final SQLConnection connection = conn.result();

      // 以下的鏈式呼叫 需要去父類裡的方法的 connection close 去掉
      // 先根據 id, age 查出一個使用者
      // 根據 使用者 id 查詢 手機號
      // 然後 根據 手機號查詢 歸屬地 (只是演示一種複雜查詢的思路)
      queryWithParams(connection, sql, new JsonArray().add(id).add(age))
        .compose(r -> queryWithParams(connection,  sql1, new JsonArray().add(r.get(0).getValue("id"))))
        .compose(r -> queryWithParams(connection, sql2, new JsonArray().add(r.get(0).getValue("phoneId"))))
        .setHandler(r -> {
          connection.close();
          if (r.failed()) {
            logger.error(r.cause().getMessage());
            respError(routingContext, 500, new JsonObject().put("error", "查詢錯誤"));
            return;
          }
          respSuccess(routingContext, new JsonObject().put("data", r.result()));
        });
    });
  }

  private void deleteUserById(RoutingContext routingContext) {
    String userId = routingContext.request().getParam("userId");
    String sql = "DELECT FROM t_user WHERE id = ?";
    getConnection()
      .compose(r -> deleteWithParams(r, sql, new JsonArray().add(userId)))
      .setHandler(res -> {
        if (res.failed()) {
          logger.error(res.cause().getMessage());
          respError(routingContext, 500, new JsonObject().put("error", "刪除錯誤"));
          return;
        }
        respSuccess(routingContext, new JsonObject().put("msg", "ok"));
      });
  }

  private void getUserList(RoutingContext routingContext) {
    String userId = routingContext.request().getParam("userId");
    String sql = "SELECT * FROM t_user WHERE id = ?";
    getConnection()
      .compose(r -> queryWithParams(r, sql, new JsonArray().add(userId)))
      .setHandler(res -> {
        if (res.failed()) {
          logger.error(res.cause().getMessage());
          respError(routingContext, 500, new JsonObject().put("error", "查詢錯誤"));
          return;
        }
        respSuccess(routingContext, new JsonObject().put("data", res.result()));
      });
  }

  private void createUser(RoutingContext routingContext) {
  }

  private void updateUserById(RoutingContext routingContext) {
  }

  private void getUserById(RoutingContext routingContext) {
  }

  private void respSuccess(RoutingContext routingContext, JsonObject jsonObject) {
    routingContext
            .response()
            .putHeader("content-type", "application/json; charset=utf-8")
            .end(jsonObject.encode());
  }

  private void respError(RoutingContext routingContext, Integer errorCode, JsonObject jsonObject) {
    routingContext
            .response()
            .putHeader("content-type", "application/json; charset=utf-8")
            .setStatusCode(errorCode)
            .end(jsonObject.encode());
  }
}
複製程式碼
  • pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         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>com.xiaoniu.demo</groupId>
    <artifactId>demo</artifactId>
    <version>1.0-SNAPSHOT</version>


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

        <dependency>
            <groupId>io.vertx</groupId>
            <artifactId>vertx-web</artifactId>
            <version>3.5.0</version>
        </dependency>

        <dependency>
            <groupId>io.vertx</groupId>
            <artifactId>vertx-web-client</artifactId>
            <version>3.5.0</version>
        </dependency>

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

        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-api</artifactId>
            <version>2.8.2</version>
        </dependency>

        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>2.8.2</version>
        </dependency>

        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-slf4j-impl</artifactId>
            <version>2.8.2</version>
        </dependency>

        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-jcl</artifactId>
            <version>2.8.2</version>
        </dependency>

        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-jul</artifactId>
            <version>2.8.2</version>
        </dependency>

        <dependency>
            <groupId>com.lmax</groupId>
            <artifactId>disruptor</artifactId>
            <version>3.3.6</version>
        </dependency>
    </dependencies>

    <build>
        <pluginManagement>
            <plugins>
                <!-- We specify the Maven compiler plugin as we need to set it to Java 1.8 -->
                <plugin>
                    <artifactId>maven-compiler-plugin</artifactId>
                    <version>3.1</version>
                    <configuration>
                        <source>1.8</source>
                        <target>1.8</target>
                    </configuration>
                </plugin>
            </plugins>
        </pluginManagement>

        <plugins>

            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-shade-plugin</artifactId>
                <version>2.3</version>
                <executions>
                    <!-- Run shade goal on package phase -->
                    <execution>
                        <phase>package</phase>
                        <goals>
                            <goal>shade</goal>
                        </goals>
                        <configuration>
                            <createDependencyReducedPom>false</createDependencyReducedPom>
                            <transformers>
                                <transformer implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
                                    <manifestEntries>
                                        <Main-Class>io.vertx.core.Launcher</Main-Class>
                                        <Main-Verticle>改成自己的啟動類</Main-Verticle>
                                    </manifestEntries>
                                </transformer>
                                <transformer implementation="org.apache.maven.plugins.shade.resource.AppendingTransformer">
                                    <resource>META-INF/services/io.vertx.core.spi.VerticleFactory</resource>
                                </transformer>
                            </transformers>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>
複製程式碼

相關文章