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 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()); // } }