代码作用：通过java查询表的元数据，然后把查询出来的数据转为Insert语句输出到文件中进行备份。
改进：
? ? ? 刚毕业那会代码写的比较乱，没设计模式和分模块思想，没按照C语言的函数功能完成编码，有待改进。
? ? ? 数据量太多会内存溢出，可以采用数据库分页思想解决。关于数据库分页可以参考：
? ? ? 表太多，备份太慢，可以通过java多线程思想解决。
?
package com.nasoft.test;
?
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.PrintStream;
import java.io.Writer;
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.util.ArrayList;
import java.util.List;
?
/**
?* @把一个表的Select语句转化为Insert语句，做备份使用
?*/
public class TestInsert {
?
//public static final String DRIVER="com.mysql.jdbc.Driver";
//
//public static final String URL="jdbc:mysql://localhost:3306/test";
//
//public static final String USERNAME="root";
//
//public static final String PASSWORD="123";
?
//public static final String DRIVER="oracle.jdbc.driver.OracleDriver";
//
//public static final String URL="jdbc:oracle:thin:@localhost:1521:ORCL";
//
//public static final String USERNAME="scott";
//
//public static final String PASSWORD="scottoracle";
?
public static final String DRIVER="com.microsoft.sqlserver.jdbc.SQLServerDriver";
?
public static final String URL="jdbc:sqlserver://localhost:1433;DatabaseName=cibcdb";
?
public static final String USERNAME="sa";
?
public static final String PASSWORD="sql";
?
public Connection ?getConn(){
Connection conn = null;
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}catch (SQLException e) {
e.printStackTrace();
}
return conn; //The local variable conn may not have been initialized:局部变量的使用必须初始化
}
?
public void init(String tableName){
Connection conn = this.getConn();
String sql ="select top 10 * from "+tableName;
try {
Statement stm = conn.createStatement();
ResultSet rs = stm.executeQuery(sql);
ResultSetMetaData rmd = rs.getMetaData();
int columncount = rmd.getColumnCount(); //得到列的数量
//
// ? ?System.out.println(rmd.getCatalogName(1)); // 得到数据库的名称
//rmd.getColumnClassName(1);
//System.out.println(rmd.getTableName(1)); //得到表的名字
?
rmd.getColumnType(1); //获取指定列的 SQL 类型
System.out.println(rmd.getColumnType(1));
rmd.getColumnTypeName(1);
//System.out.println(rmd.getColumnTypeName(1)); //CHAR:字段对应的数据库中类型
//System.out.println(rmd.getColumnClassName(3));
? ? ? ? ? ? ? ? ? ? ? ? //java.lang.String：字段对应的Java 数据类型
//stm.executeQuery(sql)
?
?
StringBuffer sb1 = ?new StringBuffer();
for(int k=1 ;k <= columncount ;k++){
//String columntype = rmd.getColumnClassName(k);
String columnName = rmd.getColumnLabel(k);
//System.out.println(columntype + "---" +columnName);
if(k < columncount){
sb1.append(columnName+",");
}else{
sb1.append(columnName+") values(");
}
}
while(rs.next()){
StringBuffer sb = ?new StringBuffer();
String insertsql="insert into "+tableName+"(";
sb.append(insertsql).append(sb1);
?
for(int i=1 ;i <=columncount ;i++){
String columntype = rmd.getColumnClassName(i);
if(i < columncount){
if(columntype.contains("Integer")){ //如果数字类型
sb.append(rs.getObject(i)+",");
}?
else?
{
String str= (String) rs.getObject(i);
if(null ==str || "".equals(str)){
sb.append("‘‘,");
}else {
sb.append("‘"+str.trim()+"‘,");
}
}
}else{
if(columntype.contains("Integer")){ //如果数字类型
sb.append(rs.getObject(i));
}?
else?
{
String str= (String) rs.getObject(i);
if(null ==str || "".equals(str)){
sb.append("‘‘,");
}else {
sb.append("‘"+str.trim()+"‘,");
}
}
}
}
//System.out.println( sb.toString()+");");
?
ExportTOFile(tableName,sb.toString()+");");
?
} catch (SQLException e) {
e.printStackTrace();
}
}
?
public void ExportTOFile001(String tableName,String sql){
File file = new File("D:/"+tableName+".sql");
FileOutputStream out = null;
try {
out = new FileOutputStream(file,true);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
PrintStream pin = new PrintStream(out);
//System.setOut(pin.println());
pin.println(sql);
pin.close();
}
?
public void ExportTOFile(String tableName,String sql){
File file = new File("D:/"+tableName+".sql");
FileOutputStream out = null;
try {
out = new FileOutputStream(file,true);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Writer wr = new OutputStreamWriter(out);
try {
wr.write(sql+"\n");
wr.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
?
}
?
public List<String> ?getTableName() throws Exception{
List list = new ArrayList();
Connection conn = this.getConn();
//String sql ="show tables"; ? ?MYSQL
String sql ="select * from tab"; ?//ORACLE
sql ="sp_help"; ?//SQL Server
Statement stm = conn.createStatement();
ResultSet rs = stm.executeQuery(sql);
while(rs.next()){
String tabname= rs.getString(1);
String type = rs.getString(3);
if("user table".equalsIgnoreCase(type)){
list.add(tabname);
}
}
?
return list;
}
?
?
public static void main(String[] args) throws Exception {
//tb_user
TestInsert tt = new TestInsert();
//tt.init("tb_user");
?
List<String> list = tt.getTableName();
for (int i = 0; i < list.size(); i++) {
//System.out.println(list.get(i));
tt.init(list.get(i));
}
?
}
?
}
?
【Java操作表元数据备份数据】Select2Insert ---2011年代码
原文：http://gaojingsong.iteye.com/blog/2279193