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

Example 6 with InsertQuery

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

the class SQLDatabaseConnection method addIngredient.

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

Example 7 with InsertQuery

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

the class SQLDatabaseConnection method registerCustomer.

@Override
public void registerCustomer(String username, String password) throws CriticalError, ClientAlreadyExist {
    log.debug("SQL Public registerCustomer: Customer trying to register with username: " + username);
    if (isCustomerExist(username)) {
        log.debug("SQL Public registerCustomer: already exist customer with username: " + username);
        throw new ClientAlreadyExist();
    }
    PreparedStatement statement = null;
    try {
        // START transaction
        connectionStartTransaction();
        //Write part of transaction
        String insertCustomerQuery = new InsertQuery(CustomersTable.customertable).addColumn(CustomersTable.customerIDCol, PARAM_MARK).addColumn(CustomersTable.customerusernameCol, PARAM_MARK).addColumn(CustomersTable.customerpasswordCol, PARAM_MARK).addColumn(CustomersTable.customerAddressCol, PARAM_MARK).addColumn(CustomersTable.customerCityCol, PARAM_MARK).addColumn(CustomersTable.customerEmailCol, PARAM_MARK).addColumn(CustomersTable.customerFirstnameCol, PARAM_MARK).addColumn(CustomersTable.customerLastnameCol, PARAM_MARK).addColumn(CustomersTable.customerisLoggedInCol, PARAM_MARK).addColumn(CustomersTable.customerPhonenumberCol, PARAM_MARK).addColumn(CustomersTable.customersecurityAnswerCol, PARAM_MARK).addColumn(CustomersTable.customersecurityQuestionCol, PARAM_MARK).validate() + "";
        statement = getParameterizedQuery(insertCustomerQuery, 0, username, password, "", "", "", "", "", 0, "", "", "");
        statement.executeUpdate();
        // END transaction
        connectionCommitTransaction();
    } catch (SQLDatabaseException e) {
        // NOTE: all exceptions flows here - for doing rollback
        connectionRollbackTransaction();
        throw e;
    } catch (SQLException e) {
        connectionRollbackTransaction();
        log.debug(e.getStackTrace());
        log.fatal(e.getMessage());
        throw new CriticalError();
    } finally {
        connectionEndTransaction();
        closeResources(statement);
    }
}

Example 8 with InsertQuery

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

the class SQLDatabaseConnection method initiateNewGroceryList.

/**
	 * Creates new grocery list to the given sessionID and adds its to the database
	 * 
	 * @param sessionID the sessionID whose the new grocery list will be created to
	 * @throws CriticalError
	 */
private void initiateNewGroceryList(int sessionID) throws CriticalError {
    String maxListIDQuery = new SelectQuery().addCustomColumns(FunctionCall.max().addColumnParams(CartsListTable.listIDCol)).validate() + "", maxHistoryListIDQuery = new SelectQuery().addCustomColumns(FunctionCall.max().addColumnParams(GroceriesListsHistoryTable.listIDCol)).validate() + "";
    ResultSet maxListIDResult = null, maxHistoryListIDResult = null;
    try {
        maxListIDResult = getParameterizedReadQuery(maxListIDQuery).executeQuery();
        maxHistoryListIDResult = getParameterizedReadQuery(maxHistoryListIDQuery).executeQuery();
        int maxListID = 0, maxHistoryListID = 0;
        // get the max id from tables (if exist)
        if (!isResultSetEmpty(maxListIDResult)) {
            maxListIDResult.first();
            maxListID = maxListIDResult.getInt(1);
        }
        if (!isResultSetEmpty(maxHistoryListIDResult)) {
            maxHistoryListIDResult.first();
            maxHistoryListID = maxHistoryListIDResult.getInt(1);
        }
        maxListID = Math.max(maxListID, maxHistoryListID) + 1;
        // adding new cart connection to table
        String insertQuery = new InsertQuery(CartsListTable.table).addColumn(CartsListTable.CartIDCol, PARAM_MARK).addColumn(CartsListTable.listIDCol, PARAM_MARK).validate() + "";
        log.debug("initiateNewGroceryList: run query: " + insertQuery);
        getParameterizedQuery(insertQuery, sessionID, maxListID).executeUpdate();
    } catch (SQLException e) {
        throw new CriticalError();
    } finally {
        closeResources(maxHistoryListIDResult, maxListIDResult);
    }
}

Example 9 with InsertQuery

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

the class SQLDatabaseConnection method freeIDOfTable.

/**
	 * Free ID when removing row. (NOTE: used for manufaturerID, ingredientID
	 * and LocationID)
	 * 
	 * @param t
	 *            The table you want to remove row from it.
	 * @param col
	 *            The ID column of that table
	 * @return
	 * @throws CriticalError
	 */
private void freeIDOfTable(DbTable t, Integer idToFree) throws CriticalError {
    String insertQuery = new InsertQuery(FreeIDsTable.table).addColumn(FreeIDsTable.fromTableNameCol, PARAM_MARK).addColumn(FreeIDsTable.IDCol, PARAM_MARK).validate() + "";
    PreparedStatement statement = getParameterizedQuery(insertQuery, t.getName(), idToFree);
    try {
        statement.executeUpdate();
    } catch (SQLException e) {
        throw new CriticalError();
    } finally {
        closeResources(statement);
    }
}

Example 10 with InsertQuery

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

the class SQLDatabaseConnection method setIngredientsForCustomer.

/**
	 * update the ingredients of the user to the ones in the hashset.
	 * 
	 * @param username The username of the customer to update.
	 * @param newSet The new set of ingredients to update to. cannot be null.
	 * @throws CriticalError
	 */
private void setIngredientsForCustomer(String username, HashSet<Ingredient> newSet) throws CriticalError {
    PreparedStatement statement = null;
    try {
        //remove the old list of ingredients
        String deleteIngredientsQuery = generateDeleteQuery(CustomersIngredientsTable.table, BinaryCondition.equalTo(CustomersIngredientsTable.customerUsernameCol, PARAM_MARK));
        statement = getParameterizedQuery(deleteIngredientsQuery, username);
        log.debug("updateIngredientsForCustomer: removing old ingredients of customer: " + username + "\nby running query: " + statement);
        statement.executeUpdate();
        closeResources(statement);
        for (Ingredient ingredient : newSet) {
            //add new ingredients 
            String insertIngredientQuery = new InsertQuery(CustomersIngredientsTable.table).addColumn(CustomersIngredientsTable.customerUsernameCol, PARAM_MARK).addColumn(CustomersIngredientsTable.ingredientIDCol, PARAM_MARK).validate() + "";
            statement = getParameterizedQuery(insertIngredientQuery, username, ingredient.getId());
            log.debug("updateIngredientsForCustomer: add igredient: " + ingredient + " to customer: " + username + "\nby running query: statement");
            statement.executeUpdate();
            closeResources(statement);
        }
    } catch (SQLException e) {
        throw new CriticalError();
    } finally {
        closeResources(statement);
    }
}