Test jdbc insert

package gs;

/*public class t {

public static void main(String[] args) {
// TODO Auto-generated method stub

}

}
导出insertsql语句*/
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

public class Test {
private static Connection conn = null;
private static Statement sm = null;
private static String schema = "FJSTL";// 模式名
private static String select = "SELECT * FROM";// 查询sql
private static String insert = "INSERT INTO";// 插入sql
private static String values = "VALUES";// values关键字
private static String[] table = { "T_USER" };// table数组
private static List<String> insertList = new ArrayList<String>();// 全局存放insertsql文件的数据
private static String filePath = "E://insertSQL.txt";// 绝对路径 导出数据的文件

/**
* 导出数据库表
*
* @param args
* @throws SQLException
*/
public static void main(String[] args) throws SQLException {
List<String> listSQL = new ArrayList<String>();
//Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
String url="jdbc:oracle:thin:@localhost:1521:orcl";//orcl为数据库的SID?
String user="system";
String password="orcl";
//Connection?conn=?DriverManager.getConnection(url,user,password);
connectSQL("oracle.jdbc.driver.OracleDriver", url, user, password);// 连接数据库
listSQL = createSQL();// 创建查询语句
executeSQL(conn, sm, listSQL);// 执行sql并拼装
createFile();// 创建文件
}

/**
* 创建insertsql.txt并导出数据
*/
private static void createFile() {
File file = new File(filePath);
if (!file.exists()) {
try {
file.createNewFile();
} catch (IOException e) {
System.out.println("创建文件名失败！！");
e.printStackTrace();
}
}
FileWriter fw = null;
BufferedWriter bw = null;
try {
fw = new FileWriter(file);
bw = new BufferedWriter(fw);
if (insertList.size() > 0) {
for (int i = 0; i < insertList.size(); i++) {
bw.append(insertList.get(i));
bw.append("\n");
}
}
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
bw.close();
fw.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}

/**
* 拼装查询语句
*
* @return 返回select集合
*/
private static List<String> createSQL() {
List<String> listSQL = new ArrayList<String>();
for (int i = 0; i < table.length; i++) {
StringBuffer sb = new StringBuffer();
sb.append(select).append(" ").append(schema).append(".")
.append(table[i]);
listSQL.add(sb.toString());
}
return listSQL;
}

/**
* 连接数据库 创建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 SQLException
*/
public static void executeSQL(Connection conn, Statement sm, List listSQL)
throws SQLException {
List<String> insertSQL = new ArrayList<String>();
ResultSet rs = null;
try {
rs = getColumnNameAndColumeValue(sm, listSQL, rs);
} catch (SQLException e) {
e.printStackTrace();
} finally {
rs.close();
sm.close();
conn.close();
}
}

/**
* 获取列名和列值
*
* @param sm
* @param listSQL
* @param rs
* @return
* @throws SQLException
*/
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) {
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("',");
} 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)) {
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 {
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)) {
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);
}
}
}
return rs;
}

/**
* 拼装insertsql 放到全局list里面
*
* @param ColumnName
* @param ColumnValue
*/
private static void insertSQL(StringBuffer ColumnName,
StringBuffer ColumnValue) {
for (int i = 0; i < table.length; i++) {
StringBuffer insertSQL = new StringBuffer();
insertSQL.append(insert).append(" ").append(schema).append(".")
.append(table[i]).append("(").append(ColumnName.toString())
.append(")").append(values).append("(")
.append(ColumnValue.toString()).append(");");
insertList.add(insertSQL.toString());
System.out.println(insertSQL.toString());
}
}
}