Vertx之响应式Mysql Template

介绍

Vertx响应式mysql客户端，基于SQL Client Templates模版操作sql脚本

1. maven项目依赖

<dependencies>
	<dependency>
		<groupId>io.vertx</groupId>
		<artifactId>vertx-web</artifactId>
	</dependency>
	<dependency>
		<groupId>io.vertx</groupId>
		<artifactId>vertx-config-yaml</artifactId>
	</dependency>
	<dependency>
		<groupId>io.vertx</groupId>
		<artifactId>vertx-mysql-client</artifactId>
	</dependency>
	<dependency>
		<groupId>io.vertx</groupId>
		<artifactId>vertx-sql-client-templates</artifactId>
	</dependency>
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>8.0.27</version>
	</dependency>
	<dependency>
		<groupId>com.fasterxml.jackson.core</groupId>
		<artifactId>jackson-databind</artifactId>
	</dependency>
	<dependency>
		<groupId>com.lance.common</groupId>
		<artifactId>vertx-common-core</artifactId>
		<version>0.0.1-SNAPSHOT</version>
	</dependency>
</dependencies>

2.YAML文件配置

server:
  port: 8000

mysql:
  host: 127.0.0.1
  port: 3306
  database: v_example
  username: root
  password: li123456
  charset: utf8
  collation: utf8_general_ci
  maxSize: 30
  reconnectAttempts: 3
  reconnectInterval: 1000
  poolName: v-pool

3.启动加载配置文件, 并放入到config中去

public class TemplateApplication {

  public static void main(String[] args) {
    Vertx vertx = Vertx.vertx();
    ConfigRetriever retriever = readYaml(vertx);

    retriever.getConfig(json -> {
      try {
        JsonObject object = json.result();
        DbHelper dbHelper = new DbHelper(object.getJsonObject("mysql"), vertx);
        dbHelper.afterPropertiesSet();

        DeploymentOptions options = new DeploymentOptions().setConfig(object);
        vertx.deployVerticle(MainApp.class.getName(), options);
      } catch (Exception ex) {
        log.error("===> Vertx start fail: ", ex);
      }
    });
  }

  /**
   * read yaml config
   *
   * @param vertx vertx
   * @return ConfigRetriever
   */
  private static ConfigRetriever readYaml(Vertx vertx) {
    ConfigStoreOptions store = new ConfigStoreOptions()
        .setType("file")
        .setFormat("yaml")
        .setOptional(true)
        .setConfig(new JsonObject().put("path", "application.yaml"));

    return ConfigRetriever.create(vertx, new ConfigRetrieverOptions().addStore(store));
  }
}

4.Mysql连接池配置

public class DbHelper {
  private final JsonObject object;
  private final Vertx vertx;
  private static MySQLPool mySqlPool;

  /**
   * 获取客户端
   *
   * @return MySQLPool
   */
  public static MySQLPool client() {
    return mySqlPool;
  }

  /**
   * 初始化mysql连接
   */
  public void afterPropertiesSet() {
    ConfigProperties.MysqlProperties properties = object.mapTo(ConfigProperties.MysqlProperties.class);
    MySQLConnectOptions connectOptions = new MySQLConnectOptions()
        .setPort(properties.getPort())
        .setHost(properties.getHost())
        .setDatabase(properties.getDatabase())
        .setUser(properties.getUsername())
        .setPassword(properties.getPassword())
        .setReconnectAttempts(properties.getReconnectAttempts())
        .setReconnectInterval(properties.getReconnectInterval())
        .setCharset(properties.getCharset())
        .setCollation(properties.getCollation());

    PoolOptions poolOptions = new PoolOptions()
        .setMaxSize(properties.getMaxSize())
        .setName(properties.getPoolName())
        .setShared(true);

    mySqlPool = MySQLPool.pool(vertx, connectOptions, poolOptions);
  }
}

5.连接池数据库sql执行

public class UserService {

  /**
   * find list
   */
  public void list(RoutingContext ctx) {
    MySQLPool pool = DbHelper.client();
    SqlTemplate.forQuery(pool, select(null))
        .mapTo(UserInfo.class)
        .execute(null)
        .onSuccess(rs -> {
          List<UserInfo> list = new ArrayList<>();
          rs.forEach(list::add);
          ctx.json(R.data(list));
        }).onFailure(e -> {
          log.warn("Failure: ", e);
          ctx.json(R.fail("list fail"));
        });
  }

  /**
   * find one
   */
  public void detail(RoutingContext ctx) {
    MySQLPool pool = DbHelper.client();
    SqlTemplate.forQuery(pool, select("where user_id=#{userId}"))
        .mapTo(UserInfo.class)
        .execute(Collections.singletonMap("userId", ctx.pathParam("userId")))
        .onSuccess(result -> {
          if (result.size() > 0) {
            ctx.json(R.data(result.iterator().next()));
            return;
          }
          ctx.json(R.data(null));
        }).onFailure(e -> {
          log.warn("Failure: ", e);
          ctx.json(R.fail("detail fail"));
        });
  }

  /**
   * add user info
   */
  public void add(RoutingContext ctx) {
    UserInfo user = ctx.getBodyAsJson().mapTo(UserInfo.class);
    if (user == null) {
      ctx.json(R.fail("参数为空"));
      return;
    }

    MySQLPool pool = DbHelper.client();
    SqlTemplate.forUpdate(pool, "insert into t_user(username,password,age,status,create_time,update_time)value(#{username},#{password},#{age},1,now(),now())")
        .mapFrom(UserInfo.class)
        .execute(user)
        .onSuccess(v -> ctx.json(R.success("save success")))
        .onFailure(e -> {
          log.warn("Failure: ", e);
          ctx.json(R.fail("save fail"));
        });
  }

  /**
   * update user
   */
  public void update(RoutingContext ctx) {
    log.info("===>{}", ctx.getBodyAsJson());
    UserInfo user = ctx.getBodyAsJson().mapTo(UserInfo.class);
    if (user == null) {
      ctx.json(R.fail("参数为空"));
      return;
    }

    MySQLPool pool = DbHelper.client();
    SqlTemplate.forUpdate(pool, "update t_user set username=#{username},password=#{password},age=#{age},status=#{status},update_time=now() where user_id=#{userId}")
        .mapFrom(UserInfo.class)
        .execute(user)
        .onSuccess(v -> ctx.json(R.success("update success")))
        .onFailure(e -> {
          log.warn("Failure: ", e);
          ctx.json(R.fail("update fail"));
        });
  }

  /**
   * delete one
   */
  public void delete(RoutingContext ctx) {
    MySQLPool pool = DbHelper.client();
    SqlTemplate.forUpdate(pool, "delete From t_user where user_id=#{userId}")
        .execute(Collections.singletonMap("userId", ctx.pathParam("userId")))
        .onSuccess(v -> ctx.json(R.success("delete success")))
        .onFailure(e -> {
          log.warn("Failure: ", e);
          ctx.json(R.fail("delete fail"));
        });
  }

  private String select(String where) {
    String select = "SELECT user_id as userId, username,password,age,status,create_time as createTime,update_time as updateTime from t_user";
    if (StringUtils.isNotBlank(where)) {
      select += " " + where;
    }
    return select;
  }
}

6.项目完整地址

Vertx之响应式Mysql Template Github 地址

Vertx之响应式Mysql Template Gitee 地址