把数据库数据 导入CSV 工具类

Table

public class Table {

	private List<Map<String, String>> columns = new ArrayList<Map<String, String>>();
	private String tableName;

	public List<Map<String, String>> getColumns() {
		return columns;
	}

	public void setColumns(List<Map<String, String>> columns) {
		this.columns = columns;
	}

	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

}

ExportQueryDataLToCSV

public interface ExportQueryDataLToCSV {

	abstract void exportCSV(String sql, Object[] params, String stateMentId,
			String defauleFileName) throws SQLException;

	abstract public void clearData(String defauleFileName);

}

ExportQueryDataLToCSVImpl

@Component("exportCSVImpl")
public class ExportQueryDataLToCSVImpl implements ExportQueryDataLToCSV {

	@Autowired
	private transient JdbcTemplate jdbcTemplate;

	private String baseDir = "./TestSource/";

	private Table table;

	public void initTable(String sql, StringBuffer buf, String stateMentId) {
		String[] words = sql.split("[ ]+");
		int i = 0;
		for (; i < words.length; i++) {
			if ("FROM".equals(words[i].toUpperCase()))
				break;
		}
		buf.append("STATEMENT ID" + "," + stateMentId + "\r\n");
		buf.append("TABLE NAME" + "," + words[i + 1] + "\r\n");

		table = new Table();
		table.setTableName(words[i + 1].toUpperCase());

	}

	public void exportCSV(String sql, Object[] params, String stateMentId,
			String defauleFileName) throws SQLException {

		StringBuffer buf = new StringBuffer();
		PrintWriter printer = null;
		printer = getDefaultPrinter(defauleFileName);
		initTable(sql, buf, stateMentId);// Table

		List<Map<String, Object>> result = jdbcTemplate.queryForList(sql,
				params);

		if (result != null && result.size() > 0) {
			for (Map<String, Object> map : result) {
				for (String key : map.keySet()) {
					Map<String, String> tempMap = new HashMap<String, String>();
					tempMap.put(key, getObjectType(map.get(key)));
					buf.append(key + ",");
					table.getColumns().add(tempMap);
				}
				buf.append("\r\n");
				break;

			}
		}

		if (result != null && result.size() > 0) {
			for (Map<String, Object> map : result) {
				for (Map<String, String> columnMap : table.getColumns()) {
					buf.append(gernateString(map, columnMap));
				}
				buf.append("\r\n");
			}
			buf.append("\r\n");

			try {
				printer.append(buf);
				printer.flush();

			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				if (printer != null)
					printer.close();
			}
		} else {
			LOGGER.debug("Without data from execute query sql !");
		}

	}

	public String gernateString(Map<String, Object> mapData,
			Map<String, String> columnMap) {
		StringBuffer tempBuf = new StringBuffer();
		String key = (String) columnMap.keySet().toArray()[0];
		String data = "";

		if (mapData.get(key) != null)
			if (columnMap.get(key).equals("Boolean")) {
				Object obj = mapData.get(key);
				boolean bl = (Boolean) obj;
				data = bl ? "Y" : "N";
			} else
				data = mapData.get(key).toString();
		String appendChar = "'";
		if (columnMap.get(key).equals("Integer"))
			appendChar = "";

		if (mapData.get(key) != null)
			tempBuf.append(appendChar + data + appendChar + ",");
		else
			tempBuf.append(data + ",");
		return tempBuf.toString();
	}

	// get default printer
	public PrintWriter getDefaultPrinter(String defauleFileName) {
		PrintWriter printer = null;
		printer = getWrite(buildCSVPath(defauleFileName));
		return printer;

	}

	public PrintWriter getWrite(File file) {
		PrintWriter printer = null;
		try {

			printer = new PrintWriter(new FileOutputStream(file, true));

		} catch (FileNotFoundException e) {
			LOGGER.debug("PrintWrite create failed!!");
		}
		return printer;

	}

	public File buildCSVPath(String defauleFileName) {
		File tmpFile = getClassPathFile(defauleFileName);
		if (tmpFile.isFile()) {
			return tmpFile;
		}
		File baseFile = new File(baseDir);
		if (!baseFile.exists()) {
			baseFile.mkdirs();
		}
		File defaultFile = new File(baseDir + defauleFileName);
		if (!defaultFile.exists()) {
			try {
				defaultFile.createNewFile();
			} catch (IOException e) {

			}
		}

		return defaultFile;
	}

	private File getClassPathFile(String classPath) {
		ClassLoader loader = this.getClass().getClassLoader();
		if (null == loader) {
			loader = ClassLoader.getSystemClassLoader();
		}
		String path = classPath.startsWith("/") ? classPath.substring(1)
				: classPath;
		URL url = loader.getResource(path);
		return new File(null == url ? loader.getResource("").getPath() + path
				: url.getPath());
	}

	public String getObjectType(Object obj) {
		String type = "String";
		if (obj instanceof String) {
			type = "String";
		} else if (obj instanceof Integer) {
			type = "Integer";
		} else if (obj instanceof BigDecimal) {
			type = "BigDecimal";
		} else if (obj instanceof Boolean) {
			type = "Boolean";
		}

		return type;

	}

	public void clearData(String defauleFileName) {
		File defaultFile = new File(baseDir + defauleFileName);
		if (defaultFile.exists()) {
			StringBuffer buf = new StringBuffer();
			PrintWriter printer = null;
			try {
				printer = new PrintWriter(new FileOutputStream(defaultFile,
						false));
				printer.append(buf);
				printer.flush();
				LOGGER.debug("Query data cleared");

			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				if (printer != null)
					printer.close();
			}
		}
	}
}

测试类

public class TestHello {

	@Autowired
	@Qualifier("exportCSVImpl")
	private ExportQueryDataLToCSV exportToCSVImpl;

	private String defauleFileName = "hello.csv";

	@Test
	public void exportMstrTbl4Rec3() {
		try {
			exportCSV();

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	private void exportCSV() throws SQLException {
		try {
			StringBuilder sb = new StringBuilder();
			sb.append("select * from table");
			String sql = sb.toString();

			exportToCSVImpl.exportCSV(sql, null, "hello", defauleFileName);

		} catch (Exception e) {

			e.printStackTrace();
		}

	}
}