Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

JEPLayer, Java 8 and jOOQ: A Match Made in Swiss Heaven

DZone's Guide to

JEPLayer, Java 8 and jOOQ: A Match Made in Swiss Heaven

· Cloud Zone
Free Resource

Are you joining the containers revolution? Start leveraging container management using Platform9's ultimate guide to Kubernetes deployment.

This tutorial shows how to use JEPLayer  ORM (v1.3) and jOOQ for SQL generation.

Introduction

JEPLayer is a non-intrusive approach to persistence of pure POJOs, a lightweight alternative to more intrusive and "magic" approaches like JPA implementations. JEPLayer tries to simplify to extreme the JDBC lifecycle hiding the JDBC API, but through listeners we can optionally access to the underlying pure JDBC layer avoiding as much as possible the API repetition of JDBC in JEPLayer. JEPLayer has a special focus on managing JDBC and JTA transactions.

jOOQ is a complete RDMS Java toolset created by the Swiss citizen Lukas Eder , JEPLayer is also an ORM, they provide two different point of view of solving the Object-Relational problem in a Java environment.

In spite of JEPLayer ORM capabilities, a powerful SQL syntax modeled by Java objects is missing in JEPLayer (instead of plain text), jOOQ SQL modeling in Java is probably the most complete in the world. jOOQ is a perfect match for JEPLayer to write robust, error-free, refactoring friendly SQL code based on Java.

JEPLayer v1.3 introduces automatic implicit SQL generation for INSERT, UPDATE and DELETE actions, no explicit SQL is needed, anyway jOOQ SQL in Java is still invaluable for SELECT queries where complexity can be cumbersome and error prone.

In this tutorial the use of jOOQ is brief, SQL examples are very simple, SQL management of jOOQ is much more powerful than shown here.

The other new kid on the block is Java 8 (Java 1.8 specification), specially interesting for JEPLayer are streams and lambdas. JEPLayer is designed using a fluid API customized with listeners most of them based on a single method, perfect to get code simplified using lambdas, in the same time, JEPLayer returns "alive" result sets based on List (and ListIterator) interfaces, and as you know a List can be easily converted to a stream in Java 8 for some type of processing. The code of this tutorial could be even less verbose when using lambdas, but some extra unnecessary variables are exposed to show the name of the interface listener involved, because remember, this is a JEPLayer tutorial (not a Java 8 tutorial).

The first part of this tutorial just show the typical DAO class based on JEPLayer to manage a POJO class (Contact). One important feature of JEPLayer is the absolute respect to the user data model, no annotation, interface or similar artifact is needed in data model, data model is clean and independent from the persistence layer (JEPLayer), only some optional Java bean property conventions are required to easy class and attribute mapping to table and columns.

The second part of this tutorial shows some use examples of persistent actions grouped, most of them using JDBC transactions (no JTA API of JEPLayer is used in this example). Most of the code is repetitive (code similar doing the same) because the motivation is to show the extreme JDBC customization allowed by JEPLayer, especially in transaction management.

Where is the code

The code of this example can be found in:

https://github.com/jmarranz/jeplayer_examples/tree/master/jeplayer_jooq

Requisites 

This example has been coded in NetBeans using Maven, Java 8 JDK, MySQL and the C3PO connection pool. JEPLayer does not mandate a concrete connection pool, just to show a "real world" configuration.

Because there is no custom SQL code, any other RDBMS database could be used with no business code change (just changing data source bootstrap).

Now the code

The methodology of this tutorial is simple, let's to show the code first, and later we will explain the details of every code snippet.

The code of this tutorial just have two parts, the DAO class and use examples managing a Contact POJO class and the second part talks about transactions. 

The ultra complex Contact class

public class Contact
{
    protected int id;
    protected String name;
    protected String phone;
    protected String email;
    protected String notPersisAttr;
    
    public Contact(int id, String name, String phone, String email)
    {
        this.id = id;
        this.name = name;
        this.phone = phone;
        this.email = email;
    }
 
    public Contact()
    {
    }
 
    public String getEmail()
    {
        return email;
    }
 
    public void setEmail(String email)
    {
        this.email = email;
    }
 
    public int getId()
    {
        return id;
    }
 
    public void setId(int id)
    {
        this.id = id;
    }
 
    public String getName()
    {
        return name;
    }
 
    public void setName(String name)
    {
        this.name = name;
    }
 
    public String getPhone()
    {
        return phone;
    }
 
    public void setPhone(String phone)
    {
        this.phone = phone;
    }
 
    public String getNotPersisAttr() 
    {
        return notPersisAttr;
    }
 
    public void setNotPersisAttr(String notPersisAttr) 
    {
        this.notPersisAttr = notPersisAttr;
    }    
}

The DAO class managing Contact objects

package example.jeplayer.jooq.dao;
 
import example.jeplayer.jooq.model.Contact;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.AbstractMap;
import java.util.List;
import java.util.Map;
import jepl.JEPLColumnDesc;
import jepl.JEPLConnection;
import jepl.JEPLDAO;
import jepl.JEPLNonJTADataSource;
import jepl.JEPLPersistAction;
import jepl.JEPLPreparedStatement;
import jepl.JEPLPreparedStatementListener;
import jepl.JEPLResultSet;
import jepl.JEPLResultSetDALListener;
import jepl.JEPLResultSetDAO;
import jepl.JEPLResultSetDAOBeanMapper;
import jepl.JEPLResultSetDAOListener;
import jepl.JEPLTask;
import jepl.JEPLUpdateDAOBeanMapper;
import jepl.JEPLUpdateDAOListener;
import static org.jooq.impl.DSL.count;
import static org.jooq.impl.DSL.field;
import static org.jooq.impl.DSL.table;
import org.jooq.impl.DefaultDSLContext;
 
/**
 *  https://groups.google.com/forum/#!topic/jooq-user/2HTS-0DE1M8
 * 
 *  Because we're using JOOQ, binding params by name (provided by JEPLayer) are not used in this example
 * 
 * @author jmarranz
 */
public class ContactDAO
{
    protected JEPLDAO<Contact> dao;
    protected DefaultDSLContext jooqCtx;
    protected JEPLUpdateDAOListener<Contact> updateListener;    
    protected JEPLResultSetDAOListener<Contact> resultSetListener; 
    
    public ContactDAO(JEPLNonJTADataSource jds,DefaultDSLContext jooqCtx)
    {
        this(jds,jooqCtx,(short)0);        
    }
    
    public ContactDAO(JEPLNonJTADataSource jds,DefaultDSLContext jooqCtx,short mappingMode)
    {
        this.dao = jds.createJEPLDAO(Contact.class); 
        this.jooqCtx = jooqCtx;
        
        // This 3 mapping approaches provides the same behaviour in this simple example, they are coded 
        // just to show the different options for mapping         
        switch(mappingMode)
        {
            case 0: // default mapping attribs and columns by name ignoring case
                this.updateListener = jds.createJEPLUpdateDAOListenerDefault(Contact.class);
                this.resultSetListener = jds.createJEPLResultSetDAOListenerDefault(Contact.class); 
                break;
            case 1: // custom mapping 
                this.updateListener = new JEPLUpdateDAOListener<Contact>() {
                        @Override
                        public String getTable(JEPLConnection jcon, Contact obj) throws Exception {
                            return "CONTACT";
                        }
 
                        @Override
                        public Map.Entry<JEPLColumnDesc, Object>[] getColumnDescAndValues(JEPLConnection jcon, Contact obj, JEPLPersistAction action) throws Exception 
                        {
                            Map.Entry<JEPLColumnDesc,Object>[] result = new AbstractMap.SimpleEntry[]
                            {
                                new AbstractMap.SimpleEntry<>(new JEPLColumnDesc("ID").setAutoIncrement(true).setPrimaryKey(true),obj.getId()),
                                new AbstractMap.SimpleEntry<>(new JEPLColumnDesc("NAME"),obj.getName()),                    
                                new AbstractMap.SimpleEntry<>(new JEPLColumnDesc("PHONE"),obj.getPhone()),                    
                                new AbstractMap.SimpleEntry<>(new JEPLColumnDesc("EMAIL"),obj.getEmail())                    
                            };
                            return result;
                        }            
                    };                
                
                this.resultSetListener = new JEPLResultSetDAOListener<Contact>() {
                        @Override
                        public void setupJEPLResultSet(JEPLResultSet jrs,JEPLTask<?> task) throws Exception {
                        }
 
                        @Override
                        public Contact createObject(JEPLResultSet jrs) throws Exception {
                            return new Contact();
                        }
 
                        @Override
                        public void fillObject(Contact obj,JEPLResultSet jrs) throws Exception {
                            ResultSet rs = jrs.getResultSet();
                            obj.setId(rs.getInt("ID"));
                            obj.setName(rs.getString("NAME"));
                            obj.setPhone(rs.getString("PHONE"));
                            obj.setEmail(rs.getString("EMAIL"));
                        }    
                    };
                break;
            case 2:  // default mapping using custom row-mappers              
                JEPLUpdateDAOBeanMapper<Contact> updateMapper = (Contact obj, JEPLConnection jcon, String columnName, Method getter, JEPLPersistAction action) -> { // public Object getColumnFromBean(Contact obj, JEPLConnection jcon, String columnName, Method getter, JEPLPersistAction action) throws Exception
                        if (columnName.equalsIgnoreCase("email"))
                        {
                            return obj.getEmail();
                        }
                        return JEPLUpdateDAOBeanMapper.NO_VALUE;
                    };                                    
                this.updateListener = jds.createJEPLUpdateDAOListenerDefault(Contact.class,updateMapper);
                    
                JEPLResultSetDAOBeanMapper<Contact> resultMapper = (Contact obj, JEPLResultSet jrs, int col, String columnName, Object value, Method setter) -> { // setColumnInBean(...)
                        if (columnName.equalsIgnoreCase("email"))
                        {
                            obj.setEmail((String)value);
                            return true;
                        }
                        return false;
                    };
                this.resultSetListener = jds.createJEPLResultSetDAOListenerDefault(Contact.class,resultMapper);                
                break;
            case 3:
                throw new RuntimeException("Unexpected");
        }
          
        dao.addJEPLListener(updateListener);        
        dao.addJEPLListener(resultSetListener);          
    }    
    
