Examples with InsertQuery com.healthmarketscience.sqlbuilder.InsertQuery used on opensource projects

Example 1 with InsertQuery

use of com.healthmarketscience.sqlbuilder.InsertQuery in project SmartCity-Market by TechnionYP5777.

the class SQLDatabaseConnection method addWorker.

@Override
public void addWorker(Integer sessionID, Login l, ForgotPasswordData security) throws CriticalError, ClientAlreadyExist, ClientNotConnected {
    log.debug("SQL Public addWorker: add new worker with username: " + l.getUserName());
    validateSessionEstablished(sessionID);
    if (isWorkerExist(l.getUserName())) {
        log.debug("SQL Public addWorker: already exist worker with username: " + l.getUserName());
        throw new ClientAlreadyExist();
    }
    PreparedStatement statement = null;
    try {
        // START transaction
        connectionStartTransaction();
        //Write part of transaction
        String insertCustomerQuery = new InsertQuery(WorkersTable.workertable).addColumn(WorkersTable.workerusernameCol, PARAM_MARK).addColumn(WorkersTable.workerpasswordCol, PARAM_MARK).addColumn(WorkersTable.workerPrivilegesCol, PARAM_MARK).addColumn(WorkersTable.workersecurityQuestionCol, PARAM_MARK).addColumn(WorkersTable.workersecurityAnswerCol, PARAM_MARK).addColumn(WorkersTable.workerisLoggedInCol, PARAM_MARK).validate() + "";
        statement = getParameterizedQuery(insertCustomerQuery, l.getUserName(), l.getPassword(), WORKERS_TABLE.VALUE_PRIVILEGE_WORKER, security.getQuestion(), security.getAnswer(), 0);
        statement.executeUpdate();
        log.debug("SQL Public addWorker: worker " + l.getUserName() + "added successfuly");
        // END transaction
        connectionCommitTransaction();
    } catch (SQLDatabaseException e) {
        // NOTE: all exceptions flows here - for doing rollback
        connectionRollbackTransaction();
        throw e;
    } catch (SQLException e) {
        connectionRollbackTransaction();
        throw new CriticalError();
    } finally {
        connectionEndTransaction();
        closeResources(statement);
    }
}

Example 2 with InsertQuery

use of com.healthmarketscience.sqlbuilder.InsertQuery in project SmartCity-Market by TechnionYP5777.

the class SQLDatabaseConnection method setNewAmountForStore.

/**
	 * Change amount of product package. according to parameters, the method
	 * create/remove/update the relevant row
	 * 
	 * @param p
	 *            - product pacakge to update
	 * @param placeCol
	 *            - location's column name of the pacakage (can be
	 *            PRODUCTS_PACKAGES_TABLE.VALUE_PLACE_STORE or
	 *            PRODUCTS_PACKAGES_TABLE.VALUE_PLACE_WAREHOUSE)
	 * @param oldAmount
	 * @param newAmount
	 * @throws ProductPackageAmountNotMatch
	 * @throws CriticalError
	 */
private void setNewAmountForStore(ProductPackage p, String placeCol, int oldAmount, int newAmount) throws ProductPackageAmountNotMatch, CriticalError {
    // case: not enough amount
    if (newAmount < 0)
        throw new ProductPackageAmountNotMatch();
    PreparedStatement statement = null;
    try {
        // case: add new row
        if (oldAmount == 0) {
            String insertQuery = new InsertQuery(ProductsPackagesTable.table).addColumn(ProductsPackagesTable.barcodeCol, PARAM_MARK).addColumn(ProductsPackagesTable.expirationDateCol, dateToString(p.getSmartCode().getExpirationDate())).addColumn(ProductsPackagesTable.placeInStoreCol, PARAM_MARK).addColumn(ProductsPackagesTable.amountCol, PARAM_MARK).validate() + "";
            insertQuery.hashCode();
            log.debug("setNewAmountForStore: create new row amount to package: " + p + ", to place: " + placeCol);
            statement = getParameterizedQuery(insertQuery, p.getSmartCode().getBarcode(), placeCol, newAmount);
        } else if (newAmount == 0) {
            // case: remove row
            String deleteQuery = generateDeleteQuery(ProductsPackagesTable.table, BinaryCondition.equalTo(ProductsPackagesTable.barcodeCol, PARAM_MARK), BinaryCondition.equalTo(ProductsPackagesTable.placeInStoreCol, PARAM_MARK), BinaryCondition.equalTo(ProductsPackagesTable.expirationDateCol, JdbcEscape.date(Date.from(p.getSmartCode().getExpirationDate().atStartOfDay(ZoneId.systemDefault()).toInstant()))));
            deleteQuery.hashCode();
            log.debug("setNewAmountForStore: remove row to package: " + p + ", from place: " + placeCol);
            statement = getParameterizedQuery(deleteQuery, p.getSmartCode().getBarcode(), placeCol);
        } else {
            // case: update amount to new value
            UpdateQuery updateQuery = generateUpdateQuery(ProductsPackagesTable.table, BinaryCondition.equalTo(ProductsPackagesTable.barcodeCol, PARAM_MARK), BinaryCondition.equalTo(ProductsPackagesTable.placeInStoreCol, PARAM_MARK), BinaryCondition.equalTo(ProductsPackagesTable.expirationDateCol, JdbcEscape.date(Date.from(p.getSmartCode().getExpirationDate().atStartOfDay(ZoneId.systemDefault()).toInstant()))));
            updateQuery.addSetClause(ProductsPackagesTable.amountCol, newAmount).validate();
            log.debug("setNewAmountForStore: update row of package: " + p + ", of place: " + placeCol);
            statement = getParameterizedQuery(updateQuery + "", p.getSmartCode().getBarcode(), placeCol);
        }
        log.debug("setNewAmountForStore : run query: " + statement);
        statement.executeUpdate();
    } catch (SQLException e) {
        throw new CriticalError();
    } finally {
        closeResources(statement);
    }
}

