【Java操作表元数据备份数据】Select2Insert ---2011年代码

代码作用：通过java查询表的元数据，然后把查询出来的数据转为Insert语句输出到文件中进行备份。

改进：

      刚毕业那会代码写的比较乱，没设计模式和分模块思想，没按照C语言的函数功能完成编码，有待改进。

      数据量太多会内存溢出，可以采用数据库分页思想解决。关于数据库分页可以参考：

              http://gaojingsong.iteye.com/blog/2266950【数据库分页】

      表太多，备份太慢，可以通过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));

}

 

}

 

}