/* SQLite Demo. Three class program
* Connnects to a SQLite database called LearnersDonations.db
* Create an array of learner class objects
 */
package sqlitedemo3;

public class SQLiteDemo3UI {

    public static void main(String[] args) {
        
        SQLiteManager sm = new SQLiteManager();
        sm.displayAll();
        String all = sm.displayAll2();
        System.out.println(all);
        sm.closeConnection();
        System.out.println("SQLite Demo 3");
    }
    
}
=============================================================
=============================================================

/* SQLite Manager containing all the methods
 */
package sqlitedemo3;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

public class SQLiteManager {
    
    private static Connection con = null;
    private static Statement state = null;
    private static ResultSet rs = null;
    private static Learners[] ln = new Learners[200];
    private static int size = 0;
    
// Constructor reads the database in and creates an array of learner objects    
public SQLiteManager() {
    getConnection();
    rs = null;

        try {
            state = con.createStatement();
            rs = state.executeQuery("SELECT * FROM tblLeaners");
            
            // Temporary field variables
            while (rs.next()) {
                int learnerID = rs.getInt("LearnerID");
                int dutyID = rs.getInt("DutyID");
                String nickName = rs.getString("Nickname");
                String firstName  = rs.getString("FirstName");;
                String surname = rs.getString("Surname");;
                int grade = rs.getInt("Grade");;
                String dob  = rs.getString("DOB");;
                String cellPhoneNumber  = rs.getString("CellphoneNumber");;
                String emailAddress = rs.getString("EmailAddress");;
                String preference = rs.getString("Preference");;
                boolean driversLicence = rs.getBoolean("DriversLicenceObtained");;
                double AmountPaid = rs.getDouble("AmountPaid");;
                
                ln[size] = new Learners(learnerID, dutyID, nickName, firstName, surname, grade, dob, cellPhoneNumber, emailAddress, preference, driversLicence, AmountPaid);
                       
                System.out.println("Success" + size);
                size++;

            } // end while
        } catch (SQLException ex) {
            System.out.println("Error");
            System.out.println(ex.getMessage());
            Logger.getLogger(SQLiteManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    
} // end constructor SQLiteManager
    
// Connect to the LearnersDonations.db database    
private void getConnection(){
    try {
        Class.forName("org.sqlite.JDBC");
        con = DriverManager.getConnection("jdbc:sqlite:LearnersDonations.db");
        System.out.println("Connection success");
    } catch (ClassNotFoundException | SQLException e) {
        System.out.println("Error%");
        System.out.println(e.getMessage());
    }
} // end getConnection


// Terminate connection to LearnersDonations.db database
public void closeConnection(){
    try {
        if (con != null) {
            con.close();
            state.close();
            rs.close();
            System.out.println("Connection closed");
        }
    } catch (SQLException ex) {
        System.out.println(ex.getMessage());
    }
} // end close connection

// Display all the learners in the database. Only a few fields
public void displayAll() {
    if (con == null) {
        getConnection();
        System.out.println("connection null");
    }
    try {
        state = con.createStatement();
        rs = state.executeQuery("SELECT firstName, Surname, Grade FROM tblLeaners");
        while (rs.next()){
           System.out.println(rs.getString("firstName") + " " + rs.getString("Surname") + " " +  rs.getString("Grade"));
        }
 
    } catch (SQLException ex) {
        System.out.println("error#");
    }
    
} // end displayAll
    
// Displays all the learners in the array of objects. All the fields
public String displayAll2() {
      String output = "";
      for(int i = 0; i < size; i++) {
         output = output + ln[i].toString();
      }  // end for
      
      return output;
      
} // end displayAll2

} // end class
===================================================================
===================================================================

/* SQLite demo
* The learner template class
 */
package sqlitedemo3;

public class Learners {
    
    private int learnerID;
    private int dutyID;
    private String nickName;
    private String firstName;
    private String surname;
    private int grade;
    private String dob;
    private String cellPhoneNumber;
    private String emailAddress;
    private String preference;
    private boolean driversLicenceObtained;
    private double amountPaid;
    
    // Construtor method
    public Learners (int id, int du, String nm, String fn, String sn, int gr, String db, String cp, String em, String pr, boolean dl, double ap){
        learnerID = id;
        dutyID = du;
        nickName = nm;
        firstName = fn;
        surname = sn;
        grade = gr;
        dob = db;
        cellPhoneNumber = cp;
        emailAddress = em;
        preference = pr;
        driversLicenceObtained = dl;
        amountPaid = ap;
        
    }
    
    // Getter methods to return data in a specific field
    // Setter methods to edit the data in a specific fiels
    public String getFirstName(){
        return firstName;
    }    
    
    public void setFirstName(String fn){
        firstName = fn;
    }     
    
    public String getSurname(){
        return surname;
    }    
    
    public void setSurname(String sn){
        surname = sn;
    }
    
    public String toString(){
        return firstName + "\t" + surname + "\t" + grade + "\t" + dob + "\t" + cellPhoneNumber + "\t" + emailAddress + "\t" + preference + "\t" + driversLicenceObtained + "\t" + amountPaid + "\n";
        
    } // end toSring
}
==========================================================================
==========================================================================