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