    public JEPLDAO<Contact> getJEPLDAO()
    {
        return dao;
    }
    
    public void insert(Contact contact)
    {
        int key = dao.createJEPLDALQuery(
                 jooqCtx.insertInto(table("CONTACT"),field("EMAIL"),field("NAME"),field("PHONE"))
                         .values("email","name", "phone").getSQL()) // INSERT INTO CONTACT (EMAIL, NAME, PHONE) VALUES (?, ?, ?)
                .addParameters(contact.getEmail(),contact.getName(),contact.getPhone())
                .getGeneratedKey(int.class);
        contact.setId(key);
    }     
    
    public void insertImplicitUpdateListener(Contact contact)
    {
        int key = dao.insert(contact).getGeneratedKey(int.class);
        contact.setId(key);
    }        
    
    public void insertExplicitResultSetListener(Contact contact)
    {
        // Just to show how data conversion can be possible if required
        JEPLResultSetDALListener listener = new JEPLResultSetDALListener()
        {
            @Override
            public void setupJEPLResultSet(JEPLResultSet jrs,JEPLTask<?> task) throws Exception { 
            }
            @Override
            @SuppressWarnings("unchecked")
            public <U> U getValue(int columnIndex, Class<U> returnType, JEPLResultSet jrs) throws Exception
            {
                if (!returnType.equals(int.class)) throw new RuntimeException("UNEXPECTED");
                // Expected columnIndex = 1 (only one row and one column is expected)
                ResultSet rs = jrs.getResultSet();
                int resInt = rs.getInt(columnIndex);
                Object resObj = rs.getObject(columnIndex);
                Integer resIntObj = (Integer)jrs.getJEPLStatement().getJEPLDAL().cast(resObj, returnType);
                if (resInt != resIntObj) throw new RuntimeException("UNEXPECTED");
                return (U)resIntObj; 
            }
        };
 
        int key = dao.createJEPLDALQuery(
                    jooqCtx.insertInto(table("CONTACT"),field("EMAIL"),field("NAME"),field("PHONE"))
                            .values("email","name", "phone").getSQL()) // INSERT INTO CONTACT (EMAIL, NAME, PHONE) VALUES (?, ?, ?)                
                    .addParameters(contact.getEmail(),contact.getName(),contact.getPhone())
                    .addJEPLListener(listener)
                    .getGeneratedKey(int.class);
         contact.setId(key);
    }    
    
    public boolean update(Contact contact)
    {
        int updated = dao.createJEPLDALQuery(
                jooqCtx.update(table("CONTACT"))
                        .set(field("EMAIL"), "email")
                        .set(field("NAME"),  "name")
                        .set(field("PHONE"), "phone")
                        .where(field("ID").equal(0)).getSQL()) // "UPDATE CONTACT SET EMAIL = ?, NAME = ?, PHONE = ? WHERE ID = ?")
                .addParameters(contact.getEmail(),contact.getName(),contact.getPhone(),contact.getId())
                .executeUpdate();
        return updated > 0;
    }    
    
    public boolean updateImplicitUpdateListener(Contact contact)
    {
        int updated = dao.update(contact)
                .executeUpdate();
        return updated > 0;
    }        
    
    public boolean delete(Contact contact)
    {
        return deleteById(contact.getId());
    }
    
    public boolean deleteById(int id)
    {
        int deleted = dao.createJEPLDALQuery( jooqCtx.delete(table("CONTACT")).where(field("ID").equal(0)).getSQL() ) // "DELETE FROM CONTACT WHERE ID = ?" 
                        .addParameters(id)             
                        .executeUpdate();     
        return deleted > 0;
    }    
    
    public boolean deleteImplicitUpdateListener(Contact contact)
    {
        int deleted = dao.delete( contact )       
                        .executeUpdate();     
        return deleted > 0;
    }    
    
    public int deleteAll()
    {
        return dao.createJEPLDALQuery( jooqCtx.delete(table("CONTACT")).getSQL() ) // "DELETE FROM CONTACT" 
                            .executeUpdate();      
    }    
  
    
    public JEPLResultSetDAO<Contact> selectActiveResult()
    {
        return dao.createJEPLDAOQuery( jooqCtx.selectFrom(table("CONTACT")).getSQL() ) // "SELECT * FROM CONTACT"
                    .getJEPLResultSetDAO();    
    }                 
    
    public static Contact[] toContactArray(JEPLResultSetDAO<Contact> list)
    {
        if (list.isClosed()) throw new RuntimeException("Unexpected");
        int size = list.size();
        Contact[] res = ((List<Contact>)list).toArray(new Contact[size]); // JEPLResultSetDAO implements List interface
        if (!list.isClosed()) throw new RuntimeException("Unexpected");
        return res;
    }         
    
    public List<Contact> selectNotActiveResult()
    {
        // "ORDER BY ID" is not really needed, is just to play with jooq
        List<Contact> list = dao.createJEPLDAOQuery( jooqCtx.selectFrom(table("CONTACT")).orderBy(field("ID")).getSQL() ) // "SELECT * FROM CONTACT ORDER BY ID"
                .getResultList();       
        return list;
    }            
    
    public List<Contact> selectNotActiveResult(int maxResults)
    {
        // "ORDER BY" is not really needed, is just to play with jooq
        List<Contact> list = dao.createJEPLDAOQuery( jooqCtx.selectFrom(table("CONTACT")).orderBy(field("ID"),field("NAME")).getSQL() ) // "SELECT * FROM CONTACT ORDER BY ID,NAME"
                .setMaxResults(maxResults)
                .getResultList();       
        return list;
    }             
    
    public List<Contact> selectNotActiveResult2(final int maxResults)
    {
        // Another (verbose) approach using JDBC        
        JEPLPreparedStatementListener<List<Contact>> listener = (JEPLPreparedStatement jstmt,JEPLTask<List<Contact>> task) -> { // void setupJEPLPreparedStatement(...) throws Exception
 
            PreparedStatement stmt = jstmt.getPreparedStatement();
            int old = stmt.getMaxRows();
            stmt.setMaxRows(maxResults);
            try
            {
                List<Contact> res = task.exec();
            }
            finally
            {
                stmt.setMaxRows(old); // Restore
            }            
        };
 
        return dao.createJEPLDAOQuery(jooqCtx.selectFrom(table("CONTACT")).getSQL())
                .addJEPLListener(listener)
                .getResultList();
    }    
    
