ips1ap101.lib.core.db.util.DB.java Source code

Java tutorial

Introduction

Here is the source code for ips1ap101.lib.core.db.util.DB.java

Source

/*
 * 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 mas informacin.
 */
package ips1ap101.lib.core.db.util;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import ips1ap101.lib.core.app.Bitacora;
import ips1ap101.lib.core.constant.EAC;
import ips1ap101.lib.core.enumeration.EnumTipoDatoSQL;
import ips1ap101.lib.core.enumeration.EnumTipoResultadoSQL;
import ips1ap101.lib.core.util.EA;
import org.apache.commons.lang.StringUtils;

/**
 * @author Jorge Campins
 */
public class DB {

    public static final String SELECT = "select";

    public static final String INSERT = "insert";

    public static final String UPDATE = "update";

    public static final String DELETE = "delete";

    public static final String DOLLAR = "$";

    public static final String JOB = "job";

    public static final String ESQUEMA_PUNTO = "";

    public static final String DOMINIO_RECURSO = ESQUEMA_PUNTO + "recurso";

    public static final String ARCHIVO_ADJUNTO_INSERT = ESQUEMA_PUNTO + "archivo_adjunto" + DOLLAR + INSERT;

    public static final String PAGINA_USUARIO_INSERT = ESQUEMA_PUNTO + "pagina_usuario" + DOLLAR + INSERT;

    public static final String RASTRO_FUNCION_INSERT = ESQUEMA_PUNTO + "rastro_funcion" + DOLLAR + INSERT;

    public static final String RASTRO_FUNCION_PAR_INSERT = ESQUEMA_PUNTO + "rastro_funcion_par" + DOLLAR + INSERT;

    public static final String RASTRO_INFORME_INSERT = ESQUEMA_PUNTO + "rastro_informe" + DOLLAR + INSERT;

    public static final String RASTRO_INFORME_UPDATE = ESQUEMA_PUNTO + "rastro_informe" + DOLLAR + UPDATE;

    public static final String RASTRO_PROCESO_INSERT = ESQUEMA_PUNTO + "rastro_proceso" + DOLLAR + INSERT;

    public static final String RASTRO_PROCESO_UPDATE = ESQUEMA_PUNTO + "rastro_proceso" + DOLLAR + UPDATE;

    public static final String RASTRO_PROCESO_CHECK_UPDATE = ESQUEMA_PUNTO + "rastro_proceso" + DOLLAR
            + "check_update";

    public static final String TAREA_USUARIO_INSERT = ESQUEMA_PUNTO + "tarea_usuario" + DOLLAR + INSERT;

    public static final String TAREA_USUARIO_UPDATE = ESQUEMA_PUNTO + "tarea_usuario" + DOLLAR + UPDATE;

    //  static final String BEFORE_APPEND = "before_append";
    static final String BEFORE_APPEND = "ba";

    //  static final String BEFORE_EDIT = "before_edit";
    static final String BEFORE_EDIT = "be";

    //  static final String BEFORE_REMOVE = "before_remove";
    static final String BEFORE_REMOVE = "br";

    //  static final String AFTER_APPEND = "after_append";
    static final String AFTER_APPEND = "aa";

    //  static final String AFTER_EDIT = "after_edit";
    static final String AFTER_EDIT = "ae";

    //  static final String AFTER_REMOVE = "after_remove";
    static final String AFTER_REMOVE = "ar";

    static final String CHECK_PROCEDURE = ESQUEMA_PUNTO + "check_procedure";

    public static Connection connect() {
        Bitacora.trace(DB.class, "connect");
        Connection connection = null;
        if (EA.isLoaded()) {
            //          String dataSourceName = EA.getString(EAC.JDBC_DATASOURCE);
            //          connection = connect(dataSourceName);
            if (connection == null) {
                String driver = EA.getString(EAC.JDBC_DRIVER);
                String url = EA.getString(EAC.JDBC_URL);
                String user = EA.getString(EAC.JDBC_USER);
                String password = EA.getString(EAC.JDBC_PASSWORD);
                connection = connect(driver, url, user, password);
            }
        }
        return connection;
    }

    public static Connection connect(String[] args) {
        return connect(args[0], args[1], args[2], args[3]);
    }

