Java tutorial
/* * Este programa es software libre; usted puede redistribuirlo y/o modificarlo bajo los trminos * de la licencia "GNU General Public License" publicada por la Fundacin "Free Software Foundation". * Este programa se distribuye con la esperanza de que pueda ser til, pero SIN NINGUNA GARANTIA; * vea la licencia "GNU General Public License" para obtener cursor informacion. */ package ips1ap101.lib.core.db.util; import java.sql.Date; import java.sql.SQLException; import java.sql.Time; import java.sql.Timestamp; import java.text.MessageFormat; import ips1ap101.lib.base.entity.enumeration.TipoAgregacionEnumeration; import ips1ap101.lib.base.util.StrUtils; import ips1ap101.lib.base.util.TemporalAddend; import ips1ap101.lib.core.app.Bitacora; import ips1ap101.lib.core.enumeration.EnumTipoResultadoSQL; import ips1ap101.lib.core.util.Utils; import org.apache.commons.lang.StringUtils; /** * @author Jorge Campins */ public class InterpreteSqlPostgreSQL extends InterpreteSqlAbstracto { // <editor-fold defaultstate="collapsed" desc="constantes protegidas"> private static final String LIKE = " ILIKE "; private static final String NOT_LIKE = " NOT ILIKE "; @Override protected String getLike() { return LIKE; } @Override protected String getNotLike() { return NOT_LIKE; } // </editor-fold> private static final String KEYWORD_LIMIT = "LIMIT"; private static final String TEMPORAL_ADDEND_PATTERN = "{0} {1} INTERVAL ''{2} {3}''"; /* para funciones que retornan tipos basicos */ private static final String COMANDO_EXECUTE_1 = "SELECT"; /* para funciones que retornan tipos compuestos (records, rows, sets, ...) */ private static final String COMANDO_EXECUTE_2 = "SELECT * FROM"; private void stamp() { Bitacora.stamp(this); } InterpreteSqlPostgreSQL() { stamp(); } @Override public String getStringDelimitado(Object obj) { String string = getString(obj); if (string == null) { return null; } else if (obj instanceof String) { String escape = StrUtils.getStringJava(string); return string.equals(escape) ? "'" + string + "'" : "E'" + escape + "'"; } else if (obj instanceof Date) { return "date" + "'" + string + "'"; } else if (obj instanceof Time) { return "time" + "'" + string + "'"; } else if (obj instanceof Timestamp || obj instanceof java.util.Date) { return "timestamp" + "'" + string + "'"; } else if (obj instanceof TemporalAddend) { return getStringDelimitado((TemporalAddend) obj); } else { return StrUtils.getStringSqlDelimitado(obj); } } private String getStringDelimitado(TemporalAddend addend) { String base, word; int quantity = addend.getQuantity(); char sign = quantity < 0 ? '-' : '+'; char unit = addend.getUnit(); switch (unit) { case 'A': case 'Y': base = "current_date"; word = "years"; break; case 'M': base = "current_date"; word = "months"; break; case 'D': base = "current_date"; word = "days"; break; case 'h': // base = "current_time"; // base = "localtime"; // base = "current_timestamp"; base = "localtimestamp"; word = "hours"; break; case 'm': // base = "current_time"; // base = "localtime"; // base = "current_timestamp"; base = "localtimestamp"; word = "minutes"; break; case 's': // base = "current_time"; // base = "localtime"; // base = "current_timestamp"; base = "localtimestamp"; word = "seconds"; break; default: return null; } return quantity == 0 ? base : MessageFormat.format(TEMPORAL_ADDEND_PATTERN, base, sign, Math.abs(quantity), word); } @Override public String getComandoSelect(String comando, int limite) { String select = super.getComandoSelect(comando, limite); if (select != null) { int i = Utils.indexOfWord(select, KEYWORD_LIMIT); if (i > 0) { select = StringUtils.trimToNull(select.substring(0, i)); } } if (select != null && limite > 0) { select += " " + KEYWORD_LIMIT + " " + limite; } return select; } @Override public String getComandoExecute(String comando, int argumentos) { return this.getComandoExecute(comando, argumentos, EnumTipoResultadoSQL.VOID); } @Override public String getComandoExecute(String comando, int argumentos, EnumTipoResultadoSQL tipoResultado) { boolean retornaResultadoCompuesto = EnumTipoResultadoSQL.COMPOUND.equals(tipoResultado); String execute = StringUtils.stripToNull(comando); String command = retornaResultadoCompuesto ? COMANDO_EXECUTE_2 : COMANDO_EXECUTE_1; if (execute != null) { String[] token = StringUtils.split(execute); String parametros = "()"; if (argumentos > 0) { parametros = ""; for (int i = 0; i < argumentos; i++, parametros += ",?") { } parametros = "(" + parametros.substring(1) + ")"; } if (!token[0].equalsIgnoreCase(COMANDO_EXECUTE_1)) { execute = command + " " + execute; } if (!execute.endsWith(parametros) && !execute.endsWith(";")) { execute += parametros; } } return execute; } private static final String[] command_ignored_en = { "current transaction is aborted", "commands ignored until end of transaction block" }; private static final String[] command_ignored_es = { "transaccin abortada", "las rdenes sern ignoradas hasta el fin de bloque de transaccin" }; private static final String[][] command_ignored = { command_ignored_en, command_ignored_es }; @Override public boolean isCommandIgnoredException(SQLException sqle) { String string = sqle == null ? null : sqle.getMessage(); return string != null && contains(string, command_ignored); } private boolean contains(String string, String[][] strings) { for (int i = 0; i < strings.length; i++) { if (contains(string, strings[i])) { return true; } } return false; } private boolean contains(String string, String[] strings) { for (int i = 0; i < strings.length; i++) { if (string.contains(strings[i])) { continue; } return false; } return true; } @Override public boolean isNotNullConstraintViolation(SQLException sqle) { boolean is = false; if (sqle != null) { String token1 = "null value in column"; String token2 = "violates not-null constraint"; is = sqle.getMessage().contains(token1) && sqle.getMessage().contains(token2); } return is; } @Override public String getNotNullConstraintViolationColumn(SQLException sqle) { String column = null; boolean is = this.isNotNullConstraintViolation(sqle); if (is) { String[] token = sqle.getMessage().split("\""); if (token.length > 2) { column = token[1]; } } return column; } @Override public String getNombreFuncionAgregacion(TipoAgregacionEnumeration tipo) { return StringUtils.lowerCase(super.getNombreFuncionAgregacion(tipo)); } }