Java tutorial
/* * Copyright 2010 Research Studios Austria Forschungsgesellschaft mBH * * This file is part of easyrec. * * easyrec 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. * * easyrec 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 General Public License for more details. * * You should have received a copy of the GNU General Public License * along with easyrec. If not, see <http://www.gnu.org/licenses/>. */ package org.easyrec.utils.spring.store.dao; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.springframework.jdbc.support.DatabaseMetaDataCallback; import org.springframework.jdbc.support.JdbcUtils; import org.springframework.jdbc.support.MetaDataAccessException; import javax.sql.DataSource; import java.sql.*; import java.util.Date; /** * Provides static utility methods for JDBC code like creating SQL lists etc. * <p/> * <p><b>Company: </b> * SAT, Research Studios Austria</p> * <p/> * <p><b>Copyright: </b> * (c) 2005</p> * <p/> * <p><b>last modified:</b><br/> * $Author: pmarschik $<br/> * $Date: 2011-02-11 11:56:23 +0100 (Fr, 11 Feb 2011) $<br/> * $Revision: 17672 $</p> * * @author Florian Kleedorfer */ public class DaoUtils { /////////////////////////////////////////////////////////////////////////////// // constants public static final String ORDER_ASC = "ASC"; public static final String ORDER_DESC = "DESC"; /////////////////////////////////////////////////////////////////////////////// // members private static final Log logger = LogFactory.getLog(DaoUtils.class); // -------------------------------------------------------------------------- // getter methods // -------------------------------------------------------------------------- /** * get a Long object from a result set column with given name * * @param rs * @param columnName * @return the value, which may be <code>null</code> * @throws SQLException */ public static Long getLong(ResultSet rs, String columnName) throws SQLException { long value = rs.getLong(columnName); if (rs.wasNull()) { return null; } return value; } /** * get an Integer object from a result set column with given name * * @param rs * @param columnName * @return the value, which may be <code>null</code> * @throws SQLException */ public static Integer getInteger(ResultSet rs, String columnName) throws SQLException { int value = rs.getInt(columnName); if (rs.wasNull()) { return null; } return value; } /** * get a Boolean object from a result set column with given name * * @param rs * @param columnName * @return the value, which may be <code>null</code> * @throws SQLException */ public static Boolean getBoolean(ResultSet rs, String columnName) throws SQLException { boolean value = rs.getBoolean(columnName); if (rs.wasNull()) { return null; } return value; } /** * get a java.util.Date object from a result set column with sql type TIMESTAMP and given name * * @param rs * @param columnName * @return the value, which may be <code>null</code> * @throws SQLException */ public static Date getDate(ResultSet rs, String columnName) throws SQLException { Timestamp ts = rs.getTimestamp(columnName); if (ts == null) { return null; } return new Date(ts.getTime()); } /** * get a Double from a result set column with given name * * @param rs * @param columnName * @return the value, which may be <code>null</code> * @throws SQLException */ public static Double getDouble(ResultSet rs, String columnName) throws SQLException { double val = rs.getDouble(columnName); if (rs.wasNull()) { return null; } return val; } // -------------------------------------------------------------------------- // 'getIfPresent' methods // -------------------------------------------------------------------------- /** * get a Long object from a result set column with given name or null if no such * column exists in the result set * * @param rs * @param columnName * @return the value, which may be <code>null</code> * @throws SQLException */ public static Long getLongIfPresent(ResultSet rs, String columnName) throws SQLException { try { rs.findColumn(columnName); } catch (SQLException e) { if (logger.isDebugEnabled()) { logger.debug( "getLong() failed for column '" + columnName + "'. ResultSet doesn't contain that column"); } return null; } return getLong(rs, columnName); } /** * get a Integer object from a result set column with given name or null if no such * column exists in the result set * * @param rs * @param columnName * @return the value, which may be <code>null</code> * @throws SQLException */ public static Integer getIntegerIfPresent(ResultSet rs, String columnName) throws SQLException { try { rs.findColumn(columnName); } catch (SQLException e) { if (logger.isDebugEnabled()) { logger.debug("getInteger() failed for column '" + columnName + "'. ResultSet doesn't contain that column"); } return null; } return getInteger(rs, columnName); } /** * get a Boolean object from a result set column with given name or null if no such * column exists in the result set * * @param rs * @param columnName * @return the value, which may be <code>null</code> * @throws SQLException */ public static Boolean getBooleanIfPresent(ResultSet rs, String columnName) throws SQLException { try { rs.findColumn(columnName); } catch (SQLException e) { if (logger.isDebugEnabled()) { logger.debug("getBoolean() failed for column '" + columnName + "'. ResultSet doesn't contain that column"); } return null; } return getBoolean(rs, columnName); } /** * get a Date object from a result set column with given name or null if no such * column exists in the result set * * @param rs * @param columnName * @return the value, which may be <code>null</code> * @throws SQLException */ public static Date getDateIfPresent(ResultSet rs, String columnName) throws SQLException { try { rs.findColumn(columnName); } catch (SQLException e) { if (logger.isDebugEnabled()) { logger.debug( "getDate() failed for column '" + columnName + "'. ResultSet doesn't contain that column"); } return null; } return getDate(rs, columnName); } /** * get a Double object from a result set column with given name or null if no such * column exists in the result set * * @param rs * @param columnName * @return the value, which may be <code>null</code> * @throws SQLException */ public static Double getDoubleIfPresent(ResultSet rs, String columnName) throws SQLException { try { rs.findColumn(columnName); } catch (SQLException e) { if (logger.isDebugEnabled()) { logger.debug("getDouble() failed for column '" + columnName + "'. ResultSet doesn't contain that column"); } return null; } return getDouble(rs, columnName); } /** * get a String object from a result set column with given name or null if no such * column exists in the result set * * @param rs rs * @param columnName columnName * @return the value, which may be <code>null</code> * @throws SQLException SQLException */ public static String getStringIfPresent(ResultSet rs, String columnName) throws SQLException { int colIndex; try { colIndex = rs.findColumn(columnName); } catch (SQLException e) { if (logger.isDebugEnabled()) { logger.debug("getString() failed for column '" + columnName + "'. ResultSet doesn't contain that column"); } return null; } return rs.getString(colIndex); } /** * get a Blob object from a result set column with given name or null if no such * column exists in the result set * * @param rs rs * @param columnName columnName * @return the value, which may be <code>null</code> * @throws SQLException SQLException */ public static Blob getBlobIfPresent(ResultSet rs, String columnName) throws SQLException { int colIndex; try { colIndex = rs.findColumn(columnName); } catch (SQLException e) { if (logger.isDebugEnabled()) { logger.debug( "getBlob() failed for column '" + columnName + "'. ResultSet doesn't contain that column"); } return null; } return rs.getBlob(colIndex); } // -------------------------------------------------------------------------- // setter methods // -------------------------------------------------------------------------- /** * set a java.lang.Long value in the given preparedStatement object, or set it to null if the * given Long is null * * @param stmt * @param value * @param index * @throws SQLException */ public static void setLong(PreparedStatement stmt, Long value, int index) throws SQLException { if (value == null) { stmt.setNull(index, Types.BIGINT); return; } stmt.setLong(index, value); } /** * set a java.lang.Integer value in the given preparedStatement object, or set it to null if the * given Integer is null * * @param stmt * @param value * @param index * @throws SQLException */ public static void setInteger(PreparedStatement stmt, Integer value, int index) throws SQLException { if (value == null) { stmt.setNull(index, Types.INTEGER); return; } stmt.setInt(index, value); } /** * set a java.lang.Integer value in the given preparedStatement object, or set it to null if the * given Integer is null * * @param stmt * @param value * @param index * @throws SQLException */ public static void setShort(PreparedStatement stmt, Short value, int index) throws SQLException { if (value == null) { stmt.setNull(index, Types.SMALLINT); return; } stmt.setShort(index, value); } /** * set a java.util.Date value in the given PreparedStatement object, or set it to null if the * given Date is null * * @param stmt * @param value * @param index * @throws SQLException */ public static void setDate(PreparedStatement stmt, Date value, int index) throws SQLException { if (value == null) { stmt.setNull(index, Types.TIMESTAMP); return; } Timestamp ts = new Timestamp(value.getTime()); stmt.setTimestamp(index, ts); } public static void setBoolean(PreparedStatement stmt, Boolean value, int index) throws SQLException { if (value == null) { stmt.setNull(index, Types.BOOLEAN); return; } stmt.setBoolean(index, value); } public static void setDouble(PreparedStatement stmt, Double value, int index) throws SQLException { if (value == null) { stmt.setNull(index, Types.DOUBLE); return; } stmt.setDouble(index, value); } // -------------------------------------------------------------------------- // sql list methods // -------------------------------------------------------------------------- /** * Creates a list that can be used for IN queries in sql. * e.g. if the given collection is a Vector containing the * values 1, 2, 4, 5, "(1,2,4,5)" will be returned * * @param values * @param quoteValues if true, values are quoted. * */ public static String createSqlList(Iterable values, boolean quoteValues) { if (values == null || !values.iterator().hasNext()) { return "()"; } StringBuilder buf = new StringBuilder(); buf.append('('); for (final Object value : values) { Object o = value; if (quoteValues) { buf.append("'"); } buf.append(o); if (quoteValues) { buf.append("'"); } buf.append(','); } buf.delete(buf.length() - 1, buf.length()); buf.append(')'); return buf.toString(); } /** * Creates a list that can be used for IN queries in sql. * e.g. if the given array containing the * values 1, 2, 4, 5, "(1,2,4,5)" will be returned * * @param values * @param quoteValues f true, values are quoted. * */ public static String createSqlList(Object[] values, boolean quoteValues) { if (values == null || values.length == 0) { return "()"; } StringBuilder buf = new StringBuilder(); buf.append('('); for (Object o : values) { if (quoteValues) { buf.append("'"); } buf.append(o); if (quoteValues) { buf.append("'"); } buf.append(','); } buf.delete(buf.length() - 1, buf.length()); buf.append(')'); return buf.toString(); } /** * Checks if a given database table is found in the given <code>DataSource</code>. * * @param dataSource * @param tableName * @return true if table exists, false if not */ public static boolean existsTable(DataSource dataSource, final String tableName) { DatabaseMetaDataCallback callback = new DatabaseMetaDataCallback() { public Object processMetaData(DatabaseMetaData dbmd) throws SQLException, MetaDataAccessException { ResultSet rs = dbmd.getTables(null, null, tableName, null); return rs.next(); } }; try { return (Boolean) JdbcUtils.extractDatabaseMetaData(dataSource, callback); } catch (Exception e) { throw new RuntimeException("unable to read database metadata", e); } } /** * @return the current database url and user name (for a given Datasource) * @throws RuntimeException when the database metadata cannot be retrieved */ public static String getDatabaseURLAndUserName(DataSource dataSource) { DatabaseMetaDataCallback callback = new DatabaseMetaDataCallback() { public Object processMetaData(DatabaseMetaData dbmd) throws SQLException, MetaDataAccessException { String url = dbmd.getURL(); String userName = dbmd.getUserName(); StringBuilder s = new StringBuilder(url); s.append(" (userName='"); s.append(userName); s.append("')"); return s.toString(); } }; try { return (String) JdbcUtils.extractDatabaseMetaData(dataSource, callback); } catch (Exception e) { throw new RuntimeException("unable to read database metadata", e); } } // -------------------------------------------------------------------------- // public inner classes // -------------------------------------------------------------------------- public static class ArgsAndTypesHolder { private Object[] args; private int[] argTypes; public ArgsAndTypesHolder(Object[] args, int[] argTypes) { this.args = args; this.argTypes = argTypes; } public Object[] getArgs() { return args; } public void setArgs(Object[] args) { this.args = args; } public int[] getArgTypes() { return argTypes; } public void setArgTypes(int[] argTypes) { this.argTypes = argTypes; } } }