databasemigration.java

一个java的ETL工具

									
									boolean conversionSuccessful = false;
									
									try
									{
										if (!fromId_type.equals("BLOB") && (toId_type.equals("CHAR") || toId_type.equals("VARCHAR") || toId_type.equals("LONGVARCHAR")))
										{
											convertObjectAfter = (String)convertObjectBefore.toString();
											conversionSuccessful = true;
	System.out.println("ToStringConversionResult = " + convertObjectAfter);
										}	

										if ((fromId_type.equals("CHAR") || fromId_type.equals("VARCHAR") || fromId_type.equals("LONGVARCHAR")) && (toId_type.equals("DATE") || toId_type.equals("TIME") || (toId_type.equals("TIMESTAMP") || toId_type.equals("DATETIME"))))
										{
											int year = -1;
											int month = -1;
											int day = -1;
											int hour = -1;
											int minute = -1;
											int second = -1;
											
											if (toId_type.equals("DATE"))  //  required format = YYYY-MM-DD
											{
												int[] ints = Tools.convertStringToDateInts(convertObjectBefore.toString());
												
												if (ints[0] == 1)
												{
													year = ints[1];
													month = ints[2];
													day = ints[3];
													
	System.out.println("Month = " + month);
	System.out.println("Day = " + day);
	System.out.println("Year = " + year);
													GregorianCalendar gc = new GregorianCalendar(year, month-1, day, 0, 0, 0);  // month is 0-11 in java
													convertObjectAfter = new Date(gc.getTime().getTime());
	System.out.println(convertObjectAfter);
			
													conversionSuccessful = true;
												}	
											}
											else if (toId_type.equals("TIME"))  // required format = HH:MM:SS
											{
												int[] ints = Tools.convertStringToTimeInts(convertObjectBefore.toString());

												if (ints[0] == 1) 	
												{
													hour = ints[1];
													minute = ints[2];
													second = ints[3];
													
													GregorianCalendar gc = new GregorianCalendar(0, 0, 1, hour, minute, second);  // month is 0-11 in java
													convertObjectAfter = new Time(gc.getTime().getTime());
			
													conversionSuccessful = true;
												}	
											}
											else if (toId_type.equals("TIMESTAMP") || toId_type.equals("DATETIME"))  // required format = YYYY-MM-DD HH:MM:SS
											{
												int[] ints = Tools.convertStringToDatetimeInts(convertObjectBefore.toString());

												if (ints[0] == 1) 	
												{
													year = ints[1];
													month = ints[2];
													day = ints[3];
													hour = ints[4];
													minute = ints[5];
													second = ints[6];
													
													GregorianCalendar gc = new GregorianCalendar(year, month-1, day, hour, minute, second);  // month is 0-11 in java
													convertObjectAfter = new Timestamp(gc.getTime().getTime());
			
													conversionSuccessful = true;
												}	
											}
										}	

										if (fromId_type.equals("CHAR") && toId_type.equals("VARCHAR"))
										{
											convertObjectAfter = (String)convertObjectBefore.toString();
											conversionSuccessful = true;
										}
										
										if (fromId_type.equals("VARCHAR") && toId_type.equals("LONGVARCHAR"))
										{
											convertObjectAfter = (String)convertObjectBefore.toString();
											conversionSuccessful = true;
										}
										
										if (fromId_type.equals("CHAR") && toId_type.equals("LONGVARCHAR"))
										{
											convertObjectAfter = (String)convertObjectBefore.toString();
											conversionSuccessful = true;
										}
										
										if (!fromId_type.endsWith("CHAR") && toId_type.equals("BIT"))
										{
											convertObjectAfter = new Boolean(convertObjectBefore.toString());	
											conversionSuccessful = true;
										}
										
										if (!fromId_type.endsWith("CHAR") && toId_type.equals("TINYINT"))
										{
											convertObjectAfter = new Byte(convertObjectBefore.toString());	
											conversionSuccessful = true;
										}
										
										if (!fromId_type.endsWith("CHAR") && toId_type.equals("SMALLINT"))
										{
											convertObjectAfter = new Short(convertObjectBefore.toString());	
											conversionSuccessful = true;
										}
										
										if (!fromId_type.endsWith("CHAR") && toId_type.equals("INTEGER"))
										{
											convertObjectAfter = new Integer(convertObjectBefore.toString());	
											conversionSuccessful = true;
										}
										
										if (!fromId_type.endsWith("CHAR") && toId_type.equals("BIGINT"))
										{
											convertObjectAfter = new Long(convertObjectBefore.toString());	
											conversionSuccessful = true;
										}
										
										if (!fromId_type.endsWith("CHAR") && toId_type.equals("REAL"))
										{
											convertObjectAfter = new Float(convertObjectBefore.toString());	
											conversionSuccessful = true;
										}
										
										if (!fromId_type.endsWith("CHAR") && toId_type.equals("DOUBLE"))
										{
											convertObjectAfter = new Double(convertObjectBefore.toString());	
											conversionSuccessful = true;
										}
									}
									catch (Exception e)
									{
										System.out.println("Error: " + e); 
										conversionSuccessful = false;
									}	
									
