SqlDataLoader

This is a multi-part message in MIME format.
--------------000608040408050908050209
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Hello,

Please find as attachement a very simple SqlDataLoader. All the 
configuration still need to be written. I've used it for a simple use, 
by hardcoding the config data (hugly, but I was in hury).

If it can benefit for anyone, and if somebody feel to write the 
configuration code, it would be nice ;-)

This code is totaly free of any right, you could use it as you want.

To use it, you just need to write
<#assign database = pp.loadData('SqlDataLoader') >

and then,
${database.tableName} is a list with all rows of table "tableName"

so you could write :
<#list database.tableName as row>
${row.fieldName}
</#list>

to retrieve your data.

Carl

--------------000608040408050908050209
Content-Type: text/plain;
 name="SqlDataLoader.java"
Content-Disposition: inline;
 filename="SqlDataLoader.java"
Content-Transfer-Encoding: quoted-printable


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import fmpp.Engine;
import fmpp.tdd.DataLoader;

/**
 *=20
 */
public class SqlDataLoader implements DataLoader {

    private Engine engine;

    private List args;

    private String driverClassName =3D "org.gjt.mm.mysql.Driver";

    private String url =3D "jdbc:mysql://192.168.1.2";

    private String login =3D "root";

    private String pwd =3D "azerty";

    private int port =3D 3306;

    private String database =3D "prospection";

    /**
     * @see fmpp.tdd.DataLoader#load(fmpp.Engine, java.util.List)
     */
    public Object load(Engine engine, List args) throws Exception {

        this.engine =3D engine;
        this.args =3D args;

        /*
         * if (args.size() < 4) { throw new IllegalArgumentException( "At=
 least
         * 4 argument (driver, url, login, pass) needed"); } Object obj =3D
         * args.get(0); if (!(obj instanceof Url)) { throw new
         * IllegalArgumentException( "The 1st argument (url) must be a
         * string."); } this.url =3D (String) args.get(0);
         *=20
         * Object obj =3D args.get(0); if (!(obj instanceof Url)) { throw=
 new
         * IllegalArgumentException( "The 1st argument (url) must be a
         * string."); } this.url =3D (String) args.get(0);
         *=20
         * obj =3D args.get(1); if (!(obj instanceof String)) { throw new
         * IllegalArgumentException( "The 1st argument (login) must be a
         * string."); } this.login =3D (String) args.get(1);
         *=20
         * obj =3D args.get(2); if (!(obj instanceof String)) { throw new
         * IllegalArgumentException( "The 1st argument (pass) must be a
         * string."); } this.pwd =3D (String) args.get(2);
         *=20
         * obj =3D args.get(0); if (!(obj instanceof String)) { throw new
         * IllegalArgumentException( "The 1st argument (file name) must b=
e a
         * string."); }
         */

        // establish a JDBC connection
        System.setProperty("jdbc.drivers", this.driverClassName);
        Connection connection =3D DriverManager.getConnection(this.url + =
":"
                + this.port + "/" + this.database, this.login, this.pwd);

        try {
            return load(connection);
        }
        finally {
            connection.close();
        }
    }

    protected Object load(Connection connection) throws Exception {
        HashMap database =3D new HashMap();

        // r=E9cup=E9ration des tables
        ResultSet rs =3D connection.getMetaData().getTables(this.database=
, null,
                null, null);
        while (rs.next()) {
            database.put(rs.getString(3), new ArrayList());
        }
        rs.close();

        // r=E9cup=E9ration des donn=E9es
        Iterator it =3D database.keySet().iterator();
        while (it.hasNext()) {
            String tableName =3D (String) it.next();
            List table =3D (List) database.get(tableName);

            // r=E9cup=E9ration de la structure de la table
            rs =3D connection.getMetaData().getColumns(this.database, nul=
l,
                    tableName, null);
            List tableStructure =3D new ArrayList();
            while (rs.next()) {
                tableStructure.add(rs.getString(4));
            }
            String[] fieldNames =3D (String[]) tableStructure
                    .toArray(new String[] {});

            // r=E9cup=E9ration des donn=E9es
            String sql =3D "SELECT * FROM " + tableName + " ;";
            Statement stmt =3D connection.createStatement();
            rs =3D stmt.executeQuery(sql);
            if (rs.isBeforeFirst()) {
                while (rs.next()) {
                    HashMap row =3D new HashMap();
                    for (int i =3D 0; i < fieldNames.length; i++) {
                        String fieldName =3D fieldNames[i];
                        row.put(fieldName, rs.getObject(i+1));
                    }
                    table.add(row);
                }
            }
            stmt.close();
        }
        return database;
    }

}
--------------000608040408050908050209--



-------------------------------------------------------
This SF.Net email is sponsored by: IntelliVIEW -- Interactive Reporting
Tool for open source databases. Create drag-&-drop reports. Save time
by over 75%! Publish reports on the web. Export to DOC, XLS, RTF, etc.
Download a FREE copy at http://www.intelliview.com/go/osdn_nl