package com.reumann.examples;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class PersistenceJDBC {

    public List getSystems() {
        List<NPPSystem> results = new ArrayList<NPPSystem>(); //good practice to return empty collections, not null
        Connection conn = null;
        try {
            Class.forName("net.sourceforge.jtds.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection("jdbc:jtds:sybase://d1npp.nielsenmedia.com:2025;DatabaseName=NPP_GUI", "npp_user", "npp_user");
            Statement stmt =  conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT nppSystemID, statusCodeID,  nppSystemName, archiveDays, versionNumber, internalModeFlag  FROM NPPSystem");
            while( rs.next() ) {
                results.add( new NPPSystem( rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getInt(4), rs.getString(5), rs.getString(6) ) );
            }
            //show results with for loop
            for(NPPSystem system: results ) {
                System.out.print("nppSystemID: "+ system.getNppSystemID() );
                System.out.println(" --- nppSystemName: "+ system.getNppSystemName() );
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if ( conn != null ) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
        return results;
    }

}

class NPPSystem {
    /*
     * In theory you should be able to avoid getters and just have public properties, but many frameworks and usages actually
     * look to call the getters by reflection, so you really need the getters. For example, EL expressions
     * in JSP pages, need to have a getter backed bean, not just properties (unless the spec recently changed
     * that I'm unaware of
     */
    private int nppSystemID;
    private int statusCodeID;
    private String nppSystemName;
    private int archiveDays;
    private String versionNumber;
    private String internalModeFlag;

    /* note that these have to be set in the correct order, and if you only want to se a few of the properties, you'll need
     * extra constructors, or just rely on setting properties manually. Compare this to groovy which has implied types of constructors that
     * you can just call like NPPSystem(nppSystemID: 4, nppSystemName: "foobar" )
     * I can just quickly set the fields I want in an object (in any order) using the above syntax without even needing a constructor
     */
    public NPPSystem(int nppSystemID, int statusCodeID, String nppSystemName, int archiveDays, String versionNumber, String internalModeFlag) {
        this.nppSystemID = nppSystemID;
        this.statusCodeID = statusCodeID;
        this.nppSystemName = nppSystemName;
        this.archiveDays = archiveDays;
        this.versionNumber = versionNumber;
        this.internalModeFlag = internalModeFlag;
    }


    public int getArchiveDays() {
        return archiveDays;
    }

    public void setArchiveDays(int archiveDays) {
        this.archiveDays = archiveDays;
    }

    public String getInternalModeFlag() {
        return internalModeFlag;
    }

    public void setInternalModeFlag(String internalModeFlag) {
        this.internalModeFlag = internalModeFlag;
    }

    public int getNppSystemID() {
        return nppSystemID;
    }

    public void setNppSystemID(int nppSystemID) {
        this.nppSystemID = nppSystemID;
    }

    public String getNppSystemName() {
        return nppSystemName;
    }

    public void setNppSystemName(String nppSystemName) {
        this.nppSystemName = nppSystemName;
    }

    public int getStatusCodeID() {
        return statusCodeID;
    }

    public void setStatusCodeID(int statusCodeID) {
        this.statusCodeID = statusCodeID;
    }

    public String getVersionNumber() {
        return versionNumber;
    }

    public void setVersionNumber(String versionNumber) {
        this.versionNumber = versionNumber;
    }


}