Java tutorial
/** * 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) { } } } }