//====================================================================
// ConnectionExample.java
//====================================================================

package com.evident.example;

import java.sql.*;
import javax.sql.*;
import javax.naming.*;
import com.evident.database.*;

/**
* Provides an example of how to use a connection manager to manage a
* the lifecycle of a database connection and separate out the usage.
*/
public class ConnectionExample {

	/**
	* A simple account object for demo purposes.
	*/
	public static class Account {
		public int id;
		public String name;
		public double balance;
		public String note;
	}

	/**
	* Executes the example given a data source name.
	* @param args the command line parameters, the first of which
	* must be a data source name.
	*/
	public static void main( String[] args ) throws Exception {
		if (args.length == 0) return;
		ConnectionExample example = 
			new ConnectionExample( getSourceNamed( args[0] ) );

		// create an account
		Account account = new Account();
		account.id = 5;
		account.name = "John Doe";
		account.balance = 500.00;
		account.note = "Opening Balance";

		// insert and query the account
		example.insertAccount( account );
		account = example.queryAccount( account.id );
		System.out.println( 
			account.name + " balance = " + account.balance
		);
	}

	/**
	* Returns the data source identified by the supplied (sourceName).
	* @param sourceName identifies a data source.
	*/
	public static DataSource 
	getSourceNamed( String sourceName ) throws NamingException {
		InitialContext context = new InitialContext();
		return (DataSource) context.lookup( sourceName );
	}


	/** Manages database connection lifecycles. */
	protected ConnectionManager connectionManager;

	/**
	* Constructs a new ConnectionExample.
	* @param source a source for database connections.
	*/
	public ConnectionExample( DataSource source ) {
		connectionManager = new ConnectionManager( source );
	}

	/**
	* Inserts an (account) into the database using a connection managed
	* by the (connectionManager).
	* @param account the account to insert.
	*/
	public void insertAccount( final Account account ) {
		connectionManager.connectUsing(
			new ConnectionUsage( ConnectionUsage.WITHOUT_AUTO_COMMIT ) {
				public Object use( Connection connection ) 
				throws SQLException {
					insertAccount(
						account,
						connection, 
						createInsert( connection ) 
					);
					// commit the insert explicitly
					connection.commit();
					return null;
				}
			}
		);
	}

	/**
	* Queries an account in the database using a connection managed
	* by the (connectionManager).
	* @param accountID identifies an account.
	* @return the selected account, or null if not found.
	*/
	public Account queryAccount( final int accountID ) {
		return (Account) connectionManager.connectUsing(
			new ConnectionUsage() {
				public Object use( Connection connection ) 
				throws SQLException {
					return queryAccount( 
						accountID,
						connection, 
						createSelect( connection ) 
					);
				}
			}
		);
	}


	/** The SQL insert statement to be prepared. */
	protected static final String PREPARED_INSERT =
		"insert into account (id,name,balance,note) values (?,?,?,?)";

	/**
	* Returns a prepared insert statement given a (connection).
	* @param connection a database connection.
	* @exception java.sql.SQLException if the statement creation fails.
	*/
	protected PreparedStatement 
	createInsert( Connection connection ) throws SQLException {
		return connection.prepareStatement( PREPARED_INSERT );
	}


	/** The SQL select statement to be prepared. */
	protected static final String PREPARED_SELECT =
		"select name from account where id=?";

	/**
	* Returns a prepared select statement given a (connection).
	* @param connection a database connection.
	* @exception java.sql.SQLException if the statement creation fails.
	*/
	protected PreparedStatement 
	createSelect( Connection connection ) throws SQLException {
		return connection.prepareStatement( PREPARED_SELECT );
	}


	/**
	* Inserts an (account) into the database using the supplied (connection).
	* @param account the account to insert.
	* @param connection a database connection.
	* @param insert a prepared insert statement.
	* @exception java.sql.SQLException if the insert fails.
	*/
	protected void insertAccount( Account account,
	Connection connection, PreparedStatement insert ) throws SQLException {
		int column = 1;
		insert.setInt( column++, account.id );
		insert.setString( column++, account.name );
		insert.setDouble( column++, account.balance );
		insert.setString( column++, account.note );
		int count = insert.executeUpdate();
		insert.close();
	}

	/**
	* Queries an account in the database using the supplied (connection).
	* @param connection a database connection.
	* @param select a prepared select statement.
	* @return the selected account, or null if not found.
	* @exception java.sql.SQLException if the query fails.
	*/
	protected Account queryAccount( int accountID,
	Connection connection, PreparedStatement select ) throws SQLException {
		select.setInt( 1, accountID );
		ResultSet results = select.executeQuery();
		if (results.next()) {
			int column = 1;
			Account account = new Account();
			account.id = accountID;
			account.name = results.getString( column++ );
			account.balance = results.getDouble( column++ );
			account.note = results.getString( column++ );
			return account;
		} else {
			return null;
		}
	}
}