This is an automated email from the ASF dual-hosted git repository. amashenkov pushed a commit to branch ignite-17088 in repository https://gitbox.apache.org/repos/asf/ignite-3.git
commit a85805b67ec816bb02492e82eb5d88e0cad61411 Author: Andrew Mashenkov <andrey.mashen...@gmail.com> AuthorDate: Mon Jun 6 18:10:53 2022 +0300 Add example of using SQL API. --- .../example/sql/{jdbc => }/ItSqlExamplesTest.java | 33 +++- .../apache/ignite/example/sql/SqlApiExample.java | 210 +++++++++++++++++++++ 2 files changed, 242 insertions(+), 1 deletion(-) diff --git a/examples/src/integrationTest/java/org/apache/ignite/example/sql/jdbc/ItSqlExamplesTest.java b/examples/src/integrationTest/java/org/apache/ignite/example/sql/ItSqlExamplesTest.java similarity index 62% rename from examples/src/integrationTest/java/org/apache/ignite/example/sql/jdbc/ItSqlExamplesTest.java rename to examples/src/integrationTest/java/org/apache/ignite/example/sql/ItSqlExamplesTest.java index 854051357..9d7662596 100644 --- a/examples/src/integrationTest/java/org/apache/ignite/example/sql/jdbc/ItSqlExamplesTest.java +++ b/examples/src/integrationTest/java/org/apache/ignite/example/sql/ItSqlExamplesTest.java @@ -15,11 +15,13 @@ * limitations under the License. */ -package org.apache.ignite.example.sql.jdbc; +package org.apache.ignite.example.sql; import static org.apache.ignite.example.ExampleTestUtils.assertConsoleOutputContains; import org.apache.ignite.example.AbstractExamplesTest; +import org.apache.ignite.example.sql.jdbc.SqlJdbcExample; +import org.junit.jupiter.api.Disabled; import org.junit.jupiter.api.Test; /** @@ -50,4 +52,33 @@ public class ItSqlExamplesTest extends AbstractExamplesTest { + " Richard, Miles, St. Petersburg\n" ); } + + /** + * Runs SqlApiExample and checks its output. + * + * @throws Exception If failed. + */ + @Disabled("https://issues.apache.org/jira/browse/IGNITE-17057") + @Test + public void testSqlApiExample() throws Exception { + assertConsoleOutputContains(SqlApiExample::main, EMPTY_ARGS, + "\nAdded cities: 3", + "\nAdded accounts: 4", + + "\nAll accounts:\n" + + " John, Doe, Forest Hill\n" + + " Jane, Roe, Forest Hill\n" + + " Mary, Major, Denver\n" + + " Richard, Miles, St. Petersburg\n", + + "\nAccounts with balance lower than 1,500:\n" + + " John, Doe, 1000.0\n" + + " Richard, Miles, 1450.0\n", + + "\nAll accounts:\n" + + " Jane, Roe, Forest Hill\n" + + " Mary, Major, Denver\n" + + " Richard, Miles, St. Petersburg\n" + ); + } } diff --git a/examples/src/main/java/org/apache/ignite/example/sql/SqlApiExample.java b/examples/src/main/java/org/apache/ignite/example/sql/SqlApiExample.java new file mode 100644 index 000000000..1c582932f --- /dev/null +++ b/examples/src/main/java/org/apache/ignite/example/sql/SqlApiExample.java @@ -0,0 +1,210 @@ +package org.apache.ignite.example.sql; + +import java.util.Arrays; +import java.util.concurrent.CompletableFuture; +import java.util.concurrent.CompletionStage; +import org.apache.ignite.client.IgniteClient; +import org.apache.ignite.sql.BatchedArguments; +import org.apache.ignite.sql.ResultSet; +import org.apache.ignite.sql.Session; +import org.apache.ignite.sql.SqlRow; +import org.apache.ignite.sql.Statement; +import org.apache.ignite.sql.async.AsyncResultSet; + +public class SqlApiExample { + /** + * Main method of the example. + * + * @param args The command line arguments. + * @throws Exception If failed. + */ + public static void main(String[] args) throws Exception { + //-------------------------------------------------------------------------------------- + // + // Creating a client to connect to the cluster. + // + //-------------------------------------------------------------------------------------- + + System.out.println("\nConnecting to server..."); + + try (IgniteClient client = IgniteClient.builder() + .addresses("127.0.0.1:10800") + .build() + ) { + //-------------------------------------------------------------------------------------- + // + // Creating tables. + // + //-------------------------------------------------------------------------------------- + + try (Session ses = client.sql().createSession()) { + ses.execute( + null, + "CREATE TABLE CITIES (" + + "ID INT PRIMARY KEY," + + "NAME VARCHAR)" + ).close(); // Ignore result. + + ses.execute( + null, + "CREATE TABLE ACCOUNTS (" + + " ACCOUNT_ID INT PRIMARY KEY," + + " CITY_ID INT," + + " FIRST_NAME VARCHAR," + + " LAST_NAME VARCHAR," + + " BALANCE DOUBLE)" + ).close(); + + //-------------------------------------------------------------------------------------- + // + // Populating 'CITIES' table. + // + //-------------------------------------------------------------------------------------- + + System.out.println("\nPopulating 'CITIES' table..."); + + try (Statement stmt = client.sql().createStatement("INSERT INTO CITIES (ID, NAME) VALUES (?, ?)")) { + long rowsAdded = 0; + + try (ResultSet rs = ses.execute(null, stmt, 1, "Forest Hill")) { + rowsAdded += rs.affectedRows(); + } + try (ResultSet rs = ses.execute(null, stmt, 2, "Denver")) { + rowsAdded += rs.affectedRows(); + } + try (ResultSet rs = ses.execute(null, stmt, 3, "St. Petersburg")) { + rowsAdded += rs.affectedRows(); + } + + System.out.println("\nAdded cities: " + rowsAdded); + } + + //-------------------------------------------------------------------------------------- + // + // Populating 'ACCOUNTS' table. + // + //-------------------------------------------------------------------------------------- + + System.out.println("\nPopulating 'ACCOUNTS' table..."); + + long rowsAdded = Arrays.stream(ses.executeBatch(null, + "INSERT INTO ACCOUNTS (ACCOUNT_ID, CITY_ID, FIRST_NAME, LAST_NAME, BALANCE) values (?, ?, ?, ?, ?)", + BatchedArguments.of(1, 1, "John", "Doe", 1000.0d) + .add(2, 1, "Jane", "Roe", 2000.0d) + .add(3, 1, "Mary", "Major", 1500.0d) + .add(4, 1, "Richard", "Miles", 1450.0d))) + .asLongStream().sum(); + + System.out.println("\nAdded accounts: " + rowsAdded); + + //-------------------------------------------------------------------------------------- + // + // Requesting information about all account owners. + // + //-------------------------------------------------------------------------------------- + + System.out.println("\nAll accounts:"); + + try (ResultSet rs = ses.execute(null, + "SELECT a.FIRST_NAME, a.LAST_NAME, c.NAME FROM ACCOUNTS a " + + "INNER JOIN CITIES c on c.ID = a.CITY_ID ORDER BY a.ACCOUNT_ID")) { + while (rs.hasNext()) { + SqlRow row = rs.next(); + + System.out.println(" " + + row.stringValue(1) + ", " + + row.stringValue(2) + ", " + + row.stringValue(3)); + } + } + + //-------------------------------------------------------------------------------------- + // + // Requesting accounts with balances lower than 1,500. + // + //-------------------------------------------------------------------------------------- + + System.out.println("\nAccounts with balance lower than 1,500:"); + + try (ResultSet rs = ses.execute(null, + "SELECT a.FIRST_NAME, a.LAST_NAME, a.BALANCE FROM ACCOUNTS a WHERE a.BALANCE < 1500.0 " + + "ORDER BY a.ACCOUNT_ID")) { + while (rs.hasNext()) { + SqlRow row = rs.next(); + + System.out.println(" " + + row.stringValue(1) + ", " + + row.stringValue(2) + ", " + + row.stringValue(3)); + } + } + + //-------------------------------------------------------------------------------------- + // + // Deleting one of the accounts. + // + //-------------------------------------------------------------------------------------- + + System.out.println("\nDeleting one of the accounts..."); + + try (ResultSet ignored = ses.execute(null, "DELETE FROM ACCOUNTS WHERE ACCOUNT_ID = ?", 1)) { + } + + //-------------------------------------------------------------------------------------- + // + // Requesting information about all account owners once again + // to verify that the account was actually deleted. + // + //-------------------------------------------------------------------------------------- + + System.out.println("\nAll accounts:"); + + // Async way. + Statement stmt = client.sql().statementBuilder() + .query("SELECT a.FIRST_NAME, a.LAST_NAME, c.NAME FROM ACCOUNTS a INNER JOIN CITIES c on c.ID = a.CITY_ID ORDER BY a.ACCOUNT_ID") + .pageSize(1) + .build(); + + ses.executeAsync(null, stmt) + .thenCompose(SqlApiExample::fetchAllRowsInto) + .get(); + + stmt.close(); + + System.out.println("\nDropping the tables..."); + + ses.execute(null, "DROP TABLE ACCOUNTS").close(); + ses.execute(null, "DROP TABLE CITIES").close(); + } + } + } + + /** + * Fetch full result set asynchronously. + * @param resultSet Async result set. + * @return Operation future. + */ + private static CompletionStage<Void> fetchAllRowsInto(AsyncResultSet resultSet) { + // + // Process current page. + // + for (var row : resultSet.currentPage()) { + System.out.println(" " + + row.stringValue(1) + ", " + + row.stringValue(2) + ", " + + row.stringValue(3)); + } + + // + // Finish if no more data. + // + if (!resultSet.hasMorePages()) { + return CompletableFuture.completedFuture(null); + } + + // + // Request for the next page in async way, then subscribe to the response. + // + return resultSet.fetchNextPage().thenCompose(SqlApiExample::fetchAllRowsInto); + } +}