import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * A simple example of using JDBC to connect to and query a database
 * Note that this requires running with a jar file available 
 * from postgresql. Compiles file without that. 
 * So to run, from the command line:
 *    java -cp postgresql-42.3.1.jar:. GTJDBC
 * assuming that postgresql-42.3.1.jar is in the current directory
 * @author gtowell
 * Created: Jan 29, 2022
 * Modified: Jan 2024
 * 
 * NOTE: THe univ query IS VUNERABLE TO SQL injection attacks!
 * see https://www.digitalocean.com/community/tutorials/sql-injection-in-java
 * 
 * the rocket query is not as it uses formatting via the PreparedStatement
 */
public class GTJDBC{
    
    // first part of the connection parameters
    // assumes that the DB server is listening to port5432 on the localhost
    // and that the db to connect to is named sakila
    //private final String url = "jdbc:postgresql://localhost:5432/sakila";
    //private final String url = "jdbc:postgresql://localhost:5432/univ";
    //private final String url = "jdbc:postgresql://localhost:5432/rocket";
    // the user name
    private final String user = "dbuser";
    // the password -- YES in clear text!!!
    private final String password = "12345678";

    private static String DB_NAME = "rocket";
    
    /**
    * Connect to the PostgreSQL database
    *
    * @return a Connection object
    */
    public Connection connect(String dbName) {
        Connection conn = null;
        try {
            String url = "jdbc:postgresql://localhost:5432/" + dbName;
            conn = DriverManager.getConnection(url, user, password);
            System.out.println("Connected to the PostgreSQL server successfully.");
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }
        return conn;
    }
    
    /**
    * @param args the command line arguments
    */
    public static void main(String[] args) {
        if (args.length > 0) {
            DB_NAME = args[0];
        }
        GTJDBC app = new GTJDBC();
        Connection conn = app.connect(DB_NAME);
        // with the connection established, do a query.
        try {
            Statement st = conn.createStatement();
            ResultSet rs = null;
            switch (DB_NAME) {
                case "sakila":
                rs = st.executeQuery("SELECT first_name, last_name from actor where last_name like 'A%'");
                break;
            case "univ":
                int courseNum = Integer.parseInt(args[1]);
                rs = st.executeQuery(String.format("SELECT course_id,title, dept_name, credits FROM course WHERE cast(course_id as int)>%d LIMIT 10", courseNum));
                break;
                case "rocket":
                default:
                    double lati = Double.parseDouble(args[1]);
                PreparedStatement ps = conn.prepareStatement("Select sitecode, latitude from site where latitude>?");
                ps.setDouble(1, lati);
                rs = ps.executeQuery();
                break;
            }
            while (rs.next()) {
                // can get by column position or coulmn name
                switch (DB_NAME) {
                    case "sakila":
                        System.out.format("first %s last:%s\n", rs.getString("first_name"), rs.getString("last_name"));
                    break;
                    case "univ":
                    System.out.format("c: %s t:%s\n", rs.getString(0), rs.getString(1));break;
                    case "rocket":
                    default:
                    System.out.format("site %20s latit:%.1f\n", rs.getString("sitecode"), rs.getDouble("latitude"));
                    break;
                }
            }
        } catch (Exception ee) {
            System.err.println(ee);
            ee.printStackTrace();
        }
        
    }
}