使用到的技术点:
1.Java写文件;
2.熟悉JDBC API;
3.Java集合ArrayList的使用;
4.Java字符串截取;
本代码仅供测试,如要使用,需自行增加数据库列类型定义和判定逻辑。
DBConnectMySQL.java
package com.manny.util.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBConnectMySQL {
private static String userName = "root";
private static String password = "123456";
private static String driver = "com.mysql.jdbc.Driver";
public Connection getConnection()
{
Connection conn = null;
try {
Class.forName(driver);
String url = "jdbc:mysql://localhost:3306/life";
conn = DriverManager.getConnection(url, userName, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
}
ExportLongitudinalData.java
package com.manny.util;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import com.manny.db.common.DataTypeConstant;
import com.manny.util.db.DBConnectMySQL;
public class ExportLongitudinalData
{
public static void main(String[] args)
{
DBConnectMySQL cm = new DBConnectMySQL();
Connection conn = null;
Statement sts = null;
ResultSet rs = null;
try
{
conn = cm.getConnection();
sts = conn.createStatement();
String sql = "select * from life.pictures";
rs = sts.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();
System.out.println("colCount=" + colCount);
List colNameAndDatas = new ArrayList();
for (int i = 1; i <= colCount; i++)
{
String colTypeName = rsmd.getColumnTypeName(i);
String colClassName = rsmd.getColumnClassName(i);
String colName = rsmd.getColumnName(i);
colNameAndDatas.add(colName);
}
while (rs.next())
{
for (int i = 1; i <= colCount; i++)
{
String colTypeName = rsmd.getColumnTypeName(i);
String colData = getColumnData(rs, i, colTypeName);
String currentData = (String) colNameAndDatas.get(i - 1);
colNameAndDatas.set(i - 1, currentData + "," + colData);
}
}
String fileName = getTableNameFromSQL(sql, "CSV");
outputToFile(colNameAndDatas, fileName);
}
catch (SQLException e)
{
e.printStackTrace();
}
catch (SecurityException e)
{
e.printStackTrace();
}
catch (IllegalArgumentException e)
{
e.printStackTrace();
}
finally
{
try
{
if (rs != null && !rs.isClosed())
rs.close();
if (sts != null && !sts.isClosed())
sts.close();
if (conn != null && !conn.isClosed())
conn.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}
private static String getColumnData(ResultSet rs, int columnIndex, String colTypeName)
{
try
{
if (DataTypeConstant.TYPE_VARCHAR.equals(colTypeName))
{
return rs.getString(columnIndex);
}
if (DataTypeConstant.TYPE_CHAR.equals(colTypeName))
{
return rs.getString(columnIndex);
}
}
catch (SQLException e)
{
e.printStackTrace();
}
return "";
}
private static void outputToFile(List rowDataList, String fileName)
{
try
{
String filePath = "D:\\temp\\";
File dir = new File(filePath);
if (!dir.exists())
{
dir.mkdirs();
}
File exportFile = new File(filePath + File.separator + fileName);
if (!exportFile.exists())
{
exportFile.createNewFile();
}
FileWriter fw = new FileWriter(exportFile);
for (Iterator iterator = rowDataList.iterator(); iterator.hasNext();)
{
String rowData = (String) iterator.next();
System.out.println(rowData);
fw.write(rowData + "\n");
}
fw.flush();
fw.close();
}
catch (IOException e)
{
e.printStackTrace();
}
}
private static String getTableNameFromSQL(String sql, String fileExt)
{
String fileName = "";
if (sql != null)
{
sql = sql.toUpperCase().trim();
int whInd = sql.indexOf(" WHERE ");
int frmInd = sql.indexOf(" FROM ");
int ordInd = sql.indexOf(" ORDER BY ");
if (frmInd == -1)
{
fileName = "ErrorSQL";
}
frmInd+=5;
if (whInd > 0)
{
fileName = sql.substring(frmInd, whInd).trim();
}
else if (ordInd > 0)
{
fileName = sql.substring(frmInd, ordInd).trim();
}
else
{
fileName = sql.substring(frmInd).trim();
}
}
return fileName + "." + fileExt;
}
}
DataTypeConstant.java
package com.manny.db.common;
public interface DataTypeConstant
{
public static String TYPE_VARCHAR="VARCHAR";
public static String TYPE_CHAR="CHAR";
}