I want to get data from ignite cache for list of keys which I can pass in IN
clause of SQL Query. BUt since IN does not take dynamic parameters, I
followed this
<http://apacheignite.gridgain.org/docs/sql-queries#performance-and-usability-considerations>
link to use JOIN to perform my task, Unfortunately I am getting below
exception. Can some one help please.
public static void test() throws InterruptedException{
Ignite ignite =
Ignition.start("examples/config/example-ignite.xml");
CacheConfiguration<Integer, Company> ccfg = new
CacheConfiguration<>("companies");
ccfg.setIndexedTypes(Integer.class, Company.class);
ccfg.setIndexedTypes(LocalDate.class, Company.class);
IgniteCache<Integer, Company> companies =
ignite.getOrCreateCache(ccfg);
List<Person> persons = new ArrayList<>();
List<Person> persons1 = new ArrayList<>();
persons.add(new Person(1, 1, "john", "Doe", "", 2000,
Date.valueOf(LocalDate.of(1989, 1, 9))));
persons1.add(new Person(2, 2, "Kathy", "Siera", "",
2000,
Date.valueOf(LocalDate.of(1979, 1, 9))));
Company c1 = new Company(Integer.valueOf(1),
"Comp1",LocalDate.of(1990,
9, 3), Date.valueOf(LocalDate.of(2016, 03, 03)),
Arrays.asList("bangalore","US"),persons);
Company c2 = new Company(Integer.valueOf(2),
"comp2",LocalDate.of(1995,
6, 1), Date.valueOf(LocalDate.of(2016, 03, 03)),
Arrays.asList("Mumbai","UK"),persons1);
Company c3 = new Company(Integer.valueOf(3),
"comp3",LocalDate.of(1997,
8, 5), Date.valueOf(LocalDate.of(2016, 03, 03)),
Arrays.asList("CA","US"),persons);
Company c4 = new Company(Integer.valueOf(4),
"comp4",LocalDate.of(2005,
3, 21), Date.valueOf(LocalDate.of(2016, 03, 03)),
Arrays.asList("GGN","US"),persons1);
Company c5 = new Company(Integer.valueOf(5),
"GG",LocalDate.of(2010, 2,
23), Date.valueOf(LocalDate.of(2016, 03, 03)),
Arrays.asList("GM","US"),persons);
Company c6 = new Company(Integer.valueOf(6),
"APache",LocalDate.of(2013,
9, 13), Date.valueOf(LocalDate.of(2016, 03, 03)),
Arrays.asList("RS","US"),persons1);
companies.put(c1.getId(), c1);
companies.put(c2.getId(), c2);
companies.put(c3.getId(), c3);
companies.put(c4.getId(), c4);
companies.put(c5.getId(), c5);
companies.put(c6.getId(), c6);
String sortby = "id";
String orderBy = "desc";
String startdate = "3Mar16";
List list = Arrays.asList(2,3,6);
String sql ="select * from COMPANY p join table(id
bigint = ?) i on p.id
= i.id";
SqlQuery<Integer, Company> query1 = new
SqlQuery<>(Company.class, sql);
query1.setArgs(list.toArray());
long start = System.currentTimeMillis();
List<Entry<Integer, Company>> res1=
companies.query(query1).getAll();
Thread.sleep(1000);
long end = System.currentTimeMillis();
System.out.println("time tken : "+(end-start)/1000);
for (Entry<Integer, Company> entry : res1) {
System.out.println(entry.getKey()+ "
"+entry.getValue());
}
}
public class Company {
@QuerySqlField(index=true)
private int id ;
@QuerySqlField(index=true)
private String name;
@QuerySqlField
private LocalDate startDate;
@QuerySqlField(index=true)
private Date inaguration;
@QuerySqlField
private List<String> offices = new ArrayList<>();
@QuerySqlField
private List<Person> employees = new ArrayList<>();
.......setters/getters
}
Exception :
Exception in thread "main" javax.cache.CacheException: class
org.apache.ignite.IgniteException: Failed to parse query: SELECT
"companies".Company._key, "companies".Company._val from COMPANY p join
table(id bigint = ?) i on p.id = i.id
at
org.apache.ignite.internal.processors.cache.IgniteCacheProxy.query(IgniteCacheProxy.java:700)
at com.test.ignite.sample.DataGrid.test(DataGrid.java:78)
at com.test.ignite.sample.DataGrid.main(DataGrid.java:27)
Caused by: class org.apache.ignite.IgniteException: Failed to parse query:
SELECT "companies".Company._key, "companies".Company._val from COMPANY p
join table(id bigint = ?) i on p.id = i.id
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.queryTwoStep(GridQueryProcessor.java:837)
at
org.apache.ignite.internal.processors.cache.IgniteCacheProxy.query(IgniteCacheProxy.java:682)
... 2 more
Caused by: class org.apache.ignite.IgniteCheckedException: Failed to parse
query: SELECT "companies".Company._key, "companies".Company._val from
COMPANY p join table(id bigint = ?) i on p.id = i.id
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:1787)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.queryTwoStep(GridQueryProcessor.java:830)
... 3 more
Caused by: javax.cache.CacheException: Failed to parse query: SELECT
"companies".Company._key, "companies".Company._val from COMPANY p join
table(id bigint = ?) i on p.id = i.id
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryTwoStep(IgniteH2Indexing.java:1074)
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryTwoStep(IgniteH2Indexing.java:1001)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor$5.applyx(GridQueryProcessor.java:832)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor$5.applyx(GridQueryProcessor.java:830)
at
org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:1769)
... 4 more
Caused by: org.h2.jdbc.JdbcSQLException: Column "companies.COMPANY._KEY" not
found; SQL statement:
SELECT "companies".Company._key, "companies".Company._val from COMPANY p
join table(id bigint = ?) i on p.id = i.id [42122-175]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
at org.h2.message.DbException.get(DbException.java:172)
at org.h2.message.DbException.get(DbException.java:149)
at
org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:144)
at org.h2.command.dml.Select.prepare(Select.java:810)
at org.h2.command.Parser.prepareCommand(Parser.java:240)
at org.h2.engine.Session.prepareLocal(Session.java:436)
at org.h2.engine.Session.prepareCommand(Session.java:379)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1138)
at
org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:70)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:267)
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatement(IgniteH2Indexing.java:366)
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryTwoStep(IgniteH2Indexing.java:1058)
... 9 more
--
View this message in context:
http://apache-ignite-users.70518.x6.nabble.com/Getting-exception-in-SQL-Query-JOINS-tp6674.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
