Java SQLConnection类代码示例

import io.vertx.ext.sql.SQLConnection; //导入依赖的package包/类
private Future<Void> delete(SQLConnection connection, String id) {
    Future<Void> future = Future.future();
    String sql = "DELETE FROM Articles WHERE id = ?";
    connection.updateWithParams(sql,
        new JsonArray().add(Integer.valueOf(id)),
        ar -> {
            connection.close();
            if (ar.failed()) {
                future.fail(ar.cause());
            } else {
                if (ar.result().getUpdated() == 0) {
                    future.fail(new NoSuchElementException("Unknown article " + id));
                } else {
                    future.complete();
                }
            }
        }
    );
    return future;
}
 

import io.vertx.ext.sql.SQLConnection; //导入依赖的package包/类
private void pageDeletionHandler(RoutingContext context) {
  String id = context.request().getParam("id");
  dbClient.getConnection(car -> {
    if (car.succeeded()) {
      SQLConnection connection = car.result();
      connection.updateWithParams(SQL_DELETE_PAGE, new JsonArray().add(id), res -> {
        connection.close();
        if (res.succeeded()) {
          context.response().setStatusCode(303);
          context.response().putHeader("Location", "/");
          context.response().end();
        } else {
          context.fail(res.cause());
        }
      });
    } else {
      context.fail(car.cause());
    }
  });
}
 

import io.vertx.ext.sql.SQLConnection; //导入依赖的package包/类
WikiDatabaseServiceImpl(JDBCClient dbClient, HashMap<SqlQuery, String> sqlQueries, Handler<AsyncResult<WikiDatabaseService>> readyHandler) {
  this.dbClient = dbClient;
  this.sqlQueries = sqlQueries;

  dbClient.getConnection(ar -> {
    if (ar.failed()) {
      LOGGER.error("Could not open a database connection", ar.cause());
      readyHandler.handle(Future.failedFuture(ar.cause()));
    } else {
      SQLConnection connection = ar.result();
      connection.execute(sqlQueries.get(SqlQuery.CREATE_PAGES_TABLE), create -> {
        connection.close();
        if (create.failed()) {
          LOGGER.error("Database preparation error", create.cause());
          readyHandler.handle(Future.failedFuture(create.cause()));
        } else {
          readyHandler.handle(Future.succeededFuture(this));
        }
      });
    }
  });
}
 

import io.vertx.ext.sql.SQLConnection; //导入依赖的package包/类
private Future<Article> insert(SQLConnection connection, Article article, boolean closeConnection) {
    Future<Article> future = Future.future();
    String sql = "INSERT INTO Articles (title, url) VALUES (?, ?)";
    connection.updateWithParams(sql,
        new JsonArray().add(article.getTitle()).add(article.getUrl()),
        ar -> {
            if (closeConnection) {
                connection.close();
            }
            future.handle(
                ar.map(res -> new Article(res.getKeys().getLong(0), article.getTitle(), article.getUrl()))
            );
        }
    );
    return future;
}
 

import io.vertx.ext.sql.SQLConnection; //导入依赖的package包/类
private Future<Article> queryOne(SQLConnection connection, String id) {
    Future<Article> future = Future.future();
    String sql = "SELECT * FROM articles WHERE id = ?";
    connection.queryWithParams(sql, new JsonArray().add(Integer.valueOf(id)), result -> {
        connection.close();
        future.handle(
            result.map(rs -> {
                List<JsonObject> rows = rs.getRows();
                if (rows.size() == 0) {
                    throw new NoSuchElementException("No article with id " + id);
                } else {
                    JsonObject row = rows.get(0);
                    return new Article(row);
                }
            })
        );
    });
    return future;
}
 

import io.vertx.ext.sql.SQLConnection; //导入依赖的package包/类
private Future<Void> update(SQLConnection connection, String id, Article article) {
    Future<Void> future = Future.future();
    String sql = "UPDATE articles SET title = ?, url = ? WHERE id = ?";
    connection.updateWithParams(sql, new JsonArray().add(article.getTitle()).add(article.getUrl())
            .add(Integer.valueOf(id)),
        ar -> {
            connection.close();
            if (ar.failed()) {
                future.fail(ar.cause());
            } else {
                UpdateResult ur = ar.result();
                if (ur.getUpdated() == 0) {
                    future.fail(new NoSuchElementException("No article with id " + id));
                } else {
                    future.complete();
                }
            }
        });
    return future;
}
 

import io.vertx.ext.sql.SQLConnection; //导入依赖的package包/类
private Future<SQLConnection> createSomeDataIfNone(SQLConnection connection) {
    Future<SQLConnection> future = Future.future();
    connection.query("SELECT * FROM Articles", select -> {
        if (select.failed()) {
            future.fail(select.cause());
        } else {
            if (select.result().getResults().isEmpty()) {
                Article article1 = new Article("Fallacies of distributed computing",
                    "https://en.wikipedia.org/wiki/Fallacies_of_distributed_computing");
                Article article2 = new Article("Reactive Manifesto",
                    "https://www.reactivemanifesto.org/");
                Future<Article> insertion1 = insert(connection, article1, false);
                Future<Article> insertion2 = insert(connection, article2, false);
                CompositeFuture.all(insertion1, insertion2)
                    .setHandler(r -> future.handle(r.map(connection)));
            } else {
                future.complete(connection);
            }
        }
    });
    return future;
}
 

