private static String driverName = "com.mysql.jdbc.Driver"; public static void main(String[] args) { Connection con = null ; Statement stmt =null; PreparedStatement pstmt = null ; try { Class.forName(driverName); con = DriverManager.getConnection("jdbc:mysql://192.168.5.148/xxx", "root", "123456"); String sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'ifms' AND TABLE_TYPE ='BASE TABLE' stmt = con.createStatement(); pstmt = con.prepareStatement(sql); ResultSet res = stmt.executeQuery(sql); while (res.next()) { String tableName = res.getString(1); /*stmt = con.createStatement();*/ if(tableName.contains("`")) continue; File file = new File("C:\\Users\\hq\\Desktop\\sql\\mysql\\a_tables\\spf_data\\"+tableName+"_data.sql"); OutputStreamWriter osw = null; try { osw = new OutputStreamWriter(new FileOutputStream(file)); ResultSet rs = pstmt.executeQuery("desc `"+tableName+"`"); StringBuffer sb = new StringBuffer(); List<String> list = new ArrayList<String>(); while(rs.next()){ sb.append("`"+rs.getString(1)+"`,"); list.add("`"+rs.getString(2)+"`"); } String str = sb.substring(0,sb.length()-1); //str+=")"; System.out.println("select "+str+" from `"+tableName+"`"); ResultSet ress = pstmt.executeQuery("select "+str+" from `"+tableName+"`" ); while(ress.next()){ StringBuffer stb = new StringBuffer(); for(int i = 0 ;i<list.size();i++){ if(list.get(i).toLowerCase().contains("int")||list.get(i).toLowerCase().contains("decimal")||list.get(i).toLowerCase().contains("float")||list.get(i).toLowerCase().contains("double")){ stb.append(ress.getString(i+1)+","); }else{ if(ress.getString(i+1)==null){ stb.append(ress.getString(i+1)+","); }else{ stb.append("'"+ress.getString(i+1)+"',"); } } } String subs = stb.substring(0,stb.length()-1); osw.write(" insert into `"+tableName+"`("+str+") values \n ("+subs+");\n"); } System.out.println(tableName+"导出成功"); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { try { osw.flush(); osw.close(); } catch (IOException e) { e.printStackTrace(); } } } } catch (ClassNotFoundException e) { e.printStackTrace(); System.exit(1); } catch (SQLException e) { e.printStackTrace(); }finally{ if(pstmt!=null){ try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if(stmt!=null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if(con!=null){ try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
转载于:https://www.cnblogs.com/sx2zx/p/6250008.html
