22 März 2013

SQL Result Mapper 0.1.0 in Maven Central Repository

I just released version 0.1.0 of my and it is now available from Maven central repository.

More details on GitHub: https://github.com/simasch/sqlresultmapper

21 März 2013

Generate TOs from Query

My SQL result mapper has a new feature:


ResultSet rs = stmt.executeQuery("SELECT NAME FROM EMPLOYEE");
ClassGenerator.generateFromResultSet("", null, "EmployeeNameTO", false, rs);


The ClassGenerator will generate a TO from any SQL SELECT query.

Check it out:
https://github.com/simasch/sqlresultmapper

08 März 2013

SQL Result Mapper on GitHub

I published the SQL Result Mapper on GitHub:

https://github.com/simasch/sqlresultmapper

JpaSqlResultMapper instead of Constructor Expression

The previously mentioned JpaSqlResultMapper can also used as a replacement of the JPA Constructor Expression.

Example:
Query q = em.createQuery("SELECT c.id, c.type, c.euroExchangeRate FROM Closing c");
List<ClosingRecord> list = JpaSqlResultMapper.list(q, ClosingRecord.class);
The advantage of this approach is, that you don't have to worry when you are moving ClosingRecord to another package because the full qualified class name is not mentioned in the JPQL query.
So it's more refactoring friendly.

JDBC: Using POJOs in SQL Queries Result Mapping Simplyfied

... and because it worked so well with JPA I created a result mapper for JDBC:

public class JdbcSqlResultMapper {

  public static <T> List<T> list(ResultSet rs, Class<T> clazz) 
    throws SQLException {
    List<T> result = new ArrayList<T>();
    Constructor<?> ctor = 
      (Constructor<?>) clazz.getDeclaredConstructors()[0];
    while (rs.next()) {
      Object[] objs = 
        new Object[ctor.getParameterTypes().length];
      for (int i = 0; i < ctor.getParameterTypes().length; i++) {
        objs[i] = rs.getObject(i + 1);
      }
      createAndAddBean(ctor, objs, result);
    }
    return result;
}

  private static <T> void createAndAddBean(
    Constructor<?> ctor, Object[] args, List<T> result) {
    try {
      T obj = (T) ctor.newInstance(args);
      result.add(obj);
    } catch (Exception e) {
      throw new RuntimeException(e);
    }
  }
}


JPA: Using POJOs in SQL Queries Result Mapping Simplyfied

Because there is no Constructor Expression for native SQL queries in JPA I wrote a simple SqlResultMapper. The result mapper is inspired by EclipseLink and Hibernate:

  • http://onpersistence.blogspot.ch/2010/07/eclipselink-jpa-native-constructor.html
  • http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html#d0e13904
In contrast to these two vendor specific implementations my JpaSqlResultMapper works with every JPA implementation.

There are two important remarks:
  1. Constructor must have the same number of arguments as the result of the SQL query
  2. The result types must match
Your comment is highly appreciated!

public class JpaSqlResultMapper {

  public static <T> List<T> list(Query q, Class<T> clazz) 
    throws IllegalArgumentException {

    Constructor<?> ctor = 
     (Constructor<?>) clazz.getDeclaredConstructors()[0];
    List<T> result = new ArrayList<T>();

    List<Object[]> list = q.getResultList();
    for (Object obj : list) {
        if (ctor.getParameterTypes().length == 1) {
          obj = new Object[] { obj };
        }
        createAndAddBean(ctor, (Object[]) obj, result);
      }
    }
    return result;
  }

  private static <T> void createAndAddBean(
    Constructor<?> ctor, Object[] args, List<T> result) {
    try {
      T obj = (T) ctor.newInstance(args);
      result.add(obj);
    } catch (Exception e) {
      throw new RuntimeException(e);
    }
  }
}