java类实现导出各种数据库insert语句
程序员文章站
2022-03-17 08:59:01
...
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>(); 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<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()); } } }