org.tradex.jdbc.JDBCHelper.java Source code

Java tutorial

Introduction

Here is the source code for org.tradex.jdbc.JDBCHelper.java

Source

/**
 * Helios, OpenSource Monitoring
 * Brought to you by the Helios Development Group
 *
 * Copyright 2007, Helios Development Group and individual contributors
 * as indicated by the @author tags. See the copyright.txt file in the
 * distribution for a full listing of individual contributors.
 *
 * This is free software; you can redistribute it and/or modify it
 * under the terms of the GNU Lesser General Public License as
 * published by the Free Software Foundation; either version 2.1 of
 * the License, or (at your option) any later version.
 *
 * This software 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 this software; if not, write to the Free
 * Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
 * 02110-1301 USA, or see the FSF site: http://www.fsf.org. 
 *
 */
package org.tradex.jdbc;

import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.Vector;
import java.util.concurrent.ConcurrentHashMap;

import javax.sql.DataSource;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterUtils;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

/**
 * <p>Title: JDBCHelper</p>
 * <p>Description: JDBC helper utility class</p> 
 * <p>Company: Helios Development Group LLC</p>
 * @author Whitehead (nwhitehead AT heliosdev DOT org)
 * <p><code>org.tradex.jdbc.JDBCHelper</code></p>
 */

public class JDBCHelper {
    private final DataSource ds;

