import javax.servlet.http.HttpSessionBindingListener ;
import javax.servlet.http.HttpSessionBindingEvent ;

import java.sql.* ;


// Implements `HttpSessionBindingListener' to ensure connection is
// closed promptly when session ends.

public class DBSessionBean implements HttpSessionBindingListener, DBConstants {

    static {
        System.setProperty("jdbc.drivers", "oracle.jdbc.driver.OracleDriver");
    }

    public DBSessionBean() throws SQLException {
        conn = DriverManager.getConnection(url, username, password);
        stat = conn.createStatement();
    }

    public void valueBound(HttpSessionBindingEvent evt) {}

    public void valueUnbound(HttpSessionBindingEvent evt) {
        System.out.println("Closing Oracle connection...") ;  // debug

        try {
           conn.close() ;
        } catch (SQLException e) {}  // Not much to be done about it.
    }


    public String getMenu() throws SQLException {
        // Extract keys from table

        ResultSet rs = stat.executeQuery("SELECT login FROM " + table) ;

        // Print selection form

        StringBuffer menu = new StringBuffer() ;
        menu.append("<select name=key size=15>") ;
        while(rs.next())
            menu.append("<option> " + rs.getString(1)) ;
        menu.append("</select>") ;

        return menu.toString() ;
    }

    public void setKey(String key) throws SQLException {

        // Query the database and extract the fields

        System.out.println("SELECT * FROM " + table + " " +
                           "WHERE login='" + key + "'") ;

        ResultSet rs = stat.executeQuery(
                           "SELECT * FROM " + table + " " +
                           "WHERE login='" + key + "'") ;

        if(rs.next()) {
            login      = rs.getString(1) ;
            lastname   = rs.getString(2) ;
            firstnames = rs.getString(3) ;
            email      = rs.getString(4) ;
            dept       = rs.getString(5) ;
        } else
            throw new SQLException("DBSessionBean: Record not found") ;

        this.key = key ;
    }

    public void setSaved(boolean saved) throws SQLException {
        if(saved) {
            stat.executeUpdate("UPDATE " + table + " SET " +
                               "login='"      + login      + "', " +
                               "lastname='"   + lastname   + "', " +
                               "firstnames='" + firstnames + "', " +
                               "email='"      + email      + "', " +
                               "dept='"       + dept       + "' " +
                               "WHERE login='" + key + "'") ;
        }
    }

    public void setLogin(String login) {
        this.login = login ;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname ;
    }

    public void setFirstnames(String firstnames) {
            System.out.println("firstnames = " + firstnames) ;
        this.firstnames = firstnames ;
    }

    public void setEmail(String email) {
        this.email = email ;
    }

    public void setDept(String dept) {
        this.dept = dept ;
    }

    public String getLogin() {
        return login ;
    }

    public String getLastname() {
        return lastname ;
    }

    public String getFirstnames() {
        return firstnames ;
    }

    public String getEmail() {
        return email ;
    }

    public String getDept() {
        return dept ;
    }

    private String login, lastname, firstnames, email, dept ;
    
    private String key ;

    private Connection conn ;
    private Statement stat ;
}