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<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();
}
}
}
}
最新文章
- python基础(七)函数
- 《DSP using MATLAB》示例Example5.14
- 【linux】学习2
- java.io.stream
- [复变函数]第06堂课 2.1 解析函数的概念与 Cauchy-Riemann 方程 (续)
- 解决Ubuntu不能挂载ntfs格式硬盘
- 百度的domain命令到底有用吗?
- 面试题25:最小的K个数
- 6.2、Android Studio内存
- Python模块之信号(signal)
- POST提交数据之---Content-Type的理解;
- tmux 复制粘贴
- f5通过vs访问其他站点的源、目的IP问题
- 获取预制和获取gameObject
- Python 字符串处理(转)
- VS2013如何添加LIb库及头文件的步骤
- Python unittest第二篇:测试夹具
- Matlab 实现对码功能
- 基于kbengine 0.4.20
- freemarker的list指令小技术归纳
热门文章
- 60个有用CSS代码片段
- CSS特异性(CSS Specificity)的细节之CSS样式权重的计算与理解(CSS样式覆盖规则)
- 转:MVC 数据验证
- Magento Connector: Error: Please check for sufficient write file permissions
- ubuntu下的时间设定(硬件时间,系统时间,本地时间)
- android:descendantFocusability用法简析
- 003:Posix IPC的消息队列
- C# 去掉List重复元素的方法
- 探索性思维——How to Solve It
- Android开发新手学习总结(六)——android开发目录结构【图文版】