    /**
     * Kills the test database
     */
    public void killDb() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = ds.getConnection();
            ps = conn.prepareStatement("DROP ALL OBJECTS");
            ps.executeUpdate();
            conn.commit();
        } catch (Exception e) {
            System.err.println("Drop Schema [TESTDB] Failed:" + e);
        } finally {
            try {
                ps.close();
            } catch (Exception e) {
            }
            try {
                conn.close();
            } catch (Exception e) {
            }
        }

    }

    /**
     * Creates a new JDBCHelper
     * @param ds The underlyng data source
     */
    public JDBCHelper(DataSource ds) {
        super();
        this.ds = ds;
    }

    /**
     * Executes the SQL script in the passed file.
     * @param fileName The sql script to run
     */
    public void runSql(String fileName) {
        executeUpdate("RUNSCRIPT FROM '" + fileName + "'");
    }

    /**
     * Executes the passed SQL as an update and returns the result code
     * @param sql The update SQL
     * @return the result code
     */
    public int executeUpdate(CharSequence sql) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = ds.getConnection();
            ps = conn.prepareStatement(sql.toString());
            conn.commit();
            return ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException("Update for [" + sql + "] failed", e);
        } finally {
            try {
                ps.close();
            } catch (Exception e) {
            }
            try {
                conn.close();
            } catch (Exception e) {
            }
        }
    }

    /**
     * Creates a new JDBCHelper and returns it.
     * @param ds The data source to use
     * @param initSql Optional init SQL. Ignored if null
     * @return the crreated JDBCHelper
     */
    public static JDBCHelper getInstance(DataSource ds, String initSql) {
        JDBCHelper helper = new JDBCHelper(ds);
        helper.executeUpdate(initSql);
        return helper;
    }

    /**
     * Issues a query for an int
     * @param sql The SQL
     * @param binds The bind values
     * @return an int value
     */
    public int templateQueryForInt(CharSequence sql, Object... binds) {
        NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(ds);
        return template.queryForInt(sql.toString(), getBinds(sql.toString().trim().toUpperCase(), binds));
    }

    /**
     * Issues a query for an Object
     * @param <T> The expected return type
     * @param sql The SQL
     * @param clazz The expected return type
     * @param binds The bind values
     * @return an Object
     */
    public <T> T templateQueryForObject(CharSequence sql, Class<T> clazz, Object... binds) {
        NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(ds);
        return template.queryForObject(sql.toString(), getBinds(sql.toString().trim().toUpperCase(), binds), clazz);
    }

    /**
     * Issues a named parameter query using numerical binds, starting at 0.
     * @param sql The SQL
     * @param binds The bind values
     * @return an Object array of the results
     */
    public Object[][] templateQuery(CharSequence sql, Object... binds) {
        NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(ds);
        final List<Object[]> results = template.query(sql.toString(),
                getBinds(sql.toString().trim().toUpperCase(), binds), new RowMapper<Object[]>() {
                    int columnCount = -1;

                    @Override
                    public Object[] mapRow(ResultSet rs, int rowNum) throws SQLException {
                        if (columnCount == -1)
                            columnCount = rs.getMetaData().getColumnCount();
                        Object[] row = new Object[columnCount];
                        for (int i = 0; i < columnCount; i++) {
                            row[i] = rs.getObject(i + 1);
                        }
                        return row;
                    }
                });
        Object[][] ret = new Object[results.size()][];
        int cnt = 0;
        for (Object[] arr : results) {
            ret[cnt] = arr;
            cnt++;
        }
        return ret;
    }

    /**
     * Executes the update defined in the passed sql 
     * @param sql The sql
     * @param binds The bind values
     * @return the number of rows updated
     */
    public int execute(CharSequence sql, Object... binds) {
        NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(ds);
        return template.update(sql.toString(), getBinds(sql.toString().trim().toUpperCase(), binds));
    }

    /**
     * Batch executes the update define in the passed sql 
     * @param sql The sql
     * @param bindSets An array of bind value arrays
     * @return an array containing the numbers of rows affected by each update in the batch
     */
    public int[] batchExecute(CharSequence sql, Object[]... bindSets) {
        NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(ds);
        SqlParameterSource[] sps = new SqlParameterSource[bindSets.length];
        for (int i = 0; i < bindSets.length; i++) {
            sps[i] = getBinds(sql.toString().trim().toUpperCase(), bindSets[i]);
        }
        return template.batchUpdate(sql.toString(), sps);
    }

    /** Cache of SQL statement parameter types */
    protected static final Map<String, int[]> TYPE_CACHE = new ConcurrentHashMap<String, int[]>();

    /**
     * Generates a SqlParameterSource for the passed SQL text and supplied binds
     * @param sql The SQL to bind to
     * @param binds The supplied variables to bind
     * @return a SqlParameterSource
     */
    public SqlParameterSource getBinds(String sql, final Object... binds) {
        final MapSqlParameterSource sqlParamSource = new MapSqlParameterSource();
        int[] parameterTypes = TYPE_CACHE.get(sql);
        if (parameterTypes == null) {
            synchronized (TYPE_CACHE) {
                parameterTypes = TYPE_CACHE.get(sql);
                if (parameterTypes == null) {
                    Connection conn = null;
                    PreparedStatement ps = null;
                    try {
                        conn = ds.getConnection();
                        ps = conn.prepareStatement(NamedParameterUtils.parseSqlStatementIntoString(sql).toString());
                        ParameterMetaData pmd = ps.getParameterMetaData();
                        int paramCount = pmd.getParameterCount();
                        if (paramCount > 0 && (binds == null || binds.length != paramCount)) {
                            throw new RuntimeException("Bind Count [" + (binds == null ? 0 : binds.length)
                                    + "] was not equal to parameter count [" + paramCount + "]");
                        }
                        parameterTypes = new int[paramCount];
                        for (int i = 0; i < paramCount; i++) {
                            parameterTypes[i] = pmd.getParameterType(i + 1);
                        }
                    } catch (RuntimeException re) {
                        throw re;
                    } catch (Exception e) {
                        throw new RuntimeException("Failed to get binds for [" + sql + "]", e);
                    } finally {
                        try {
                            ps.close();
                        } catch (Exception e) {
                        }
                        try {
                            conn.close();
                        } catch (Exception e) {
                        }
                    }

                }
                TYPE_CACHE.put(sql, parameterTypes);
            }
        }
        for (int i = 0; i < parameterTypes.length; i++) {
            sqlParamSource.addValue("" + i, binds[i], parameterTypes[i]);
        }
        return sqlParamSource;
    }

    /**
     * Executes a query and returns the single column, single row result as an int
     * @param sql The sql to execute
     * @return The returned int
     */
    public int queryForInt(CharSequence sql) {
        Object[][] result = query(sql);
        if (result.length == 1 && result[0].length == 1) {
            return ((Number) result[0][0]).intValue();
        }
        throw new RuntimeException("Query did not return 1 row and 1 column");
    }

    /**
     * Executes the passed SQL and returns the resulting rows maps of values keyed by column name within a map keyed by rownumber (starting with zero)  
     * @param sql The SQL to execute
     * @return the results
     */
    public Map<Integer, Map<String, Object>> result(CharSequence sql) {
        Map<Integer, Map<String, Object>> results = new TreeMap<Integer, Map<String, Object>>();
        Map<Integer, String> colNumToName;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rset = null;
        try {
            conn = ds.getConnection();
            ps = conn.prepareStatement(sql.toString());
            rset = ps.executeQuery();
            int colCount = rset.getMetaData().getColumnCount();
            colNumToName = new HashMap<Integer, String>(colCount);
            ResultSetMetaData rsmd = rset.getMetaData();
            for (int i = 1; i <= colCount; i++) {
                colNumToName.put(i, rsmd.getColumnLabel(i));
            }
            int rowNum = 0;
            while (rset.next()) {
                Map<String, Object> row = new HashMap<String, Object>(colCount);
                results.put(rowNum, row);
                for (int i = 1; i <= colCount; i++) {
                    row.put(colNumToName.get(i), rset.getObject(i));
                }
                rowNum++;
            }
            return results;
        } catch (Exception e) {
            throw new RuntimeException("Query for [" + sql + "] failed", e);
        } finally {
            try {
                rset.close();
            } catch (Exception e) {
            }
            try {
                ps.close();
            } catch (Exception e) {
            }
            try {
                conn.close();
            } catch (Exception e) {
            }
        }
    }

    /**
     * Executes the passed SQL and returns the results in a 2D object array
     * @param sql The SQL query to executer
     * @return the results of the query
     */
    public Object[][] query(CharSequence sql) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rset = null;

        Vector<Object[]> rows = new Vector<Object[]>();
        try {
            conn = ds.getConnection();
            ps = conn.prepareStatement(sql.toString());
            rset = ps.executeQuery();
            int colCount = rset.getMetaData().getColumnCount();
            while (rset.next()) {
                Object[] row = new Object[colCount];
                for (int i = 1; i <= colCount; i++) {
                    row[i - 1] = rset.getObject(i);
                }
                rows.add(row);
            }
            Object[][] result = new Object[rows.size()][];
            int cnt = 0;
            for (Object[] row : rows) {
                result[cnt] = row;
                cnt++;
            }
            return result;
        } catch (Exception e) {
            throw new RuntimeException("Query for [" + sql + "] failed", e);
        } finally {
            try {
                rset.close();
            } catch (Exception e) {
            }
            try {
                ps.close();
            } catch (Exception e) {
            }
            try {
                conn.close();
            } catch (Exception e) {
            }
        }

    }
}