    public static Connection connect(String driver, String url, String user, String password) {
        Bitacora.trace(DB.class, "connect", driver, url, user);
        Connection connection = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException | SQLException ex) {
            Bitacora.logFatal(ex);
        }
        Bitacora.stamp(connection, url);
        return connection;
    }

    public static Connection connect(String dataSourceName) {
        return connect(dataSourceName, true);
    }

    public static Connection connect(String dataSourceName, boolean autoCommit) {
        Bitacora.trace(DB.class, "connect", dataSourceName, autoCommit);
        Connection connection = null;
        try {
            DataSource dataSource = (DataSource) InitialContext.doLookup(dataSourceName);
            connection = dataSource.getConnection();
            if (setAutoCommit(connection, autoCommit)) {
                Bitacora.stamp(connection, dataSourceName);
                return connection;
            }
        } catch (NamingException | SQLException ex) {
            Bitacora.logFatal(ex);
        }
        close(connection);
        return null;
    }

    public static boolean setAutoCommit(Connection connection, boolean autoCommit) {
        if (connection != null) {
            try {
                if (!connection.isClosed()) {
                    if (connection.getAutoCommit() == autoCommit) {
                        Bitacora.trace("autocommit is already " + autoCommit);
                    } else {
                        Bitacora.trace("setting autocommit to " + autoCommit);
                        connection.setAutoCommit(autoCommit);
                    }
                    return true;
                }
            } catch (SQLException ex) {
                Bitacora.logFatal(ex);
            }
        }
        return false;
    }

    public static boolean commit(Connection connection) {
        boolean autoCommit = true; /* evita el rollback si falla el getAutoCommit */
        if (connection != null) {
            try {
                if (!connection.isClosed()) {
                    autoCommit = connection.getAutoCommit();
                    if (!autoCommit) {
                        connection.commit();
                    }
                    return true;
                }
            } catch (SQLException ex) {
                Bitacora.logFatal(ex);
                if (!autoCommit) {
                    rollback(connection);
                }
            }
        }
        return false;
    }

    public static boolean rollback(Connection connection) {
        if (connection != null) {
            try {
                if (!connection.isClosed() && !connection.getAutoCommit()) {
                    connection.rollback();
                }
                return true;
            } catch (SQLException ex) {
                Bitacora.logFatal(ex);
            }
        }
        return false;
    }

    public static boolean close(Connection connection) {
        if (connection != null) {
            try {
                if (!connection.isClosed()) {
                    Bitacora.stamp(connection, "*** close ***");
                    connection.close();
                }
                return true;
            } catch (SQLException ex) {
                Bitacora.logFatal(ex);
            }
        }
        return false;
    }

    public static boolean close(CallableStatement callableStatement) {
        if (callableStatement != null) {
            try {
                callableStatement.close();
                return true;
            } catch (SQLException ex) {
                Bitacora.logFatal(ex);
            }
        }
        return false;
    }

    public static boolean close(PreparedStatement preparedStatement) {
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
                return true;
            } catch (SQLException ex) {
                Bitacora.logFatal(ex);
            }
        }
        return false;
    }

    public static boolean close(ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
                return true;
            } catch (SQLException ex) {
                Bitacora.logFatal(ex);
            }
        }
        return false;
    }

    public static CallableStatement prepareCall(Connection connection, String sql) {
        return prepareCall(connection, sql);
    }

    public static CallableStatement prepareCall(Connection connection, String sql, Object[] args) {
        return prepareCall(connection, sql, null);
    }

    public static CallableStatement prepareCall(Connection connection, String sql, Object[] args,
            EnumTipoResultadoSQL resultType, EnumTipoDatoSQL dataType) {
        CallableStatement callableStatement;
        if (connection != null && sql != null) {
            try {
                callableStatement = connection.prepareCall(sql);
                int n = args == null ? 0 : args.length;
                if (n > 0) {
                    for (int i = 0; i < n; i++) {
                        if (args[i] == null) {
                            //                          callableStatement.setNull(i + 1, java.sql.Types.OTHER);
                            callableStatement.setNull(i + 1, java.sql.Types.NULL);
                        } else if (args[i] instanceof EnumTipoDatoSQL) {
                            EnumTipoDatoSQL tipoDatoSQL = (EnumTipoDatoSQL) args[i];
                            callableStatement.setNull(i + 1, tipoDatoSQL.intValue());
                        } else {
                            callableStatement.setObject(i + 1, args[i]);
                        }
                    }
                }
                if (EnumTipoResultadoSQL.SIMPLE.equals(resultType) && dataType != null) {
                    callableStatement.registerOutParameter(n + 1, dataType.intValue());
                }
                return callableStatement;
            } catch (SQLException ex) {
                Bitacora.logFatal(ex);
            }
        }
        return null;
    }

    public static PreparedStatement prepareStatement(Connection connection, String sql) {
        return prepareStatement(connection, sql);
    }

    public static PreparedStatement prepareStatement(Connection connection, String sql, Object[] args) {
        PreparedStatement preparedStatement;
        if (connection != null && sql != null) {
            try {
                preparedStatement = connection.prepareStatement(sql);
                int n = args == null ? 0 : args.length;
                if (n > 0) {
                    for (int i = 0; i < n; i++) {
                        if (args[i] == null) {
                            //                          callableStatement.setNull(i + 1, java.sql.Types.OTHER);
                            preparedStatement.setNull(i + 1, java.sql.Types.NULL);
                        } else if (args[i] instanceof EnumTipoDatoSQL) {
                            EnumTipoDatoSQL tipoDatoSQL = (EnumTipoDatoSQL) args[i];
                            preparedStatement.setNull(i + 1, tipoDatoSQL.intValue());
                        } else {
                            preparedStatement.setObject(i + 1, args[i]);
                        }
                    }
                }
                return preparedStatement;
            } catch (SQLException ex) {
                Bitacora.logFatal(ex);
            }
        }
        return null;
    }

    public static String getBeforeAppendingProcedure(String tablaDestino, String tablaMaestro) {
        return StringUtils.isBlank(tablaDestino) || StringUtils.isBlank(tablaMaestro) ? null
                : ESQUEMA_PUNTO + tablaMaestro + DOLLAR + BEFORE_APPEND + DOLLAR + tablaDestino;
    }

    public static String getBeforeEditingProcedure(String tablaDestino) {
        return StringUtils.isBlank(tablaDestino) ? null : ESQUEMA_PUNTO + tablaDestino + DOLLAR + BEFORE_EDIT;
    }

    public static String getBeforeRemovingProcedure(String tablaDestino) {
        return StringUtils.isBlank(tablaDestino) ? null : ESQUEMA_PUNTO + tablaDestino + DOLLAR + BEFORE_REMOVE;
    }

    public static String getAfterAppendingProcedure(String tablaDestino, String tablaMaestro) {
        return StringUtils.isBlank(tablaDestino) || StringUtils.isBlank(tablaMaestro) ? null
                : ESQUEMA_PUNTO + tablaMaestro + DOLLAR + AFTER_APPEND + DOLLAR + tablaDestino;
    }

    public static String getAfterEditingProcedure(String tablaDestino, String tablaMaestro) {
        return StringUtils.isBlank(tablaDestino) || StringUtils.isBlank(tablaMaestro) ? null
                : ESQUEMA_PUNTO + tablaMaestro + DOLLAR + AFTER_EDIT + DOLLAR + tablaDestino;
    }

    public static String getAfterRemovingProcedure(String tablaDestino, String tablaMaestro) {
        return StringUtils.isBlank(tablaDestino) || StringUtils.isBlank(tablaMaestro) ? null
                : ESQUEMA_PUNTO + tablaMaestro + DOLLAR + AFTER_REMOVE + DOLLAR + tablaDestino;
    }

    //  private static Calendar calendar = Calendar.getInstance();
    //
    //  private static java.sql.Date getJavaSqlDate(Object arg) {
    //      Bitacora.trace("*** getJavaSqlDate ***");
    //      calendar.setTime((java.util.Date)arg);
    //      return new java.sql.Date(calendar.getTimeInMillis());
    //  }
    //
    //  private static java.util.Date getJavaUtilDate(Object arg) {
    //      Bitacora.trace("*** getJavaUtilDate ***");
    //      calendar.setTime((java.util.Date)arg);
    //      return new java.util.Date(calendar.getTimeInMillis());
    //  }
}