Example 11 with DbDocImpl
use of com.mysql.cj.xdevapi.DbDocImpl in project aws-mysql-jdbc by awslabs.
the class CollectionFindTest method testCollectionFindOverlaps.
@Test
public void testCollectionFindOverlaps() throws Exception {
assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");
try {
int i = 0, j = 0, maxrec = 8, minArraySize = 3;
DbDoc doc = null;
DocResult docs = null;
long l3 = 2147483647;
double d1 = 1000.1234;
for (i = 0; i < maxrec; i++) {
DbDoc newDoc2 = new DbDocImpl();
newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
newDoc2.add("F1", new JsonNumber().setValue(String.valueOf(i + 1)));
JsonArray jarray = new JsonArray();
for (j = 0; j < (minArraySize + i); j++) {
jarray.addValue(new JsonNumber().setValue(String.valueOf((l3 + j + i))));
}
newDoc2.add("ARR1", jarray);
JsonArray karray = new JsonArray();
for (j = 0; j < (minArraySize + i); j++) {
karray.addValue(new JsonNumber().setValue(String.valueOf((d1 + j + i))));
}
newDoc2.add("ARR2", karray);
JsonArray larray = new JsonArray();
for (j = 0; j < (minArraySize + i); j++) {
larray.addValue(new JsonString().setValue("St_" + i + "_" + j));
}
newDoc2.add("ARR3", larray);
this.collection.add(newDoc2).execute();
newDoc2 = null;
jarray = null;
}
assertEquals((maxrec), this.collection.count());
/* find with single OVERLAPS in array */
docs = this.collection.find("2147483647 overlaps $.ARR1").execute();
doc = docs.next();
assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
assertFalse(docs.hasNext());
Table table = this.schema.getCollectionAsTable(this.collectionName);
RowResult rows = table.select("doc->$._id as _id").where("2147483647 overlaps $.ARR1").execute();
Row r = rows.next();
assertEquals("\"1000\"", r.getString("_id"));
assertFalse(rows.hasNext());
/* find with array OVERLAPS array */
docs = this.collection.find("[2147483647, 2147483648, 2147483649] overlaps $.ARR1").execute();
doc = docs.next();
assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
doc = docs.next();
assertEquals("1001", (((JsonString) doc.get("_id")).getString()));
doc = docs.next();
assertEquals("1002", (((JsonString) doc.get("_id")).getString()));
assertFalse(docs.hasNext());
rows = table.select("doc->$._id as _id").where("[2147483647, 2147483648, 2147483649] overlaps $.ARR1").execute();
r = rows.next();
assertEquals("\"1000\"", r.getString("_id"));
r = rows.next();
assertEquals("\"1001\"", r.getString("_id"));
r = rows.next();
assertEquals("\"1002\"", r.getString("_id"));
assertFalse(rows.hasNext());
/* find with array OVERLAPS array with orderBy */
docs = this.collection.find("[2147483648, 2147483648, 2147483649] overlaps $.ARR1").orderBy("_id").execute();
doc = docs.next();
assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
doc = docs.next();
assertEquals("1001", (((JsonString) doc.get("_id")).getString()));
doc = docs.next();
assertEquals("1002", (((JsonString) doc.get("_id")).getString()));
assertFalse(docs.hasNext());
/* */
docs = this.collection.find("[!false && true] OVERLAPS [true]").execute();
assertEquals(maxrec, docs.count());
/* Overlaps with NULL */
docs = this.collection.find("NULL overlaps $.ARR1").execute();
assertFalse(docs.hasNext());
rows = table.select("doc->$._id as _id").where("NULL overlaps $.ARR1").execute();
assertFalse(rows.hasNext());
docs = this.collection.find("$.ARR1 overlaps null").execute();
assertFalse(docs.hasNext());
rows = table.select("doc->$._id as _id").where("$.ARR1 overlaps NULL").execute();
assertFalse(rows.hasNext());
/* Not Overlaps with NULL */
docs = this.collection.find("NULL not overlaps $.ARR1").execute();
assertTrue(docs.hasNext());
assertEquals(maxrec, docs.count());
rows = table.select("doc->$._id as _id").where("NULL not overlaps $.ARR1").execute();
assertTrue(rows.hasNext());
assertEquals(maxrec, docs.count());
docs = this.collection.find("$.ARR1 not overlaps null").execute();
assertTrue(docs.hasNext());
assertEquals(maxrec, docs.count());
rows = table.select("doc->$._id as _id").where("$.ARR1 not overlaps null").execute();
assertTrue(rows.hasNext());
assertEquals(maxrec, docs.count());
/* Test OVERLAPS/NOT OVERLAPS with empty array - Expected to pass, though the array is empty but still valid */
// checking the case insensitivity as well
docs = this.collection.find("[] Overlaps $.ARR1").execute();
assertFalse(docs.hasNext());
rows = table.select().where("[] ovErlaps $.ARR1").execute();
assertFalse(rows.hasNext());
// checking the case insensitivity as well
docs = this.collection.find("$.ARR1 overlapS []").execute();
assertFalse(docs.hasNext());
rows = table.select().where("$.ARR1 ovErlaps []").execute();
assertFalse(rows.hasNext());
docs = this.collection.find("[] not overlaps $.ARR1").execute();
assertTrue(docs.hasNext());
assertEquals(maxrec, docs.count());
rows = table.select().where("[] not overlaps $.ARR1").execute();
assertTrue(rows.hasNext());
assertEquals(maxrec, docs.count());
// checking the case insensitivity as well
docs = this.collection.find("$.ARR1 not oveRlaps []").execute();
assertTrue(docs.hasNext());
assertEquals(maxrec, docs.count());
rows = table.select().where("$.ARR1 not overlaps []").execute();
assertTrue(rows.hasNext());
assertEquals(maxrec, docs.count());
/* When the right number of operands are not provided - error should be thrown */
assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 0", () -> this.collection.find("overlaps $.ARR1").execute());
assertThrows(WrongArgumentException.class, "No more tokens when expecting one at token pos 4", () -> this.collection.find("$.ARR1 OVERLAPS").execute());
assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 0", () -> this.collection.find("OVERLAPS").execute());
assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 1", () -> this.collection.find("not overlaps $.ARR1").execute());
assertThrows(WrongArgumentException.class, "No more tokens when expecting one at token pos 5", () -> this.collection.find("$.ARR1 NOT OVERLAPS").execute());
assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 1", () -> this.collection.find("not OVERLAPS").execute());
final Table table1 = this.schema.getCollectionAsTable(this.collectionName);
assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 0", () -> table1.select().where("overlaps $.ARR1").execute());
assertThrows(WrongArgumentException.class, "No more tokens when expecting one at token pos 4", () -> table1.select().where("$.ARR1 OVERLAPS").execute());
assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 0", () -> table1.select().where("OVERLAPS").execute());
assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 1", () -> table1.select().where("not overlaps $.ARR1").execute());
assertThrows(WrongArgumentException.class, "No more tokens when expecting one at token pos 5", () -> table1.select().where("$.ARR1 NOT OVERLAPS").execute());
assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token pos: 1", () -> table1.select().where("not OVERLAPS").execute());
/* invalid criteria, e.g. .find("[1, 2, 3] OVERLAPS $.age") . where $.age is atomic value */
dropCollection("coll2");
Collection coll2 = this.schema.createCollection("coll2", true);
coll2.add("{ \"_id\": \"1\", \"name\": \"nonjson\", \"age\": \"50\",\"arrayField\":[1,[7]]}").execute();
// The below command should give exception, but X-plugin doesn't return any error
docs = coll2.find("[1,2,3] overlaps $.age").execute();
assertEquals(0, docs.count());
docs = coll2.find("arrayField OVERLAPS [7]").execute();
assertEquals(0, docs.count());
docs = coll2.find("arrayField[1] OVERLAPS [7]").execute();
assertEquals(1, docs.count());
table = this.schema.getCollectionAsTable("coll2");
rows = table.select().where("[1,2,3] overlaps $.age").execute();
assertEquals(0, rows.count());
/* Test with empty spaces */
dropCollection("coll3");
Collection coll3 = this.schema.createCollection("coll3", true);
// List contains an array without any space
coll3.add("{ \"_id\":1, \"name\": \"Record1\",\"list\":[\"\"], \"age\":15, \"intList\":[1,2,3] }").execute();
// List contains an array with space
coll3.add("{ \"_id\":2, \"name\": \"overlaps\",\"list\":[\" \"],\"age\":24}").execute();
coll3.add("{ \"_id\":3, \"overlaps\": \"overlaps\",\"age\":30}").execute();
docs = coll3.find("[''] OVERLAPS $.list").execute();
assertEquals(1, docs.count());
assertEquals(new Integer(1), ((JsonNumber) docs.next().get("_id")).getInteger());
table = this.schema.getCollectionAsTable("coll3");
rows = table.select("doc->$._id as _id").where("[''] overlaps $.list").execute();
r = rows.next();
assertEquals("1", r.getString("_id"));
docs = coll3.find("[' '] OVERLAPS $.list").execute();
assertEquals(1, docs.count());
assertEquals(new Integer(2), ((JsonNumber) docs.next().get("_id")).getInteger());
rows = table.select("doc->$._id as _id").where("[' '] overlaps $.list").execute();
r = rows.next();
assertEquals("2", r.getString("_id"));
docs = coll3.find("'overlaps' OVERLAPS $.name").execute();
assertEquals(1, docs.count());
assertEquals(new Integer(2), ((JsonNumber) docs.next().get("_id")).getInteger());
rows = table.select("doc->$._id as _id").where("'overlaps' overlaps $.name").execute();
r = rows.next();
assertEquals(1, docs.count());
assertEquals("2", r.getString("_id"));
docs = coll3.find("[3] OVERLAPS $.intList").execute();
assertEquals(1, docs.count());
rows = table.select().where("[3] overlaps $.intList").execute();
assertEquals(1, rows.count());
/* Escape the keyword, to use it as identifier */
docs = coll3.find("`overlaps` OVERLAPS $.`overlaps`").execute();
assertEquals(1, docs.count());
rows = table.select().where("'overlaps' overlaps $.`overlaps`").execute();
assertEquals(1, rows.count());
docs = coll3.find("$.`overlaps` OVERLAPS `overlaps`").execute();
assertEquals(1, docs.count());
rows = table.select().where("$.`overlaps` overlaps 'overlaps'").execute();
assertEquals(1, rows.count());
dropCollection("coll4");
Collection coll4 = this.schema.createCollection("coll4", true);
coll4.add("{\"overlaps\":{\"one\":1, \"two\":2, \"three\":3},\"list\":{\"one\":1, \"two\":2, \"three\":3},\"name\":\"one\"}").execute();
coll4.add("{\"overlaps\":{\"one\":1, \"two\":2, \"three\":3},\"list\":{\"four\":4, \"five\":5, \"six\":6},\"name\":\"two\"}").execute();
coll4.add("{\"overlaps\":{\"one\":1, \"three\":3, \"five\":5},\"list\":{\"two\":2, \"four\":4, \"six\":6},\"name\":\"three\"}").execute();
coll4.add("{\"overlaps\":{\"one\":1, \"three\":3, \"five\":5},\"list\":{\"three\":3, \"six\":9, \"nine\":9},\"name\":\"four\"}").execute();
coll4.add("{\"overlaps\":{\"one\":1, \"three\":3, \"five\":5},\"list\":{\"three\":6, \"six\":12, \"nine\":18},\"name\":\"five\"}").execute();
coll4.add("{\"overlaps\":{\"one\":[1,2,3]}, \"list\":{\"one\":[3,4,5]}, \"name\":\"six\"}").execute();
coll4.add("{\"overlaps\":{\"one\":[1,2,3]}, \"list\":{\"one\":[1,2,3]}, \"name\":\"seven\"}").execute();
docs = coll4.find("`overlaps` OVERLAPS `list`").execute();
assertEquals(3, docs.count());
doc = docs.fetchOne();
assertEquals("one", (((JsonString) doc.get("name")).getString()));
doc = docs.fetchOne();
assertEquals("four", (((JsonString) doc.get("name")).getString()));
doc = docs.fetchOne();
assertEquals("seven", (((JsonString) doc.get("name")).getString()));
table = this.schema.getCollectionAsTable("coll4");
rows = table.select("doc->$.name as name").where("$.`overlaps` OVERLAPS $.`list`").execute();
assertEquals(3, rows.count());
r = rows.next();
assertEquals("\"one\"", r.getString("name"));
} finally {
dropCollection("coll4");
dropCollection("coll3");
dropCollection("coll2");
}
}
Also used :
Table(com.mysql.cj.xdevapi.Table)
JsonArray(com.mysql.cj.xdevapi.JsonArray)
DbDoc(com.mysql.cj.xdevapi.DbDoc)
RowResult(com.mysql.cj.xdevapi.RowResult)
DbDocImpl(com.mysql.cj.xdevapi.DbDocImpl)
JsonNumber(com.mysql.cj.xdevapi.JsonNumber)
Collection(com.mysql.cj.xdevapi.Collection)
JsonString(com.mysql.cj.xdevapi.JsonString)
Row(com.mysql.cj.xdevapi.Row)
DocResult(com.mysql.cj.xdevapi.DocResult)
Test(org.junit.jupiter.api.Test)
Example 12 with DbDocImpl
use of com.mysql.cj.xdevapi.DbDocImpl in project aws-mysql-jdbc by awslabs.
the class CollectionFindTest method testCollRemoveTabDeleteWithOverlaps.
@SuppressWarnings("deprecation")
@Test
public void testCollRemoveTabDeleteWithOverlaps() throws Exception {
assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");
int i = 0, maxrec = 10;
DocResult docs = null;
Result res = null;
/* add(DbDoc[] docs) */
DbDoc[] jsonlist = new DbDocImpl[maxrec];
for (i = 0; i < maxrec; i++) {
DbDoc newDoc2 = new DbDocImpl();
newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(10 * (i + 1) + 0.1234)));
newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(i + 1)));
newDoc2.add("F4", new JsonNumber().setValue(String.valueOf(10000 + i)));
jsonlist[i] = newDoc2;
newDoc2 = null;
}
this.collection.add(jsonlist).execute();
assertEquals((maxrec), this.collection.count());
/* find without Condition */
docs = this.collection.find().fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3,$.F2/10 as tmp1,1/2 as tmp2").orderBy("$.F3").execute();
i = 0;
while (docs.hasNext()) {
docs.next();
i++;
}
assertEquals((maxrec), i);
/* remove with single OVERLAPS */
res = this.collection.remove("'1001' overlaps $._id").execute();
assertEquals(1, res.getAffectedItemsCount());
docs = this.collection.find("'1001' overlaps $._id").execute();
assertFalse(docs.hasNext());
/* remove with mulltiple OVERLAPS */
String findCond = "";
for (i = 1; i < maxrec; i++) {
findCond = findCond + "'";
findCond = findCond + String.valueOf(i + 1000) + "' not overlaps $._id";
if (i != maxrec - 1) {
findCond = findCond + " and ";
}
}
res = this.collection.remove(findCond).execute();
assertEquals(1, res.getAffectedItemsCount());
docs = this.collection.find(findCond).execute();
assertFalse(docs.hasNext());
/* remove with single OVERLAPS */
res = this.collection.remove("10004 overlaps $.F4").orderBy("CAST($.F4 as SIGNED)").execute();
assertEquals(1, res.getAffectedItemsCount());
docs = this.collection.find("10004 overlaps $.F4").orderBy("CAST($.F4 as SIGNED)").execute();
assertFalse(docs.hasNext());
/* remove with single OVERLAPS for float */
res = this.collection.remove("30.1234 overlaps $.F2").orderBy("CAST($.F4 as SIGNED)").execute();
assertEquals(1, res.getAffectedItemsCount());
docs = this.collection.find("30.1234 overlaps $.F2").orderBy("CAST($.F4 as SIGNED)").execute();
assertFalse(docs.hasNext());
res = this.collection.remove("true").execute();
jsonlist = new DbDocImpl[maxrec];
for (i = 0; i < maxrec; i++) {
DbDoc newDoc2 = new DbDocImpl();
newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(10 * (i + 1) + 0.1234)));
newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(i + 1)));
newDoc2.add("F4", new JsonNumber().setValue(String.valueOf(10000 + i)));
jsonlist[i] = newDoc2;
newDoc2 = null;
}
this.collection.add(jsonlist).execute();
assertEquals((maxrec), this.collection.count());
Table table = this.schema.getCollectionAsTable(this.collectionName);
/* delete with single OVERLAPS */
res = table.delete().where("'1001' overlaps doc->$._id").execute();
assertEquals(res.getAffectedItemsCount(), 1);
RowResult rows = table.select("doc->$.F1 as _id").where("'1001' overlaps doc->$._id").execute();
assertFalse(rows.hasNext());
/* delete with multiple OVERLAPS */
findCond = "";
for (i = 1; i < maxrec; i++) {
findCond = findCond + "'";
findCond = findCond + String.valueOf(i + 1000) + "' not overlaps doc->$._id";
if (i != maxrec - 1) {
findCond = findCond + " and ";
}
}
res = table.delete().where(findCond).execute();
assertEquals(res.getAffectedItemsCount(), 1);
rows = table.select("doc->$.F1 as _id").where(findCond).execute();
assertFalse(rows.hasNext());
/* delete with single OVERLAPS for float */
res = table.delete().where("30.1234 overlaps doc->$.F2").execute();
assertEquals(res.getAffectedItemsCount(), 1);
rows = table.select("doc->$.F1 as F1").where("30.1234 overlaps doc->$.F2").execute();
assertFalse(rows.hasNext());
/* delete with single OVERLAPS for int */
res = table.delete().where("10004 overlaps doc->$.F4").execute();
assertEquals(res.getAffectedItemsCount(), 1);
rows = table.select("doc->$.F1 as F1").where("10004 overlaps doc->$.F4").execute();
assertFalse(rows.hasNext());
}
Also used :
DbDoc(com.mysql.cj.xdevapi.DbDoc)
RowResult(com.mysql.cj.xdevapi.RowResult)
DbDocImpl(com.mysql.cj.xdevapi.DbDocImpl)
Table(com.mysql.cj.xdevapi.Table)
JsonNumber(com.mysql.cj.xdevapi.JsonNumber)
JsonString(com.mysql.cj.xdevapi.JsonString)
JsonString(com.mysql.cj.xdevapi.JsonString)
DocResult(com.mysql.cj.xdevapi.DocResult)
RowResult(com.mysql.cj.xdevapi.RowResult)
Result(com.mysql.cj.xdevapi.Result)
AddResult(com.mysql.cj.xdevapi.AddResult)
DocResult(com.mysql.cj.xdevapi.DocResult)
SqlResult(com.mysql.cj.xdevapi.SqlResult)
Test(org.junit.jupiter.api.Test)
Example 13 with DbDocImpl
use of com.mysql.cj.xdevapi.DbDocImpl in project aws-mysql-jdbc by awslabs.
the class CollectionFindTest method testCollectionFindInInvalid.
@Test
public void testCollectionFindInInvalid() throws Exception {
assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");
int i = 0, j = 0, maxrec = 8, minArraySize = 3;
DocResult docs = null;
String json = "";
for (i = 0; i < maxrec; i++) {
DbDoc newDoc2 = new DbDocImpl();
newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
newDoc2.add("F1", new JsonNumber().setValue(String.valueOf(i + 1)));
JsonArray jarray = new JsonArray();
for (j = 0; j < (minArraySize + i); j++) {
jarray.addValue(new JsonString().setValue("Field-1-Data-" + i));
}
newDoc2.add("ARR1", jarray);
this.collection.add(newDoc2).execute();
newDoc2 = null;
jarray = null;
}
assertEquals((maxrec), this.collection.count());
/* add(DbDoc[] docs) */
DbDoc[] jsonlist = new DbDocImpl[maxrec];
for (i = 0; i < maxrec; i++) {
DbDoc newDoc2 = new DbDocImpl();
newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1100)));
newDoc2.add("ARR1", new JsonString().setValue("Field-1-Data-" + i));
newDoc2.add("F2", new JsonString().setValue("10-15-201" + i));
jsonlist[i] = newDoc2;
newDoc2 = null;
}
this.collection.add(jsonlist).execute();
json = "{\"_id\":\"1201\",\"XYZ\":2222, \"DATAX\":{\"D1\":1, \"D2\":2, \"D3\":3}}";
this.collection.add(json).execute();
/* find with invalid IN in document */
try {
docs = this.collection.find("{\"D1\":3, \"D2\":2, \"D3\":3} in $.DATAX").execute();
assertFalse(docs.hasNext());
} catch (XProtocolError Ex) {
Ex.printStackTrace();
if (Ex.getErrorCode() != MysqlErrorNumbers.ER_BAD_NULL_ERROR) {
throw Ex;
}
}
/* find with IN that does not match */
docs = this.collection.find("\"2222\" in $.XYZ").execute();
assertFalse(docs.hasNext());
/* find with NULL IN */
docs = this.collection.find("NULL in $.ARR1").execute();
assertFalse(docs.hasNext());
/* find with NULL IN */
docs = this.collection.find("NULL in $.DATAX").execute();
assertFalse(docs.hasNext());
/* find with IN for non existant key */
docs = this.collection.find("\"ABC\" in $.nonexistant").execute();
assertFalse(docs.hasNext());
}
Also used :
JsonArray(com.mysql.cj.xdevapi.JsonArray)
DbDoc(com.mysql.cj.xdevapi.DbDoc)
DbDocImpl(com.mysql.cj.xdevapi.DbDocImpl)
XProtocolError(com.mysql.cj.protocol.x.XProtocolError)
JsonNumber(com.mysql.cj.xdevapi.JsonNumber)
JsonString(com.mysql.cj.xdevapi.JsonString)
JsonString(com.mysql.cj.xdevapi.JsonString)
DocResult(com.mysql.cj.xdevapi.DocResult)
Test(org.junit.jupiter.api.Test)
Example 14 with DbDocImpl
use of com.mysql.cj.xdevapi.DbDocImpl in project aws-mysql-jdbc by awslabs.
the class CollectionFindTest method testCollectionFindInSanity.
@Test
public void testCollectionFindInSanity() throws Exception {
assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");
int i = 0, maxrec = 10;
DbDoc doc = null;
DocResult docs = null;
DbDoc[] jsonlist = new DbDocImpl[maxrec];
for (i = 0; i < maxrec; i++) {
DbDoc newDoc2 = new DbDocImpl();
newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(10 * (i + 1) + 0.1234)));
newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(i + 1)));
newDoc2.add("F4", new JsonNumber().setValue(String.valueOf(10000 - i)));
jsonlist[i] = newDoc2;
newDoc2 = null;
}
this.collection.add(jsonlist).execute();
assertEquals((maxrec), this.collection.count());
/* find without Condition */
docs = this.collection.find().fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3,$.F2/10 as tmp1,1/2 as tmp2").orderBy("$.F3").execute();
i = 0;
while (docs.hasNext()) {
doc = docs.next();
i++;
}
assertEquals((maxrec), i);
/* find with single element IN which uses json_contains */
docs = this.collection.find("'1001' in $._id").execute();
doc = docs.next();
assertEquals("1001", (((JsonString) doc.get("_id")).getString()));
assertFalse(docs.hasNext());
/* find with multiple IN which uses json_contains */
String findCond = "";
for (i = 1; i < maxrec; i++) {
findCond = findCond + "'";
findCond = findCond + String.valueOf(i + 1000) + "' not in $._id";
if (i != maxrec - 1) {
findCond = findCond + " and ";
}
}
docs = this.collection.find(findCond).execute();
doc = docs.next();
assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
assertFalse(docs.hasNext());
/* find with single IN for string with orderBy */
docs = this.collection.find("'Field-1-Data-2' in $.F1").orderBy("CAST($.F4 as SIGNED)").execute();
doc = docs.next();
assertEquals(String.valueOf(1002), (((JsonString) doc.get("_id")).getString()));
assertFalse(docs.hasNext());
/* find with single IN for numeric with orderBy */
docs = this.collection.find("10000 in $.F4").orderBy("CAST($.F4 as SIGNED)").execute();
doc = docs.next();
assertEquals(String.valueOf(1000), (((JsonString) doc.get("_id")).getString()));
assertFalse(docs.hasNext());
/* find with single IN for float with orderBy */
docs = this.collection.find("20.1234 in $.F2").orderBy("CAST($.F4 as SIGNED)").execute();
doc = docs.next();
assertEquals(String.valueOf(1001), (((JsonString) doc.get("_id")).getString()));
assertFalse(docs.hasNext());
/* Testing with table */
Table table = this.schema.getCollectionAsTable(this.collectionName);
/* find with single IN for string */
RowResult rows = table.select("doc->$._id as _id").where("'1001' in doc->$._id").execute();
Row r = rows.next();
assertEquals(r.getString("_id"), "\"1001\"");
assertFalse(rows.hasNext());
/* find with multiple IN in single select */
findCond = "";
for (i = 1; i < maxrec; i++) {
findCond = findCond + "'";
findCond = findCond + String.valueOf(i + 1000) + "' not in doc->$._id";
if (i != maxrec - 1) {
findCond = findCond + " and ";
}
}
/* find with single IN for string */
rows = table.select("doc->$._id as _id").where(findCond).execute();
r = rows.next();
assertEquals(r.getString("_id"), "\"1000\"");
assertFalse(rows.hasNext());
/* find with single IN for float */
rows = table.select("doc->$._id as _id").where("20.1234 in doc->$.F2").execute();
r = rows.next();
assertEquals(r.getString("_id"), "\"1001\"");
assertFalse(rows.hasNext());
/* find with single IN for string */
rows = table.select("doc->$._id as _id").where("'Field-1-Data-2' in doc->$.F1").execute();
r = rows.next();
assertEquals(r.getString("_id"), "\"1002\"");
assertFalse(rows.hasNext());
/* find with single IN for numeric */
rows = table.select("doc->$._id as _id").where("10000 in doc->$.F4").execute();
r = rows.next();
assertEquals(r.getString("_id"), "\"1000\"");
assertFalse(rows.hasNext());
}
Also used :
DbDoc(com.mysql.cj.xdevapi.DbDoc)
RowResult(com.mysql.cj.xdevapi.RowResult)
DbDocImpl(com.mysql.cj.xdevapi.DbDocImpl)
Table(com.mysql.cj.xdevapi.Table)
JsonNumber(com.mysql.cj.xdevapi.JsonNumber)
JsonString(com.mysql.cj.xdevapi.JsonString)
JsonString(com.mysql.cj.xdevapi.JsonString)
Row(com.mysql.cj.xdevapi.Row)
DocResult(com.mysql.cj.xdevapi.DocResult)
Test(org.junit.jupiter.api.Test)
Example 15 with DbDocImpl
use of com.mysql.cj.xdevapi.DbDocImpl in project aws-mysql-jdbc by awslabs.
the class CollectionFindTest method testCollectionFindWithBitOperation.
/* |,&,^,<<,>>,~ */
@Test
public void testCollectionFindWithBitOperation() throws Exception {
assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");
int i = 0, maxrec = 10;
int SLen = 1;
DbDoc doc = null;
DbDoc[] jsonlist = new DbDocImpl[maxrec];
for (i = 0; i < maxrec; i++) {
DbDoc newDoc2 = new DbDocImpl();
newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1 + 1000)));
newDoc2.add("F1", new JsonNumber().setValue(String.valueOf(i + 1)));
newDoc2.add("F2", new JsonNumber().setValue(String.valueOf((int) Math.pow(2, (i + 1)))));
newDoc2.add("F3", new JsonString().setValue(buildString(SLen + i, 'q')));
// newDoc2.add("F3", new JsonString().setValue("?????"));
jsonlist[i] = newDoc2;
newDoc2 = null;
}
this.collection.add(jsonlist).execute();
assertEquals((maxrec), this.collection.count());
/* find With bitwise | Condition */
DocResult docs = this.collection.find("CAST($.F2 as SIGNED) | pow(2,$.F1) = $.F2 ").fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3 , $.F2 | pow(2,$.F1) as tmp").execute();
i = 0;
while (docs.hasNext()) {
doc = docs.next();
assertEquals(String.valueOf(i + 1 + 1000), (((JsonString) doc.get("_id")).getString()));
assertEquals((long) (i + 1), (long) (((JsonNumber) doc.get("f1")).getInteger()));
assertEquals((long) ((int) Math.pow(2, (i + 1))), (long) (((JsonNumber) doc.get("f2")).getInteger()));
assertEquals(buildString(SLen + i, 'q'), (((JsonString) doc.get("f3")).getString()));
assertEquals((long) Math.pow(2, (i + 1)), (long) (((JsonNumber) doc.get("tmp")).getInteger()));
i++;
}
assertEquals((maxrec), i);
/* find With bitwise & Condition */
docs = this.collection.find("CAST($.F2 as SIGNED) & 64 ").fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3 , $.F2 & 64 as tmp").execute();
doc = docs.next();
assertEquals((long) 64, (long) (((JsonNumber) doc.get("f2")).getInteger()));
assertEquals((long) 64, (long) (((JsonNumber) doc.get("tmp")).getInteger()));
assertFalse(docs.hasNext());
/* find With bitwise | Condition */
docs = this.collection.find("CAST($.F2 as SIGNED) | $.F1 = 37 ").fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3 ,$.F2 | $.F1 as tmp").execute();
doc = docs.next();
assertEquals((long) 32, (long) (((JsonNumber) doc.get("f2")).getInteger()));
assertEquals((long) 37, (long) (((JsonNumber) doc.get("tmp")).getInteger()));
assertFalse(docs.hasNext());
/* find With bitwise << Condition */
docs = this.collection.find("CAST($.F2 as SIGNED) = 1<<4 ").fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3 , 1<<4 as tmp").execute();
doc = docs.next();
assertEquals((long) 16, (long) (((JsonNumber) doc.get("f2")).getInteger()));
assertEquals((long) 16, (long) (((JsonNumber) doc.get("tmp")).getInteger()));
assertFalse(docs.hasNext());
/* find With bitwise >> Condition */
docs = this.collection.find("CAST($.F2 as SIGNED) = 32>>4 ").fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3 , 32>>4 as tmp").execute();
doc = docs.next();
assertEquals((long) 2, (long) (((JsonNumber) doc.get("f2")).getInteger()));
assertEquals((long) 2, (long) (((JsonNumber) doc.get("tmp")).getInteger()));
assertFalse(docs.hasNext());
/* find With bitwise ^ Condition */
docs = this.collection.find("CAST($.F2 as SIGNED) ^ 1 = 17").fields("$._id as _id,$.F2 as f2, $.F2 ^ 1 as tmp").execute();
i = 0;
while (docs.hasNext()) {
doc = docs.next();
assertEquals((long) 16, (long) (((JsonNumber) doc.get("f2")).getInteger()));
assertEquals((long) 17, (long) (((JsonNumber) doc.get("tmp")).getInteger()));
i++;
}
assertFalse(docs.hasNext());
this.collection.add("{\"x1\":\"31\", \"x2\":\"13\", \"x3\":\"8\", \"x4\":\"18446744073709551614\"}").execute();
/* find With bitwise ~ Condition **********FAILING************ */
docs = this.collection.find("~16 = ~CAST($.F2 as SIGNED)").fields("$._id as _id,$.F2 as f2, ~1 as tmp").execute();
i = 0;
while (docs.hasNext()) {
doc = docs.next();
assertEquals((long) 16, (long) (((JsonNumber) doc.get("f2")).getInteger()));
// assertEquals(17, (((JsonNumber) doc.get("tmp")).getInteger()));
i++;
}
assertFalse(docs.hasNext());
}
Also used :
DbDoc(com.mysql.cj.xdevapi.DbDoc)
DbDocImpl(com.mysql.cj.xdevapi.DbDocImpl)
JsonNumber(com.mysql.cj.xdevapi.JsonNumber)
JsonString(com.mysql.cj.xdevapi.JsonString)
DocResult(com.mysql.cj.xdevapi.DocResult)
Test(org.junit.jupiter.api.Test)
Aggregations
DbDocImpl (com.mysql.cj.xdevapi.DbDocImpl)57
JsonString (com.mysql.cj.xdevapi.JsonString)57
DbDoc (com.mysql.cj.xdevapi.DbDoc)56
Test (org.junit.jupiter.api.Test)56
DocResult (com.mysql.cj.xdevapi.DocResult)52
JsonNumber (com.mysql.cj.xdevapi.JsonNumber)50
AddResult (com.mysql.cj.xdevapi.AddResult)21
Result (com.mysql.cj.xdevapi.Result)19
JsonArray (com.mysql.cj.xdevapi.JsonArray)15
RowResult (com.mysql.cj.xdevapi.RowResult)11
Row (com.mysql.cj.xdevapi.Row)9
SqlResult (com.mysql.cj.xdevapi.SqlResult)8
Table (com.mysql.cj.xdevapi.Table)8
BigDecimal (java.math.BigDecimal)7
ExecutionException (java.util.concurrent.ExecutionException)7
ArrayList (java.util.ArrayList)4
HashMap (java.util.HashMap)4
CompletableFuture (java.util.concurrent.CompletableFuture)4
Collection (com.mysql.cj.xdevapi.Collection)2
Session (com.mysql.cj.xdevapi.Session)2