    public int selectCount()  // selectCount method name is used instead "count" to avoid name clashing with jooq "org.jooq.impl.DSL.count()"
    {
        return dao.createJEPLDALQuery( jooqCtx.select(count()).from(table("CONTACT")).getSQL() )
                            .getOneRowFromSingleField(int.class);     
    }
    
    public List<Contact> selectRange(int from,int to)
    {
        // The simplest form
        return dao.createJEPLDAOQuery(jooqCtx.selectFrom(table("CONTACT")).orderBy(field("ID")).getSQL() ) // "SELECT * FROM CONTACT ORDER BY ID"
                .setFirstResult(from)
                .setMaxResults(to - from)
                .getResultList();
    }    
    
    public List<Contact> selectRange2(final int from,final int to)
    {
        // Another (verbose) approach using JDBC
        JEPLResultSetDAOListener<Contact> listener = new JEPLResultSetDAOListener<Contact>()
        {
            @Override
            public  void setupJEPLResultSet(JEPLResultSet jrs,JEPLTask<?> task) throws Exception
            {
                resultSetListener.setupJEPLResultSet(jrs, task);
                
                ResultSet rs = jrs.getResultSet();
                rs.absolute(from); // Is not still closed
 
                // Not needed, just to know the state of ResultSet and some demostrative check:
                @SuppressWarnings("unchecked")
                List<Contact> res = (List<Contact>)task.exec(); 
                if (res.size() > to - from) throw new RuntimeException("Unexpected");
            }
 
            @Override
            public Contact createObject(JEPLResultSet jrs) throws Exception
            {
                return resultSetListener.createObject(jrs);
            }
 
            @Override
            public void fillObject(Contact obj,JEPLResultSet jrs) throws Exception
            {
                resultSetListener.fillObject(obj, jrs);
                
                ResultSet rs = jrs.getResultSet();
                int row = rs.getRow(); 
                if (row + 1 == to)
                    jrs.stop();
            }
        };
 
        return dao.createJEPLDAOQuery(jooqCtx.selectFrom(table("CONTACT")).orderBy(field("ID")).getSQL() ) // "SELECT * FROM CONTACT ORDER BY ID"
                .addJEPLListener(listener)
                .getResultList();
    }    
}

Finally the Use Cases

package example.jeplayer.jooq;
 
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mchange.v2.c3p0.DataSources;
import example.jeplayer.jooq.dao.ContactDAO;
import example.jeplayer.jooq.model.Contact;
import java.beans.PropertyVetoException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.List;
import jepl.JEPLBootRoot;
import jepl.JEPLCachedResultSet;
import jepl.JEPLConnection;
import jepl.JEPLConnectionListener;
import jepl.JEPLDAL;
import jepl.JEPLNonJTADataSource;
import jepl.JEPLResultSet;
import jepl.JEPLResultSetDAO;
import jepl.JEPLTask;
import jepl.JEPLTransaction;
import jepl.JEPLTransactionalNonJTA;
import org.jooq.Field;
import org.jooq.SQLDialect;
import static org.jooq.impl.DSL.avg;
import static org.jooq.impl.DSL.count;
import static org.jooq.impl.DSL.field;
import static org.jooq.impl.DSL.table;
import org.jooq.impl.DefaultDSLContext;
import org.junit.After;
import org.junit.AfterClass;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
 
/**
 *
 * @author jmarranz
 */
public class TestJooq
{
    public TestJooq()
    {
    }
    
    // Here the test setup stuff...
    
    @BeforeClass
    public static void setUpClass()
    {
    }
    
    @AfterClass
    public static void tearDownClass()
    {
    }
    
    @Before
    public void setUp()
    {
    }
    
    @After
    public void tearDown()
    {
    }
        
    @Test
    public void jooqExample() throws Exception
    {    
        for(short i = 0; i < 3; i++)
        {
            System.out.println("Mapping mode:" + i);
            jooqExample(i);
        }
    }
    
    private void jooqExample(short mappingMode) throws Exception
    {
        ComboPooledDataSource ds = new ComboPooledDataSource();        
        try
        {
            configureDataSource(ds);
 
            DefaultDSLContext jooqCtx = new DefaultDSLContext(SQLDialect.MYSQL);            
            
            JEPLNonJTADataSource jds = JEPLBootRoot.get().createJEPLBootNonJTA().createJEPLNonJTADataSource(ds);           
            JEPLConnectionListener conListener = (JEPLConnection con,JEPLTask task2) -> { // void setupJEPLConnection(JEPLConnection con,JEPLTask task) throws Exception                      
                        con.getConnection().setAutoCommit(true); 
                    };            
            jds.addJEPLListener(conListener); // Simple alternative:  jds.setDefaultAutoCommit(true);
            
            createTables(jds);
                       
            ContactDAO dao = new ContactDAO(jds,jooqCtx,mappingMode);
          
            Contact contact1 = new Contact();
            contact1.setName("One Contact");
            contact1.setPhone("1111111");
            contact1.setEmail("contactOne@world.com");
            dao.insert(contact1);
            
            Contact contact2 = new Contact();            
            contact2.setName("Another Contact");
            contact2.setPhone("2222222");
            contact2.setEmail("contactAnother@world.com");            
            dao.insertImplicitUpdateListener(contact2);  // just to play  
            
            Contact contact3 = new Contact();            
            contact3.setName("And other Contact");
            contact3.setPhone("3333333");
            contact3.setEmail("contactAndOther@world.com");            
            dao.insertExplicitResultSetListener(contact3); // just to play           
                        
            contact3.setPhone("4444444");            
            boolean updated = dao.update(contact3);
            assertTrue(updated);            
            
            contact3.setPhone("3333333");            
            updated = dao.updateImplicitUpdateListener(contact3); // just to play
            assertTrue(updated);          
            
            JEPLTask<Contact[]> task = () ->
            { // Connection got
                JEPLResultSetDAO<Contact> list = dao.selectActiveResult();
                assertFalse(list.isClosed());                
                Contact[] res = ContactDAO.toContactArray(list);
                assertTrue(list.isClosed());
             
                int size2 = dao.selectCount();
                assertTrue(res.length == size2);
 
                return res;
            }; // Connection released
            Contact[] array = dao.getJEPLDAO().getJEPLDataSource().exec(task);                                   
            System.out.println("Result:");
            for(Contact contact : array)
                System.out.println("  Contact: " + contact.getId() + " " + contact.getName() + " " + contact.getPhone());
            
            List<Contact> list = dao.selectNotActiveResult();           
            System.out.println("Result:");            
            list.stream().forEach((contact) -> {            
                System.out.println("  Contact: " + contact.getId() + " " + contact.getName() + " " + contact.getPhone());
            });
            
            int maxResults = 2;
            list = dao.selectNotActiveResult(maxResults);
            assertTrue(list.size() == maxResults);
            
            System.out.println("Result maxResults (" + maxResults + "):");            
            list.stream().forEach((contact) -> {            
                System.out.println("  Contact: " + contact.getId() + " " + contact.getName() + " " + contact.getPhone());
            });                        
            
            list = dao.selectNotActiveResult2(maxResults);
            assertTrue(list.size() == maxResults);              
            System.out.println("Result maxResults (" + maxResults + "):");            
            list.stream().forEach((contact) -> {            
                System.out.println("  Contact: " + contact.getId() + " " + contact.getName() + " " + contact.getPhone());
            });               
                       
            
            int from = 1;
            int to = 2;            
            list = dao.selectRange(from,to);
            assertTrue(list.size() == (to - from));            
            System.out.println("Result from/to " + from + "/" + to + ":");
            list.stream().forEach((contact) -> {            
                System.out.println("  Contact: " + contact.getId() + " " + contact.getName() + " " + contact.getPhone());
            });            
                               
            list = dao.selectRange2(from,to);
            assertTrue(list.size() == (to - from));             
            System.out.println("Result from/to " + from + "/" + to + ":");
            list.stream().forEach((contact) -> {            
                System.out.println("  Contact: " + contact.getId() + " " + contact.getName() + " " + contact.getPhone());
            });             
            
            JEPLDAL dal = jds.createJEPLDAL();
            
            dalActiveSelect(dal,jooqCtx);
            
            dalNotActiveSelect(dal,jooqCtx);
            
            jdbcTxnExample(dao);            
         
            jdbcTxnExample2(dao);             
            
            jdbcTxnExample3(dao); 
            
            jdbcTxnExample4(dao);            
            
            jdbcTxnExample5(dao,true);
            
            jdbcTxnExample5(dao,false);            
        }
        /*
        catch(Exception ex)
        {
            ex.printStackTrace();
            throw ex;
        } */       
        finally
        {
            destroyDataSource(ds);
        }
        
    }
 
    
    private static void configureDataSource(ComboPooledDataSource cpds) throws PropertyVetoException
    {
        // Create before a database named "testjooq"
        String jdbcXADriver = "com.mysql.jdbc.jdbc2.optional.MysqlXADataSource";
        String jdbcURL="jdbc:mysql://127.0.0.1:3306/testjooq?pinGlobalTxToPhysicalConnection=true"; // testjooq
        String jdbcUserName="root";
        String jdbcPassword="root2000";        
        int poolSize = 3;
        int maxStatements = 180;
 
        cpds.setDriverClass(jdbcXADriver);            
        cpds.setJdbcUrl(jdbcURL);
        cpds.setUser(jdbcUserName);
        cpds.setPassword(jdbcPassword);
 
        cpds.setMaxPoolSize(poolSize);
        cpds.setMaxStatements(maxStatements);    
    }
    
