io.vertx.sqlclient.RowSet Java Examples

static void selectReturn(AsyncResult<RowSet<Row>> res, Handler<AsyncResult<Row>> replyHandler) {
  if (res.failed()) {
    replyHandler.handle(Future.failedFuture(res.cause()));
    return;
  }
  try {
    if (!res.result().iterator().hasNext()) {
      replyHandler.handle(Future.succeededFuture(null));
      return;
    }
    replyHandler.handle(Future.succeededFuture(res.result().iterator().next()));
  } catch (Exception e) {
    log.error(e.getMessage(), e);
    replyHandler.handle(Future.failedFuture(e));
  }
}
 

public void booleanExample01(SqlClient client) {
  client
    .query("SELECT graduated FROM students WHERE id = 0")
    .execute(ar -> {
    if (ar.succeeded()) {
      RowSet<Row> rowSet = ar.result();
      for (Row row : rowSet) {
        int pos = row.getColumnIndex("graduated");
        Byte value = row.get(Byte.class, pos);
        Boolean graduated = row.getBoolean("graduated");
      }
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}
 

@Test
public void testAutoClosingNonCacheOneShotPreparedBatchStatement(TestContext ctx) {
  MySQLConnection.connect(vertx, options.setCachePreparedStatements(false), ctx.asyncAssertSuccess(conn -> {
    conn.query("SHOW VARIABLES LIKE 'max_prepared_stmt_count'").execute(ctx.asyncAssertSuccess(res0 -> {
      Row row = res0.iterator().next();
      int maxPreparedStatementCount = Integer.parseInt(row.getString(1));
      ctx.assertEquals("max_prepared_stmt_count", row.getString(0));
      ctx.assertEquals(16382, maxPreparedStatementCount);

      for (int i = 0; i < 20000; i++) {
        // if we don't close the statement automatically in the codec, the statement handles would leak and raise an statement limit error
        List<Tuple> params = Arrays.asList(Tuple.of(1), Tuple.of(2), Tuple.of(3));
        conn.preparedQuery("SELECT CAST(? AS CHAR)").executeBatch(params, ctx.asyncAssertSuccess(res1 -> {
          ctx.assertEquals("1", res1.iterator().next().getString(0));
          RowSet<Row> res2 = res1.next();
          ctx.assertEquals("2", res2.iterator().next().getString(0));
          RowSet<Row> res3 = res2.next();
          ctx.assertEquals("3", res3.iterator().next().getString(0));
        }));
      }
    }));
  }));
}
 

@Test
public void testSetOption(TestContext ctx) {
  MySQLConnection.connect(vertx, options, ctx.asyncAssertSuccess(conn -> {
    // CLIENT_MULTI_STATEMENTS is on by default
    conn.query("SELECT 1; SELECT 2;").execute(ctx.asyncAssertSuccess(rowSet1 -> {
      ctx.assertEquals(1, rowSet1.size());
      Row row1 = rowSet1.iterator().next();
      ctx.assertEquals(1, row1.getInteger(0));
      RowSet<Row> rowSet2 = rowSet1.next();
      ctx.assertEquals(1, rowSet2.size());
      Row row2 = rowSet2.iterator().next();
      ctx.assertEquals(2, row2.getInteger(0));

      conn.setOption(MySQLSetOption.MYSQL_OPTION_MULTI_STATEMENTS_OFF, ctx.asyncAssertSuccess(v -> {
        // CLIENT_MULTI_STATEMENTS is off now
        conn.query("SELECT 1; SELECT 2;").execute(ctx.asyncAssertFailure(error -> {
          conn.close();
        }));
      }));
    }));
  }));
}
 

public void usingCursors01(SqlConnection connection) {
  connection.prepare("SELECT * FROM users WHERE age > ?", ar1 -> {
    if (ar1.succeeded()) {
      PreparedStatement pq = ar1.result();

      // Create a cursor
      Cursor cursor = pq.cursor(Tuple.of(18));

      // Read 50 rows
      cursor.read(50, ar2 -> {
        if (ar2.succeeded()) {
          RowSet<Row> rows = ar2.result();

          // Check for more ?
          if (cursor.hasMore()) {
            // Repeat the process...
          } else {
            // No more rows - close the cursor
            cursor.close();
          }
        }
      });
    }
  });
}
 

@Test
public void testBatch(TestContext ctx) {
  SqlTemplate<Map<String, Object>, RowSet<Row>> template = SqlTemplate
    .forQuery(connection, "SELECT #{id} :: INT4 \"id\", #{randomnumber} :: INT4 \"randomnumber\"");
  Map<String, Object> params1 = new HashMap<>();
  params1.put("id", 1);
  params1.put("randomnumber", 10);
  Map<String, Object> params2 = new HashMap<>();
  params1.put("id", 2);
  params1.put("randomnumber", 20);
  template.executeBatch(Arrays.asList(params1, params2), ctx.asyncAssertSuccess(res -> {
    ctx.assertEquals(1, res.size());
    Row row = res.iterator().next();
    ctx.assertEquals(2, row.getInteger(0));
    ctx.assertEquals(20, row.getInteger(1));
    res = res.next();
    ctx.assertNotNull(res);
    row = res.iterator().next();
    // Somehow returns null ... investigate bug
    // ctx.assertEquals(1, row.getInteger(0));
    // ctx.assertEquals(10, row.getInteger(1));
  }));
}
 

public void queries10(SqlConnection sqlConnection) {
  sqlConnection
    .prepare("SELECT * FROM users WHERE id = $1", ar -> {
      if (ar.succeeded()) {
        PreparedStatement preparedStatement = ar.result();
        preparedStatement.query()
          .execute(Tuple.of("julien"), ar2 -> {
            if (ar2.succeeded()) {
              RowSet<Row> rows = ar2.result();
              System.out.println("Got " + rows.size() + " rows ");
              preparedStatement.close();
            } else {
              System.out.println("Failure: " + ar2.cause().getMessage());
            }
          });
      } else {
        System.out.println("Failure: " + ar.cause().getMessage());
      }
    });
}
 

void query(String sql, Tuple tuple,
           Handler<ExtendedAsyncResult<RowSet<Row>>> fut) {

  getCon(gres -> {
    if (gres.failed()) {
      fut.handle(new Failure<>(gres.getType(), gres.cause()));
      return;
    }
    logger.debug("preparedQuery sql {}", sql);
    conn.preparedQuery(sql, tuple, qres -> {
      if (qres.failed()) {
        logger.fatal("preparedQuery sql {} failed: {}",
            sql, qres.cause().getMessage());
        close();
        fut.handle(new Failure<>(ErrorType.INTERNAL, qres.cause()));
        return;
      }
      fut.handle(new Success<>(qres.result()));
    });
  });
}
 

public void usingConnections03(SqlConnection connection) {
  connection.prepare("INSERT INTO USERS (id, name) VALUES (?, ?)", ar1 -> {
    if (ar1.succeeded()) {
      PreparedStatement prepared = ar1.result();

      // Create a query : bind parameters
      List<Tuple> batch = new ArrayList();

      // Add commands to the createBatch
      batch.add(Tuple.of("julien", "Julien Viet"));
      batch.add(Tuple.of("emad", "Emad Alblueshi"));

      prepared.query().executeBatch(batch, res -> {
        if (res.succeeded()) {

          // Process rows
          RowSet<Row> rows = res.result();
        } else {
          System.out.println("Batch failed " + res.cause());
        }
      });
    }
  });
}
 

public void queries10(SqlConnection sqlConnection) {
  sqlConnection
    .prepare("SELECT * FROM users WHERE id=$1", ar -> {
      if (ar.succeeded()) {
        PreparedStatement preparedStatement = ar.result();
        preparedStatement.query()
          .execute(Tuple.of("julien"), ar2 -> {
            if (ar2.succeeded()) {
              RowSet<Row> rows = ar2.result();
              System.out.println("Got " + rows.size() + " rows ");
              preparedStatement.close();
            } else {
              System.out.println("Failure: " + ar2.cause().getMessage());
            }
          });
      } else {
        System.out.println("Failure: " + ar.cause().getMessage());
      }
    });
}
 

@Test
public void selectReturnOneRow(TestContext context) {
  List<String> columns = new LinkedList<>();
  columns.add("field");
  RowDesc rowDesc = new RowDesc(columns);
  List<Row> rows = new LinkedList<>();
  Row row = new RowImpl(rowDesc);
  row.addString("value");
  rows.add(row);
  RowSet rowSet = new LocalRowSet(1).withColumns(columns).withRows(rows);

  Promise<RowSet<Row>> promise = Promise.promise();
  promise.complete(rowSet);
  PostgresClient.selectReturn(promise.future(), context.asyncAssertSuccess(res ->
      context.assertEquals("value", res.getString(0))));
}
 

public void enumType01Example(SqlClient client) {
  client
    .preparedQuery("INSERT INTO colors VALUES ($1)")
    .execute(Tuple.of(Color.red))
    .flatMap(res ->
      client
        .preparedQuery("SELECT color FROM colors")
        .execute()
    ).onComplete(res -> {
      if (res.succeeded()) {
        RowSet<Row> rows = res.result();
        for (Row row : rows) {
          System.out.println(row.get(Color.class, "color"));
        }
      }
  });
}
 

public void geometryExample03(SqlClient client) {
  client
    .query("SELECT g FROM geom;")
    .execute(ar -> {
    if (ar.succeeded()) {
      // Fetch the spatial data as a Vert.x Data Object
      RowSet<Row> result = ar.result();
      for (Row row : result) {
        Point point = row.get(Point.class, 0);
        System.out.println("Point x: " + point.getX());
        System.out.println("Point y: " + point.getY());
      }
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}
 

/**
 * converts a result set into pojos - handles 3 types of queries:
 * 1. a regular query will return N rows, where each row contains Y columns. one of those columns is the jsonb
 * column which is mapped into a pojo. each row will also contain the count column (if count was requested for
 * the query), other fields , like updated date may also be returned if they were requested in the select.
 *    1a. note that there is an attempt to map external (non jsonb) columns to fields in the pojo. for example,
 *    a column called update_date will attempt to map its value to a field called updateDate in the pojo. however,
 *    for this to happen, the query must select the update_date -> select id,jsonb,update_date from ....
 * 2. a facet query returns 2 columns, a uuid and a jsonb column. the results of the query are returned as
 * id and json rows. facets are returned as jsonb values:
 * {"facetValues": [{"count": 542,"value": "11 ed."}], "type": "name"}
 * (along with a static '00000000-0000-0000-0000-000000000000' uuid)
 * the count for a facet query is returned in the following manner:
 * {"count": 501312} , with a static uuid as the facets
 * 3. audit queries - queries that query an audit table, meaning the clazz parameter passed in has a jsonb member.
 *
 * @param rs
 * @param total
 * @param clazz
 * @return
 */
<T> Results<T> processResults(RowSet<Row> rs, Integer total, int offset, int limit, Class<T> clazz) {
  long start = System.nanoTime();

  if (total == null) {
    // NOTE: this may not be an accurate total, may be better for it to be 0 or null
    total = rs.rowCount();
  }

  ResultsHelper<T> resultsHelper = new ResultsHelper<>(rs, total, clazz);

  deserializeResults(resultsHelper);

  ResultInfo resultInfo = new ResultInfo();
  resultsHelper.facets.forEach((k , v) -> resultInfo.getFacets().add(v));
  Integer totalRecords = getTotalRecords(resultsHelper.list.size(),
      resultsHelper.total, offset, limit);
  resultInfo.setTotalRecords(totalRecords);

  Results<T> results = new Results<>();
  results.setResults(resultsHelper.list);
  results.setResultInfo(resultInfo);

  statsTracker(PROCESS_RESULTS_STAT_METHOD, clazz.getSimpleName(), start);
  return results;
}
 

private void getRoles(String username, Handler<AsyncResult<Set<Authorization>>> resultHandler) {
  if (options.getRolesQuery() != null) {
    client.preparedQuery(options.getRolesQuery()).execute(Tuple.of(username), preparedQuery -> {
      if (preparedQuery.succeeded()) {
        RowSet<Row> rows = preparedQuery.result();
        Set<Authorization> authorizations = new HashSet<>();
        for (Row row : rows) {
          String role = row.getString(0);
          authorizations.add(RoleBasedAuthorization.create(role));
        }
        resultHandler.handle(Future.succeededFuture(authorizations));
      } else {
        resultHandler.handle(Future.failedFuture(preparedQuery.cause()));
      }
    });
  } else {
    resultHandler.handle(Future.succeededFuture(Collections.emptySet()));
  }
}
 

private String intsAsString(RowSet<Row> resultSet) {
  StringBuilder s = new StringBuilder();
  RowIterator<Row> iterator = resultSet.iterator();
  while (iterator.hasNext()) {
    if (s.length() > 0) {
      s.append(", ");
    }
    s.append(iterator.next().getInteger(0));
  }
  return s.toString();
}
 

public void queries01(SqlClient client) {
  client
    .query("SELECT * FROM users WHERE id='julien'")
    .execute(ar -> {
    if (ar.succeeded()) {
      RowSet<Row> result = ar.result();
      System.out.println("Got " + result.size() + " rows ");
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}
 

public void geometryExample02(SqlClient client) {
  client
    .query("SELECT ST_AsBinary(g) FROM geom;")
    .execute(ar -> {
    if (ar.succeeded()) {
      // Fetch the spatial data in WKB format
      RowSet<Row> result = ar.result();
      for (Row row : result) {
        Buffer wkbValue = row.getBuffer(0);
      }
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}
 

/**
 * Create a parameterized/prepared INSERT, UPDATE or DELETE statement and
 * run it with a list of sets of parameters.
 *
 * <p>Example:
 * <pre>
 *  postgresClient.startTx(beginTx -> {
 *        try {
 *          postgresClient.execute(beginTx, sql, params, reply -> {...
 * </pre>
 * @param conn - connection - see {@link #startTx(Handler)}
 * @param sql - the sql to run
 * @param params - there is one list entry for each sql invocation containing the parameters for the placeholders.
 * @param replyHandler - reply handler with one UpdateResult for each list entry of params.
 */
public void execute(AsyncResult<SQLConnection> conn, String sql, List<Tuple> params,
                    Handler<AsyncResult<List<RowSet<Row>>>> replyHandler) {
  try {
    if (conn.failed()) {
      replyHandler.handle(Future.failedFuture(conn.cause()));
      return;
    }
    PgConnection sqlConnection = conn.result().conn;
    List<RowSet<Row>> results = new ArrayList<>(params.size());
    Iterator<Tuple> iterator = params.iterator();
    Runnable task = new Runnable() {
      @Override
      public void run() {
        if (! iterator.hasNext()) {
          replyHandler.handle(Future.succeededFuture(results));
          return;
        }
        Tuple params1 = iterator.next();
        sqlConnection.preparedQuery(sql).execute(params1, query -> {
          if (query.failed()) {
            replyHandler.handle(Future.failedFuture(query.cause()));
            return;
          }
          results.add(query.result());
          this.run();
        });
      }
    };
    task.run();
  } catch (Exception e) {
    log.error(e.getMessage(), e);
    replyHandler.handle(Future.failedFuture(e));
  }
}
 

public void queries01(SqlClient client) {
  client
    .query("SELECT * FROM users WHERE id='julien'")
    .execute(ar -> {
    if (ar.succeeded()) {
      RowSet<Row> result = ar.result();
      System.out.println("Got " + result.size() + " rows ");
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}
 

public void update(AsyncResult<SQLConnection> conn, String table, Object entity, String jsonbField,
                   String whereClause, boolean returnUpdatedIds, Handler<AsyncResult<RowSet<Row>>> replyHandler) {
  if (conn.failed()) {
    replyHandler.handle(Future.failedFuture(conn.cause()));
    return;
  }
  long start = System.nanoTime();
  StringBuilder sb = new StringBuilder();
  sb.append(whereClause);
  StringBuilder returning = new StringBuilder();
  if (returnUpdatedIds) {
    returning.append(RETURNING_ID);
  }
  try {
    String q = UPDATE + schemaName + DOT + table + SET + jsonbField + " = $1::jsonb " + whereClause
        + SPACE + returning;
    log.debug("update query = " + q);
    conn.result().conn.preparedQuery(q).execute(Tuple.of(pojo2JsonObject(entity)), query -> {
      if (query.failed()) {
        log.error(query.cause().getMessage(), query.cause());
      }
      statsTracker(UPDATE_STAT_METHOD, table, start);
      replyHandler.handle(query);
    });
  } catch (Exception e) {
    log.error(e.getMessage(), e);
    replyHandler.handle(Future.failedFuture(e));
  }
}
 

protected void testBinaryDecode(TestContext ctx, String sql, Tuple params, Consumer<RowSet<Row>> checker) {
  MySQLConnection.connect(vertx, options, ctx.asyncAssertSuccess(conn -> {
    conn.preparedQuery(sql).execute(params, ctx.asyncAssertSuccess(result -> {
      checker.accept(result);
      conn.close();
    }));
  }));
}
 

public ResultsHelper(RowSet<Row> resultSet, int total, Class<T> clazz) {
  this.list = new ArrayList<>();
  this.facets = new HashMap<>();
  this.resultSet = resultSet;
  this.clazz= clazz;
  this.total = total;
  this.offset = 0;
}
 

public void queries09(SqlClient client, SqlConnectOptions connectOptions) {

    // Enable prepare statements caching
    connectOptions.setCachePreparedStatements(true);
    client
      .preparedQuery("SELECT * FROM users WHERE id=$1")
      .execute(Tuple.of("julien"), ar -> {
        if (ar.succeeded()) {
          RowSet<Row> rows = ar.result();
          System.out.println("Got " + rows.size() + " rows ");
        } else {
          System.out.println("Failure: " + ar.cause().getMessage());
        }
      });
  }
 

public void queries02(SqlClient client) {
  client
    .preparedQuery("SELECT * FROM users WHERE id=?")
    .execute(Tuple.of("julien"), ar -> {
    if (ar.succeeded()) {
      RowSet<Row> rows = ar.result();
      System.out.println("Got " + rows.size() + " rows ");
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}
 

/**
 * update a section / field / object in the pojo -
 * <br>
 * for example:
 * <br> if a json called po_line contains the following field
 * <pre>
 *     "po_line_status": {
 *       "value": "SENT",
 *       "desc": "sent to vendor"
 *     },
 * </pre>
 *  this translates into a po_line_status object within the po_line object - to update the entire object / section
 *  create an updateSection object pushing into the section the po line status as the field and the value (string / json / etc...) to replace it with
 *  <pre>
 *  a = new UpdateSection();
 *  a.addField("po_line_status");
 *  a.setValue(new JsonObject("{\"value\":\"SOMETHING_NEW4\",\"desc\":\"sent to vendor again\"}"));
 *  </pre>
 * Note that postgres does not update inplace the json but rather will create a new json with the
 * updated section and then reference the id to that newly created json
 * <br>
 * Queries generated will look something like this:
 * <pre>
 *
 * update test.po_line set jsonb = jsonb_set(jsonb, '{po_line_status}', '{"value":"SOMETHING_NEW4","desc":"sent to vendor"}') where _id = 19;
 * update test.po_line set jsonb = jsonb_set(jsonb, '{po_line_status, value}', '"SOMETHING_NEW5"', false) where _id = 15;
 * </pre>
 *
 * @param table - table to update
 * @param section - see UpdateSection class
 * @param when - Criterion object
 * @param replyHandler
 *
 */
public void update(String table, UpdateSection section, Criterion when, boolean returnUpdatedIdsCount,
                   Handler<AsyncResult<RowSet<Row>>> replyHandler) {
  long start = System.nanoTime();
  getConnection(res -> {
    if (res.succeeded()) {
      PgConnection connection = res.result();
      try {
        String value = section.getValue().replace("'", "''");
        String where = when == null ? "" : when.toString();
        String returning = returnUpdatedIdsCount ? RETURNING_ID : "";
        String q = UPDATE + schemaName + DOT + table + SET + DEFAULT_JSONB_FIELD_NAME
            + " = jsonb_set(" + DEFAULT_JSONB_FIELD_NAME + ","
            + section.getFieldsString() + ", '" + value + "', false) " + where + returning;
        log.debug("update query = " + q);
        connection.query(q).execute(query -> {
          connection.close();
          statsTracker(UPDATE_STAT_METHOD, table, start);
          if (query.failed()) {
            log.error(query.cause().getMessage(), query.cause());
            replyHandler.handle(Future.failedFuture(query.cause()));
          } else {
            replyHandler.handle(Future.succeededFuture(query.result()));
          }
        });
      } catch (Exception e) {
        if (connection != null){
          connection.close();
        }
        log.error(e.getMessage(), e);
        replyHandler.handle(Future.failedFuture(e));
      }
    } else {
      log.error(res.cause().getMessage(), res.cause());
      replyHandler.handle(Future.failedFuture(res.cause()));
    }
  });
}
 

public void typeMapping01(Pool pool) {
  pool
    .query("SELECT an_int_column FROM exampleTable")
    .execute(ar -> {
    RowSet<Row> rowSet = ar.result();
    Row row = rowSet.iterator().next();

    // Stored as INTEGER column type and represented as java.lang.Integer
    Object value = row.getValue(0);

    // Convert to java.lang.Long
    Long longValue = row.getLong(0);
  });
}
 

public void generatedKeys(SqlClient client) {
  client
    .preparedQuery("SELECT color_id FROM FINAL TABLE ( INSERT INTO color (color_name) VALUES (?), (?), (?) )")
    .execute(Tuple.of("white", "red", "blue"), ar -> {
    if (ar.succeeded()) {
      RowSet<Row> rows = ar.result();
      System.out.println("Inserted " + rows.rowCount() + " new rows.");
      for (Row row : rows) {
        System.out.println("generated key: " + row.getInteger("color_id"));
      }
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}
 

public void gettingStarted() {

    // Connect options
    DB2ConnectOptions connectOptions = new DB2ConnectOptions()
      .setPort(50000)
      .setHost("the-host")
      .setDatabase("the-db")
      .setUser("user")
      .setPassword("secret");

    // Pool options
    PoolOptions poolOptions = new PoolOptions()
      .setMaxSize(5);

    // Create the client pool
    DB2Pool client = DB2Pool.pool(connectOptions, poolOptions);

    // A simple query
    client
      .query("SELECT * FROM users WHERE id='julien'")
      .execute(ar -> {
      if (ar.succeeded()) {
        RowSet<Row> result = ar.result();
        System.out.println("Got " + result.size() + " rows ");
      } else {
        System.out.println("Failure: " + ar.cause().getMessage());
      }

      // Now close the pool
      client.close();
    });
  }
 

private static void setStatus(final ResultObject worldObject, AsyncResult<RowSet<Row>> ar) {
	if (ar.succeeded()) {														
		worldObject.setStatus(200);			
		
	} else {	
		System.out.println("unable to update");
		if (ar.cause()!=null) {
			ar.cause().printStackTrace();
		}
		
		worldObject.setStatus(500);
	}
}