/*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.jdbi.v3.postgres;
import static org.assertj.core.api.Assertions.assertThat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;
import java.util.UUID;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.stream.Collectors;
import java.util.stream.IntStream;
import org.jdbi.v3.core.Handle;
import org.jdbi.v3.core.Something;
import org.jdbi.v3.core.mapper.SomethingMapper;
import org.jdbi.v3.sqlobject.customizer.BindBean;
import org.jdbi.v3.sqlobject.SingleValue;
import org.jdbi.v3.sqlobject.statement.SqlBatch;
import org.jdbi.v3.sqlobject.statement.SqlQuery;
import org.jdbi.v3.sqlobject.statement.SqlUpdate;
import org.jdbi.v3.sqlobject.config.RegisterRowMapper;
import org.junit.Before;
import org.junit.ClassRule;
import org.junit.Test;
public class TestSqlArrays {
private static final String U_SELECT = "SELECT u FROM uuids";
private static final String U_INSERT = "INSERT INTO uuids VALUES(:uuids, NULL)";
private static final String I_SELECT = "SELECT i FROM uuids";
private static final String I_INSERT = "INSERT INTO uuids VALUES(NULL, :ints)";
@ClassRule
public static PostgresDbRule db = new PostgresDbRule();
private Handle h;
private ArrayObject ao;
@Before
public void setUp() {
h = db.getSharedHandle();
h.useTransaction(th -> {
th.execute("DROP TABLE IF EXISTS uuids");
th.execute("CREATE TABLE uuids (u UUID[], i INT[])");
});
ao = h.attach(ArrayObject.class);
}
private final UUID[] testUuids = new UUID[] {
UUID.randomUUID(), UUID.randomUUID(), UUID.randomUUID(), UUID.randomUUID(), UUID.randomUUID()
};
private final int[] testInts = new int[] {
5, 4, -6, 1, 9, Integer.MAX_VALUE, Integer.MIN_VALUE
};
@Test
public void testUuidArray() throws Exception {
ao.insertUuidArray(testUuids);
assertThat(ao.fetchUuidArray()).containsExactly(testUuids);
}
@Test
public void testUuidList() throws Exception {
ao.insertUuidList(Arrays.asList(testUuids));
assertThat(ao.fetchUuidList()).contains(testUuids);
}
@Test
public void testUuidArrayList() throws Exception {
ao.insertUuidList(Arrays.asList(testUuids));
assertThat(ao.fetchUuidArrayList()).contains(testUuids);
}
@Test
public void testUuidLinkedList() throws Exception {
ao.insertUuidList(Arrays.asList(testUuids));
assertThat(ao.fetchUuidLinkedList()).contains(testUuids);
}
@Test
public void testUuidCopyOnWriteArrayList() throws Exception {
ao.insertUuidList(Arrays.asList(testUuids));
assertThat(ao.fetchUuidCopyOnWriteArrayList()).contains(testUuids);
}
@Test
public void testIntArray() throws Exception {
ao.insertIntArray(testInts);
int[] actuals = ao.fetchIntArray();
assertThat(actuals).containsExactly(testInts);
}
@Test
public void testEmptyIntArray() throws Exception {
ao.insertIntArray(new int[0]);
assertThat(ao.fetchIntArray()).isEmpty();
}
@Test
public void testBoxedIntArray() throws Exception {
Integer[] source = IntStream.of(testInts).mapToObj(Integer::valueOf).toArray(Integer[]::new);
ao.insertBoxedIntArray(source);
Integer[] actuals = ao.fetchBoxedIntArray();
assertThat(actuals).containsExactly(actuals);
}
@Test
public void testObjectArray() throws Exception {
ao.insertIntArray(testInts);
Object[] actuals = ao.fetchObjectArray();
Object[] expecteds = IntStream.of(testInts).mapToObj(Integer::valueOf).toArray(Object[]::new);
assertThat(actuals).containsExactly(expecteds);
}
@Test
public void testIntList() throws Exception {
List<Integer> testIntList = Arrays.stream(testInts).boxed().collect(Collectors.toList());
ao.insertIntList(testIntList);
assertThat(ao.fetchIntList()).containsExactlyElementsOf(testIntList);
}
@Test
public void testNullArray() throws Exception {
ao.insertUuidArray(null);
assertThat(ao.fetchUuidArray()).isNull();
}
@Test
public void testNullList() throws Exception {
ao.insertUuidList(null);
assertThat(ao.fetchUuidLinkedList()).isNull();
}
public interface ArrayObject {
@SqlQuery(U_SELECT)
@SingleValue
UUID[] fetchUuidArray();
@SqlUpdate(U_INSERT)
void insertUuidArray(UUID[] uuids);
@SqlQuery(U_SELECT)
@SingleValue
List<UUID> fetchUuidList();
@SqlQuery(U_SELECT)
@SingleValue
ArrayList<UUID> fetchUuidArrayList();
@SqlQuery(U_SELECT)
@SingleValue
LinkedList<UUID> fetchUuidLinkedList();
@SqlQuery(U_SELECT)
@SingleValue
CopyOnWriteArrayList<UUID> fetchUuidCopyOnWriteArrayList();
@SqlUpdate(U_INSERT)
void insertUuidList(List<UUID> uuids);
@SqlQuery(I_SELECT)
@SingleValue
int[] fetchIntArray();
@SqlQuery(I_SELECT)
@SingleValue
Integer[] fetchBoxedIntArray();
@SqlQuery(I_SELECT)
@SingleValue
Object[] fetchObjectArray();
@SqlUpdate(I_INSERT)
void insertIntArray(int[] ints);
@SqlUpdate(I_INSERT)
void insertBoxedIntArray(Integer[] ints);
@SqlQuery(I_SELECT)
@SingleValue
List<Integer> fetchIntList();
@SqlUpdate(I_INSERT)
void insertIntList(List<Integer> ints);
}
@Test
public void testWhereInArray() throws Exception {
WhereInDao dao = h.attach(WhereInDao.class);
dao.createTable();
Something a = new Something(1, "Alice");
Something b = new Something(2, "Bob");
Something c = new Something(3, "Candace");
Something d = new Something(4, "David");
Something e = new Something(5, "Emily");
dao.insert(a, b, c, d, e);
assertThat(dao.getByIds(1)).containsExactly(a);
assertThat(dao.getByIds(2)).containsExactly(b);
assertThat(dao.getByIds(3)).containsExactly(c);
assertThat(dao.getByIds(4)).containsExactly(d);
assertThat(dao.getByIds(5)).containsExactly(e);
assertThat(dao.getByIds(1, 2, 5)) // Three, sir!
.containsExactly(a, b, e);
}
@RegisterRowMapper(SomethingMapper.class)
public interface WhereInDao {
@SqlUpdate("create table something(id int, name text)")
void createTable();
@SqlBatch("insert into something(id, name) values (:id, :name)")
void insert(@BindBean Something... somethings);
@SqlQuery("select * from something where id = any(:ids) order by id")
List<Something> getByIds(int... ids);
}
}