quinta-feira, 17 de maio de 2012

JPA (EclipseLink) and Complex Parameters Stored Procedures

As JAVA has a limited support to Stored Procedures (SP) and there still are people that think of it like one of the "Wonders of the Software's World", some frameworks and a hard work can help solving some complex problems that clients love to come up with.

In this sample I m working with ORACLE as my database server, so the scripts were made for it.

The SP that I m dealing here are far away from usual, they got TYPE definitions in the packages and cursors all mixed together.

The EclipseLink project (http://www.eclipse.org/eclipselink) has a great SP support but some SP calls could be a real nightmare.

Glossary:
TIPO means TYPE
ENTRADA means INPUT
USUARIO means USER
SISTEMA means SYSTEM
RETORNO means OUTPUT
MENSAGEM means MESSAGE
Procedure sample:

 PACKAGE  RBR_SP_SAMPLE AS  
   
  TYPE TIPO_ENTRADA IS RECORD (  
   NM_USUARIO VARCHAR2(30),  
   ID_SISTEMA NUMBER(15)  
  );  
   
  TYPE TIPO_RETORNO IS RECORD (  
   TIPO_MENSAGEM NUMBER(15),  
   DESC_MENSAGEM VARCHAR2(30)  
  );  

  PROCEDURE SP_LISTA_USUARIO (  
   PR_NM_USUARIO IN  RBR_SP_SAMPLE.TIPO_ENTRADA,  
   PR_VC_USUARIO OUT SYS_REFCURSOR,  
   PR_TIPO_RETORNO OUT RBR_SP_SAMPLE.TIPO_RETORNO  
  );  
   
 END RBR_SP_SAMPLE;  


Now we have our SP sample, now let's define the TYPES as OBJECTS:

 CREATE OR REPLACE TYPE O_TIPO_ENTRADA AS OBJECT (  
   NM_USUARIO VARCHAR2(30),  
   ID_SISTEMA  NUMBER(15)  
  );  
   
  CREATE OR REPLACE TYPE O_TIPO_RETORNO AS OBJECT (  
   DESC_MENSAGEM VARCHAR2(30),  
   TIPO_MENSAGEM NUMBER(15)  
  );  


Those objects will be very handy in the mapping process.

Ok then code...

Let's start defining an interface:

 package br.com.brainsoftware.rbr.storedprocedure;  
   
 import java.util.Collection;  
   
 public interface StoredProcedure<ENT, RET, TYPE> {  
   
      /**  
       * Call the stored procedure and prepare the results  
       *   
       * @throws StoredProcedureException  
       */  
      public void call(ENT entry) throws StoredProcedureException;  
   
      /**  
       * After call the <code>call()</code> method this one should return  
       * <code>true</code>  
       *   
       * @return  
       */  
      boolean isReady();  
   
      /**  
       * Only call this method after <code>call()</code> and  
       * <code>isReady()</code>  
       *   
       * @return  
       */  
      public RET getReturn();  
   
      /**  
       * Only call this method after <code>call()</code> and  
       * <code>isReady()</code>  
       *   
       * @return  
       */  
      public Collection<TYPE> getResults();  
 }  
   

Now an abstract class that implements the defined interface, controls the data source connection and defines the type and object mapping:


 package br.com.brainsoftware.rbr.storedprocedure;  
   
 import org.eclipse.persistence.logging.SessionLog;  
 import org.eclipse.persistence.sessions.DatabaseSession;  
 import org.eclipse.persistence.sessions.Project;  
 import org.eclipse.persistence.sessions.Session;  
   
 import br.com.brainsoftware.rbr..pojo.RWEntrada;  
 import br.com.brainsoftware.rbr..pojo.RWRetorno;  
 import br.com.brainsoftware.rbr..util.ConfigUtil;  
   
 public abstract class AbstractStoredProcedure<ENT, RET, TYPE> implements  
           StoredProcedure<ENT, RET, TYPE> {  
      protected Session session;  
      protected boolean ready = false;  
   
      public AbstractStoredProcedure() {  
           // Configuring connection properties  
           Project project = new Project(ConfigUtil.getLogin());  
   
           // Mapping input parameter type - O_TIPO_ENTRADA -> TipoEntrada  
           project.addDescriptor(TipoEntrada.getMapping());  
   
           // Mapping output parameter type - O_TIPO_RETORNO -> TipoRetorno  
           project.addDescriptor(TipoRetorno.getMapping());  
   
           // Connecting to database  
           session = project.createDatabaseSession();  
           session.setLogLevel(SessionLog.FINE);  
           ((DatabaseSession) session).login();  
      }  
 }  



Mapping classes:

 package br.com.brainsoftware.rbr.pojo;  
   
 import java.io.Serializable;  
   
 import org.eclipse.persistence.descriptors.ClassDescriptor;  
 import org.eclipse.persistence.mappings.DirectToFieldMapping;  
 import org.eclipse.persistence.mappings.structures.ObjectRelationalDataTypeDescriptor;  
 import org.eclipse.persistence.platform.database.jdbc.JDBCTypes;  
 import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLrecord;  
   
 public class TipoEntrada implements Serializable {  
   
      public static final String PARAMETER_NAME = "PR_NM_USUARIO";  
      private static final long serialVersionUID = 2165067461541295168L;  
   
      // Mapping  
      private static ObjectRelationalDataTypeDescriptor recordDescriptor;  
      private static PLSQLrecord record;  
      static {  
           recordDescriptor = new ObjectRelationalDataTypeDescriptor();  
           recordDescriptor.descriptorIsAggregate();  
           recordDescriptor.setJavaClass(TipoEntrada.class);  
           recordDescriptor.setAlias("Entrada");  
           recordDescriptor.setStructureName("O_TIPO_ENTRADA");  
           DirectToFieldMapping nmUsuarioMapping = new DirectToFieldMapping();  
           nmUsuarioMapping.setAttributeName("nmUsuario");  
           nmUsuarioMapping.setFieldName("NM_USUARIO");  
           recordDescriptor.addMapping(nmUsuarioMapping);  
           DirectToFieldMapping idSistemaMapping = new DirectToFieldMapping();  
           idSistemaMapping.setAttributeName("idSistema");  
           idSistemaMapping.setFieldName("ID_SISTEMA");  
           recordDescriptor.addMapping(idSistemaMapping);  
   
           record = new PLSQLrecord();  
           record.setTypeName("RBR_SP_SAMPLE.TIPO_ENTRADA");  
           record.setCompatibleType("O_TIPO_ENTRADA");  
           record.setJavaType(TipoEntrada.class);  
           record.addField("NM_USUARIO", JDBCTypes.VARCHAR_TYPE);  
           record.addField("ID_SISTEMA", JDBCTypes.NUMERIC_TYPE);  
      }  
   
      private String nmUsuario;  
      private String idSistema;  
   
      public TipoEntrada() {  
      }  
   
      public TipoEntrada(String nmUsuario, String idSistema) {  
           super();  
           this.nmUsuario = nmUsuario;  
           this.idSistema = idSistema;  
      }  
   
      public String getNmUsuario() {  
           return nmUsuario;  
      }  
   
      public void setNmUsuario(String nmUsuario) {  
           this.nmUsuario = nmUsuario;  
      }  
   
      public String getIdSistema() {  
           return idSistema;  
      }  
   
      public void setIdSistema(String idSistema) {  
           this.idSistema = idSistema;  
      }  
   
      public String toString() {  
           return String.format("TipoEntrada [nmUsuario=%s, idSistema=%s]", nmUsuario,  
                     idSistema);  
      }  
   
      public static ClassDescriptor getMapping() {  
           return recordDescriptor;  
      }  
   
      public static PLSQLrecord getRecord() {  
           return record;  
      }  
 }  
   

 package br.com.brainsoftware.rbr.pojo;  
   
 import java.io.Serializable;  
   
 import org.eclipse.persistence.descriptors.ClassDescriptor;  
 import org.eclipse.persistence.mappings.DirectToFieldMapping;  
 import org.eclipse.persistence.mappings.structures.ObjectRelationalDataTypeDescriptor;  
 import org.eclipse.persistence.platform.database.jdbc.JDBCTypes;  
 import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLrecord;  
   
 public class TipoRetorno implements Serializable {  
   
      public static final String PARAMETER_NAME = "PR_TIPO_RETORNO";  
      private static final long serialVersionUID = -8192064531359394512L;  
   
      // Mapping  
      private static ObjectRelationalDataTypeDescriptor recordDescriptor;  
      private static PLSQLrecord record;  
      static {  
           recordDescriptor = new ObjectRelationalDataTypeDescriptor();  
           recordDescriptor.descriptorIsAggregate();  
           recordDescriptor.setJavaClass(TipoRetorno.class);  
           recordDescriptor.setAlias("Retorno");  
           recordDescriptor.setStructureName("O_TIPO_RETORNO");  
           DirectToFieldMapping descMensagemMapping = new DirectToFieldMapping();  
           descMensagemMapping.setAttributeName("descMensagem");  
           descMensagemMapping.setFieldName("DESC_MENSAGEM");  
           recordDescriptor.addMapping(descMensagemMapping);  
           DirectToFieldMapping tipoMensagemMapping = new DirectToFieldMapping();  
           tipoMensagemMapping.setAttributeName("tipoMensagem");  
           tipoMensagemMapping.setFieldName("TIPO_MENSAGEM");  
           recordDescriptor.addMapping(tipoMensagemMapping);  
   
           record = new PLSQLrecord();  
           record.setTypeName("RBR_SP_SAMPLE.TIPO_RETORNO");  
           record.setCompatibleType("O_TIPO_RETORNO");  
           record.setJavaType(TipoRetorno.class);  
           record.addField("DESC_MENSAGEM", JDBCTypes.VARCHAR_TYPE);  
           record.addField("TIPO_MENSAGEM", JDBCTypes.NUMERIC_TYPE);  
      }  
   
      private String tipoMensagem;  
      private String descMensagem;  
   
      public TipoRetorno() {  
      }  
   
      public TipoRetorno(String tipoMensagem, String descMensagem) {  
           super();  
           this.tipoMensagem = tipoMensage;  
           this.descMensagem = descMensagem;  
      }  
   
      public String getTipoMensagem() {  
           return tipoMensagem;  
      }  
   
      public void setTipoMensagem(String tipoMensagem) {  
           this.tipoMensagem = tipoMensagem;  
      }  
   
      public String getDescMensagem() {  
           return descMensagem;  
      }  
   
      public void setDescMensagem(String descMensagem) {  
           this. descMensagem = descMensagem;  
      }  
   
      public String toString() {  
           return String.format("TipoRetorno [tipoMensagem=%s, descMensagem=%s]",tipoMensagem,  
                     descMensagem);  
      }  
   
      public static ClassDescriptor getMapping() {  
           return recordDescriptor;  
      }  
   
      public static PLSQLrecord getRecord() {  
           return record;  
      }  
 }  

An exception:

 package br.com.brainsoftware.rbr.storedprocedure;  
   
 public class StoredProcedureException extends Exception {  
   
      private static final long serialVersionUID = 6325249553440417092L;  
   
      public StoredProcedureException() {  
      }  
   
      public StoredProcedureException(String arg0) {  
           super(arg0);  
      }  
   
      public StoredProcedureException(Throwable arg0) {  
           super(arg0);  
      }  
   
      public StoredProcedureException(String arg0, Throwable arg1) {  
           super(arg0, arg1);  
      }  
   
 }  

By extend the AbstracStoredProcedure class, the next class will call the SP and prepares the data so they can be used properly and easier:

 package br.com.brainsoftware.rbr.storedprocedure;  
   
 import java.util.ArrayList;  
 import java.util.Collection;  
 import java.util.Collections;  
 import java.util.Enumeration;  
 import java.util.List;  
 import java.util.Vector;  
   
 import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLStoredProcedureCall;  
 import org.eclipse.persistence.queries.DataReadQuery;  
 import org.eclipse.persistence.sessions.DatabaseRecord;  
   
 import br.com.brainsoftware.rbr.databasetype.OracleCursorDatabaseType;  
 import br.com.brainsoftware.rbr.pojo.Usuario;  
 import br.com.brainsoftware.rbr.pojo.TipoEntrada;  
 import br.com.brainsoftware.rbr.pojo.TipoRetorno;  
 import br.com.brainsoftware.rbr.util.ReflectionUtil;  
   
 /**  
  * Chama a procedure usando objetos de entrada e saida e tratando o resultado do  
  * cursor  
  *   
  */  
 public class ListaMaterial extends  
           AbstractStoredProcedure<RWEntrada, RWRetorno, Material> {  
   
      private static final String CURSOR_PARAMETER_NAME = "PR_VC_USUARIO";  
      private static final String PROCEDURE_NAME = "RBR_SP_SAMPLE.SP_LISTA_USUARIO";  
      private TipoRetorno retorno = null;  
      private List<Usuario> usuarios = Collections.emptyList();  
   
      @SuppressWarnings("unchecked")  
      public void call(TipoEntrada entrada) throws StoredProcedureException {  
           // Preparing the SP call  
           PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();  
           call.setProcedureName(PROCEDURE_NAME);  
           call.addNamedArgument(TipoEntrada.PARAMETER_NAME, TipoEntrada.getRecord());  
           call.addNamedOutputArgument(TipoRetorno.PARAMETER_NAME,  
                     TipoRetorno.getRecord());  
           call.addNamedOutputArgument(CURSOR_PARAMETER_NAME,  
                     new OracleCursorDatabaseType());  
   
           // Preparing the query  
           DataReadQuery query = new DataReadQuery();  
           query.setCall(call);  
           query.addArgument(TipoEntrada.PARAMETER_NAME);  
   
           // Adding arguments  
           List<Object> queryArgs = new ArrayList<Object>();  
           queryArgs.add(entrada);  
           query.bindAllParameters();  
   
           // Executing query  
           Object result = session.executeQuery(query, queryArgs);  
   
           // Treating the results  
           Vector<DatabaseRecord> results = new Vector<DatabaseRecord>();  
           Enumeration<DatabaseRecord> records = ((Vector<DatabaseRecord>) result)  
                     .elements();  
           while (records.hasMoreElements()) {  
                DatabaseRecord record = records.nextElement();  
                retorno = (TipoRetorno) record.get(TipoRetorno.PARAMETER_NAME);  
                results = (Vector<DatabaseRecord>) record  
                          .get(CURSOR_PARAMETER_NAME);  
           }  
   
           try {  
                List<DatabaseRecord> outList = new ArrayList<DatabaseRecord>();  
                outList.addAll(results);  
                materiais = ReflectionUtil.mapper(outList, Usuario.class);  
                ready = true;  
           } catch (Exception e) {  
                throw new StoredProcedureException(e);  
           }  
      }  
   
      public boolean isReady() {  
           return ready;  
      }  
   
      public RWRetorno getReturn() {  
           return retorno;  
      }  
   
      public Collection<Usuario> getResults() {  
           return usuarios;  
      }  
 }  
   


A little hacking... the returning data must work like a cursor, therefore the next class implements that behavior:



 package br.com.brainsoftware.rbr.databasetype;  
   
 import static org.eclipse.persistence.internal.helper.DatabaseType.DatabaseTypeHelper.databaseTypeHelper;  
 import static org.eclipse.persistence.internal.helper.Helper.NL;  
   
 import java.util.List;  
 import java.util.ListIterator;  
   
 import oracle.jdbc.OracleTypes;  
   
 import org.eclipse.persistence.internal.helper.DatabaseField;  
 import org.eclipse.persistence.internal.helper.SimpleDatabaseType;  
 import org.eclipse.persistence.internal.sessions.AbstractRecord;  
 import org.eclipse.persistence.platform.database.DatabasePlatform;  
 import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLStoredProcedureCall;  
 import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLargument;  
 import org.eclipse.persistence.queries.StoredProcedureCall;  
 import org.eclipse.persistence.sessions.DatabaseRecord;  
   
 @SuppressWarnings("rawtypes")  
 public class OracleCursorDatabaseType implements SimpleDatabaseType {  
   
      public boolean isComplexDatabaseType() {  
           return false;  
      }  
   
      public boolean isJDBCType() {  
           return false;  
      }  
   
      public int getSqlCode() {  
           return OracleTypes.CURSOR;  
      }  
   
      public int getConversionCode() {  
           return getSqlCode();  
      }  
   
      public String getTypeName() {  
           return "SYS_REFCURSOR";  
      }  
   
      public int computeInIndex(PLSQLargument inArg, int newIndex,  
                ListIterator<PLSQLargument> i) {  
           inArg.outIndex = newIndex;  
           return ++newIndex;  
      }  
   
      public int computeOutIndex(PLSQLargument outArg, int newIndex,  
                ListIterator<PLSQLargument> iterator) {  
           outArg.outIndex = newIndex;  
           return newIndex;  
      }  
   
      public void buildInDeclare(StringBuilder sb, PLSQLargument inArg) {  
           // TODO Auto-generated method stub  
           System.out.println("buildInDeclare");  
      }  
   
      public void buildOutDeclare(StringBuilder sb, PLSQLargument outArg) {  
           sb.append(" ");  
           sb.append(databaseTypeHelper.buildTarget(outArg));  
           sb.append(" ");  
           sb.append(getTypeName());  
           sb.append(";");  
           sb.append(NL);  
      }  
   
      public void buildBeginBlock(StringBuilder sb, PLSQLargument arg,  
                PLSQLStoredProcedureCall call) {  
           // TODO Auto-generated method stub  
           System.out.println("buildBeginBlock");  
   
      }  
   
      public void buildOutAssignment(StringBuilder sb, PLSQLargument outArg,  
                PLSQLStoredProcedureCall call) {  
           String target = databaseTypeHelper.buildTarget(outArg);  
           sb.append(" :");  
           sb.append(outArg.outIndex);  
           sb.append(" := ");  
           sb.append(target);  
           sb.append(";");  
           sb.append(NL);  
      }  
   
      public void translate(PLSQLargument arg, AbstractRecord translationRow,  
                AbstractRecord copyOfTranslationRow,  
                List<DatabaseField> copyOfTranslationFields,  
                List<DatabaseField> translationRowFields,  
                List translationRowValues, StoredProcedureCall call) {  
           // TODO Auto-generated method stub  
           System.out.println("translate");  
      }  
   
      public void buildOutputRow(PLSQLargument outArg, AbstractRecord outputRow,  
                DatabaseRecord newOutputRow, List<DatabaseField> outputRowFields,  
                List outputRowValues) {  
           databaseTypeHelper.buildOutputRow(outArg, outputRow, newOutputRow,  
                     outputRowFields, outputRowValues);  
      }  
   
      public void logParameter(StringBuilder sb, Integer direction,  
                PLSQLargument arg, AbstractRecord translationRow,  
                DatabasePlatform platform) {  
           // TODO Auto-generated method stub  
           System.out.println("logParameter");  
      }  
 }  


Some util classes:

 package br.com.brainsoftware.rbr.util;  
   
 import org.eclipse.persistence.platform.database.oracle.Oracle11Platform;  
 import org.eclipse.persistence.sessions.DatabaseLogin;  
   
 public class ConfigUtil {  
   
      public static DatabaseLogin getLogin() {  
           String USERNAME = "user_name";  
           String PASSWORD = "password";  
           String URL = "jdbc:oracle:thin:@192.168.51.10:1521:XE";  
           String DRIVER = "oracle.jdbc.driver.OracleDriver";  
   
           DatabaseLogin login = new DatabaseLogin();  
           login.setUserName(USERNAME);  
           login.setPassword(PASSWORD);  
           login.setConnectionString(URL);  
           login.setDriverClassName(DRIVER);  
           login.setDatasourcePlatform(new Oracle11Platform());  
           ((DatabaseLogin) login).bindAllParameters();  
           return login;  
      }  
   
 }  
   

 package br.com.brainsoftware.rbr.util;  
   
 import java.lang.reflect.Constructor;  
 import java.lang.reflect.Field;  
 import java.lang.reflect.InvocationTargetException;  
 import java.lang.reflect.Method;  
 import java.math.BigDecimal;  
 import java.util.ArrayList;  
 import java.util.List;  
   
 import org.eclipse.persistence.sessions.DatabaseRecord;  
   
 public class ReflectionUtil {  
   
      public static Object invokeGetterMethod(Field field, Object object)  
                throws SecurityException, IllegalArgumentException,  
                NoSuchMethodException, IllegalAccessException,  
                InvocationTargetException {  
           String methodName = "get"  
                     + String.valueOf(field.getName().charAt(0)).toUpperCase()  
                     + field.getName().substring(1);  
           Class<?>[] parameterTypes = new Class[0];  
           Method method = object.getClass().getMethod(methodName, parameterTypes);  
           return method.invoke(object);  
      }  
   
      public static Object invokeSetterMethod(Field field, Object object,  
                Object value) throws SecurityException, IllegalArgumentException,  
                NoSuchMethodException, IllegalAccessException,  
                InvocationTargetException {  
           Method method = null;  
           Object result = null;  
           String methodName = "set" + field.getName();  
           for (Method m : object.getClass().getMethods()) {  
                if (methodName.equalsIgnoreCase(m.getName())) {  
                     method = m;  
                }  
           }  
           if (null != method) {  
                result = method.invoke(object, value);  
           }  
           return result;  
      }  
   
      public static <T> List<T> mapper(List<DatabaseRecord> records, Class<T> type)  
                throws SecurityException, IllegalArgumentException,  
                InstantiationException, IllegalAccessException,  
                NoSuchMethodException, InvocationTargetException {  
           List<T> result = new ArrayList<T>();  
           for (DatabaseRecord record : records) {  
                result.add(setValues(record, type));  
           }  
   
           return result;  
      }  
   
      @SuppressWarnings("unchecked")  
      private static <T> T setValues(DatabaseRecord record, Class<T> type)  
                throws InstantiationException, IllegalAccessException,  
                SecurityException, IllegalArgumentException, NoSuchMethodException,  
                InvocationTargetException {  
           Object result = type.newInstance();  
   
           for (Field field : result.getClass().getDeclaredFields()) {  
                Object value = record.get(field.getName().toUpperCase());  
                value = adjustType(field.getType(), value);  
   
                ReflectionUtil.invokeSetterMethod(field, result, value);  
           }  
   
           return (T) result;  
      }  
   
      private static Object adjustType(Class<?> type, Object value)  
                throws IllegalArgumentException, InstantiationException,  
                IllegalAccessException, InvocationTargetException {  
           Object result = value;  
   
           if (value == null) {  
                return result;  
           }  
   
           if (BigDecimal.class.equals(value.getClass())) {  
                BigDecimal new_name = (BigDecimal) value;  
                int helper = new_name.intValue();  
                for (Constructor<?> constructor : type.getDeclaredConstructors()) {  
                     Class<?>[] parameters = constructor.getParameterTypes();  
                     if (parameters.length == 1 && parameters[0].equals(int.class)) {  
                          result = constructor.newInstance(helper);  
                          break;  
                     }  
                }  
           }  
           return result;  
      }  
 }  
   


The POJO class (user data) and its members as well as the cursor statement, you can write by yourself. ;)

Now the main class:

   
      public static void main(String[] args) throws Exception {  
           TipoRetorno retorno = null;  
           List<Usuario> usuarios = Collections.emptyList();  
           ListaUsuario listaUsuario = new ListaUsuario();  
           TipoEntrada entrada = new TipoEntrada();  
           entrada.setNmUsuario("USER1");  
           entrada.setIdSistema("1");  
           listaUsuario.call(entrada);  
           if (listaUsuario.isReady()) {  
                retorno = listaUsuario.getReturn();  
                usuarios = (List<Usuario>) listaUsuario.getResults();  
           }  
           // Displaying the results  
           System.out.format("Retono: Tipo:[%s] Mensagem:[%s]%n",  
                     retorno.getTipoMensagem(), retorno.getDescMensagem());  
           for (Usuario usuario : usuarios) {  
                System.out.format("Usuario [%s]%n", usuario);  
           }  
   
      }  
   


Well, we are done. Wasn't that hard, was it?

I tried to solve the problem using EclipseLink's resources but... no luck, so I took a deep breath and started reading the code, this way I  would understand better how it works, then a implementation of DatabaseType interface and a couple of classes to create a interesting solution.

Nenhum comentário:

Postar um comentário