/*vvv WinStudent List vvv*/

import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.util.*;

public class WinStudentList implements ActionListener{
    private JLabel lNo;
    private JLabel lFName;
    private JLabel lLName;
    private JTextField tNo;
    private JTextField tFName;
    private JTextField tLName;
    private JButton next;
    private JButton prev;
    private JFrame win;
    private ArrayList<Student> list;
    private DBAccess db;
    private int counter=0;

    public void actionPerformed(ActionEvent e){
        if(e.getActionCommand().equals("next")){
            if(list.size()>counter)
            counter++;
        }else
        if(e.getActionCommand().equals("prev")){
            counter--;
        }
        tFName.setText(list.get(counter).getF());
        tLName.setText(list.get(counter).getL());
        tNo.setText(""+list.get(counter).getN());
    }

    public WinStudentList(){
        list = new ArrayList<Student>();
        db = new DBAccess();
        list = db.getStudent();
        lNo=new JLabel("No: ");
        lFName=new JLabel("First: ");
        lLName=new JLabel("Last: ");
        tNo=new JTextField();
        tFName=new JTextField();
        tLName=new JTextField();
        next=new JButton("->");
        prev=new JButton("<-");
        next.addActionListener(this);
        prev.addActionListener(this);
        next.setActionCommand("next");
        prev.setActionCommand("prev");
        win=new JFrame();
        win.setLayout(new GridLayout(4,2));
        win.add(lNo);
        win.add(tNo);
        win.add(lFName);
        win.add(tFName);
        win.add(lLName);
        win.add(tLName);
        win.add(prev);
        win.add(next);
        tNo.setEditable(false);
        tFName.setEditable(false);
        tLName.setEditable(false);
        tFName.setText(list.get(0).getF());
        tLName.setText(list.get(0).getL());
        tNo.setText(""+list.get(0).getN());
        win.pack();
        win.setVisible(true);
        win.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    }
    public static void main(String args[]){
        new WinStudentList();
    }
}

/*vvv DBAccess vvv*/
import java.sql.*;
import oracle.jdbc.pool.*;
import java.io.*;
import java.util.*;

public class DBAccess{

    //The Connect String
    static final String connect_string=
                    "jdbc:oracle:thin:system/db_147@//localhost:1521/XE";
    //The query we will execute
     String query = "select 'Hello JDBC: ' || sysdate from dual";
    //The Connection to the database
    private Connection conn;
/*
    public static void main(String args[]){
        new DBAccess();
    }
*/
    public DBAccess(){
        try{
            //Seeif we need to open the connection to the database
            if(conn==null){
                //Create a OracleDataSOurce instance and set URL
                OracleDataSource ods = new OracleDataSource();
                ods.setURL(connect_string);
                //Connect to the database
                System.out.println("Connecting to " + connect_string + "\n");
                conn=ods.getConnection();
                System.out.println("Connected\n");
            }
            //Create a statement
            Statement stmt = conn.createStatement();
            //Execute the query
            System.out.println("Executing query" + query + "\n");
            ResultSet rset = stmt.executeQuery(query);
            //Dump the result
            while(rset.next()){
                System.out.println(rset.getString (1) + "\n");
                //System.out.println(rset.getString("FName"));
                //System.out.println(rset.getString (" LName") + "\n");
            }
            //We're done
            System.out.println("done.\n");
        }
        catch(Exception e){
            System.out.println(e.getMessage() + "\n");
        }
    }//DB constructor

    public void addStudent(String No, String Fname, String Lname){
        //Create a statement
        try{
            Statement stmt = conn.createStatement();
            query="insert into students(SNO, FNAME, LNAME) values("+No+",'"+Fname+"','"+Lname+"')";
            //Execute the query
            System.out.println("Executing query" + query + "\n");
            stmt.executeQuery(query);
        }catch(Exception e){
            System.out.println(e);
        }
    }

    public ArrayList getStudent(){
        ArrayList<Student> list= new ArrayList<Student>();
        try{
            Statement stmt = conn.createStatement();
            query="select sno, fname, lname from students";
            ResultSet rset=stmt.executeQuery(query);
            Student s;
            while(rset.next()){
                s = new Student(
                        rset.getInt("SNO"),
                        rset.getString("FNAME"),
                        rset.getString("LNAME")
                        );
                list.add(s);
            }

        }catch(Exception e){
            System.out.println(e);
        }
        return list;
    }

}