/*
* 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.core.statement;
import static java.util.stream.Collectors.toList;
import static java.util.stream.Collectors.toMap;
import static org.assertj.core.api.Assertions.assertThat;
import static org.assertj.core.api.Assertions.assertThatThrownBy;
import static org.assertj.core.api.Assertions.entry;
import static org.jdbi.v3.core.locator.ClasspathSqlLocator.findSqlOnClasspath;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Optional;
import com.google.common.collect.Maps;
import org.jdbi.v3.core.Handle;
import org.jdbi.v3.core.Something;
import org.jdbi.v3.core.result.NoResultsException;
import org.jdbi.v3.core.result.ResultIterable;
import org.jdbi.v3.core.result.ResultIterator;
import org.jdbi.v3.core.rule.H2DatabaseRule;
import org.junit.After;
import org.junit.Before;
import org.junit.Rule;
import org.junit.Test;
import org.junit.rules.ExpectedException;
public class TestQueries
{
@Rule
public H2DatabaseRule dbRule = new H2DatabaseRule();
@Rule
public ExpectedException expectedException = ExpectedException.none();
private Handle h;
@Before
public void setUp() throws Exception
{
h = dbRule.openHandle();
}
@After
public void doTearDown() throws Exception
{
if (h != null) h.close();
}
@Test
public void testCreateQueryObject() throws Exception
{
h.createUpdate("insert into something (id, name) values (1, 'eric')").execute();
h.createUpdate("insert into something (id, name) values (2, 'brian')").execute();
List<Map<String, Object>> results = h.createQuery("select * from something order by id").mapToMap().list();
assertThat(results).hasSize(2);
assertThat(results.get(0).get("name")).isEqualTo("eric");
}
@Test
public void testMappedQueryObject() throws Exception
{
h.execute("insert into something (id, name) values (1, 'eric')");
h.execute("insert into something (id, name) values (2, 'brian')");
ResultIterable<Something> query = h.createQuery("select * from something order by id").mapToBean(Something.class);
List<Something> r = query.list();
assertThat(r.get(0)).isEqualTo(new Something(1, "eric"));
}
@Test
public void testMappedQueryObjectWithNulls() throws Exception
{
h.execute("insert into something (id, name, integerValue) values (1, 'eric', null)");
ResultIterable<Something> query = h.createQuery("select * from something order by id").mapToBean(Something.class);
List<Something> r = query.list();
Something eric = r.get(0);
assertThat(eric).isEqualTo(new Something(1, "eric"));
assertThat(eric.getIntegerValue()).isNull();
}
@Test
public void testMappedQueryObjectWithNullForPrimitiveIntField() throws Exception
{
h.execute("insert into something (id, name, intValue) values (1, 'eric', null)");
ResultIterable<Something> query = h.createQuery("select * from something order by id").mapToBean(Something.class);
List<Something> r = query.list();
Something eric = r.get(0);
assertThat(eric).isEqualTo(new Something(1, "eric"));
assertThat(eric.getIntValue()).isZero();
}
@Test
public void testMapper() throws Exception
{
h.execute("insert into something (id, name) values (1, 'eric')");
h.execute("insert into something (id, name) values (2, 'brian')");
ResultIterable<String> query = h.createQuery("select name from something order by id").map((r, ctx) -> r.getString(1));
String name = query.list().get(0);
assertThat(name).isEqualTo("eric");
}
@Test
public void testConvenienceMethod() throws Exception
{
h.execute("insert into something (id, name) values (1, 'eric')");
h.execute("insert into something (id, name) values (2, 'brian')");
List<Map<String, Object>> r = h.select("select * from something order by id").mapToMap().list();
assertThat(r).hasSize(2);
assertThat(r.get(0).get("name")).isEqualTo("eric");
}
@Test
public void testConvenienceMethodWithParam() throws Exception
{
h.execute("insert into something (id, name) values (1, 'eric')");
h.execute("insert into something (id, name) values (2, 'brian')");
List<Map<String, Object>> r = h.select("select * from something where id = ?", 1).mapToMap().list();
assertThat(r).hasSize(1);
assertThat(r.get(0).get("name")).isEqualTo("eric");
}
@Test
public void testPositionalArgWithNamedParam() throws Exception
{
h.execute("insert into something (id, name) values (1, 'eric')");
h.execute("insert into something (id, name) values (2, 'brian')");
assertThatThrownBy(() ->
h.createQuery("select * from something where name = :name")
.bind(0, "eric")
.mapToBean(Something.class)
.list())
.isInstanceOf(UnableToExecuteStatementException.class)
.hasMessageContaining("no named parameter matches 'name'");
}
@Test
public void testMixedSetting() throws Exception
{
h.execute("insert into something (id, name) values (1, 'eric')");
h.execute("insert into something (id, name) values (2, 'brian')");
assertThatThrownBy(() ->
h.createQuery("select * from something where name = :name and id = :id")
.bind(0, "eric")
.bind("id", 1)
.mapToBean(Something.class)
.list())
.isInstanceOf(UnableToExecuteStatementException.class)
.hasMessageContaining("no named parameter matches 'name'");
}
@Test(expected = UnableToExecuteStatementException.class)
public void testHelpfulErrorOnNothingSet() throws Exception
{
h.createQuery("select * from something where name = :name").mapToMap().list();
}
@Test
public void testFirstResult() throws Exception
{
h.execute("insert into something (id, name) values (1, 'eric')");
h.execute("insert into something (id, name) values (2, 'brian')");
Something r = h.createQuery("select * from something order by id")
.mapToBean(Something.class)
.findFirst()
.get();
assertThat(r.getName()).isEqualTo("eric");
}
@Test
public void testIteratedResult() throws Exception
{
h.execute("insert into something (id, name) values (1, 'eric')");
h.execute("insert into something (id, name) values (2, 'brian')");
try (ResultIterator<Something> i = h.createQuery("select * from something order by id")
.mapToBean(Something.class)
.iterator()) {
assertThat(i.hasNext()).isTrue();
Something first = i.next();
assertThat(first.getName()).isEqualTo("eric");
assertThat(i.hasNext()).isTrue();
Something second = i.next();
assertThat(second.getId()).isEqualTo(2);
assertThat(i.hasNext()).isFalse();
}
}
@Test
public void testIteratorBehavior() throws Exception
{
h.execute("insert into something (id, name) values (1, 'eric')");
h.execute("insert into something (id, name) values (2, 'brian')");
try (ResultIterator<Something> i = h.createQuery("select * from something order by id")
.mapToBean(Something.class)
.iterator()) {
assertThat(i.hasNext()).isTrue();
Something first = i.next();
assertThat(first.getName()).isEqualTo("eric");
assertThat(i.hasNext()).isTrue();
Something second = i.next();
assertThat(second.getId()).isEqualTo(2);
assertThat(i.hasNext()).isFalse();
}
}
@Test
public void testIteratorBehavior2() throws Exception
{
h.execute("insert into something (id, name) values (1, 'eric')");
h.execute("insert into something (id, name) values (2, 'brian')");
try (ResultIterator<Something> i = h.createQuery("select * from something order by id")
.mapToBean(Something.class)
.iterator()) {
Something first = i.next();
assertThat(first.getName()).isEqualTo("eric");
Something second = i.next();
assertThat(second.getId()).isEqualTo(2);
assertThat(i.hasNext()).isFalse();
}
}
@Test
public void testIteratorBehavior3() throws Exception
{
h.execute("insert into something (id, name) values (1, 'eric')");
h.execute("insert into something (id, name) values (2, 'eric')");
assertThat(h.createQuery("select * from something order by id").mapToBean(Something.class))
.extracting(Something::getName)
.containsExactly("eric", "eric");
}
@Test
public void testFetchSize() throws Exception
{
h.createScript(findSqlOnClasspath("default-data")).execute();
ResultIterable<Something> ri = h.createQuery("select id, name from something order by id")
.setFetchSize(1)
.mapToBean(Something.class);
ResultIterator<Something> r = ri.iterator();
assertThat(r.hasNext()).isTrue();
r.next();
assertThat(r.hasNext()).isTrue();
r.next();
assertThat(r.hasNext()).isFalse();
}
@Test
public void testFirstWithNoResult() throws Exception
{
Optional<Something> s = h.createQuery("select id, name from something").mapToBean(Something.class).findFirst();
assertThat(s.isPresent()).isFalse();
}
@Test
public void testNullValueInColumn() throws Exception
{
h.execute("insert into something (id, name) values (?, ?)", 1, null);
Optional<String> s = h.createQuery("select name from something where id=1").mapTo(String.class).findFirst();
assertThat(s.isPresent()).isFalse();
}
@Test
public void testListWithMaxRows() throws Exception
{
h.prepareBatch("insert into something (id, name) values (?, ?)")
.add(1, "Brian")
.add(2, "Keith")
.add(3, "Eric")
.execute();
assertThat(h.createQuery("select id, name from something")
.mapToBean(Something.class)
.withStream(stream -> stream.limit(1).count())
.longValue()).isEqualTo(1);
assertThat(h.createQuery("select id, name from something")
.mapToBean(Something.class)
.withStream(stream -> stream.limit(2).count())
.longValue()).isEqualTo(2);
}
@Test
public void testFold() throws Exception
{
h.prepareBatch("insert into something (id, name) values (?, ?)")
.add(1, "Brian")
.add(2, "Keith")
.execute();
Map<String, Integer> rs = h.createQuery("select id, name from something")
.<Entry<String, Integer>>map((r, ctx) -> Maps.immutableEntry(r.getString("name"), r.getInt("id")))
.collect(toMap(Entry::getKey, Entry::getValue));
assertThat(rs).containsOnly(entry("Brian", 1), entry("Keith", 2));
}
@Test
public void testCollectList() throws Exception
{
h.prepareBatch("insert into something (id, name) values (?, ?)")
.add(1, "Brian")
.add(2, "Keith")
.execute();
List<String> rs = h.createQuery("select name from something order by id")
.mapTo(String.class)
.collect(toList());
assertThat(rs).containsExactly("Brian", "Keith");
}
@Test
public void testUsefulArgumentOutputForDebug() throws Exception
{
expectedException.expect(StatementException.class);
expectedException.expectMessage("arguments:{ positional:{7:8}, named:{name:brian}, finder:[{one=two},{lazy bean property arguments \"java.lang.Object");
h.createUpdate("insert into something (id, name) values (:id, :name)")
.bind("name", "brian")
.bind(7, 8)
.bindMap(new HandyMapThing<String>().add("one", "two"))
.bindBean(new Object())
.execute();
}
@Test
public void testStatementCustomizersPersistAfterMap() throws Exception
{
h.execute("insert into something (id, name) values (?, ?)", 1, "hello");
h.execute("insert into something (id, name) values (?, ?)", 2, "world");
List<Something> rs = h.createQuery("select id, name from something")
.setMaxRows(1)
.mapToBean(Something.class)
.list();
assertThat(rs).hasSize(1);
}
@Test
public void testQueriesWithNullResultSets() throws Exception
{
expectedException.expect(NoResultsException.class);
h.select("insert into something (id, name) values (?, ?)", 1, "hello").mapToMap().list();
}
}