/* * 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.sqlobject; import static java.util.Arrays.asList; import static org.assertj.core.api.Assertions.assertThat; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.UUID; import org.jdbi.v3.core.Handle; import org.jdbi.v3.core.Jdbi; import org.jdbi.v3.core.Something; import org.jdbi.v3.core.mapper.SomethingMapper; import org.jdbi.v3.core.result.ResultIterable; import org.jdbi.v3.core.rule.H2DatabaseRule; import org.jdbi.v3.sqlobject.config.RegisterRowMapper; import org.jdbi.v3.sqlobject.customizer.Bind; import org.jdbi.v3.sqlobject.customizer.BindBean; import org.jdbi.v3.sqlobject.statement.BatchChunkSize; 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.statement.UseRowMapper; import org.junit.Rule; import org.junit.Test; import com.google.common.collect.ImmutableList; import com.google.common.collect.ImmutableMap; public class TestDocumentation { @Rule public H2DatabaseRule dbRule = new H2DatabaseRule().withPlugin(new SqlObjectPlugin()); @Test public void testFiveMinuteFluentApi() throws Exception { try (Handle h = dbRule.openHandle()) { h.execute("insert into something (id, name) values (?, ?)", 1, "Brian"); String name = h.createQuery("select name from something where id = :id") .bind("id", 1) .mapTo(String.class) .findOnly(); assertThat(name).isEqualTo("Brian"); } } public interface MyDAO { @SqlUpdate("insert into something (id, name) values (:id, :name)") void insert(@Bind("id") int id, @Bind("name") String name); @SqlQuery("select name from something where id = :id") String findNameById(@Bind("id") int id); } @Test public void testFiveMinuteSqlObjectExample() throws Exception { dbRule.getJdbi().useExtension(MyDAO.class, dao -> { dao.insert(2, "Aaron"); String name = dao.findNameById(2); assertThat(name).isEqualTo("Aaron"); }); } @Test public void testObtainHandleViaOpen() throws Exception { try (Handle handle = dbRule.getJdbi().open()) { } } @Test public void testObtainHandleInCallback() throws Exception { Jdbi db = Jdbi.create("jdbc:h2:mem:" + UUID.randomUUID()); db.useHandle(handle -> handle.execute("create table silly (id int)")); } @Test public void testExecuteSomeStatements() throws Exception { try (Handle h = dbRule.openHandle()) { h.execute("insert into something (id, name) values (?, ?)", 3, "Patrick"); List<Map<String, Object>> rs = h.select("select id, name from something").mapToMap().list(); assertThat(rs).containsExactlyElementsOf(ImmutableList.of(ImmutableMap.of("id", 3L, "name", "Patrick"))); } } @Test public void testFluentUpdate() throws Exception { try (Handle h = dbRule.openHandle()) { h.createUpdate("insert into something(id, name) values (:id, :name)") .bind("id", 4) .bind("name", "Martin") .execute(); } } @Test public void testMappingExampleChainedIterator2() throws Exception { try (Handle h = dbRule.openHandle()) { h.execute("insert into something (id, name) values (1, 'Brian')"); h.execute("insert into something (id, name) values (2, 'Keith')"); Iterator<String> rs = h.createQuery("select name from something order by id") .mapTo(String.class) .iterator(); assertThat(rs.next()).isEqualTo("Brian"); assertThat(rs.next()).isEqualTo("Keith"); assertThat(rs.hasNext()).isFalse(); } } @Test public void testMappingExampleChainedIterator3() throws Exception { try (Handle h = dbRule.openHandle()) { h.execute("insert into something (id, name) values (1, 'Brian')"); h.execute("insert into something (id, name) values (2, 'Keith')"); ResultIterable<String> names = h.createQuery("select name from something order by id").mapTo(String.class); assertThat(names.iterator()).containsExactly("Brian", "Keith"); } } @Test public void testAttachToObject() throws Exception { try (Handle h = dbRule.openHandle()) { MyDAO dao = h.attach(MyDAO.class); dao.insert(1, "test"); } } @Test public void testOnDemandDao() throws Exception { MyDAO dao = dbRule.getJdbi().onDemand(MyDAO.class); dao.insert(2, "test"); } public interface SomeQueries { @SqlQuery("select name from something where id = :id") String findName(@Bind("id") int id); @SqlQuery("select name from something where id > :from and id < :to order by id") List<String> findNamesBetween(@Bind("from") int from, @Bind("to") int to); @SqlQuery("select name from something order by id") Iterator<String> findAllNames(); } @Test public void testSomeQueriesWorkCorrectly() throws Exception { try (Handle h = dbRule.openHandle()) { h.prepareBatch("insert into something (id, name) values (:id, :name)") .bind("id", 1).bind("name", "Brian").add() .bind("id", 2).bind("name", "Robert").add() .bind("id", 3).bind("name", "Patrick").add() .bind("id", 4).bind("name", "Maniax").add() .execute(); SomeQueries sq = h.attach(SomeQueries.class); assertThat(sq.findName(2)).isEqualTo("Robert"); assertThat(sq.findNamesBetween(1, 4)).containsExactly("Robert", "Patrick"); Iterator<String> names = sq.findAllNames(); assertThat(names).containsExactly("Brian", "Robert", "Patrick", "Maniax"); } } @RegisterRowMapper(SomethingMapper.class) public interface AnotherQuery { @SqlQuery("select id, name from something where id = :id") Something findById(@Bind("id") int id); } public interface YetAnotherQuery { @SqlQuery("select id, name from something where id = :id") @UseRowMapper(SomethingMapper.class) Something findById(@Bind("id") int id); } public interface BatchInserter { @SqlBatch("insert into something (id, name) values (:id, :name)") void insert(@BindBean Something... somethings); } @Test public void testAnotherCoupleInterfaces() throws Exception { try (Handle h = dbRule.openHandle()) { h.attach(BatchInserter.class).insert(new Something(1, "Brian"), new Something(3, "Patrick"), new Something(2, "Robert")); AnotherQuery aq = h.attach(AnotherQuery.class); YetAnotherQuery yaq = h.attach(YetAnotherQuery.class); assertThat(yaq.findById(3)).isEqualTo(new Something(3, "Patrick")); assertThat(aq.findById(2)).isEqualTo(new Something(2, "Robert")); } } public interface QueryReturningResultIterable { @SqlQuery("select name from something where id = :id") ResultIterable<String> findById(@Bind("id") int id); } @Test public void testFoo() throws Exception { try (Handle h = dbRule.openHandle()) { h.attach(BatchInserter.class).insert(new Something(1, "Brian"), new Something(3, "Patrick"), new Something(2, "Robert")); QueryReturningResultIterable qrri = h.attach(QueryReturningResultIterable.class); ResultIterable<String> iterable = qrri.findById(1); assertThat(iterable.findOnly()).isEqualTo("Brian"); } } public interface Update { @SqlUpdate("insert into something (id, name) values (:id, :name)") int insert(@Bind("id") int id, @Bind("name") String name); @SqlUpdate("update something set name = :name where id = :id") int update(@BindBean Something s); } @Test public void testUpdateAPI() throws Exception { try (Handle h = dbRule.openHandle()) { Update u = h.attach(Update.class); u.insert(17, "David"); u.update(new Something(17, "David P.")); String name = h.createQuery("select name from something where id = 17") .mapTo(String.class) .findOnly(); assertThat(name).isEqualTo("David P."); } } public interface BatchExample { @SqlBatch("insert into something (id, name) values (:id, :first || ' ' || :last)") void insertFamily(@Bind("id") List<Integer> ids, @Bind("first") Iterator<String> firstNames, @Bind("last") String lastName); @SqlQuery("select name from something where id = :id") String findNameById(@Bind("id") int id); } @Test public void testBatchExample() throws Exception { try (Handle h = dbRule.openHandle()) { BatchExample b = h.attach(BatchExample.class); List<Integer> ids = asList(1, 2, 3, 4, 5); Iterator<String> first_names = asList("Tip", "Jane", "Brian", "Keith", "Eric").iterator(); b.insertFamily(ids, first_names, "McCallister"); assertThat(b.findNameById(1)).isEqualTo("Tip McCallister"); assertThat(b.findNameById(2)).isEqualTo("Jane McCallister"); assertThat(b.findNameById(3)).isEqualTo("Brian McCallister"); assertThat(b.findNameById(4)).isEqualTo("Keith McCallister"); assertThat(b.findNameById(5)).isEqualTo("Eric McCallister"); } } public interface ChunkedBatchExample { @SqlBatch("insert into something (id, name) values (:id, :first || ' ' || :last)") @BatchChunkSize(2) void insertFamily(@Bind("id") List<Integer> ids, @Bind("first") Iterator<String> firstNames, @Bind("last") String lastName); @SqlUpdate("create table something(id int primary key, name varchar(32))") void createSomethingTable(); @SqlQuery("select name from something where id = :id") String findNameById(@Bind("id") int id); } public interface BindExamples { @SqlUpdate("insert into something (id, name) values (:id, :name)") void insert(@Bind("id") int id, @Bind("name") String name); @SqlUpdate("delete from something where name = :it") void deleteByName(@Bind String name); } public interface BindBeanExample { @SqlUpdate("insert into something (id, name) values (:id, :name)") void insert(@BindBean Something s); @SqlUpdate("update something set name = :s.name where id = :s.id") void update(@BindBean("s") Something something); } }