void insertSomeFoos(Connection c) throws SQLException
    {
        try(PreparedStatement insert = c.prepareStatement(
                "INSERT INTO FOO(DESCRIPTION) VALUES (?)"
        ))
        {
            for(String descr: asList("one", "two", "three"))
            {
                insert.setString(1, descr);
                insert.executeUpdate();
            }

        }
    }

    List<Foo> selectTheFoos(Connection c) throws SQLException
    {
        try(PreparedStatement s = c.prepareStatement("SELECT ID, DESCRIPTION FROM FOO"))
        {
            List<Foo> result = new ArrayList<>();
            ResultSet rs = s.executeQuery();
            while(rs.next())
            {
                result.add(new Foo(rs.getInt(1), rs.getString(2)));
            }

            return result;

        }
    }

A run(Connection c) throws SQLException;

   @Test
   public void nothingHappens()
   {
       @SuppressWarnings("unused")
       SelectOp.FjList<String> selectThem = new SelectOp.FjList<>(
               "SELECT FOO FROM WHATEVER",
               NO_BINDER,
               rs -> rs.getString(1)
       );
   }

            @Test
            public void syncInterpreter()
            {
                // create a synchronous DB interpreter. It is a stateless object, and the act of creating one is also
                // referentially transparent
                SyncDbInterpreter dbi = new SyncDbInterpreter(
                        // provide a piece of code which knows how to spawn connections
                        // in this case we are just using the DriverManager
                        () -> DriverManager.getConnection("jdbc:hsqldb:mem:DescribeVsInterpret", "sa", "")
                );
        
                // submit an Update (mutate) operation which creates a table
                dbi.submit(new UpdateOp(
                        "CREATE TABLE FOO (WHATEVER VARCHAR(200))",
                        NO_BINDER
                ));
        
                // objects we will insert in the table
                List<String> helloSaneDbc = List.arrayList("hello", "sane", "dbc");
        
                //insert some data
                Option<Integer> updateCount = dbi.submit(
                        //this describes an operation which inserts an iterable of objects in a table via addBatch / executeBatch
                        new BatchUpdateOp<>(
                                "INSERT INTO FOO(WHATEVER) VALUES(?)",
                                x -> preparedStatement -> preparedStatement.setString(1, x),
                                helloSaneDbc
                        )
                );
        
                // the operation returns an optional update count, since the JDBC driver might not return an update count at all
                assertThat(updateCount, is(some(3)));
        
                
                List<String> result = dbi.submit(
                        // select all of the objects in the table
                        new SelectOp.FjList<>("SELECT WHATEVER FROM FOO", NO_BINDER, resultSet -> resultSet.getString(1))
                );
        
                
                assertThat(result, is(helloSaneDbc));
            }

package com.novarto.sanedbc.examples;

import com.novarto.sanedbc.core.interpreter.SyncDbInterpreter;
import com.novarto.sanedbc.core.ops.*;
import fj.Unit;
import fj.control.db.DB;
import fj.data.List;
import fj.data.Option;
import org.junit.Test;

import java.sql.DriverManager;

import static com.novarto.sanedbc.core.ops.DbOps.unique;
import static fj.data.List.nil;
import static java.util.Arrays.asList;
import static org.hamcrest.CoreMatchers.is;
import static org.junit.Assert.assertThat;

public class BasicUsage
{

    /*
     * In a real-world application, DB-related operations are usually encapsulated in a separate module, so that's what we'll do here.
     * It's kind of like a DAO (Data Access Object) since it provides operations over a table, or a set of logically related tables.
     *
     * It's different from a DAO in that it does not access the database itself; rather it returns descriptions of how it should
     * be accessed - as already explained.
     *
     */
    public static final class StuffDb
    {

        /*
            Create the STUFF table.
            An EffectOp is an operation which takes nothing, and returns nothing( fj.Unit ). It is useful for performing
            side effects which take no parameters, such as executing DDL:

         */
        public static DB<Unit> createStuffTable()
        {
            //this specific table has auto-generated ID
            return new EffectOp("CREATE TABLE STUFF (ID INTEGER PRIMARY KEY IDENTITY, DESCRIPTION NVARCHAR(200) NOT NULL)");
        }

        /*
            Insert a single record. Return the updateCount - in this case it will always be 1
         */
        public static DB<Integer> insertStuff(String description)
        {
            // an UpdateOp takes SQL, and a binder. It returns an update count
            // a binder is a piece of code which takes a prepared statement, and returns nothing.
            // it is so called because it binds the prepared statement parameters
            return new UpdateOp(
                    "INSERT INTO STUFF(DESCRIPTION) VALUES(?)",
                    // bind the single parameter of the statement
                    ps -> ps.setString(1, description)
            );
        }

