Hi,
I am trying to use SqlQuery JOIN clause as mentioned in below link which is
work around to avoid IN clause of Sql Query.
As mentioned in docuents, we can pass array or lits of parameters, but query
does not accept array or list with more than one parameter
http://apacheignite.gridgain.org/docs/sql-queries#performance-and-usability-considerations
I have written small program to verify it.
public class SQLJoinPOC {
public static void main(String[] args) {
Ignite ignite =
Ignition.start("examples/config/example-ignite.xml");
CacheConfiguration<Integer, Student> config = new
CacheConfiguration<>("students");
config.setIndexedTypes(Integer.class, Student.class);
IgniteCache<Integer, Student> students =
ignite.getOrCreateCache(config);
Student student1 = new Student(1, "John");
Student student2 = new Student(2, "Doe");
Student student3 = new Student(3, "kathy");
Student student4 = new Student(4, "Siera");
students.put(student1.getId(), student1);
students.put(student2.getId(), student2);
students.put(student3.getId(), student3);
students.put(student4.getId(), student4);
List list = Arrays.asList(2,3);
/*StringBuffer queryStrings = new StringBuffer(" ");
for (Object object : list) {
queryStrings.append("?,");
}
queryStrings.deleteCharAt(queryStrings.length() - 1);
String sql = "select * from Student where Id in
("+queryStrings+")";*/
String sql = "select s.* from STUDENT s join table(id bigint =
?) i on
s.Id = i.Id";
SqlQuery<Integer, Student> query1 = new
SqlQuery<>(Student.class, sql);
query1.setArgs(list.toArray());
List<Entry<Integer, Student>> res1 =
students.query(query1).getAll();
for (Entry<Integer, Student> entry : res1) {
System.out.println(entry.getKey() + " " +
entry.getValue());
}
}
}
public class Student implements Serializable{
@QuerySqlField(index=true)
private int Id;
@QuerySqlField(index=true)
private String name;
public int getId() {
return Id;
}
public void setId(int id) {
Id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Student(int id, String name) {
Id = id;
this.name = name;
}
@Override
public int hashCode() {
return Objects.hash(Id, name);
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Student other = (Student) obj;
return Objects.equals(Id, other.getId()) && Objects.equals(name,
other.getName());
}
@Override
public String toString() {
return "Student [Id=" + Id + ", name=" + name + "]";
}
}
Exception:
Exception in thread "main" javax.cache.CacheException: class
org.apache.ignite.IgniteException: Failed to bind parameters: [qry=SELECT
s._key, s._val from STUDENT s join table(id bigint = ?) i on s.Id = i.Id,
params=[2, 3]]
at
org.apache.ignite.internal.processors.cache.IgniteCacheProxy.query(IgniteCacheProxy.java:700)
at com.test.ignite.sample.SQLJoinPOC.main(SQLJoinPOC.java:44)
Caused by: class org.apache.ignite.IgniteException: Failed to bind
parameters: [qry=SELECT s._key, s._val from STUDENT s join table(id bigint =
?) i on s.Id = i.Id, params=[2, 3]]
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.queryTwoStep(GridQueryProcessor.java:837)
at
org.apache.ignite.internal.processors.cache.IgniteCacheProxy.query(IgniteCacheProxy.java:682)
... 1 more
Caused by: class org.apache.ignite.IgniteCheckedException: Failed to bind
parameters: [qry=SELECT s._key, s._val from STUDENT s join table(id bigint =
?) i on s.Id = i.Id, params=[2, 3]]
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:1787)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.queryTwoStep(GridQueryProcessor.java:830)
... 2 more
Caused by: javax.cache.CacheException: Failed to bind parameters:
[qry=SELECT s._key, s._val from STUDENT s join table(id bigint = ?) i on
s.Id = i.Id, params=[2, 3]]
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryTwoStep(IgniteH2Indexing.java:1083)
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)
... 3 more
Caused by: class org.apache.ignite.IgniteCheckedException: Failed to bind
parameter [idx=2, obj=3, stmt=prep1: SELECT s._key, s._val from STUDENT s
join table(id bigint = ?) i on s.Id = i.Id {1: 2}]
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.bindObject(IgniteH2Indexing.java:505)
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.bindParameters(IgniteH2Indexing.java:930)
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryTwoStep(IgniteH2Indexing.java:1080)
... 8 more
Caused by: org.h2.jdbc.JdbcSQLException: Invalid value "2" for parameter
"parameterIndex" [90008-175]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
at org.h2.message.DbException.get(DbException.java:172)
at
org.h2.message.DbException.getInvalidValueException(DbException.java:218)
at
org.h2.jdbc.JdbcPreparedStatement.setParameter(JdbcPreparedStatement.java:1338)
at
org.h2.jdbc.JdbcPreparedStatement.setObject(JdbcPreparedStatement.java:451)
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.bindObject(IgniteH2Indexing.java:502)
... 10 more
--
View this message in context:
http://apache-ignite-users.70518.x6.nabble.com/SQLQuery-JOIN-does-not-accept-multiple-parameters-in-list-array-as-placeholder-tp6837.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
