Monday, September 23, 2013

Monday's Useful Link's

  1. http://www.artfulsoftware.com/infotree/queries.php - SQL queries cookbook
  2. http://txt2re.com/ - user-friendly regex generator

Wednesday, September 18, 2013

(yet another) How to enhance OpenJPA classes with TomEE 1.5.2 using Eclipse

import java.io.File;
import java.util.Collection;

import org.apache.commons.io.FileUtils;

/**
 * OpenJPA relies on a class "enhancement" process that is not fully supported in a easy way for eclipse users.
 *
 * <p>Before writing this script, I've considered the following actions
 *
 * <ol>
 * <li>Trying the <a href="http://openjpa.apache.org/openjpaeclipseinstallation.html">eclipse openJPA plugin</a> - the plugin UI hides the last
 * check button and seems not to work properly.
 * <li>Trying the <a href="http://openjpa.apache.org/enhancement-with-eclipse.html">recommended ant builder</a>. I would have to change the
 * project structure
 * <li>Adding <a href="http://webspherepersistence.blogspot.com.br/2009/02/openjpa-enhancement.html">a user-agent parameter to the VM</a>
 * - but at the time of this writing, TomEE 1.5.2 (the "stable" one) had a bug that the javaagent doesn't work (according to Romain), so
 * they fixed in the 1.6.0 trunk, which does not seems acceptable for any project that goes in production for a client.
 * <li>Switching to IBM JDK 7 (J9) virtual machines. Although they default to subclass for enhancement, which is not recommended, the
 * system seems to work with them, for both Windows and Linux, but the gotcha here is that for Windows, IBM distributes the JDK in a
 * "<a href="http://www.ibm.com/developerworks/java/jdk/eclipse/">IBM Development Package for Eclipse</a>" which deals with the problem gracefully
 * . I consider this as an acceptable alternative, but remember that using the IBM JDK is no the same
 * as actually enhancing the classes, as recommended by OpenJPA.
 * </ol>
 *
 * <p>So please forgive me if this procedure is cumbersome.
 *
 * <p>Now, before running standalone tests and migration procedures from main() methods or from JUnit @Test annotated methods, you must
 * run this procedure below to enhance the classes. The same for the web app, but using the destination path used by the server you're
 * running from inside eclipse. And of course, before deploying...
 *
 * <p>September 2013
 *
 *
 */
public class Enhancer {

    public static void main(String[] args) {

        String baseClassDir = "/path/to/eclipse/workspace/.metadata/.plugins/org.eclipse.wst.server.core/tmp0/wtpwebapps/yourapp/WEB-INF/classes/";
//        String baseClassDir = "/path/to/eclipse/workspace/yourapp/build/classes/";
        Collection<File> files = FileUtils.listFiles(new File(baseClassDir), null, true);
        for(File file:files){
            String[] a = new String[3];
            a[0] = "-d";
            a[1] = baseClassDir;
            a[2] = file.getAbsolutePath();
            org.apache.openjpa.enhance.PCEnhancer.main(a);
        }
    }

}

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.