JDBC实现纵向导出数据库数据

使用到的技术点：

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";
}