Wednesday, September 4, 2013

How to run a SQL query against a in-memory CSV file

It's a pity that CsvJdbc cannot run against an in-memory CSV string or InputStream or Reader. That's because it follows the JDBC contract and the only way to push data inside the driver is using the JDBC url connection string.

Too bad, because even a JDBC url does not have any built-in method to return a map of parameters (as far as I know) and other URL functions used to do the same thing for http urls don't work because "jdbc" is not a valid protocol ;-).

So, it's time to do nasty things using typecasting and altering the source code. Something like this.

[1] Replace org.relique.jdbc.csv.CsvConnection with another version that includes an extra attribute, a Reader

public class CsvConnection implements Connection{
...
private Reader reader;
...
    public Reader getReader() {
        return reader;
    }

    public void setReader(Reader reader) {
        this.reader = reader;
    }
}


[2] Now, create a class to implement the TableReader interface

import java.io.File;
import java.io.FileReader;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.relique.io.TableReader;
import org.relique.jdbc.csv.CsvConnection;
import org.relique.jdbc.csv.CsvDriver;

public class CSVQuery implements TableReader{

    public static void main(String[] args) throws Exception {
        CSVQuery q = new CSVQuery();
       
        Reader reader = new FileReader(
                new File("/path/to/some/file.csv"));

        q.executeQuery(reader);
       
        reader.close();
    }

    public void executeQuery(Reader reader) throws Exception{

        Class.forName("org.relique.jdbc.csv.CsvDriver");
        CsvConnection conn = (CsvConnection)DriverManager.getConnection("jdbc:relique:csv:class:CSVQuery");

        conn.setReader(reader);       
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM myTable where increment like 'FTP%'");
        CsvDriver.writeToCsv(rs, System.out, true);

        rs.close();
        stmt.close();
        conn.close();
    }

    @Override
    public Reader getReader(Statement stmt, String table) throws SQLException {
        CsvConnection csvConn = (CsvConnection)stmt.getConnection();
        return csvConn.getReader();
    }

    @Override
    public List<String> getTableNames(Connection conn) throws SQLException {
        ArrayList<String> names = new ArrayList<String>();
        names.add("myTable");
        return names;
    }
}


and that's it.


No comments:

Post a Comment