/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package jdbc_project;
import java.sql.*;
import java.util.Scanner;
/**
 *
 * @author djbla
 */
public class JDBC_Project
{
   //  Database credentials
    static String USER;
    static String PASS;
    static String DBNAME;
    //This is the specification for the printout that I'm doing:
    //each % denotes the start of a new field.
    //The - denotes left justification.
    //The number indicates how wide to make the field.
    //The "s" denotes that it's a string.  All of our output in this test are
    //strings, but that won't always be the case.
    static final String displayFormat="%-5s%-15s%-15s%-15s\n";
// JDBC driver name and database URL
    static final String JDBC_DRIVER = "org.apache.derby.jdbc.ClientDriver";
    static String DB_URL = "jdbc:derby://localhost:1527/";
//            + "testdb;user=";
/**
 * Takes the input string and outputs "N/A" if the string is empty or null.
 * @param input The string to be mapped.
 * @return  Either the input string or "N/A" as appropriate.
 */
    public static String dispNull (String input)
    {
        //because of short circuiting, if it's null, it never checks the length.
        if (input == null || input.length() == 0)
            return "N/A";
        else
            return input;
    }

    public static void main(String[] args)
    {
        //Prompt the user for the database name, and the credentials.
       //If your database has no credentials, you can update this code to
        //remove that from the connection string.
        Scanner in = new Scanner(System.in);
        System.out.print("Name of the database (not the user account): ");
        DBNAME = in.nextLine();
        System.out.print("Database user name: ");
        USER = in.nextLine();
        System.out.print("Database password: ");
        PASS = in.nextLine();
        //Constructing the database URL connection string
        DB_URL = DB_URL + DBNAME + ";user="+ USER + ";password=" + PASS;
        Connection conn = null; //initialize the connection
        Statement stmt = null;  //initialize the statement that we're using
        try {
            //STEP 2: Register JDBC driver
            Class.forName("org.apache.derby.jdbc.ClientDriver");



            Scanner input=new Scanner(System.in);
            int option=input.nextInt();
            //string value=input.nextString();
            //STEP 3: Open a connection
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL);
            System.out.println("Enter a number to pick an option.");
            System.out.println("1. List all writing groups");
            System.out.println("2. List all publishers");
            System.out.println("3. List all book titles");
            System.out.println("4. List data for a group specified by user");
            System.out.println("5. List data for a publisher specified by user");
            System.out.println("6. List data for a book specified by user");
            System.out.println("7. Insert a new book");
            System.out.println("8. Insert a new publisher and update all book "
                    + "published by one publisher to be the new publisher");
            System.out.println("9.Remove a book specified by user.");
            System.out.println("10. Exit");

            //STEP 4: Execute a query
            //System.out.println("Creating statement...");
           // stmt = conn.createStatement();
            ResultSet rs = "";
            String sql,sql2,sql3,sql4,sql5,sql6,sql7,sql8,sql9;
            switch(option)
            {
                case 1:
                    sql = "SELECT * FROM WritingGroups";
                    rs = stmt.executeQuery(sql);
                    break;
                case 2:
                    sql2 = "SELECT * FROM Publishers";
                    rs = stmt.executeQuery(sql2);
                    break;
                case 3:
                    sql3 = "SELECT * FROM Books";
                    break;
                case 4:
                    PreparedStatement stmt4 = conn.prepareStatement("SELECT * FROM WritingGroups WHERE GroupName = ?");
                    sql4 = input.next();
                    break;
                case 5:
                    PreparedStatement stmt5 = conn.prepareStatement("SELECT * FROM Publishers WHERE PublisherName = ?");
                    sql5 =input.next();
                case 6:
                    PreparedStatement stmt6 = conn.prepareStatement("SELECT * FROM Books WHERE BookTitle = ?");
                    sql6 = input.next();
                case 7:
                    PreparedStatement stmt7 = conn.prepareStatement("INSERT INTO Books(BookTitle, YearPublished, NumberPages, GroupName VALUES BookTitle = (?,?,?,?)");
                    sql7 = input.next();
                case 8:
                    PreparedStatement stmt8 = conn.prepareStatement("");
                    sql8 = "";
                case 9:
                    System.out.println("Enter book title for deletion");
                    PreparedStatement stmt9 = conn.prepareStatement("DELETE FROM Books WHERE BookTitle=?");
                    sql9 = input.next();
                case 10:
                default:



            //STEP 5: Extract data from result set
            System.out.printf(displayFormat, "ID", "First Name", "Last Name", "Phone #");
            while (rs.next())
            {
                //Retrieve by column name
                String id = rs.getString("au_id");
                String phone = rs.getString("phone");
                String first = rs.getString("au_fname");
                String last = rs.getString("au_lname");

                //Display values
                System.out.printf(displayFormat,
                        dispNull(id), dispNull(first), dispNull(last), dispNull(phone));
            }
            //STEP 6: Clean-up environment
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            //Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            //Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            //finally block used to close resources
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException se2) {
            }// nothing we can do
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException se) {
                se.printStackTrace();
            }//end finally try
        }//end try
        System.out.println("Goodbye!");
    }//end main

}//end project