blazeDS +Spring 整合 小例子. (附带 ,Oracle 像EDB 表数据迁移小工具)

package DBTableDateMove; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import java.util.HashMap; import java.util.List; import java.util.Map; import DBDrive.OraclePostgreSQLDrive; public class OracleTableDataToPostgresqlTableData { private static int REMOVETOTAL = 0; private static String SQL_CONTENT(String ownerName ,String tableName){ return "SELECT t.COLUMN_NAME ,t.DATA_TYPE FROM all_tab_columns t WHERE owner = UPPER('"+ownerName+"') AND t.table_name = UPPER('"+tableName+"') ORDER BY t.COLUMN_ID"; } private static String SQL_TABLE_DATA(String tableName){ return "SELECT * FROM "+tableName; } private static String DELETE_TABLE_DATA(String tableName){ return "DELETE "+tableName; } private static String INSERTITEM(String tableName){ return "insert into "+tableName+"( "; } public static void main(String[] args) { List<String> tableNmaeList = OraclePostgreSQLDrive.getTableNmaeList(); /** * 追加需要导入数据表 **/ if(tableNmaeList!=null && tableNmaeList.size() == 0){ return; } for(int tableSize = 0 ; tableSize < tableNmaeList.size();tableSize++){ String COLUMNNAME = ""; String VALUEITEM = ") values ( "; String INSERTDETAIL = ""; int rowCount = 0; Map<String, String> map = new HashMap<String, String>(); int rowNum = 0; try { // Class.forName(OraclePostgreSQLDrive.PostgreSQL_DRIVER); Connection pconn = DriverManager.getConnection(OraclePostgreSQLDrive.Restore_PostgreSQL_URL, OraclePostgreSQLDrive.PostgreSQL_USERNAME, OraclePostgreSQLDrive.PostgreSQL_PASSWORD); PreparedStatement pstmt = null; Statement pdetelestmt = pconn.createStatement(); Class.forName(OraclePostgreSQLDrive.OracleSQL_DRIVER); Connection oconn = DriverManager.getConnection(OraclePostgreSQLDrive.Backup_OracleSQL_URL, OraclePostgreSQLDrive.OracleSQL_USERNAME, OraclePostgreSQLDrive.OracleSQL_PASSWORD); Statement ostmt = oconn.createStatement(); // System.out.println(SQL_CONTENT(OraclePostgreSQLDrive.OwnerName,tableNmaeList.get(tableSize))); ostmt.executeQuery(SQL_CONTENT(OraclePostgreSQLDrive.OwnerName,tableNmaeList.get(tableSize))); ResultSet resultSet = ostmt.getResultSet(); while (resultSet.next()) { String columnName = resultSet.getString(1); String dataType = resultSet.getString(2); if(resultSet.getRow() == 1){ COLUMNNAME = COLUMNNAME + columnName; VALUEITEM = VALUEITEM + "?"; }else{ COLUMNNAME = COLUMNNAME + ","+columnName; VALUEITEM = VALUEITEM + ", ?"; } rowNum++; map.put(Integer.toString(rowNum), dataType); } if(!resultSet.next()){ INSERTDETAIL = INSERTITEM(tableNmaeList.get(tableSize)) + COLUMNNAME + VALUEITEM + ")"; System.out.println(INSERTDETAIL); ostmt.executeQuery(SQL_TABLE_DATA(tableNmaeList.get(tableSize))); resultSet = ostmt.getResultSet(); pstmt = pconn.prepareStatement(INSERTDETAIL); while (resultSet.next()) { for(int i = 1; i<= rowNum ; i++){ String dataType = ""; dataType = map.get(Integer.toString(i)); if("VARCHAR2".equals(dataType)){ pstmt.setString(i, resultSet.getString(i)); }else if("DATE".equals(dataType)){ pstmt.setDate(i, resultSet.getDate(i)); }else if("NUMBER".equals(dataType)){ pstmt.setDouble(i, resultSet.getDouble(i)); }else{ System.out.println("==========="+dataType+" 类型缺少,请添加 ==========="); throw new Exception();//抛出异常 } } if(resultSet.getRow() == 1){ REMOVETOTAL = pdetelestmt.executeUpdate(DELETE_TABLE_DATA(tableNmaeList.get(tableSize))); System.out.println("============开始导入"+tableNmaeList.get(tableSize)+"表数据,删除原有数据"+Integer.toString(REMOVETOTAL)+"行============"); } pstmt.executeUpdate(); rowCount++; } if(!resultSet.next()){ System.out.println("============导入成功导入"+rowCount+"行记录============="); } } pstmt.close(); pconn.close(); ostmt.close(); oconn.close(); } catch (Exception e) { e.printStackTrace(); } } } }