        /*
            Insert a single record, and return its generated key
         */
        public static DB<Integer> insertStuffGetKey(String description)
        {
            /*
                An InsertGenKeysOp is the same as an UpdateOp, only that it expects an auto-generated key to be present
                after executing the update, and returns it as the result.

                InsertGenKeysOp.Int is a specialization which expects an Integer key
             */
            return new InsertGenKeysOp.Int(
                    "INSERT INTO STUFF(DESCRIPTION) VALUES(?)",
                    // you can see that the binder is the same as in the previous operation.
                    // in that case it is good practice to extract it as a static final field of your module
                    ps -> ps.setString(1, description)
            );
        }

        /*
            Select entries by description
         */
        public static DB<List<Stuff>> selectByDescription(String description)
        {
            /*
                A select operation takes SQL, a binder and a mapper, and returns an iterable of results
                A mapper is a function which maps a single row from the resultset to a single object
                A generic SelectOp also needs to be told what collection to use in the result
                The SelectOp.FjList specialization uses fj.data.List, which is an immutable singly-linked list.
                (Also known as Cons list)
                There is also a specialization for java Lists; or you can supply your own collection builder
             */
            return new SelectOp.FjList<>(
                    "SELECT ID, DESCRIPTION FROM STUFF WHERE DESCRIPTION=?",
                    ps -> ps.setString(1, description),
                    //build a Stuff from a resultset row
                    rs -> new Stuff(rs.getInt(1), rs.getString(2))
                    );
        }

        /*
            Select an entry by id
            The return type is fj.data.Option, since it may be that no entry with this id exists
            fj.data.Option is equivalent to Java Optional
         */
        public static DB<Option<Stuff>> selectByKey(int id)
        {

            //given a regular operation which returns an iterable:
            DB<List<Stuff>> temp = new SelectOp.FjList<>(
                    "SELECT ID, DESCRIPTION FROM STUFF WHERE ID=?",
                    ps -> ps.setInt(1, id),
                    rs -> new Stuff(rs.getInt(1), rs.getString(2)));

            // using the unique() function,
            // we can convert it to an operation which expects at most one result, and returns that optional result:
            return unique(temp);
        }

        /*
            Insert many entries, using addBatch / executeBatch, and return the update count.
            It is possible that a JDBC driver does not return the update count, therefore the optional result.
         */
        public static DB<Option<Integer>> insertMany(Iterable<String> descriptions)
        {
            return new BatchUpdateOp<>(
                    //SQL
                    "INSERT INTO STUFF(DESCRIPTION) VALUES(?)",
                    //given the current element of the iterable, return a binder which sets the parameters for that element
                    // if you extracted the binder from the previous operations in a field named SET_DESCRIPTION, code becomes
                    // description -> SET_DESCRIPTION
                    description -> ps -> ps.setString(1, description),
                    //the iterables to insert
                    descriptions
            );
        }

        /*
            Count all entries in the table, where the description is LIKE the passed parameter
         */
        public static DB<Long> count(String like)
        {
            String searchQuery = like.trim().toLowerCase();

            //an aggregate op expects the resultset to have one element, and that element to be cast to long
            //useful for numeric aggregate operations
            return new AggregateOp("SELECT COUNT(*) FROM STUFF WHERE DESCRIPTION LIKE LOWER(?)",
                    ps -> ps.setString(1, searchQuery   "%")
            );
        }


    }

    @Test
    public void testIt()
    {
        SyncDbInterpreter dbi = new SyncDbInterpreter(
                () -> DriverManager.getConnection("jdbc:hsqldb:mem:test", "sa", ""
        ));
        dbi.submit(StuffDb.createStuffTable());

        Integer updateCount = dbi.submit(StuffDb.insertStuff("stuff 1"));
        assertThat(updateCount, is(1));

        Integer generatedKey = dbi.submit(StuffDb.insertStuffGetKey("stuff 2"));

        List<Stuff> stuffs = dbi.submit(StuffDb.selectByDescription("no such"));
        assertThat(stuffs.isEmpty(), is(true));

        stuffs = dbi.submit(StuffDb.selectByDescription("stuff 1"));
        //with head() we select the first element of the immutable list
        assertThat(stuffs.head().description, is("stuff 1"));
        //with tail() we select the rest of the list. it should be the empty list: nil()
        assertThat(stuffs.tail(), is(nil()));

        Option<Stuff> stuff2Option = dbi.submit(StuffDb.selectByKey(generatedKey));
        assertThat(stuff2Option.isSome(), is(true));
        Stuff stuff2 = stuff2Option.some();
        assertThat(stuff2, is(new Stuff(generatedKey, "stuff 2")));

        Option<Integer> batchCountOpt = dbi.submit(StuffDb.insertMany(asList("a", "b", "c")));
        Integer batchCount = batchCountOpt.some();
        assertThat(batchCount, is(3));

        Long stuCount = dbi.submit(StuffDb.count("StU"));
        //stuff 1 and stuff 2 match StU, so count should be 2
        assertThat(stuCount, is(2L));

    }

