Java tutorial
/** * Copyright (c) 2000-2012 Liferay, Inc. All rights reserved. * * This library 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 library 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. */ package com.liferay.portal.upgrade.util; import com.liferay.portal.kernel.dao.jdbc.DataAccess; import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader; import com.liferay.portal.kernel.io.unsync.UnsyncBufferedWriter; import com.liferay.portal.kernel.log.Log; import com.liferay.portal.kernel.log.LogFactoryUtil; import com.liferay.portal.kernel.upgrade.StagnantRowException; import com.liferay.portal.kernel.upgrade.UpgradeException; import com.liferay.portal.kernel.util.DateUtil; import com.liferay.portal.kernel.util.FileUtil; import com.liferay.portal.kernel.util.GetterUtil; import com.liferay.portal.kernel.util.StringBundler; import com.liferay.portal.kernel.util.StringPool; import com.liferay.portal.kernel.util.StringUtil; import com.liferay.portal.kernel.util.SystemProperties; import com.liferay.portal.kernel.util.Validator; import com.liferay.portal.kernel.uuid.PortalUUIDUtil; import com.liferay.portal.util.PropsUtil; import java.io.FileReader; import java.io.FileWriter; import java.sql.Clob; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; import java.sql.Types; import java.text.DateFormat; import java.util.Date; import org.apache.commons.lang.time.StopWatch; /** * @author Alexander Chow * @author Brian Wing Shun Chan * @author Raymond Aug */ public class Table { public static final int BATCH_SIZE = GetterUtil.getInteger(PropsUtil.get("hibernate.jdbc.batch_size")); public static final String SAFE_COMMA_CHARACTER = "_SAFE_COMMA_CHARACTER_"; public static final String SAFE_NEWLINE_CHARACTER = "_SAFE_NEWLINE_CHARACTER_"; public static final String SAFE_RETURN_CHARACTER = "_SAFE_RETURN_CHARACTER_"; public static final String[][] SAFE_CHARS = { { StringPool.RETURN, StringPool.COMMA, StringPool.NEW_LINE }, { SAFE_RETURN_CHARACTER, SAFE_COMMA_CHARACTER, SAFE_NEWLINE_CHARACTER } }; public Table(String tableName) { _tableName = tableName; } public Table(String tableName, Object[][] columns) { _tableName = tableName; setColumns(columns); } public void appendColumn(StringBuilder sb, Object value, boolean last) throws Exception { if (value == null) { throw new UpgradeException("Nulls should never be inserted into the database. " + "Attempted to append column to " + sb.toString() + "."); } else if (value instanceof Clob || value instanceof String) { value = StringUtil.replace((String) value, SAFE_CHARS[0], SAFE_CHARS[1]); sb.append(value); } else if (value instanceof Date) { DateFormat df = DateUtil.getISOFormat(); sb.append(df.format(value)); } else { sb.append(value); } sb.append(StringPool.COMMA); if (last) { sb.append(StringPool.NEW_LINE); } } public void appendColumn(StringBuilder sb, ResultSet rs, String name, Integer type, boolean last) throws Exception { Object value = null; try { value = getValue(rs, name, type); } catch (SQLException sqle) { if (name.equals("uuid_")) { sb.append(PortalUUIDUtil.generate()); } sb.append(StringPool.COMMA); if (last) { sb.append(StringPool.NEW_LINE); } return; } appendColumn(sb, value, last); } public String generateTempFile() throws Exception { Connection con = DataAccess.getConnection(); try { return generateTempFile(con); } finally { DataAccess.cleanUp(con); } } public String generateTempFile(Connection con) throws Exception { PreparedStatement ps = null; ResultSet rs = null; boolean empty = true; String tempFileName = SystemProperties.get(SystemProperties.TMP_DIR) + "/temp-db-" + _tableName + "-" + System.currentTimeMillis(); StopWatch stopWatch = null; if (_log.isInfoEnabled()) { stopWatch = new StopWatch(); stopWatch.start(); _log.info("Starting backup of " + _tableName + " to " + tempFileName); } String selectSQL = getSelectSQL(); UnsyncBufferedWriter unsyncBufferedWriter = new UnsyncBufferedWriter(new FileWriter(tempFileName)); try { ps = con.prepareStatement(selectSQL); rs = ps.executeQuery(); while (rs.next()) { String data = null; try { data = getExportedData(rs); unsyncBufferedWriter.write(data); _totalRows++; empty = false; } catch (StagnantRowException sre) { if (_log.isWarnEnabled()) { _log.warn("Skipping stagnant data in " + _tableName + ": " + sre.getMessage()); } } } if (_log.isInfoEnabled()) { _log.info("Finished backup of " + _tableName + " to " + tempFileName + " in " + stopWatch.getTime() + " ms"); } } catch (Exception e) { FileUtil.delete(tempFileName); throw e; } finally { DataAccess.cleanUp(null, ps, rs); unsyncBufferedWriter.close(); } if (!empty) { return tempFileName; } else { FileUtil.delete(tempFileName); return null; } } public Object[][] getColumns() { return _columns; } public String getCreateSQL() throws Exception { return _createSQL; } public String getDeleteSQL() throws Exception { return "DELETE FROM " + _tableName; } public String getExportedData(ResultSet rs) throws Exception { StringBuilder sb = new StringBuilder(); Object[][] columns = getColumns(); for (int i = 0; i < columns.length; i++) { boolean last = false; if ((i + 1) == columns.length) { last = true; } appendColumn(sb, rs, (String) columns[i][0], (Integer) columns[i][1], last); } return sb.toString(); } public String getInsertSQL() throws Exception { String sql = "INSERT INTO " + getInsertTableName() + " ("; for (int i = 0; i < _order.length; i++) { int pos = _order[i]; sql += _columns[pos][0]; if ((i + 1) < _columns.length) { sql += ", "; } else { sql += ") VALUES ("; } } for (int i = 0; i < _columns.length; i++) { sql += "?"; if ((i + 1) < _columns.length) { sql += ", "; } else { sql += ")"; } } return sql; } public String getInsertTableName() throws Exception { String createSQL = getCreateSQL(); if (Validator.isNotNull(createSQL)) { String createSQLLowerCase = createSQL.toLowerCase(); int x = createSQLLowerCase.indexOf("create table "); if (x == -1) { return _tableName; } x += 13; int y = createSQL.indexOf(" ", x); return createSQL.substring(x, y).trim(); } else { return _tableName; } } public int[] getOrder() { return _order; } public String getSelectSQL() throws Exception { if (_selectSQL == null) { /*String sql = "select "; for (int i = 0; i < _columns.length; i++) { sql += _columns[i][0]; if ((i + 1) < _columns.length) { sql += ", "; } else { sql += " from " + _tableName; } } return sql;*/ return "select * from " + _tableName; } else { return _selectSQL; } } public String getTableName() { return _tableName; } public long getTotalRows() { return _totalRows; } public Object getValue(ResultSet rs, String name, Integer type) throws Exception { Object value = null; int t = type.intValue(); if (t == Types.BIGINT) { try { value = GetterUtil.getLong(rs.getLong(name)); } catch (SQLException e) { value = GetterUtil.getLong(rs.getString(name)); } } else if (t == Types.BOOLEAN) { value = GetterUtil.getBoolean(rs.getBoolean(name)); } else if (t == Types.CLOB) { try { Clob clob = rs.getClob(name); if (clob == null) { value = StringPool.BLANK; } else { UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(clob.getCharacterStream()); StringBundler sb = new StringBundler(); String line = null; while ((line = unsyncBufferedReader.readLine()) != null) { if (sb.length() != 0) { sb.append(SAFE_NEWLINE_CHARACTER); } sb.append(line); } value = sb.toString(); } } catch (Exception e) { // If the database doesn't allow CLOB types for the column // value, then try retrieving it as a String value = GetterUtil.getString(rs.getString(name)); } } else if (t == Types.DOUBLE) { value = GetterUtil.getDouble(rs.getDouble(name)); } else if (t == Types.FLOAT) { value = GetterUtil.getFloat(rs.getFloat(name)); } else if (t == Types.INTEGER) { value = GetterUtil.getInteger(rs.getInt(name)); } else if (t == Types.SMALLINT) { value = GetterUtil.getShort(rs.getShort(name)); } else if (t == Types.TIMESTAMP) { try { value = rs.getTimestamp(name); } catch (Exception e) { } if (value == null) { value = StringPool.NULL; } } else if (t == Types.VARCHAR) { value = GetterUtil.getString(rs.getString(name)); } else { throw new UpgradeException("Upgrade code using unsupported class type " + type); } return value; } public void populateTable(String tempFileName) throws Exception { Connection con = DataAccess.getConnection(); try { populateTable(tempFileName, con); } finally { DataAccess.cleanUp(con); } } public void populateTable(String tempFileName, Connection con) throws Exception { PreparedStatement ps = null; String insertSQL = getInsertSQL(); UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(new FileReader(tempFileName)); String line = null; try { DatabaseMetaData databaseMetaData = con.getMetaData(); if (!databaseMetaData.supportsBatchUpdates()) { if (_log.isDebugEnabled()) { _log.debug("Database does not support batch updates"); } } int count = 0; while ((line = unsyncBufferedReader.readLine()) != null) { String[] values = StringUtil.split(line); Object[][] columns = getColumns(); if ((values.length) != (columns.length)) { throw new UpgradeException("Column lengths differ between temp file and schema. " + "Attempted to insert row " + line + "."); } if (count == 0) { ps = con.prepareStatement(insertSQL); } int[] order = getOrder(); for (int i = 0; i < order.length; i++) { int pos = order[i]; setColumn(ps, i, (Integer) columns[pos][1], values[pos]); } if (databaseMetaData.supportsBatchUpdates()) { ps.addBatch(); if (count == BATCH_SIZE) { populateTableRows(ps, true); count = 0; } else { count++; } } else { populateTableRows(ps, false); } } if (databaseMetaData.supportsBatchUpdates()) { if (count != 0) { populateTableRows(ps, true); } } } finally { DataAccess.cleanUp(null, ps); unsyncBufferedReader.close(); } if (_log.isDebugEnabled()) { _log.debug(getTableName() + " table populated with data"); } } public void populateTableRows(PreparedStatement ps, boolean batch) throws Exception { if (_log.isDebugEnabled()) { _log.debug("Updating rows for " + getTableName()); } if (batch) { ps.executeBatch(); } else { ps.executeUpdate(); } ps.close(); } public void setColumn(PreparedStatement ps, int index, Integer type, String value) throws Exception { int t = type.intValue(); int paramIndex = index + 1; if (t == Types.BIGINT) { ps.setLong(paramIndex, GetterUtil.getLong(value)); } else if (t == Types.BOOLEAN) { ps.setBoolean(paramIndex, GetterUtil.getBoolean(value)); } else if ((t == Types.CLOB) || (t == Types.VARCHAR)) { value = StringUtil.replace(value, SAFE_CHARS[1], SAFE_CHARS[0]); ps.setString(paramIndex, value); } else if (t == Types.DOUBLE) { ps.setDouble(paramIndex, GetterUtil.getDouble(value)); } else if (t == Types.FLOAT) { ps.setFloat(paramIndex, GetterUtil.getFloat(value)); } else if (t == Types.INTEGER) { ps.setInt(paramIndex, GetterUtil.getInteger(value)); } else if (t == Types.SMALLINT) { ps.setShort(paramIndex, GetterUtil.getShort(value)); } else if (t == Types.TIMESTAMP) { if (StringPool.NULL.equals(value)) { ps.setTimestamp(paramIndex, null); } else { DateFormat df = DateUtil.getISOFormat(); ps.setTimestamp(paramIndex, new Timestamp(df.parse(value).getTime())); } } else { throw new UpgradeException("Upgrade code using unsupported class type " + type); } } public void setColumns(Object[][] columns) { _columns = columns; // LEP-7331 _order = new int[_columns.length]; int clobCount = 0; for (int i = 0; i < _columns.length; ++i) { Integer type = (Integer) columns[i][1]; if (type.intValue() == Types.CLOB) { clobCount++; int pos = _columns.length - clobCount; _order[pos] = i; } else { int pos = i - clobCount; _order[pos] = i; } } } public void setCreateSQL(String createSQL) throws Exception { _createSQL = createSQL; } public void setSelectSQL(String selectSQL) throws Exception { _selectSQL = selectSQL; } private static Log _log = LogFactoryUtil.getLog(Table.class); private Object[][] _columns; private String _createSQL; private int[] _order; private String _selectSQL; private String _tableName; private long _totalRows; }