SHARE
TWEET

Untitled

a guest Mar 20th, 2017 78 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2.  
  3. import java.sql.*;
  4. import java.util.Scanner;
  5. import java.util.logging.Level;
  6. import java.util.logging.Logger;
  7.  
  8. /**
  9.  *
  10.  * @author Mimi Opkins with some tweaking from Dave Brown
  11.  */
  12. public class JDBC_Project {
  13.     //  Database credentials
  14.     static String USER;
  15.     static String PASS;
  16.     static String DBNAME;
  17.     //This is the specification for the printout that I'm doing:
  18.     //each % denotes the start of a new field.
  19.     //The - denotes left justification.
  20.     //The number indicates how wide to make the field.
  21.     //The "s" denotes that it's a string.  All of our output in this test are
  22.     //strings, but that won't always be the case.
  23.     static final String displayFormat="%-5s%-15s%-15s%-15s\n";
  24. // JDBC driver name and database URL
  25.     static final String JDBC_DRIVER = "org.apache.derby.jdbc.ClientDriver";
  26.     static String DB_URL = "jdbc:derby://localhost:1527/";
  27. //            + "testdb;user=";
  28. /**
  29.  * Takes the input string and outputs "N/A" if the string is empty or null.
  30.  * @param input The string to be mapped.
  31.  * @return  Either the input string or "N/A" as appropriate.
  32.  */
  33.     public static String dispNull (String input) {
  34.         //because of short circuiting, if it's null, it never checks the length.
  35.         if (input == null || input.length() == 0)
  36.             return "N/A";
  37.         else
  38.             return input;
  39.     }
  40.    
  41.     public static int menu(Scanner in){
  42.         int x = 0;
  43.         do{
  44.             System.out.println("What do you want to do?");
  45.             System.out.println("1. List all writing groups");
  46.             System.out.println("2. List all data for a specific groups");
  47.             System.out.println("3. List all publishers");
  48.             System.out.println("4. List all data for a specific publisher");
  49.             System.out.println("5. List all book titles");
  50.             System.out.println("6. List all data for a specific book");
  51.             System.out.println("7. Insert a new book");
  52.             System.out.println("8. Insert new publisher");
  53.             System.out.println("9. Quit");
  54.            
  55.             x = in.nextInt();
  56.             in.nextLine();
  57.         } while(x < 1 || x > 9);
  58.         return x;
  59.     }
  60.    
  61.     public static void writingGroupRS(PreparedStatement g_name, String input){
  62.         ResultSet rs = null;
  63.         try {
  64.             g_name.setString(1, input);
  65.             g_name.execute();
  66.             rs = g_name.getResultSet();
  67.             while (rs.next()) {
  68.                 //Retrieve by column name
  69.                 String group_name = rs.getString("GROUP_NAME");
  70.                 String head_writer = rs.getString("HEAD_WRITER");
  71.                 String year_formed = rs.getString("YEAR_FORMED");
  72.                 String subject = rs.getString("SUBJECT");
  73.  
  74.                 //Display values
  75.                 System.out.println("Group Name: " + dispNull(group_name));
  76.                 System.out.println("Head Writer: " + dispNull(head_writer));
  77.                 System.out.println("Year Formed: " + dispNull(year_formed));
  78.                 System.out.println("Subject: " + dispNull(subject));
  79.             }
  80.         } catch (SQLException ex) {
  81.             ex.printStackTrace();
  82.         }
  83.     }
  84.     public static void publisherRS(PreparedStatement p_name, String input){
  85.         ResultSet rs = null;
  86.         try{
  87.             p_name.setString(1, input);
  88.             p_name.execute();
  89.             rs = p_name.getResultSet();
  90.             while (rs.next()) {
  91.                 //Retrieve by column name
  92.                 String pub_name = rs.getString("PUBLISHER_NAME");
  93.                 String pub_add = rs.getString("PUBlISHER_ADDRESS");
  94.                 String pub_phone = rs.getString("PUBLISHER_PHONE");
  95.                 String pub_email = rs.getString("PUBLISHER_EMAIL");
  96.  
  97.                 //Display values
  98.                 System.out.println("Publisher Name: " + dispNull(pub_name));
  99.                 System.out.println("Publisher Address: " + dispNull(pub_add));
  100.                 System.out.println("Publisher Phone: " + dispNull(pub_phone));
  101.                 System.out.println("Publisher Email: " + dispNull(pub_email));
  102.             }
  103.         } catch (SQLException ex) {
  104.             ex.printStackTrace();
  105.         }
  106.     }
  107.    
  108.     public static void bookRS(PreparedStatement b_name, String input, Connection conn){
  109.         ResultSet rs = null;
  110.         try{
  111.             b_name.setString(1, input);
  112.             b_name.execute();
  113.             rs = b_name.getResultSet();
  114.             while (rs.next()) {
  115.                 //Retrieve by column name
  116.                 String group_name = rs.getString("GROUP_NAME");
  117.                 String book_title = rs.getString("BOOK_TITLE");
  118.                 String pub_name = rs.getString("PUBLISHER_NAME");
  119.                 String numOfPages = rs.getString("NUMOFPAGES");
  120.  
  121.                 //Display values
  122.                 System.out.println("Group Name: " + dispNull(group_name));
  123.                 System.out.println();
  124.                 writingGroupRS(conn.prepareStatement("SELECT * FROM WRITINGGROUPS WHERE GROUP_NAME = ?"), group_name);
  125.                 System.out.println();
  126.                 System.out.println("Book Title: " + dispNull(book_title));
  127.                 System.out.println("Publisher Name: " + dispNull(pub_name));
  128.                 System.out.println();
  129.                 publisherRS(conn.prepareStatement("SELECT * FROM PUBLISHERS WHERE PUBLISHER_NAME = ?"), pub_name);
  130.                 System.out.println();
  131.                 System.out.println("Number of Pages: " + dispNull(numOfPages));
  132.             }
  133.         } catch (SQLException ex) {
  134.             ex.printStackTrace();
  135.         }
  136.     }
  137.    
  138.     public static void main(String[] args) {
  139.         //Prompt the user for the database name, and the credentials.
  140.         //If your database has no credentials, you can update this code to
  141.         //remove that from the connection string.
  142.         Scanner in = new Scanner(System.in);
  143.         System.out.print("Name of the database (not the user account): ");
  144.         DBNAME = in.nextLine();
  145.         System.out.print("Database user name: ");
  146.         USER = in.nextLine();
  147.         System.out.print("Database password: ");
  148.         PASS = in.nextLine();
  149.         //Constructing the database URL connection string
  150.         DB_URL = DB_URL + DBNAME + ";user="+ USER + ";password=" + PASS;
  151.         Connection conn = null; //initialize the connection
  152.         Statement stmt = null;  //initialize the statement that we're using
  153.         ResultSet rs = null;  //Initialize the result set
  154.         try {
  155.             //STEP 2: Register JDBC driver
  156.             Class.forName("org.apache.derby.jdbc.ClientDriver");
  157.  
  158.             //STEP 3: Open a connection
  159.             System.out.println("Connecting to database...");
  160.             conn = DriverManager.getConnection(DB_URL);
  161.             System.out.println("Connected!");
  162.            
  163.             //Ask user what to do with database
  164.             int choice = 0;
  165.             while(choice != 9){
  166.                 choice = menu(in);
  167.  
  168.                 //STEP 4: Execute a query
  169.                 stmt = conn.createStatement();
  170.                 String sql = "";
  171.  
  172.  
  173.                 if(choice == 1){
  174.                     sql = "SELECT * FROM WRITINGGROUPS";
  175.                     rs = stmt.executeQuery(sql);
  176.                     //STEP 5: Extract data from result set
  177.                     System.out.println("Group Name:");
  178.                     while (rs.next()) {
  179.                         //Retrieve by column name
  180.                         String group_name = rs.getString("GROUP_NAME");
  181.                         //Display values
  182.                         System.out.println(dispNull(group_name));
  183.                     }
  184.                 }
  185.                 else if(choice == 2){
  186.                     PreparedStatement g_name = null;
  187.                     sql = "SELECT * FROM WRITINGGROUPS WHERE GROUP_NAME = ?";
  188.                     g_name = conn.prepareStatement(sql);
  189.                     System.out.print("Enter the group name: ");
  190.                     String input = in.nextLine();
  191.                     writingGroupRS(g_name, input);
  192.                 }
  193.                 else if(choice == 3){
  194.                     sql = "SELECT * FROM PUBLISHERS";
  195.                     rs = stmt.executeQuery(sql);
  196.                     //STEP 5: Extract data from result set
  197.                     System.out.println("Publisher Name:");
  198.                     while (rs.next()) {
  199.                         //Retrieve by column name
  200.                         String group_name = rs.getString("PUBLISHER_NAME");
  201.                         //Display values
  202.                         System.out.println(dispNull(group_name));
  203.                     }
  204.                 }
  205.                 else if(choice == 4){
  206.                     PreparedStatement p_name = null;
  207.                     sql = "SELECT * FROM PUBLISHERS WHERE PUBLISHER_NAME = ?";
  208.                     p_name = conn.prepareStatement(sql);
  209.                     System.out.print("Enter the publisher's name: ");
  210.                     String input = in.nextLine();
  211.                     publisherRS(p_name, input);
  212.                 }
  213.                 else if(choice == 5){
  214.                     sql = "SELECT * FROM BOOKS";
  215.                     rs = stmt.executeQuery(sql);
  216.                     //STEP 5: Extract data from result set
  217.                     System.out.println("Book Titles:");
  218.                     while (rs.next()) {
  219.                         //Retrieve by column name
  220.                         String group_name = rs.getString("BOOK_TITLE");
  221.                         //Display values
  222.                         System.out.println(dispNull(group_name));
  223.                     }
  224.                 }
  225.                 else if(choice == 6){
  226.                     PreparedStatement b_name = null;
  227.                     sql = "SELECT * FROM BOOKS WHERE BOOK_TITLE = ?";
  228.                     b_name = conn.prepareStatement(sql);
  229.                     System.out.print("Enter the book's title: ");
  230.                     String input = in.nextLine();
  231.                     bookRS(b_name, input, conn);
  232.                 }
  233.                 else if(choice == 7){
  234.                     PreparedStatement bookSQL = null;
  235.                     sql = "INSERT INTO BOOKS VALUES(?,?,?,?,?)";
  236.                     bookSQL = conn.prepareStatement(sql);
  237.                     System.out.print("Enter the book's group name: ");
  238.                     String group = in.nextLine();
  239.                     System.out.print("Enter the book's title: ");
  240.                     String title = in.nextLine();
  241.                     System.out.print("Enter the publisher's name: ");
  242.                     String pubName = in.nextLine();
  243.                     System.out.print("Enter the year published: ");
  244.                     String year = in.nextLine();
  245.                     System.out.print("Enter the number of pages: ");
  246.                     String pages = in.nextLine();
  247.                    
  248.                     bookSQL.setString(1, group);
  249.                     bookSQL.setString(2, title);
  250.                     bookSQL.setString(3, pubName);
  251.                     bookSQL.setString(4, year);
  252.                     bookSQL.setString(5, pages);
  253.                     bookSQL.execute();
  254.                     //rs = book.getResultSet();
  255.                 }
  256.                
  257.             }
  258.             //STEP 6: Clean-up environment
  259.             rs.close();
  260.             stmt.close();
  261.             conn.close();
  262.         } catch (SQLException se) {
  263.             //Handle errors for JDBC
  264.             se.printStackTrace();
  265.         } catch (Exception e) {
  266.             //Handle errors for Class.forName
  267.             e.printStackTrace();
  268.         } finally {
  269.             //finally block used to close resources
  270.             try {
  271.                 if (stmt != null) {
  272.                     stmt.close();
  273.                 }
  274.             } catch (SQLException se2) {
  275.             }// nothing we can do
  276.             try {
  277.                 if (conn != null) {
  278.                     conn.close();
  279.                 }
  280.             } catch (SQLException se) {
  281.                 se.printStackTrace();
  282.             }//end finally try
  283.         }//end try
  284.         System.out.println("Goodbye!");
  285.     }//end main
  286. }//end FirstExample}
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top