io.vertx.sqlclient.SqlConnection Java Examples

public void queries10(SqlConnection sqlConnection) {
  sqlConnection
    .prepare("SELECT * FROM users WHERE id= ?", 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 usingConnections01(Vertx vertx, Pool pool) {

    pool.getConnection(ar1 -> {
      if (ar1.succeeded()) {
        SqlConnection connection = ar1.result();

        connection
          .query("SELECT * FROM users WHERE id='julien'")
          .execute(ar2 -> {
          if (ar1.succeeded()) {
            connection
              .query("SELECT * FROM users WHERE id='paulo'")
              .execute(ar3 -> {
              // Do something with rows and return the connection to the pool
              connection.close();
            });
          } else {
            // Return the connection to the pool
            connection.close();
          }
        });
      }
    });
  }
 

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

@Override
public Future<SqlConnection> getConnection() {
  ContextInternal current = vertx.getOrCreateContext();
  Object metric;
  if (metrics != null) {
    metric = metrics.enqueueRequest();
  } else {
    metric = null;
  }
  Promise<Connection> promise = current.promise();
  acquire(promise);
  if (metrics != null) {
    promise.future().onComplete(ar -> {
      metrics.dequeueRequest(metric);
    });
  }
  return promise.future().map(conn -> {
    SqlConnectionImpl wrapper = wrap(current, conn);
    conn.init(wrapper);
    return wrapper;
  });
}
 

@Override
protected void initConnector() {
  String uri = "db2://"+rule.options().getUser()+":"
                       +rule.options().getPassword()+"@"
                       +rule.options().getHost()+":"
                       +rule.options().getPort()+"/"
                       +rule.options().getDatabase();

  connector = new Connector<SqlConnection>() {
    @Override
    public void connect(Handler<AsyncResult<SqlConnection>> handler) {
      DB2Connection.connect(vertx, uri, ar -> {
        if (ar.succeeded()) {
          handler.handle(Future.succeededFuture(ar.result()));
        } else {
          handler.handle(Future.failedFuture(ar.cause()));
        }
      });
    }

    @Override
    public void close() {
    }
  };
}
 

public void usingConnections03(SqlConnection connection) {
  connection.prepare("INSERT INTO USERS (id, name) VALUES ($1, $2)", 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());
        }
      });
    }
  });
}
 

protected void initConnector() {
  connector = handler -> {
    Pool pool = getPool();
    pool.getConnection(ar1 -> {
      if (ar1.succeeded()) {
        SqlConnection conn = ar1.result();
        conn.begin(ar2 -> {
          if (ar2.succeeded()) {
            Transaction tx = ar2.result();
            tx.completion().onComplete(ar3 -> {
              conn.close();
            });
            handler.handle(Future.succeededFuture(new Result(conn, tx)));
          } else {
            conn.close();
          }
        });
      } else {
        handler.handle(ar1.mapEmpty());
      }
    });
  };
}
 

