<commit_before>package org.sql2o;

import org.hsqldb.jdbc.JDBCDataSource;
import org.hsqldb.jdbcDriver;
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.*;
import java.util.*;
import java.util.Date;

import static org.hamcrest.CoreMatchers.*;
import static org.junit.Assert.*;

/**
 * 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 {

    @Parameterized.Parameters(name = "{index} - {4}")
    public static Collection<Object[]> getData(){
        return Arrays.asList(new Object[][]{
                {null, "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1","sa", "", "H2 test" },
                {new jdbcDriver(), "jdbc:hsqldb:mem:testmemdb", "SA", "", "HyperSQL DB test"}
        });
    }


    private final Sql2o sql2o;// = new Sql2o(this.url, this.user, this.pass);
    private String url;
    private String user;
    private String pass;
    private boolean isHyperSql;

    public Sql2oTest(Driver driverToRegister, String url, String user, String pass, String testName){

        if (driverToRegister != null) {
            try {
                DriverManager.registerDriver(driverToRegister);
            } catch (SQLException e) {
                throw new RuntimeException("could not register driver '" + driverToRegister.getClass().getName() + "'", e);
            }
        }

        this.sql2o = new Sql2o(url, user, pass);

        HashMap<String, String> defaultColumnMap = new HashMap<String,String>();
        defaultColumnMap.put("ID", "id");
        defaultColumnMap.put("NAME", "name");
        defaultColumnMap.put("EMAIL", "email");
        defaultColumnMap.put("TEXT", "text");
        defaultColumnMap.put("ANUMBER", "aNumber");
        defaultColumnMap.put("ALONGNUMBER", "aLongNumber");
        sql2o.setDefaultColumnMappings(defaultColumnMap);

        this.url = url;
        this.user = user;
        this.pass = <PASSWORD>;
        this.isHyperSql = "HyperSQL DB test".equals( testName );

        if (this.isHyperSql) {
            sql2o.createQuery("set database sql syntax MSS true").executeUpdate();
        }
    }

    private int insertIntoUsers = 0;

    //@Test  TODO. commented out. Can't get test to work without an application server.
    public void testCreateSql2oFromJndi() throws Exception {
        System.setProperty(Context.INITIAL_CONTEXT_FACTORY, "org.apache.naming.java.javaURLContextFactory");
        System.setProperty(Context.URL_PKG_PREFIXES, "org.apache.naming");

        InitialContext ic = new InitialContext();

        ic.createSubcontext("java:");
        ic.createSubcontext("java:comp");
        ic.createSubcontext("java:comp/env");

        JDBCDataSource datasource = new JDBCDataSource();
        datasource.setUrl(url);
        datasource.setUser(user);
        datasource.setPassword(<PASSWORD>);

        ic.bind("java:comp/env/Sql2o", datasource);

        System.out.println("Datasource initialized.");

        Sql2o jndiSql2o = new Sql2o("Sql2o");

        assertTrue(jndiSql2o != null);
    }

    @Test
    public void testExecuteAndFetch(){
        createAndFillUserTable();

        Date before = new Date();
        List<User> allUsers = sql2o.createQuery("select * from User").executeAndFetch(User.class);
        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 = sql2o.createQuery("select * from User").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 testExecuteAndFetchWithNulls(){
        String sql =
                "create table testExecWithNullsTbl (" +
                "id int identity primary key, " +
                "text varchar(255), " +
                "aNumber int, " +
                "aLongNumber bigint)";
        sql2o.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 = sql2o.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(\n" +
                "id int identity primary key,\n" +
                "name varchar(20),\n" +
                "email varchar(255),\n" +
                "text varchar(100))").executeUpdate();

        String insQuery = "insert into User(name, email, text) values (:name, :email, :text)";

        Connection con = sql2o.beginTransaction();
        int[] inserted = con.createQuery(insQuery).addParameter("name", "test").addParameter("email", "<EMAIL>").addParameter("text", "something exciting").addToBatch()
                .addParameter("name", "test2").addParameter("email", "<EMAIL>").addParameter("text", "something exciting too").addToBatch()
                .addParameter("name", "test3").addParameter("email", "<EMAIL>").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 where id = 2").executeScalar();
        assertTrue(o.getClass().equals(String.class));

        Object o2 = sql2o.createQuery("select 10").executeScalar();
        assertEquals(o2, 10);

        deleteUserTable();
    }

    @Test
    public void testBatchNoTransaction(){

        sql2o.createQuery(
                "create table User(\n" +
                "id int identity primary key,\n" +
                "name varchar(20),\n" +
                "email varchar(255),\n" +
                "text varchar(100))").executeUpdate();

        String insQuery = "insert into User(name, email, text) values (:name, :email, :text)";

        sql2o.createQuery(insQuery).addParameter("name", "test").addParameter("email", "<EMAIL>").addParameter("text", "something exciting").addToBatch()
                .addParameter("name", "test2").addParameter("email", "<EMAIL>").addParameter("text", "something exciting too").addToBatch()
                .addParameter("name", "test3").addParameter("email", "<EMAIL>").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
    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 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 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.isHyperSql) {
            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.isHyperSql) {
            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.256).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.0"), 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(url, user, 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 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(){
        ComplexEntity pojo = sql2o.createQuery("select 1 id, 1 \"entity.id\", 'something' \"entity.value\" from (values(0))", "testComplexTypes").executeAndFetchFirst(ComplexEntity.class);

        assertEquals(1, pojo.id);
        assertEquals(1, pojo.entity.getId());
        assertEquals("something1", pojo.entity.getValue());
    }

//    public void testMultiResult(){
//        sql2o.createQuery("create table multi1(id integer identity primary key, value varchar(20))").executeUpdate();
//        sql2o.createQuery("create table multi2(id integer identity primary key, value2 varchar(20))").executeUpdate();
//
//        sql2o.createQuery("insert into multi1(value) values (:val)")
//                .addParameter("val", "test1").addToBatch()
//                .addParameter("val", "test2").addToBatch()
//                .executeBatch();
//
//        sql2o.createQuery("insert into multi2(value2) values (:val)")
//                .addParameter("val", "test3").addToBatch()<commit_msg>add simple test for table as list map<commit_after>package org.sql2o;

import org.hsqldb.jdbc.JDBCDataSource;
import org.hsqldb.jdbcDriver;
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.*;
import java.util.*;
import java.util.Date;

import static org.hamcrest.CoreMatchers.*;
import static org.junit.Assert.*;

/**
 * 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 {

    @Parameterized.Parameters(name = "{index} - {4}")
    public static Collection<Object[]> getData(){
        return Arrays.asList(new Object[][]{
                {null, "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1","sa", "", "H2 test" },
                {new jdbcDriver(), "jdbc:hsqldb:mem:testmemdb", "SA", "", "HyperSQL DB test"}
        });
    }


    private final Sql2o sql2o;// = new Sql2o(this.url, this.user, this.pass);
    private String url;
    private String user;
    private String pass;
    private boolean isHyperSql;

    public Sql2oTest(Driver driverToRegister, String url, String user, String pass, String testName){

        if (driverToRegister != null) {
            try {
                DriverManager.registerDriver(driverToRegister);
            } catch (SQLException e) {
                throw new RuntimeException("could not register driver '" + driverToRegister.getClass().getName() + "'", e);
            }
        }

        this.sql2o = new Sql2o(url, user, pass);

        HashMap<String, String> defaultColumnMap = new HashMap<String,String>();
        defaultColumnMap.put("ID", "id");
        defaultColumnMap.put("NAME", "name");
        defaultColumnMap.put("EMAIL", "email");
        defaultColumnMap.put("TEXT", "text");
        defaultColumnMap.put("ANUMBER", "aNumber");
        defaultColumnMap.put("ALONGNUMBER", "aLongNumber");
        sql2o.setDefaultColumnMappings(defaultColumnMap);

        this.url = url;
        this.user = user;
        this.pass = pass;
        this.isHyperSql = "HyperSQL DB test".equals( testName );

        if (this.isHyperSql) {
            sql2o.createQuery("set database sql syntax MSS true").executeUpdate();
        }
    }

    private int insertIntoUsers = 0;

    //@Test  TODO. commented out. Can't get test to work without an application server.
    public void testCreateSql2oFromJndi() throws Exception {
        System.setProperty(Context.INITIAL_CONTEXT_FACTORY, "org.apache.naming.java.javaURLContextFactory");
        System.setProperty(Context.URL_PKG_PREFIXES, "org.apache.naming");

        InitialContext ic = new InitialContext();

        ic.createSubcontext("java:");
        ic.createSubcontext("java:comp");
        ic.createSubcontext("java:comp/env");

        JDBCDataSource datasource = new JDBCDataSource();
        datasource.setUrl(url);
        datasource.setUser(user);
        datasource.setPassword(<PASSWORD>);

        ic.bind("java:comp/env/Sql2o", datasource);

        System.out.println("Datasource initialized.");

        Sql2o jndiSql2o = new Sql2o("Sql2o");

        assertTrue(jndiSql2o != null);
    }

    @Test
    public void testExecuteAndFetch(){
        createAndFillUserTable();

        Date before = new Date();
        List<User> allUsers = sql2o.createQuery("select * from User").executeAndFetch(User.class);
        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 = sql2o.createQuery("select * from User").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 testExecuteAndFetchWithNulls(){
        String sql =
                "create table testExecWithNullsTbl (" +
                "id int identity primary key, " +
                "text varchar(255), " +
                "aNumber int, " +
                "aLongNumber bigint)";
        sql2o.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 = sql2o.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(\n" +
                "id int identity primary key,\n" +
                "name varchar(20),\n" +
                "email varchar(255),\n" +
                "text varchar(100))").executeUpdate();

        String insQuery = "insert into User(name, email, text) values (:name, :email, :text)";

        Connection con = sql2o.beginTransaction();
        int[] inserted = con.createQuery(insQuery).addParameter("name", "test").addParameter("email", "<EMAIL>").addParameter("text", "something exciting").addToBatch()
                .addParameter("name", "test2").addParameter("email", "<EMAIL>").addParameter("text", "something exciting too").addToBatch()
                .addParameter("name", "test3").addParameter("email", "<EMAIL>").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 where id = 2").executeScalar();
        assertTrue(o.getClass().equals(String.class));

        Object o2 = sql2o.createQuery("select 10").executeScalar();
        assertEquals(o2, 10);

        deleteUserTable();
    }

    @Test
    public void testBatchNoTransaction(){

        sql2o.createQuery(
                "create table User(\n" +
                "id int identity primary key,\n" +
                "name varchar(20),\n" +
                "email varchar(255),\n" +
                "text varchar(100))").executeUpdate();

        String insQuery = "insert into User(name, email, text) values (:name, :email, :text)";

        sql2o.createQuery(insQuery).addParameter("name", "test").addParameter("email", "<EMAIL>").addParameter("text", "something exciting").addToBatch()
                .addParameter("name", "test2").addParameter("email", "<EMAIL>").addParameter("text", "something exciting too").addToBatch()
                .addParameter("name", "test3").addParameter("email", "<EMAIL>").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
    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 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 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.isHyperSql) {
            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.isHyperSql) {
            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.256).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.0"), 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(url, user, 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();

        Table table = sql2o.createQuery("select * from user").executeAndFetchTable();

        List<Map<String, Object>> 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(){
        ComplexEntity pojo = sql2o.createQuery("select 1 id, 1 \"entity.id\", 'something' \"entity.value\" from (values(0))", "testComplexTypes").executeAndFetchFirst(ComplexEntity.class);

        assertEquals(1, pojo.id);
        assertEquals(1, pojo.entity.getId());
        assertEquals("something1", pojo.entity.getValue());
    }

//    public void testMultiResult(){
//        sql2o.createQuery("create table multi1(id integer identity primary key, value varchar(20))").executeUpdate();
//        sql2o.createQuery("create table multi2(id integer identity primary key, value2 varchar(20))").executeUpdate();
//
//        sql2o.createQuery("insert into multi1(value) values (:val)")
//                .addParameter("val", "test1").addToBatch()
//                .addParameter("val", "test2").addToBatch()
//                .executeBatch();
//
//        sql2o.createQuery("insert into multi2(value2) values (:val)")
//                .addParameter("val", "test3").addToBatch()
{'fraction_non_alphanumeric': 0.14260342213558588, 'fraction_numerical': 0.019406541043967943, 'mean_word_length': 2.9771728831079334, 'pattern_counts': {'":': 0, '<': 59, '<?xml version=': 0, '>': 61, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '517041', 'n_tokens_mistral': 13757, 'n_tokens_neox': 13068, 'n_words': 2794}