com.huawei.dataconvert.db

最新推荐文章于 2010-05-31 20:17:05 发布

原创最新推荐文章于 2010-05-31 20:17:05 发布 · 87 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL #HSQLDB

本文介绍了一个用于生成HSQLDB数据库表结构DDL(SQL定义语言)的方法。该方法通过输入表信息对象，遍历其所有列并转换为对应的HSQLDB数据类型，同时处理主键约束，最终形成完整的表定义语句。

public static String getHQLTableDdlSQL(TableInfo tableInfo)
{
StringBuffer resultSql = new StringBuffer();

// System.out.println(user+" :: "+tabName);

// HSQLDB不支持对象名中带$，将其替换为_S_
String tableName = tableInfo.getTablename();
// tableName = tableName.replaceAll("\\$","_S_");
resultSql.append("create cached table " + tableName + "\n(\n");

int colIndex = 0;
List<Column> cols = tableInfo.getColumns();
for (Column col : cols)
{

// 少量有问题的数据类型，要跳过去
// if (col.getColumn_stype().equals("NVARCHAR2")
// ||col.getColumn_name().equals("COUNT")) {
// System.out.println("跳过："+col.getColumn_stype());
// continue;
// }

if (0 != colIndex)
{
resultSql.append(",\n");
}

resultSql.append(" \"" + col.getColumn_name()).append("\" ");

String typeValue = HsqlDataBase.getTypeString(col);

resultSql.append(typeValue);

// 是否可以为NULL
if (col.getColumn_nullable() == 0)
{
resultSql.append(" NOT NULL");
}

colIndex++;
}
if (null != tableInfo.getPri_key())
{
resultSql.append(",\n");
resultSql.append("CONSTRAINT "
+ tableInfo.getPri_key().getKeyName() + " PRIMARY KEY (");
List<String> kcols = tableInfo.getPri_key().getCols();
int ii = 0;
for (String col : kcols)
{
if (ii != 0)
resultSql.append(",");
resultSql.append("\"");
resultSql.append(col).append("\"");

ii++;
}
resultSql.append(")");
}
resultSql.append("\n);\n");
return resultSql.toString();
}

@Override
public Primary_Key getPrimaryKey(SqlRecord record) throws SQLException
{
String user = record.getUser().toUpperCase();
String sql = "SELECT A.CONSTRAINT_NAME,A.COLUMN_NAME FROM USER_CONS_COLUMNS A,USER_CONSTRAINTS B "
+ "WHERE A.TABLE_NAME = '"
+ record.getTableName()
+ "' AND"
+ " B.CONSTRAINT_TYPE = 'P' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.OWNER='"
+ user + "'";

Connection conn = this.connMap.get(user);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
Primary_Key priKey = new Primary_Key(record.getTableName());
if (record.getTableName().equalsIgnoreCase("DC_SUB_LIMIT"))
{
System.out.println("");
}
while (rs.next())
{
if ("" == priKey.getKeyName())
{
String keyName = rs.getString(1);
if (keyName.contains(Constant.SPECIAL_CHAR_$))
{
keyName = keyName.replace(Constant.SPECIAL_CHAR_$,
Constant.SPECIAL_CHAR_R$);
}
priKey.setKeyName(keyName);
}
String colName = rs.getString(2);
priKey.getCols().add(colName);
}
if (null == priKey.getKeyName() | priKey.getKeyName().equals(""))
{
stmt.close();
rs.close();
return null;
}
stmt.close();
rs.close();
return priKey;
}