Java Code Examples for io.vertx.sqlclient.RowSet

The following examples show how to use io.vertx.sqlclient.RowSet. These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source Project: vertx-sql-client   Source File: SqlClientExamples.java    License: Apache License 2.0 6 votes vote down vote up
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());
      }
    });
}
 
Example 2
Source Project: vertx-sql-client   Source File: SqlClientExamples.java    License: Apache License 2.0 6 votes vote down vote up
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();
          }
        }
      });
    }
  });
}
 
Example 3
Source Project: vertx-sql-client   Source File: PgClientExamples.java    License: Apache License 2.0 6 votes vote down vote up
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"));
        }
      }
  });
}
 
Example 4
Source Project: raml-module-builder   Source File: PostgresClientIT.java    License: Apache License 2.0 6 votes vote down vote up
@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))));
}
 
Example 5
Source Project: vertx-sql-client   Source File: MySQLClientExamples.java    License: Apache License 2.0 6 votes vote down vote up
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());
    }
  });
}
 
Example 6
Source Project: raml-module-builder   Source File: PostgresClient.java    License: Apache License 2.0 6 votes vote down vote up
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));
  }
}
 
Example 7
Source Project: vertx-auth   Source File: SqlAuthorizationImpl.java    License: Apache License 2.0 6 votes vote down vote up
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()));
  }
}
 
Example 8
Source Project: vertx-sql-client   Source File: MySQLClientExamples.java    License: Apache License 2.0 6 votes vote down vote up
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());
    }
  });
}
 
Example 9
Source Project: vertx-sql-client   Source File: MySQLQueryTest.java    License: Apache License 2.0 6 votes vote down vote up
@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));
        }));
      }
    }));
  }));
}
 
Example 10
Source Project: vertx-sql-client   Source File: PgClientTest.java    License: Apache License 2.0 6 votes vote down vote up
@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));
  }));
}
 
Example 11
Source Project: vertx-sql-client   Source File: SqlClientExamples.java    License: Apache License 2.0 6 votes vote down vote up
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());
        }
      });
    }
  });
}
 
Example 12
Source Project: vertx-sql-client   Source File: SqlClientExamples.java    License: Apache License 2.0 6 votes vote down vote up
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());
      }
    });
}
 
Example 13
Source Project: vertx-sql-client   Source File: MySQLUtilityCommandTest.java    License: Apache License 2.0 6 votes vote down vote up
@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();
        }));
      }));
    }));
  }));
}
 
Example 14
Source Project: raml-module-builder   Source File: PostgresClient.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * 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;
}
 
Example 15
Source Project: okapi   Source File: PostgresQuery.java    License: Apache License 2.0 6 votes vote down vote up
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()));
    });
  });
}
 
Example 16
public CompletionStage<RowSet<Row>> preparedQuery(String sql, Tuple parameters) {
	feedback(sql);
	String processedSql = usePostgresStyleParameters ? Parameters.process( sql, parameters.size() ) : sql;
	return Handlers.toCompletionStage(
			handler -> client().preparedQuery( processedSql ).execute( parameters, handler )
	);
}
 
Example 17
Source Project: raml-module-builder   Source File: PostgresClientTest.java    License: Apache License 2.0 5 votes vote down vote up
@Test
public void testProcessResults() {
  PostgresClient testClient = PostgresClient.testClient();

  int total = 15;
  RowSet<Row> rs = getMockTestPojoResultSet(total);

  List<TestPojo> results = testClient.processResults(rs, total, DEFAULT_OFFSET, DEFAULT_LIMIT, TestPojo.class).getResults();

  assertTestPojoResults(results, total);
}
 
Example 18
Source Project: raml-module-builder   Source File: PostgresClient.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Run a parameterized/prepared select query.
 *
 * <p>This never closes the connection conn.
 *
 * <p>To update see {@link #execute(AsyncResult, String, Tuple, Handler)}.
 *
 * @param conn  The connection on which to execute the query on.
 * @param sql  The sql query to run.
 * @param params  The parameters for the placeholders in sql.
 * @param replyHandler  The query result or the failure.
 */
public void select(AsyncResult<SQLConnection> conn, String sql, Tuple params,
    Handler<AsyncResult<RowSet<Row>>> replyHandler) {
  try {
    if (conn.failed()) {
      replyHandler.handle(Future.failedFuture(conn.cause()));
      return;
    }
    conn.result().conn.preparedQuery(sql).execute(params, replyHandler);
  } catch (Exception e) {
    log.error("select sql: " + e.getMessage() + " - " + sql, e);
    replyHandler.handle(Future.failedFuture(e));
  }
}
 
Example 19
Source Project: vertx-sql-client   Source File: SqlClientExamples.java    License: Apache License 2.0 5 votes vote down vote up
public void queries02(SqlClient client) {
  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());
    }
  });
}
 