    private static void destroyDataSource(ComboPooledDataSource cpds) 
    {
        try
        {
            DataSources.destroy(cpds);
            cpds.close();
        }
        catch (SQLException ex)
        {
            throw new RuntimeException(ex);
        }          
    }    
       
    
    private static void createTables(JEPLNonJTADataSource jds)
    {
        JEPLDAL dal = jds.createJEPLDAL();  
              
        dal.createJEPLDALQuery("DROP TABLE IF EXISTS CONTACT").executeUpdate();
 
        dal.createJEPLDALQuery(
            "CREATE TABLE CONTACT (" +
            " ID INT NOT NULL AUTO_INCREMENT," +
            " EMAIL VARCHAR(255) NOT NULL," +
            " NAME VARCHAR(255) NOT NULL," +
            " PHONE VARCHAR(255) NOT NULL," +
            " PRIMARY KEY (ID)" +
            ")" +
            "ENGINE=InnoDB"
        ).executeUpdate(); 
    }
           
    
    public static void dalActiveSelect(final JEPLDAL dal,DefaultDSLContext jooqCtx)
    {          
        // Supposed 3 rows in contact table
        JEPLTask<Void> task = () -> { // public Void exec() throws Exception
            JEPLResultSet resSet = dal.createJEPLDALQuery(
                    jooqCtx.select(count().as("CO"),avg(field("ID",int.class)).as("AV")).from(table("CONTACT")).getSQL()) // SELECT COUNT(*) AS CO,AVG(ID) AS AV FROM CONTACT  
                    .getJEPLResultSet();
 
            assertFalse(resSet.isClosed());                
 
            ResultSet rs = resSet.getResultSet();
            ResultSetMetaData metadata = rs.getMetaData();
            int ncols = metadata.getColumnCount();
            String[] colNames = new String[ncols];
            for(int i = 0; i < ncols; i++)
                colNames[i] = metadata.getColumnLabel(i + 1); // Starts at 1                     
 
            assertTrue(colNames.length == 2);
            assertTrue(colNames[0].equals("CO"));
            assertTrue(colNames[1].equals("AV"));
 
            assertTrue(rs.getRow() == 0);                 
 
            assertFalse(resSet.isClosed());
 
            resSet.next();
 
            assertTrue(rs.getRow() == 1);
 
            int count = rs.getInt(1);
            assertTrue(count == 3);       
            count = rs.getInt("CO");
            assertTrue(count == 3);
 
            float avg = rs.getFloat(1);
            assertTrue(avg > 0);        
            avg = rs.getFloat("AV");
            assertTrue(avg > 0);                       
 
            assertFalse(resSet.next());                
            assertTrue(resSet.isClosed());                
 
            assertTrue(resSet.count() == 1); 
            return null;
        };
        dal.getJEPLDataSource().exec(task);
    }        
    
    public static void dalNotActiveSelect(final JEPLDAL dal,DefaultDSLContext jooqCtx)
    {          
        // Supposed 3 rows in contact table
        JEPLCachedResultSet resSet = dal.createJEPLDALQuery(
                jooqCtx.select(count().as("CO"),avg(field("ID",int.class)).as("AV")).from(table("CONTACT")).getSQL()) // SELECT COUNT(*) AS CO,AVG(ID) AS AV FROM CONTACT     
                .getJEPLCachedResultSet();
        String[] colNames = resSet.getColumnLabels();
        assertTrue(colNames.length == 2);
        assertTrue(colNames[0].equals("CO"));
        assertTrue(colNames[1].equals("AV"));
        
        assertTrue(resSet.size() == 1);
 
        int count = resSet.getValue(1, 1, int.class); // Row 1, column 1
        assertTrue(count == 3);
        count = resSet.getValue(1, "CO", int.class);
        assertTrue(count == 3);
 
        float avg = resSet.getValue(1, 2, float.class); // Row 1, column 2
        assertTrue(avg > 0);
        avg = resSet.getValue(1, "AV", float.class);
        assertTrue(avg > 0);
    }        
        
    
    private static void jdbcTxnExample(ContactDAO dao)
    {
        checkNotEmpty(dao);      
        
        List<Contact> contacts = dao.selectNotActiveResult();        
        
        JEPLTask<Void> task = () -> { // Void exec() throws Exception
            contacts.stream().forEach((contact) ->
            {            
                boolean deleted = dao.delete(contact);
                assertTrue(deleted);               
            });
            // No, no, we need a rollback
            throw new Exception("I want a rollback to avoid to delete rows");
        };
        
        try
        {
            JEPLNonJTADataSource jds = (JEPLNonJTADataSource)dao.getJEPLDAO().getJEPLDataSource();
            boolean autoCommit = false;
            jds.exec(task,autoCommit); 
            
            throw new RuntimeException("Unexpected, ever executed rollback in this example");
        }
        catch(Exception ex)
        {
            // Rollback, data is still saved
            checkNotEmpty(dao);
        }
    }            
    
    private static void jdbcTxnExample2(ContactDAO dao)
    {
        checkNotEmpty(dao);      
        
        List<Contact> contacts = dao.selectNotActiveResult();        
        
        JEPLTask<Void> task = () -> { // Void exec() throws Exception
            contacts.stream().forEach((contact) ->
            {            
                boolean deleted = dao.deleteImplicitUpdateListener(contact); // just to play
                assertTrue(deleted);               
            });
            // No, no, we need a rollback
            throw new Exception("I want a rollback to avoid to delete rows");
        };
        
        JEPLConnectionListener conListener = (JEPLConnection con,JEPLTask task2) -> { // void setupJEPLConnection(JEPLConnection con,JEPLTask task) throws Exception                      
                    con.getConnection().setAutoCommit(false); 
                };        
        
        try
        {
            dao.getJEPLDAO().getJEPLDataSource().exec(task,conListener); 
            
            throw new RuntimeException("Unexpected, ever executed rollback in this example");
        }
        catch(Exception ex)
        {
            // Rollback, data is still saved
            checkNotEmpty(dao);
        }
    }            
    
    private static void jdbcTxnExample3(ContactDAO dao)
    {
        checkNotEmpty(dao);      
        
        List<Contact> contacts = dao.selectNotActiveResult();
        
        JEPLTask<Void> task = () -> { // Void exec() throws Exception
            contacts.stream().forEach((contact) ->
            {
                boolean deleted = dao.delete(contact);
                assertTrue(deleted);                
            });
            // No, no, we need a rollback
            throw new Exception("I want a rollback to avoid to delete rows");
        };
        
        JEPLConnectionListener connListener = (JEPLConnection con,JEPLTask task2) -> 
            {
                con.getConnection().setAutoCommit(false); // transaction
                try
                {
                    task2.exec();
                    con.getConnection().commit();
                }
                catch(Exception ex)
                {
                    con.getConnection().rollback();
                    throw new SQLException(ex);
                }
            };
        
        try
        {
            dao.getJEPLDAO().getJEPLDataSource().exec(task,connListener); 
            
            throw new RuntimeException("Unexpected, ever executed rollback in this example");
        }
        catch(Exception ex)
        {
            // Rollback, data is still saved
            checkNotEmpty(dao);
        }
    }
       
    
    private static void jdbcTxnExample4(ContactDAO dao)
    {
        checkNotEmpty(dao);      
        
        List<Contact> contacts = dao.selectNotActiveResult();
        
        JEPLTask<Void> task = () -> { // Void exec() throws Exception
            contacts.stream().forEach((contact) ->
            {
                boolean deleted = dao.delete(contact);
                assertTrue(deleted);            
            });
            // No, no, we need a rollback
            throw new Exception("I want a rollback to avoid to delete rows");
        };
        
        JEPLConnectionListener connListener = (JEPLConnection con,JEPLTask task2) -> 
            {
                JEPLTransaction txn = con.getJEPLTransaction();
                txn.begin(); // Executes setDefaultAutoCommit(false);
 
                try
                {
                    task2.exec();
                    txn.commit();
                }
                catch(Exception ex)
                {
                    txn.rollback();
                    throw ex;
                }
            };        
        
        try
        {
            JEPLNonJTADataSource jds = (JEPLNonJTADataSource)dao.getJEPLDAO().getJEPLDataSource();
            jds.exec(task,connListener); 
            
            throw new RuntimeException("Unexpected, ever executed rollback in this example");
        }
        catch(Exception ex)
        {
            // Rollback, data is still saved
            checkNotEmpty(dao);
        }
    }    
        
