org.bml.util.sql.DBUtil.java Source code

Java tutorial

Introduction

Here is the source code for org.bml.util.sql.DBUtil.java

Source

package org.bml.util.sql;

/*
 * #%L
 * org.bml
 * %%
 * Copyright (C) 2006 - 2014 Brian M. Lima
 * %%
 * This file is part of ORG.BML.
 * 
 *     ORG.BML is free software: you can redistribute it and/or modify
 *     it under the terms of the GNU General Public License as published by
 *     the Free Software Foundation, either version 3 of the License, or
 *     (at your option) any later version.
 * 
 *     ORG.BML is distributed in the hope that it will be useful,
 *     but WITHOUT ANY WARRANTY; without even the implied warranty of
 *     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *     GNU Lesser General Public License for more details.
 * 
 *     You should have received a copy of the GNU Lesser General Public License
 *     along with ORG.BML.  If not, see <http://www.gnu.org/licenses/>.
 * #L%
 */

import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.beans.PropertyVetoException;
import java.io.IOException;
import java.net.InetAddress;
import java.net.UnknownHostException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.ParseException;
import java.util.Date;
import java.util.Map;
import java.util.Properties;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.bml.util.ConversionUtils;
import org.bml.util.TimeUtils;

/**
 * @author Brian M. Lima
 */
public abstract class DBUtil {

    /**
     */
    private static final Log LOG = LogFactory.getLog(DBUtil.class);

    /**
     * Creates a ComboPooledDataSource from a properly populated Properties object
     *
     * @param theProperties
     * @return a ComboPooledDataSource or null on error
     * @throws IOException
     */
    public static ComboPooledDataSource makeComboPooledDataSource(Properties theProperties) throws IOException {
        ComboPooledDataSource aComboPooledDataSource = null;

        LOG.info("Begining creation of ComboPooledDataSource " + theProperties.toString());
        try {
            aComboPooledDataSource = new ComboPooledDataSource();
            aComboPooledDataSource.setDriverClass(theProperties.getProperty("JDBC_DRIVER"));
            aComboPooledDataSource.setJdbcUrl(theProperties.getProperty("JDBCURL"));
            aComboPooledDataSource.setUser(theProperties.getProperty("DBUSER"));
            aComboPooledDataSource.setPassword(theProperties.getProperty("DBPASS"));
            aComboPooledDataSource.setPreferredTestQuery("SELECT 1 AS dbcp_connection_test;");
            aComboPooledDataSource.setMaxPoolSize(20);
            aComboPooledDataSource.setIdleConnectionTestPeriod(10000);
            aComboPooledDataSource.setAutoCommitOnClose(true);

            aComboPooledDataSource.setDebugUnreturnedConnectionStackTraces(true);
            aComboPooledDataSource.setMaxConnectionAge(30000);
            aComboPooledDataSource.setAcquireIncrement(2);
            aComboPooledDataSource.setAcquireRetryAttempts(4);
            aComboPooledDataSource.setAcquireRetryDelay(500);
            aComboPooledDataSource.setTestConnectionOnCheckin(true);
            aComboPooledDataSource.setTestConnectionOnCheckout(true);
            aComboPooledDataSource.setMaxIdleTime(5000);
            aComboPooledDataSource.setUnreturnedConnectionTimeout(120000);
            /**
             * try { aComboPooledDataSource.setLogWriter(new PrintWriter(System.out));
             * } catch (SQLException ex) { LOG.warn(ex); }
             */
        } catch (PropertyVetoException ex) {
            LOG.warn(ex);
        }
        LOG.info("Finished creation of ComboPooledDataSource " + theProperties.toString());
        return aComboPooledDataSource;
    }

    public static final Float DEFAULT_FLOAT = -1.0f;
    private static ComboPooledDataSource DEFAULT_DATA_SOURCE;

    static {
        //INIT DEFAULT_DATA_SOURCE
        try {
            DEFAULT_DATA_SOURCE = makeComboPooledDataSource(System.getProperties());
        } catch (IOException ex) {
            LOG.warn("IOException caught while initializing the default data source. Check system properties "
                    + System.getProperties().toString(), ex);
        }
    }

    public static ComboPooledDataSource getDefaultDataSource() {
        return DEFAULT_DATA_SOURCE;
    }

    /**
     *
     * @param ps
     * @param keyName
     * @param fieldId
     * @param map
     * @param remove
     * @throws SQLException
     */
    public static void setFloat(PreparedStatement ps, String keyName, int fieldId, Map<String, String> map,
            Boolean remove) throws SQLException {
        String val = map.get(keyName);
        if (remove) {
            map.remove(keyName);
        }
        if (val == null) {
            ps.setString(fieldId, null);
            return;
        }
        if (val.isEmpty()) {
            ps.setFloat(fieldId, DEFAULT_FLOAT);

        } else {
            if (val.equals("null")) {
                ps.setFloat(fieldId, DEFAULT_FLOAT);
            } else {
                ps.setFloat(fieldId, Float.parseFloat(val));
            }
        }
    }