import io.vertx.ext.sql.SQLConnection; //导入依赖的package包/类
@Test
public void testStockTradesPersisted(TestContext context) throws ClassNotFoundException {
    Async async = context.async();
    JsonObject jdbcConfig = new JsonObject(config.getObject("jdbc").render(ConfigRenderOptions.concise()));
    JDBCClient jdbc = JDBCClient.createNonShared(vertx, jdbcConfig);
    Class.forName(jdbcConfig.getString("driverclass"));

    jdbc.getConnection(ar -> {
        SQLConnection connection = ar.result();
        if (ar.failed()) {
            context.fail(ar.cause());
        } else {
            connection.query(SELECT_STATEMENT, result -> {
                ResultSet set = result.result();
                List<JsonObject> operations = set.getRows().stream()
                        .map(json -> new JsonObject(json.getString("OPERATION")))
                        .collect(Collectors.toList());
                context.assertTrue(operations.size() >= 3);
                connection.close();
                async.complete();
            });
        }
    });
}
 

import io.vertx.ext.sql.SQLConnection; //导入依赖的package包/类
/**
 * send a query to update within a transaction
 * @param conn - connection - see startTx
 * @param sql - the sql to run
 * @param replyHandler
 * Example:
 *  postgresClient.startTx(beginTx -> {
 *        try {
 *          postgresClient.mutate(beginTx, sql, reply -> {...
 */
@SuppressWarnings("unchecked")
public void mutate(Object conn, String sql, Handler<AsyncResult<String>> replyHandler){
  SQLConnection sqlConnection = ((Future<SQLConnection>) conn).result();
  try {
    sqlConnection.update(sql, query -> {
      if (query.failed()) {
        replyHandler.handle(Future.failedFuture(query.cause()));
      } else {
        replyHandler.handle(Future.succeededFuture(query.result().toString()));
      }
    });
  } catch (Exception e) {
    log.error(e.getMessage(), e);
    replyHandler.handle(Future.failedFuture(e));
  }
}
 

import io.vertx.ext.sql.SQLConnection; //导入依赖的package包/类
public void example3_1(SQLConnection connection) {

    String query = "SELECT ID, FNAME, LNAME, SHOE_SIZE from PEOPLE WHERE LNAME=? AND SHOE_SIZE > ?";
    JsonArray params = new JsonArray().add("Fox").add(9);

    connection.queryWithParams(query, params, res -> {

      if (res.succeeded()) {
        // Get the result set
        ResultSet resultSet = res.result();
      } else {
        // Failed!
      }
    });

  }
 

import io.vertx.ext.sql.SQLConnection; //导入依赖的package包/类
public void example8(SQLConnection connection) {
  // Assume that there is a SQL function like this:
  //
  // create function one_hour_ago() returns timestamp
  //    return now() - 1 hour;

  // note that you do not need to declare the output for functions
  String func = "{ call one_hour_ago() }";

  connection.call(func, res -> {

    if (res.succeeded()) {
      ResultSet result = res.result();
    } else {
      // Failed!
    }
  });
}
 

import io.vertx.ext.sql.SQLConnection; //导入依赖的package包/类
public void example9(SQLConnection connection) {
  // Assume that there is a SQL procedure like this:
  //
  // create procedure new_customer(firstname varchar(50), lastname varchar(50))
  //   modifies sql data
  //   insert into customers values (default, firstname, lastname, current_timestamp);

  String func = "{ call new_customer(?, ?) }";

  connection.callWithParams(func, new JsonArray().add("John").add("Doe"), null, res -> {

    if (res.succeeded()) {
      // Success!
    } else {
      // Failed!
    }
  });
}
 

import io.vertx.ext.sql.SQLConnection; //导入依赖的package包/类
public void example10(SQLConnection connection) {
  // Assume that there is a SQL procedure like this:
  //
  // create procedure customer_lastname(IN firstname varchar(50), OUT lastname varchar(50))
  //   modifies sql data
  //   select lastname into lastname from customers where firstname = firstname;

  String func = "{ call customer_lastname(?, ?) }";

  connection.callWithParams(func, new JsonArray().add("John"), new JsonArray().addNull().add("VARCHAR"), res -> {

    if (res.succeeded()) {
      ResultSet result = res.result();
    } else {
      // Failed!
    }
  });
}
 

import io.vertx.ext.sql.SQLConnection; //导入依赖的package包/类
public void example15(SQLConnection connection) {
  connection.queryStream("SELECT * FROM large_table; SELECT * FROM other_table", stream -> {
    if (stream.succeeded()) {
      SQLRowStream sqlRowStream = stream.result();

      sqlRowStream
        .resultSetClosedHandler(v -> {
          // will ask to restart the stream with the new result set if any
          sqlRowStream.moreResults();
        })
        .handler(row -> {
          // do something with the row...
        })
        .endHandler(v -> {
          // no more data available...
        });
    }
  });
}
 

import io.vertx.ext.sql.SQLConnection; //导入依赖的package包/类
public void example4(JDBCClient client) {

    // Now do stuff with it:

    client.getConnection(res -> {
      if (res.succeeded()) {

        SQLConnection connection = res.result();

        connection.query("SELECT * FROM some_table", res2 -> {
          if (res2.succeeded()) {

            ResultSet rs = res2.result();
            // Do something with results
          }
        });
      } else {
        // Failed to get connection - deal with it
      }
    });

  }