@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 testQueryFromSub() {
for (int i = 0; i < 10; i++) {
Person person = randomPerson();
person.setAge(i + 1);
data.insert(person);
}
Integer result =
data.select(NamedExpression.ofInteger("avg_age").avg())
.from(data.select(Person.AGE.sum().as("avg_age")).groupBy(Person.AGE).as("t1"))
.get()
.first()
.get(0);
assertTrue(result >= 5); // derby rounds up
}
@Test
public void testQueryLike() {
Person person = randomPerson();
person.setName("Carol");
data.insert(person);
person = randomPerson();
person.setName("Bob");
data.insert(person);
Person a = data.select(Person.class).where(Person.NAME.like("B%")).get().first();
assertSame(a, person);
a = data.select(Person.class).where(Person.NAME.lower().like("b%")).get().first();
assertSame(a, person);
Person b = data.select(Person.class).where(Person.NAME.notLike("B%")).get().firstOrNull();
assertTrue(b != person);
}
@Test
public void testListResult() {
data.delete(Person.class).get();
Person person = randomPerson();
data.insert(person);
assertEquals(1, data.select(Person.class).get().toList().size());
}
@Test
public void testFillResult() {
data.delete(Person.class).get();
Person person = randomPerson();
data.insert(person);
assertEquals(1, data.select(Person.class).get().collect(new HashSet<Person>()).size());
}
@Test
public void testInsertConcurrent() throws InterruptedException {
ExecutorService executorService = Executors.newFixedThreadPool(3);
final int count = 5;
final CountDownLatch latch = new CountDownLatch(count);
final Map<Integer, Person> map = new HashMap<>();
for (int i = 0; i < count; i++) {
executorService.submit(
new Callable<Person>() {
@Override
public Person call() throws Exception {
Person person = randomPerson();
data.insert(person);
assertTrue(person.getId() > 0);
map.put(person.getId(), person);
latch.countDown();
return person;
}
});
}
assertTrue(latch.await(10, TimeUnit.SECONDS));
for (Map.Entry<Integer, Person> entry : map.entrySet()) {
Person cached =
data.select(Person.class).where(Person.ID.equal(entry.getKey())).get().first();
assertSame(cached, entry.getValue());
}
executorService.shutdownNow();
}
@Test
public void testQueryCompoundConditions() {
Person person = randomPerson();
person.setAge(75);
data.insert(person);
Person p = data.select(Person.class).where(Person.AGE.gte(75)).get().first();
}
@Test
public void testQueryBetween() {
Person person = randomPerson();
person.setAge(75);
data.insert(person);
Person p = data.select(Person.class).where(Person.AGE.between(50, 100)).get().first();
assertTrue(p == person);
}
@Test
public void testInsert() {
Person person = randomPerson();
data.insert(person);
assertTrue(person.getId() > 0);
Person cached = data.select(Person.class).where(Person.ID.equal(person.getId())).get().first();
assertSame(cached, person);
}
@Test
public void testQueryNotNull() {
for (int i = 0; i < 10; i++) {
Person person = randomPerson();
data.insert(person);
}
assertNotNull(data.select(Person.class).where(Person.NAME.notNull()).get().first());
}
@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 testDeleteOne() {
Person person = randomPerson();
data.insert(person);
assertTrue(person.getId() > 0);
data.delete(person);
Person cached =
data.select(Person.class).where(Person.ID.equal(person.getId())).get().firstOrNull();
assertNull(cached);
}
@Test
public void testQuerySelectTrim() {
// TODO fix use ltrim/rtrim for SQLServer
Person person = randomPerson();
person.setName(" Name ");
data.insert(person);
Tuple result = data.select(Person.NAME.trim().as("name")).get().first();
String name = result.get(0);
assertEquals(name, "Name");
}
@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 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 testDeleteAll() {
final String name = "someName";
for (int i = 0; i < 10; i++) {
Person person = randomPerson();
person.setName(name);
data.insert(person);
}
assertTrue(data.delete(Person.class).get().value() > 0);
assertTrue(data.select(Person.class).get().toList().isEmpty());
}
@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 testQuerySelectCount() {
data.insert(randomPersons(10));
try (Result<Tuple> result = data.select(Count.count(Person.class).as("bb")).get()) {
assertTrue(result.first().get("bb").equals(10));
}
try (Result<Tuple> result = data.select(Count.count(Person.class)).get()) {
assertTrue(result.first().get(0).equals(10));
}
assertEquals(10, data.count(Person.class).get().value().intValue());
data.count(Person.class)
.get()
.consume(
new Consumer<Integer>() {
@Override
public void accept(Integer integer) {
assertTrue(integer == 10);
}
});
}
@Test
public void testQueryOrderBy() {
for (int i = 0; i < 5; i++) {
Person person = randomPerson();
person.setAge(i);
data.insert(person);
}
try (Result<Tuple> query = data.select(Person.AGE).orderBy(Person.AGE.desc()).get()) {
Integer i = query.first().get(0);
assertTrue(i.equals(4));
}
}
@Test
public void testQuerySelectWhereIn() {
final String name = "Hello!";
Person person = randomPerson();
person.setName(name);
data.insert(person);
Group group = new Group();
group.setName("Hello!");
data.insert(group);
person.getGroups().add(group);
data.update(person);
Return<Result<Tuple>> groupNames = data.select(Group.NAME).where(Group.NAME.equal(name));
Person p = data.select(Person.class).where(Person.NAME.in(groupNames)).get().first();
assertEquals(p.getName(), name);
p = data.select(Person.class).where(Person.NAME.notIn(groupNames)).get().firstOrNull();
assertNull(p);
p =
data.select(Person.class)
.where(Person.NAME.in(Arrays.asList("Hello!", "Other")))
.get()
.first();
assertEquals(p.getName(), name);
p =
data.select(Person.class)
.where(Person.NAME.in(Collections.singleton("Hello!")))
.get()
.first();
assertEquals(p.getName(), name);
assertTrue(
data.select(Person.class)
.where(Person.NAME.notIn(Collections.singleton("Hello!")))
.get()
.toList()
.isEmpty());
}
@Test
public void testDeleteCascadeOneToMany() {
Person person = randomPerson();
data.insert(person);
Phone phone1 = randomPhone();
phone1.setOwner(person);
data.insert(phone1);
int phoneId = phone1.getId();
person.getPhoneNumbers();
data.delete(person);
Phone phone = data.select(Phone.class).where(Phone.ID.equal(phoneId)).get().firstOrNull();
assertNull(phone);
}
@Test
public void testInsertBatch() {
List<Person> persons = new ArrayList<>();
for (int i = 0; i < 100; i++) {
Person person = randomPerson();
persons.add(person);
}
data.insert(persons);
int people = 0;
for (Person ignored : data.select(Person.class).get()) {
people++;
}
assertEquals(100, people);
}
@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 testQueryMap() {
int count = 10;
for (int i = 0; i < count; i++) {
Person person = randomPerson();
if (i == 0) {
person.setEmail("*****@*****.**");
}
data.insert(person);
}
Result<Person> result = data.select(Person.class).get();
Map<String, Person> map = result.toMap(Person.EMAIL, new ConcurrentHashMap<String, Person>());
assertNotNull(map.get("*****@*****.**"));
map = result.toMap(Person.EMAIL);
assertNotNull(map.get("*****@*****.**"));
}
@Test
public void testQueryConsume() {
int count = 10;
for (int i = 0; i < count; i++) {
Person person = randomPerson();
data.insert(person);
}
final int[] counts = new int[] {0};
Result<Person> result = data.select(Person.class).get();
result.each(
new Consumer<Person>() {
@Override
public void accept(Person e) {
counts[0] = counts[0] + 1;
}
});
assertEquals(counts[0], count);
}
@Test
public void testQueryUnion() {
Person person = randomPerson();
person.setName("Carol");
data.insert(person);
Group group = new Group();
group.setName("Hello!");
data.insert(group);
List<Tuple> result =
data.select(Person.NAME.as("name"))
.union()
.select(Group.NAME.as("name"))
.orderBy(Group.NAME.as("name"))
.get()
.toList();
assertTrue(result.size() == 2);
assertTrue(result.get(0).get(0).equals("Carol"));
assertTrue(result.get(1).get(0).equals("Hello!"));
}
@SuppressWarnings("MagicConstant")
@Test
public void testQuerySelectMin() {
for (int i = 0; i < 10; i++) {
Person person = randomPerson();
if (i == 9) {
Calendar calendar = Calendar.getInstance();
calendar.set(1800, Calendar.NOVEMBER, 11, 0, 0, 0);
person.setBirthday(calendar.getTime());
}
data.insert(person);
}
try (Result<Tuple> query = data.select(Person.BIRTHDAY.min().as("oldestBday")).get()) {
Date date = query.first().get("oldestBday");
Calendar c = Calendar.getInstance();
c.setTime(date);
assertTrue(c.get(Calendar.YEAR) == 1800); // just check the year...
}
}
@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("*****@*****.**"));
}
@Test
public void testTransactionRollback() {
ArrayList<Integer> ids = new ArrayList<>();
try (Transaction transaction = data.transaction().begin()) {
for (int i = 0; i < 10; i++) {
Person person = randomPerson();
data.insert(person);
assertTrue(person.getId() > 0);
ids.add(person.getId());
if (i == 5) {
throw new Exception("rollback...");
}
}
transaction.commit();
} catch (Exception ignored) {
}
for (Integer id : ids) {
Person p = data.select(Person.class).where(Person.ID.equal(id)).get().firstOrNull();
assertNull(p);
}
}
@Test
public void testQueryConditions() {
Person person = randomPerson();
person.setAge(75);
data.insert(person);
Person p = data.select(Person.class).where(Person.AGE.greaterThanOrEqual(75)).get().first();
assertSame(p, person);
p = data.select(Person.class).where(Person.AGE.lessThanOrEqual(75)).get().first();
assertSame(p, person);
p = data.select(Person.class).where(Person.AGE.greaterThan(75)).get().firstOrNull();
assertNull(p);
p = data.select(Person.class).where(Person.AGE.notEqual(75)).get().firstOrNull();
assertNull(p);
p = data.select(Person.class).where(Person.AGE.gte(75)).get().first();
assertSame(p, person);
assertSame(p, person);
p = data.select(Person.class).where(Person.AGE.lte(75)).get().first();
assertSame(p, person);
p = data.select(Person.class).where(Person.AGE.gt(75)).get().firstOrNull();
assertNull(p);
p = data.select(Person.class).where(Person.AGE.ne(75)).get().firstOrNull();
assertNull(p);
}