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

Example 6 with UpdateQuery

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

the class SQLDatabaseConnection method logoutAsWorker.

/**
	 * logout worker from system. the method disconnects worker with the given
	 * sessionID and username.
	 * 
	 * @param sessionID
	 * @param username
	 * @throws ClientNotConnected
	 * @throws CriticalError
	 */
private void logoutAsWorker(Integer sessionID, String username) throws CriticalError {
    String query = generateSelectQuery1Table(WorkersTable.workertable, BinaryCondition.equalTo(WorkersTable.workerusernameCol, PARAM_MARK), BinaryCondition.equalTo(WorkersTable.workersessionIDCol, PARAM_MARK));
    PreparedStatement statement = getParameterizedQuery(query, username, sessionID);
    ResultSet result = null;
    try {
        result = statement.executeQuery();
        // check if no results or more than one - throw exception
        if (getResultSetRowCount(result) != 1)
            throw new SQLDatabaseException.CriticalError();
        /*
			 * EVERYTHING OK - disconnect worker
			 */
        UpdateQuery updateQuery = generateUpdateQuery(WorkersTable.workertable, BinaryCondition.equalTo(WorkersTable.workerusernameCol, PARAM_MARK));
        updateQuery.addSetClause(WorkersTable.workersessionIDCol, SqlObject.NULL_VALUE).validate();
        statement = getParameterizedQuery(updateQuery + "", username);
        statement.executeUpdate();
    } catch (SQLException e) {
        throw new CriticalError();
    } finally {
        closeResources(statement, result);
    }
}

Example 7 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 8 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);
    }
}