/**
* 连接数据库 创建statement对象
*
* @param driver
* @param url
* @param UserName
* @param Password
*/
public static void connectSQL(String driver, String url, String UserName, String Password) {
try {
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url, UserName, Password);
sm = conn.createStatement();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 执行sql并返回插入sql
*
* @param conn
* @param sm
* @param listSQL
* @throws java.sql.SQLException
*/
public static void executeSQL(Connection conn, Statement sm, List listSQL, List listTable) throws SQLException {
List<String> insertSQL = new ArrayList<String>();
ResultSet rs = null;
try {
rs = getColumnNameAndColumeValue(sm, listSQL, listTable, rs);
} catch (SQLException e) {
e.printStackTrace();
} finally {
rs.close();
sm.close();
conn.close();
}
}
/**
* 获取列名和列值
*
* @param sm
* @param listSQL
* @param rs
* @return
* @throws java.sql.SQLException
*/
private static ResultSet getColumnNameAndColumeValue(Statement sm,
List listSQL, List ListTable, ResultSet rs) throws SQLException {
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);
if (i == columnCount) {
ColumnName.append(rsmd.getColumnName(i));
if (Types.CHAR == rsmd.getColumnType(i) || Types.VARCHAR == rsmd.getColumnType(i)
|| Types.LONGVARCHAR == rsmd.getColumnType(i)) {
if (value == null) {
ColumnValue.append("null");
} else {
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)) {
if (value == null) {
ColumnValue.append("null");
} else {
ColumnValue.append(value);
}
} else if (Types.DATE == rsmd.getColumnType(i) || Types.TIME == rsmd.getColumnType(i)
|| Types.TIMESTAMP == rsmd.getColumnType(i)) {
if (value == null) {
ColumnValue.append("null");
} else {
ColumnValue.append("timestamp'").append(value).append("'");
}
} else {
if (value == null) {
ColumnValue.append("null");
} 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)) {
if (value == null) {
ColumnValue.append("null,");
} else {
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)) {
if (value == null) {
ColumnValue.append("null,");
} else {
ColumnValue.append(value).append(",");
}
} else if (Types.DATE == rsmd.getColumnType(i) || Types.TIME == rsmd.getColumnType(i)
|| Types.TIMESTAMP == rsmd.getColumnType(i)) {
if (value == null) {
ColumnValue.append("null,");
} else {
ColumnValue.append("timestamp'").append(value).append("',");
}
} else {
if (value == null) {
ColumnValue.append("null,");
} else {
ColumnValue.append(value).append(",");
}
}
}
}
//System.out.println(ColumnName.toString());
//System.out.println(ColumnValue.toString());
insertSQL(ListTable.get(j).toString(), ColumnName, ColumnValue);
}
}
return rs;
}