    private static void jdbcTxnExample5(ContactDAO dao,final boolean simulateRollback)
    {
        checkNotEmpty(dao);              
        
        JEPLTask<Void> task = new JEPLTask<Void>() {
 
            @Override
            @JEPLTransactionalNonJTA  // Is equals to @JEPLTransactionalNonJTA(autoCommit = false)
            public Void exec() throws SQLException
            { // Connection got
                JEPLResultSetDAO<Contact> list = dao.selectActiveResult();                  
                assertFalse(list.isClosed());
 
                ((List<Contact>)list).stream().forEach((contact) ->  // JEPLResultSetDAO implements the List interface
                {
                    boolean deleted = dao.delete(contact);
                    assertTrue(deleted);
 
                    if (simulateRollback)
                        throw new RuntimeException("Force Rollback");
                });
                
                assertTrue(list.isClosed());
                
                checkEmpty(dao);
                
                return null;
            } // Connection released
        };
        
        try
        {
            dao.getJEPLDAO().getJEPLDataSource().exec(task); 
            
            checkEmpty(dao);
        }
        catch(Exception ex)
        {
            // Rollback when simulateFailed = true
            checkNotEmpty(dao);           
        }
    }    
    
    private static void checkEmpty(ContactDAO dao)
    {            
        assertTrue(dao.selectCount() == 0);            
    }
    
    private static void checkNotEmpty(ContactDAO dao)
    {            
        assertFalse(dao.selectCount() == 0);            
    }    
}
 

Let's Explain

Initialization

