com.huawei.dataconvert.db.convert

原创于 2010-05-31 19:57:40 发布 · 112 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL #HSQLDB

本文介绍了一个用于将Oracle数据库中的表结构及数据迁移到HSQLDB的工具实现细节。该工具通过读取源Oracle数据库的信息，创建相应的表结构，并将数据导入目标HSQLDB数据库中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

public class OraToHsql implements DBtoDB
{

private HsqlDataBase hsqlDataBase = null;

public OraToHsql(DataBase hsqldb)
{
this.hsqlDataBase = (HsqlDataBase) hsqldb;
}

public void convertAllTable(DataBase sourceDB, Connection oraConn,
Connection hsqlConn, SqlRecord record) throws Exception
{
long time = System.currentTimeMillis();
// 建表并导入数据
System.out.println("创建表user:" + record.getUser() + " table:"
+ record.getTableName());
if (null == oraConn)
{
throw new Exception("ORA--00942 table : " + record.getTableName()
+ " not exist.");
}
// String theSql = getCreateTableStmt(oraConn, record);
TableInfo tableInfo = getCreateTableSQL(sourceDB, oraConn, record);
// System.out.println("get tableInfo");
insertTableInfo(tableInfo, hsqlConn);
// System.out.println("save tableInfo");
String theSql = tableInfo.getHsqldbCreatTableSQL();
// System.out.println(theSql);
// System.out.println("get table dll");
// System.out.println(user+" :: "+theSql);
Statement hsqlStmt = hsqlConn.createStatement();
// System.out.println(theSql);
try
{
hsqlStmt.execute(theSql);
} catch (Exception e)
{
System.out.println(theSql);
throw e;
}
hsqlStmt.close();
// hsqlStmt.close();

// // 添加主键
// String keySql = tableInfo.getHsqldbCreateKey();
// System.out.println(keySql);
// hsqlStmt.execute(keySql);
// hsqlStmt.close();

// 导入数据
convertData(oraConn, hsqlConn, record);
// break;
// 创建索引
convertIndex(oraConn, hsqlConn, record);
Constant.table_count++;
System.out.println("转换时间：" + (System.currentTimeMillis() - time) + " "
+ Constant.table_count);
}

private void insertTableInfo(TableInfo tableInfo, Connection conn)
throws SQLException
{
try
{
Statement stm = conn.createStatement();
String tableName = tableInfo.getTablename();
List<Column> cols = tableInfo.getColumns();
String front_sql = "INSERT INTO UBHAVE_TABLEINFO"
+ "( TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, COLUMN_LENGTH,"
+ " COLUMN_NULLABLE, COLUMN_SCALE, COLUMN_PRECISION )"
+ "VALUES ( '" + tableName + "', '";
// colname', 'coltype', collength,collnullable , colscale, colpreci)
for (Column col : cols)
{
String name = col.getColumn_name();
String type = col.getColumn_stype();
int length = col.getColumn_length();
int nullable = col.getColumn_nullable();
int scale = col.getColumn_data_scale();
int precis = col.getColumn_precision();
String sql = front_sql + name + "', '" + type + "', " + length
+ ", " + nullable + ", " + scale + ", " + precis + ")";
// System.out.println(sql);
stm.addBatch(sql);
}
stm.executeBatch();
stm.close();
} catch (SQLException e)
{
e.printStackTrace();
throw e;
}
}