IGNITE-7318: Move all SQL examples out of "data grid" package
Project: http://git-wip-us.apache.org/repos/asf/ignite/repo Commit: http://git-wip-us.apache.org/repos/asf/ignite/commit/4b7a2c78 Tree: http://git-wip-us.apache.org/repos/asf/ignite/tree/4b7a2c78 Diff: http://git-wip-us.apache.org/repos/asf/ignite/diff/4b7a2c78 Branch: refs/heads/ignite-zk Commit: 4b7a2c78764fb769cfbc03e549e210de7a0cb0f9 Parents: 6c3430b Author: Denis Magda <[email protected]> Authored: Tue Dec 26 09:43:14 2017 -0800 Committer: Denis Magda <[email protected]> Committed: Tue Dec 26 09:43:14 2017 -0800 ---------------------------------------------------------------------- .../examples/datagrid/CacheQueryDdlExample.java | 118 ------- .../examples/datagrid/CacheQueryDmlExample.java | 163 --------- .../examples/datagrid/CacheQueryExample.java | 179 +--------- .../ignite/examples/datagrid/JdbcExample.java | 135 -------- .../ignite/examples/sql/SqlDDLExample.java | 118 +++++++ .../ignite/examples/sql/SqlDmlExample.java | 163 +++++++++ .../ignite/examples/sql/SqlJdbcExample.java | 135 ++++++++ .../ignite/examples/sql/SqlQueriesExample.java | 346 +++++++++++++++++++ .../ignite/examples/sql/package-info.java | 22 ++ .../CacheExamplesMultiNodeSelfTest.java | 4 +- .../ignite/examples/CacheExamplesSelfTest.java | 8 +- .../ignite/examples/SqlExamplesSelfTest.java | 49 +++ .../testsuites/IgniteExamplesSelfTestSuite.java | 2 + 13 files changed, 847 insertions(+), 595 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/ignite/blob/4b7a2c78/examples/src/main/java/org/apache/ignite/examples/datagrid/CacheQueryDdlExample.java ---------------------------------------------------------------------- diff --git a/examples/src/main/java/org/apache/ignite/examples/datagrid/CacheQueryDdlExample.java b/examples/src/main/java/org/apache/ignite/examples/datagrid/CacheQueryDdlExample.java deleted file mode 100644 index 7ac2751..0000000 --- a/examples/src/main/java/org/apache/ignite/examples/datagrid/CacheQueryDdlExample.java +++ /dev/null @@ -1,118 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one or more - * contributor license agreements. See the NOTICE file distributed with - * this work for additional information regarding copyright ownership. - * The ASF licenses this file to You under the Apache License, Version 2.0 - * (the "License"); you may not use this file except in compliance with - * the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, software - * distributed under the License is distributed on an "AS IS" BASIS, - * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - * See the License for the specific language governing permissions and - * limitations under the License. - */ - -package org.apache.ignite.examples.datagrid; - -import java.util.List; -import org.apache.ignite.Ignite; -import org.apache.ignite.IgniteCache; -import org.apache.ignite.Ignition; -import org.apache.ignite.cache.query.SqlFieldsQuery; -import org.apache.ignite.configuration.CacheConfiguration; -import org.apache.ignite.examples.ExampleNodeStartup; - -/** - * Example to showcase DDL capabilities of Ignite's SQL engine. - * <p> - * Remote nodes could be started from command line as follows: - * {@code 'ignite.{sh|bat} examples/config/example-ignite.xml'}. - * <p> - * Alternatively you can run {@link ExampleNodeStartup} in either same or another JVM. - */ -public class CacheQueryDdlExample { - /** Dummy cache name. */ - private static final String DUMMY_CACHE_NAME = "dummy_cache"; - - /** - * Executes example. - * - * @param args Command line arguments, none required. - * @throws Exception If example execution failed. - */ - @SuppressWarnings({"unused", "ThrowFromFinallyBlock"}) - public static void main(String[] args) throws Exception { - try (Ignite ignite = Ignition.start("examples/config/example-ignite.xml")) { - print("Cache query DDL example started."); - - // Create dummy cache to act as an entry point for SQL queries (new SQL API which do not require this - // will appear in future versions, JDBC and ODBC drivers do not require it already). - CacheConfiguration<?, ?> cacheCfg = new CacheConfiguration<>(DUMMY_CACHE_NAME).setSqlSchema("PUBLIC"); - - try ( - IgniteCache<?, ?> cache = ignite.getOrCreateCache(cacheCfg) - ) { - // Create reference City table based on REPLICATED template. - cache.query(new SqlFieldsQuery( - "CREATE TABLE city (id LONG PRIMARY KEY, name VARCHAR) WITH \"template=replicated\"")).getAll(); - - // Create table based on PARTITIONED template with one backup. - cache.query(new SqlFieldsQuery( - "CREATE TABLE person (id LONG, name VARCHAR, city_id LONG, PRIMARY KEY (id, city_id)) " + - "WITH \"backups=1, affinity_key=city_id\"")).getAll(); - - // Create an index. - cache.query(new SqlFieldsQuery("CREATE INDEX on Person (city_id)")).getAll(); - - print("Created database objects."); - - SqlFieldsQuery qry = new SqlFieldsQuery("INSERT INTO city (id, name) VALUES (?, ?)"); - - cache.query(qry.setArgs(1L, "Forest Hill")).getAll(); - cache.query(qry.setArgs(2L, "Denver")).getAll(); - cache.query(qry.setArgs(3L, "St. Petersburg")).getAll(); - - qry = new SqlFieldsQuery("INSERT INTO person (id, name, city_id) values (?, ?, ?)"); - - cache.query(qry.setArgs(1L, "John Doe", 3L)).getAll(); - cache.query(qry.setArgs(2L, "Jane Roe", 2L)).getAll(); - cache.query(qry.setArgs(3L, "Mary Major", 1L)).getAll(); - cache.query(qry.setArgs(4L, "Richard Miles", 2L)).getAll(); - - print("Populated data."); - - List<List<?>> res = cache.query(new SqlFieldsQuery( - "SELECT p.name, c.name FROM Person p INNER JOIN City c on c.id = p.city_id")).getAll(); - - print("Query results:"); - - for (Object next : res) - System.out.println(">>> " + next); - - cache.query(new SqlFieldsQuery("drop table Person")).getAll(); - cache.query(new SqlFieldsQuery("drop table City")).getAll(); - - print("Dropped database objects."); - } - finally { - // Distributed cache can be removed from cluster only by #destroyCache() call. - ignite.destroyCache(DUMMY_CACHE_NAME); - } - - print("Cache query DDL example finished."); - } - } - - /** - * Prints message. - * - * @param msg Message to print before all objects are printed. - */ - private static void print(String msg) { - System.out.println(); - System.out.println(">>> " + msg); - } -} http://git-wip-us.apache.org/repos/asf/ignite/blob/4b7a2c78/examples/src/main/java/org/apache/ignite/examples/datagrid/CacheQueryDmlExample.java ---------------------------------------------------------------------- diff --git a/examples/src/main/java/org/apache/ignite/examples/datagrid/CacheQueryDmlExample.java b/examples/src/main/java/org/apache/ignite/examples/datagrid/CacheQueryDmlExample.java deleted file mode 100644 index 21027d0..0000000 --- a/examples/src/main/java/org/apache/ignite/examples/datagrid/CacheQueryDmlExample.java +++ /dev/null @@ -1,163 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one or more - * contributor license agreements. See the NOTICE file distributed with - * this work for additional information regarding copyright ownership. - * The ASF licenses this file to You under the Apache License, Version 2.0 - * (the "License"); you may not use this file except in compliance with - * the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, software - * distributed under the License is distributed on an "AS IS" BASIS, - * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - * See the License for the specific language governing permissions and - * limitations under the License. - */ - -package org.apache.ignite.examples.datagrid; - -import org.apache.ignite.Ignite; -import org.apache.ignite.IgniteCache; -import org.apache.ignite.Ignition; -import org.apache.ignite.cache.query.SqlFieldsQuery; -import org.apache.ignite.configuration.CacheConfiguration; -import org.apache.ignite.examples.model.Organization; -import org.apache.ignite.examples.model.Person; - -import java.util.List; - -/** - * Example to showcase DML capabilities of Ignite's SQL engine. - */ -public class CacheQueryDmlExample { - /** Organizations cache name. */ - private static final String ORG_CACHE = CacheQueryDmlExample.class.getSimpleName() + "Organizations"; - - /** Persons cache name. */ - private static final String PERSON_CACHE = CacheQueryDmlExample.class.getSimpleName() + "Persons"; - - /** - * Executes example. - * - * @param args Command line arguments, none required. - * @throws Exception If example execution failed. - */ - @SuppressWarnings({"unused", "ThrowFromFinallyBlock"}) - public static void main(String[] args) throws Exception { - try (Ignite ignite = Ignition.start("examples/config/example-ignite.xml")) { - print("Cache query DML example started."); - - CacheConfiguration<Long, Organization> orgCacheCfg = new CacheConfiguration<>(ORG_CACHE); - orgCacheCfg.setIndexedTypes(Long.class, Organization.class); - - CacheConfiguration<Long, Person> personCacheCfg = new CacheConfiguration<>(PERSON_CACHE); - personCacheCfg.setIndexedTypes(Long.class, Person.class); - - // Auto-close cache at the end of the example. - try ( - IgniteCache<Long, Organization> orgCache = ignite.getOrCreateCache(orgCacheCfg); - IgniteCache<Long, Person> personCache = ignite.getOrCreateCache(personCacheCfg) - ) { - insert(orgCache, personCache); - select(personCache, "Insert data"); - - update(personCache); - select(personCache, "Update salary for Master degrees"); - - delete(personCache); - select(personCache, "Delete non-Apache employees"); - } - finally { - // Distributed cache could be removed from cluster only by #destroyCache() call. - ignite.destroyCache(PERSON_CACHE); - ignite.destroyCache(ORG_CACHE); - } - - print("Cache query DML example finished."); - } - } - - /** - * Populate cache with test data. - * - * @param orgCache Organization cache, - * @param personCache Person cache. - */ - private static void insert(IgniteCache<Long, Organization> orgCache, IgniteCache<Long, Person> personCache) { - // Insert organizations. - SqlFieldsQuery qry = new SqlFieldsQuery("insert into Organization (_key, id, name) values (?, ?, ?)"); - - orgCache.query(qry.setArgs(1L, 1L, "ASF")); - orgCache.query(qry.setArgs(2L, 2L, "Eclipse")); - - // Insert persons. - qry = new SqlFieldsQuery( - "insert into Person (_key, id, orgId, firstName, lastName, salary, resume) values (?, ?, ?, ?, ?, ?, ?)"); - - personCache.query(qry.setArgs(1L, 1L, 1L, "John", "Doe", 4000, "Master")); - personCache.query(qry.setArgs(2L, 2L, 1L, "Jane", "Roe", 2000, "Bachelor")); - personCache.query(qry.setArgs(3L, 3L, 2L, "Mary", "Major", 5000, "Master")); - personCache.query(qry.setArgs(4L, 4L, 2L, "Richard", "Miles", 3000, "Bachelor")); - } - - /** - * Example of conditional UPDATE query: raise salary by 10% to everyone who has Master degree. - * - * @param personCache Person cache. - */ - private static void update(IgniteCache<Long, Person> personCache) { - String sql = - "update Person set salary = salary * 1.1 " + - "where resume = ?"; - - personCache.query(new SqlFieldsQuery(sql).setArgs("Master")); - } - - /** - * Example of conditional DELETE query: delete non-Apache employees. - * - * @param personCache Person cache. - */ - private static void delete(IgniteCache<Long, Person> personCache) { - String sql = - "delete from Person " + - "where id in (" + - "select p.id " + - "from Person p, \"" + ORG_CACHE + "\".Organization as o " + - "where o.name != ? and p.orgId = o.id" + - ")"; - - personCache.query(new SqlFieldsQuery(sql).setArgs("ASF")).getAll(); - } - - /** - * Query current data. - * - * @param personCache Person cache. - * @param msg Message. - */ - private static void select(IgniteCache<Long, Person> personCache, String msg) { - String sql = - "select p.id, concat(p.firstName, ' ', p.lastName), o.name, p.resume, p.salary " + - "from Person as p, \"" + ORG_CACHE + "\".Organization as o " + - "where p.orgId = o.id"; - - List<List<?>> res = personCache.query(new SqlFieldsQuery(sql).setDistributedJoins(true)).getAll(); - - print(msg); - - for (Object next : res) - System.out.println(">>> " + next); - } - - /** - * Prints message. - * - * @param msg Message to print before all objects are printed. - */ - private static void print(String msg) { - System.out.println(); - System.out.println(">>> " + msg); - } -} http://git-wip-us.apache.org/repos/asf/ignite/blob/4b7a2c78/examples/src/main/java/org/apache/ignite/examples/datagrid/CacheQueryExample.java ---------------------------------------------------------------------- diff --git a/examples/src/main/java/org/apache/ignite/examples/datagrid/CacheQueryExample.java b/examples/src/main/java/org/apache/ignite/examples/datagrid/CacheQueryExample.java index 4a6fc1b..7a1d25f 100644 --- a/examples/src/main/java/org/apache/ignite/examples/datagrid/CacheQueryExample.java +++ b/examples/src/main/java/org/apache/ignite/examples/datagrid/CacheQueryExample.java @@ -17,7 +17,6 @@ package org.apache.ignite.examples.datagrid; -import java.util.List; import javax.cache.Cache; import org.apache.ignite.Ignite; import org.apache.ignite.IgniteCache; @@ -37,7 +36,7 @@ import org.apache.ignite.examples.model.Person; import org.apache.ignite.lang.IgniteBiPredicate; /** - * Cache queries example. This example demonstrates SQL, TEXT, and FULL SCAN + * Cache queries example. This example demonstrates TEXT and FULL SCAN * queries over cache. * <p> * Example also demonstrates usage of fields queries that return only required @@ -69,9 +68,6 @@ public class CacheQueryExample { private static final String ORG_CACHE = CacheQueryExample.class.getSimpleName() + "Organizations"; /** Persons collocated with Organizations cache name. */ - private static final String COLLOCATED_PERSON_CACHE = CacheQueryExample.class.getSimpleName() + "CollocatedPersons"; - - /** Persons cache name. */ private static final String PERSON_CACHE = CacheQueryExample.class.getSimpleName() + "Persons"; /** @@ -90,21 +86,15 @@ public class CacheQueryExample { orgCacheCfg.setCacheMode(CacheMode.PARTITIONED); // Default. orgCacheCfg.setIndexedTypes(Long.class, Organization.class); - CacheConfiguration<AffinityKey<Long>, Person> colPersonCacheCfg = - new CacheConfiguration<>(COLLOCATED_PERSON_CACHE); - - colPersonCacheCfg.setCacheMode(CacheMode.PARTITIONED); // Default. - colPersonCacheCfg.setIndexedTypes(AffinityKey.class, Person.class); - - CacheConfiguration<Long, Person> personCacheCfg = new CacheConfiguration<>(PERSON_CACHE); + CacheConfiguration<AffinityKey<Long>, Person> personCacheCfg = + new CacheConfiguration<>(PERSON_CACHE); personCacheCfg.setCacheMode(CacheMode.PARTITIONED); // Default. - personCacheCfg.setIndexedTypes(Long.class, Person.class); + personCacheCfg.setIndexedTypes(AffinityKey.class, Person.class); try { // Create caches. ignite.getOrCreateCache(orgCacheCfg); - ignite.getOrCreateCache(colPersonCacheCfg); ignite.getOrCreateCache(personCacheCfg); // Populate caches. @@ -113,34 +103,11 @@ public class CacheQueryExample { // Example for SCAN-based query based on a predicate. scanQuery(); - // Example for SQL-based querying employees based on salary ranges. - sqlQuery(); - - // Example for SQL-based querying employees for a given organization - // (includes SQL join for collocated objects). - sqlQueryWithJoin(); - - // Example for SQL-based querying employees for a given organization - // (includes distributed SQL join). - sqlQueryWithDistributedJoin(); - // Example for TEXT-based querying for a given string in peoples resumes. textQuery(); - - // Example for SQL-based querying to calculate average salary - // among all employees within a company. - sqlQueryWithAggregation(); - - // Example for SQL-based fields queries that return only required - // fields instead of whole key-value pairs. - sqlFieldsQuery(); - - // Example for SQL-based fields queries that uses joins. - sqlFieldsQueryWithJoin(); } finally { // Distributed cache could be removed from cluster only by Ignite.destroyCache() call. - ignite.destroyCache(COLLOCATED_PERSON_CACHE); ignite.destroyCache(PERSON_CACHE); ignite.destroyCache(ORG_CACHE); } @@ -154,7 +121,7 @@ public class CacheQueryExample { */ private static void scanQuery() { IgniteCache<BinaryObject, BinaryObject> cache = Ignition.ignite() - .cache(COLLOCATED_PERSON_CACHE).withKeepBinary(); + .cache(PERSON_CACHE).withKeepBinary(); ScanQuery<BinaryObject, BinaryObject> scan = new ScanQuery<>( new IgniteBiPredicate<BinaryObject, BinaryObject>() { @@ -168,73 +135,6 @@ public class CacheQueryExample { print("People with salaries between 0 and 1000 (queried with SCAN query): ", cache.query(scan).getAll()); } - /** - * Example for SQL queries based on salary ranges. - */ - private static void sqlQuery() { - IgniteCache<Long, Person> cache = Ignition.ignite().cache(PERSON_CACHE); - - // SQL clause which selects salaries based on range. - String sql = "salary > ? and salary <= ?"; - - // Execute queries for salary ranges. - print("People with salaries between 0 and 1000 (queried with SQL query): ", - cache.query(new SqlQuery<AffinityKey<Long>, Person>(Person.class, sql). - setArgs(0, 1000)).getAll()); - - print("People with salaries between 1000 and 2000 (queried with SQL query): ", - cache.query(new SqlQuery<AffinityKey<Long>, Person>(Person.class, sql). - setArgs(1000, 2000)).getAll()); - } - - /** - * Example for SQL queries based on all employees working for a specific organization. - */ - private static void sqlQueryWithJoin() { - IgniteCache<AffinityKey<Long>, Person> cache = Ignition.ignite().cache(COLLOCATED_PERSON_CACHE); - - // SQL clause query which joins on 2 types to select people for a specific organization. - String joinSql = - "from Person, \"" + ORG_CACHE + "\".Organization as org " + - "where Person.orgId = org.id " + - "and lower(org.name) = lower(?)"; - - // Execute queries for find employees for different organizations. - print("Following people are 'ApacheIgnite' employees: ", - cache.query(new SqlQuery<AffinityKey<Long>, Person>(Person.class, joinSql). - setArgs("ApacheIgnite")).getAll()); - - print("Following people are 'Other' employees: ", - cache.query(new SqlQuery<AffinityKey<Long>, Person>(Person.class, joinSql). - setArgs("Other")).getAll()); - } - - /** - * Example for SQL queries based on all employees working - * for a specific organization (query uses distributed join). - */ - private static void sqlQueryWithDistributedJoin() { - IgniteCache<Long, Person> cache = Ignition.ignite().cache(PERSON_CACHE); - - // SQL clause query which joins on 2 types to select people for a specific organization. - String joinSql = - "from Person, \"" + ORG_CACHE + "\".Organization as org " + - "where Person.orgId = org.id " + - "and lower(org.name) = lower(?)"; - - SqlQuery qry = new SqlQuery<Long, Person>(Person.class, joinSql). - setArgs("ApacheIgnite"); - - // Enable distributed joins for query. - qry.setDistributedJoins(true); - - // Execute queries for find employees for different organizations. - print("Following people are 'ApacheIgnite' employees (distributed join): ", cache.query(qry).getAll()); - - qry.setArgs("Other"); - - print("Following people are 'Other' employees (distributed join): ", cache.query(qry).getAll()); - } /** * Example for TEXT queries using LUCENE-based indexing of people's resumes. @@ -254,65 +154,6 @@ public class CacheQueryExample { print("Following people have 'Bachelor Degree' in their resumes: ", bachelors.getAll()); } - /** - * Example for SQL queries to calculate average salary for a specific organization. - */ - private static void sqlQueryWithAggregation() { - IgniteCache<AffinityKey<Long>, Person> cache = Ignition.ignite().cache(COLLOCATED_PERSON_CACHE); - - // Calculate average of salary of all persons in ApacheIgnite. - // Note that we also join on Organization cache as well. - String sql = - "select avg(salary) " + - "from Person, \"" + ORG_CACHE + "\".Organization as org " + - "where Person.orgId = org.id " + - "and lower(org.name) = lower(?)"; - - QueryCursor<List<?>> cursor = cache.query(new SqlFieldsQuery(sql).setArgs("ApacheIgnite")); - - // Calculate average salary for a specific organization. - print("Average salary for 'ApacheIgnite' employees: ", cursor.getAll()); - } - - /** - * Example for SQL-based fields queries that return only required - * fields instead of whole key-value pairs. - */ - private static void sqlFieldsQuery() { - IgniteCache<Long, Person> cache = Ignition.ignite().cache(PERSON_CACHE); - - // Execute query to get names of all employees. - QueryCursor<List<?>> cursor = cache.query(new SqlFieldsQuery( - "select concat(firstName, ' ', lastName) from Person")); - - // In this particular case each row will have one element with full name of an employees. - List<List<?>> res = cursor.getAll(); - - // Print names. - print("Names of all employees:", res); - } - - /** - * Example for SQL-based fields queries that return only required - * fields instead of whole key-value pairs. - */ - private static void sqlFieldsQueryWithJoin() { - IgniteCache<AffinityKey<Long>, Person> cache = Ignition.ignite().cache(COLLOCATED_PERSON_CACHE); - - // Execute query to get names of all employees. - String sql = - "select concat(firstName, ' ', lastName), org.name " + - "from Person, \"" + ORG_CACHE + "\".Organization as org " + - "where Person.orgId = org.id"; - - QueryCursor<List<?>> cursor = cache.query(new SqlFieldsQuery(sql)); - - // In this particular case each row will have one element with full name of an employees. - List<List<?>> res = cursor.getAll(); - - // Print persons' names and organizations' names. - print("Names of all employees and organizations they belong to: ", res); - } /** * Populate cache with test data. @@ -330,12 +171,10 @@ public class CacheQueryExample { orgCache.put(org1.id(), org1); orgCache.put(org2.id(), org2); - IgniteCache<AffinityKey<Long>, Person> colPersonCache = Ignition.ignite().cache(COLLOCATED_PERSON_CACHE); - IgniteCache<Long, Person> personCache = Ignition.ignite().cache(PERSON_CACHE); + IgniteCache<AffinityKey<Long>, Person> colPersonCache = Ignition.ignite().cache(PERSON_CACHE); // Clear caches before running the example. colPersonCache.clear(); - personCache.clear(); // People. Person p1 = new Person(org1, "John", "Doe", 2000, "John Doe has Master Degree."); @@ -349,12 +188,6 @@ public class CacheQueryExample { colPersonCache.put(p2.key(), p2); colPersonCache.put(p3.key(), p3); colPersonCache.put(p4.key(), p4); - - // These Person objects are not collocated with their organizations. - personCache.put(p1.id, p1); - personCache.put(p2.id, p2); - personCache.put(p3.id, p3); - personCache.put(p4.id, p4); } /** http://git-wip-us.apache.org/repos/asf/ignite/blob/4b7a2c78/examples/src/main/java/org/apache/ignite/examples/datagrid/JdbcExample.java ---------------------------------------------------------------------- diff --git a/examples/src/main/java/org/apache/ignite/examples/datagrid/JdbcExample.java b/examples/src/main/java/org/apache/ignite/examples/datagrid/JdbcExample.java deleted file mode 100644 index 7a02a7e..0000000 --- a/examples/src/main/java/org/apache/ignite/examples/datagrid/JdbcExample.java +++ /dev/null @@ -1,135 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one or more - * contributor license agreements. See the NOTICE file distributed with - * this work for additional information regarding copyright ownership. - * The ASF licenses this file to You under the Apache License, Version 2.0 - * (the "License"); you may not use this file except in compliance with - * the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, software - * distributed under the License is distributed on an "AS IS" BASIS, - * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - * See the License for the specific language governing permissions and - * limitations under the License. - */ - -package org.apache.ignite.examples.datagrid; - -import java.sql.Connection; -import java.sql.DriverManager; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.Statement; -import org.apache.ignite.examples.ExampleNodeStartup; - -/** - * This example demonstrates usage of Ignite JDBC driver. - * <p> - * Ignite nodes must be started in separate process using {@link ExampleNodeStartup} before running this example. - */ -public class JdbcExample { - /** - * Executes example. - * - * @param args Command line arguments, none required. - * @throws Exception If example execution failed. - */ - public static void main(String[] args) throws Exception { - print("JDBC example started."); - - // Open JDBC connection - try (Connection conn = DriverManager.getConnection("jdbc:ignite:thin://127.0.0.1/")) { - print("Connected to server."); - - // Create database objects. - try (Statement stmt = conn.createStatement()) { - // Create reference City table based on REPLICATED template. - stmt.executeUpdate("CREATE TABLE city (id LONG PRIMARY KEY, name VARCHAR) " + - "WITH \"template=replicated\""); - - // Create table based on PARTITIONED template with one backup. - stmt.executeUpdate("CREATE TABLE person (id LONG, name VARCHAR, city_id LONG, " + - "PRIMARY KEY (id, city_id)) WITH \"backups=1, affinity_key=city_id\""); - - // Create an index. - stmt.executeUpdate("CREATE INDEX on Person (city_id)"); - } - - print("Created database objects."); - - // Populate City table with PreparedStatement. - try (PreparedStatement stmt = conn.prepareStatement("INSERT INTO city (id, name) VALUES (?, ?)")) { - stmt.setLong(1, 1L); - stmt.setString(2, "Forest Hill"); - stmt.executeUpdate(); - - stmt.setLong(1, 2L); - stmt.setString(2, "Denver"); - stmt.executeUpdate(); - - stmt.setLong(1, 3L); - stmt.setString(2, "St. Petersburg"); - stmt.executeUpdate(); - } - - // Populate Person table with PreparedStatement. - try (PreparedStatement stmt = - conn.prepareStatement("INSERT INTO person (id, name, city_id) values (?, ?, ?)")) { - stmt.setLong(1, 1L); - stmt.setString(2, "John Doe"); - stmt.setLong(3, 3L); - stmt.executeUpdate(); - - stmt.setLong(1, 2L); - stmt.setString(2, "Jane Roe"); - stmt.setLong(3, 2L); - stmt.executeUpdate(); - - stmt.setLong(1, 3L); - stmt.setString(2, "Mary Major"); - stmt.setLong(3, 1L); - stmt.executeUpdate(); - - stmt.setLong(1, 4L); - stmt.setString(2, "Richard Miles"); - stmt.setLong(3, 2L); - stmt.executeUpdate(); - } - - print("Populated data."); - - // Get data. - try (Statement stmt = conn.createStatement()) { - try (ResultSet rs = - stmt.executeQuery("SELECT p.name, c.name FROM Person p INNER JOIN City c on c.id = p.city_id")) { - print("Query results:"); - - while (rs.next()) - System.out.println(">>> " + rs.getString(1) + ", " + rs.getString(2)); - } - } - - // Drop database objects. - try (Statement stmt = conn.createStatement()) { - stmt.executeUpdate("DROP TABLE Person"); - stmt.executeUpdate("DROP TABLE City"); - } - - print("Dropped database objects."); - } - - print("JDBC example finished."); - } - - /** - * Prints message. - * - * @param msg Message to print before all objects are printed. - */ - private static void print(String msg) { - System.out.println(); - System.out.println(">>> " + msg); - } -} \ No newline at end of file http://git-wip-us.apache.org/repos/asf/ignite/blob/4b7a2c78/examples/src/main/java/org/apache/ignite/examples/sql/SqlDDLExample.java ---------------------------------------------------------------------- diff --git a/examples/src/main/java/org/apache/ignite/examples/sql/SqlDDLExample.java b/examples/src/main/java/org/apache/ignite/examples/sql/SqlDDLExample.java new file mode 100644 index 0000000..12a9ac8 --- /dev/null +++ b/examples/src/main/java/org/apache/ignite/examples/sql/SqlDDLExample.java @@ -0,0 +1,118 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.ignite.examples.sql; + +import java.util.List; +import org.apache.ignite.Ignite; +import org.apache.ignite.IgniteCache; +import org.apache.ignite.Ignition; +import org.apache.ignite.cache.query.SqlFieldsQuery; +import org.apache.ignite.configuration.CacheConfiguration; +import org.apache.ignite.examples.ExampleNodeStartup; + +/** + * Example to showcase DDL capabilities of Ignite's SQL engine. + * <p> + * Remote nodes could be started from command line as follows: + * {@code 'ignite.{sh|bat} examples/config/example-ignite.xml'}. + * <p> + * Alternatively you can run {@link ExampleNodeStartup} in either same or another JVM. + */ +public class SqlDdlExample { + /** Dummy cache name. */ + private static final String DUMMY_CACHE_NAME = "dummy_cache"; + + /** + * Executes example. + * + * @param args Command line arguments, none required. + * @throws Exception If example execution failed. + */ + @SuppressWarnings({"unused", "ThrowFromFinallyBlock"}) + public static void main(String[] args) throws Exception { + try (Ignite ignite = Ignition.start("examples/config/example-ignite.xml")) { + print("Cache query DDL example started."); + + // Create dummy cache to act as an entry point for SQL queries (new SQL API which do not require this + // will appear in future versions, JDBC and ODBC drivers do not require it already). + CacheConfiguration<?, ?> cacheCfg = new CacheConfiguration<>(DUMMY_CACHE_NAME).setSqlSchema("PUBLIC"); + + try ( + IgniteCache<?, ?> cache = ignite.getOrCreateCache(cacheCfg) + ) { + // Create reference City table based on REPLICATED template. + cache.query(new SqlFieldsQuery( + "CREATE TABLE city (id LONG PRIMARY KEY, name VARCHAR) WITH \"template=replicated\"")).getAll(); + + // Create table based on PARTITIONED template with one backup. + cache.query(new SqlFieldsQuery( + "CREATE TABLE person (id LONG, name VARCHAR, city_id LONG, PRIMARY KEY (id, city_id)) " + + "WITH \"backups=1, affinity_key=city_id\"")).getAll(); + + // Create an index. + cache.query(new SqlFieldsQuery("CREATE INDEX on Person (city_id)")).getAll(); + + print("Created database objects."); + + SqlFieldsQuery qry = new SqlFieldsQuery("INSERT INTO city (id, name) VALUES (?, ?)"); + + cache.query(qry.setArgs(1L, "Forest Hill")).getAll(); + cache.query(qry.setArgs(2L, "Denver")).getAll(); + cache.query(qry.setArgs(3L, "St. Petersburg")).getAll(); + + qry = new SqlFieldsQuery("INSERT INTO person (id, name, city_id) values (?, ?, ?)"); + + cache.query(qry.setArgs(1L, "John Doe", 3L)).getAll(); + cache.query(qry.setArgs(2L, "Jane Roe", 2L)).getAll(); + cache.query(qry.setArgs(3L, "Mary Major", 1L)).getAll(); + cache.query(qry.setArgs(4L, "Richard Miles", 2L)).getAll(); + + print("Populated data."); + + List<List<?>> res = cache.query(new SqlFieldsQuery( + "SELECT p.name, c.name FROM Person p INNER JOIN City c on c.id = p.city_id")).getAll(); + + print("Query results:"); + + for (Object next : res) + System.out.println(">>> " + next); + + cache.query(new SqlFieldsQuery("drop table Person")).getAll(); + cache.query(new SqlFieldsQuery("drop table City")).getAll(); + + print("Dropped database objects."); + } + finally { + // Distributed cache can be removed from cluster only by #destroyCache() call. + ignite.destroyCache(DUMMY_CACHE_NAME); + } + + print("Cache query DDL example finished."); + } + } + + /** + * Prints message. + * + * @param msg Message to print before all objects are printed. + */ + private static void print(String msg) { + System.out.println(); + System.out.println(">>> " + msg); + } +} http://git-wip-us.apache.org/repos/asf/ignite/blob/4b7a2c78/examples/src/main/java/org/apache/ignite/examples/sql/SqlDmlExample.java ---------------------------------------------------------------------- diff --git a/examples/src/main/java/org/apache/ignite/examples/sql/SqlDmlExample.java b/examples/src/main/java/org/apache/ignite/examples/sql/SqlDmlExample.java new file mode 100644 index 0000000..54f0fb9 --- /dev/null +++ b/examples/src/main/java/org/apache/ignite/examples/sql/SqlDmlExample.java @@ -0,0 +1,163 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.ignite.examples.sql; + +import org.apache.ignite.Ignite; +import org.apache.ignite.IgniteCache; +import org.apache.ignite.Ignition; +import org.apache.ignite.cache.query.SqlFieldsQuery; +import org.apache.ignite.configuration.CacheConfiguration; +import org.apache.ignite.examples.model.Organization; +import org.apache.ignite.examples.model.Person; + +import java.util.List; + +/** + * Example to showcase DML capabilities of Ignite's SQL engine. + */ +public class SqlDmlExample { + /** Organizations cache name. */ + private static final String ORG_CACHE = SqlDmlExample.class.getSimpleName() + "Organizations"; + + /** Persons cache name. */ + private static final String PERSON_CACHE = SqlDmlExample.class.getSimpleName() + "Persons"; + + /** + * Executes example. + * + * @param args Command line arguments, none required. + * @throws Exception If example execution failed. + */ + @SuppressWarnings({"unused", "ThrowFromFinallyBlock"}) + public static void main(String[] args) throws Exception { + try (Ignite ignite = Ignition.start("examples/config/example-ignite.xml")) { + print("Cache query DML example started."); + + CacheConfiguration<Long, Organization> orgCacheCfg = new CacheConfiguration<>(ORG_CACHE); + orgCacheCfg.setIndexedTypes(Long.class, Organization.class); + + CacheConfiguration<Long, Person> personCacheCfg = new CacheConfiguration<>(PERSON_CACHE); + personCacheCfg.setIndexedTypes(Long.class, Person.class); + + // Auto-close cache at the end of the example. + try ( + IgniteCache<Long, Organization> orgCache = ignite.getOrCreateCache(orgCacheCfg); + IgniteCache<Long, Person> personCache = ignite.getOrCreateCache(personCacheCfg) + ) { + insert(orgCache, personCache); + select(personCache, "Insert data"); + + update(personCache); + select(personCache, "Update salary for Master degrees"); + + delete(personCache); + select(personCache, "Delete non-Apache employees"); + } + finally { + // Distributed cache could be removed from cluster only by #destroyCache() call. + ignite.destroyCache(PERSON_CACHE); + ignite.destroyCache(ORG_CACHE); + } + + print("Cache query DML example finished."); + } + } + + /** + * Populate cache with test data. + * + * @param orgCache Organization cache, + * @param personCache Person cache. + */ + private static void insert(IgniteCache<Long, Organization> orgCache, IgniteCache<Long, Person> personCache) { + // Insert organizations. + SqlFieldsQuery qry = new SqlFieldsQuery("insert into Organization (_key, id, name) values (?, ?, ?)"); + + orgCache.query(qry.setArgs(1L, 1L, "ASF")); + orgCache.query(qry.setArgs(2L, 2L, "Eclipse")); + + // Insert persons. + qry = new SqlFieldsQuery( + "insert into Person (_key, id, orgId, firstName, lastName, salary, resume) values (?, ?, ?, ?, ?, ?, ?)"); + + personCache.query(qry.setArgs(1L, 1L, 1L, "John", "Doe", 4000, "Master")); + personCache.query(qry.setArgs(2L, 2L, 1L, "Jane", "Roe", 2000, "Bachelor")); + personCache.query(qry.setArgs(3L, 3L, 2L, "Mary", "Major", 5000, "Master")); + personCache.query(qry.setArgs(4L, 4L, 2L, "Richard", "Miles", 3000, "Bachelor")); + } + + /** + * Example of conditional UPDATE query: raise salary by 10% to everyone who has Master degree. + * + * @param personCache Person cache. + */ + private static void update(IgniteCache<Long, Person> personCache) { + String sql = + "update Person set salary = salary * 1.1 " + + "where resume = ?"; + + personCache.query(new SqlFieldsQuery(sql).setArgs("Master")); + } + + /** + * Example of conditional DELETE query: delete non-Apache employees. + * + * @param personCache Person cache. + */ + private static void delete(IgniteCache<Long, Person> personCache) { + String sql = + "delete from Person " + + "where id in (" + + "select p.id " + + "from Person p, \"" + ORG_CACHE + "\".Organization as o " + + "where o.name != ? and p.orgId = o.id" + + ")"; + + personCache.query(new SqlFieldsQuery(sql).setArgs("ASF")).getAll(); + } + + /** + * Query current data. + * + * @param personCache Person cache. + * @param msg Message. + */ + private static void select(IgniteCache<Long, Person> personCache, String msg) { + String sql = + "select p.id, concat(p.firstName, ' ', p.lastName), o.name, p.resume, p.salary " + + "from Person as p, \"" + ORG_CACHE + "\".Organization as o " + + "where p.orgId = o.id"; + + List<List<?>> res = personCache.query(new SqlFieldsQuery(sql).setDistributedJoins(true)).getAll(); + + print(msg); + + for (Object next : res) + System.out.println(">>> " + next); + } + + /** + * Prints message. + * + * @param msg Message to print before all objects are printed. + */ + private static void print(String msg) { + System.out.println(); + System.out.println(">>> " + msg); + } +} http://git-wip-us.apache.org/repos/asf/ignite/blob/4b7a2c78/examples/src/main/java/org/apache/ignite/examples/sql/SqlJdbcExample.java ---------------------------------------------------------------------- diff --git a/examples/src/main/java/org/apache/ignite/examples/sql/SqlJdbcExample.java b/examples/src/main/java/org/apache/ignite/examples/sql/SqlJdbcExample.java new file mode 100644 index 0000000..dc9191c --- /dev/null +++ b/examples/src/main/java/org/apache/ignite/examples/sql/SqlJdbcExample.java @@ -0,0 +1,135 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.ignite.examples.sql; + +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.Statement; +import org.apache.ignite.examples.ExampleNodeStartup; + +/** + * This example demonstrates usage of Ignite JDBC driver. + * <p> + * Ignite nodes must be started in separate process using {@link ExampleNodeStartup} before running this example. + */ +public class SqlJdbcExample { + /** + * Executes example. + * + * @param args Command line arguments, none required. + * @throws Exception If example execution failed. + */ + public static void main(String[] args) throws Exception { + print("JDBC example started."); + + // Open JDBC connection + try (Connection conn = DriverManager.getConnection("jdbc:ignite:thin://127.0.0.1/")) { + print("Connected to server."); + + // Create database objects. + try (Statement stmt = conn.createStatement()) { + // Create reference City table based on REPLICATED template. + stmt.executeUpdate("CREATE TABLE city (id LONG PRIMARY KEY, name VARCHAR) " + + "WITH \"template=replicated\""); + + // Create table based on PARTITIONED template with one backup. + stmt.executeUpdate("CREATE TABLE person (id LONG, name VARCHAR, city_id LONG, " + + "PRIMARY KEY (id, city_id)) WITH \"backups=1, affinity_key=city_id\""); + + // Create an index. + stmt.executeUpdate("CREATE INDEX on Person (city_id)"); + } + + print("Created database objects."); + + // Populate City table with PreparedStatement. + try (PreparedStatement stmt = conn.prepareStatement("INSERT INTO city (id, name) VALUES (?, ?)")) { + stmt.setLong(1, 1L); + stmt.setString(2, "Forest Hill"); + stmt.executeUpdate(); + + stmt.setLong(1, 2L); + stmt.setString(2, "Denver"); + stmt.executeUpdate(); + + stmt.setLong(1, 3L); + stmt.setString(2, "St. Petersburg"); + stmt.executeUpdate(); + } + + // Populate Person table with PreparedStatement. + try (PreparedStatement stmt = + conn.prepareStatement("INSERT INTO person (id, name, city_id) values (?, ?, ?)")) { + stmt.setLong(1, 1L); + stmt.setString(2, "John Doe"); + stmt.setLong(3, 3L); + stmt.executeUpdate(); + + stmt.setLong(1, 2L); + stmt.setString(2, "Jane Roe"); + stmt.setLong(3, 2L); + stmt.executeUpdate(); + + stmt.setLong(1, 3L); + stmt.setString(2, "Mary Major"); + stmt.setLong(3, 1L); + stmt.executeUpdate(); + + stmt.setLong(1, 4L); + stmt.setString(2, "Richard Miles"); + stmt.setLong(3, 2L); + stmt.executeUpdate(); + } + + print("Populated data."); + + // Get data. + try (Statement stmt = conn.createStatement()) { + try (ResultSet rs = + stmt.executeQuery("SELECT p.name, c.name FROM Person p INNER JOIN City c on c.id = p.city_id")) { + print("Query results:"); + + while (rs.next()) + System.out.println(">>> " + rs.getString(1) + ", " + rs.getString(2)); + } + } + + // Drop database objects. + try (Statement stmt = conn.createStatement()) { + stmt.executeUpdate("DROP TABLE Person"); + stmt.executeUpdate("DROP TABLE City"); + } + + print("Dropped database objects."); + } + + print("JDBC example finished."); + } + + /** + * Prints message. + * + * @param msg Message to print before all objects are printed. + */ + private static void print(String msg) { + System.out.println(); + System.out.println(">>> " + msg); + } +} \ No newline at end of file http://git-wip-us.apache.org/repos/asf/ignite/blob/4b7a2c78/examples/src/main/java/org/apache/ignite/examples/sql/SqlQueriesExample.java ---------------------------------------------------------------------- diff --git a/examples/src/main/java/org/apache/ignite/examples/sql/SqlQueriesExample.java b/examples/src/main/java/org/apache/ignite/examples/sql/SqlQueriesExample.java new file mode 100644 index 0000000..081b417 --- /dev/null +++ b/examples/src/main/java/org/apache/ignite/examples/sql/SqlQueriesExample.java @@ -0,0 +1,346 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.ignite.examples.sql; + +import java.util.List; +import javax.cache.Cache; +import org.apache.ignite.Ignite; +import org.apache.ignite.IgniteCache; +import org.apache.ignite.Ignition; +import org.apache.ignite.binary.BinaryObject; +import org.apache.ignite.cache.CacheMode; +import org.apache.ignite.cache.affinity.AffinityKey; +import org.apache.ignite.cache.query.QueryCursor; +import org.apache.ignite.cache.query.ScanQuery; +import org.apache.ignite.cache.query.SqlFieldsQuery; +import org.apache.ignite.cache.query.SqlQuery; +import org.apache.ignite.cache.query.TextQuery; +import org.apache.ignite.configuration.CacheConfiguration; +import org.apache.ignite.examples.ExampleNodeStartup; +import org.apache.ignite.examples.model.Organization; +import org.apache.ignite.examples.model.Person; +import org.apache.ignite.lang.IgniteBiPredicate; + +/** + * SQL queries example with the usage of Java SQL API. + * <p> + * Example also demonstrates usage of fields queries that return only required + * fields instead of whole key-value pairs. When fields queries are distributed + * across several nodes, they may not work as expected. Keep in mind following + * limitations (not applied if data is queried from one node only): + * <ul> + * <li> + * Non-distributed joins will work correctly only if joined objects are stored in + * collocated mode. Refer to {@link AffinityKey} javadoc for more details. + * <p> + * To use distributed joins it is necessary to set query 'distributedJoin' flag using + * {@link SqlFieldsQuery#setDistributedJoins(boolean)} or {@link SqlQuery#setDistributedJoins(boolean)}. + * </li> + * <li> + * Note that if you created query on to replicated cache, all data will + * be queried only on one node, not depending on what caches participate in + * the query (some data from partitioned cache can be lost). And visa versa, + * if you created it on partitioned cache, data from replicated caches + * will be duplicated. + * </li> + * </ul> + * <p> + * Remote nodes should be started using {@link ExampleNodeStartup} which will + * start node with {@code examples/config/example-ignite.xml} configuration. + */ +public class SqlQueriesExample { + /** Organizations cache name. */ + private static final String ORG_CACHE = SqlQueriesExample.class.getSimpleName() + "Organizations"; + + /** Persons collocated with Organizations cache name. */ + private static final String COLLOCATED_PERSON_CACHE = SqlQueriesExample.class.getSimpleName() + "CollocatedPersons"; + + /** Persons cache name. */ + private static final String PERSON_CACHE = SqlQueriesExample.class.getSimpleName() + "Persons"; + + /** + * Executes example. + * + * @param args Command line arguments, none required. + * @throws Exception If example execution failed. + */ + public static void main(String[] args) throws Exception { + try (Ignite ignite = Ignition.start("examples/config/example-ignite.xml")) { + System.out.println(); + System.out.println(">>> SQL queries example started."); + + CacheConfiguration<Long, Organization> orgCacheCfg = new CacheConfiguration<>(ORG_CACHE); + + orgCacheCfg.setCacheMode(CacheMode.PARTITIONED); // Default. + orgCacheCfg.setIndexedTypes(Long.class, Organization.class); + + CacheConfiguration<AffinityKey<Long>, Person> colPersonCacheCfg = + new CacheConfiguration<>(COLLOCATED_PERSON_CACHE); + + colPersonCacheCfg.setCacheMode(CacheMode.PARTITIONED); // Default. + colPersonCacheCfg.setIndexedTypes(AffinityKey.class, Person.class); + + CacheConfiguration<Long, Person> personCacheCfg = new CacheConfiguration<>(PERSON_CACHE); + + personCacheCfg.setCacheMode(CacheMode.PARTITIONED); // Default. + personCacheCfg.setIndexedTypes(Long.class, Person.class); + + try { + // Create caches. + ignite.getOrCreateCache(orgCacheCfg); + ignite.getOrCreateCache(colPersonCacheCfg); + ignite.getOrCreateCache(personCacheCfg); + + // Populate caches. + initialize(); + + // Example for SQL-based querying employees based on salary ranges. + sqlQuery(); + + // Example for SQL-based querying employees for a given organization + // (includes SQL join for collocated objects). + sqlQueryWithJoin(); + + // Example for SQL-based querying employees for a given organization + // (includes distributed SQL join). + sqlQueryWithDistributedJoin(); + + // Example for SQL-based querying to calculate average salary + // among all employees within a company. + sqlQueryWithAggregation(); + + // Example for SQL-based fields queries that return only required + // fields instead of whole key-value pairs. + sqlFieldsQuery(); + + // Example for SQL-based fields queries that uses joins. + sqlFieldsQueryWithJoin(); + } + finally { + // Distributed cache could be removed from cluster only by Ignite.destroyCache() call. + ignite.destroyCache(COLLOCATED_PERSON_CACHE); + ignite.destroyCache(PERSON_CACHE); + ignite.destroyCache(ORG_CACHE); + } + + print("SQL queries example finished."); + } + } + + /** + * Example for SQL queries based on salary ranges. + */ + private static void sqlQuery() { + IgniteCache<Long, Person> cache = Ignition.ignite().cache(PERSON_CACHE); + + // SQL clause which selects salaries based on range. + String sql = "salary > ? and salary <= ?"; + + // Execute queries for salary ranges. + print("People with salaries between 0 and 1000 (queried with SQL query): ", + cache.query(new SqlQuery<AffinityKey<Long>, Person>(Person.class, sql). + setArgs(0, 1000)).getAll()); + + print("People with salaries between 1000 and 2000 (queried with SQL query): ", + cache.query(new SqlQuery<AffinityKey<Long>, Person>(Person.class, sql). + setArgs(1000, 2000)).getAll()); + } + + /** + * Example for SQL queries based on all employees working for a specific organization. + */ + private static void sqlQueryWithJoin() { + IgniteCache<AffinityKey<Long>, Person> cache = Ignition.ignite().cache(COLLOCATED_PERSON_CACHE); + + // SQL clause query which joins on 2 types to select people for a specific organization. + String joinSql = + "from Person, \"" + ORG_CACHE + "\".Organization as org " + + "where Person.orgId = org.id " + + "and lower(org.name) = lower(?)"; + + // Execute queries for find employees for different organizations. + print("Following people are 'ApacheIgnite' employees: ", + cache.query(new SqlQuery<AffinityKey<Long>, Person>(Person.class, joinSql). + setArgs("ApacheIgnite")).getAll()); + + print("Following people are 'Other' employees: ", + cache.query(new SqlQuery<AffinityKey<Long>, Person>(Person.class, joinSql). + setArgs("Other")).getAll()); + } + + /** + * Example for SQL queries based on all employees working + * for a specific organization (query uses distributed join). + */ + private static void sqlQueryWithDistributedJoin() { + IgniteCache<Long, Person> cache = Ignition.ignite().cache(PERSON_CACHE); + + // SQL clause query which joins on 2 types to select people for a specific organization. + String joinSql = + "from Person, \"" + ORG_CACHE + "\".Organization as org " + + "where Person.orgId = org.id " + + "and lower(org.name) = lower(?)"; + + SqlQuery qry = new SqlQuery<Long, Person>(Person.class, joinSql). + setArgs("ApacheIgnite"); + + // Enable distributed joins for query. + qry.setDistributedJoins(true); + + // Execute queries for find employees for different organizations. + print("Following people are 'ApacheIgnite' employees (distributed join): ", cache.query(qry).getAll()); + + qry.setArgs("Other"); + + print("Following people are 'Other' employees (distributed join): ", cache.query(qry).getAll()); + } + + /** + * Example for SQL queries to calculate average salary for a specific organization. + */ + private static void sqlQueryWithAggregation() { + IgniteCache<AffinityKey<Long>, Person> cache = Ignition.ignite().cache(COLLOCATED_PERSON_CACHE); + + // Calculate average of salary of all persons in ApacheIgnite. + // Note that we also join on Organization cache as well. + String sql = + "select avg(salary) " + + "from Person, \"" + ORG_CACHE + "\".Organization as org " + + "where Person.orgId = org.id " + + "and lower(org.name) = lower(?)"; + + QueryCursor<List<?>> cursor = cache.query(new SqlFieldsQuery(sql).setArgs("ApacheIgnite")); + + // Calculate average salary for a specific organization. + print("Average salary for 'ApacheIgnite' employees: ", cursor.getAll()); + } + + /** + * Example for SQL-based fields queries that return only required + * fields instead of whole key-value pairs. + */ + private static void sqlFieldsQuery() { + IgniteCache<Long, Person> cache = Ignition.ignite().cache(PERSON_CACHE); + + // Execute query to get names of all employees. + QueryCursor<List<?>> cursor = cache.query(new SqlFieldsQuery( + "select concat(firstName, ' ', lastName) from Person")); + + // In this particular case each row will have one element with full name of an employees. + List<List<?>> res = cursor.getAll(); + + // Print names. + print("Names of all employees:", res); + } + + /** + * Example for SQL-based fields queries that return only required + * fields instead of whole key-value pairs. + */ + private static void sqlFieldsQueryWithJoin() { + IgniteCache<AffinityKey<Long>, Person> cache = Ignition.ignite().cache(COLLOCATED_PERSON_CACHE); + + // Execute query to get names of all employees. + String sql = + "select concat(firstName, ' ', lastName), org.name " + + "from Person, \"" + ORG_CACHE + "\".Organization as org " + + "where Person.orgId = org.id"; + + QueryCursor<List<?>> cursor = cache.query(new SqlFieldsQuery(sql)); + + // In this particular case each row will have one element with full name of an employees. + List<List<?>> res = cursor.getAll(); + + // Print persons' names and organizations' names. + print("Names of all employees and organizations they belong to: ", res); + } + + /** + * Populate cache with test data. + */ + private static void initialize() { + IgniteCache<Long, Organization> orgCache = Ignition.ignite().cache(ORG_CACHE); + + // Clear cache before running the example. + orgCache.clear(); + + // Organizations. + Organization org1 = new Organization("ApacheIgnite"); + Organization org2 = new Organization("Other"); + + orgCache.put(org1.id(), org1); + orgCache.put(org2.id(), org2); + + IgniteCache<AffinityKey<Long>, Person> colPersonCache = Ignition.ignite().cache(COLLOCATED_PERSON_CACHE); + IgniteCache<Long, Person> personCache = Ignition.ignite().cache(PERSON_CACHE); + + // Clear caches before running the example. + colPersonCache.clear(); + personCache.clear(); + + // People. + Person p1 = new Person(org1, "John", "Doe", 2000, "John Doe has Master Degree."); + Person p2 = new Person(org1, "Jane", "Doe", 1000, "Jane Doe has Bachelor Degree."); + Person p3 = new Person(org2, "John", "Smith", 1000, "John Smith has Bachelor Degree."); + Person p4 = new Person(org2, "Jane", "Smith", 2000, "Jane Smith has Master Degree."); + + // Note that in this example we use custom affinity key for Person objects + // to ensure that all persons are collocated with their organizations. + colPersonCache.put(p1.key(), p1); + colPersonCache.put(p2.key(), p2); + colPersonCache.put(p3.key(), p3); + colPersonCache.put(p4.key(), p4); + + // These Person objects are not collocated with their organizations. + personCache.put(p1.id, p1); + personCache.put(p2.id, p2); + personCache.put(p3.id, p3); + personCache.put(p4.id, p4); + } + + /** + * Prints message and query results. + * + * @param msg Message to print before all objects are printed. + * @param col Query results. + */ + private static void print(String msg, Iterable<?> col) { + print(msg); + print(col); + } + + /** + * Prints message. + * + * @param msg Message to print before all objects are printed. + */ + private static void print(String msg) { + System.out.println(); + System.out.println(">>> " + msg); + } + + /** + * Prints query results. + * + * @param col Query results. + */ + private static void print(Iterable<?> col) { + for (Object next : col) + System.out.println(">>> " + next); + } +} http://git-wip-us.apache.org/repos/asf/ignite/blob/4b7a2c78/examples/src/main/java/org/apache/ignite/examples/sql/package-info.java ---------------------------------------------------------------------- diff --git a/examples/src/main/java/org/apache/ignite/examples/sql/package-info.java b/examples/src/main/java/org/apache/ignite/examples/sql/package-info.java new file mode 100644 index 0000000..fe2eeb9 --- /dev/null +++ b/examples/src/main/java/org/apache/ignite/examples/sql/package-info.java @@ -0,0 +1,22 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +/** + * <!-- Package description. --> + * Demonstrates Ignite SQL API usage. + */ +package org.apache.ignite.examples.sql; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/ignite/blob/4b7a2c78/examples/src/test/java/org/apache/ignite/examples/CacheExamplesMultiNodeSelfTest.java ---------------------------------------------------------------------- diff --git a/examples/src/test/java/org/apache/ignite/examples/CacheExamplesMultiNodeSelfTest.java b/examples/src/test/java/org/apache/ignite/examples/CacheExamplesMultiNodeSelfTest.java index f940ff7..c04f97e 100644 --- a/examples/src/test/java/org/apache/ignite/examples/CacheExamplesMultiNodeSelfTest.java +++ b/examples/src/test/java/org/apache/ignite/examples/CacheExamplesMultiNodeSelfTest.java @@ -17,7 +17,7 @@ package org.apache.ignite.examples; -import org.apache.ignite.examples.datagrid.JdbcExample; +import org.apache.ignite.examples.sql.SqlJdbcExample; /** * Cache examples multi-node self test. @@ -49,6 +49,6 @@ public class CacheExamplesMultiNodeSelfTest extends CacheExamplesSelfTest { * @throws Exception If failed. */ public void testJdbcThinExample() throws Exception { - JdbcExample.main(EMPTY_ARGS); + SqlJdbcExample.main(EMPTY_ARGS); } } \ No newline at end of file http://git-wip-us.apache.org/repos/asf/ignite/blob/4b7a2c78/examples/src/test/java/org/apache/ignite/examples/CacheExamplesSelfTest.java ---------------------------------------------------------------------- diff --git a/examples/src/test/java/org/apache/ignite/examples/CacheExamplesSelfTest.java b/examples/src/test/java/org/apache/ignite/examples/CacheExamplesSelfTest.java index c42c91a..d39fab5 100644 --- a/examples/src/test/java/org/apache/ignite/examples/CacheExamplesSelfTest.java +++ b/examples/src/test/java/org/apache/ignite/examples/CacheExamplesSelfTest.java @@ -23,8 +23,8 @@ import org.apache.ignite.examples.datagrid.CacheApiExample; import org.apache.ignite.examples.datagrid.CacheContinuousQueryExample; import org.apache.ignite.examples.datagrid.CacheDataStreamerExample; import org.apache.ignite.examples.datagrid.CachePutGetExample; -import org.apache.ignite.examples.datagrid.CacheQueryDdlExample; -import org.apache.ignite.examples.datagrid.CacheQueryDmlExample; +import org.apache.ignite.examples.sql.SqlDdlExample; +import org.apache.ignite.examples.sql.SqlDmlExample; import org.apache.ignite.examples.datagrid.CacheQueryExample; import org.apache.ignite.examples.datagrid.CacheTransactionExample; import org.apache.ignite.examples.datagrid.DataRegionsExample; @@ -133,14 +133,14 @@ public class CacheExamplesSelfTest extends GridAbstractExamplesTest { * @throws Exception If failed. */ public void testCacheQueryDmlExample() throws Exception { - CacheQueryDmlExample.main(EMPTY_ARGS); + SqlDmlExample.main(EMPTY_ARGS); } /** * @throws Exception If failed. */ public void testCacheQueryDdlExample() throws Exception { - CacheQueryDdlExample.main(EMPTY_ARGS); + SqlDdlExample.main(EMPTY_ARGS); } /** http://git-wip-us.apache.org/repos/asf/ignite/blob/4b7a2c78/examples/src/test/java/org/apache/ignite/examples/SqlExamplesSelfTest.java ---------------------------------------------------------------------- diff --git a/examples/src/test/java/org/apache/ignite/examples/SqlExamplesSelfTest.java b/examples/src/test/java/org/apache/ignite/examples/SqlExamplesSelfTest.java new file mode 100644 index 0000000..0bf01d8 --- /dev/null +++ b/examples/src/test/java/org/apache/ignite/examples/SqlExamplesSelfTest.java @@ -0,0 +1,49 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.ignite.examples; + +import org.apache.ignite.examples.sql.SqlDdlExample; +import org.apache.ignite.examples.sql.SqlDmlExample; +import org.apache.ignite.examples.sql.SqlQueriesExample; +import org.apache.ignite.testframework.junits.common.GridAbstractExamplesTest; + +/** + * SQL examples self test. + */ +public class SqlExamplesSelfTest extends GridAbstractExamplesTest { + /** + * @throws Exception If failed. + */ + public void testSqlJavaExample() throws Exception { + SqlQueriesExample.main(EMPTY_ARGS); + } + + /** + * @throws Exception If failed. + */ + public void testSqlDmlExample() throws Exception { + SqlDmlExample.main(EMPTY_ARGS); + } + + /** + * @throws Exception If failed. + */ + public void testSqlDdlExample() throws Exception { + SqlDdlExample.main(EMPTY_ARGS); + } +} http://git-wip-us.apache.org/repos/asf/ignite/blob/4b7a2c78/examples/src/test/java/org/apache/ignite/testsuites/IgniteExamplesSelfTestSuite.java ---------------------------------------------------------------------- diff --git a/examples/src/test/java/org/apache/ignite/testsuites/IgniteExamplesSelfTestSuite.java b/examples/src/test/java/org/apache/ignite/testsuites/IgniteExamplesSelfTestSuite.java index 3e29e5c..0eea40a 100644 --- a/examples/src/test/java/org/apache/ignite/testsuites/IgniteExamplesSelfTestSuite.java +++ b/examples/src/test/java/org/apache/ignite/testsuites/IgniteExamplesSelfTestSuite.java @@ -43,6 +43,7 @@ import org.apache.ignite.examples.MonteCarloExamplesMultiNodeSelfTest; import org.apache.ignite.examples.MonteCarloExamplesSelfTest; import org.apache.ignite.examples.SpringBeanExamplesSelfTest; import org.apache.ignite.examples.SpringDataExampleSelfTest; +import org.apache.ignite.examples.SqlExamplesSelfTest; import org.apache.ignite.examples.TaskExamplesMultiNodeSelfTest; import org.apache.ignite.examples.TaskExamplesSelfTest; @@ -63,6 +64,7 @@ public class IgniteExamplesSelfTestSuite extends TestSuite { TestSuite suite = new TestSuite("Ignite Examples Test Suite"); suite.addTest(new TestSuite(CacheExamplesSelfTest.class)); + suite.addTest(new TestSuite(SqlExamplesSelfTest.class)); suite.addTest(new TestSuite(BasicExamplesSelfTest.class)); suite.addTest(new TestSuite(ContinuationExamplesSelfTest.class)); suite.addTest(new TestSuite(ContinuousMapperExamplesSelfTest.class));
