/**
 * Simple Database Client for Java
 *
 */

package org.homelinux.largo.sql.client;

import java.util.List;
import java.util.ArrayList;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

import javax.naming.Context;
import javax.naming.NamingException;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class DBClient {
	private Connection connection;
	private DatabaseMetaData metadata;
	private Statement statement;
	private ResultSet resultset;

	private String driver, username, password;
	private String dbURL;

	/**
	 * Constructor to connect to a database with a specific driver
	 * and connection string
	 */
	public DBClient (String driver, String dbURL, String username, String password) throws ClassNotFoundException, SQLException {
		this.driver = driver;
		this.dbURL = dbURL;

		Class.forName (driver);
		connect (username, password);
	}

	/**
	 * Constructor to connect to a database with a specific driver
	 * and connection string
	 */
	public DBClient (String driver, String dbURL) throws ClassNotFoundException, SQLException {
		this (driver, dbURL, null, null);
	}

	/**
	 * Default Constructor to connect to my database
	 */
	public DBClient () throws ClassNotFoundException, SQLException {
		this ("org.apache.derby.jdbc.EmbeddedDriver", "jdbc:derby:clubstatistik", null, null);
	}

	/**
	 * Constructor to connect to a DataSource on an AppServer
	 */
	public DBClient (String jndiResource) throws NamingException, SQLException {
		Context ctx = new InitialContext ();
		DataSource ds = (DataSource) ctx.lookup (jndiResource);

		connection = ds.getConnection ();
		statement = connection.createStatement ();
	}

        protected void connect(String user, String pass) throws SQLException {
		assert (connection == null) : "CONNECT: Already connected";

		this.username = user;
		this.password = pass;

		connection = DriverManager.getConnection (dbURL, username, password);
		metadata = connection.getMetaData();
		statement = connection.createStatement ();
	}

	public void disconnect () throws SQLException {
		if (connection == null)
			throw new SQLException ("DISCONNECT: Not connected");
		else
			connection.close ();
	}

	public String getColumn (int pos) throws SQLException {
		return resultset.getString (pos);
	}

	public String getColumn (String name) throws SQLException {
		return resultset.getString (name);
	}

	public List<String> getTables() throws SQLException {
		List<String> list = new ArrayList<String>();
		resultset = metadata.getTables(null, null, "%", null);

		while (resultset.next()) {
			list.add(resultset.getString("TABLE_NAME"));
		}

		return list;
	}

	public List<String> getColumns(String table) throws SQLException {
		List<String> list = new ArrayList<String>();
		resultset = metadata.getColumns(null, null, table, null);

		while (resultset.next()) {
			list.add(resultset.getString("COLUMN_NAME"));
		}

		return list;
	}

	public Connection getConnection () {
		return connection;
	}

	public String getDbURL () {
		return dbURL;
	}

	public String getDriver () {
		return driver;
	}

	public String getPassword () {
		return password;
	}

	public String getUsername () {
		return username;
	}

	public boolean hasNext () throws SQLException {
		return resultset.next ();
	}

	public void query (String query) throws SQLException {
		resultset = statement.executeQuery (query);
	}

	/**
         * returns the number of updated rows
         */
	public int update (String update) throws SQLException {
		return statement.executeUpdate (update);
	}

	public void setConnection (Connection connection) {
		this.connection = connection;
	}

	public void setDbURL (String dbURL) {
		this.dbURL = dbURL;
	}

	public void setDriver (String driver) {
		this.driver = driver;
	}

	public void setPassword (String password) {
		this.password = password;
	}

	public void setUsername (String username) {
		this.username = username;
	}
}