公司因一招标项目,对方要求使用pgsql数据库,在这里将遇到的问题和解决问题的办法记录一下
一、整个数据库表转换
首先我们做的是将整个oracle数据库转换为可执行的pgsql数据库执行语句,主要是因为pgsql的数据类型与oracle大不相同,从oracle直接导出的执行语句根本无法在pgsql数据库执行。我们是使用java程序生成pgsql建表建库脚本,下面贴出一部分代码:
StringBuffer dropSb = new StringBuffer();
dropSb.append("-- DROP table \n");
dropSb.append("DROP TABLE IF EXISTS "+pgUser+table+";\n");
dropTable.write(dropSb.toString());
StringBuffer createSb = new StringBuffer();
createSb.append("-- Create table \n");
createSb.append("create table if not exists "+pgUser+table+"(\n");
StringBuffer commentsSb = new StringBuffer();
// 查询表信息
Table tableInfo = iDataInfoDao1.findTableInfo(table);
if (!StringUtils.isEmpty(tableInfo.getComments())) {
// 表注释
commentsSb.append("-- Add comments to the table \n");
commentsSb.append("COMMENT ON TABLE "+pgUser+table+" IS '"+tableInfo.getComments()+"';\n");
}
// 约束相关信息
StringBuffer constraintSb = new StringBuffer();
constraintSb.append("-- Create/Recreate primary, unique and foreign key constraints \n");
List<Constraint> listConstraint = iDataInfoDao1.findTableConstraint(table);
for (Constraint constraint: listConstraint) {
if ("P".equals(constraint.getConstraintType())) {
constraintSb.append("alter table "+pgUser+table+" add constraint "+constraint.getConstraintName()+" primary key ("
+constraint.getColumnName()+");\n");
} else if ("R".equals(constraint.getConstraintType())) {
fConstraintSb.append("-- Create/Recreate foreign key constraints \n");
fConstraintSb.append("alter table "+pgUser+table+" add constraint "+constraint.getConstraintName()+" foreign key ("
+constraint.getColumnName()+")\n");
fConstraintSb.append("references "+constraint.getrTableName()+" ("+constraint.getrColumnName()+");\n");
fConstraintSb.append("\n");
fConstraintSb.append("\n");
} else if ("U".equals(constraint.getConstraintType())) {
constraintSb.append("alter table "+pgUser+table+" add constraint "+constraint.getConstraintName()+" unique ("
+constraint.getColumnName()+");\n");
}
}
// 索引相关信息
StringBuffer indexSb = new StringBuffer();
indexSb.append("-- Create/Recreate indexes \n");
List<Index> listIndex = iDataInfoDao1.findTableIndex(table);
for (Index index: listIndex) {
if ("UNIQUE".equals(index.getUniqueness())) {
indexSb.append("cre