Example 3 with InsertQuery

use of com.healthmarketscience.sqlbuilder.InsertQuery in project SmartCity-Market by TechnionYP5777.

the class SQLDatabaseConnection method setNewAmountForCart.

/**
	 * Change amount of product package in cart. according to parameters, the
	 * method create/remove/update the relevant row
	 * 
	 * @param p
	 *            - product pacakge to update
	 * @param listID
	 *            - id of the grocery list to update
	 * @param oldAmount
	 * @param newAmount
	 * @throws ProductPackageAmountNotMatch
	 * @throws CriticalError
	 */
private void setNewAmountForCart(ProductPackage p, Integer listID, int oldAmount, int newAmount) throws ProductPackageAmountNotMatch, CriticalError {
    // case: not enough amount
    if (newAmount < 0)
        throw new ProductPackageAmountNotMatch();
    PreparedStatement statement = null;
    try {
        // case: add new row
        if (oldAmount == 0) {
            String insertQuery = new InsertQuery(GroceriesListsTable.table).addColumn(GroceriesListsTable.barcodeCol, PARAM_MARK).addColumn(GroceriesListsTable.expirationDateCol, JdbcEscape.date(Date.from(p.getSmartCode().getExpirationDate().atStartOfDay(ZoneId.systemDefault()).toInstant()))).addColumn(GroceriesListsTable.listIDCol, PARAM_MARK).addColumn(GroceriesListsTable.amountCol, PARAM_MARK).validate() + "";
            insertQuery.hashCode();
            statement = getParameterizedQuery(insertQuery, p.getSmartCode().getBarcode(), listID, newAmount);
        } else if (newAmount == 0) {
            // case: remove row
            String deleteQuery = generateDeleteQuery(GroceriesListsTable.table, BinaryCondition.equalTo(GroceriesListsTable.barcodeCol, PARAM_MARK), BinaryCondition.equalTo(GroceriesListsTable.listIDCol, PARAM_MARK), BinaryCondition.equalTo(GroceriesListsTable.expirationDateCol, JdbcEscape.date(Date.from(p.getSmartCode().getExpirationDate().atStartOfDay(ZoneId.systemDefault()).toInstant()))));
            deleteQuery.hashCode();
            statement = getParameterizedQuery(deleteQuery, p.getSmartCode().getBarcode(), listID);
        } else {
            // case: update amount to new value
            UpdateQuery updateQuery = generateUpdateQuery(GroceriesListsTable.table, BinaryCondition.equalTo(GroceriesListsTable.barcodeCol, PARAM_MARK), BinaryCondition.equalTo(GroceriesListsTable.listIDCol, PARAM_MARK), BinaryCondition.equalTo(GroceriesListsTable.expirationDateCol, JdbcEscape.date(Date.from(p.getSmartCode().getExpirationDate().atStartOfDay(ZoneId.systemDefault()).toInstant()))));
            updateQuery.addSetClause(GroceriesListsTable.amountCol, newAmount).validate();
            statement = getParameterizedQuery(updateQuery + "", p.getSmartCode().getBarcode(), listID);
        }
        statement.executeUpdate();
    } catch (SQLException e) {
        throw new CriticalError();
    } finally {
        closeResources(statement);
    }
}

Example 4 with InsertQuery

use of com.healthmarketscience.sqlbuilder.InsertQuery in project SmartCity-Market by TechnionYP5777.