public void queries10(SqlConnection sqlConnection) {
  sqlConnection
    .prepare("SELECT * FROM users WHERE id = ?", 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 usingConnections03(SqlConnection connection) {
  connection.prepare("INSERT INTO USERS (id, name) VALUES ($1, $2)", 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"));
      batch.add(Tuple.of("andy", "Andy Guibert"));

      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 usingConnections01(Vertx vertx, Pool pool) {

    pool.getConnection(ar1 -> {
      if (ar1.succeeded()) {
        SqlConnection connection = ar1.result();

        connection
          .query("SELECT * FROM users WHERE id='julien'")
          .execute(ar2 -> {
          if (ar1.succeeded()) {
            connection
              .query("SELECT * FROM users WHERE id='paulo'")
              .execute(ar3 -> {
              // Do something with rows and return the connection to the pool
              connection.close();
            });
          } else {
            // Return the connection to the pool
            connection.close();
          }
        });
      }
    });
  }
 

public void usingConnections01(Vertx vertx, Pool pool) {

    pool.getConnection(ar1 -> {
      if (ar1.succeeded()) {
        SqlConnection connection = ar1.result();

        connection
          .query("SELECT * FROM users WHERE id='andy'")
          .execute(ar2 -> {
          if (ar1.succeeded()) {
            connection
              .query("SELECT * FROM users WHERE id='julien'")
              .execute(ar3 -> {
              // Do something with rows and return the connection to the pool
              connection.close();
            });
          } else {
            // Return the connection to the pool
            connection.close();
          }
        });
      }
    });
  }
 

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 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 usingConnections03(SqlConnection connection) {
  connection.prepare("INSERT INTO USERS (id, name) VALUES ($1, $2)", 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 usingCursors03(SqlConnection connection) {
  connection.prepare("SELECT * FROM users WHERE age > ?", ar1 -> {
    if (ar1.succeeded()) {
      PreparedStatement pq = ar1.result();

      // Fetch 50 rows at a time
      RowStream<Row> stream = pq.createStream(50, Tuple.of(18));

      // Use the stream
      stream.exceptionHandler(err -> {
        System.out.println("Error: " + err.getMessage());
      });
      stream.endHandler(v -> {
        System.out.println("End of stream");
      });
      stream.handler(row -> {
        System.out.println("User: " + row.getString("last_name"));
      });
    }
  });
}
 

public void usingConnections01(Vertx vertx, Pool pool) {

    pool.getConnection(ar1 -> {
      if (ar1.succeeded()) {
        SqlConnection connection = ar1.result();

        connection
          .query("SELECT * FROM users WHERE id='julien'")
          .execute(ar2 -> {
          if (ar1.succeeded()) {
            connection
              .query("SELECT * FROM users WHERE id='paulo'")
              .execute(ar3 -> {
              // Do something with rows and return the connection to the pool
              connection.close();
            });
          } else {
            // Return the connection to the pool
            connection.close();
          }
        });
      }
    });
  }
 

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 usingCursors03(SqlConnection connection) {
  connection.prepare("SELECT * FROM users WHERE first_name LIKE $1", ar0 -> {
    if (ar0.succeeded()) {
      PreparedStatement pq = ar0.result();

      // Streams require to run within a transaction
      connection.begin(ar1 -> {
        if (ar1.succeeded()) {
          Transaction tx = ar1.result();

          // Fetch 50 rows at a time
          RowStream<Row> stream = pq.createStream(50, Tuple.of("julien"));

          // Use the stream
          stream.exceptionHandler(err -> {
            System.out.println("Error: " + err.getMessage());
          });
          stream.endHandler(v -> {
            tx.commit();
            System.out.println("End of stream");
          });
          stream.handler(row -> {
            System.out.println("User: " + row.getString("last_name"));
          });
        }
      });
    }
  });
}
 

public void usingCursors01(SqlConnection connection) {
  connection.prepare("SELECT * FROM users WHERE first_name LIKE $1", ar0 -> {
    if (ar0.succeeded()) {
      PreparedStatement pq = ar0.result();

      // Cursors require to run within a transaction
      connection.begin(ar1 -> {
        if (ar1.succeeded()) {
          Transaction tx = ar1.result();

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

          // 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 - commit the transaction
                tx.commit();
              }
            }
          });
        }
      });
    }
  });
}
 

public void usingCursors03(SqlConnection connection) {
  connection.prepare("SELECT * FROM users WHERE first_name LIKE $1", ar0 -> {
    if (ar0.succeeded()) {
      PreparedStatement pq = ar0.result();

      // Streams require to run within a transaction
      connection.begin(ar1 -> {
        if (ar1.succeeded()) {
          Transaction tx = ar1.result();

          // Fetch 50 rows at a time
          RowStream<Row> stream = pq.createStream(50, Tuple.of("julien"));

          // Use the stream
          stream.exceptionHandler(err -> {
            System.out.println("Error: " + err.getMessage());
          });
          stream.endHandler(v -> {
            tx.commit();
            System.out.println("End of stream");
          });
          stream.handler(row -> {
            System.out.println("User: " + row.getString("last_name"));
          });
        }
      });
    }
  });
}
 

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("andy"), ar2 -> {
        if (ar2.succeeded()) {
          // All rows
          RowSet<Row> rows = ar2.result();
        }
      });
    }
  });
}
 

