Java tutorial
/******************************************************************************* * Copyright (c) 2009 David Harrison. * All rights reserved. This program and the accompanying materials * are made available under the terms of the GNU Public License v3.0 * which accompanies this distribution, and is available at * http://www.gnu.org/licenses/gpl-3.0.html * * Contributors: * David Harrison - initial API and implementation ******************************************************************************/ package com.sfs.dao; import java.io.File; import java.io.FileWriter; import java.io.IOException; import java.util.ArrayList; import java.util.Collection; import java.util.Iterator; import java.sql.ResultSet; import java.sql.SQLException; import org.apache.commons.lang.StringUtils; import org.apache.log4j.Logger; import org.springframework.jdbc.core.RowMapper; import org.springframework.dao.IncorrectResultSizeDataAccessException; /** * Non-mysqldump backup based on code supplied by Alazar Ghebrehiwet. * http://forums.mysql.com/read.php?28,21329,90781#msg-90781 */ public class BackupDAOImpl extends BaseDAOImpl implements BackupDAO { /** The data logger. */ private static Logger dataLogger = Logger.getLogger(BackupDAOImpl.class); /** * Backup the database to the supplied File object. * * @param backupFile the backup file * * @throws com.sfs.dao.SFSDaoException * @throws SFSDaoException the SFS dao * exception */ public final void backup(final File backupFile) throws SFSDaoException { FileWriter sqlbackup = null; try { sqlbackup = new FileWriter(backupFile); } catch (IOException ioe) { throw new SFSDaoException("Error creating backup file: " + ioe.getMessage()); } dataLogger.info("Backing up the database"); try { Collection<String> tables = getTables(); if (tables != null) { /** * Do not bother with the create database statement as it * assumed that a database will exist due to the nature of JNDI * connections. * * query the table structure of the database using Show Create * Table query which is unique to mysql returns resultset with * two fields databasename and the structrure **/ dataLogger.debug("Create table SQL: " + this.getSQL().getValue("backup/createtable")); for (String tableName : tables) { sqlbackup.write("--\n"); sqlbackup.write("-- Table structure for table '"); sqlbackup.write(tableName); sqlbackup.write("'\n--\n\n"); sqlbackup.write("DROP TABLE IF EXISTS `"); sqlbackup.write(tableName); sqlbackup.write("`;\n"); String createSQL = this.getSQL().getValue("backup/createtable") + " " + tableName; Collection<String> tableDescriptions = getTableDesc(createSQL); // Iterate through the returned table descriptions and add to output for (String tableDescription : tableDescriptions) { sqlbackup.write(tableDescription); sqlbackup.write(";\n\n"); } } // With the structures dumped now dump the table data dataLogger.debug("Dump data SQL: " + this.getSQL().getValue("backup/selectdata")); for (String tableName : tables) { sqlbackup.write("--\n"); sqlbackup.write("-- Dumping data for table '"); sqlbackup.write(tableName); sqlbackup.write("'\n--\n\n"); sqlbackup.write("/*!40000 ALTER TABLE `"); sqlbackup.write(tableName); sqlbackup.write("` DISABLE KEYS */;\n"); sqlbackup.write("LOCK TABLES `"); sqlbackup.write(tableName); sqlbackup.write("` WRITE;\n"); String selectSQL = this.getSQL().getValue("backup/selectdata") + " " + tableName; Collection<String> tableContents = getTableContents(selectSQL); if (tableContents.size() > 0) { sqlbackup.write("INSERT INTO `"); sqlbackup.write(tableName); sqlbackup.write("` VALUES \n"); // Iterate through the returned table contents and // add to output Iterator<String> contentIterator = tableContents.iterator(); int x = 0; while (contentIterator.hasNext()) { String tableContent = contentIterator.next(); sqlbackup.write(tableContent); x++; if (x == tableContents.size()) { sqlbackup.write(";\n\n"); } else { sqlbackup.write(",\n"); } } sqlbackup.write("UNLOCK TABLES;\n"); sqlbackup.write("/*!40000 ALTER TABLE `"); sqlbackup.write(tableName); sqlbackup.write("` ENABLE KEYS */;\n"); } } } } catch (IOException ioe) { throw new SFSDaoException("Error writing to backup file: " + ioe.getMessage()); } finally { // Close the file writer if (sqlbackup != null) { try { sqlbackup.close(); } catch (IOException ioe) { throw new SFSDaoException("Error closing backup file: " + ioe.getMessage()); } } } } /** * Filter data. * * @param dataVal the data * * @return the string */ private String filterData(final String dataVal) { String data = ""; data = StringUtils.replace(dataVal, "[']", "\\\\'"); data = StringUtils.replace(data, "[\"]", "\\\\\""); data = StringUtils.replace(data, "[\n]", "\\\\n"); data = StringUtils.replace(data, "[\t]", "\\\\t"); data = StringUtils.replace(data, "[\r]", "\\\\r"); return data; } /** * Gets the table names. * * @return the table names as a collection of strings */ @SuppressWarnings("unchecked") private Collection<String> getTables() { Collection<String> tables = null; try { tables = this.getJdbcTemplateReader().query(getSQL().getValue("backup/showtables"), new RowMapper() { public Object mapRow(final ResultSet rs, final int rowNum) throws SQLException { return rs.getString(1); } }); } catch (IncorrectResultSizeDataAccessException ie) { dataLogger.debug("No tables results found: " + ie.getMessage()); } return tables; } /** * Gets the table descriptions. * * @param sql the sql string * * @return the table descriptions as a collection of strings */ @SuppressWarnings("unchecked") private Collection<String> getTableDesc(final String sql) { Collection<String> tableDescriptions = null; try { tableDescriptions = this.getJdbcTemplateReader().query(sql, new RowMapper() { public Object mapRow(final ResultSet rs, final int rowNum) throws SQLException { return rs.getString(2); } }); } catch (IncorrectResultSizeDataAccessException ie) { dataLogger.debug("No table description results found: " + ie.getMessage()); } if (tableDescriptions == null) { tableDescriptions = new ArrayList<String>(); } return tableDescriptions; } /** * Gets the table contents. * * @param sql the sql string * * @return the table contents */ @SuppressWarnings("unchecked") private Collection<String> getTableContents(final String sql) { Collection<String> tableContents = null; try { tableContents = this.getJdbcTemplateReader().query(sql, new RowMapper() { public Object mapRow(final ResultSet rs, final int rowNum) throws SQLException { int colCount = rs.getMetaData().getColumnCount(); final StringBuffer sqlValues = new StringBuffer(); sqlValues.append("("); for (int x = 1; x < colCount + 1; x++) { String data = null; try { data = rs.getString(x); } catch (SQLException sqe) { dataLogger.debug("Error getting data field: " + sqe.getMessage()); } sqlValues.append("\'"); if (data != null) { sqlValues.append(filterData(data)); } sqlValues.append("\'"); if (x < colCount) { sqlValues.append(", "); } } sqlValues.append(")"); return sqlValues.toString(); } }); } catch (IncorrectResultSizeDataAccessException ie) { dataLogger.debug("No table contents results found: " + ie.getMessage()); } if (tableContents == null) { tableContents = new ArrayList<String>(); } return tableContents; } }