使用jdbc获取数据生成新的insert sql文本

以oracle数据库为列：

1、pom文件配置

 
		
		UTF-8
		1.8
	
	
		
			oracle
			ojdbc5
			11g
		
	
	
		oa-dts
		
		
			
			
				org.apache.maven.plugins
				maven-compiler-plugin
				
					
						compile
						
							compile
						
						
						
					
				
				
					${jdk.version}
					${jdk.version}
				
			
			
			
				org.apache.maven.plugins
				maven-resources-plugin
				2.6
				
					UTF-8
				
			
			
				maven-war-plugin
				2.1.1
				
					src/main/webapp
				
			
		
	

2、配置文件config.properties

DBServer=192.168.1.251
DBPort=1520
DBName=KNBM
DBUser=KNBM
DBPsw=knbm
ExportPath=D:/CreateSql/

3、使用Java获取配置文件

package com.sanwei;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

public class PropertiesConfig {

	public String getConfig(String config) {
         //获取配置文件
		InputStream inStream = this.getClass().getClassLoader().getResourceAsStream("config.properties");
		Properties p = new Properties();
		try {
			p.load(inStream);

		} catch (IOException e) {
			e.printStackTrace();
		}
		return p.getProperty(config);
	}

}

4、使用jdbc读取数据，写入磁盘

package com.sanwei;

import java.io.File;
import java.io.FileOutputStream;
import java.io.PrintStream;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class OracleDB {
	private static Connection conn = null;
	static PropertiesConfig pc=new PropertiesConfig();
	
	private static Connection getConnection() {
		//Connection conn = null;
		//PropertiesConfig pc=new PropertiesConfig();
		String serverIP=pc.getConfig("DBServer");
		String port=pc.getConfig("DBPort");
		String db=pc.getConfig("DBName");
		String user=pc.getConfig("DBUser");
		String pass=pc.getConfig("DBPsw");
		File f=new File(pc.getConfig("ExportPath"));
		if(!f.exists()){
			f.mkdir();
		}
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@"+serverIP+":"+port+":"+db;
			conn = DriverManager.getConnection(url, user, pass);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	public static List CreateSql(String newTabName) throws Exception{
		if(conn==null||conn.isClosed()){
			getConnection();
		}
		//过滤数据表，仅保留当前数据库下的数据表
		String filePath=pc.getConfig("ExportPath")+"sendfilesql.sql";
		
		String sql="select * from sendfile";
		PreparedStatement stmt;
		stmt = conn.prepareStatement(sql);
		ResultSet rs = stmt.executeQuery(sql);
		List list=new ArrayList();
		String sq="";
		int i=1;
		File file = new File(filePath);
        PrintStream ps = new PrintStream(new FileOutputStream(file));
		ps.print("-----start.sendfile------n");
		while (rs.next()) {
			sq="insert into "+newTabName+" values(";
			sq+=rs.getInt(1)+",";
			for(int j=2;j<=5;j++){
				Clob clob = rs.getClob(j);//Java.sql.Clob
				String detailinfo = "";
				if(clob != null){
				   detailinfo = clob.getSubString((long)1,(int)clob.length());
				   sq+="'"+detailinfo+"',";
				}else{
					 sq+="null,";
				}
			}
			for(int j=6;j<=23;j++){
				if(j!=17&&j!=18){
					String detailinfo=rs.getString(j);
					if(detailinfo != null){
						sq+="'"+detailinfo+"',";
					}else{
						sq+="null,";
					}
				}else if(j==17){
					sq+=rs.getInt(j)+",";
				}else if(j==18){
					if(rs.getDate(j)==null){
						sq+="null,";
					}else{
						sq+="'"+rs.getDate(j)+"',";
					}
					
				}
			}
			Clob clob = rs.getClob(24);//Java.sql.Clob
			String detailinfo = "";
			if(clob != null){
			   detailinfo = clob.getSubString((long)1,(int)clob.length());
			   sq+="'"+detailinfo+"');";
			}else{
				sq+="null);";
			}
			
			//System.out.println("--"+i+"--");
			//System.out.println(sq);
			i++;
			ps.append(sq+"n");
			list.add(sq);
		}
		rs.close();
		conn.close();
		i=i-1;
		ps.append("-----end。共计"+i+"条数据------n");
		return list;
	}
}

5、进行测试

package com.sanwei;

public class test {

	public static void main(String[] args) throws Exception {
		System.out.println("--start--");
		OracleDB.CreateSql("dts_sendfile");
		//OracleDB.CreateRecSql("dts_recmsg");
		//OracleDB.CreateSecpcSql("T_SECPC");
		//OracleDB.CreateUnitdzSql("T_UNIT_DZ");
		System.out.println("--end--");
	}

}