To initialize JEPLayer (non-JTA) we just need a DataSource:

    private void jooqExample(short mappingMode) throws Exception
    {
        ComboPooledDataSource ds = new ComboPooledDataSource();        
        try
        {
            configureDataSource(ds);
 
            DefaultDSLContext jooqCtx = new DefaultDSLContext(SQLDialect.MYSQL);            
            
            JEPLNonJTADataSource jds = JEPLBootRoot.get().createJEPLBootNonJTA().createJEPLNonJTADataSource(ds);

to finally obtain a JEPLNonJTADataSource object, this object wraps the provided DataSource. In fact JEPLayer is basically a set of wrappers on top of JDBC, nothing new, the "new" JEPLayer specific part is you ever have optional access to original JDBC objects when you need some specific configuration and behavior, depending of the phase of the persistent lifecycle, avoiding re-inventing JDBC again and again.

As you can see jOOQ initialization for MySQL is trivial because we are going to use just a subset of its capabilities. 

            JEPLConnectionListener conListener = (JEPLConnection con,JEPLTask task2) -> { // void setupJEPLConnection(JEPLConnection con,JEPLTask task) throws Exception                      
                        con.getConnection().setAutoCommit(true); 
                    };            
            jds.addJEPLListener(conListener); 
            // Simple alternative:  jds.setDefaultAutoCommit(true);

The previous code configures by default the JEPLNonJTADataSource root object to disable transactions using aJEPLConnectionListener, this is the verbose version of jds.setDefaultAutoCommit(true), in fact it is also unnecessary because transactions are disabled by default (later we are going to see many options to execute transactions with no need of enable them by default, in a ideal world all persistent actions must be inside transactions but in practice only changing actions should). 

            createTables(jds);
                       
            ContactDAO dao = new ContactDAO(jds,jooqCtx,mappingMode);

The method createTables() shows how to execute SQL raw code  using a JEPLDAL (DAL=Data Access Layer) object. AJEPLDAL object can be a singleton (the same as JEPLDAO objects in fact this interface inherits from JEPLDAL) and is designed to execute persistent actions when you do not need to convert requested data to POJOs.

    private static void createTables(JEPLNonJTADataSource jds)
    {
        JEPLDAL dal = jds.createJEPLDAL();  
              
        dal.createJEPLDALQuery("DROP TABLE IF EXISTS CONTACT").executeUpdate();
 
        dal.createJEPLDALQuery(
            "CREATE TABLE CONTACT (" +
            " ID INT NOT NULL AUTO_INCREMENT," +
            " EMAIL VARCHAR(255) NOT NULL," +
            " NAME VARCHAR(255) NOT NULL," +
            " PHONE VARCHAR(255) NOT NULL," +
            " PRIMARY KEY (ID)" +
            ")" +
            "ENGINE=InnoDB"
        ).executeUpdate(); 
    }

Let's see how ContactDAO is initialized:

    public ContactDAO(JEPLNonJTADataSource jds,DefaultDSLContext jooqCtx,short mappingMode)
    {
        this.dao = jds.createJEPLDAO(Contact.class); 
        this.jooqCtx = jooqCtx;
        
        // This 3 mapping approaches provides the same behaviour in this simple example, they are coded 
        // just to show the different options for mapping         
        switch(mappingMode)
        {
            case 0: // default mapping attribs and columns by name ignoring case
                this.updateListener = jds.createJEPLUpdateDAOListenerDefault(Contact.class);
                this.resultSetListener = jds.createJEPLResultSetDAOListenerDefault(Contact.class); 
                break;
            case 1: // custom mapping 
                this.updateListener = new JEPLUpdateDAOListener<Contact>() {
                        @Override
                        public String getTable(JEPLConnection jcon, Contact obj) throws Exception {
                            return "CONTACT";
                        }
 
                        @Override
                        public Map.Entry<JEPLColumnDesc, Object>[] getColumnDescAndValues(JEPLConnection jcon, Contact obj, JEPLPersistAction action) throws Exception 
                        {
                            Map.Entry<JEPLColumnDesc,Object>[] result = new AbstractMap.SimpleEntry[]
                            {
                                new AbstractMap.SimpleEntry<>(new JEPLColumnDesc("ID").setAutoIncrement(true).setPrimaryKey(true),obj.getId()),
                                new AbstractMap.SimpleEntry<>(new JEPLColumnDesc("NAME"),obj.getName()),                    
                                new AbstractMap.SimpleEntry<>(new JEPLColumnDesc("PHONE"),obj.getPhone()),                    
                                new AbstractMap.SimpleEntry<>(new JEPLColumnDesc("EMAIL"),obj.getEmail())                    
                            };
                            return result;
                        }            
                    };                
                
                this.resultSetListener = new JEPLResultSetDAOListener<Contact>() {
                        @Override
                        public void setupJEPLResultSet(JEPLResultSet jrs,JEPLTask<?> task) throws Exception {
                        }
 
                        @Override
                        public Contact createObject(JEPLResultSet jrs) throws Exception {
                            return new Contact();
                        }
 
                        @Override
                        public void fillObject(Contact obj,JEPLResultSet jrs) throws Exception {
                            ResultSet rs = jrs.getResultSet();
                            obj.setId(rs.getInt("ID"));
                            obj.setName(rs.getString("NAME"));
                            obj.setPhone(rs.getString("PHONE"));
                            obj.setEmail(rs.getString("EMAIL"));
                        }    
                    };
                break;
            case 2:  // default mapping using custom row-mappers              
                JEPLUpdateDAOBeanMapper<Contact> updateMapper = (Contact obj, JEPLConnection jcon, String columnName, Method getter, JEPLPersistAction action) -> { // public Object getColumnFromBean(Contact obj, JEPLConnection jcon, String columnName, Method getter, JEPLPersistAction action) throws Exception
                        if (columnName.equalsIgnoreCase("email"))
                        {
                            return obj.getEmail();
                        }
                        return JEPLUpdateDAOBeanMapper.NO_VALUE;
                    };                                    
                this.updateListener = jds.createJEPLUpdateDAOListenerDefault(Contact.class,updateMapper);
                    
                JEPLResultSetDAOBeanMapper<Contact> resultMapper = (Contact obj, JEPLResultSet jrs, int col, String columnName, Object value, Method setter) -> { // setColumnInBean(...)
                        if (columnName.equalsIgnoreCase("email"))
                        {
                            obj.setEmail((String)value);
                            return true;
                        }
                        return false;
                    };
                this.resultSetListener = jds.createJEPLResultSetDAOListenerDefault(Contact.class,resultMapper);                
                break;
            case 3:
                throw new RuntimeException("Unexpected");
        }
          
        dao.addJEPLListener(updateListener);        
        dao.addJEPLListener(resultSetListener);          
    }    

This constructor creates a JEPLDAO<Contact> object, this object can be a singleton and can be used to manage Contact objects, besides implementing the interface JEPLDAL, JEPLDAO<T> provides methods to manage the persistence of the class T specified, in this case Contact.

The constructor is very verbose to show the options of mapping columns and attributes, the parameter mappingMode decides the approach used for mapping. In this example all approaches are the same, all attributes are mapped to the columns with the same name ignoring case. The first one is enough and the simplest in this case:

            case 0: // default mapping attribs and columns by name ignoring case
                this.updateListener = jds.createJEPLUpdateDAOListenerDefault(Contact.class);
                this.resultSetListener = jds.createJEPLResultSetDAOListenerDefault(Contact.class); 
                break;

The JEPLUpdateDAOListenerDefault when registered will be used to internally generate the SQL code and parameters to execute JEPLDAO<Contact>.insert(Contact obj)/update(Contact obj)/delete(Contact obj) methods. 

The JEPLResultSetDAOListenerDefault when registered will be used to create the POJOs mapped to resulting rows when executing DAO queries. 

If you need more complex bindings and data transformation use other more specific approach. The JEPLResultSetDAOBeanMapper is interesting when most of them match by default but someone need a custom binding or excluding.

Inserting persistent objects

Insertion example: 

            Contact contact1 = new Contact();
            contact1.setName("One Contact");
            contact1.setPhone("9999999");
            contact1.setEmail("contactOne@world.com");
            dao.insert(contact1);

This is the insert() method in DAO:

   public void insert(Contact contact)
    {
        int key = dao.createJEPLDALQuery(
                 jooqCtx.insertInto(table("CONTACT"),field("EMAIL"),field("NAME"),field("PHONE"))
                         .values("email","name", "phone").getSQL()) // INSERT INTO CONTACT (EMAIL, NAME, PHONE) VALUES (?, ?, ?)
                .addParameters(contact.getEmail(),contact.getName(),contact.getPhone())
                .getGeneratedKey(int.class);
        contact.setId(key);
    }     

Because we are not going to return Contact objects, this method uses a DAL query.The values "email", "name", "phone" are non-sense values, they are required by jOOQ and will be replaced by ?, if you need to provide inline values use inline("some.real@email.com") and similar as parameters (this is jOOQ specific). jOOQ generates parameters with format ? , JEPLayer also allows parameters with :name format to avoid "counting accidents", because of jOOQ they are not shown in this tutorial (see the JEPLayer Manual). Because of insertion we finally call the method getGeneratedKey() calling under the hood the similar JDBC method.

There is another example of insertion, the result is the same but it is defined to show how we can optionally modify how the results are processed (in this case only one row and column, the generated id, is expected): 

    public void insertExplicitResultSetListener(Contact contact)
    {
        // Just to show how data conversion can be possible if required
        JEPLResultSetDALListener listener = new JEPLResultSetDALListener()
        {
            @Override
            public void setupJEPLResultSet(JEPLResultSet jrs,JEPLTask<?> task) throws Exception { 
            }
            @Override
            @SuppressWarnings("unchecked")
            public <U> U getValue(int columnIndex, Class<U> returnType, JEPLResultSet jrs) throws Exception
            {
                if (!returnType.equals(int.class)) throw new RuntimeException("UNEXPECTED");
                // Expected columnIndex = 1 (only one row and one column is expected)
                ResultSet rs = jrs.getResultSet();
                int resInt = rs.getInt(columnIndex);
                Object resObj = rs.getObject(columnIndex);
                Integer resIntObj = (Integer)jrs.getJEPLStatement().getJEPLDAL().cast(resObj, returnType);
                if (resInt != resIntObj) throw new RuntimeException("UNEXPECTED");
                return (U)resIntObj; 
            }
        };
 
        int key = dao.createJEPLDALQuery(
                    jooqCtx.insertInto(table("CONTACT"),field("EMAIL"),field("NAME"),field("PHONE"))
                            .values("email","name", "phone").getSQL()) // INSERT INTO CONTACT (EMAIL, NAME, PHONE) VALUES (?, ?, ?)                
                    .addParameters(contact.getEmail(),contact.getName(),contact.getPhone())
                    .addJEPLListener(listener)
                    .getGeneratedKey(int.class);
         contact.setId(key);
    }    

This specified JEPLResultSetDALListener could be registered on the constructor of ContactDAO (do not confuse with JEPLResultSetDAOListener, is DAL not DAO). 

Finally there is a simple DAO sentence for insertion without explicit SQL code and parameters, the JEPLDAO<T>.insert(T obj)method uses under the hood the JEPLUpdateDAOListener<T> registered, this listener provides the column mapping and values to insert. 

    public void insertImplicitUpdateListener(Contact contact)
    {
        int key = dao.insert(contact).getGeneratedKey(int.class);
        contact.setId(key);
    }

Updating persistent objects

Now the code to update:

    public void update(Contact contact)
    {
        int updated = dao.createJEPLDALQuery(
                jooqCtx.update(table("CONTACT"))
                        .set(field("EMAIL"), "email")
                        .set(field("NAME"),  "name")
                        .set(field("PHONE"), "phone")
                        .where(field("ID").equal(0)).getSQL()) // "UPDATE CONTACT SET EMAIL = ?, NAME = ?, PHONE = ? WHERE ID = ?")
                .addParameters(contact.getEmail(),contact.getName(),contact.getPhone(),contact.getId())
                .executeUpdate();
        if (updated != 1)
            throw new RuntimeException("Unexpected");
    }

Nothing to explain, very similar to insertion, again jOOQ in action. In this case we call executeUpdate() returning the number of "rows" involved (one in this case). 

Similar to insertion we can use the simple DAO sentence for updating without explicit SQL code and parameters, theJEPLDAO<T>.update(T obj) method uses under the hood the JEPLUpdateDAOListener<T> registered. 

    public boolean updateImplicitUpdateListener(Contact contact)
    {
        int updated = dao.update(contact)
                .executeUpdate();
        return updated > 0;
    }  

Deleting persistent objects

The code to delete one row: 

    public boolean deleteById(int id)
    {
        int deleted = dao.createJEPLDALQuery( jooqCtx.delete(table("CONTACT")).where(field("ID").equal(0)).getSQL() ) // "DELETE FROM CONTACT WHERE ID = ?" 
                        .addParameters(id)             
                        .executeUpdate();     
        return deleted > 0;
    }   

Again the 0 literal value is not used and a ? is generated instead. The call executeUpdate()returns the number of "rows" involved (one in this case). 

The same to insertion and update we can use the simple DAO sentence for deleting without explicit SQL code and parameters, theJEPLDAO<T>.delete(T obj) method uses under the hood the JEPLUpdateDAOListener<T> registered. 

    public boolean deleteImplicitUpdateListener(Contact contact)
    {
        int deleted = dao.delete( contact )       
                        .executeUpdate();     
        return deleted > 0;
    }

Query and processing alive/active results

The DAO method getJEPLResultSetDAO():

    public JEPLResultSetDAO<Contact> selectActiveResult()
    {
        return dao.createJEPLDAOQuery( jooqCtx.selectFrom(table("CONTACT")).getSQL() ) // "SELECT * FROM CONTACT"
                    .getJEPLResultSetDAO();    
    }   

only can be called inside a Connection got from DataSource, we cannot directly call the same as we executeexecuteUpdate() because JEPLResultSetDAO holds an active JDBC ResultSet.So we need to wrap the call and data extraction using a JEPLTask. 

            JEPLTask<Contact[]> task = () ->
            { // Connection got
                JEPLResultSetDAO<Contact> list = dao.selectActiveResult();
                if (list.isClosed()) throw new RuntimeException("Unexpected");                
                Contact[] res = ContactDAO.toContactArray(list);
                if (!list.isClosed()) throw new RuntimeException("Unexpected");
             
                int size2 = dao.selectCount();
                if (res.length != size2)
                    throw new RuntimeException("Unexpected");
 
                return res;
            }; // Connection released
            Contact[] array = dao.getJEPLDAO().getJEPLDataSource().exec(task);

One shot query

If you know the number of resulting rows or you just want to load an affordable subset of rows, there is no need of using a JEPLResultSetDAO. Instead we call getResultSet() which returns a conventional List<T> (remember you can optionally register a JEPLResultSetDALListener and a mapping listener calling addJEPLListener() before getResultSet()). 

    public List<Contact> selectNotActiveResult(int maxResults)
    {
        // "ORDER BY" is not really needed, is just to play with jooq
        List<Contact> list = dao.createJEPLDAOQuery( jooqCtx.selectFrom(table("CONTACT")).orderBy(field("ID"),field("NAME")).getSQL() ) // "SELECT * FROM CONTACT ORDER BY ID,NAME"
                .setMaxResults(maxResults)
                .getResultList();       
        return list;
    }     

The method setMaxResults() is used in this example to limit the number of results. 

One shot query, alternative

The method setMaxResults()is enough but to show how much configuration is possible, we are going to show an alternative to do the same registering a JEPLPreparedStatementListener listener to customize the PrepatedStatement used under the hood (we have seen before the same kind of customization of ResultSet). By the way do not worry about threading, aPreparedStatement is bound to a Connection and only one thread can hold a Connection. 

    public List<Contact> selectNotActiveResult2(final int maxResults)
    {
        // Another (verbose) approach using JDBC        
        JEPLPreparedStatementListener<List<Contact>> listener = (JEPLPreparedStatement jstmt,JEPLTask<List<Contact>> task) -> { // void setupJEPLPreparedStatement(...) throws Exception
 
            PreparedStatement stmt = jstmt.getPreparedStatement();
            int old = stmt.getMaxRows();
            stmt.setMaxRows(maxResults);
            try
            {
                List<Contact> res = task.exec();
            }
            finally
            {
                stmt.setMaxRows(old); // Restore
            }            
        };
 
        return dao.createJEPLDAOQuery("SELECT * FROM CONTACT")
                .addJEPLListener(listener)
                .getResultList();
    } 

Counting rows

Because we expect just one row and a single field, there is a specific method getOneRowFromSingleField(). 

    public int selectCount()  // selectCount method name is used instead "count" to avoid name clashing with jooq "org.jooq.impl.DSL.count()"
    {
        return dao.createJEPLDALQuery( jooqCtx.select(count()).from(table("CONTACT")).getSQL() )
                            .getOneRowFromSingleField(int.class);     
    }

Select a range

Most of your queries need a range of the results based on a search criteria and order. This is why setFirstResult() andsetMaxResults() exist. 

    public List<Contact> selectRange(int from,int to)
    {
        // The simplest form
        return dao.createJEPLDAOQuery(jooqCtx.selectFrom(table("CONTACT")).orderBy(field("ID")).getSQL() ) // "SELECT * FROM CONTACT ORDER BY ID"
                .setFirstResult(from)
                .setMaxResults(to - from)
                .getResultList();
    }    

Select a range, alternative

If you are an obsessed for control, you can alternatively control how the range is got through JDBC level methods. 

    public List<Contact> selectRange2(final int from,final int to)
    {
        // Another (verbose) approach using JDBC
        JEPLResultSetDAOListener<Contact> listener = new JEPLResultSetDAOListener<Contact>()
        {
            @Override
            public  void setupJEPLResultSet(JEPLResultSet jrs,JEPLTask<?> task) throws Exception
            {
                resultSetListener.setupJEPLResultSet(jrs, task);
                
                ResultSet rs = jrs.getResultSet();
                rs.absolute(from); // Is not still closed
 
                // Not needed, just to know the state of ResultSet and some demostrative check:
                @SuppressWarnings("unchecked")
                List<Contact> res = (List<Contact>)task.exec(); 
                if (res.size() > to - from) throw new RuntimeException("Unexpected");
            }
 
            @Override
            public Contact createObject(JEPLResultSet jrs) throws Exception
            {
                return resultSetListener.createObject(jrs);
            }
 
            @Override
            public void fillObject(Contact obj,JEPLResultSet jrs) throws Exception
            {
                resultSetListener.fillObject(obj, jrs);
                
                ResultSet rs = jrs.getResultSet();
                int row = rs.getRow(); 
                if (row + 1 == to)
                    jrs.stop();
            }
        };
 
        return dao.createJEPLDAOQuery(jooqCtx.selectFrom(table("CONTACT")).orderBy(field("ID")).getSQL() ) // "SELECT * FROM CONTACT ORDER BY ID"
                .addJEPLListener(listener)
                .getResultList();
    }    

Data Access Layer (DAL) level queries

Frequently you want to execute queries returning diverse data beyond model objects, for instance we need the number of columns and the average value of a column of a table in a single query. JEPLayer provides two approaches, using an alivejava.sql.ResultSet wrapped by the interface JEPLResultSet and by using a cached result set with the interfaceJEPLCachedResultSet. 

DAL Active Queries

When an alive java.sql.ResultSet wrapped by the interface JEPLResultSet is returned, it is similar toJEPLResultSetDAO, in this case diverse data is returned instead data model objects. Because result iteration requires an alive connection a task is required.

    public static void dalActiveSelect(final JEPLDAL dal,DefaultDSLContext jooqCtx)
    {          
        // Supposed 3 rows in contact table
        JEPLTask<Void> task = () -> { // public Void exec() throws Exception
            JEPLResultSet resSet = dal.createJEPLDALQuery(
                    jooqCtx.select(count().as("CO"),avg(field("ID",int.class)).as("AV")).from(table("CONTACT")).getSQL()) // SELECT COUNT(*) AS CO,AVG(ID) AS AV FROM CONTACT  
                    .getJEPLResultSet();
 
            assertFalse(resSet.isClosed());                
 
            ResultSet rs = resSet.getResultSet();
            ResultSetMetaData metadata = rs.getMetaData();
            int ncols = metadata.getColumnCount();
            String[] colNames = new String[ncols];
            for(int i = 0; i < ncols; i++)
                colNames[i] = metadata.getColumnLabel(i + 1); // Starts at 1                     
 
            assertTrue(colNames.length == 2);
            assertTrue(colNames[0].equals("CO"));
            assertTrue(colNames[1].equals("AV"));
 
            assertTrue(rs.getRow() == 0);                 
 
            assertFalse(resSet.isClosed());
 
            resSet.next();
 
            assertTrue(rs.getRow() == 1);
 
            int count = rs.getInt(1);
            assertTrue(count == 3);       
            count = rs.getInt("CO");
            assertTrue(count == 3);
 
            float avg = rs.getFloat(1);
            assertTrue(avg > 0);        
            avg = rs.getFloat("AV");
            assertTrue(avg > 0);                       
 
            assertFalse(resSet.next());                
            assertTrue(resSet.isClosed());                
 
            assertTrue(resSet.count() == 1); 
            return null;
        };
        dal.getJEPLDataSource().exec(task);
    }        

DAL Not Active Queries

When returned a JEPLCachedResultSet, it is similar to the POJO List returned by JEPLDAOQuery<T>.getResultList(), again diverse data is returned instead data model objects. No task is required because a connection is not required for iterating the result, everything is cached into JEPLCachedResultSet. 
    public static void dalNotActiveSelect(final JEPLDAL dal,DefaultDSLContext jooqCtx)
    {          
        // Supposed 3 rows in contact table
        JEPLCachedResultSet resSet = dal.createJEPLDALQuery(
                jooqCtx.select(count().as("CO"),avg(field("ID",int.class)).as("AV")).from(table("CONTACT")).getSQL()) // SELECT COUNT(*) AS CO,AVG(ID) AS AV FROM CONTACT     
                .getJEPLCachedResultSet();
        String[] colNames = resSet.getColumnLabels();
        assertTrue(colNames.length == 2);
        assertTrue(colNames[0].equals("CO"));
        assertTrue(colNames[1].equals("AV"));
        
        assertTrue(resSet.size() == 1);
 
        int count = resSet.getValue(1, 1, int.class); // Row 1, column 1
        assertTrue(count == 3);
        count = resSet.getValue(1, "CO", int.class);
        assertTrue(count == 3);
 
        float avg = resSet.getValue(1, 2, float.class); // Row 1, column 2
        assertTrue(avg > 0);
        avg = resSet.getValue(1, "AV", float.class);
        assertTrue(avg > 0);
    }

Transactions, transactions, transactions...

One of the most important features of RDBMS is transactions, more specifically, the implied rollback capability of transactions. The everything or nothing persistence is one of the most important features when evaluating the confidence of a IT system. 

JEPLayer is conscious of how important is transactions and how much tedious and error prone is the typical manual demarcation of other DB APIs (I am not talking about jOOQ, which follows a similar approach to JEPLayer). This is why transaction demarcation (begin/commit) is defined by a single user method (nested transactions are possible) and commit is ever implicit when the method normally ends or rollback if some exception is thrown. Later we are going to see how to optionally we can manually demarcate transactions. 

JEPLayer provides support to JDBC and JTA transactions, this tutorial only shows JDBC transactions (controlled by the auto-commit JDBC mode). When auto-commit is set to true (the default) every SQL sentence is executed into a built-in transaction according to the guaranties typical of an ACID system. Our interest is when auto-commit is set to false and we need to change several rows by several sentences inside a transaction. 

The following examples are ever rollback examples because rollbacking our changes is how we can evaluate if our sentences have been executed into a transactions. 

The simplest transaction

In JEPLayer code executed into a transaction is ever wrapped by the only method (exec()) of a JEPLTask. By setting theautoCommit parameter to false we ensure JEPLayer executes the task into a transaction and execute a commit just in the end of the task (or rollback when an exception is thrown). 

    private static void jdbcTxnExample(ContactDAO dao)
    {
        checkNotEmpty(dao);      
        
        List<Contact> contacts = dao.selectNotActiveResult();        
        
        JEPLTask<Void> task = () -> { // Void exec() throws Exception
            contacts.stream().forEach((contact) ->
            {            
                boolean deleted = dao.delete(contact);
                assertTrue(deleted);               
            });
            // No, no, we need a rollback
            throw new Exception("I want a rollback to avoid to delete rows");
        };
        
        try
        {
            JEPLNonJTADataSource jds = (JEPLNonJTADataSource)dao.getJEPLDAO().getJEPLDataSource();
            boolean autoCommit = false;
            jds.exec(task,autoCommit); 
            
            throw new RuntimeException("Unexpected, ever executed rollback in this example");
        }
        catch(Exception ex)
        {
            // Rollback, data is still saved
            checkNotEmpty(dao);
        }
    } 

Transaction by configuring the connection

By using a JEPLConnectionListener we can set auto-commit false in the Connectiongoing to be used in the transaction. JEPLayer executes the task into a transaction. 

    private static void jdbcTxnExample2(ContactDAO dao)
    {
        checkNotEmpty(dao);      
        
        List<Contact> contacts = dao.selectNotActiveResult();        
        
        JEPLTask<Void> task = () -> { // Void exec() throws Exception
            contacts.stream().forEach((contact) ->
            {            
                boolean deleted = dao.deleteImplicitUpdateListener(contact); // just to play
                assertTrue(deleted);               
            });
            // No, no, we need a rollback
            throw new Exception("I want a rollback to avoid to delete rows");
        };
        
        JEPLConnectionListener conListener = (JEPLConnection con,JEPLTask task2) -> { // void setupJEPLConnection(JEPLConnection con,JEPLTask task) throws Exception                      
                    con.getConnection().setAutoCommit(false); 
                };        
        
        try
        {
            dao.getJEPLDAO().getJEPLDataSource().exec(task,conListener); 
            
            throw new RuntimeException("Unexpected, ever executed rollback in this example");
        }
        catch(Exception ex)
        {
            // Rollback, data is still saved
            checkNotEmpty(dao);
        }
    } 

Transaction by configuring the connection (2)

With a JEPLConnectionListener we can make much more complex things and manual transaction control. 

    private static void jdbcTxnExample3(ContactDAO dao)
    {
        checkNotEmpty(dao);      
        
        List<Contact> contacts = dao.selectNotActiveResult();
        
        JEPLTask<Void> task = () -> { // Void exec() throws Exception
            contacts.stream().forEach((contact) ->
            {
                boolean deleted = dao.delete(contact);
                assertTrue(deleted);                
            });
            // No, no, we need a rollback
            throw new Exception("I want a rollback to avoid to delete rows");
        };
        
        JEPLConnectionListener connListener = (JEPLConnection con,JEPLTask task2) -> 
            {
                con.getConnection().setAutoCommit(false); // transaction
                try
                {
                    task2.exec();
                    con.getConnection().commit();
                }
                catch(Exception ex)
                {
                    con.getConnection().rollback();
                    throw new SQLException(ex);
                }
            };
        
        try
        {
            dao.getJEPLDAO().getJEPLDataSource().exec(task,connListener); 
            
            throw new RuntimeException("Unexpected, ever executed rollback in this example");
        }
        catch(Exception ex)
        {
            // Rollback, data is still saved
            checkNotEmpty(dao);
        }
    }

Previous example can be coded in a generic way hiding the JDBC Connection object usingJEPLTransaction instead. 

    private static void jdbcTxnExample4(ContactDAO dao)
    {
        checkNotEmpty(dao);      
        
        List<Contact> contacts = dao.selectNotActiveResult();
        
        JEPLTask<Void> task = () -> { // Void exec() throws Exception
            contacts.stream().forEach((contact) ->
            {
                boolean deleted = dao.delete(contact);
                assertTrue(deleted);            
            });
            // No, no, we need a rollback
            throw new Exception("I want a rollback to avoid to delete rows");
        };
        
        JEPLConnectionListener connListener = (JEPLConnection con,JEPLTask task2) -> 
            {
                JEPLTransaction txn = con.getJEPLTransaction();
                txn.begin(); // Executes setDefaultAutoCommit(false);
 
                try
                {
                    task2.exec();
                    txn.commit();
                }
                catch(Exception ex)
                {
                    txn.rollback();
                    throw ex;
                }
            };        
        
        try
        {
            JEPLNonJTADataSource jds = (JEPLNonJTADataSource)dao.getJEPLDAO().getJEPLDataSource();
            jds.exec(task,connListener); 
            
            throw new RuntimeException("Unexpected, ever executed rollback in this example");
        }
        catch(Exception ex)
        {
            // Rollback, data is still saved
            checkNotEmpty(dao);
        }
    }

Transaction by annotation

Finally we can specify a task is going to be executed into a transaction specifying @JEPLTransactionalNonJTA.

    private static void jdbcTxnExample5(ContactDAO dao,final boolean simulateRollback)
    {
        checkNotEmpty(dao);              
        
        JEPLTask<Void> task = new JEPLTask<Void>() {
 
            @Override
            @JEPLTransactionalNonJTA  // Is equals to @JEPLTransactionalNonJTA(autoCommit = false)
            public Void exec() throws SQLException
            { // Connection got
                JEPLResultSetDAO<Contact> list = dao.selectActiveResult();                  
                assertFalse(list.isClosed());
 
                ((List<Contact>)list).stream().forEach((contact) ->  // JEPLResultSetDAO implements the List interface
                {
                    boolean deleted = dao.delete(contact);
                    assertTrue(deleted);
 
                    if (simulateRollback)
                        throw new RuntimeException("Force Rollback");
                });
                
                assertTrue(list.isClosed());
                
                checkEmpty(dao);
                
                return null;
            } // Connection released
        };
        
        try
        {
            dao.getJEPLDAO().getJEPLDataSource().exec(task); 
            
            checkEmpty(dao);
        }
        catch(Exception ex)
        {
            // Rollback when simulateFailed = true
            checkNotEmpty(dao);           
        }
    } 

Epilogue

We have seen how to mix JEPLayer, Java 8 and jOOQ to code true POJO based persistent applications with less verbosity thanks to Java 8 and code less error-prone by using jOOQ. 

ENJOY!!

Using Containers? Read our Kubernetes Comparison eBook to learn the positives and negatives of Kubernetes, Mesos, Docker Swarm and EC2 Container Services.

Topics:

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}