Esempio n. 1
0
 public void dispose() {
   try {
     stmt.close();
     con.close();
   } catch (SQLException e) {
   }
 }
  public void connectItems(String query)
        // PRE:  query must be initialized
        // POST: Updates the list of Items based on the query.
      {
    String driver = "org.apache.derby.jdbc.ClientDriver"; // Driver for DB
    String url = "jdbc:derby://localhost:1527/ShopDataBase"; // Url for DB
    String user = "******"; // Username for db
    String pass = "******"; // Password for db
    Connection myConnection; // Connection obj to db
    Statement stmt; // Statement to execute a result appon
    ResultSet results; // A set containing the returned results
    int rowcount; // Num objects in the resultSet
    int i; // Index for the array

    try { // Try connection to db

      Class.forName(driver).newInstance(); // Create our db driver

      myConnection = DriverManager.getConnection(url, user, pass); // Initalize our connection

      stmt =
          myConnection.createStatement(
              ResultSet.TYPE_SCROLL_INSENSITIVE,
              ResultSet.CONCUR_UPDATABLE); // Create a new statement
      results = stmt.executeQuery(query); // Store the results of our query

      rowcount = 0;
      if (results.last()) // Go to the last result
      {
        rowcount = results.getRow();
        results.beforeFirst();
      }
      itemsArray = new Item[rowcount];

      i = 0;
      while (results.next()) // Itterate through the results set
      {
        itemsArray[i] =
            new Item(
                results.getInt("item_id"),
                results.getString("item_name"),
                results.getString("item_type"),
                results.getInt("price"),
                results.getInt("owner_id"),
                results.getString("item_path")); // Creat new Item
        i++;
      }

      results.close(); // Close the ResultSet
      stmt.close(); // Close the statement
      myConnection.close(); // Close the connection to db

    } catch (Exception e) // Cannot connect to db
    {
      System.err.println(e.toString());
      System.err.println("Error, something went horribly wrong in connectItems!");
    }
  }
  public void updateTables(String updateQuery1, String updateQuery2, String updateQuery3)
        // PRE:  updateQuery1,updateQuery2, updateQuery3 must be initialized
        // POST: Updates the list of Items based on the querys.
      {
    String driver = "org.apache.derby.jdbc.ClientDriver"; // Driver for DB
    String url = "jdbc:derby://localhost:1527/ShopDataBase"; // Url for DB
    String user = "******"; // Username for db
    String pass = "******"; // Password for db
    Connection myConnection; // Connection obj to db
    Statement stmt; // Statement to execute a result appon
    ResultSet results; // A set containing the returned results
    int i; // Index for the array
    try // Try connection to db
    {

      Class.forName(driver).newInstance(); // Create our db driver

      myConnection = DriverManager.getConnection(url, user, pass); // Initalize our connection

      stmt =
          myConnection.createStatement(
              ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
      stmt.executeUpdate(updateQuery1);

      stmt.executeUpdate(updateQuery2);

      stmt.executeUpdate(updateQuery3);

      results = stmt.executeQuery(previous_query); // Call the previous query

      i = 0;
      while (results.next()) // Itterate through the results set
      {
        itemsArray[i] =
            new Item(
                results.getInt("item_id"),
                results.getString("item_name"),
                results.getString("item_type"),
                results.getInt("price"),
                results.getInt("owner_id"),
                results.getString("item_path"));
        i++;
      }
      results.close(); // Close the ResultSet
      stmt.close(); // Close the statement
      myConnection.close(); // Close the connection to db

    } catch (Exception e) // Cannot connect to db
    {
      System.err.println(e.toString());
      System.err.println("Error, something went horribly wrong! in updateTables");
    }
  }
Esempio n. 4
0
 public void closeDB() {
   try {
     if (statement != null) {
       statement.close();
     }
     if (db != null) {
       db.close();
     }
   } catch (Exception e) {
     System.out.println("Database could not closed");
     e.printStackTrace();
   }
 } // closeDB sonu
  public void actionPerformed(ActionEvent ae) {
    String str = ae.getActionCommand();
    if (str.equals("Ok")) {
      String str1 = (String) jcmname.getSelectedItem();

      Connection con = null;
      Statement stat = null;

      if (str1.equals("Pulsar")
          || str1.equals("CT 100")
          || str1.equals("Discover DTS-i")
          || str1.equals("Wave DTS-i")) {
        try {
          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
          con = DriverManager.getConnection("Jdbc:Odbc:showroom");
          System.out.println("Got Connection :" + con);
          stat = con.createStatement();
          ResultSet rs = stat.executeQuery("select * from vehicle");
          System.out.println("chk1");
          while (rs.next()) {
            if (str1.equals(rs.getString(1))) {

              tfcap.setText("" + rs.getInt(2));
              tfeng.setText("" + rs.getInt(3));
              tfbhp.setText("" + rs.getInt(4));
              tfvolt.setText("" + rs.getInt(5));
              tfrpm.setText("" + rs.getInt(6));
              tfweight.setText("" + rs.getInt(7));
              tfgear.setText("" + rs.getInt(8));
            }
          }

          stat.close();
          con.close();

        } catch (Exception ex) {
        }

      } else {
        JOptionPane.showMessageDialog(
            null, "Please Choose Model Name", "Error", JOptionPane.ERROR_MESSAGE);
      }
    }

    if (str.equals("can")) {
      this.dispose();
      // new menu(1);
    }
  }
  /**
   * Fill the table using m_sql
   *
   * @param table table
   */
  private static void tableLoad(MiniTable table) {
    //	log.finest(m_sql + " - " +  m_groupBy);
    String sql =
        MRole.getDefault()
                .addAccessSQL(m_sql.toString(), "tab", MRole.SQL_FULLYQUALIFIED, MRole.SQL_RO)
            + m_groupBy
            + m_orderBy;

    log.finest(sql);
    try {
      Statement stmt = DB.createStatement();
      ResultSet rs = stmt.executeQuery(sql);
      table.loadTable(rs);
      stmt.close();
    } catch (SQLException e) {
      log.log(Level.SEVERE, sql, e);
    }
  } //  tableLoad
Esempio n. 7
0
  public void login() {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:orcl";
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;

    String id, pw, sql;
    try {
      Class.forName(driver);
      con = DriverManager.getConnection(url, "hr", "hr");
      stmt = con.createStatement();
      sql = "select * from chatuser where id = '" + lid.getText() + "'";
      rs = stmt.executeQuery(sql);
      if (rs.next()) {
        pw = rs.getString(2);
        nick = rs.getString(3);
        if (lpw.getText().equals(pw)) {
          jd.setVisible(false);
          setVisible(true);
        } else {
          lpw.setText("일치하지 않습니다.");
        }
      } else {
        lid.setText("일치하지 않습니다.");
      }
    } catch (Exception e1) {
      e1.printStackTrace();
      System.out.println("데이터 베이스 연결 실패!!");
    } finally {
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
        if (con != null) con.close();
      } catch (Exception e1) {
        System.out.println(e1.getMessage());
      }
    }
  }
