001: package com.xoetrope.util;
002:
003: import java.awt.Dimension;
004: import java.awt.GridLayout;
005: import java.sql.Connection;
006: import java.sql.DriverManager;
007: import java.sql.DatabaseMetaData;
008: import java.sql.SQLException;
009: import java.sql.Statement;
010: import java.sql.ResultSet;
011: import java.sql.Types;
012: import java.sql.PreparedStatement;
013: import java.sql.ResultSetMetaData;
014:
015: import java.util.ArrayList;
016: import java.util.prefs.Preferences;
017:
018: import javax.swing.JLabel;
019: import javax.swing.JPasswordField;
020: import javax.swing.JTextField;
021: import javax.swing.JOptionPane;
022: import javax.swing.JPanel;
023:
024: /**
025: * Transfer data between two JDBC databases
026: *
027: * <p> Copyright (c) Xoetrope Ltd., 2001-2006, This software is licensed under
028: * the GNU Public License (GPL), please see license.txt for more details. If
029: * you make commercial use of this software you must purchase a commercial
030: * license from Xoetrope.</p>
031: * <p> $Revision: 1.6 $</p>
032: */
033: public class DatabaseTransferManager {
034: protected String sourceDriverName;
035: protected String sourceConnectionString;
036: protected String sourceUserName;
037: protected String sourcePassword;
038:
039: protected String targetDriverName;
040: protected String targetConnectionString;
041: protected String targetUserName;
042: protected String targetPassword;
043:
044: protected Connection sourceConn;
045: protected Connection targetConn;
046:
047: protected boolean dropTables;
048:
049: protected Preferences prefs;
050:
051: private DatabaseMetaData databaseMetaData;
052:
053: public static void main(String[] args) {
054: DatabaseTransferManager tm = new DatabaseTransferManager();
055: if (tm.getConnectionParameters(true)
056: && tm.getConnectionParameters(false))
057: tm.doTransfer();
058: }
059:
060: /** Creates a new instance of TransferManager */
061: public DatabaseTransferManager() {
062: dropTables = true;
063: prefs = Preferences
064: .userNodeForPackage(DatabaseTransferManager.class);
065:
066: sourceDriverName = prefs.get("SourceDriver",
067: "sun.jdbc.odbc.JdbcOdbcDriver");
068: sourceConnectionString = prefs.get("SourceUrl",
069: "jdbc:odbc:<database name>");
070: sourceUserName = prefs.get("SourceUserName", "sa");
071: sourcePassword = prefs.get("SourcePassword", "");
072:
073: targetDriverName = prefs.get("TargetDriver",
074: "org.hsqldb.jdbcDriver");
075: targetConnectionString = prefs.get("TargetUrl",
076: "jdbc:hsqldb:<database name>");
077: targetUserName = prefs.get("TargetUserName", "sa");
078: targetPassword = prefs.get("TargetPassword", "");
079: }
080:
081: public void doTransfer() {
082: setupDataSource();
083: setupDataTarget();
084: copyTables();
085: closeConnections();
086: }
087:
088: /**
089: * Setup a connection to the source database
090: */
091: public void setupDataSource() {
092: if (sourceUserName == null)
093: getConnectionParameters(true);
094:
095: sourceConn = getConnection(sourceDriverName,
096: sourceConnectionString, sourceUserName, sourcePassword);
097: }
098:
099: /**
100: * Setup a connection to the target or sink database
101: */
102: public void setupDataTarget() {
103: if (targetUserName == null)
104: getConnectionParameters(false);
105:
106: targetConn = getConnection(targetDriverName,
107: targetConnectionString, targetUserName, targetPassword);
108: }
109:
110: /**
111: * Setup a connection to the target or sink database
112: */
113: public void closeConnections() {
114: try {
115: if (sourceConn != null)
116: sourceConn.close();
117: if (targetConn != null)
118: targetConn.close();
119: } catch (SQLException e) {
120: e.printStackTrace();
121: }
122: }
123:
124: /**
125: * Copy tables between the source and sink
126: */
127: public void copyTables() {
128: try {
129: // Get the table names
130: String[] names = { "TABLE" };
131: databaseMetaData = sourceConn.getMetaData();
132: ResultSet rs = databaseMetaData.getTables(null, "%", "%",
133: names);
134: ArrayList tableNames = new ArrayList();
135:
136: while (rs.next()) {
137: // Get the table info
138: String tableName = rs.getString("TABLE_NAME");
139: String tableType = rs.getString("TABLE_TYPE");
140: if ((tableName.indexOf("dt") == 0)
141: || (tableName.indexOf("sys") == 0))
142: continue;
143: else if (tableType.equals("TABLE"))
144: tableNames.add(tableName);
145: }
146: rs.close();
147:
148: int numTables = tableNames.size();
149: for (int i = 0; i < numTables; i++) {
150: String tableName = (String) tableNames.get(i);
151: if (dropTables)
152: dropTable(tableName);
153: else
154: clearTable(tableName);
155:
156: createTable(tableName);
157: copyTableData(tableName);
158: }
159: } catch (SQLException e) {
160: System.err.println(e.getMessage());
161: }
162: }
163:
164: /**
165: * Drop the target table if it exists
166: * @param tableName the name of the table to delete/drop
167: */
168: public void dropTable(String tableName) {
169: try {
170: String sql = "DROP TABLE " + tableName;
171: Statement stmt = targetConn.createStatement();
172: System.out.println(sql);
173: stmt.executeQuery(sql);
174: } catch (SQLException e) {
175: System.err.println(e.getMessage());
176: }
177: }
178:
179: /**
180: * Empty the table of existing data
181: * @param tableName the name of the table to delete/drop
182: */
183: public void clearTable(String tableName) {
184: try {
185: String sql = "DELETE FROM TABLE " + tableName;
186: Statement stmt = targetConn.createStatement();
187: System.out.println(sql);
188: stmt.executeQuery(sql);
189: } catch (SQLException e) {
190: System.err.println(e.getMessage());
191: }
192: }
193:
194: /**
195: * Create a table in the target database
196: * @param tableName the name of the table to delete/drop
197: */
198: public void createTable(String tableName) {
199: try {
200: ResultSet columns = databaseMetaData.getColumns(null, "%",
201: tableName, "%");
202: String sql = "CREATE TABLE " + tableName + " (";
203: String fields = "";
204: while (columns.next()) {
205: String columnName = columns.getString("COLUMN_NAME");
206: int dataType = columns.getInt("DATA_TYPE");
207: String dataTypeName = columns.getString("TYPE_NAME");
208: int dataSize = columns.getInt("COLUMN_SIZE");
209: int digits = columns.getInt("DECIMAL_DIGITS");
210: int nullable = columns.getInt("NULLABLE");
211: boolean isNullable = (nullable == 1);
212:
213: if (fields.length() > 0)
214: fields += ", ";
215:
216: fields += columnName.replace(" ", "_")
217: .replace("#", "_").replace("/", "_").replace(
218: "(", "_").replace(")", "_")
219: + " ";
220: switch (dataType) {
221: case -10: // NTEXT
222: fields += "VARCHAR(255) ";
223: break;
224: case -9: // NVARCHAR
225: fields += "VARCHAR(" + dataSize + ") ";
226: break;
227: case Types.VARCHAR:
228: case Types.CHAR:
229: case Types.LONGVARCHAR:
230: fields += dataTypeName + "(" + dataSize + ") ";
231: break;
232: case Types.BIT:
233: case Types.BOOLEAN:
234: case Types.DATE:
235: case Types.DECIMAL:
236: case Types.DOUBLE:
237: case Types.FLOAT:
238: case Types.INTEGER:
239: case Types.NUMERIC:
240: case Types.REAL:
241: case Types.SMALLINT:
242: case Types.TIME:
243: case Types.TINYINT:
244: fields += dataTypeName;
245: break;
246:
247: case Types.TIMESTAMP:
248: fields += "TIMESTAMP";
249: break;
250: }
251: if (!isNullable)
252: fields += " NOT NULL";
253:
254: }
255: sql += fields + ")";
256: Statement stmt = targetConn.createStatement();
257: System.out.println(sql);
258: stmt.executeQuery(sql);
259: stmt.close();
260: } catch (SQLException ex) {
261: ex.printStackTrace();
262: }
263: }
264:
265: /**
266: * Copy the table's data
267: * @param tableName the name of the table
268: */
269: public void copyTableData(String tableName) {
270: try {
271: Statement stmt = sourceConn.createStatement(
272: ResultSet.TYPE_SCROLL_INSENSITIVE,
273: ResultSet.CONCUR_READ_ONLY);
274: stmt.setEscapeProcessing(true);
275: stmt.setFetchDirection(ResultSet.FETCH_FORWARD);
276: ResultSet sourceRS = stmt.executeQuery("SELECT * FROM "
277: + tableName);
278: String sql = "INSERT INTO " + tableName + " (";
279: String fields = "";
280: String values = "";
281:
282: ResultSetMetaData rsmd = sourceRS.getMetaData();
283: int numFields = rsmd.getColumnCount();
284: int columnType[] = new int[numFields];
285: for (int k = 0; k < numFields; k++) {
286: String columnName = rsmd.getColumnName(k + 1);
287: columnType[k] = rsmd.getColumnType(k + 1);
288: if (fields.length() > 0)
289: fields += ", ";
290: if (values.length() > 0)
291: values += ", ";
292:
293: fields += columnName.replace(" ", "_")
294: .replace("#", "_").replace("/", "_").replace(
295: "(", "_").replace(")", "_")
296: + " ";
297: values += "?";
298: }
299: sql += fields + ") VALUES (" + values + ")";
300: System.out.println(sql);
301: PreparedStatement preparedStmt = targetConn
302: .prepareStatement(sql);
303:
304: boolean isValid = true;
305: sourceRS.first();
306: while (isValid) {
307: preparedStmt.clearParameters();
308: for (int k = 0; k < numFields; k++) {
309: if (columnType[k] == java.sql.Types.INTEGER) {
310: int iValue = sourceRS.getInt(k + 1);
311: if (!sourceRS.wasNull())
312: preparedStmt.setInt(k + 1, iValue);
313: } else if (columnType[k] == java.sql.Types.FLOAT) {
314: float fValue = sourceRS.getFloat(k + 1);
315: if (!sourceRS.wasNull())
316: preparedStmt.setFloat(k + 1, fValue);
317: } else if (columnType[k] == java.sql.Types.DOUBLE) {
318: double dValue = sourceRS.getDouble(k + 1);
319: if (!sourceRS.wasNull())
320: preparedStmt.setDouble(k + 1, dValue);
321: } else if (columnType[k] == java.sql.Types.BOOLEAN) {
322: boolean bValue = sourceRS.getBoolean(k + 1);
323: if (!sourceRS.wasNull())
324: preparedStmt.setBoolean(k + 1, bValue);
325: } else if (columnType[k] == java.sql.Types.CHAR) {
326: String sValue = sourceRS.getString(k + 1);
327: if (!sourceRS.wasNull())
328: preparedStmt.setString(k + 1, sValue);
329: } else if (columnType[k] == java.sql.Types.DATE) {
330: java.sql.Date dValue = sourceRS.getDate(k + 1);
331: if (!sourceRS.wasNull())
332: preparedStmt.setDate(k + 1, dValue);
333: } else if (columnType[k] == java.sql.Types.TIME) {
334: java.sql.Time tValue = sourceRS.getTime(k + 1);
335: if (!sourceRS.wasNull())
336: preparedStmt.setTime(k + 1, tValue);
337: } else if (columnType[k] == java.sql.Types.TIMESTAMP) {
338: java.sql.Timestamp tValue = sourceRS
339: .getTimestamp(k + 1);
340: if (!sourceRS.wasNull())
341: preparedStmt.setTimestamp(k + 1, tValue);
342: } else {
343: String sValue = sourceRS.getString(k + 1);
344: if (!sourceRS.wasNull())
345: preparedStmt.setString(k + 1, sValue);
346: }
347: }
348: preparedStmt.executeUpdate();
349: isValid = sourceRS.next();
350: }
351:
352: } catch (SQLException ex) {
353: ex.printStackTrace();
354: }
355: }
356:
357: /**
358: * Ask the user for the connection parameters
359: * @param isSource true if the source parameters are being queried
360: * @return true if the input is ok
361: */
362: public boolean getConnectionParameters(boolean isSource) {
363: JLabel[] labels = new JLabel[4];
364: labels[0] = new JLabel("Driver class:");
365: labels[1] = new JLabel("Database URL:");
366: labels[2] = new JLabel("User name:");
367: labels[3] = new JLabel("Password:");
368: JTextField[] inputs = new JTextField[4];
369: inputs[0] = new JTextField(isSource ? sourceDriverName
370: : targetDriverName);
371: inputs[1] = new JTextField(isSource ? sourceConnectionString
372: : targetConnectionString);
373: inputs[2] = new JTextField(isSource ? sourceUserName
374: : targetUserName);
375: inputs[3] = new JPasswordField(isSource ? sourcePassword
376: : targetPassword);
377: for (int i = 0; i < 4; i++)
378: labels[i].setHorizontalAlignment(JTextField.RIGHT);
379: JPanel panel = new JPanel();
380: GridLayout layout = new GridLayout(4, 2);
381: layout.setHgap(8);
382: panel.setLayout(layout);
383: panel.add(labels[0]);
384: panel.add(inputs[0]);
385: panel.add(labels[1]);
386: panel.add(inputs[1]);
387: panel.add(labels[2]);
388: panel.add(inputs[2]);
389: panel.add(labels[3]);
390: panel.add(inputs[3]);
391: panel.setPreferredSize(new Dimension(450, 104));
392: int rc = JOptionPane.showConfirmDialog(null, panel,
393: "Enter the connection parameters for the "
394: + (isSource ? "source" : "target")
395: + " database", JOptionPane.YES_NO_OPTION,
396: JOptionPane.INFORMATION_MESSAGE);
397: if (rc != JOptionPane.YES_OPTION)
398: return false;
399: if (isSource) {
400: sourceDriverName = inputs[0].getText();
401: sourceConnectionString = inputs[1].getText();
402: sourceUserName = inputs[2].getText();
403: sourcePassword = inputs[3].getText();
404: prefs.put("SourceDriver", sourceDriverName);
405: prefs.put("SourceUrl", sourceConnectionString);
406: prefs.put("SourceUserName", sourceUserName);
407: prefs.put("SourcePassword", sourcePassword);
408: } else {
409: targetDriverName = inputs[0].getText();
410: targetConnectionString = inputs[1].getText();
411: targetUserName = inputs[2].getText();
412: targetPassword = inputs[3].getText();
413: prefs.put("TargetDriver", targetDriverName);
414: prefs.put("TargetUrl", targetConnectionString);
415: prefs.put("TargetUserName", targetUserName);
416: prefs.put("TargetPassword", targetPassword);
417: }
418: return true;
419: }
420:
421: /**
422: * Gets a database connection
423: * @param driver the jdbc driver class name
424: * @param urlStr the url of the database
425: * @param userName the user name to use in making the connections
426: * @param password the connection password
427: */
428: public Connection getConnection(String driver, String urlStr,
429: String userName, String password) {
430: try {
431: Class.forName(driver.trim());
432: } catch (ClassNotFoundException e) {
433: System.out.println("Cannot get JDBC driver");
434: e.printStackTrace();
435: return null;
436: }
437:
438: try {
439: Connection conn = DriverManager.getConnection(urlStr,
440: userName, password);
441: System.out
442: .println("Succesfully connected to the database.");
443: return conn;
444: } catch (SQLException se) {
445: System.out
446: .println("Unable to make a connection to the master database, please check username, password and connection string.");
447: se.printStackTrace();
448: }
449: return null;
450: }
451: }
|