com.huawei.dataconvert.util

最新推荐文章于 2022-05-23 19:58:04 发布

原创最新推荐文章于 2022-05-23 19:58:04 发布 · 113 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle

本文介绍了一个实用的工具，该工具可以根据不同的数据库类型（如HSQL和Oracle）生成对应的SQL插入语句。通过解析表格信息和数据结果集，能够自动生成包含所有列的完整SQL插入语句。

public static int getColumnType(String typeName, String dbtype)
{
if ("hsql".equalsIgnoreCase(dbtype))
{
return hsqlDBMap.get(typeName);
} else if ("oracle".equalsIgnoreCase(dbtype))
{
return oracleMap.get(typeName);
}
return 0;
}

public static String getDataInsertSql(TableInfo tableInfo, ResultSet data)
throws Exception
{
String dbType = tableInfo.getDbType();
String tableName = tableInfo.getTablename();
List<Column> columns = tableInfo.getColumns();
Object value = null;
StringBuffer insertSql = new StringBuffer();
insertSql.append("insert into " + tableName + "(");
for (int i = 0; i < columns.size(); i++)
{
if (i != 0)
{
insertSql.append(",");
}
Column column = columns.get(i);
insertSql.append(column.getColumn_name());
}
insertSql.append(") values (");

for (int i = 0; i < columns.size(); i++)
{
if (i != 0)
{
insertSql.append(",");
}
Column column = columns.get(i);
insertSql.append(ColumnTypeMapping.getStringValue(data, column));
}

insertSql.append(")");

return insertSql.toString();
}

private static String getStringValue(ResultSet rs, Column column)
throws SQLException
{
int type = column.getColumn_type();
String columnName = column.getColumn_name();
System.out
.println("columnName:" + columnName + " columnType:" + type);
String value = "";
switch (type)
{
case 12:
value = getCharacterValue(rs.getString(columnName));
break;
case -5:
value = rs.getLong(columnName) + "";
break;
case 91:
value = getDate(rs.getDate(columnName), 91);
break;
case 92:
value = getDate(rs.getTime(columnName), 92);
break;
case 93:
value = getDate(rs.getTimestamp(columnName), 93);
break;
default:
value = rs.getString(columnName);
}
// System.out.println(columnName+" type:"+type);
// System.out.println("Value:"+value);
return value;
}

private static String getDate(Object date, int type)
{
String sdate = "NULL";
if (null != date)
{

Timestamp timestamp = (Timestamp) date;
System.out.println(date.toString());
System.out.println(timestamp.getTime());
sdate = "TO_TIMESTAMP('"
+ ColumnTypeMapping.dateFormat.format(timestamp)
+ "','YYYY-MM-DD HH24:MI:SS')";
}
return sdate;
}

private static String getCharacterValue(String string)
{
if (null == string)
{
return "NULL";
} else
{
return "'" + string + "'";
}
}