【Java/JDBC】利用ResultSetMetaData从数据库的某表中获取字段信息并存到csv文件
代码下载:https://files.cnblogs.com/files/xiandedanteng/FindNotnullColumns20191102-2.rar
这篇还不够完善,请看更完善的续篇 https://www.cnblogs.com/xiandedanteng/p/11783796.html
本例主要使用的是JDBC提供的ResultSetMetaData类去取表的相关信息,文中只取了四种,其它信息大家可以查看这个类的方法而得知。
JavaCode:
package com.hy; import java.io.File; import java.io.FileWriter; import java.io.IOException; import java.sql.Connection; 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<Field> fields; /** * 从DB里取字段 * @param tablename * @return */ public List<Field> fetchAllColumns(String tablename){ fields=new ArrayList<Field>(); 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<count+1; i++) { // 列名 String columnName = rsMetadata.getColumnLabel(i); // 列容量 int size =rsMetadata.getColumnDisplaySize(i); // 列数据类型 String columnType = rsMetadata.getColumnTypeName(i); // 列是否允許为空 boolean allowNull=(rsMetadata.isNullable(i)!=0); /*String raw = "columnName={0},size={1},columnType={2},allowNull={3}"; Object[] arr = { columnName, size, columnType,allowNull}; String outStr = MessageFormat.format(raw, arr); log.info(outStr);*/ Field f=new Field(); f.name=columnName; f.size=size; f.type=columnType; f.allowNull=allowNull; fields.add(f); } } rs.close(); } catch (Exception e) { System.out.print("DB/SQL ERROR:" + e.getMessage()); } finally { try { stmt.close(); conn.close(); } catch (SQLException e) { System.out.print("Can't close stmt/conn because of " + e.getMessage()); } } return fields; } /** * 打印全部字段 */ public void printFields() { for(Field f:fields) { String raw = "column name={0},size={1},type={2},allowNull={3}"; Object[] arr = { f.name, f.size, f.type,f.allowNull}; String outStr = MessageFormat.format(raw, arr); //log.info(outStr); } } /** * 输出全部列到csv文件 * @param csvFilename * @return */ public boolean saveAllToCsv(String csvFilename) { try { FileWriter fileWriter = new FileWriter(new File(csvFilename), true); List<String> allowNulls=new ArrayList<String>(); List<String> allowType=new ArrayList<String>(); List<String> allowSize=new ArrayList<String>(); List<String> allowName=new ArrayList<String>(); 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<String> allowNulls=new ArrayList<String>(); List<String> allowType=new ArrayList<String>(); List<String> allowSize=new ArrayList<String>(); List<String> allowName=new ArrayList<String>(); 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