System.out.println("Conversion Successful = " + conversionSuccessful);

										// push data

									if (conversionSuccessful)	
									{
										if (toId_ht.equals("source"))
											sourceValues_HT.put(toId, convertObjectAfter);
										else if (toId_ht.equals("target"))
											targetValues_HT.put(toId, convertObjectAfter);
										else if (toId_ht.equals("temporary"))
											temporaryValues_HT.put(toId, convertObjectAfter);
									}
								}	
								else
								{
									System.out.println("Bad Field ID");
								}
							}	
							else
							{
								System.out.println("Conversion FROM " + fromId_type + " TO " + toId_type + " NOT supported");		
							}	
						}	
					}
				}  // next INSERT_ROW_TRANSFER			

				if (doTransfer == false || operation.equals("TEST"))
				{
					System.out.println("Transfer NOT Attempted");
				}
				else
				{																		
					// replace "?" with actual values for INSERT and UPDATE PreparedStatements
					
					PreparedStatement targetStatement_UPDATE = null;
					String targetUPDATE = migration.createTargetSQL_UPDATE(targetValues_HT);
System.out.println("Target Fallback UPDATE SQL:  " + targetUPDATE);
					if (!targetUPDATE.equals("")) targetStatement_UPDATE = targetConnection.prepareStatement(targetUPDATE);
		
					System.out.println("Build Target Prepared Statements");

					int u = -1;  // Update PreparedStatement Field Counter

					for (int j=0; j<targetFields_AL.size(); j++)
					{
						Field field = (Field)targetFields_AL.get(j);
		
						String id = field.getId();
						String name = field.getNameSQL();
						String type = field.getTypeSQL();

						boolean updatePossible = false;
						
						if (targetUPDATE.indexOf(name) >= 0) 
						{
							updatePossible = true;
							u++;
						}	
							

						Object value = targetValues_HT.get(id);
						
						if (type.equals("BIT"))
						{
							if (value == null)
							{														
								targetStatement_INSERT.setNull(j+1,Types.BIT);
								if (updatePossible) targetStatement_UPDATE.setNull(u+1,Types.BIT);
							}
							else
							{
								targetStatement_INSERT.setBoolean(j+1,((Boolean)value).booleanValue());
								if (updatePossible) targetStatement_UPDATE.setBoolean(u+1,((Boolean)value).booleanValue());
							}	
						}
						else if (type.equals("TINYINT"))
						{
							if (value == null)
							{														
								targetStatement_INSERT.setNull(j+1,Types.TINYINT);
								if (updatePossible) targetStatement_UPDATE.setNull(u+1,Types.TINYINT);
							}
							else
							{
								targetStatement_INSERT.setByte(j+1,((Byte)value).byteValue());
								if (updatePossible) targetStatement_UPDATE.setByte(u+1,((Byte)value).byteValue());
							}	
						}
						else if (type.equals("SMALLINT"))
						{
							if (value == null)
							{														
								targetStatement_INSERT.setNull(j+1,Types.SMALLINT);
								if (updatePossible) targetStatement_UPDATE.setNull(u+1,Types.SMALLINT);
							}
							else
							{
								targetStatement_INSERT.setShort(j+1,((Short)value).shortValue());
								if (updatePossible) targetStatement_UPDATE.setShort(u+1,((Short)value).shortValue());
							}	
						}
						else if (type.equals("INTEGER"))
						{
							if (value == null)
							{														
								targetStatement_INSERT.setNull(j+1,Types.INTEGER);
								if (updatePossible) targetStatement_UPDATE.setNull(u+1,Types.INTEGER);
							}
							else
							{
								targetStatement_INSERT.setInt(j+1,((Integer)value).intValue());
								if (updatePossible) targetStatement_UPDATE.setInt(u+1,((Integer)value).intValue());
							}	
						}
						else if (type.equals("BIGINT"))
						{
							if (value == null)
							{														
								targetStatement_INSERT.setNull(j+1,Types.BIGINT);
								if (updatePossible) targetStatement_UPDATE.setNull(u+1,Types.BIGINT);
							}
							else
							{
								targetStatement_INSERT.setLong(j+1,((Long)value).longValue());	
								if (updatePossible) targetStatement_UPDATE.setLong(u+1,((Long)value).longValue());
							}	
						}
						else if (type.equals("REAL"))
						{
							if (value == null)
							{														
								targetStatement_INSERT.setNull(j+1,Types.REAL);
								if (updatePossible) targetStatement_UPDATE.setNull(u+1,Types.REAL);
							}
							else
							{
								targetStatement_INSERT.setFloat(j+1,((Float)value).floatValue());
								if (updatePossible) targetStatement_UPDATE.setFloat(u+1,((Float)value).floatValue());
							}	
						}
						else if (type.equals("DOUBLE"))
						{
							if (value == null)
							{														
								targetStatement_INSERT.setNull(j+1,Types.DOUBLE);
								if (updatePossible) targetStatement_UPDATE.setNull(u+1,Types.DOUBLE);
							}
							else
							{
								targetStatement_INSERT.setDouble(j+1,((Double)value).doubleValue());
								if (updatePossible) targetStatement_UPDATE.setDouble(u+1,((Double)value).doubleValue());
							}	
						}
						else if (type.equals("CHAR"))
						{
							if (value == null)
							{														
								targetStatement_INSERT.setNull(j+1,Types.CHAR);
								if (updatePossible) targetStatement_UPDATE.setNull(u+1,Types.CHAR);
							}
							else
							{
								targetStatement_INSERT.setString(j+1,(String)value);
								if (updatePossible) targetStatement_UPDATE.setString(u+1,(String)value);
							}	
						}
						else if (type.equals("VARCHAR"))
						{
							if (value == null)
							{														
								targetStatement_INSERT.setNull(j+1,Types.VARCHAR);
								if (updatePossible) targetStatement_UPDATE.setNull(u+1,Types.VARCHAR);
							}
							else
							{
								targetStatement_INSERT.setString(j+1,(String)value);
								if (updatePossible) targetStatement_UPDATE.setString(u+1,(String)value);
							}	
						}
						else if (type.equals("LONGVARCHAR"))
						{
							if (value == null)
							{														
								targetStatement_INSERT.setNull(j+1,Types.LONGVARCHAR);
								if (updatePossible) targetStatement_UPDATE.setNull(u+1,Types.LONGVARCHAR);
							}
							else
							{
								targetStatement_INSERT.setString(j+1,(String)value);
								if (updatePossible) targetStatement_UPDATE.setString(u+1,(String)value);
							}	
						}
						else if (type.equals("DATE"))
						{
							if (value == null)
							{														
								targetStatement_INSERT.setNull(j+1,Types.DATE);
								if (updatePossible) targetStatement_UPDATE.setNull(u+1,Types.DATE);
							}
							else
							{
								targetStatement_INSERT.setDate(j+1,(Date)value);
								if (updatePossible) targetStatement_UPDATE.setDate(u+1,(Date)value);
							}	
						}
						else if (type.equals("TIME"))
						{
							if (value == null)
							{														
								targetStatement_INSERT.setNull(j+1,Types.TIME);
								if (updatePossible) targetStatement_UPDATE.setNull(u+1,Types.TIME);
							}
							else
							{
								targetStatement_INSERT.setTime(j+1,(Time)value);
								if (updatePossible) targetStatement_UPDATE.setTime(u+1,(Time)value);
							}	
						}
						else if (type.equals("TIMESTAMP") || type.equals("DATETIME"))
						{
							if (value == null)
							{														
								targetStatement_INSERT.setNull(j+1,Types.TIMESTAMP);
								if (updatePossible) targetStatement_UPDATE.setNull(u+1,Types.TIMESTAMP);
							}
							else
							{
								targetStatement_INSERT.setTimestamp(j+1,(Timestamp)value);
								if (updatePossible) targetStatement_UPDATE.setTimestamp(u+1,(Timestamp)value);
							}	
						}
						else if (type.equals("BLOB"))
						{
							if (value == null)
							{														
								targetStatement_INSERT.setNull(j+1,Types.BLOB);
								if (updatePossible) targetStatement_UPDATE.setNull(u+1,Types.BLOB);
							}
							else
							{
								targetStatement_INSERT.setObject(j+1,value);
								if (updatePossible) targetStatement_UPDATE.setObject(u+1,value);
							}	
						}
					}

					//  Persist Data changes to Target Table
					
					int rowsInserted = targetStatement_INSERT.executeUpdate();
					if (rowsInserted == 0)
					{
						int rowsUpdated = targetStatement_UPDATE.executeUpdate();
						if (rowsUpdated == 0)
							System.out.println("Can't UPDATE or INSERT into target Table");
						else	
							System.out.println(rowsUpdated + " row(s) UPDATED successfully");
					}
					else
					{
						System.out.println(rowsInserted + " row(s) INSERTED successfully");
					}		

					// reset PreparedStatements for next row
					
					targetStatement_INSERT.clearParameters();
				}	
			}  //  next ResultSet

			sourceRS.close();
		}  // next TABLE_MIGRATION
	}
}