java 生成sql脚本_java导出insert语句并生成sql脚本

insertSQL = new ArrayList();

ResultSet rs = null;

try {

rs = getColumnNameAndColumeValue(sm, listSQL, rs);

} catch (SQLException e) {

e.printStackTrace();

} finally {

rs.close();

sm.close();

conn.close();

}

}

/**

* 获取列名和列值

*

* @return

* @paramsm

* @paramlistSQL

* @paramrs

* @throwsSQLException

*/

private static ResultSet getColumnNameAndColumeValue(Statement sm, List listSQL, ResultSet rs) throws SQLException {

if (listSQL.size() > 0) {

for (int j = 0; j < listSQL.size(); j++) {

String sql = String.valueOf(listSQL.get(j));

rs = sm.executeQuery(sql);

ResultSetMetaData rsmd = rs.getMetaData();

int columnCount = rsmd.getColumnCount();

while (rs.next()) {

StringBuffer ColumnName = new StringBuffer();

StringBuffer ColumnValue = new StringBuffer();

for (int i = 1; i <= columnCount; i++) {

String value = rs.getString(i).trim();

if ("".equals(value)) {

value = "";

}

if (i == 1 || i == columnCount) {

if(i==columnCount){

ColumnName.append(",");

}

ColumnName.append(rsmd.getColumnName(i));

if( i== 1){

if (Types.CHAR == rsmd.getColumnType(i) || Types.VARCHAR == rsmd.getColumnType(i) || Types.LONGVARCHAR == rsmd.getColumnType(i)) {

ColumnValue.append("'").append(value).append("',");

} else if (Types.SMALLINT == rsmd.getColumnType(i) || Types.INTEGER == rsmd.getColumnType(i) || Types.BIGINT == rsmd.getColumnType(i) || Types.FLOAT == rsmd.getColumnType(i) || Types.DOUBLE == rsmd.getColumnType(i) || Types.NUMERIC == rsmd.getColumnType(i) || Types.DECIMAL == rsmd.getColumnType(i)|| Types.TINYINT == rsmd.getColumnType(i)) {

ColumnValue.append(value).append(",");

} else if (Types.DATE == rsmd.getColumnType(i) || Types.TIME == rsmd.getColumnType(i) || Types.TIMESTAMP == rsmd.getColumnType(i)) {

ColumnValue.append("timestamp'").append(value).append("',");

} else {

ColumnValue.append(value).append(",");

}

}else{

if (Types.CHAR == rsmd.getColumnType(i) || Types.VARCHAR == rsmd.getColumnType(i) || Types.LONGVARCHAR == rsmd.getColumnType(i)) {

ColumnValue.append("'").append(value);

} else if (Types.SMALLINT == rsmd.getColumnType(i) || Types.INTEGER == rsmd.getColumnType(i) || Types.BIGINT == rsmd.getColumnType(i) || Types.FLOAT == rsmd.getColumnType(i) || Types.DOUBLE == rsmd.getColumnType(i) || Types.NUMERIC == rsmd.getColumnType(i) || Types.DECIMAL == rsmd.getColumnType(i)|| Types.TINYINT == rsmd.getColumnType(i)) {

ColumnValue.append(value);

} else if (Types.DATE == rsmd.getColumnType(i) || Types.TIME == rsmd.getColumnType(i) || Types.TIMESTAMP == rsmd.getColumnType(i)) {

ColumnValue.append("timestamp'").append(value);

} else {

ColumnValue.append(value);

}

}

} else {

ColumnName.append("," + rsmd.getColumnName(i));

if (Types.CHAR == rsmd.getColumnType(i) || Types.VARCHAR == rsmd.getColumnType(i) || Types.LONGVARCHAR == rsmd.getColumnType(i)) {

ColumnValue.append("'").append(value).append("'").append(",");

} else if (Types.SMALLINT == rsmd.getColumnType(i) || Types.INTEGER == rsmd.getColumnType(i) || Types.BIGINT == rsmd.getColumnType(i) || Types.FLOAT == rsmd.getColumnType(i) || Types.DOUBLE == rsmd.getColumnType(i) || Types.NUMERIC == rsmd.getColumnType(i) || Types.DECIMAL == rsmd.getColumnType(i)|| Types.TINYINT == rsmd.getColumnType(i)) {

ColumnValue.append(value).append(",");

} else if (Types.DATE == rsmd.getColumnType(i) || Types.TIME == rsmd.getColumnType(i) || Types.TIMESTAMP == rsmd.getColumnType(i)) {

ColumnValue.append("timestamp'").append(value).append("',");

} else {

ColumnValue.append(value).append(",");

}

}

}

System.out.println(ColumnName.toString());

System.out.println(ColumnValue.toString());

insertSQL(ColumnName, ColumnValue,table[j]);

}

}

}

return rs;

}

/**

* 拼装insertsql放到全局list里面

* @paramColumnName

* @paramColumnValue

*/

private static void insertSQL(StringBuffer ColumnName, StringBuffer ColumnValue,String tableName) {

StringBuffer insertSQL = new StringBuffer();

insertSQL.append(insert).append(" ").append(schema).append(".")

.append(tableName).append("(").append(ColumnName.toString()).append(")").append(values).append("(").append(ColumnValue.toString()).append(");");

insertList.add(insertSQL.toString());

System.out.println(insertSQL.toString());

}

}