java类实现导出各种数据库语句 (2).doc

导出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;/查询sqlprivate static String insert=INSERT INTO;/插入sqlprivate 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;iinsertList.size();i+)bw.append(insertList.get(i);bw.append(n); catch (IOException e) e.printStackTrace();finallytry bw.close();fw.close(); catch (IOException e) e.printStackTrace();/* * 拼装查询语句 * return 返回select集合 */private static List createSQL() List listSQL=new ArrayList();for(int i=0;itable.length;i+)StringBuffer sb=new StringBuffer();sb.append(select).append( ).append(schema).append(.).append(tablei);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)tryClass.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 SQLExceptionList 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;jlistSQL.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(,);elseColumnValue.append(value).append(,);elseColumnName.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(,);elseColumnValue.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;itable.length;i+)StringBuffer insertSQL=new StringBuffer()