mysql分别导出所有表_mysql 导出批量导出表数据 (程序)

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 list = new ArrayList();

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

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

}

}

}

}