package com.vefan.csv;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 本文讲的是在java环境下对CSV文件进行读取操作,前几天在网上找到一个驱动可以把这种格式的文件当作数据库一样读取。操作步骤如下:
* 第一、下载驱动程序http://sourceforge.net/projects/csvjdbc
* 第二、把csvjdbc.jar文件放到classpath路径中去
* */
public class JdbcCsv
{
public static void main(String args[]){
JdbcCsv jdbcCsv = new JdbcCsv();
jdbcCsv.queryCsv();
}
public void queryCsv(){
Connection conn = null;
Statement stmt = null;
ResultSet results = null;
try
{
//load the driver into memory
Class.forName("org.relique.jdbc.csv.CsvDriver");
// create a connection. The first command line parameter is assumed
// to be the directory in which the .csv files are held
conn = DriverManager.getConnection("jdbc:relique:csv:E:\\workspace\\JavaApp\\csv");
// create a Statement object to execute the query
stmt = conn.createStatement();
String sql = "select * from user";
// String sql = "select * from user where rownum<3"; //不支持此种查询
// String sql = "select top 10 * from user"; //不支持此种查询
results = stmt.executeQuery(sql);
// results.setFetchSize(3); //不支持
ResultSetMetaData metaData = results.getMetaData();
int columnCount = metaData.getColumnCount(); //一共有多少列
for(int i=1; i<=columnCount; i++){
String name = metaData.getColumnName(i);
if(i!=columnCount)
System.out.print(name+"\t\t");
else
System.out.println(name+"\t\t");
}
while(results.next()){
// int rowNo = results.getRow(); 不支持获取当前行号
// System.out.println("第"+rowNo+"行");
for(int i=1; i<=columnCount; i++){
String value = results.getString(i);
if(i!=columnCount)
System.out.print(value+"\t\t");
else
System.out.println(value+"\t\t");
}
}
} catch (Exception e)
{
e.printStackTrace();
}
finally{
try
{
if(null != results) results.close();
if(null != stmt) stmt.close();
if(null != conn) conn.close();
} catch (SQLException e)
{
e.printStackTrace();
}
} //end of finally
}
}
/*
A read-only JDBC driver for Java that uses Comma Separated Value (CSV) files as database tables.
Ideal for writing data import programs and conversion programs.
The latest file releases:
Package Release Date
CsvJdbc 0.10 August 9, 2004
当时下载的csvjdbc.jar文件版本是0.10, 有很多JDBC的功能都不支持,
它只能把csv文件的第一行作为字段名,从第二行作为数据行,不能指定其它行作为字段名;
在创建链接时,csvjdbc是把存放csv文件的文件夹作为数据库,文件夹中的每一个csv文件作为数据库中的表;
*/
/*
* Advanced Options
--------------------------------------------------------------------------------
The driver also supports a number of parameters that change the default behaviour of the driver.
These properties are:
separator
Used to specify a different column separator (Default is ',').
suppressHeaders
Used to specify if the first line contains column header information (Default is false; column headers are on first line).
fileExtension
Used to specify a different file extension (Default is ".csv")
charset
Used to specify a different than default charset encoding of input file (default is same VM default charset)
This following example code shows how these properties are used.
...
Properties props = new java.util.Properties();
props.put("separator","|"); // separator is a bar
props.put("suppressHeaders","true"); // first line contains data
props.put("fileExtension",".txt"); // file extension is .txt
props.put("charset","ISO-8859-2"); // file encoding is "ISO-8859-2"
Connection conn = Drivermanager.getConnection("jdbc:relique:csv:" + args[0],props)
Scrollable ResultSets
--------------------------------------------------------------------------------
The driver also now supports scrollable result sets.
This following example code shows how these are used.
...
Connection conn = Drivermanager.getConnection("jdbc:relique:csv:" + args[0],props)
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 0);
ResultSet results = stmt.executeQuery("SELECT ID,NAME FROM sample");
resulst.next();
...
results.first();
...
results.previous();
...
results.relative(2);
...
results.absolute(2);
...
*/
下面是user.csv文件内容:
[table]
|ID CODE NAME MOBILE EMAIL VALID ORGANID LOGO|
|33157 jia 用户甲 13625845145 0 24072 <BLOB>|
|20600 1 高利强 13064878451 0 41 <BLOB>|
|33163 yi 用户乙 13436963236 0 24072 <BLOB>|
|33691 312 用户甲 13636363626 0 24072 <BLOB>|
|78874 fsdf_gg 大幅度 15865895236 0 999 <BLOB>|
|81322 1 中心 13533333333 1 45702 <BLOB>|
|33879 YI 用户乙 13526262512 0 24072 <BLOB>|
|33966 6 小李 13523075568 0 24072 <BLOB>|
|81330 12234 中心 13533333333 1 45702 <BLOB>|
|41637 12 12 15648551210 0 20583 <BLOB>|
|41665 12 12 15648551210 0 20583 <BLOB>|
|45829 hi hello 13589898545 0 24082 <BLOB>
|
[/table]