334 lines
13 KiB
Plaintext
Executable File
334 lines
13 KiB
Plaintext
Executable File
package com.fp.bpm.query;
|
|
|
|
import java.util.Collection;
|
|
import java.util.Iterator;
|
|
import java.util.List;
|
|
import java.util.Map;
|
|
import java.util.Set;
|
|
|
|
import javax.persistence.NoResultException;
|
|
import javax.persistence.NonUniqueResultException;
|
|
import javax.persistence.Query;
|
|
|
|
import com.fp.common.helper.BeanManager;
|
|
import com.fp.dto.AbstractDataTransport;
|
|
import com.fp.dto.hb.HibernateBean;
|
|
import com.fp.dto.query.AdditionalQuery;
|
|
import com.fp.dto.query.QueryBean;
|
|
import com.fp.dto.query.QueryCriteria;
|
|
import com.fp.persistence.commondb.PersistenceHelper;
|
|
import com.fp.persistence.commondb.db.DataHelper;
|
|
import com.fp.persistence.commondb.exception.CommondbException;
|
|
import com.fp.persistence.pgeneral.gene.TgeneCatalogDetail;
|
|
|
|
/**
|
|
* Clase utilitaria utiliza en el motor generico de consultas.
|
|
* @author Jorge Vaca.
|
|
* @version 2.1
|
|
*/
|
|
public class QueryHelper { // NOPMD by jorge on 12/10/10 21:53
|
|
|
|
/**Almacena la senetencia dinamica de consulta.*/
|
|
protected StringBuffer query = new StringBuffer(64); // NOPMD by jorge on 12/10/10 21:53
|
|
/**Map que almacen los parametros o restricciones para cada consulta asociada a un Query.*/
|
|
protected Map<String, Object> mParameters;
|
|
|
|
/**
|
|
* Metodo que se encarga de adicionar a la sentencia las restricciones del query.
|
|
* @param pCriteria Objeto que contiene los criterios a aplicar en la sentencia.
|
|
* @param pType Tipo de dato del campo al cual se aplica la restriccion.
|
|
* @throws CommondbException
|
|
* @throws Exception
|
|
*/
|
|
@SuppressWarnings("rawtypes")
|
|
protected void addRestrictions(QueryCriteria pCriteria, Class pType) throws CommondbException,Exception { // NOPMD by jorge on 12/10/10 21:51
|
|
String field = pCriteria.getProperty();
|
|
String condition = pCriteria.getOperation();
|
|
Object value = pCriteria.getValue();
|
|
try {
|
|
value = BeanManager.convertObject(pCriteria.getValue(), pType);
|
|
} catch (Exception e) {
|
|
}
|
|
|
|
if (this.basic(condition)) {
|
|
String aux = field.substring(field.indexOf(".") + 1,field.length()); // NOPMD by jorge on 12/10/10 21:52
|
|
aux = this.getAux(aux);
|
|
query.append("t."+field+" "+condition+" "+":"+aux);
|
|
mParameters.put(aux, value);
|
|
return;
|
|
}
|
|
if (condition.compareTo("isNull") == 0 || condition.compareTo("isNotNull") == 0 ) {
|
|
query.append("t."+field+" is null ");
|
|
return;
|
|
}
|
|
if ( condition.compareTo("isNotNull") == 0 ) {
|
|
query.append("t."+field+" is not null ");
|
|
return;
|
|
}
|
|
if (condition.compareTo("sql") == 0) {
|
|
query.append(value);
|
|
return;
|
|
}
|
|
if (condition.compareTo("in") == 0 || condition.compareTo("not in") == 0) {
|
|
query.append("t."+field+" "+condition+"("+value+")");
|
|
return;
|
|
}
|
|
if (pCriteria.getOperation().compareTo("between") == 0) {
|
|
Object[] param = ((Collection) pCriteria.getValue()).toArray();
|
|
query.append("t."+field+" "+condition+" "+param[0] +" and "+param[1]);
|
|
return;
|
|
}
|
|
throw new CommondbException("QRY001", "OPERADOR {0} NO DISPONIBLE", pCriteria.getOperation());
|
|
}
|
|
|
|
/**
|
|
* Metodo que adiciona un numero secuencial si el campo de la restriccion se repite en la sentencia.
|
|
* @param pAux Parametro de la sentencia.
|
|
* @return String
|
|
* @throws Exception
|
|
*/
|
|
private String getAux(String pAux) throws Exception {
|
|
if(!mParameters.containsKey(pAux)){
|
|
return pAux;
|
|
}
|
|
Set<String> s = mParameters.keySet();
|
|
int i = 0;
|
|
for (String obj : s) {
|
|
if(!obj.contains("AAA")){
|
|
continue;
|
|
}
|
|
String data = obj.substring(obj.indexOf("AAA"),obj.length());
|
|
data = data.substring(3, data.length());
|
|
i = Integer.valueOf(data);
|
|
++i;
|
|
}
|
|
return pAux+"AAA"+i;
|
|
}
|
|
|
|
/**
|
|
* Metodo que se encarga de adicionar a la senetencia los criterios de orden de la consulta.
|
|
* @param pQueryBean Objeto que contiene el bean a consultas, y las restricciones.
|
|
* @throws Exception
|
|
*/
|
|
protected void addOrder(QueryBean pQueryBean) throws Exception {
|
|
boolean first = true;
|
|
for (QueryCriteria order : pQueryBean.getOrder(true)) {
|
|
if(first){
|
|
query.append(" ORDER BY ");
|
|
}else{
|
|
query.append(" ,");
|
|
}
|
|
query.append("t."+order.getProperty()+" asc ");
|
|
first = false;
|
|
}
|
|
for (QueryCriteria order : pQueryBean.getOrder(false)) {
|
|
if(first){
|
|
query.append(" ORDER BY ");
|
|
}else{
|
|
query.append(" ,");
|
|
}
|
|
query.append("t."+order.getProperty()+" desc ");
|
|
first = false;
|
|
}
|
|
}
|
|
|
|
|
|
|
|
/**
|
|
* Metodo que crea un objeto de tipo beanname asociado al QueryBean.
|
|
* @param pQueryBean Objeto que contiene el bean a consultas, y las restricciones.
|
|
* @return Object
|
|
*/
|
|
protected Object createObject(QueryBean pQueryBean) {
|
|
try {
|
|
HibernateBean obj = (HibernateBean) Class.forName(pQueryBean.getBeanName()).newInstance();
|
|
try {
|
|
Object id = BeanManager.getBeanAttributeValue(obj, "pk");
|
|
if (id == null) {
|
|
Object key = Class.forName(pQueryBean.getBeanName() + "Key").newInstance();
|
|
BeanManager.setBeanAttributeValue(obj, "pk", key);
|
|
}
|
|
} catch (Exception e) {
|
|
return obj;
|
|
}
|
|
return obj;
|
|
|
|
} catch (Exception e) {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Metodo que transforma los campos de un registro a un AbstractDataTransport.
|
|
* @param pRecord Datos de un registro obtenidos de la base de datos.
|
|
* @param nameAttributes Atributos adicionales con los cuales se completa la descripcion del catalogo.
|
|
* @return Object.
|
|
* @throws Exception
|
|
*/
|
|
protected AbstractDataTransport fillRecord(QueryBean pQueryBean,Object pRecord, String[] nameAttributes) throws Exception {
|
|
if (pRecord == null) {
|
|
return null;
|
|
}
|
|
AbstractDataTransport obj = (AbstractDataTransport) pRecord;
|
|
this.completeDescriptionCatalog(obj, nameAttributes);
|
|
this.addAditionData(pQueryBean, obj);
|
|
return obj;
|
|
|
|
}
|
|
|
|
/**
|
|
* Metodo que se encarga de completar descripciones de catalogo.
|
|
* @param pObject Objeto al cual se completa las descripciones de catalogo.
|
|
* @param nameAttributes Arreglo con los nombres de catalogo a buscar descripciones ejemlo
|
|
* [baserate,calculationbase]
|
|
* @throws Exception
|
|
*/
|
|
protected void completeDescriptionCatalog(AbstractDataTransport pObject, String[] nameAttributes) throws Exception { // NOPMD by jorge on 12/10/10 21:53
|
|
if (nameAttributes == null) {
|
|
return;
|
|
}
|
|
for (int i = 0; i < nameAttributes.length; i++) {
|
|
if (nameAttributes[i].startsWith("pk_")) {
|
|
nameAttributes[i] = nameAttributes[i].replaceAll("_", ".");
|
|
}
|
|
String descCatalog = "catalog";
|
|
String descCatalogCode = "catalogcode";
|
|
if (nameAttributes[i].compareTo("pk.catalog") == 0 || nameAttributes[i].compareTo("catalog") == 0) {
|
|
descCatalog = "";
|
|
descCatalogCode = "code";
|
|
}
|
|
if (pObject == null) {
|
|
break;
|
|
}
|
|
String catalog = (String) BeanManager.getBeanAttributeValue(pObject, nameAttributes[i] + descCatalog);
|
|
String catalogcode = (String) BeanManager.getBeanAttributeValue(pObject, nameAttributes[i] + descCatalogCode);
|
|
String nameNewAtribute = nameAttributes[i] + "desc";
|
|
if (nameNewAtribute.startsWith("pk.")) {
|
|
nameNewAtribute = nameNewAtribute.substring(3, nameNewAtribute.length());
|
|
}
|
|
if (catalog != null && catalogcode != null) {
|
|
TgeneCatalogDetail detail = DataHelper.getInstance().getTgeneCatalogDetail(catalog, catalogcode);
|
|
pObject.addAddtionalInfo(nameNewAtribute, detail.getDescription());
|
|
} else {
|
|
pObject.addAddtionalInfo(nameNewAtribute, null);
|
|
}
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Metodo que verifica que la condicion de la restriccion sea basica.
|
|
* @param pCondition Codigo de condicion de la restriccion.
|
|
* @return boolean
|
|
* @throws Exception
|
|
*/
|
|
protected boolean basic(String pCondition) throws Exception {
|
|
if (pCondition.compareTo("<") == 0) {
|
|
return true;
|
|
}
|
|
if (pCondition.compareTo(">") == 0) {
|
|
return true;
|
|
}
|
|
if (pCondition.compareTo("<=") == 0) {
|
|
return true;
|
|
}
|
|
if (pCondition.compareTo(">=") == 0) {
|
|
return true;
|
|
}
|
|
if (pCondition.compareTo("=") == 0) {
|
|
return true;
|
|
}
|
|
if (pCondition.compareTo("<>") == 0) {
|
|
return true;
|
|
}
|
|
if (pCondition.compareTo("like") == 0) {
|
|
return true;
|
|
}
|
|
return false;
|
|
}
|
|
|
|
/**
|
|
* Metodo que se encarga de ejecutar sentencias que completan informacion de la consulta asociada al QueryBean.
|
|
* @param pQueryBean Objeto que contiene el bean a consultas, y las restricciones.
|
|
* @param pObject Objeto que contiene la datos resultado del query del bean principal de la consulta.
|
|
* @throws Exception
|
|
*/
|
|
protected void addAditionData(QueryBean pQueryBean,AbstractDataTransport pObject) throws Exception {
|
|
List<AdditionalQuery> lData = pQueryBean.getAdditionalQuery();
|
|
if(lData == null || lData.isEmpty()){
|
|
return;
|
|
}
|
|
for (AdditionalQuery additionalQuery : lData) {
|
|
this.processByAditionalQuery(additionalQuery, pObject);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Metodo que ejecuta sentecias por cada AdditionalQuery, que llega como parametro. <br>
|
|
* Los valores de la restriccion los toma del resultado del bean principal.
|
|
* @param pAdditional Objeto que contiene las consideraciones necesarias para armar una consula adiciona de datos.
|
|
* @param pObject Objeto que contiene la datos resultado del query del bean principal de la consulta.
|
|
* @throws Exception
|
|
*/
|
|
@SuppressWarnings("rawtypes")
|
|
private void processByAditionalQuery(AdditionalQuery pAdditional,AbstractDataTransport pObject) throws Exception { // NOPMD by jorge on 12/10/10 21:52
|
|
if(pObject == null){
|
|
return;
|
|
}
|
|
StringBuffer query = new StringBuffer(); // NOPMD by jorge on 12/10/10 21:52
|
|
query.append("select t."+pAdditional.getField()+" from "+pAdditional.getBean()+" t ");
|
|
List<Map<String, String>> lCriteria = pAdditional.getCriteria();
|
|
mParameters.clear();
|
|
boolean first = true;
|
|
boolean execute = true;
|
|
for (Map<String, String> map : lCriteria) {
|
|
if(first){
|
|
query.append(" WHERE ");
|
|
first = false;
|
|
}else{
|
|
query.append(" AND ");
|
|
}
|
|
String aux = map.get("inner").substring(map.get("inner").indexOf(".") + 1,map.get("inner").length()); // NOPMD by jorge on 12/10/10 21:52
|
|
query.append("t."+map.get("inner")+" = "+":"+aux);
|
|
Object value = map.get("value");
|
|
if(value == null){
|
|
Class type = null;
|
|
try {
|
|
type = BeanManager.getBeanGetterMethod(pObject, map.get("outer")).getReturnType();
|
|
} catch (Exception e1) {
|
|
}
|
|
String aux1 = map.get("outer"); //Si es pk simple llega pk, si es compuesta llega pk.campo
|
|
value = BeanManager.getBeanAttributeValue(pObject, aux1);
|
|
if(value == null){
|
|
execute = false; //Si no encuentra valor en la tabla padre no ejecuta el query.
|
|
break;
|
|
}
|
|
if(type != null){
|
|
value = BeanManager.convertObject(value, type);
|
|
}
|
|
}
|
|
mParameters.put(aux, value);
|
|
}
|
|
if(!execute){
|
|
pObject.addAddtionalInfo(pAdditional.getAlias(), null);
|
|
return;
|
|
}
|
|
Query qry = PersistenceHelper.getEntityManager().createQuery(query.toString());
|
|
Iterator<String> itr = mParameters.keySet().iterator();
|
|
while(itr.hasNext()){
|
|
String key = itr.next();
|
|
qry.setParameter(key, mParameters.get(key));
|
|
}
|
|
try {
|
|
Object result = qry.getSingleResult();
|
|
pObject.addAddtionalInfo(pAdditional.getAlias(), result);
|
|
} catch (NoResultException e) {
|
|
pObject.addAddtionalInfo(pAdditional.getAlias(), null);
|
|
} catch (NonUniqueResultException e) {
|
|
throw new CommondbException("COMMONDB-00015","EXISTE MAS DE UN REGISTRO PARA LA TABAL: {0}", e,pAdditional.getBean());
|
|
}
|
|
|
|
|
|
}
|
|
|
|
}
|