Java tutorial
/* =================================================================== * AbstractJdbcDao.java * * Created Jul 15, 2008 8:36:00 AM * * This program 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 2 of * the License, or (at your option) any later version. * * This program 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 this program; if not, write to the Free Software * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA * 02111-1307 USA * =================================================================== */ package net.solarnetwork.node.dao.jdbc; import static net.solarnetwork.node.dao.jdbc.JdbcDaoConstants.SCHEMA_NAME; import static net.solarnetwork.node.dao.jdbc.JdbcDaoConstants.TABLE_SETTINGS; import java.io.IOException; import java.io.InputStreamReader; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Arrays; import java.util.HashMap; import java.util.Map; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.context.MessageSource; import org.springframework.context.support.ResourceBundleMessageSource; import org.springframework.core.io.ClassPathResource; import org.springframework.core.io.Resource; import org.springframework.dao.DataAccessException; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.ConnectionCallback; import org.springframework.jdbc.core.PreparedStatementCallback; import org.springframework.jdbc.core.PreparedStatementCreator; import org.springframework.jdbc.core.support.JdbcDaoSupport; import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.util.FileCopyUtils; import org.springframework.util.StringUtils; /** * Base class for JDBC based DAO implementations. * * <p> * This class extends {@link JdbcDaoSupport} with methods for handling upgrade * maintenance of the table(s) managed by this DAO over time, e.g. creating * tables if they don't exist, running DDL update scripts to upgrade to a new * version, etc. * </p> * * @author matt * @version 1.1 * @param <T> * the domain object type managed by this DAO */ public abstract class AbstractJdbcDao<T> extends JdbcDaoSupport implements JdbcDao { /** A class-level Logger. */ protected final Logger log = LoggerFactory.getLogger(getClass()); private String sqlGetTablesVersion = null; private String sqlResourcePrefix = null; private int tablesVersion = 1; private boolean useAutogeneratedKeys = false; private Resource initSqlResource = null; private String schemaName = SCHEMA_NAME; private String tableName = TABLE_SETTINGS; private MessageSource messageSource = null; private final Map<String, String> sqlResourceCache = new HashMap<String, String>(10); /** * Initialize this class after properties are set. */ public void init() { // verify database table exists, and if not create it verifyDatabaseExists(this.schemaName, this.tableName, this.initSqlResource); // now veryify database tables version is up-to-date try { upgradeTablesVersion(); } catch (IOException e) { throw new RuntimeException("Unable to upgrade tables to version " + getTablesVersion(), e); } if (messageSource == null) { ResourceBundleMessageSource ms = new ResourceBundleMessageSource(); ms.setBasename(getClass().getName()); ms.setBundleClassLoader(getClass().getClassLoader()); setMessageSource(ms); } } /** * Insert a new domain object. * * @param obj * the domain object to insert * @param sqlInsert * the SQL to persist the object with */ protected void insertDomainObject(final T obj, final String sqlInsert) { getJdbcTemplate().update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection con) throws SQLException { PreparedStatement ps = con.prepareStatement(sqlInsert); setStoreStatementValues(obj, ps); return ps; } }); } /** * Store (insert) a new domain object. * * <p> * If {@link #isUseAutogeneratedKeys()} is <em>true</em> then this method * will use JDBC's {@link Statement#RETURN_GENERATED_KEYS} to obtain the * auto-generated primary key for the newly inserted object. Otherwise, this * method will call the * {@link #storeDomainObjectWithoutAutogeneratedKeys(T, String)} method. * </p> * * @param obj * the domain object to persist * @param sqlInsert * the SQL to persist the object with * @return the primary key created for the domain object */ protected Long storeDomainObject(final T obj, final String sqlInsert) { if (!useAutogeneratedKeys) { return storeDomainObjectWithoutAutogeneratedKeys(obj, sqlInsert); } GeneratedKeyHolder keyHolder = new GeneratedKeyHolder(); getJdbcTemplate().update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection con) throws SQLException { PreparedStatement ps = con.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS); setStoreStatementValues(obj, ps); return ps; } }, keyHolder); if (keyHolder.getKey() != null) { return Long.valueOf(keyHolder.getKey().longValue()); } return null; } /** * Set {@link PreparedStatement} values for storing a domain object. * * <p> * Called from {@link #storeDomainObject(T, String)} and * {@link #storeDomainObjectWithoutAutogeneratedKeys(T, String)} to persist * values of a domain object. * </p> * * <p> * This implementation does not do anything. Extending classes should * override this and set values on the {@code PreparedStatement} object as * needed to persist the domain object. * </p> * * @param obj * the domain object to persist * @param ps * the PreparedStatement to persist with * @throws SQLException * if any SQL error occurs */ protected void setStoreStatementValues(T obj, PreparedStatement ps) throws SQLException { // this is a no-op method, override to do something useful } /** * Persist a domain object, without using auto-generated keys. * * @param obj * the domain object to persist * @param sqlInsert * the SQL insert statement to use * @return the primary key created for the domain object */ protected Long storeDomainObjectWithoutAutogeneratedKeys(final T obj, final String sqlInsert) { Object result = getJdbcTemplate().execute(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection con) throws SQLException { PreparedStatement ps = con.prepareStatement(sqlInsert); setStoreStatementValues(obj, ps); return ps; } }, new PreparedStatementCallback<Object>() { @Override public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException { ps.execute(); int count = ps.getUpdateCount(); if (count == 1 && ps.getMoreResults()) { ResultSet rs = ps.getResultSet(); if (rs.next()) { return rs.getObject(1); } } return null; } }); if (result instanceof Long) { return (Long) result; } else if (result instanceof Number) { return Long.valueOf(((Number) result).longValue()); } if (log.isWarnEnabled()) { log.warn("Unexpected (non-number) primary key returned: " + result); } return null; } /** * Verify a database table exists, and if not initialize the database with * the SQL in the provided {@code initSqlResource}. * * @param schema * the schema to check * @param table * the table to check * @param initSql * the init SQL resource */ protected void verifyDatabaseExists(final String schema, final String table, final Resource initSql) { getJdbcTemplate().execute(new ConnectionCallback<Object>() { @Override public Object doInConnection(Connection con) throws SQLException, DataAccessException { if (!tableExists(con, schema, table)) { String[] initSqlStatements = getBatchSqlResource(initSql); if (initSqlStatements != null) { if (log.isInfoEnabled()) { log.info("Initializing database from [" + initSql + ']'); } getJdbcTemplate().batchUpdate(initSqlStatements); } } return null; } }); } /** * Test if a table exists in the database. * * @param conn * the connection * @param aSchemaName * the schema name to look for (or <em>null</em> for any schema) * @param aTableName * the table name to look for * @return boolean if table is found * @throws SQLException * if any SQL error occurs */ protected boolean tableExists(Connection conn, String aSchemaName, String aTableName) throws SQLException { DatabaseMetaData dbMeta = conn.getMetaData(); ResultSet rs = null; try { rs = dbMeta.getTables(null, null, null, null); while (rs.next()) { String schema = rs.getString(2); String table = rs.getString(3); if ((aSchemaName == null || (aSchemaName.equalsIgnoreCase(schema))) && aTableName.equalsIgnoreCase(table)) { if (log.isDebugEnabled()) { log.debug("Found table " + schema + '.' + table); } return true; } } return false; } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { // ignore this } } } } /** * Upgrade the database tables to the configured version, if the database * version is less than the configured version. * * <p> * This method uses the {@link #getSqlGetTablesVersion()} SQL statement to * query for the current database table version. If the version found there * is less than the {@link #getTablesVersion()} value then a sequence of SQL * resources, relative to the {@link #getInitSqlResource()} resource, are * loaded and executed. The resources are assumed to have a file name patter * like <code><em>[tablesUpdatePrefix]</em>-update-<em>[#]</em>.sql</code> * where <em>[tablesUpdatePrefix]</em> is the * {@link #getTablesUpdatePrefix()} value and <em>[#]</em> is the version * number, starting at the currently found table version + 1 up through and * including {@link #getTablesVersion()}. If no version value is found when * querying, the current version is assumed to be {@code 0}. * </p> * * <p> * For example, if by querying the current version is reported as {@code 1} * and the {@link #getTablesVersion()} value is {@code 3}, and the * {@link #getTablesUpdatePrefix()} is {@code derby-mytable}, the following * SQL resources will be exectued: * </p> * * <ol> * <li>derby-mytable-update-2.sql</li> * <li>derby-mytable-update-3.sql</li> * </ol> * * <p> * Each update SQL is expected, therefor, to also update the "current" table * version so that after running the update subsequent calls to this method * where {@link #getTablesVersion()} has not changed will not do anything. * </p> * * @throws IOException * if update resources cannot be found */ protected void upgradeTablesVersion() throws IOException { String currVersion = "0"; try { currVersion = getJdbcTemplate().queryForObject(sqlGetTablesVersion, String.class); } catch (EmptyResultDataAccessException e) { if (log.isInfoEnabled()) { log.info("Table version setting not found, assuming version 0."); } } int curr = Integer.parseInt(currVersion); while (curr < this.tablesVersion) { if (log.isInfoEnabled()) { log.info("Updating database tables version from " + curr + " to " + (curr + 1)); } Resource sql = this.initSqlResource .createRelative(this.sqlResourcePrefix + "-update-" + (curr + 1) + ".sql"); String[] batch = getBatchSqlResource(sql); int[] result = getJdbcTemplate().batchUpdate(batch); if (log.isDebugEnabled()) { log.debug("Database tables updated to version " + (curr + 1) + " update results: " + Arrays.toString(result)); } curr++; } } /** * Load a classpath SQL resource into a String. * * <p> * The classpath resource is taken as the {@link #getSqlResourcePrefix()} * value and {@code -} and the {@code classPathResource} combined with a * {@code .sql} suffix. If that resoruce is not found, then the prefix is * split into components separated by a {@code -} character, and the last * component is dropped and then combined with {@code -} and * {@code classPathResource} again to try to find a match, until there is no * prefix left and just the {@code classPathResource} itself is tried. * </p> * * <p> * This method will cache the SQL resource in-memory for quick future * access. * </p> * * @param string * the classpath resource to load as a SQL string * @return the String */ protected String getSqlResource(String classPathResource) { Class<?> myClass = getClass(); String resourceName = getSqlResourcePrefix() + "-" + classPathResource + ".sql"; String key = myClass.getName() + ";" + classPathResource; if (sqlResourceCache.containsKey(key)) { return sqlResourceCache.get(key); } String[] prefixes = getSqlResourcePrefix().split("-"); int prefixEndIndex = prefixes.length - 1; try { Resource r = new ClassPathResource(resourceName, myClass); while (!r.exists() && prefixEndIndex >= 0) { // try by chopping down prefix, which we split on a dash character String subName; if (prefixEndIndex > 0) { String[] subPrefixes = new String[prefixEndIndex]; System.arraycopy(prefixes, prefixEndIndex, subPrefixes, 0, prefixEndIndex); subName = StringUtils.arrayToDelimitedString(subPrefixes, "-") + "-" + classPathResource; } else { subName = classPathResource; } subName += ".sql"; r = new ClassPathResource(subName, myClass); prefixEndIndex--; } if (!r.exists()) { throw new RuntimeException("SQL resource " + resourceName + " not found"); } String result = FileCopyUtils.copyToString(new InputStreamReader(r.getInputStream())); if (result != null && result.length() > 0) { sqlResourceCache.put(key, result); } return result; } catch (IOException e) { throw new RuntimeException(e); } } /** * Load a SQL resource into a String. * * @param resource * the SQL resource to load * @return the String */ protected String getSqlResource(Resource resource) { try { return FileCopyUtils.copyToString(new InputStreamReader(resource.getInputStream())); } catch (IOException e) { throw new RuntimeException(e); } } /** * Get batch SQL statements, split into multiple statements on the * {@literal ;} character. * * @param sqlResource * the SQL resource to load * @return split SQL */ protected String[] getBatchSqlResource(Resource sqlResource) { String sql = getSqlResource(sqlResource); if (sql == null) { return null; } return sql.split(";\\s*"); } /** * This implementation simply returns a new array with a single value: * {@link #getTableName()}. * * @see net.solarnetwork.node.dao.jdbc.JdbcDao#getTableNames() */ @Override public String[] getTableNames() { return new String[] { getTableName() }; } public String getSqlGetTablesVersion() { return sqlGetTablesVersion; } public void setSqlGetTablesVersion(String sqlGetTablesVersion) { this.sqlGetTablesVersion = sqlGetTablesVersion; } /** * @return the tablesUpdatePrefix * @deprecated use {@link #getSqlResourcePrefix()} */ @Deprecated public String getTablesUpdatePrefix() { return getSqlResourcePrefix(); } /** * @param tablesUpdatePrefix * the tablesUpdatePrefix to set * @deprecated use {@link #setSqlResourcePrefix(String)} */ @Deprecated public void setTablesUpdatePrefix(String tablesUpdatePrefix) { setSqlResourcePrefix(tablesUpdatePrefix); } public String getSqlResourcePrefix() { return sqlResourcePrefix; } public void setSqlResourcePrefix(String sqlResourcePrefix) { this.sqlResourcePrefix = sqlResourcePrefix; } public int getTablesVersion() { return tablesVersion; } /** * @param tablesVersion * the tablesVersion to set */ public void setTablesVersion(int tablesVersion) { this.tablesVersion = tablesVersion; } public boolean isUseAutogeneratedKeys() { return useAutogeneratedKeys; } public void setUseAutogeneratedKeys(boolean useAutogeneratedKeys) { this.useAutogeneratedKeys = useAutogeneratedKeys; } public Resource getInitSqlResource() { return initSqlResource; } public void setInitSqlResource(Resource initSqlResource) { this.initSqlResource = initSqlResource; } @Override public String getSchemaName() { return schemaName; } public void setSchemaName(String schemaName) { this.schemaName = schemaName; } @Override public String getTableName() { return tableName; } public void setTableName(String tableName) { this.tableName = tableName; } @Override public MessageSource getMessageSource() { return messageSource; } public void setMessageSource(MessageSource messageSource) { this.messageSource = messageSource; } }