public void usingCursors03(SqlConnection connection) {
  connection.prepare("SELECT * FROM users WHERE first_name LIKE $1", ar0 -> {
    if (ar0.succeeded()) {
      PreparedStatement pq = ar0.result();

      // Streams require to run within a transaction
      connection.begin(ar1 -> {
        if (ar1.succeeded()) {
          Transaction tx = ar1.result();

          // Fetch 50 rows at a time
          RowStream<Row> stream = pq.createStream(50, Tuple.of("julien"));

          // Use the stream
          stream.exceptionHandler(err -> {
            System.out.println("Error: " + err.getMessage());
          });
          stream.endHandler(v -> {
            tx.commit();
            System.out.println("End of stream");
          });
          stream.handler(row -> {
            System.out.println("User: " + row.getString("last_name"));
          });
        }
      });
    }
  });
}
 

SqlClientConnection(SqlConnection connection,
					boolean showSQL, boolean formatSQL,
					boolean usePostgresStyleParameters) {
	this.showSQL = showSQL;
	this.connection = connection;
	this.formatSQL = formatSQL;
	this.usePostgresStyleParameters = usePostgresStyleParameters;
}
 

@Override
public void getConnection(Handler<AsyncResult<SqlConnection>> handler) {
  Future<SqlConnection> fut = getConnection();
  if (handler != null) {
    fut.onComplete(handler);
  }
}
 

@Test
public void testPrepareAndQuery(TestContext ctx) {
  Function<SqlConnection, Future<?>> fn = conn -> conn
    .prepare("SELECT * FROM immutable WHERE id=1")
    .compose(ps -> ps.query().execute());
  testMetrics(ctx, false, fn);
}
 

@Test
public void testPrepareAndBatchQuery(TestContext ctx) {
  Function<SqlConnection, Future<?>> fn = conn -> conn
    .prepare("SELECT * FROM immutable WHERE id=1")
    .compose(ps -> ps.query().executeBatch(Collections.singletonList(Tuple.tuple())));
  testMetrics(ctx, false, fn);
}
 

public void usingConnections02(SqlConnection connection) {
  connection.prepare("SELECT * FROM users WHERE first_name LIKE ?", ar1 -> {
    if (ar1.succeeded()) {
      PreparedStatement prepared = ar1.result();
      prepared.query().execute(Tuple.of("julien"), ar2 -> {
        if (ar2.succeeded()) {
          // All rows
          RowSet<Row> rows = ar2.result();
        }
      });
    }
  });
}
 

@Override
public void getConnection(Handler<AsyncResult<SqlConnection>> con) {
  if ("0".equals(conf.getString("postgres_port"))) {
    con.handle(Future.failedFuture("getConnection failed"));
    return;
  }
  con.handle(Future.succeededFuture(new FakeSqlConnection()));
}
 

private PreparedQuery selectableQuery() {
	
	if (null!=selectQuery || building) {
		return selectQuery;		
	} else {
		building = true;
		pm.pool().getConnection(h -> {
			
			if (h.succeeded()) {
				SqlConnection connection = h.result();
				
				connection.prepare("SELECT * FROM world WHERE id=$1", ph -> {
					if (ph.succeeded()) {							
						selectQuery = ph.result();	
						
						building = false;
						if (updateQuery==null) {
							updateQuery();
						}
						
					} else {							
						ph.cause().printStackTrace();
					}
				});
				
				connection.close();
			} else {
				h.cause().printStackTrace();
			}
		});
		return null;
	}
}
 

private PreparedQuery updateQuery() {
	
	if (null!=updateQuery || building) {
		return updateQuery;		
	} else {
		building = true;
		pm.pool().getConnection(h -> {
			
			if (h.succeeded()) {
				SqlConnection connection = h.result();
				
				connection.prepare("UPDATE world SET randomnumber=$1 WHERE id=$2", ph -> {
					if (ph.succeeded()) {							
						updateQuery = ph.result();	
						building = false;
						if (selectQuery == null) {
							selectableQuery();
						}
					} 
				});
				
				connection.close();
			} 
			
			
		});
		return null;
	}
}