the class SQLDatabaseConnection method addManufacturer.

@Override
public String addManufacturer(Integer sessionID, String manufacturerName) throws CriticalError, ClientNotConnected {
    log.debug("SQL Public addManufacturer: manufacturer name: " + manufacturerName + " (SESSION: " + sessionID + " )");
    validateSessionEstablished(sessionID);
    int $;
    // START transaction
    connectionStartTransaction();
    try {
        // WRITE part of transaction
        // get "fresh" id for the new manufacturer
        $ = allocateIDToTable(ManufacturerTable.table, ManufacturerTable.manufacturerIDCol);
        String insertQuery = new InsertQuery(ManufacturerTable.table).addColumn(ManufacturerTable.manufacturerIDCol, PARAM_MARK).addColumn(ManufacturerTable.manufacturerNameCol, PARAM_MARK).validate() + "";
        insertQuery.hashCode();
        getParameterizedQuery(insertQuery, $, manufacturerName).executeUpdate();
        // END transaction
        connectionCommitTransaction();
    } catch (CriticalError | SQLException e) {
        connectionRollbackTransaction();
        throw new CriticalError();
    } finally {
        connectionEndTransaction();
    }
    return Serialization.serialize(new Manufacturer($, manufacturerName));
}

Example 5 with InsertQuery

use of com.healthmarketscience.sqlbuilder.InsertQuery in project SmartCity-Market by TechnionYP5777.

the class SQLDatabaseConnection method addCatalogProduct.

/**
	 * Add product to the SQL database
	 * 
	 * @param p
	 *            New product to add
	 * @throws CriticalError
	 * @throws SQLException
	 */
private void addCatalogProduct(CatalogProduct p) throws CriticalError, SQLException {
    // add all ingredients of product
    for (Ingredient ¢ : p.getIngredients()) {
        String insertToProductQuery = new InsertQuery(ProductsCatalogIngredientsTable.table).addColumn(ProductsCatalogIngredientsTable.barcodeCol, PARAM_MARK).addColumn(ProductsCatalogIngredientsTable.ingredientIDCol, PARAM_MARK).validate() + "";
        insertToProductQuery.hashCode();
        PreparedStatement statement = getParameterizedQuery(insertToProductQuery, p.getBarcode(), ¢.getId());
        statement.executeUpdate();
        closeResources(statement);
    }
    // add all locations of product
    for (Location ¢ : p.getLocations()) {
        int newID = allocateIDToTable(LocationsTable.table, LocationsTable.locationIDCol);
        String insertLocationQuery = new InsertQuery(LocationsTable.table).addColumn(LocationsTable.locationIDCol, PARAM_MARK).addColumn(LocationsTable.placeInStoreCol, PARAM_MARK).addColumn(LocationsTable.pointXCol, PARAM_MARK).addColumn(LocationsTable.pointYCol, PARAM_MARK).validate() + "";
        insertLocationQuery.hashCode();
        PreparedStatement insertLocationStatement = getParameterizedQuery(insertLocationQuery, newID, ¢.getPlaceInMarket().equals(PlaceInMarket.STORE) ? LOCATIONS_TABLE.VALUE_PLACE_STORE : LOCATIONS_TABLE.VALUE_PLACE_WAREHOUSE, ¢.getX(), ¢.getY());
        String insertToProductQuery = new InsertQuery(ProductsCatalogLocationsTable.table).addColumn(ProductsCatalogLocationsTable.barcodeCol, PARAM_MARK).addColumn(ProductsCatalogLocationsTable.locationIDCol, PARAM_MARK).validate() + "";
        PreparedStatement statement = getParameterizedQuery(insertToProductQuery, p.getBarcode(), newID);
        insertLocationStatement.executeUpdate();
        statement.executeUpdate();
        closeResources(insertLocationStatement);
        closeResources(statement);
    }
    // add the product itself
    String insertQuery = new InsertQuery(ProductsCatalogTable.table).addColumn(ProductsCatalogTable.barcodeCol, PARAM_MARK).addColumn(ProductsCatalogTable.manufacturerIDCol, PARAM_MARK).addColumn(ProductsCatalogTable.productDescriptionCol, PARAM_MARK).addColumn(ProductsCatalogTable.productNameCol, PARAM_MARK).addColumn(ProductsCatalogTable.productPictureCol, PARAM_MARK).addColumn(ProductsCatalogTable.productPriceCol, PARAM_MARK).validate() + "";
    PreparedStatement statement = getParameterizedQuery(insertQuery, p.getBarcode(), p.getManufacturer().getId(), p.getDescription(), p.getName(), p.getImageUrl(), p.getPrice());
    statement.executeUpdate();
    closeResources(statement);
}