    public static class Stuff
    {
        public final int id;
        public final String description;

        public Stuff(int id, String description)
        {
            this.id = id;
            this.description = description;
        }

        @Override public boolean equals(Object o); //... noise ommitted
        

        @Override public int hashCode(); //... noise ommitted
       
    }
}

public final <B> DB<B> map(final F<A, B> f)

public static class UserDB
    {


        public static DB<Boolean> login(String email, String pass)
        {
            //we map an operation DB<Option<User>> with a function which takes an Option<User> and returns a boolean,
            //resulting in a DB<Boolean>
            return selectUser(email).map(userOpt -> {
                if (userOpt.isNone())
                {
                    //invalid email
                    return false;
                }

                User user = userOpt.some();
                //check pass hash matches
                return loginOk(user, pass);
            });
        }

        private static boolean loginOk(User user, String password)
        {
            return hash(password).equals(user.hash);
        }
        
        private static String hash(String password); // impl omitted

    }

        dbi.submit(UserDB.insertUser("[email protected]", "abcd"));

        boolean success = dbi.submit(UserDB.login("[email protected]", "abcd"));
        assertThat(success, is(true));

        success = dbi.submit(UserDB.login("[email protected]", "wrong"));
        assertThat(success, is(false));

        success = dbi.submit(UserDB.login("[email protected]", "abcd"));
        assertThat(success, is(false));

public final <B> DB<B> bind(final F<A, DB<B>> f);

        //the operation authenticates the user, and reads their orders
        //it returns either an error message (string), in case the login fails; or the list of orders
        public static DB<Either<String, List<Order>>> authenticateAndGetOrders(String email, String pass)
        {
            // with bind (a.k.a. flatMap), we take the result of one operation, and use it to return another operation
            return UserDB.login(email, pass).bind(success -> {

                if(!success)
                {
                    Either<String, List<Order>> errorMessage = Either.left("auth failure");
                    // the DB.unit operation returns an immediate result with the passed value, without touching the connection
                    return DB.unit(errorMessage);
                }

                return selectOrdersByEmail(email).map(orders -> Either.right(orders));
            });
        }

        public static DB<List<Order>> selectOrdersByEmail(String userEmail)
        {
            return new SelectOp.FjList<>(
                    "SELECT ORDER_ID, USER_EMAIL, TEXT FROM ORDERS WHERE USER_EMAIL=?",
                    ps -> ps.setString(1, userEmail),
                    rs -> new Order(rs.getInt(1), rs.getString(2), rs.getString(3))
            );
        }

        dbi.submit(UserDB.insertUser("[email protected]", "abcd"));
        dbi.submit(UserDB.insertUser("[email protected]", "abcd"));

        dbi.submit(
                OrderDb.insertOrders(
                        arrayList(new CreateOrder("[email protected]", "Hi there"),
                                new CreateOrder("[email protected]", "Bye there")
                        ))
        );

        Either<String, List<Order>> result = dbi.submit(OrderDb.authenticateAndGetOrders("[email protected]", "abcd"));
        assertThat(result.isRight(), is(true));

        List<Order> orders = result.right().value();
        assertThat(orders.isSingle(), is(true));

        Order johnOrder = orders.head();
        assertThat(johnOrder.text, is("Hi there"));

        Either<String, List<Order>> shouldFail = dbi.submit(OrderDb.authenticateAndGetOrders("haxx0r", "abcd"));
        assertThat(shouldFail, is(Either.left("auth failure")));

db.map(iterable -> iterable.reduce(...));