    /**
     *
     * @param ps
     * @param keyName
     * @param fieldId
     * @param map
     * @param remove
     * @throws SQLException
     */
    public static void setString(PreparedStatement ps, String keyName, int fieldId, Map<String, String> map,
            Boolean remove) throws SQLException {
        String val = map.get(keyName);
        if (remove) {
            map.remove(keyName);
        }
        if (val == null) {
            ps.setString(fieldId, null);
            return;
        }
        if (val.isEmpty()) {
            ps.setString(fieldId, null);
        } else {
            if (val.equals("null")) {
                ps.setString(fieldId, null);
            } else {
                ps.setString(fieldId, val);
            }
        }
    }

    /**
     *
     * @param ps
     * @param keyName
     * @param fieldId
     * @param map
     * @param remove
     * @throws SQLException
     */
    public static void setInt(PreparedStatement ps, String keyName, int fieldId, Map<String, String> map,
            Boolean remove) throws SQLException {

        String val = map.get(keyName);
        if (remove) {
            map.remove(keyName);
        }
        if (val == null) {
            ps.setString(fieldId, null);
            return;
        }
        if (val.isEmpty()) {
            ps.setString(fieldId, null);
        } else {
            try {
                ps.setInt(fieldId, Integer.parseInt(val));
            } catch (NumberFormatException nfe) {
                ps.setString(fieldId, null);
            }
        }
    }

    /**
     *
     * @param ps
     * @param keyName
     * @param fieldId
     * @param map
     * @param remove
     * @throws SQLException
     */
    public static void setLong(PreparedStatement ps, String keyName, int fieldId, Map<String, String> map,
            boolean remove) throws SQLException {
        String val = map.get(keyName);
        if (remove) {
            map.remove(keyName);
        }
        if (val == null) {
            ps.setString(fieldId, null);
            return;
        }
        if (val.isEmpty()) {
            ps.setString(fieldId, null);
        } else {
            try {
                ps.setLong(fieldId, Long.parseLong(val));
            } catch (NumberFormatException nfe) {
                ps.setString(fieldId, null);
            }
        }
    }

    /**
     *
     * @param ps
     * @param keyName
     * @param fieldId
     * @param map
     * @param remove
     * @throws SQLException
     */
    public static void setBoolean(PreparedStatement ps, String keyName, int fieldId, Map<String, String> map,
            boolean remove) throws SQLException {
        String val = map.get(keyName);
        if (remove) {
            map.remove(keyName);
        }
        if (val == null) {
            ps.setBoolean(fieldId, false);
            return;
        }
        if (val.isEmpty()) {
            ps.setBoolean(fieldId, false);
        } else {
            ps.setBoolean(fieldId, Boolean.parseBoolean(val));
        }
    }

    /**
     *
     * @param ps
     * @param keyName
     * @param fieldId
     * @param map
     * @param remove
     * @param dateFormat
     * @throws SQLException
     * @throws ParseException
     */
    public static void setTimeStamp(PreparedStatement ps, String keyName, int fieldId, Map<String, String> map,
            boolean remove, DateFormat dateFormat) throws SQLException, ParseException, NumberFormatException {
        String val = map.get(keyName);
        if (remove) {
            map.remove(keyName);
        }
        if (val == null) {
            ps.setTimestamp(fieldId, null);
            return;
        }
        Date parse, currentDate = new Date(new Date().getTime() + 3600000);
        try {
            parse = dateFormat.parse(val);
            if (parse.after(currentDate)) {
                LOG.warn("Date parsed from FIELD=" + keyName + " VALUE=" + val
                        + " Is more than one hour in the future.");
            }
            if (parse.before(TimeUtils.UTC_DATE_2005)) {
                LOG.warn("Date parsed from FIELD=" + keyName + " VALUE=" + val + " Is before .");
            }

        } catch (NumberFormatException nfe) {
            nfe.fillInStackTrace();
            throw nfe;
        }
        Timestamp timestamp = new Timestamp(parse.getTime());
        ps.setTimestamp(fieldId, timestamp);
    }

    /**
     *
     * @param ps
     * @param fieldId
     * @param timeStamp
     * @throws SQLException
     */
    public static void setTimeStamp(PreparedStatement ps, int fieldId, Timestamp timeStamp) throws SQLException {
        if (timeStamp == null) {
            ps.setTimestamp(fieldId, null);
            return;
        }

        ps.setTimestamp(fieldId, timeStamp);
    }

    /**
     *
     * @param ps
     * @param keyName
     * @param fieldId
     * @param map
     * @param remove
     * @throws SQLException
     * @throws ParseException
     */
    public static void setInetAddress(PreparedStatement ps, String keyName, int fieldId, Map<String, String> map,
            boolean remove) throws SQLException, ParseException, UnknownHostException {
        String val = map.get(keyName);
        if (remove) {
            map.remove(keyName);
        }
        if (val == null) {
            ps.setTimestamp(fieldId, null);
            return;
        }
        InetAddress ip;
        try {
            ip = InetAddress.getByName(val);
            ps.setInt(fieldId, ConversionUtils.byteArrayToUnsignedInt(ip.getAddress()));
        } catch (UnknownHostException ex) {
            if (LOG.isDebugEnabled()) {
                LOG.debug("UnknownHostException Found while converting IPV4 adress to unsigned int.", ex);
            } else if (LOG.isWarnEnabled()) {
                LOG.warn("UnknownHostException Found while converting IPV4 adress to unsigned int. ADDRESS=" + val);
            }
            throw ex;
        }
    }
}