@Test
public void testSingleQueryExecute() {
data.insert(randomPersons(10));
Result<Person> result = data.select(Person.class).get();
assertEquals(10, result.toList().size());
Person person = randomPerson();
data.insert(person);
assertEquals(11, result.toList().size());
}
@Test
public void testSingleQueryWhereNull() {
data.delete(Person.class).where(Person.NAME.isNull()).get();
Person person = randomPerson();
person.setName(null);
data.insert(person);
try (Result<Person> query = data.select(Person.class).where(Person.NAME.equal(null)).get()) {
assertEquals(1, query.toList().size());
}
try (Result<Person> query = data.select(Person.class).where(Person.NAME.isNull()).get()) {
assertEquals(1, query.toList().size());
}
}
@Test
public void testQuerySelectDistinct() {
for (int i = 0; i < 10; i++) {
Person person = randomPerson();
person.setName(String.valueOf(i / 2));
data.insert(person);
}
try (Result<Tuple> result = data.select(Person.NAME).distinct().get()) {
assertEquals(5, result.toList().size());
}
}
@Test
public void testSingleQueryLimitSkip() {
final String name = "duplicateFirstName";
data.delete(Person.class).where(Person.NAME.equal(name)).get();
for (int i = 0; i < 10; i++) {
Person person = randomPerson();
person.setName(name);
data.insert(person);
}
for (int i = 0; i < 3; i++) {
try (Result<Person> query =
data.select(Person.class).where(Person.NAME.equal(name)).limit(5).get()) {
assertEquals(5, query.toList().size());
}
try (Result<Person> query =
data.select(Person.class).where(Person.NAME.equal(name)).limit(5).offset(5).get()) {
assertEquals(5, query.toList().size());
}
}
}
@Test
public void testQueryCase() {
String[] names = new String[] {"Carol", "Bob", "Jack"};
for (String name : names) {
Person person = randomPerson();
person.setName(name);
data.insert(person);
}
Result<Tuple> a =
data.select(
Person.NAME,
Case.type(String.class)
.when(Person.NAME.equal("Bob"), "B")
.when(Person.NAME.equal("Carol"), "C")
.elseThen("Unknown"))
.from(Person.class)
.orderBy(Person.NAME)
.get();
List<Tuple> list = a.toList();
assertTrue(list.get(0).get(1).equals("B"));
assertTrue(list.get(1).get(1).equals("C"));
assertTrue(list.get(2).get(1).equals("Unknown"));
a =
data.select(
Person.NAME,
Case.type(Integer.class)
.when(Person.NAME.equal("Bob"), 1)
.when(Person.NAME.equal("Carol"), 2)
.elseThen(0))
.orderBy(Person.NAME)
.get();
list = a.toList();
assertTrue(list.get(0).get(1).equals(1));
assertTrue(list.get(1).get(1).equals(2));
assertTrue(list.get(2).get(1).equals(0));
}
@Test
public void testQueryGroupBy() {
for (int i = 0; i < 5; i++) {
Person person = randomPerson();
person.setAge(i);
data.insert(person);
}
try (Result<Tuple> result =
data.select(Person.AGE).groupBy(Person.AGE).having(Person.AGE.greaterThan(3)).get()) {
assertTrue(result.toList().size() == 1);
}
assertTrue(
data.select(Person.AGE)
.groupBy(Person.AGE)
.having(Person.AGE.lessThan(0))
.get()
.toList()
.isEmpty());
}
@Test
public void testQueryCoalesce() {
Person person = randomPerson();
person.setName("Carol");
person.setEmail(null);
data.insert(person);
person = randomPerson();
person.setName("Bob");
person.setEmail("*****@*****.**");
person.setHomepage(null);
data.insert(person);
Result<Tuple> result = data.select(Coalesce.coalesce(Person.EMAIL, Person.NAME)).get();
List<Tuple> list = result.toList();
List<String> values = new ArrayList<>();
for (Tuple tuple : list) {
values.add(tuple.get(0).toString());
}
assertEquals(values.size(), 2);
assertTrue(values.contains("Carol"));
assertTrue(values.contains("*****@*****.**"));
}