// we will select employees and group them by department
// the result type is immutable map from integer (department id) to a immutable list of employees from that department
FoldLeftSelectOp<HashArrayMappedTrie<Integer, List<Employee>>> selectGroupedByDepartment =
new FoldLeftSelectOp<>(
    "SELECT * FROM EMPLOYEES",
    NO_BINDER,
    (soFar, rs) -> {
        // the current row of the resultset, which we will append to the result so far
        Employee employee = new Employee(rs.getInt(1), rs.getString(2), rs.getInt(3));

        // the employees collected so far for this department
        // if this is the first employee of this department, fromThisDepartment will be none()
        Option<List<Employee>> fromThisDepartment = soFar.find(employee.departmentId);

        return fromThisDepartment
            //in case there are already employees collected for this department,
            //append this employee to the already collected (cons)
            //and update the map so far, associating departmentId with the appended list
            .map(employees -> soFar.set(employee.departmentId, employees.cons(employee)))
            //otherwise update the map so far, associating departmentId with a sized-one list
            //containing this employee
            .orSome(() -> soFar.set(employee.departmentId, List.single(employee)));
        },

        initial
    );

import static com.novarto.sanedbc.core.ops.DbOps.sequence;

//given an iterable of DB's
List<DB<String>> dbList = arrayList(
    DB.unit("foo"),
    DB.unit("bar"),
    DB.unit("baz")
);

//we can treat it as a DB<List>
DB<List<String>> db = sequence(dbList);

List<String> result = dbi.submit(db);

assertThat(result, is(arrayList("foo", "bar", "baz")));

// a datasource backed by a HikariCP connection pool
HikariDataSource hikariDS = Hikari.createHikari("jdbc:hsqldb:mem:test", "sa", "", new Properties());


SyncDbInterpreter sync = new SyncDbInterpreter(
    //transform a datasource to a Try0<Connection, SqlException>
    lift(hikariDS)
);

sync.submit(new EffectOp("INSERT INTO DUMMY VALUES(1,'a')"));
Long count = sync.submit(new AggregateOp("SELECT COUNT(*) FROM DUMMY"));
assertThat(count, is(1L));

try
{
    sync.transact(new EffectOp(
        "INSERT INTO DUMMY VALUES((2, 'b'), (1,'a'))"
    ));
    fail("expected constraint violation");
}
catch (RuntimeException e)
{
    assertThat(e.getCause(), is(instanceOf(SQLException.class)));
    count = sync.submit(new AggregateOp("SELECT COUNT(*) FROM DUMMY"));
    //transactional, no records were updated
    assertThat(count, is(1L));
}

//construct an interpreter that turns the result type to Validation<Exception, A>
// we can reuse the same data source across multiple interpreters
ValidationDbInterpreter vdb = new ValidationDbInterpreter(lift(hikariDS));

Validation<Exception, Long> successExpected = vdb.submit(new AggregateOp("SELECT COUNT(*) FROM DUMMY"));
assertThat(successExpected.isSuccess(), is(true));
assertThat(successExpected.success(), is(1L));

RuntimeException rte = new RuntimeException("failed I have");
Validation<Exception, Long> failExpected = vdb.submit(new DB<Long>()
{
    @Override public Long run(Connection c) throws SQLException
    {
        // all subclasses of java.lang.Exception and lifted to Validation, not just SqlException
        throw rte;
    }
});

assertThat(failExpected.isFail(), is(true));
assertThat(failExpected.fail(), is(rte));

ExecutorService ex = Executors.newCachedThreadPool();

// submits DB operations using the supplied executor, returns CompletableFuture<A>
AsyncDbInterpreter async = new AsyncDbInterpreter(lift(hikariDS), ex);

CompletableFuture<Long> countFuture = async.submit(new AggregateOp("SELECT COUNT(*) FROM DUMMY"));
//blocking call, don't do this in production
Long theCount = countFuture.get();
assertThat(theCount, is(1L));

CompletableFuture<Long> failedFuture = async.submit(new AggregateOp("BLA BLA BLA"));

//blocking call, don't do in production
TestUtil.waitFor(() -> failedFuture.isCompletedExceptionally(), 5, SECONDS);

Throwable failure = getFailure(failedFuture);

assertThat(failure.getCause(), instanceOf(SQLException.class));

    someDbOp() // is eventually interpreted in a thread 'X'
        .map(function1) //happens in thread 'X'
        .bind(result -> anotherDbOp(result)) //lambda called in thread 'X', and anotherDbOp() interpreted in thread 'X'
        .map(otherFunction) //thread 'X' again

    AsyncDbInterpreter ai = ...;
    CompletableFuture<A> aFuture = ai.submit(op1);
    CompletableFuture<B> bFuture = ai.transact(op2);