import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
class Field{
String name; // 列名
int size; // 列容量
String type; // 列数据类型
boolean allowNull;// 列是否允許为空
* 利用ResultSetMetaData从数据库的某表中获取字段信息并存到csv文件
* @author horn1
public class ColumnSeeker {
private static Logger log = Logger.getLogger(ColumnSeeker.class);
private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
private static final String DB_URL = "jdbc:mysql://192.168.161.130:3306/test";
private static final String USER = "root";
private static final String PSWD = "12345678";
private List
fields;
* 从DB里取字段
* @param tablename
* @return
public List fetchAllColumns(String tablename){
fields=new ArrayList();
Connection conn = null;
Statement stmt = null;
try {
Class.forName(JDBC_DRIVER).newInstance();
conn = DriverManager.getConnection(DB_URL, USER, PSWD);
stmt = conn.createStatement();
String sql = "select * from emp order by id limit 1";
ResultSet rs = stmt.executeQuery(sql);
ResultSetMetaData rsMetadata = rs.getMetaData();
while (rs.next()) {
int count = rsMetadata.getColumnCount();
for (int i=1; i allowNulls=new ArrayList();
List allowType=new ArrayList();
List allowSize=new ArrayList();
List allowName=new ArrayList();
for(Field f:fields) {
allowNulls.add(String.valueOf(f.allowNull));
allowType.add(f.type);
allowSize.add(String.valueOf(f.size));
allowName.add(f.name);
String line="";
line =String.join(",", allowSize)+ System.getProperty("line.separator");
fileWriter.write(line);
line =String.join(",", allowType)+ System.getProperty("line.separator");
fileWriter.write(line);
line =String.join(",", allowNulls)+ System.getProperty("line.separator");
fileWriter.write(line);
line =String.join(",", allowName)+ System.getProperty("line.separator");
fileWriter.write(line);
fileWriter.flush();
fileWriter.close();
return true;
} catch (IOException e) {
e.printStackTrace();
return false;
* 仅将非空列输出到CSV
* @param csvFilename
* @return
public boolean saveNotnullToCsv(String csvFilename) {
try {
FileWriter fileWriter = new FileWriter(new File(csvFilename), true);
List allowNulls=new ArrayList();
List allowType=new ArrayList();
List allowSize=new ArrayList();
List allowName=new ArrayList();
for(Field f:fields) {
if(f.allowNull==false) {
allowNulls.add(String.valueOf(f.allowNull));
allowType.add(f.type);
allowSize.add(String.valueOf(f.size));
allowName.add(f.name);
String line="";
line =String.join(",", allowSize)+ System.getProperty("line.separator");
fileWriter.write(line);
line =String.join(",", allowType)+ System.getProperty("line.separator");
fileWriter.write(line);
line =String.join(",", allowNulls)+ System.getProperty("line.separator");
fileWriter.write(line);
line =String.join(",", allowName)+ System.getProperty("line.separator");
fileWriter.write(line);
fileWriter.flush();
fileWriter.close();
return true;
} catch (IOException e) {
e.printStackTrace();
return false;
public static void main(String[] args) {
ColumnSeeker cs=new ColumnSeeker();
cs.fetchAllColumns("emp");
cs.printFields();
cs.saveAllToCsv("d:\\all.csv");
cs.saveNotnullToCsv("d:\\notnulls.csv");
log.info("Completed");
输出的CSV文件截图：
--END-- 2019年11月2日11:05:53
