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));// 清空responseresponse.reset();// 设置response的headerresponse.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);}// 主表sqlinsertList = getTableSql(jdbcTemplate,insertList,columnCount,sqlRowSet,sqlRsmd,tableName,ls_id);// 从表sqlif(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里面}}