В этом руководстве показано, как использовать JEPLayer ORM (v1.3) и jOOQ для генерации SQL.
Вступление
JEPLayer — это ненавязчивый подход к сохранению чистых POJO, легкая альтернатива более навязчивым и «волшебным» подходам, таким как реализации JPA. JEPLayer пытается упростить до предела жизненный цикл JDBC, скрывающий JDBC API, но через слушателей мы можем дополнительно получить доступ к нижележащему чистому слою JDBC, максимально избегая повторения API JDBC в JEPLayer. JEPLayer уделяет особое внимание управлению транзакциями JDBC и JTA.
JOOQ — это полный набор инструментов Java RDMS, созданный гражданином Швейцарии Лукасом Эдером , JEPLayer также является ORM, они предоставляют две разные точки зрения для решения объектно-реляционной проблемы в среде Java.
Несмотря на возможности JEPLayer ORM, в JEPLayer отсутствует мощный синтаксис SQL, моделируемый объектами Java (вместо обычного текста), моделирование jOOQ SQL в Java, вероятно, является наиболее полным в мире. JOOQ идеально подходит для JEPLayer для написания надежного, безошибочного, рефакторизированного кода SQL на основе Java.
JEPLayer v1.3 представляет автоматическую неявную генерацию SQL для действий INSERT, UPDATE и DELETE, явный SQL не требуется, в любом случае jOOQ SQL в Java по-прежнему неоценим для запросов SELECT, где сложность может быть громоздкой и подверженной ошибкам.
В этом уроке краткое использование jOOQ, примеры SQL очень просты, управление SQL в jOOQ намного мощнее, чем показано здесь.
Другой новичок в этом блоке — Java 8 (спецификация Java 1.8), особенно интересным для JEPLayer являются потоки и лямбды. JEPLayer разработан с использованием плавного API, настроенного для прослушивателей, большинство из которых основано на одном методе, идеально подходит для упрощения кода с использованием лямбда-выражений, в то же время JEPLayer возвращает «живые» результирующие наборы на основе интерфейсов List (и ListIterator) и, как Вы знаете, что список может быть легко преобразован в поток в Java 8 для некоторого типа обработки. Код этого учебного пособия может быть еще менее подробным при использовании лямбды, но некоторые дополнительные ненужные переменные отображаются для отображения имени подключенного слушателя интерфейса, потому что помните, что это учебник JEPLayer (а не учебник по Java 8).
Первая часть этого урока просто показывает типичный класс DAO, основанный на JEPLayer, для управления классом POJO (контакт). Одна важная особенность JEPLayer — абсолютное уважение к пользовательской модели данных, в модели данных не требуется никаких аннотаций, интерфейса или подобного артефакта, модель данных является чистой и независимой от уровня персистентности (JEPLayer), только некоторые необязательные соглашения о свойствах Java-бина требуется для простого сопоставления класса и атрибута с таблицей и столбцами.
Во второй части этого руководства приведены примеры использования сгруппированных постоянных действий, большинство из которых используют транзакции JDBC (в этом примере не используется JTA API JEPLayer). Большая часть кода повторяется (аналогичный код выполняет то же самое), потому что мотивация — показать крайнюю настройку JDBC, разрешенную JEPLayer, особенно в управлении транзакциями.
Где код
Код этого примера можно найти в:
https://github.com/jmarranz/jeplayer_examples/tree/master/jeplayer_jooq
реквизиты
Этот пример был написан в NetBeans с использованием Maven, Java 8 JDK, MySQL и пула соединений C3PO. JEPLayer не требует конкретного пула соединений, просто чтобы показать конфигурацию «реального мира».
Поскольку нет никакого специального кода SQL, любая другая база данных СУБД может быть использована без изменения бизнес-кода (просто изменение начальной загрузки источника данных).
Теперь код
Методология этого урока проста, давайте сначала покажем код, а позже объясним детали каждого фрагмента кода.
Код этого руководства состоит из двух частей: класса DAO и примеров использования класса Contact POJO, а во второй части говорится о транзакциях.
Ультра комплекс Контакт-класс
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("[email protected]");
dao.insert(contact1);
Contact contact2 = new Contact();
contact2.setName("Another Contact");
contact2.setPhone("2222222");
contact2.setEmail("[email protected]");
dao.insertImplicitUpdateListener(contact2); // just to play
Contact contact3 = new Contact();
contact3.setName("And other Contact");
contact3.setPhone("3333333");
contact3.setEmail("[email protected]");
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("[email protected]");
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(«[email protected]») 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 не активные запросы
Когда возвращается JEPLCachedResultSet, он аналогичен списку POJO, возвращенному JEPLDAOQuery <T> .getResultList (), снова вместо объектов модели данных возвращаются разнообразные данные. Никаких задач не требуется, поскольку для итерации результата соединение не требуется, все кэшируется в 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!!