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

以oracle数据库为列：

1、pom文件配置

 <properties>
		<!-- 编译编码方式 -->
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<jdk.version>1.8</jdk.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>oracle</groupId>
			<artifactId>ojdbc5</artifactId>
			<version>11g</version>
		</dependency>
	</dependencies>
	<build>
		<finalName>oa-dts</finalName>
		<!-- 插件 -->
		<plugins>
			<!-- 设置编译jdk版本插件 -->
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<executions>
					<execution>
						<phase>compile</phase>
						<goals>
							<goal>compile</goal>
						</goals>
						<configuration>
						</configuration>
					</execution>
				</executions>
				<configuration>
					<source>${jdk.version}</source>
					<target>${jdk.version}</target>
				</configuration>
			</plugin>
			<!-- 编码设置 -->
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-resources-plugin</artifactId>
				<version>2.6</version>
				<configuration>
					<encoding>UTF-8</encoding>
				</configuration>
			</plugin>
			<plugin>
				<artifactId>maven-war-plugin</artifactId>
				<version>2.1.1</version>
				<configuration>
					<warSourceDirectory>src/main/webapp</warSourceDirectory>
				</configuration>
			</plugin>
		</plugins>
	</build>

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();
	/**
	 * 连接数据库
	 * @return
	 */
	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;
	}
	/***
	 * 对表数据进行处理生成新的sql文件
	 * @param newTabName
	 * @return
	 * @throws Exception
	 */
	public static List<String> 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<String> list=new ArrayList<String>();
		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--");
	}

}