Example 20
Source Project: vertx-sql-client   Source File: SqlClientExamples.java    License: Apache License 2.0 5 votes vote down vote up
public void queries03(SqlClient client) {
  client
    .preparedQuery("SELECT first_name, last_name FROM users")
    .execute(ar -> {
    if (ar.succeeded()) {
      RowSet<Row> rows = ar.result();
      for (Row row : rows) {
        System.out.println("User " + row.getString(0) + " " + row.getString(1));
      }
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}
 
Example 21
Source Project: vertx-sql-client   Source File: SqlClientExamples.java    License: Apache License 2.0 5 votes vote down vote up
public void queries04(SqlClient client) {
  client
    .preparedQuery("INSERT INTO users (first_name, last_name) VALUES ($1, $2)")
    .execute(Tuple.of("Julien", "Viet"),  ar -> {
    if (ar.succeeded()) {
      RowSet<Row> rows = ar.result();
      System.out.println(rows.rowCount());
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}
 
Example 22
Source Project: vertx-sql-client   Source File: SqlClientExamples.java    License: Apache License 2.0 5 votes vote down vote up
public void queries08(SqlClient client) {

    // Add commands to the batch
    List<Tuple> batch = new ArrayList<>();
    batch.add(Tuple.of("julien", "Julien Viet"));
    batch.add(Tuple.of("emad", "Emad Alblueshi"));

    // Execute the prepared batch
    client
      .preparedQuery("INSERT INTO USERS (id, name) VALUES ($1, $2)")
      .executeBatch(batch, res -> {
      if (res.succeeded()) {

        // Process rows
        RowSet<Row> rows = res.result();
      } else {
        System.out.println("Batch failed " + res.cause());
      }
    });
  }
 
Example 23
Source Project: vertx-sql-client   Source File: SqlClientExamples.java    License: Apache License 2.0 5 votes vote down vote up
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());
        }
      });
  }
 
Example 24
Source Project: vertx-sql-client   Source File: SqlClientExamples.java    License: Apache License 2.0 5 votes vote down vote up
public void usingConnections02(SqlConnection connection) {
  connection.prepare("SELECT * FROM users WHERE first_name LIKE $1", ar1 -> {
    if (ar1.succeeded()) {
      PreparedStatement pq = ar1.result();
      pq.query().execute(Tuple.of("julien"), ar2 -> {
        if (ar2.succeeded()) {
          // All rows
          RowSet<Row> rows = ar2.result();
        }
      });
    }
  });
}
 
Example 25
Source Project: vertx-sql-client   Source File: PgClientExamples.java    License: Apache License 2.0 5 votes vote down vote up
public void typeMapping01(Pool pool) {
  pool
    .query("SELECT 1::BIGINT \"VAL\"")
    .execute(ar -> {
    RowSet<Row> rowSet = ar.result();
    Row row = rowSet.iterator().next();

    // Stored as java.lang.Long
    Object value = row.getValue(0);

    // Convert to java.lang.Integer
    Integer intValue = row.getInteger(0);
  });
}
 
Example 26
Source Project: vertx-sql-client   Source File: PgClientExamples.java    License: Apache License 2.0 5 votes vote down vote up
public void customType01Example(SqlClient client) {
  client
    .preparedQuery("SELECT address, (address).city FROM address_book WHERE id=$1")
    .execute(Tuple.of(3),  ar -> {
    if (ar.succeeded()) {
      RowSet<Row> rows = ar.result();
      for (Row row : rows) {
        System.out.println("Full Address " + row.getString(0) + ", City " + row.getString(1));
      }
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}
 
Example 27
Source Project: vertx-sql-client   Source File: PgClientExamples.java    License: Apache License 2.0 5 votes vote down vote up
public void tsQuery01Example(SqlClient client) {
  client
    .preparedQuery("SELECT to_tsvector( $1 ) @@ to_tsquery( $2 )")
    .execute(Tuple.of("fat cats ate fat rats", "fat & rat"),  ar -> {
    if (ar.succeeded()) {
      RowSet<Row> rows = ar.result();
      for (Row row : rows) {
        System.out.println("Match : " + row.getBoolean(0));
      }
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}
 
Example 28
Source Project: raml-module-builder   Source File: PostgresClient.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Delete by CQL wrapper.
 * @param connection where to run, can be within a transaction
 * @param table table name without schema
 * @param cql which records to delete
 */
public void delete(AsyncResult<SQLConnection> connection, String table, CQLWrapper cql,
    Handler<AsyncResult<RowSet<Row>>> replyHandler) {
  try {
    String where = cql == null ? "" : cql.toString();
    doDelete(connection, table, where, replyHandler);
  } catch (Exception e) {
    replyHandler.handle(Future.failedFuture(e));
  }
}
 
Example 29
Source Project: vertx-sql-client   Source File: SqlClientExamples.java    License: Apache License 2.0 5 votes vote down vote up
public void queries02(SqlClient client) {
  client
    .preparedQuery("SELECT * FROM users WHERE id=$1")
    .execute(Tuple.of("andy"), ar -> {
    if (ar.succeeded()) {
      RowSet<Row> rows = ar.result();
      System.out.println("Got " + rows.size() + " rows ");
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}
 
Example 30
Source Project: vertx-sql-client   Source File: SqlClientExamples.java    License: Apache License 2.0 5 votes vote down vote up
public void queries03(SqlClient client) {
  client
    .preparedQuery("SELECT first_name, last_name FROM users")
    .execute(ar -> {
    if (ar.succeeded()) {
      RowSet<Row> rows = ar.result();
      for (Row row : rows) {
        System.out.println("User " + row.getString(0) + " " + row.getString(1));
      }
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}