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());
      }
    });
}
 

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();
          }
        }
      });
    }
  });
}
 

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"));
        }
      }
  });
}
 

@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 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());
    }
  });
}
 

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));
  }
}
 

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()));
  }
}
 

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());
    }
  });
}
 

@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 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 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 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();
        }));
      }));
    }));
  }));
}
 

/**
 * 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;
}
 

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 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 )
	);
}
 

@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);
}
 

/**
 * 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));
  }
}
 

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());
    }
  });
}
 

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());
    }
  });
}
 

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());
    }
  });
}
 

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());
      }
    });
  }
 

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 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();
        }
      });
    }
  });
}
 

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);
  });
}
 

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());
    }
  });
}
 

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());
    }
  });
}
 

/**
 * 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));
  }
}
 

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());
    }
  });
}
 

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());
    }
  });
}