Esempio n. 8
0
  public void bookRoom() {
    try {
      int code = 0;
      Connection con =
          DriverManager.getConnection(
              "jdbc:mysql://67.20.111.85:3306/jeehtove_caliking?relaxAutoCommit=false",
              "jeehtove_ck",
              "Z_^PBBZT+kcy");
      Statement stmt_insert = con.createStatement();
      Statement stmt_tableRange = con.createStatement();
      Statement stmt_checkrooms = con.createStatement();
      Statement stmt_checkrooms_2 = con.createStatement();

      String insert = null;
      int flag = 0;

      String room_type;
      if (standardRoom.isSelected()) {
        room_type = "Standard";
        code = 1;
      } else if (familyRoom.isSelected()) {
        room_type = "Family";
        code = 2;
      } else {
        room_type = "Suite";
        code = 3;
      }

      // Converting string from checkin and checkout dates so that they can access the correct table
      String currentDate_checkin_getText = checkindateField.getText();
      String currentDate_checkin_rest = currentDate_checkin_getText.substring(5);
      String currentDate_checkin_replace = currentDate_checkin_rest.replace("-", "_");
      String currentDate_checkin_year = currentDate_checkin.substring(0, 4);
      String currentDate_checkin_final =
          currentDate_checkin_year + "_" + currentDate_checkin_replace;

      String currentDate_checkout_getText = checkoutdateField.getText();
      String currentDate_checkout_rest = currentDate_checkout_getText.substring(5);
      String currentDate_checkout_replace = currentDate_checkout_rest.replace("-", "_");
      String currentDate_checkout_year = currentDate_checkout.substring(0, 4);
      String currentDate_checkout_final =
          currentDate_checkout_year + "_" + currentDate_checkout_replace;

      // MySQL Statement to select tables from schema that are in between checkin and checkout range
      String tableRange =
          "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='jeehtove_caliking' AND TABLE_NAME BETWEEN 'rooms_"
              + currentDate_checkin_final
              + "' AND 'rooms_"
              + currentDate_checkout_final
              + "';";
      ResultSet tb_Range = stmt_tableRange.executeQuery(tableRange);

      System.out.println(tableRange);
      List tb_range_list = new ArrayList();

      while (tb_Range.next()) {
        String tb_range_date = tb_Range.getString("table_name");
        tb_range_list.add(tb_range_date);
        // String current_table = tb_Range.getString("table_name");
        // System.out.println(tb_range_list.add(tb_Range.getString("table_name")));
      }

      stmt_tableRange.close();

      for (int x = 0; x < tb_range_list.size(); x++) {
        String query_checkrooms =
            "SELECT * FROM "
                + tb_range_list.get(x)
                + " WHERE type = '"
                + code
                + "' AND booked = '0' LIMIT 1;";
        // System.out.println(query_checkrooms);
        ResultSet checkrooms = stmt_checkrooms.executeQuery(query_checkrooms);
        while (checkrooms.next()) {
          String getroomnumber = checkrooms.getString("room");
          insert =
              "INSERT INTO `reservations` VALUES ('"
                  + getroomnumber
                  + "', '"
                  + checkindateField.getText()
                  + "', '"
                  + checkoutdateField.getText()
                  + "',  '"
                  + room_type
                  + "',  '"
                  + nameField.getText()
                  + "');";
          String bookroom =
              "UPDATE `"
                  + tb_range_list.get(x)
                  + "` SET booked = '1' WHERE room = '"
                  + getroomnumber
                  + "';";
          // System.out.println(insert);
          int cr = stmt_checkrooms_2.executeUpdate(bookroom);
          flag = 1;
          System.out.println("Room Booked!");
        }
      }

      if (flag == 1) {
        int rs = stmt_insert.executeUpdate(insert);
      } else {
        System.out.println("All " + room_type + " rooms are booked for the time requested.");
      }

      /*String query_checkrooms = "SELECT * FROM rooms_" + currentDate_checkin_final + " WHERE type = '" + code + "' AND booked = '0';";
      System.out.println(query_checkrooms);
      Statement stmt_checkrooms = con.createStatement();
      ResultSet checkrooms = stmt_checkrooms.executeQuery(query_checkrooms);*/

      /*if (checkrooms.next()){
      		String getroomnumber = checkrooms.getString("room");
      		String insert = "INSERT INTO `reservations` VALUES ('" + getroomnumber + "', '" +  checkindateField.getText() + "', '" +  checkoutdateField.getText() + "',  '" + room_type + "',  '" + nameField.getText() + "');";
      		String bookroom = "UPDATE `rooms_" + currentDate_checkin_final + "` SET booked = '1' WHERE room = '" + getroomnumber + "';";
      //System.out.println(insert);
      	int rs=stmt_insert.executeUpdate(insert);
      	int cr=stmt_checkrooms.executeUpdate(bookroom);
      	con.commit();
      	System.out.println("Room Booked!");
      	}*/

      // else System.out.println("All " + room_type + " rooms are booked for the time requested.");
      System.out.println("Complete.");
    } catch (Exception e) {
      System.out.println(e);
    }
  }