root/tools/routingservice/trunk/src/jp/co/orkney/restlet/util/DatabaseConnection.java

package jp.co.orkney.restlet.util;

/**
 * Copyright (c) 2007 Orkney, Inc. <http://www.orkney.co.jp/>
 * 
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

import java.io.File;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.rowset.WebRowSet;

import org.antlr.stringtemplate.StringTemplate;
import org.restlet.Client;
import org.restlet.data.MediaType;
import org.restlet.data.Protocol;
import org.restlet.data.Request;
import org.restlet.data.Response;
import org.restlet.resource.Representation;
import org.restlet.resource.StringRepresentation;

import com.noelios.restlet.ext.jdbc.JdbcClientHelper;
import com.noelios.restlet.ext.jdbc.RowSetRepresentation;
import com.sun.org.apache.xalan.internal.xsltc.runtime.Hashtable;

import jp.co.orkney.restlet.util.Log;

/**
 * <b>To access to a database</b>
 * <p>
 * Provides methods to open, request and close a database connection <br />
 * DatabaseConnection object is characterized by the following information:
 * <ul>
 * <li>A Connection object</li>
 * <li>A Statement object</li>
 * <li>A boolean to know if the connection is started or not</li>
 * <li>An DatabaseConfiguration object to provide information about "how to
 * connect"</li>
 * </ul>
 * </p>
 * 
 * @author Matthieu Bilbille - Orkney Inc.
 * @version 1.0
 * @see Connection
 * @see Statement
 * @see DatabaseConfiguration
 */
public class DatabaseConnection
{
    private IOHelper ioHelper;

    private Log log;

    private DatabaseConfiguration databaseConfiguration;

    private Configuration configuration;

    /**
         * Constructor DatabaseConnection
         * <p>
         * Creates a new DatabaseConnection object with the specified
         * Configuration object
         * </p>
         * 
         * @param configuration
         *                The current WebRouting Service's configuration
         */
    public DatabaseConnection(Configuration configuration)
    {
        this.configuration = configuration;
        log = configuration.createLog();
        // databaseConfiguration =
        // configuration.getService().getDatabaseConfiguration();
        ioHelper = new IOHelper(configuration);
    }

    /**
         * Creates and sends the query using pool connection
         * 
         * @param query
         *                theSQL query
         * @return the result of the request
         */
    public ResultSet getResult(Service service, String query)
    {
        StringTemplate queryTemplate = new StringTemplate(ioHelper
                .loadFile(new File(configuration.getUrlXMLTemplateQuery())));

        databaseConfiguration = service.getDatabaseConfiguration();

        // Makes the SQL query
        queryTemplate.setAttribute("user", databaseConfiguration.getUser());
        queryTemplate.setAttribute("password", databaseConfiguration
                .getPassword());
        queryTemplate.setAttribute("query", query);

        ResultSet result = null;

        Client client = new Client(Protocol.JDBC);
        JdbcClientHelper helper = new JdbcClientHelper(client);
        try
        {
            Class.forName(databaseConfiguration.getDriver());
        }
        catch (ClassNotFoundException e)
        {
            log.write("--ERROR: Can not find the driver", 0);
        }
        Representation req = new StringRepresentation(queryTemplate.toString()
                .replace("&", "&amp;"), MediaType.TEXT_XML);
        Request request = JdbcClientHelper.create(databaseConfiguration
                .getURL(), req);
        Response response = new Response(request);
        helper.handle(request, response);

        WebRowSet rowSet = ((RowSetRepresentation) response.getEntity())
                .getWebRowSet();

        try
        {
            result = rowSet;
        }
        catch (Exception ex)
        {
            log
                    .write(
                            "--ERROR: Can not cast the result from WebRowSet to ResultSet",
                            0);
        }
        return result;
    }

    public void fillKeysTable(Service service, Hashtable keys)
            throws SQLException
    {
    String q = service.getSQL()[0];     
        ResultSet rs = getResult(service, q);
        while (rs != null && rs.next())
            keys.put(rs.getString("client_id"), rs
                    .getString("key"));
    }

    public Point transform(Service service, Point point, String sridIn, String sridOut)
            throws SQLException
    {
        Point res = point;

        double x = point.getX().doubleValue();
        double y = point.getY().doubleValue();
        
        String query = "select x(a.g), y(a.g) from (select transform(geometryfromtext('POINT("
                + x + " " + y + ")', " + sridIn + "), " + sridOut
                + ") as g) as a";
        
        this.log.write(query, 2);

        ResultSet rs = getResult(service, query);

        while (rs != null && rs.next())
            res = new Point(BigDecimal.valueOf(rs.getDouble("x")),
                    BigDecimal.valueOf(rs.getDouble("y")));

        return res;
    }
}