概要
- id, data1, data2, data3, ...のように並んだCSVファイルから,idからdata3までのデータをSqliteのテーブルに書き込む.
- sqliteを利用する方法は前回と同じ
- 引数で読み込むCSVファイルと書き込むテーブル名を指定できる
- idは一意な値として作成し,同じidだった場合は,上書きする
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.io.*;
public class Csv2Sqlite
{
public static void main(String[] args) throws ClassNotFoundException
{
Class.forName("org.sqlite.JDBC");
Connection connection = null;
try
{
String csv = args.length>0 ? args[0] : "input.csv";
String table = args.length>0 ? args[1] : "test";
connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
Statement statement = connection.createStatement();
statement.setQueryTimeout(30);
statement.executeUpdate("create table if not exists "+table+" (uid integer UNIQUE, url string, contents string, value string)");
try
{
FileInputStream fis = new FileInputStream(new File(csv));
InputStreamReader isr = new InputStreamReader(fis , "UTF-8");
BufferedReader br = new BufferedReader(isr);
String line = "";
String insert = "";
boolean header = true;
while((line = br.readLine()) != null)
{
StringTokenizer token = new StringTokenizer(line, ",");
int column_queue = 0;
while(token.hasMoreTokens())
{
String tmpString = token.nextToken();
System.out.println(tmpString);
if(column_queue==0)
{
insert += tmpString + ", ";
}
else if(column_queue<=3)
{
insert += "\'" + tmpString + "\', ";
}
column_queue++;
}
if(column_queue<4)
{
while(column_queue<4)
{
insert += "\'null\', ";
column_queue++;
}
}
if(header)
{
header = false;
}
else
{
insert = insert.substring(0,insert.length()-2);
insert ="insert or replace into "+table+" values("+insert+")";
System.out.println(insert);
statement.executeUpdate(insert);
}
insert = "";
System.out.println("------------------------------");
}
}
catch (FileNotFoundException e)
{
e.printStackTrace();
}
catch (IOException e)
{
e.printStackTrace();
}
ResultSet rs = statement.executeQuery("select * from "+table);
while(rs.next())
{
System.out.println("\t"+rs.getString("uid")+"\t"+rs.getString("url")+"\t"+rs.getString("contents")+"\t"+rs.getString("value"));
}
}
catch(SQLException e)
{
System.err.println("exception :"+e.getMessage());
}
finally
{
try
{
if(connection != null) connection.close();
}
catch(SQLException e)
{
System.err.println(e);
}
}
}
}