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

Example 1 with UpdateQuery

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

the class SQLDatabaseConnection method editManufacturer.

@Override
public void editManufacturer(Integer sessionID, Manufacturer newManufacturer) throws CriticalError, ClientNotConnected, ManufacturerNotExist {
    log.debug("SQL Public editManufacturer: edit to manufacturer: " + newManufacturer + " (SESSION: " + sessionID + " )");
    validateSessionEstablished(sessionID);
    // START transaction
    connectionStartTransaction();
    try {
        // READ part of transaction
        if (!isManufacturerExist((int) newManufacturer.getId()))
            throw new ManufacturerNotExist();
        // WRITE part of transaction
        // update manufacturer
        UpdateQuery updateQuery = generateUpdateQuery(ManufacturerTable.table, BinaryCondition.equalTo(ManufacturerTable.manufacturerIDCol, PARAM_MARK));
        updateQuery.addSetClause(ManufacturerTable.manufacturerNameCol, PARAM_MARK).validate();
        //note: the id is last because in the query the order of parameters is: set newValue and then Where ManufacturerID 
        getParameterizedQuery(updateQuery + "", newManufacturer.getName(), newManufacturer.getId()).executeUpdate();
        // END transaction
        connectionCommitTransaction();
    } catch (SQLException e) {
        connectionRollbackTransaction();
        throw new CriticalError();
    } finally {
        connectionEndTransaction();
    }
}

Example 2 with UpdateQuery

use of com.healthmarketscience.sqlbuilder.UpdateQuery 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 UpdateQuery

use of com.healthmarketscience.sqlbuilder.UpdateQuery 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 UpdateQuery

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

the class SQLDatabaseConnection method setCustomerProfile.

@Override
public void setCustomerProfile(String username, CustomerProfile p) throws CriticalError, ClientNotExist, IngredientNotExist {
    log.debug("SQL Public setCustomerProfile: Customer set profile: " + p + " to username: " + username);
    if (!isCustomerExist(username)) {
        log.debug("SQL Public setCustomerProfile: no such customer with username: " + username);
        throw new ClientNotExist();
    }
    checkAllIngredientsExist(p.getAllergens());
    PreparedStatement statement = null;
    try {
        // START transaction
        connectionStartTransaction();
        //Write part of transaction
        //updating general info
        UpdateQuery updateQuery = generateUpdateQuery(CustomersTable.customertable, BinaryCondition.equalTo(CustomersTable.customerusernameCol, PARAM_MARK));
        updateQuery.addSetClause(CustomersTable.customerAddressCol, PARAM_MARK).addSetClause(CustomersTable.customerCityCol, PARAM_MARK).addSetClause(CustomersTable.customerEmailCol, PARAM_MARK).addSetClause(CustomersTable.customerFirstnameCol, PARAM_MARK).addSetClause(CustomersTable.customerLastnameCol, PARAM_MARK).addSetClause(CustomersTable.customerPhonenumberCol, PARAM_MARK).addSetClause(CustomersTable.customerBirthdateCol, JdbcEscape.date(Date.from(p.getBirthdate().atStartOfDay(ZoneId.systemDefault()).toInstant()))).validate();
        //note: the username is in the end because the structure of set clause
        statement = getParameterizedQuery(updateQuery + "", p.getStreet(), p.getCity(), p.getEmailAddress(), p.getFirstName(), p.getLastName(), p.getPhoneNumber(), username);
        statement.executeUpdate();
        //updating ingredients of customer
        setIngredientsForCustomer(username, p.getAllergens());
        log.debug("SQL Public setCustomerProfile: Success setting profile for username: " + username);
        // END transaction
        connectionCommitTransaction();
    } catch (SQLDatabaseException e) {
        connectionRollbackTransaction();
        throw e;
    } catch (SQLException e) {
        connectionRollbackTransaction();
        throw new CriticalError();
    } finally {
        connectionEndTransaction();
        closeResources(statement);
    }
}

Example 5 with UpdateQuery

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

the class SQLDatabaseConnection method logoutAllUsers.

@Override
public void logoutAllUsers() throws CriticalError {
    log.debug("SQL Public logoutAllUsers.");
    // START transaction
    connectionStartTransaction();
    PreparedStatement statement = null;
    try {
        // WRITE part of transaction
        // disconnect all carts
        statement = getParameterizedQuery(generateDeleteQuery(CartsListTable.table));
        log.debug("logoutAllUsers: clear carts.\n by using query: " + statement);
        statement.executeUpdate();
        closeResources(statement);
        // deletes all grocery lists
        //Todo - noam : when log out all users - the products not returns to shelf
        statement = getParameterizedQuery(generateDeleteQuery(GroceriesListsTable.table));
        log.debug("logoutAllUsers: delete grocery lists.\n by using query: " + statement);
        statement.executeUpdate();
        closeResources(statement);
        // disconnect all workers
        statement = getParameterizedQuery(new UpdateQuery(WorkersTable.workertable).addSetClause(WorkersTable.workersessionIDCol, SqlObject.NULL_VALUE).validate() + "");
        log.debug("logoutAllUsers: logout workers.\n by using query: " + statement);
        statement.executeUpdate();
        closeResources(statement);
        // disconnect all customers
        statement = getParameterizedQuery(new UpdateQuery(CustomersTable.customertable).addSetClause(CustomersTable.customersessionIDCol, SqlObject.NULL_VALUE).validate() + "");
        log.debug("logoutAllUsers: logout customers.\n by using query: " + statement);
        statement.executeUpdate();
        closeResources(statement);
        // END transaction
        connectionCommitTransaction();
    } catch (SQLException e) {
        connectionRollbackTransaction();
        throw new CriticalError();
    } finally {
        connectionEndTransaction();
        closeResources(statement);
    }
}