TestSqlArrays.java

/*
 * 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);
    }
}