java类实现导出各种数据库insert语句

导出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 insertList=new ArrayList();//全局存放insertsql文件的数据

private static String filePath="E://insertSQL.txt";//绝对路径导出数据的文件

/**

*导出数据库表

*@param args

*@throws SQLException

*/

public static void main(String[] args) throws SQLException { List listSQL=new ArrayList();

connectSQL("oracle.jdbc.driver.OracleDriver", "xxx.xxx.xxx", "xxx", "xxx");//连接数据库

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

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 createSQL() {

List listSQL=new ArrayList();

for(int i=0;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 insertSQL=new ArrayList();

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

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

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());

}

}

}