You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by am...@apache.org on 2022/06/06 15:11:02 UTC
[ignite-3] 01/01: Add example of using SQL API.
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 <an...@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);
+ }
+}