20161027 sql文件下载（主从表sql文件）

本文是sql文件下载，主从表都可以下载

调用方法：

download.DownloadSql(jdbcTemplate,主表中要下载的所有主键id, 主表表名, 所有从表表名, 从表中与主表关联的列名,response);

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.jeecgframework.core.util.ResourceUtil;
import org.jeecgframework.web.cgform.service.migrate.MigrateForm;
import org.jeecgframework.web.cgform.util.PublicUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
import org.springframework.util.StringUtils;

/**
 * 下载sql文件
 * @author hua 20161027
 *
 */
public class DownloadFileSql{
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger.getLogger(DownloadFileSql.class);
	
	/**
	 * 下载sql文件
	 * @param jdbcTemplate jdbcTemplate
	 * @param ids          主表的所有主键id
	 * @param parentTable  主表的表名
	 * @param childTables  从表的所有表名
	 * @param childKey     从表与主表相关联的外键
	 * @param response     reponse
	 */
	public void DownloadSql(JdbcTemplate jdbcTemplate,
			String ids,
			String parentTable,
			List<String> childTables,
			String childKey,
			HttpServletResponse response){
		SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
		String nowTime = format.format(new Date());
		String savePath = ResourceUtil.getSystempPath() + parentTable +"_sql_"+nowTime+".sql";
		File file = new File(savePath);
		if (!file.exists()) {
			try {
				file.createNewFile();
			} catch (IOException e) {
				logger.error("创建文件名失败！！");
				e.printStackTrace();
			}
		}
		
		// 获得sql语句
		List<String> insertSqlList = getInsertSql(jdbcTemplate,ids,parentTable,childTables,childKey);
		
		FileWriter fw = null;
		BufferedWriter bw = null;
		try {
			fw = new FileWriter(file);
			bw = new BufferedWriter(fw);
			if (insertSqlList.size() > 0) {
				for (int i = 0; i < insertSqlList.size(); i++) {
					bw.append(insertSqlList.get(i));
					bw.append("\n");
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
			logger.error(e.getMessage());
		} finally {
			try {
				bw.close();
				fw.close();
			} catch (IOException e) {
				e.printStackTrace();
				logger.error(e.getMessage());
			}
		}
		
		// 输出生成的sql文件
		String fileName = file.getName();
		InputStream fis = null;
		try{
			fis = new BufferedInputStream(new FileInputStream(savePath));
			// 清空response
			response.reset();
			// 设置response的header
			response.setContentType("text/html;charset=utf-8");
			response.addHeader("Content-Length", "" + file.length());
			OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
			response.setContentType("application/octet-stream");
			response.setHeader("Content-disposition", "attachment;filename="
					+ new String(fileName.getBytes("utf-8"), "ISO8859-1"));
			int bytesRead = 0;
			byte[] buffer = new byte[8192];
			while ((bytesRead = fis.read(buffer, 0, 8192)) != -1) {
				toClient.write(buffer, 0, bytesRead);
			}
			toClient.write(buffer);
			toClient.flush();
			toClient.close();
			fis.close();
			
		}catch(Exception e){
			e.printStackTrace();
			logger.error(e.getMessage());
		}finally {
			try{
				fis.close();
			}catch(IOException e){
				e.printStackTrace();
				logger.error(e.getMessage());
			}
		}
	}


	/**
	 * 获得插入数据库的sql
	 * @param ids 所有要下载的主表id
	 * @param parentTable 主表表名
	 * @param childTables 从表表名
	 * @param childKey    从表关联主表的列名
	 * @return
	 */
	private List<String> getInsertSql(JdbcTemplate jdbcTemplate,String ids,String parentTable,List<String> childTables,String childKey){
		List<String> insertList = new ArrayList<String>();
		String[] idArray = ids.split(",");
		String sql = "SELECT * FROM "+parentTable+" WHERE ID = ";
		String ls_id = "";
		String sqlSelect = "";
		for(String id : idArray){
			sqlSelect = sql + "'"+id+"'";
			
			SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(sqlSelect);
			SqlRowSetMetaData sqlRsmd = sqlRowSet.getMetaData();
			int columnCount = sqlRsmd.getColumnCount(); // 获得表字段个数
			String tableName = sqlRsmd.getTableName(columnCount); // 获得表名称
			if(StringUtils.isEmpty(tableName)){
				tableName = PublicUtil.getTableName(sqlSelect);
			}
			
			// 主表sql
			insertList = getTableSql(jdbcTemplate,insertList,columnCount,sqlRowSet,sqlRsmd,tableName,ls_id);
			
			// 从表sql
			if(null == childTables || 0 == childTables.size() || StringUtils.isEmpty(childKey)){
				continue;
			}
			
			for(String childTable : childTables){
				String childSelectSql = "select * from " + childTable + " where "+childKey+"='"+id+"'";
				String child_ls_id = "";
				
				SqlRowSet childSqlRowSet = jdbcTemplate.queryForRowSet(childSelectSql);
				SqlRowSetMetaData childSqlRsmd = childSqlRowSet.getMetaData();
				int childColumnCount = childSqlRsmd.getColumnCount(); // 获得表字段个数
				String childTableName = childSqlRsmd.getTableName(childColumnCount); // 获得表名称
				if(StringUtils.isEmpty(childTableName)){
					childTableName = PublicUtil.getTableName(childSelectSql);
				}
				insertList = getTableSql(jdbcTemplate,insertList,childColumnCount,childSqlRowSet,childSqlRsmd,childTableName,child_ls_id);
			}
		}
		return insertList;
	}
	
	/**
	 * 拼接sql语句
	 * @param jdbcTemplate
	 * @param insertList
	 * @param columnCount
	 * @param sqlRowSet
	 * @param sqlRsmd
	 * @param tableName
	 * @param ls_id
	 * @param tableId
	 * @return
	 */
	private List<String> getTableSql(JdbcTemplate jdbcTemplate,
			List<String> insertList,
			int columnCount,
			SqlRowSet sqlRowSet,
			SqlRowSetMetaData sqlRsmd,
			String tableName,
			String ls_id){
		String tableId="";
		while(sqlRowSet.next()){
			StringBuffer ColumnName = new StringBuffer();
			StringBuffer ColumnValue = new StringBuffer();

			for (int i = 1; i <= columnCount; i++) {
				String value = sqlRowSet.getString(i);
				if (value == null || "".equals(value)) {
					value = "";
				}
				Map<String, String> fieldMap = new HashMap<String, String>();
				fieldMap.put("name", sqlRsmd.getColumnName(i));
				fieldMap.put("fieldType", String.valueOf(sqlRsmd.getColumnType(i)));
				// 生成插入数据sql语句
				if (i == 1) {
					// 1、生成删除指定ID语句，清除现有冲突数据
					insertList.add("delete from " + tableName + " where " + sqlRsmd.getColumnName(i) + "='" + value + "';");

					ColumnName.append(sqlRsmd.getColumnName(i));
					ls_id = value;
					tableId = value;
					if (Types.CHAR == sqlRsmd.getColumnType(i) 
							|| Types.VARCHAR == sqlRsmd.getColumnType(i)) {
						ColumnValue.append("'").append(value).append("',");
					} else if (Types.SMALLINT == sqlRsmd.getColumnType(i) 
							|| Types.INTEGER == sqlRsmd.getColumnType(i) 
							|| Types.BIGINT == sqlRsmd.getColumnType(i) 
							|| Types.FLOAT == sqlRsmd.getColumnType(i) 
							|| Types.DOUBLE == sqlRsmd.getColumnType(i) 
							|| Types.NUMERIC == sqlRsmd.getColumnType(i) 
							|| Types.DECIMAL == sqlRsmd.getColumnType(i)) {
						if ("".equals(value))	value = "0";
						ColumnValue.append(value).append(",");
					} else if (Types.DATE == sqlRsmd.getColumnType(i) 
							|| Types.TIME == sqlRsmd.getColumnType(i) 
							|| Types.TIMESTAMP == sqlRsmd.getColumnType(i)) {
						if ("".equals(value))
							value = "2000-01-01";
						ColumnValue.append("'").append(value).append("',");
					} else {
						ColumnValue.append(value).append(",");
					}
				} else if (i == columnCount) {
					ColumnName.append("," + sqlRsmd.getColumnName(i));
					if (Types.CHAR == sqlRsmd.getColumnType(i) 
							|| Types.VARCHAR == sqlRsmd.getColumnType(i) 
							|| Types.LONGVARCHAR == sqlRsmd.getColumnType(i)) {
						ColumnValue.append("'").append(value).append("'");
					} else if (Types.SMALLINT == sqlRsmd.getColumnType(i) 
							|| Types.INTEGER == sqlRsmd.getColumnType(i) 
							|| Types.BIGINT == sqlRsmd.getColumnType(i) 
							|| Types.FLOAT == sqlRsmd.getColumnType(i) 
							|| Types.DOUBLE == sqlRsmd.getColumnType(i) 
							|| Types.NUMERIC == sqlRsmd.getColumnType(i) 
							|| Types.DECIMAL == sqlRsmd.getColumnType(i)) {
						if ("".equals(value))	value = "0";
						ColumnValue.append(value);
					} else if (Types.DATE == sqlRsmd.getColumnType(i) 
							|| Types.TIME == sqlRsmd.getColumnType(i) 
							|| Types.TIMESTAMP == sqlRsmd.getColumnType(i)) {
						if ("".equals(value))
							value = "2000-01-01";
						ColumnValue.append("'").append(value).append("'");
					} else {
						ColumnValue.append(value).append("");
					}
				} else {
					ColumnName.append("," + sqlRsmd.getColumnName(i));
					if (Types.CHAR == sqlRsmd.getColumnType(i) 
							|| Types.VARCHAR == sqlRsmd.getColumnType(i) 
							|| Types.LONGVARCHAR == sqlRsmd.getColumnType(i)) {
						ColumnValue.append("'").append(value).append("'").append(",");
					} else if (Types.SMALLINT == sqlRsmd.getColumnType(i) 
							|| Types.INTEGER == sqlRsmd.getColumnType(i) 
							|| Types.BIGINT == sqlRsmd.getColumnType(i) 
							|| Types.FLOAT == sqlRsmd.getColumnType(i) 
							|| Types.DOUBLE == sqlRsmd.getColumnType(i) 
							|| Types.NUMERIC == sqlRsmd.getColumnType(i) 
							|| Types.DECIMAL == sqlRsmd.getColumnType(i)) {
						if ("".equals(value))	value = "0";
						ColumnValue.append(value).append(",");
					} else if (Types.DATE == sqlRsmd.getColumnType(i) 
							|| Types.TIME == sqlRsmd.getColumnType(i) 
							|| Types.TIMESTAMP == sqlRsmd.getColumnType(i)) {
						if ("".equals(value))
							value = "2000-01-01";
						ColumnValue.append("'").append(value).append("',");
					} else if (Types.BLOB == sqlRsmd.getColumnType(i) 
							|| Types.LONGVARCHAR == sqlRsmd.getColumnType(i) 
							|| Types.LONGNVARCHAR == sqlRsmd.getColumnType(i) 
							|| Types.BINARY == sqlRsmd.getColumnType(i) 
							|| Types.LONGVARBINARY == sqlRsmd.getColumnType(i) 
							|| Types.VARBINARY == sqlRsmd.getColumnType(i)) {
						String ls_tmp = MigrateForm.getBlob(ls_id, tableName, sqlRsmd.getColumnName(i), jdbcTemplate);
						ColumnValue.append(ls_tmp).append(",");
					} else {
						ColumnValue.append(value).append(",");
					}
				}

			}
			
			insertSQL(tableName, ColumnName, ColumnValue,insertList);// 拼装并放到全局list里面
			if(tableName.equals("cgform_head")){
				insertList.add("update cgform_head set is_dbsynch='N' where id='"+tableId+"';");// 设为未同步		
			}
		}
		
		return insertList;
	}
	
	/**
	 * 拼装insertsql 放到全局list里面
	 * 
	 * @param ColumnName
	 * @param ColumnValue
	 */
	public static void insertSQL(String tablename, StringBuffer ColumnName, StringBuffer ColumnValue,List<String> insertList) {
		StringBuffer insertSQL = new StringBuffer();
		// 拼装sql语句
		insertSQL.append("insert into ").append(" ").append(tablename).append("(").append(ColumnName.toString()).append(")").append(" values ").append("(").append(ColumnValue.toString()).append(");");
		insertList.add(insertSQL.toString()); // 放到全局list里面
	}

}