/* Imports removed for brevity */

/*
 *  Usage Examples:
 *
 * [{JDBCPlugin WHERE 
 *   dsn='java:/jdbc/MyDS',
 *   user='my_user',
 *   pw='my_pw',
 *   sql='select email,registrationDate from users',
 *   pre='<table border="1" width="100%"><tr><th>Email</th><th>Registration Date</th></tr>',
 *   resultSet='<tr><td>{0}</td><td>{1,date}</td></tr>',
 *   post='</table>'}]
 *
 * [{JDBCPlugin WHERE 
 *   dsn='java:/jdbc/MyDS',
 *   user='my_user',
 *   pw='my_pw',
 *   sql='select email from users',
 *   pre='<ul>',
 *   resultSet='<li>{0}</li>',
 *   post='</ul>'}]
 */
public class JDBCPlugin implements WikiPlugin
{
  private static Log log = LogFactory.getLog(JDBCPlugin.class);


  public String execute(
    WikiContext wikiContext,
    Map params) throws PluginException
  {
    StringBuffer buffer = new StringBuffer();
    String dsn = (String)params.get("dsn");
    String username = (String)params.get("user");
    String password = (String)params.get("pw");
    String sql = (String)params.get("sql");
    String pattern = (String)params.get("resultSet");
    String pre = (String)params.get("pre");
    String post = (String)params.get("post");
    MessageFormat format = new MessageFormat(pattern);

    if (pre != null)
      buffer.append(pre);

    try
    {
      Context context = new InitialContext();
      Object obj = context.lookup(dsn);
      DataSource dataSource = (DataSource)PortableRemoteObject.narrow(obj, DataSource.class);
      Connection connection = null;

      try
      {
        Statement statement = null;

        connection = dataSource.getConnection(username, password);

        try
        {
          ResultSet resultSet = null;

          statement = connection.createStatement();

          try
          {
            resultSet = statement.executeQuery(sql);

            while (resultSet.next())
            {
              int columnCount = resultSet.getMetaData().getColumnCount();
              Object[] arguments = new Object[columnCount];

              for (int x = 0; x < arguments.length; x++)
              {
                // Remember that JDBC columns are 1-based
                arguments[x] = resultSet.getObject(x + 1);
              }

              format.format(arguments, buffer, null);
            }
          }
          finally
          {
            if (resultSet != null)
              resultSet.close();
          }
        }
        finally
        {
          if (statement != null)
            statement.close();
        }
      }
      finally
      {
        if (connection != null)
          connection.close();
      }

      if (post != null)
        buffer.append(post);

      return buffer.toString();
    }
    catch (NamingException e)
    {
      throw new PluginException("dsn not found: " + dsn, e);
    }
    catch (SQLException e)
    {
      throw new PluginException("sql=" + sql, e);
    }
  }

}