content: package org.sql2o;
  
  import com.google.common.collect.ImmutableList;
  import org.hsqldb.jdbc.JDBCDataSource;
  import org.joda.time.DateTime;
  import org.joda.time.LocalTime;
  import org.joda.time.Period;
  import org.junit.Test;
  import org.junit.runner.RunWith;
  import org.junit.runners.Parameterized;
  import org.sql2o.data.LazyTable;
  import org.sql2o.data.Row;
  import org.sql2o.data.Table;
  import org.sql2o.pojos.*;
  import org.sql2o.tools.IOUtils;
  
  import javax.naming.Context;
  import javax.naming.InitialContext;
  import java.io.ByteArrayInputStream;
  import java.io.IOException;
  import java.io.InputStream;
  import java.math.BigDecimal;
  import java.sql.SQLException;
  import java.sql.Time;
  import java.sql.Timestamp;
  import java.time.OffsetTime;
  import java.util.*;
  
  import static org.hamcrest.CoreMatchers.*;
  import static org.junit.Assert.*;
  import static org.sql2o.connectionsources.ConnectionSources.join;
  
  /**
   * Created by IntelliJ IDEA.
   * User: lars
   * Date: 5/21/11
   * Time: 9:25 PM
   * Most sql2o tests are in this class.
   */
  @RunWith(Parameterized.class)
  public class Sql2oTest extends BaseMemDbTest {
  
      private static final int NUMBER_OF_USERS_IN_THE_TEST = 10000;
  
      private int insertIntoUsers = 0;
  
      public Sql2oTest(DbType dbType, String testName) {
          super(dbType, testName);
      }
  
      @Test
      public void testExecuteAndFetch(){
          createAndFillUserTable();
  
          try (Connection con = sql2o.open()) {
  
              Date before = new Date();
              List<User> allUsers = con.createQuery("select * from user_table").executeAndFetch(User.class);
  
  
              assertNotNull(allUsers);
  
              Date after = new Date();
              long span = after.getTime() - before.getTime();
              System.out.println(String.format("Fetched %s user: %s ms", insertIntoUsers, span));
  
              // repeat this
              before = new Date();
              allUsers = con.createQuery("select * from user_table").executeAndFetch(User.class);
              after = new Date();
              span = after.getTime() - before.getTime();
              System.out.println(String.format("Again Fetched %s user: %s ms", insertIntoUsers, span));
  
              assertTrue(allUsers.size() == insertIntoUsers);
  
          }
          deleteUserTable();
      }
  
      @Test
      public void testExecuteAndFetchUniqueWhenUnique(){
          createAndFillUserTable();
  
          try (Connection con = sql2o.open()) {
  
              User user = con.createQuery("select * from user_table u where u.id = 1").executeAndFetchUnique(User.class);
  
              assertNotNull(user);
          }
  
          deleteUserTable();
      }
  
      @Test(expected = Sql2oException.class)
      public void testExecuteAndFetchUniqueWhenNotUnique(){
          createAndFillUserTable();
  
          try (Connection con = sql2o.open()) {
  
              User user = con.createQuery("select * from user_table u where u.id > 0").executeAndFetchUnique(User.class);
          }
          finally {
              deleteUserTable();
          }
      }
  
      @Test
      public void testExecuteAndFetchWithNulls(){
          String sql =
                  "create table testExecWithNullsTbl (" +
                  "id int identity primary key, " +
                  "text varchar(255), " +
                  "aNumber int, " +
                  "aLongNumber bigint)";
          try (Connection con = sql2o.open()) {
              con.createQuery(sql, "testExecuteAndFetchWithNulls").executeUpdate();
  
  
              Connection connection = sql2o.beginTransaction();
              Query insQuery = connection.createQuery("insert into testExecWithNullsTbl (text, aNumber, aLongNumber) values(:text, :number, :lnum)");
              insQuery.addParameter("text", "some text").addParameter("number", 2).addParameter("lnum", 10L).executeUpdate();
              insQuery.addParameter("text", "some text").addParameter("number", (Integer) null).addParameter("lnum", 10L).executeUpdate();
              insQuery.addParameter("text", (String) null).addParameter("number", 21).addParameter("lnum", (Long) null).executeUpdate();
              insQuery.addParameter("text", "some text").addParameter("number", 1221).addParameter("lnum", 10).executeUpdate();
              insQuery.addParameter("text", "some text").addParameter("number", 2311).addParameter("lnum", 12).executeUpdate();
              connection.commit();
  
              List<Entity> fetched = con.createQuery("select * from testExecWithNullsTbl").executeAndFetch(Entity.class);
  
              assertTrue(fetched.size() == 5);
              assertNull(fetched.get(2).text);
              assertNotNull(fetched.get(3).text);
  
              assertNull(fetched.get(1).aNumber);
              assertNotNull(fetched.get(2).aNumber);
  
              assertNull(fetched.get(2).aLongNumber);
              assertNotNull(fetched.get(3).aLongNumber);
          }
      }
  
      @Test
      public void testBatch(){
          sql2o.createQuery(
                  "create table user_table(\n" +
                  "id int identity primary key,\n" +
                  "name varchar(20),\n" +
                  "email varchar(255),\n" +
                  "text varchar(100))").executeUpdate();
  
          String insQuery = "insert into user_table(name, email, text) values (:name, :email, :text)";
  
          Connection con = sql2o.beginTransaction();
          int[] inserted = con.createQuery(insQuery).addParameter("name", "test").addParameter("email", "test@test.com").addParameter("text", "something exciting").addToBatch()
                  .addParameter("name", "test2").addParameter("email", "test2@test.com").addParameter("text", "something exciting too").addToBatch()
                  .addParameter("name", "test3").addParameter("email", "test3@test.com").addParameter("text", "blablabla").addToBatch()
                  .executeBatch().getBatchResult();
          con.commit();
  
          assertEquals(3, inserted.length);
          for (int i : inserted){
              assertEquals(1, i);
          }
  
          deleteUserTable();
      }
  
      @Test
      public void testExecuteScalar(){
          createAndFillUserTable();
  
          Object o = sql2o.createQuery("select text from user_table where id = 2").executeScalar();
          assertTrue(o.getClass().equals(String.class));
  
          Object o2 = sql2o.createQuery("select 10 from (values(0))").executeScalar();
          assertEquals(o2, 10);
  
          deleteUserTable();
      }
  
      @Test
      public void testBatchNoTransaction(){
  
          sql2o.createQuery(
                  "create table user_table(\n" +
                  "id int identity primary key,\n" +
                  "name varchar(20),\n" +
                  "email varchar(255),\n" +
                  "text varchar(100))").executeUpdate();
  
          String insQuery = "insert into user_table(name, email, text) values (:name, :email, :text)";
  
          sql2o.createQuery(insQuery).addParameter("name", "test").addParameter("email", "test@test.com").addParameter("text", "something exciting").addToBatch()
                  .addParameter("name", "test2").addParameter("email", "test2@test.com").addParameter("text", "something exciting too").addToBatch()
                  .addParameter("name", "test3").addParameter("email", "test3@test.com").addParameter("text", "blablabla").addToBatch()
                  .executeBatch();
  
          deleteUserTable();
      }
  
      @Test
      public void testCaseInsensitive(){
          sql2o.createQuery("create table testCI(id2 int primary key, value2 varchar(20), sometext varchar(20), valwithgetter varchar(20))").executeUpdate();
  
          Query query = sql2o.createQuery("insert into testCI(id2, value2, sometext, valwithgetter) values(:id, :value, :someText, :valwithgetter)");
          for (int i = 0; i < 20; i++){
              query.addParameter("id", i).addParameter("value", "some text " + i).addParameter("someText", "whatever " + i).addParameter("valwithgetter", "spaz" + i).addToBatch();
          }
          query.executeBatch();
  
          List<CIEntity> ciEntities = sql2o.createQuery("select * from testCI").setCaseSensitive(false).executeAndFetch(CIEntity.class);
  
          assertTrue(ciEntities.size() == 20);
  
  
          // test defaultCaseSensitive;
          sql2o.setDefaultCaseSensitive(false);
          List<CIEntity> ciEntities2 = sql2o.createQuery("select * from testCI").executeAndFetch(CIEntity.class);
          assertTrue(ciEntities2.size() == 20);
      }
  
      @Test(expected = java.lang.IllegalArgumentException.class)
      public void testSetMaxBatchRecords(){
          try (Connection conn = this.sql2o.open()){
              Query q = conn.createQuery("select 'test'");
              q.setMaxBatchRecords(20);
              assertTrue(q.getMaxBatchRecords() == 20);
  
              q.setMaxBatchRecords(0);
              assertTrue(q.getMaxBatchRecords() == 0);
  
              q.setMaxBatchRecords(-1);
          }
      }
  
      @Test
      public void testBatchWithMaxBatchRecords(){
          try (Connection connection = sql2o.open()) {
              createAndFillUserTable(connection, true, 50);
              genericTestOnUserData(connection);
          }
  
          //also test with an odd number
  
          try (Connection connection = sql2o.open()) {
              createAndFillUserTable(connection, true, 29);
              genericTestOnUserData(connection);
          }
      }
  
      @Test
      public void testExecuteAndFetchResultSet() throws SQLException {
          List<Integer> list = sql2o.createQuery("select 1 val from (values(0)) union select 2 from (values(0)) union select 3 from (values(0))").executeScalarList(Integer.class);
  
          assertEquals((int)list.get(0), 1);
          assertEquals((int)list.get(1), 2);
          assertEquals((int)list.get(2), 3);
      }
  
      @Test
      public void testExecuteScalarListWithNulls() throws SQLException {
          List<String> list = sql2o.createQuery("select val from ( "+
                                                "select 1 ord, null val from (values(0)) union " +
                                                "select 2 ord, 'one' from (values(0)) union " +
                                                "select 3 ord, null from (values(0)) union " +
                                                "select 4 ord, 'two' from (values(0)) " +
                                                ") order by ord")  // explicit ordering since nulls seem to mess with ordering
                                   .executeScalarList(String.class);
  
          assertEquals(4, list.size());
  
          assertNull(list.get(0));
          assertEquals(list.get(1), "one");
          assertNull(list.get(2));
          assertEquals(list.get(3), "two");
      }
  
      @Test
      public void testJodaTime(){
  
          sql2o.createQuery("create table testjoda(id int primary key, joda1 datetime, joda2 datetime)").executeUpdate();
  
          sql2o.createQuery("insert into testjoda(id, joda1, joda2) values(:id, :joda1, :joda2)")
                  .addParameter("id", 1).addParameter("joda1", new DateTime()).addParameter("joda2", new DateTime().plusDays(-1)).addToBatch()
                  .addParameter("id", 2).addParameter("joda1", new DateTime().plusYears(1)).addParameter("joda2", new DateTime().plusDays(-2)).addToBatch()
                  .addParameter("id", 3).addParameter("joda1", new DateTime().plusYears(2)).addParameter("joda2", new DateTime().plusDays(-3)).addToBatch()
                  .executeBatch();
  
          List<JodaEntity> list = sql2o.createQuery("select * from testjoda").executeAndFetch(JodaEntity.class);
  
          assertTrue(list.size() == 3);
          assertTrue(list.get(0).getJoda2().isBeforeNow());
  
      }
  
      @Test
      public void testColumnAnnotation(){
          try(Connection connection = sql2o.open()) {
              connection.createQuery("create table test_column_annotation(id int primary key, text_col varchar(20))").executeUpdate();
  
              connection.createQuery("insert into test_column_annotation(id, text_col) values(:id, :text)")
                  .addParameter("id", 1).addParameter("text", "test1").addToBatch()
                  .addParameter("id", 2).addParameter("text", "test2").addToBatch()
                  .executeBatch();
  
              List<ColumnEntity> result = connection.createQuery("select * from test_column_annotation").executeAndFetch(ColumnEntity.class);
  
              assertTrue(result.size() == 2);
              assertEquals(1, result.get(0).getId());
              assertEquals("test1", result.get(0).getText());
              assertEquals(2, result.get(1).getId());
              assertEquals("test2", result.get(1).getText());
          }
      }
  
      @Test
      public void testUtilDate(){
          sql2o.createQuery("create table testutildate(id int primary key, d1 datetime, d2 timestamp, d3 date)").executeUpdate();
  
          Date now = new Date();
  
          sql2o.createQuery("insert into testutildate(id, d1, d2, d3) values(:id, :d1, :d2, :d3)")
                  .addParameter("id", 1).addParameter("d1", now).addParameter("d2", now).addParameter("d3", now).addToBatch()
                  .addParameter("id", 2).addParameter("d1", now).addParameter("d2", now).addParameter("d3", now).addToBatch()
                  .addParameter("id", 3).addParameter("d1", now).addParameter("d2", now).addParameter("d3", now).addToBatch()
                  .executeBatch();
  
          List<UtilDateEntity> list = sql2o.createQuery("select * from testutildate").executeAndFetch(UtilDateEntity.class);
  
          assertTrue(list.size() == 3);
  
          // make sure d1, d2, d3 were properly inserted and selected
          for (UtilDateEntity e : list) {
              assertEquals(now, e.d1);
              assertEquals(now, e.getD2());
              Date dateOnly = new DateTime(now).toDateMidnight().toDate();
              assertEquals(dateOnly, e.getD3());
          }
      }
  
      @Test
      public void testConversion(){
  
          String sql = "select cast(1 as smallint) as val1, 2 as val2 from (values(0)) union select cast(3 as smallint) as val1, 4 as val2 from (values(0))";
          List<TypeConvertEntity> entities = sql2o.createQuery(sql).executeAndFetch(TypeConvertEntity.class);
  
          assertTrue(entities.size() == 2);
      }
  
      @Test
      public void testUpdateNoTransaction() throws SQLException {
          String ddlQuery = "create table testUpdateNoTransaction(id int primary key, \"value\" varchar(50))";
          Connection connection = sql2o.createQuery(ddlQuery).executeUpdate();
  
          assertTrue(connection.getJdbcConnection().isClosed());
  
          String insQuery = "insert into testUpdateNoTransaction(id, \"value\") values (:id, :value)";
          sql2o.createQuery(insQuery).addParameter("id",1).addParameter("value", "test1").executeUpdate()
                  .createQuery(insQuery).addParameter("id", 2).addParameter("value", "val2").executeUpdate();
  
          assertTrue(connection.getJdbcConnection().isClosed());
      }
  
      @Test
      public void testNullDate(){
          sql2o.createQuery("create table nullDateTest(id integer primary key, somedate datetime)").executeUpdate();
  
          sql2o.createQuery("insert into nullDateTest(id, somedate) values(:id, :date)")
                  .addParameter("id", 1)
                  .addParameter("date", (Date)null).executeUpdate();
  
          Date d = (Date)sql2o.createQuery("select somedate from nullDateTest where id = 1").executeScalar();
          assertNull(d);
      }
  
      @Test
      public void testGetResult(){
  
          sql2o.createQuery("create table get_result_test(id integer primary key, \"value\" varchar(20))").executeUpdate();
  
          String insertSql =
                  "insert into get_result_test(id, \"value\") " +
                  "select 1, 'hello' from (values(0)) union " +
                  "select 2, 'hello2' from (values(0)) union " +
                  "select 3, 'hello3' from (values(0))";
  
          int result = sql2o.createQuery(insertSql).executeUpdate().getResult();
  
          assertEquals(3, result);
      }
  
      @Test
      public void testGetKeys(){
  
          sql2o.createQuery("create table get_keys_test(id integer identity primary key, \"value\" varchar(20))").executeUpdate();
  
          String insertSql = "insert into get_keys_test(\"value\") values(:val)";
  //        try{
  //            Integer key = (Integer)sql2o.createQuery(insertSql).addParameter("val", "something").executeUpdate().getKey();
  //            throw new RuntimeException("Sql2oException expected in code line above");
  //        }
  //        catch(Sql2oException ex){
  //            assertTrue(ex.getMessage().contains("executeUpdate(true)"));
  //        }
  
          Integer key = (Integer)sql2o.createQuery(insertSql).addParameter("val", "something").executeUpdate().getKey();
  
          assertNotNull(key);
          assertTrue(key >= 0);
  
          String multiInsertSql = "insert into get_keys_test(\"value\") select 'a val' col1 from (values(0)) union select 'another val' col1 from (values(0))";
          Object[] keys = sql2o.createQuery(multiInsertSql).executeUpdate().getKeys();
  
          assertNotNull(keys);
  
          // return value of auto generated keys is DB dependent.
          // H2 will always just return the last generated identity.
          // HyperSQL returns all generated identities (which is more ideal).
          if (this.dbType == DbType.HyperSQL) {
              assertTrue(keys.length == 2);
          }
          else {
              assertTrue(keys.length > 0);
          }
      }
  
      @Test
      public void testExecuteBatchGetKeys() {
          sql2o.createQuery("create table get_keys_test2(id integer identity primary key, \"value\" varchar(20))").executeUpdate();
  
          String insertSql = "insert into get_keys_test2(\"value\") values(:val)";
  
          List<String> vals = new ArrayList<String>(){{
              add("something1");
              add("something2");
              add("something3");
          }};
  
          Query query = sql2o.createQuery(insertSql, true);
  
          for (String val : vals) {
              query.addParameter("val", val);
              query.addToBatch();
          }
  
          List<Integer> keys = query.executeBatch().getKeys(Integer.class);
  
          assertNotNull(keys);
          for (Integer key : keys) {
              assertTrue(key >= 0);
          }
  
          // return value of auto generated keys is DB dependent.
          // H2 will always just return the last generated identity.
          // HyperSQL returns all generated identities (which is more ideal).
          if (this.dbType == DbType.HyperSQL) {
              assertTrue(keys.size() == vals.size());
          }
          else {
              assertTrue(keys.size() > 0);
          }
      }
  
      @Test
      public void testRollback(){
  
          sql2o.createQuery("create table test_rollback_table(id integer identity primary key, \"value\" varchar(25))").executeUpdate();
  
          sql2o
                  //first insert something, and commit it.
                  .beginTransaction()
                  .createQuery("insert into test_rollback_table(\"value\") values (:val)")
                  .addParameter("val", "something")
                  .executeUpdate()
                  .commit()
  
                          // insert something else, and roll it back.
                  .beginTransaction()
                  .createQuery("insert into test_rollback_table(\"value\") values (:val)")
                  .addParameter("val", "something to rollback")
                  .executeUpdate()
                  .rollback();
          long rowCount = (Long)sql2o.createQuery("select count(*) from test_rollback_table").executeScalar();
  
          assertEquals(1, rowCount);
      }
  
      @Test
      public void testBigDecimals(){
  
          sql2o.createQuery("create table bigdectesttable (id integer identity primary key, val1 numeric(5,3), val2 integer)").executeUpdate();
  
          sql2o.createQuery("insert into bigdectesttable(val1, val2) values(:val1, :val2)").addParameter("val1",1.256d).addParameter("val2", 4).executeUpdate();
  
          BigDecimalPojo pojo = sql2o.createQuery("select * from bigdectesttable").executeAndFetchFirst(BigDecimalPojo.class);
  
          assertEquals(new BigDecimal("1.256"), pojo.val1);
          assertEquals(new BigDecimal("4"), pojo.val2);
      }
  
      @Test
      public void testQueryDbMappings(){
          Entity entity = sql2o.createQuery("select 1 as id, 'something' as caption, cast('2011-01-01' as date) as theTime from (values(0))")
                  .addColumnMapping("caption", "text")
                  .addColumnMapping("theTime", "time").executeAndFetchFirst(Entity.class);
  
          assertEquals(1, entity.id);
          assertEquals("something", entity.text);
          assertEquals(new DateTime(2011,1,1,0,0,0,0).toDate(), entity.time);
      }
  
      @Test
      public void testGlobalDbMappings(){
          Sql2o sql2o1 = new Sql2o(dbType.url, dbType.user, dbType.pass);
  
          Map<String,String> defaultColMaps = new HashMap<String, String>();
          defaultColMaps.put("caption", "text");
          defaultColMaps.put("theTime", "time");
  
          sql2o1.setDefaultColumnMappings(defaultColMaps);
  
          Entity entity = sql2o1.createQuery("select 1 as id, 'something' as caption, cast('2011-01-01' as date) as theTime from (values(0))").executeAndFetchFirst(Entity.class);
  
          assertEquals(1, entity.id);
          assertEquals("something", entity.text);
          assertEquals(new DateTime(2011,1,1,0,0,0,0).toDate(), entity.time);
  
      }
  
      @Test
      public void testSetPrivateFields(){
          EntityWithPrivateFields entity = sql2o.createQuery("select 1 id, 'hello' \"value\" from (values(0))").executeAndFetchFirst(EntityWithPrivateFields.class);
  
          assertEquals(1, entity.getId());
          assertEquals("hello1", entity.getValue());
      }
  
      @Test
      public void testFetchTable(){
          sql2o.createQuery("create table tabletest(id integer identity primary key, \"VALUE\" varchar(20), value2 decimal(5,1))").executeUpdate();
          sql2o.createQuery("insert into tabletest(\"VALUE\",value2) values (:value, :value2)")
                  .addParameter("value", "something").addParameter("value2", new BigDecimal("3.4")).addToBatch()
                  .addParameter("value", "bla").addParameter("value2", new BigDecimal("5.5")).addToBatch().executeBatch();
  
  
          Table table = sql2o.createQuery("select * from tabletest order by id").executeAndFetchTable();
  
          assertEquals(3, table.columns().size());
          assertEquals("ID", table.columns().get(0).getName());
          assertEquals("VALUE", table.columns().get(1).getName());
          assertEquals("VALUE2", table.columns().get(2).getName());
  
          assertEquals(2, table.rows().size());
  
          Row row0 = table.rows().get(0);
          Row row1 = table.rows().get(1);
  
          assertTrue(0 <= row0.getInteger("ID"));
          assertEquals("something", row0.getString(1));
          assertEquals(new BigDecimal("3.4"), row0.getBigDecimal("VALUE2"));
  
          assertTrue(1 <= row1.getInteger(0));
          assertEquals("bla", row1.getString("VALUE"));
          assertEquals(5.5D, row1.getDouble(2), 0.00001);
      }
  
      @Test
      public void testTable_asList() {
          createAndFillUserTable();
  
          List<Map<String, Object>> rows;
          try (Connection con = sql2o.open()) {
              Table table = con.createQuery("select * from user_table").executeAndFetchTable();
  
              rows = table.asList();
          }
  
          assertEquals(insertIntoUsers, rows.size());
  
          for (Map<String, Object> row : rows) {
              assertEquals(4, row.size());
              assertTrue(row.containsKey("id"));
              assertTrue(row.containsKey("name"));
              assertTrue(row.containsKey("email"));
              assertTrue(row.containsKey("text"));
          }
  
          deleteUserTable();
      }
  
      @Test
      public void testStringConversion() {
          StringConversionPojo pojo = sql2o.createQuery("select '1' val1, '2  ' val2, '' val3, '' val4, null val5 from (values(0))").executeAndFetchFirst(StringConversionPojo.class);
  
          assertEquals((Integer)1, pojo.val1);
          assertEquals(2l, pojo.val2);
          assertNull(pojo.val3);
          assertEquals(0, pojo.val4);
          assertNull(pojo.val5);
      }
  
      @Test
      public void testSuperPojo(){
          SuperPojo pojo = sql2o.createQuery("select 1 id, 'something' \"value\" from (values(0))").executeAndFetchFirst(SuperPojo.class);
  
          assertEquals(1, pojo.getId());
          assertEquals("something1", pojo.getValue());
      }
  
      @Test
      public void testComplexTypes(){
          try (final var connection = sql2o.open()) {
              ComplexEntity pojo = connection.createQuery("select 1 id, 1 \"entity.id\", 'something' \"entity.value\" from (values(0))").setName("testComplexTypes").executeAndFetchFirst(ComplexEntity.class);
  
              assertEquals(1, pojo.id);
              assertEquals(1, pojo.getEntity().getId());
              assertEquals("something1", pojo.getEntity().getValue());
          }
      }
  
      @Test
      public void testRunInsideTransaction(){
  
          sql2o.createQuery("create table runinsidetransactiontable(id integer identity primary key, \"value\" varchar(50))").executeUpdate();
          boolean failed = false;
  
          try{
              sql2o.runInTransaction(new StatementRunnable() {
                  public void run(Connection connection, Object argument) throws Throwable {
                      connection.createQuery("insert into runinsidetransactiontable(\"value\") values(:value)")
                              .addParameter("value", "test").executeUpdate();
  
                      throw new RuntimeException("ouch!");
  
  
                  }
              });
          }
          catch(Sql2oException ex){
              failed = true;
          }
  
          assertTrue(failed);
          long rowCount = (Long)sql2o.createQuery("select count(*) from runinsidetransactiontable").executeScalar();
          assertEquals(0, rowCount);
  
          sql2o.runInTransaction(new StatementRunnable() {
              public void run(Connection connection, Object argument) throws Throwable {
                  connection.createQuery("insert into runinsidetransactiontable(\"value\") values(:value)")
                          .addParameter("value", "test").executeUpdate();
              }
          });
  
          rowCount = (Long)sql2o.createQuery("select count(*) from runinsidetransactiontable").executeScalar();
          assertEquals(1, rowCount);
  
  
          String argument = "argument test";
  
          sql2o.runInTransaction(new StatementRunnable() {
              public void run(Connection connection, Object argument) throws Throwable {
                  Integer id = connection.createQuery("insert into runinsidetransactiontable(\"value\") values(:value)")
                          .addParameter("value", argument).executeUpdate().getKey(Integer.class);
  
                  String insertedValue = connection.createQuery("select \"value\" from runinsidetransactiontable where id = :id").addParameter("id", id).executeScalar(String.class);
                  assertEquals("argument test", insertedValue);
              }
          }, argument);
  
          rowCount = (Long)sql2o.createQuery("select count(*) from runinsidetransactiontable").executeScalar();
          assertEquals(2, rowCount);
      }
  
      @Test
      public void testRunInsideTransactionWithResult(){
          sql2o.createQuery("create table testRunInsideTransactionWithResultTable(id integer identity primary key, \"value\" varchar(50))").executeUpdate();
  
      }
  
      private static class runnerWithResultTester implements StatementRunnableWithResult<List<Integer>> {
  
          public List<Integer> run(Connection connection, Object argument) throws Throwable {
              String[] vals = (String[])argument;
              List<Integer> keys = new ArrayList<Integer>();
              for (String val : vals){
                  Integer key = connection.createQuery("insert into testRunInsideTransactionWithResultTable(value) values(:val)", "runnerWithResultTester")
                          .addParameter("val", val)
                          .executeUpdate().getKey(Integer.class);
                  keys.add(key);
              }
  
              return keys;
          }
      }
  
      @Test
      public void testDynamicExecuteScalar(){
          Object origVal = sql2o.createQuery("select 1 from (values(0))").executeScalar();
          assertTrue(Integer.class.equals(origVal.getClass()));
          assertEquals(1, origVal);
  
          Long intVal = sql2o.createQuery("select 1 from (values(0))").executeScalar(Long.class);
          assertEquals((Long)1l, intVal);
  
          Short shortVal = sql2o.createQuery("select 2 from (values(0))").executeScalar(Short.class);
          Short expected = 2;
          assertEquals(expected, shortVal);
      }
  
      @Test
      public void testUpdateWithNulls() {
          sql2o.createQuery("create table testUpdateWithNulls_2(id integer identity primary key, \"value\" integer)").executeUpdate();
  
          Integer nullInt = null;
  
          sql2o.createQuery("insert into testUpdateWithNulls_2(\"value\") values(:val)").addParameter("val", 2).addToBatch().addParameter("val", nullInt).addToBatch().executeBatch();
      }
  
      @Test
      public void testExceptionInRunnable() {
          sql2o.createQuery("create table testExceptionInRunnable(id integer primary key, \"value\" varchar(20))").executeUpdate();
  
          try{
              sql2o.runInTransaction(new StatementRunnable() {
                  public void run(Connection connection, Object argument) throws Throwable {
                      connection.createQuery("insert into testExceptionInRunnable(id, \"value\") values(:id, :val)")
                              .addParameter("id", 1)
                              .addParameter("val", "something").executeUpdate();
  
                      connection.createQuery("insert into testExceptionInRunnable(id, \"value\") values(:id, :val)")
                              .addParameter("id", 1)
                              .addParameter("val", "something").executeUpdate();
                  }
              });
          } catch(Throwable t) {
  
          }
  
          int c = sql2o.createQuery("select count(*) from testExceptionInRunnable").executeScalar(Integer.class);
          assertEquals(0, c);
  
  
          sql2o.runInTransaction(new StatementRunnable() {
              public void run(Connection connection, Object argument) throws Throwable {
                  connection.createQuery("insert into testExceptionInRunnable(id, \"value\") values(:id, :val)")
                          .addParameter("id", 1)
                          .addParameter("val", "something").executeUpdate();
  
                  try{
                      connection.createQuery("insert into testExceptionInRunnable(id, \"value\") values(:id, :val)")
                              .addParameter("id", 1)
                              .addParameter("val", "something").executeUpdate();
                  } catch(Sql2oException ex){
  
                  }
              }
          });
  
          c = sql2o.createQuery("select count(*) from testExceptionInRunnable").executeScalar(Integer.class);
          assertEquals(1, c);
  
      }
  
      public static enum TestEnum{
          HELLO, WORLD;
      }
  
      public static class EntityWithEnum{
          public int id;
          public TestEnum val;
          public TestEnum val2;
      }
  
      @Test
      public void testEnums() {
          sql2o.createQuery("create table EnumTest(id int identity primary key, enum_val varchar(10), enum_val2 int) ").executeUpdate();
  
          sql2o.createQuery("insert into EnumTest(enum_val, enum_val2) values (:val, :val2)")
                  .addParameter("val", TestEnum.HELLO).addParameter("val2", TestEnum.HELLO.ordinal()).addToBatch()
                  .addParameter("val", TestEnum.WORLD).addParameter("val2", TestEnum.WORLD.ordinal()).addToBatch().executeBatch();
  
          List<EntityWithEnum> list = sql2o.createQuery("select id, enum_val val, enum_val2 val2 from EnumTest").executeAndFetch(EntityWithEnum.class);
  
          assertThat(list.get(0).val, is(TestEnum.HELLO));
          assertThat(list.get(0).val2, is(TestEnum.HELLO));
          assertThat(list.get(1).val, is(TestEnum.WORLD));
          assertThat(list.get(1).val2, is(TestEnum.WORLD));
  
          TestEnum testEnum = sql2o.createQuery("select 'HELLO' from (values(0))").executeScalar(TestEnum.class);
          assertThat(testEnum, is(TestEnum.HELLO));
  
          TestEnum testEnum2 = sql2o.createQuery("select NULL from (values(0))").executeScalar(TestEnum.class);
          assertThat(testEnum2, is(nullValue()));
      }
  
      public static class BooleanPOJO {
          public boolean val1;
          public Boolean val2;
      }
  
      @Test
      public void testBooleanConverter() {
          String sql = "select true as val1, false as val2 from (values(0))";
  
          BooleanPOJO pojo = sql2o.createQuery(sql).executeAndFetchFirst(BooleanPOJO.class);
          assertTrue(pojo.val1);
          assertFalse(pojo.val2);
  
          String sql2 = "select null as val1, null as val2 from (values(0))";
          BooleanPOJO pojo2 = sql2o.createQuery(sql2).executeAndFetchFirst(BooleanPOJO.class);
          assertFalse(pojo2.val1);
          assertNull(pojo2.val2);
  
          String sql3 = "select 'false' as val1, 'true' as val2 from (values(0))";
          BooleanPOJO pojo3 = sql2o.createQuery(sql3).executeAndFetchFirst(BooleanPOJO.class);
          assertFalse(pojo3.val1);
          assertTrue(pojo3.val2);
      }
  
      public static class BlobPOJO1{
          public int id;
          public byte[] data;
      }
  
      public static class BlobPOJO2{
          public int id;
          public InputStream data;
      }
  
      @Test
      public void testBlob() throws IOException {
          String createSql = "create table blobtbl2(id int identity primary key, data blob)";
          sql2o.createQuery(createSql).executeUpdate();
  
          String dataString = "test";
          byte[] data = dataString.getBytes();
          String insertSql = "insert into blobtbl2(data) values(:data)";
          sql2o.createQuery(insertSql).addParameter("data", data).executeUpdate();
  
          // select
          String sql = "select id, data from blobtbl2";
          BlobPOJO1 pojo1 = sql2o.createQuery(sql).executeAndFetchFirst(BlobPOJO1.class);
          BlobPOJO2 pojo2 = sql2o.createQuery(sql).executeAndFetchFirst(BlobPOJO2.class);
  
          String pojo1DataString = new String(pojo1.data);
          assertThat(dataString, is(equalTo(pojo1DataString)));
  
          byte[] pojo2Data = IOUtils.toByteArray(pojo2.data);
          String pojo2DataString = new String(pojo2Data);
          assertThat(dataString, is(equalTo(pojo2DataString)));
      }
  
      @Test
      public void testInputStream() throws IOException {
          String createSql = "create table blobtbl(id int identity primary key, data blob)";
          sql2o.createQuery(createSql).executeUpdate();
  
          String dataString = "test";
          byte[] data = dataString.getBytes();
  
          InputStream inputStream = new ByteArrayInputStream( data );
  
          String insertSql = "insert into blobtbl(data) values(:data)";
          sql2o.createQuery(insertSql).addParameter("data", inputStream).executeUpdate();
  
          // select
          String sql = "select id, data from blobtbl";
          BlobPOJO1 pojo1 = sql2o.createQuery(sql).executeAndFetchFirst(BlobPOJO1.class);
          BlobPOJO2 pojo2 = sql2o.createQuery(sql).executeAndFetchFirst(BlobPOJO2.class);
  
          String pojo1DataString = new String(pojo1.data);
          assertThat(dataString, is(equalTo(pojo1DataString)));
  
          byte[] pojo2Data = IOUtils.toByteArray(pojo2.data);
          String pojo2DataString = new String(pojo2Data);
          assertThat(dataString, is(equalTo(pojo2DataString)));
      }
  
      @Test
      public void testTimeConverter(){
          String sql = "select current_time as col1 from (values(0))";
  
          Time sqlTime = sql2o.createQuery(sql).executeScalar(Time.class);
  
          Period p = new Period(new LocalTime(sqlTime), new LocalTime());
  
  
          assertNotNull(sqlTime);
          assertEquals(0, p.getMinutes());
  
          LocalTime jodaTime = sql2o.createQuery(sql).executeScalar(LocalTime.class);
          assertTrue(jodaTime.getMillisOfDay() > 0);
          assertThat(jodaTime.getHourOfDay(), is(equalTo(new LocalTime().getHourOfDay())));
      }
  
      public static class BindablePojo{
          String data1;
          private Timestamp data2;
          private Long data3;
          private Float data4;
          public Timestamp getData2() {
              return data2;
          }
  
          public Long getData3() {
              return data3;
          }
          public Float getData4() { return data4; }
          public void setData2(Timestamp data2) {
              this.data2 = data2;
          }
          public void setData3(Long data3) {
              this.data3 = data3;
          }
          public void setData4(Float data4) { this.data4 = data4; }
          @Override
          public boolean equals(Object obj) {
              if((obj != null) && (obj instanceof BindablePojo)){
                  BindablePojo other = (BindablePojo)obj;
                  /*System.out.println(data1 + " == " + other.data1);
                  System.out.println(data2 + " == " + other.data2);
                  System.out.println(data3 + " == " + other.data3);*/
                  boolean res = data1.equals(other.data1) && data2.equals(other.data2) && data3.equals(other.data3);
                  return res;
              }else
                  return false;
          }
  
      }
  
      @Test
      public void testBindPojo(){
          String createSql = "create table bindtbl(id int identity primary key, data1 varchar(10), data2 timestamp, data3 bigint)";
          sql2o.createQuery(createSql).executeUpdate();
  
          // Anonymous class inherits POJO
          BindablePojo pojo1 = new BindablePojo(){
              {
                  // Field access
                  data1="Foo";
                  setData2(new Timestamp(new Date().getTime()));
                  setData3(789456123L);
                  setData4(4.5f);
              }
          };
  
          String insertSql = "insert into bindtbl(data1, data2, data3) values(:data1, :data2, :data3)";
          sql2o.createQuery(insertSql).bind(pojo1).executeUpdate();
  
          String selectSql = "select data1, data2, data3 from bindtbl";
          BindablePojo pojo2 = sql2o.createQuery(selectSql).executeAndFetchFirst(BindablePojo.class);
  
          assertTrue(pojo1.equals(pojo2));
      }
  
      @Test
      public void testRowGetObjectWithConverters() {
          String sql = "select 1 col1, '23' col2 from (values(0))";
          Table t = sql2o.createQuery(sql).executeAndFetchTable();
          Row r = t.rows().get(0);
  
          String col1AsString = r.getObject("col1", String.class);
          Integer col1AsInteger = r.getObject("col1", Integer.class);
          Long col1AsLong = r.getObject("col1", Long.class);
  
          assertThat(col1AsString, is(equalTo("1")));
          assertThat(col1AsInteger, is(equalTo(1)));
          assertThat(col1AsLong, is(equalTo(1L)));
  
          String col2AsString = r.getObject("col2", String.class);
          Integer col2AsInteger = r.getObject("col2", Integer.class);
          Long col2AsLong = r.getObject("col2", Long.class);
  
          assertThat(col2AsString, is(equalTo("23")));
          assertThat(col2AsInteger, is(equalTo(23)));
          assertThat(col2AsLong, is(equalTo(23L)));
      }
  
      @Test
      public void testExecuteAndFetchLazy(){
          createAndFillUserTable();
  
          ResultSetIterable<User> allUsers = sql2o.createQuery("select * from user_table").executeAndFetchLazy(User.class);
  
          // read in batches, because maybe we are bulk exporting and can't fit them all into a list
          int totalSize = 0;
          int batchSize = 500;
          List<User> batch = new ArrayList<User>(batchSize);
          for (User u : allUsers) {
              totalSize++;
              if (batch.size() == batchSize) {
                  System.out.println(String.format("Read batch of %d users, great!", batchSize));
                  batch.clear();
              }
              batch.add(u);
          }
  
          allUsers.close();
  
          assertTrue(totalSize == insertIntoUsers);
          deleteUserTable();
      }
  
      @Test
      public void testResultSetIterator_multipleHasNextWorks() {
          createAndFillUserTable();
  
          ResultSetIterable<User> allUsers = sql2o.createQuery("select * from user_table").executeAndFetchLazy(User.class);
  
          Iterator<User> usersIterator = allUsers.iterator();
  
          // call hasNext a few times, should have no effect
          usersIterator.hasNext();
          usersIterator.hasNext();
          usersIterator.hasNext();
  
          int totalSize = 0;
          while (usersIterator.hasNext()) {
              totalSize++;
              usersIterator.next();
          }
  
          allUsers.close();
  
          assertTrue(totalSize == insertIntoUsers);
          deleteUserTable();
      }
  
      @Test
      public void testExecuteAndFetch_fallbackToExecuteScalar() {
          createAndFillUserTable();
  
          // this should NOT fallback to executeScalar
          List<User> users = sql2o.createQuery("select name from user_table").executeAndFetch(User.class);
  
          // only the name should be set
          for (User u : users) {
              assertNotNull(u.name);
          }
  
          // this SHOULD fallback to executeScalar
          List<String> userNames = sql2o.createQuery("select name from user_table").executeAndFetch(String.class);
  
          assertEquals(users.size(), userNames.size());
  
          deleteUserTable();
      }
  
      @Test
      public void testExecuteAndFetchWithAutoclose() throws SQLException {
          createAndFillUserTable();
  
          Connection con = sql2o.open();
  
          try (ResultSetIterable<User> userIterable = con.createQuery("select * from user_table")
                  .executeAndFetchLazy(User.class)) {
  
              userIterable.setAutoCloseConnection(true);
  
              for (User u : userIterable) {
                  assertThat(u.getEmail(), is(not(nullValue())));
              }
          }
  
          assertTrue(con.getJdbcConnection().isClosed());
  
  
      }
  
      @Test
      public void testLazyTable() throws SQLException {
          createAndFillUserTable();
  
          Query q = sql2o.createQuery("select * from user_table");
          LazyTable lt = null;
          try {
              lt = q.executeAndFetchTableLazy();
              for (Row r : lt.rows()){
                  String name = r.getString("name");
  
                  assertThat(name, notNullValue());
              }
  
              // still in autoClosable scope. Expecting connection to be open.
              assertThat(q.getConnection().getJdbcConnection().isClosed(), is(false));
          } finally {
              // simulate autoClose.
              lt.close();
          }
  
          // simulated autoClosable scope exited. Expecting connection to be closed.
          assertThat(q.getConnection().getJdbcConnection().isClosed(), is(true));
      }
  
      @Test
      public void testTransactionAutoClosable() {
  
          sql2o.createQuery("create table testTransactionAutoClosable(id int primary key, val varchar(20) not null)").executeUpdate();
  
          Connection connection = null;
          try {
              connection = sql2o.beginTransaction();
              String sql = "insert into testTransactionAutoClosable(id, val) values (:id, :val);";
              connection.createQuery(sql).addParameter("id", 1).addParameter("val", "foo").executeUpdate();
          } finally {
              // autoclosing
              connection.close();
          }
  
          int count = sql2o.createQuery("select count(*) from testTransactionAutoClosable").executeScalar(Integer.class);
          assertThat(count, is(equalTo(0)));
  
          connection = null;
          try {
              connection = sql2o.beginTransaction();
              String sql = "insert into testTransactionAutoClosable(id, val) values (:id, :val);";
              connection.createQuery(sql).addParameter("id", 1).addParameter("val", "foo").executeUpdate();
  
              connection.commit();
          } finally {
              // autoclosing
              connection.close();
          }
  
          count = sql2o.createQuery("select count(*) from testTransactionAutoClosable").executeAndFetchFirst(Integer.class);
          assertThat(count, is(equalTo(1)));
  
      }
  
      @Test
      public void testExternalTransactionCommit() {
  
          try (Connection connection1 = sql2o.open()) {
              connection1.createQuery("create table testExternalTransactionCommit(id int primary key, val varchar(20) not null)")
                      .executeUpdate();
          }
  
  
          try (Connection globalConnection = sql2o.beginTransaction()) {
              java.sql.Connection globalTransaction = globalConnection.getJdbcConnection();
  
  
              try(Connection connection = sql2o.beginTransaction(join(globalTransaction)) ) {
                  String sql = "insert into testExternalTransactionCommit(id, val) values (:id, :val);";
                  connection.createQuery(sql).addParameter("id", 1).addParameter("val", "foo").executeUpdate();
                  connection.commit();
              }
  
              try (Connection connection2 = sql2o.open(join(globalTransaction))) {
                  int count = connection2.createQuery("select count(*) from testExternalTransactionCommit").executeAndFetchFirst(Integer.class);
                  assertThat(count, is(equalTo(1)));
              }
  
              try(Connection connection = sql2o.beginTransaction(join(globalTransaction)) ) {
                  String sql = "insert into testExternalTransactionCommit(id, val) values (:id, :val);";
                  connection.createQuery(sql).addParameter("id", 2).addParameter("val", "bar").executeUpdate();
                  connection.commit();
              }
  
              try (Connection connection2 = sql2o.open(join(globalTransaction))) {
                  int count = connection2.createQuery("select count(*) from testExternalTransactionCommit").executeAndFetchFirst(Integer.class);
                  assertThat(count, is(equalTo(2)));
              }
  
              globalConnection.commit();
          }
  
          try (Connection connection2 = sql2o.open()) {
              int count = connection2.createQuery("select count(*) from testExternalTransactionCommit").executeAndFetchFirst(Integer.class);
  
              assertThat(count, is(equalTo(2)));
          }
  
      }
  
      @Test
      public void testExternalTransactionRollback() {
  
          try (Connection connection1 = sql2o.open()) {
              connection1.createQuery("create table testExternalTransactionRollback(id int primary key, val varchar(20) not null)")
                      .executeUpdate();
          }
  
  
          try (Connection globalConnection = sql2o.beginTransaction()) {
              java.sql.Connection globalTransaction = globalConnection.getJdbcConnection();
  
  
              try(Connection connection = sql2o.beginTransaction(join(globalTransaction)) ) {
                  String sql = "insert into testExternalTransactionRollback(id, val) values (:id, :val);";
                  connection.createQuery(sql).addParameter("id", 1).addParameter("val", "foo").executeUpdate();
                  connection.commit();
              }
  
              try (Connection connection2 = sql2o.open(join(globalTransaction))) {
                  int count = connection2.createQuery("select count(*) from testExternalTransactionRollback").executeAndFetchFirst(Integer.class);
                  assertThat(count, is(equalTo(1)));
              }
  
              try(Connection connection = sql2o.beginTransaction(join(globalTransaction)) ) {
                  String sql = "insert into testExternalTransactionRollback(id, val) values (:id, :val);";
                  connection.createQuery(sql).addParameter("id", 2).addParameter("val", "bar").executeUpdate();
                  connection.commit();
              }
  
              try (Connection connection2 = sql2o.open(join(globalTransaction))) {
                  int count = connection2.createQuery("select count(*) from testExternalTransactionRollback").executeAndFetchFirst(Integer.class);
                  assertThat(count, is(equalTo(2)));
              }
  
              globalConnection.rollback();
          }
  
          try (Connection connection2 = sql2o.open()) {
              int count = connection2.createQuery("select count(*) from testExternalTransactionRollback").executeAndFetchFirst(Integer.class);
  
              assertThat(count, is(equalTo(0)));
          }
  
      }
  
      @Test
      public void testOpenConnection() throws SQLException {
  
          Connection connection = sql2o.open();
  
          createAndFillUserTable(connection);
  
          assertThat(connection.getJdbcConnection().isClosed(), is(false));
  
          List<User> users = connection.createQuery("select * from user_table").executeAndFetch(User.class);
  
          assertThat(users.size(), is(equalTo(NUMBER_OF_USERS_IN_THE_TEST)));
          assertThat(connection.getJdbcConnection().isClosed(), is(false));
  
          connection.close();
  
          assertThat(connection.getJdbcConnection().isClosed(), is(true));
      }
  
      @Test
      public void testWithConnection() {
  
          createAndFillUserTable();
  
          final String insertsql = "insert into user_table(name, email, text) values (:name, :email, :text)";
  
  
          sql2o.withConnection(new StatementRunnable() {
              public void run(Connection connection, Object argument) throws Throwable {
  
                  connection.createQuery(insertsql)
                          .addParameter("name", "Sql2o")
                          .addParameter("email", "sql2o@sql2o.org")
                          .addParameter("text", "bla bla")
                          .executeUpdate();
  
                  connection.createQuery(insertsql)
                          .addParameter("name", "Sql2o2")
                          .addParameter("email", "sql2o@sql2o.org")
                          .addParameter("text", "bla bla")
                          .executeUpdate();
  
                  connection.createQuery(insertsql)
                          .addParameter("name", "Sql2o3")
                          .addParameter("email", "sql2o@sql2o.org")
                          .addParameter("text", "bla bla")
                          .executeUpdate();
  
              }
          });
  
          List<User> users = sql2o.withConnection(new StatementRunnableWithResult<List<User>>() {
              public List<User> run(Connection connection, Object argument) throws Throwable {
                  return sql2o.createQuery("select * from user_table").executeAndFetch(User.class);
              }
          });
  
          assertThat(users.size(), is(equalTo(10003)));
  
          try{
              sql2o.withConnection(new StatementRunnable() {
                  public void run(Connection connection, Object argument) throws Throwable {
  
                      connection.createQuery(insertsql)
                              .addParameter("name", "Sql2o")
                              .addParameter("email", "sql2o@sql2o.org")
                              .addParameter("text", "bla bla")
                              .executeUpdate();
  
                      throw new RuntimeException("whaa!");
  
                  }
              });
          } catch (Exception e) {
              // ignore. expected
          }
  
          List<User> users2 = sql2o.createQuery("select * from user_table").executeAndFetch(User.class);
  
          // expect that the last insert was committed, as this should not be run in a transaction.
          assertThat(users2.size(), is(equalTo(10004)));
      }
  
      static class LocalPojo{
          private long idVal;
          private String anotherVeryExcitingValue;
  
          public long getIdVal() {
              return idVal;
          }
  
          public String getAnotherVeryExcitingValue() {
              return anotherVeryExcitingValue;
          }
  
          public void setAnotherVeryExcitingValue(String anotherVeryExcitingValue) {
              this.anotherVeryExcitingValue = anotherVeryExcitingValue;
          }
      }
  
      @Test
      public void testAutoDeriveColumnNames () {
          String createTableSql = "create table testAutoDeriveColumnNames (id_val integer primary key, another_very_exciting_value varchar(20))";
          String insertSql = "insert into testAutoDeriveColumnNames values (:id, :val)";
          String selectSql = "select * from testAutoDeriveColumnNames";
  
  
  
          try (Connection con = sql2o.open()) {
              con.createQuery(createTableSql).executeUpdate();
              con.createQuery(insertSql).addParameter("id", 1).addParameter("val", "test1").executeUpdate();
  
              Exception ex = null;
              try {
                  // expected to fail, as autoDeriveColumnNames are not set
                  con.createQuery(selectSql).executeAndFetchFirst(LocalPojo.class);
              } catch(Exception e) {
                  ex = e;
              }
  
              assertNotNull(ex);
  
              LocalPojo p = con.createQuery(selectSql)
                      .setAutoDeriveColumnNames(true)
                      .executeAndFetchFirst(LocalPojo.class);
  
              assertNotNull(p);
              assertEquals(1, p.getIdVal());
              assertEquals("test1", p.getAnotherVeryExcitingValue());
  
          }
      }
  
      @Test
      public void testClob() {
          try (Connection connection = sql2o.open()) {
              connection.createQuery("create table testClob(id integer primary key, val clob)")
                      .executeUpdate();
  
              connection.createQuery("insert into testClob (id, val) values (:id, :val)")
                      .addParameter("id", 1)
                      .addParameter("val", "something")
                      .executeUpdate();
  
              String val = connection.createQuery("select val from testClob where id = :id")
                      .addParameter("id", 1)
                      .executeScalar(String.class);
  
              assertThat(val, is(equalTo("something")));
          }
  
  
      }
  
      @Test
      public void testBindInIteration() {
          try (Connection connection = sql2o.open()) {
              createAndFillUserTable(connection, true);
              genericTestOnUserData(connection);
          }
  
      }
  
      @Test
      public void testArrayParameter(){
          createAndFillUserTable();
  
          try(Connection connection = sql2o.open()) {
              List<User> result = connection
                      .createQuery("select * from user_table where id in(:ids)")
                      .addParameter("ids", 1, 2, 3)
                      .executeAndFetch(User.class);
  
              assertTrue(result.size() == 3);
          }
  
          try(Connection connection = sql2o.open()) {
              List<User> result = connection
                      .createQuery("select * from user_table where" +
                              " email like :email" +
                              " and id in(:ids)" +
                              " and text = :text")
                      .addParameter("email", "%email.com")
                      .addParameter("ids", 1, 2, 3)
                      .addParameter("text", "some text")
                      .executeAndFetch(User.class);
  
              assertTrue(result.size() == 3);
          }
  
          try(Connection connection = sql2o.open()) {
              List<User> result = connection
                      .createQuery("select * from user_table where" +
                              " email like :email" +
                              " and id in(:ids)" +
                              " and text = :text")
                      .addParameter("email", "%email.com")
                      .addParameter("ids", ImmutableList.of())
                      .addParameter("text", "some text")
                      .executeAndFetch(User.class);
  
              assertTrue(result.size() == 0);
          }
  
          try(Connection connection = sql2o.open()) {
              List<User> result = connection
                      .createQuery("select * from user_table where" +
                              " email like :email" +
                              " and id in(:ids)" +
                              " and text = :text")
                      .addParameter("email", "%email.com")
                      .addParameter("ids", ImmutableList.of(1))
                      .addParameter("text", "some text")
                      .executeAndFetch(User.class);
  
              assertTrue(result.size() == 1);
          }
  
          try(Connection connection = sql2o.open()) {
              List<User> result = connection
                      .createQuery("select * from user_table where" +
                              " email like :email" +
                              " and text = :text" +
                              " and id in(:ids)" +
                              " and text = :text" +
                              " and id in(:ids)")
                      .addParameter("email", "%email.com")
                      .addParameter("ids", 1, 2, 3)
                      .addParameter("text", "some text")
                      .executeAndFetch(User.class);
  
              assertTrue(result.size() == 3);
          }
  
          try(Connection connection = sql2o.open()) {
              connection.createQuery("insert into user_table (id, text_col) values(:id, :text)")
                      .addParameter("id", 1, 2, 3).addParameter("text", "test1").addToBatch();
              fail("Batch with array parameter is not supported");
          } catch (Sql2oException e) {
              // as expected
          }
  
          try(Connection connection = sql2o.open()) {
              List<User> result = connection
                      .createQuery("select * from user_table where id in(:ids)")
                      .addParameter("ids", new int[]{1, 2, 3})
                      .executeAndFetch(User.class);
  
              assertTrue(result.size() == 3);
          }
  
          try(Connection connection = sql2o.open()) {
              List<User> result = connection
                      .createQuery("select * from user_table where id in(:ids)")
                      .addParameter("ids", (Object) ImmutableList.of(1, 2, 3))
                      .executeAndFetch(User.class);
  
              assertTrue(result.size() == 3);
          }
      }
  
      /************** Helper stuff ******************/
  
      private void createAndFillUserTable() {
          Connection connection = sql2o.open();
  
          createAndFillUserTable(connection);
  
          connection.close();
      }
  
  
      private void createAndFillUserTable(Connection connection){
          createAndFillUserTable(connection, false);
      }
  
      private void createAndFillUserTable(Connection connection, boolean useBind){
          createAndFillUserTable(connection, useBind, 0);
      }
  
      private void createAndFillUserTable(Connection connection, boolean useBind, int maxBatchRecords){
  
          try{
              connection.createQuery("drop table user_table").executeUpdate();
          } catch(Sql2oException e) {
              // if it fails, its because the User table doesn't exists. Just ignore this.
          }
  
          int rowCount = NUMBER_OF_USERS_IN_THE_TEST;
          String sql = "create table user_table( " +
              "id int identity primary key,\n" +
              "name varchar(20),\n" +
              "email varchar(255),\n" +
              "text varchar(100))";
          connection.createQuery(sql).executeUpdate();
  
          Query insQuery = connection.createQuery("insert into user_table(name, email, text) values (:name, :email, :text)");
          insQuery.setMaxBatchRecords(maxBatchRecords);
          UserInserter inserter = UserInserterFactory.buildUserInserter(useBind);
  
          Date before = new Date();
          for (int idx = 0; idx < rowCount; idx++){
              inserter.insertUser(insQuery,idx);
          }
  
          /*
           This check is required because the HSQL jdbc implementation
           throws an exception if executeBatch is called without anything added to the batch.
          */
          if (insQuery.isExplicitExecuteBatchRequired()) {
              insQuery.executeBatch();
          }
          Date after = new Date();
          Long span = after.getTime() - before.getTime();
  
          System.out.println(String.format("inserted %d rows into user_table table. Time used: %s ms", rowCount, span));
  
          insertIntoUsers += rowCount;
  
      }
  
      private void genericTestOnUserData(Connection connection){
          List<User> users = connection.createQuery("select * from user_table order by id").executeAndFetch(User.class);
  
          assertThat(users.size(), is(equalTo(NUMBER_OF_USERS_IN_THE_TEST)));
  
          for (int idx = 0; idx < NUMBER_OF_USERS_IN_THE_TEST; idx++) {
              User user = users.get(idx);
              assertThat("a name " + idx, is(equalTo(user.name)));
              assertThat(String.format("test%s@email.com", idx), is(equalTo(user.getEmail())));
          }
      }
  
      private void deleteUserTable(){
          sql2o.createQuery("drop table user_table").executeUpdate();
          insertIntoUsers = 0;
      }
  }
  