数据库抽取，生成CSV文件导出，CSVUtils工具类

开发背景：

最近一直在忙一个任务调度系统，需求一直没定下来，需求一直变更，调度一直改，往往复复。。。

等这波忙完了可以写一下关于BI这边调度任务的相关问题，从今天开始陆陆续续的写调度中的事儿了。这个需求是这样的，业务部门有导出excel的需求，然而现在BI的展示用了一个号称BI界前50强的第三方，竟然不支持分页，所以数据导出就成了问题，落我头上了，所以我的这个需求就是根据sql来生成csv文件。我把我写这个需求的demo传上来。

package com.****.common.util;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.*;/*** @author liyang*/
public class TestTables {public static void main(String args[]) {Connection con = null;// 创建一个数据库连接PreparedStatement pre = null;// 创建预编译语句对象，一般都是用这个而不用StatementResultSet dataset = null;// 创建一个结果集对象ResultSet insertResult = null;List list = new ArrayList();try {Class.forName("oracle.jdbc.driver.OracleDriver");// 加载Oracle驱动程序System.out.println("开始尝试连接数据库！");String url = "jdbc:oracle:" + "thin:@101.95.139.62:1521:anebidev";// 127.0.0.1是本机地址，XE是精简版Oracle的默认数据库名String user = "ods_lb";// 用户名,系统默认的账户名String password = "ods_lb";// 你安装时选设置的密码con = DriverManager.getConnection(url, user, password);// 获取连接System.out.println("连接成功！");String sql = "select * from s_user";pre = con.prepareStatement(sql);// 实例化预编译语句// pre.setString(1, "刘显安");// 设置参数，前面的1表示参数的索引，而不是表中列名的索引dataset = pre.executeQuery();// 执行查询，注意括号中不需要再加参数while (dataset.next()) {ResultSetMetaData md = dataset.getMetaData();int columnCount = md.getColumnCount();   //获得列数LinkedHashMap<String,Object> rowData = new LinkedHashMap<String,Object>();for (int i = 1; i <= columnCount; i++) {rowData.put(md.getColumnName(i), dataset.getObject(i));}list.add(rowData);}} catch (Exception e) {e.printStackTrace();} finally {try {// 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源// 注意关闭的顺序，最后使用的最先关闭
//                if (result != null)
//                    result.close();if (insertResult != null)insertResult.close();if (pre != null)pre.close();if (con != null)con.close();System.out.println("数据库连接已关闭！");} catch (Exception e) {e.printStackTrace();}}//生成csv文件String dataDate = "20170505";CSVUtils.createCSVFile(list, "D:\\aa\\bb\\cc\\dd", "##DATADATE##", dataDate);}
}

package com.****.common.util;import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.security.AccessController;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;import javax.servlet.http.HttpServletResponse;import sun.security.action.GetPropertyAction;/*** 文件操作* @author liyang* @version $Id: CSVUtils.java, v 0.1
2017年4月22日 下午2:19:59 Exp $*/
public class CSVUtils {/*** 生成为CVS文件 * @param exportData*              源数据List*              csv文件的列表头map* @param outPutPath*              文件路径* @param fileName*              文件名称* @return*/@SuppressWarnings("rawtypes")public static File createCSVFile(List<LinkedHashMap<String,Object>> exportData, /*LinkedHashMap map,*/ String outPutPath,String fileName,String taskDate) {File csvFile = null;BufferedWriter csvFileOutputStream = null;String fileNameNew = null;System.setProperty("sun.jnu.encoding","utf-8");if(fileName.indexOf("##DATADATE##") >= 0){fileNameNew = fileName.replace("##DATADATE##", String.valueOf(Integer.valueOf(taskDate) - 1) + "-" + taskDate);}outPutPath = outPutPath+System.getProperty("file.separator")+taskDate.substring(0,4)+System.getProperty("file.separator")+taskDate.substring(4,6);try {//如果存在，则删除文件File f = generateFile(fileNameNew, ".csv", new File(outPutPath));if(f.exists()&& f.isFile()){f.getAbsoluteFile().delete();}File file = new File(outPutPath);if (!file.exists()) {file.getAbsoluteFile().mkdirs();}//定义文件名格式并创建csvFile = createTempFile(fileNameNew,".csv", new File(outPutPath));// UTF-8使正确读取分隔符","  csvFileOutputStream = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile), "GBK"), 1024);// 写入文件头部LinkedHashMap<String,Object> map = exportData.get(0);for (Iterator propertyIterator = map.entrySet().iterator(); propertyIterator.hasNext();) {java.util.Map.Entry propertyEntry = (java.util.Map.Entry) propertyIterator.next();csvFileOutputStream.write(propertyEntry.getKey() != null ? new String(((String) propertyEntry.getKey().toString()).getBytes("GBK"), "GBK") : "");if (propertyIterator.hasNext()) {csvFileOutputStream.write(",");}}csvFileOutputStream.write("\r\n");// 写入文件内容for(LinkedHashMap<String,Object> mapData :exportData){for (Iterator dataIterator = mapData.entrySet().iterator(); dataIterator.hasNext();) {java.util.Map.Entry dataEntry = (java.util.Map.Entry) dataIterator.next();csvFileOutputStream.write(dataEntry.getValue() != null ? new String(((String) dataEntry.getValue().toString()).getBytes("GBK"), "GBK") : "");if (dataIterator.hasNext()) {csvFileOutputStream.write(",");}}csvFileOutputStream.write("\r\n");}} catch (Exception e) {e.printStackTrace();} finally {try {csvFileOutputStream.close();} catch (IOException e) {e.printStackTrace();}}return csvFile;}/*** 下载文件* @param response* @param csvFilePath*              文件路径* @param fileName*              文件名称* @throws IOException*/public static void exportFile(HttpServletResponse response, String csvFilePath, String fileName)throws IOException {response.setContentType("application/csv;charset=GBK");response.setHeader("Content-Disposition","attachment;  filename=" + new String(fileName.getBytes("GBK"), "ISO8859-1"));//URLEncoder.encode(fileName, "GBK")InputStream in = null;try {in = new FileInputStream(csvFilePath);int len = 0;byte[] buffer = new byte[1024];response.setCharacterEncoding("GBK");OutputStream out = response.getOutputStream();while ((len = in.read(buffer)) > 0) {//out.write(new byte[] { (byte) 0xEF, (byte) 0xBB, (byte) 0xBF });out.write(buffer, 0, len);}} catch (FileNotFoundException e) {System.out.println(e);} finally {if (in != null) {try {in.close();} catch (Exception e) {throw new RuntimeException(e);}}}}/*** 删除该目录filePath下的所有文件* @param filePath*            文件目录路径*/public static void deleteFiles(String filePath) {File file = new File(filePath);if (file.exists()) {File[] files = file.listFiles();for (int i = 0; i < files.length; i++) {if (files[i].isFile()) {files[i].delete();}}}}/*** 删除单个文件* @param filePath*         文件目录路径* @param fileName*         文件名称*/public static void deleteFile(String filePath, String fileName) {File file = new File(filePath);if (file.exists()) {File[] files = file.listFiles();for (int i = 0; i < files.length; i++) {if (files[i].isFile()) {if (files[i].getName().equals(fileName)) {files[i].delete();return;}}}}}public static File createTempFile(String prefix, String suffix, File directory) throws IOException {if (prefix.length() < 3)throw new IllegalArgumentException("Prefix string too short");if (suffix == null)suffix = ".tmp";File tmpdir = (directory != null) ? directory : location();SecurityManager sm = System.getSecurityManager();File f;f = generateFile(prefix, suffix, tmpdir);if (sm != null) {try {sm.checkWrite(f.getPath());} catch (SecurityException se) {// don't reveal temporary directory locationif (directory == null)throw new SecurityException("Unable to create temporary file");throw se;}}return f;}/* -- Temporary files -- */private static final File tmpdir = new File(AccessController.doPrivileged(new GetPropertyAction("java.io.tmpdir")));static File location() {return tmpdir;}// file name generationstatic File generateFile(String prefix, String suffix, File dir)throws IOException{// Use only the file name from the supplied prefixprefix = (new File(prefix)).getName();String name = prefix + suffix;File f = new File(dir, name);return f;}}

以上我只测试过文件导出功能，文件下载、删除功能没有验证，同时要注意一点的是：生成的文件如果调用file.creatTempFile()方法会在生成文件名和后缀之间加上随机数，api里写明：这个问题是为了避免文件名重复而故意设计成这样的，但是为了项目的需求，我把rt.jar里的file.java拿出来改造了。还有一个要注意的是里面关于“”DATADATE“”的东西，这个是项目里的一个约定参数，你可以根据你主观修改。