public static Vector<DBColumnModel> getColumnByCode(String tableCode) {

    if (conn == null) conn = DBConnection.getConnection();

    Vector<DBColumnModel> result = new Vector<DBColumnModel>();
    String query = " SELECT * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='" + tableCode + "'";
    Statement stmt;
    try {
      stmt = conn.createStatement();
      stmt.executeQuery(query);
      ResultSet rst = stmt.getResultSet();
      while (rst.next()) {
        DBColumnModel desc = new DBColumnModel();
        desc.setKod(rst.getString(4));
        String isNullable = rst.getString(7);
        if (isNullable.equalsIgnoreCase("YES")) {
          desc.setNullable(true);
        } else {
          desc.setNullable(false);
        }

        desc.setTip(rst.getString(8));
        if (rst.getString(9) != null) {
          desc.setDuzina(new Integer(rst.getString(9)));
        }
        result.add(desc);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }

    return result;
  }
  public void save(CurrentProduct currentProduct) {
    con = dbCon.geConnection();
    try {
      pst = con.prepareStatement("insert into Products values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
      pst.setString(1, null);
      pst.setString(2, currentProduct.productId);
      pst.setString(3, currentProduct.productName);
      pst.setString(4, currentProduct.quantity);
      pst.setString(5, currentProduct.description);
      pst.setString(6, currentProduct.supplierId);
      pst.setString(7, currentProduct.brandId);
      pst.setString(8, currentProduct.catagoryId);
      pst.setString(9, currentProduct.unitId);
      pst.setString(10, currentProduct.pursesPrice);
      pst.setString(11, currentProduct.sellPrice);
      pst.setString(12, currentProduct.rmaId);
      pst.setString(13, currentProduct.userId);
      pst.setString(14, currentProduct.date);
      pst.executeUpdate();
      pst.close();
      con.close();

    } catch (SQLException ex) {
      Logger.getLogger(CurrentProduct.class.getName()).log(Level.SEVERE, null, ex);
      System.out.println("Too Many Connection");
    }
  }
 public Vector<Message> getInboxMessages(String username, int type) {
   if (type == 4) username = "******";
   ResultSet rs =
       con.queryDB(
           "SELECT * FROM "
               + MESSAGES
               + " WHERE toName = \""
               + username
               + "\" AND messageType = "
               + type
               + " ORDER BY timeStamp DESC");
   Vector<Message> inbox = new Vector<Message>();
   try {
     while (rs.next()) {
       inbox.add(
           new Message(
               rs.getString("fromName"),
               username,
               rs.getString("message"),
               rs.getInt("messageType"),
               rs.getTimestamp("timeStamp")));
     }
   } catch (SQLException e) {
     throw new RuntimeException("SQLException in MessageDatabase::getInboxMessages");
   }
   return inbox;
 }
 public boolean isNotSoled(CurrentProduct currentProduct) {
   con = dbCon.geConnection();
   boolean isNotSoled = false;
   try {
     pst = con.prepareStatement("select * from Sell where ProductId=?");
     pst.setString(1, currentProduct.id);
     rs = pst.executeQuery();
     while (rs.next()) {
       Dialogs.create()
           .title("")
           .masthead("Error")
           .message("This product has been  soled you can't delete it")
           .styleClass(Dialog.STYLE_CLASS_UNDECORATED)
           .showError();
       return isNotSoled;
     }
     rs.close();
     pst.close();
     con.close();
     isNotSoled = true;
   } catch (SQLException ex) {
     Logger.getLogger(CurrentProductGetway.class.getName()).log(Level.SEVERE, null, ex);
   }
   return isNotSoled;
 }
  public void searchByRMA(CurrentProduct currentProduct) {
    con = dbCon.geConnection();

    currentProduct.currentProductList.clear();

    try {
      pst = con.prepareStatement("select * from Products where RMAId=?");
      pst.setString(1, currentProduct.rmaId);
      rs = pst.executeQuery();
      while (rs.next()) {
        currentProduct.id = rs.getString(1);
        currentProduct.productId = rs.getString(2);
        currentProduct.productName = rs.getString(3);
        currentProduct.quantity = rs.getString(4);
        currentProduct.description = rs.getString(5);
        currentProduct.supplierId = rs.getString(6);
        currentProduct.brandId = rs.getString(7);
        currentProduct.catagoryId = rs.getString(8);
        currentProduct.unitId = rs.getString(9);
        currentProduct.pursesPrice = rs.getString(10);
        currentProduct.sellPrice = rs.getString(11);
        currentProduct.rmaId = rs.getString(12);
        currentProduct.userId = rs.getString(13);
        currentProduct.date = rs.getString(14);
        currentProduct.supplierName =
            sql.getName(currentProduct.supplierId, currentProduct.supplierName, "Supplyer");
        currentProduct.brandName =
            sql.getName(currentProduct.brandId, currentProduct.brandName, "Brands");
        currentProduct.catagoryName =
            sql.getName(currentProduct.catagoryId, currentProduct.catagoryName, "Catagory");
        currentProduct.unitName =
            sql.getName(currentProduct.unitId, currentProduct.unitName, "Unit");
        currentProduct.rmaName = sql.getName(currentProduct.rmaId, currentProduct.rmaName, "RMA");
        currentProduct.userName =
            sql.getName(currentProduct.userId, currentProduct.userName, "User");
        currentProduct.currentProductList.addAll(
            new ListProduct(
                currentProduct.id,
                currentProduct.productId,
                currentProduct.productName,
                currentProduct.quantity,
                currentProduct.description,
                currentProduct.supplierName,
                currentProduct.brandName,
                currentProduct.catagoryName,
                currentProduct.unitName,
                currentProduct.pursesPrice,
                currentProduct.sellPrice,
                currentProduct.rmaName,
                currentProduct.userName,
                currentProduct.date));
      }
      pst.close();
      con.close();
      rs.close();
    } catch (SQLException ex) {
      Logger.getLogger(CurrentProductGetway.class.getName()).log(Level.SEVERE, null, ex);
    }
  }
  public void view(CurrentProduct currentProduct) {
    currentProduct.currentProductList.clear();
    con = dbCon.geConnection();

    try {
      pst = con.prepareStatement("SELECT SQL_NO_CACHE * FROM Products");
      rs = pst.executeQuery();
      while (rs.next()) {

        currentProduct.id = rs.getString(1);
        currentProduct.productId = rs.getString(2);
        currentProduct.productName = rs.getString(3);
        currentProduct.quantity = rs.getString(4);
        currentProduct.description = rs.getString(5);
        currentProduct.supplierId = rs.getString(6);
        currentProduct.brandId = rs.getString(7);
        currentProduct.catagoryId = rs.getString(8);
        currentProduct.unitId = rs.getString(9);
        currentProduct.pursesPrice = rs.getString(10);
        currentProduct.sellPrice = rs.getString(11);
        currentProduct.rmaId = rs.getString(12);
        currentProduct.userId = rs.getString(13);
        currentProduct.date = rs.getString(14);
        currentProduct.supplierName =
            sql.getName(currentProduct.supplierId, currentProduct.supplierName, "Supplyer");
        currentProduct.brandName =
            sql.getName(currentProduct.brandId, currentProduct.brandName, "Brands");
        currentProduct.catagoryName =
            sql.getName(currentProduct.catagoryId, currentProduct.catagoryName, "Catagory");
        currentProduct.unitName =
            sql.getName(currentProduct.unitId, currentProduct.unitName, "Unit");
        currentProduct.rmaName = sql.getName(currentProduct.rmaId, currentProduct.rmaName, "RMA");
        currentProduct.userName =
            sql.getName(currentProduct.userId, currentProduct.userName, "User");
        currentProduct.currentProductList.addAll(
            new ListProduct(
                currentProduct.id,
                currentProduct.productId,
                currentProduct.productName,
                currentProduct.quantity,
                currentProduct.description,
                currentProduct.supplierName,
                currentProduct.brandName,
                currentProduct.catagoryName,
                currentProduct.unitName,
                currentProduct.pursesPrice,
                currentProduct.sellPrice,
                currentProduct.rmaName,
                currentProduct.userName,
                currentProduct.date));
      }
      pst.close();
      con.close();
      rs.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
 public void delete(CurrentProduct currentProduct) {
   con = dbCon.geConnection();
   try {
     pst = con.prepareStatement("delete from Products where id=?");
     pst.setString(1, currentProduct.id);
     pst.executeUpdate();
     pst.close();
     con.close();
   } catch (SQLException ex) {
     Logger.getLogger(CurrentProduct.class.getName()).log(Level.SEVERE, null, ex);
   }
 }
  public static void main(String[] args) {
    DBConnection db = new DBConnection();
    // set up jdbc connection
    if (db.createConnection()) {
      // read file to build auto
      BuildAuto cars = new BuildAuto();
      cars.buildAuto("Focus.txt");
      cars.buildAuto("Prius.txt");

      // create table from text file
      ArrayList<String> sql = FileIO.readFile("sql.txt");
      CreateTable table = new CreateTable(db, sql.get(0), sql.get(1), sql.get(2));
      table.createTable();

      // add new automobile to db
      System.out.println("------------- add new automobile to database ---------------");
      Automobile focus = cars.getAutoByModelname("Focus");
      Automobile prius = cars.getAutoByModelname("Prius");
      AddAuto add = new AddAuto(db);
      add.addAuto(focus);
      add.addAuto(prius);
      String[] arr1 = {"model", "make", "base_price"};
      String[] arr2 = {"model", "setname"};
      String[] arr3 = {"model", "setname", "option_name", "price"};

      int two = db.showAll("automobile", arr1);
      if (two == 2) {
        System.out.println("add automobile to database successfully");
      } else {
        System.out.println("test failed, add automobile error");
      }

      // update automobile
      System.out.println(
          "------------- update option price & optionSet name to database---------------");
      UpdateDBAuto update = new UpdateDBAuto(db);
      // update option price
      update.updateOptionPrice("Focus", "Transmission", "Automatic", 20.0f);
      update.updateOptionsetName("Prius", "PowerMoonroof", "Power");
      db.showAll("optionset", arr2);
      db.showAll("options", arr3);

      // delete automobile
      System.out.println("------------- delete automobile from database ---------------");
      DeleteAuto delete = new DeleteAuto(db);
      delete.deleteAuto(focus);
      cars.delAutoByName("Focus");
      int one = db.showAll("automobile", arr1);
      if (one == 1) System.out.println("delete automobile from database successfully");
      else System.out.println("test failed, delete automobile error");
      System.out.println("");
      db.closeConnection();
    }
  }
 /**
  * Puts a message in the database Note: the timestamp will be stored as the current time at which
  * this method is called.
  */
 public void storeMessage(Message message) {
   con.updateDB(
       "INSERT INTO "
           + MESSAGES
           + " VALUES ( \""
           + message.getFrom()
           + "\", \""
           + message.getTo()
           + "\", \""
           + message.getBody()
           + "\", "
           + null
           + ", "
           + message.getType()
           + ")");
 }
  public void cbSupplyer(CurrentProduct currentProduct) {
    con = dbCon.geConnection();

    try {
      pst = con.prepareStatement("select * from Supplyer");
      rs = pst.executeQuery();
      while (rs.next()) {
        currentProduct.supplyerList = rs.getString(2);
      }
      pst.close();
      con.close();
      rs.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
 /**
  * Puts a message in the database Note: the timestamp will be stored as the current time at which
  * this method is called.
  */
 public void storeMessage(
     String from, String to, String message, int messageType, String timestamp) {
   con.updateDB(
       "INSERT INTO "
           + MESSAGES
           + " VALUES ( \""
           + from
           + "\", \""
           + to
           + "\", \""
           + message
           + "\", "
           + null
           + ", "
           + messageType
           + ")");
 }
 public boolean containsFriendRequest(String from, String to) {
   ResultSet rs =
       con.queryDB(
           "SELECT * FROM "
               + MESSAGES
               + " WHERE fromName = \""
               + from
               + "\" AND toName = \""
               + to
               + "\" AND messageType = 2");
   try {
     if (rs.next()) return true;
   } catch (SQLException e) {
     throw new RuntimeException("SQLException in MessageDatabase::containsFriendRequest");
   }
   return false;
 }
  public void sView(CurrentProduct currentProduct) {
    con = dbCon.geConnection();

    try {
      pst = con.prepareStatement("select * from Products where ProductId=?");
      pst.setString(1, currentProduct.productId);
      rs = pst.executeQuery();
      while (rs.next()) {
        currentProduct.id = rs.getString(1);
        currentProduct.productId = rs.getString(2);
        currentProduct.productName = rs.getString(3);
        currentProduct.quantity = rs.getString(4);
        currentProduct.description = rs.getString(5);
        currentProduct.supplierId = rs.getString(6);
        currentProduct.brandId = rs.getString(7);
        currentProduct.catagoryId = rs.getString(8);
        currentProduct.unitId = rs.getString(9);
        currentProduct.pursesPrice = rs.getString(10);
        currentProduct.sellPrice = rs.getString(11);
        currentProduct.rmaId = rs.getString(12);
        currentProduct.userId = rs.getString(13);
        currentProduct.date = rs.getString(14);
        currentProduct.supplierName =
            sql.getName(currentProduct.supplierId, currentProduct.supplierName, "Supplyer");
        currentProduct.brandName =
            sql.getName(currentProduct.brandId, currentProduct.brandName, "Brands");
        currentProduct.catagoryName =
            sql.getName(currentProduct.catagoryId, currentProduct.catagoryName, "Catagory");
        currentProduct.unitName =
            sql.getName(currentProduct.unitId, currentProduct.unitName, "Unit");
        currentProduct.rmaName = sql.getName(currentProduct.rmaId, currentProduct.rmaName, "RMA");
        currentProduct.userName =
            sql.getName(currentProduct.userId, currentProduct.userName, "User");
        currentProduct.rmaDayesss = sql.getDayes(currentProduct.rmaDayesss, currentProduct.rmaId);
        long dateRMA = Long.parseLong(currentProduct.rmaDayesss);

        currentProduct.warrentyVoidDate = LocalDate.now().plusDays(dateRMA).toString();
      }
      pst.close();
      con.close();
      rs.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  public static boolean isForeignKey(String tableCode, String columnCode) {
    if (conn == null) conn = DBConnection.getConnection();

    String query = "SELECT * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE;";
    Statement stmt;
    try {
      stmt = conn.createStatement();
      stmt.executeQuery(query);
      ResultSet rst = stmt.getResultSet();
      while (rst.next()) {
        if (rst.getString(6).equalsIgnoreCase(tableCode)
            && rst.getString(7).equalsIgnoreCase(columnCode)
            && rst.getString(3).startsWith("PK")) {
          return true;
        }
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return false;
  }
  public static Vector<String> getTableCodes() {

    Vector<String> ret = new Vector<>();
    String query = "SELECT * from INFORMATION_SCHEMA.TABLES;";
    Statement stmt;

    if (conn == null) conn = DBConnection.getConnection();

    try {
      stmt = conn.createStatement();
      stmt.executeQuery(query);
      ResultSet rst = stmt.getResultSet();
      while (rst.next()) {
        ret.add(rst.getString(3));
      }
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }

    return ret;
  }
  public static HashMap<String, String> getParentCodeAndColumnCodInParent(
      String tableCode, String columnCode) throws SQLException {

    if (conn == null) conn = DBConnection.getConnection();

    if (dbMetadata == null) dbMetadata = conn.getMetaData();

    HashMap<String, String> res = new HashMap<String, String>();
    ResultSet result;
    try {
      result = dbMetadata.getImportedKeys(null, "dbo", tableCode);
      while (result.next()) {
        if (result.getString(8).equals(columnCode)) {
          res.put(
              result.getString(3), result.getString(4)); // 3 - naziv tabele iz koje je nasledjen
        } // 4 - naziv koda u toj tabeli
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return res;
  }
 /*
  * Returns a Vector representing all messages in the user's outbox.  The most recent
  * messages are at the beginning, and the oldest ones are at the end.
  */
 public Vector<Message> getOutboxMessages(String username) {
   ResultSet rs =
       con.queryDB(
           "SELECT * FROM "
               + MESSAGES
               + " WHERE fromName = \""
               + username
               + "\" ORDER BY timeStamp DESC");
   Vector<Message> outbox = new Vector<Message>();
   try {
     while (rs.next()) {
       outbox.add(
           new Message(
               username,
               rs.getString("toName"),
               rs.getString("message"),
               rs.getInt("messageType"),
               rs.getTimestamp("timeStamp")));
     }
   } catch (SQLException e) {
     throw new RuntimeException("SQLException in MessageDatabase::getOutboxMessages");
   }
   return outbox;
 }
  public void update(CurrentProduct currentProduct) {
    con = dbCon.geConnection();

    try {
      pst =
          con.prepareStatement(
              "update Products set ProductId=?, ProductName=?, Quantity=?, Description=?, "
                  + "SupplyerId=?, BrandId=?, CatagoryId=?,"
                  + " UnitId=?, PursesPrice=?, SellPrice=?, RMAId=?, Date=?  where Id=?");
      pst.setString(1, currentProduct.productId);
      pst.setString(2, currentProduct.productName);
      pst.setString(3, currentProduct.quantity);
      pst.setString(4, currentProduct.description);
      pst.setString(5, currentProduct.supplierId);
      pst.setString(6, currentProduct.brandId);
      pst.setString(7, currentProduct.catagoryId);
      pst.setString(8, currentProduct.unitId);
      pst.setString(9, currentProduct.pursesPrice);
      pst.setString(10, currentProduct.sellPrice);
      pst.setString(11, currentProduct.rmaId);
      pst.setString(12, currentProduct.date);
      pst.setString(13, currentProduct.id);
      pst.executeUpdate();
      pst.close();
      con.close();
      rs.close();
      Dialogs.create()
          .lightweight()
          .title("Update")
          .masthead("Update Sucess")
          .message("Update Success")
          .showInformation();
    } catch (SQLException ex) {
      Logger.getLogger(CurrentProduct.class.getName()).log(Level.SEVERE, null, ex);
    }
  }
 public DBQueryTable() {
   conn = DBConnection.getConnection();
 }
 public MessageDatabase() {
   con = DBConnection.getSharedConnection();
 }