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

Example 1 with SelectQuery

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

the class SQLDatabaseConnection method cartCheckout.

/*
	 * (non-Javadoc)
	 * 
	 * @see SQLDatabase.ISQLDatabaseConnection#cartCheckout(java.lang.Integer)
	 */
@Override
public void cartCheckout(Integer cartID) throws CriticalError, ClientNotConnected, GroceryListIsEmpty {
    log.debug("SQL Public cartCheckout: of cart: " + cartID + " (SESSION: " + cartID + " )");
    validateCartSessionEstablished(cartID);
    // START transaction
    connectionStartTransaction();
    PreparedStatement copyStatement = null, deleteGroceryList = null;
    ResultSet cartGroceryList = null;
    try {
        // READ part of transaction
        // check if grocery list of that cart is empty
        cartGroceryList = getGroceryListResultSetByCartID(cartID);
        if (isResultSetEmpty(cartGroceryList))
            throw new GroceryListIsEmpty();
        // everything ok - perform checkout
        int listID = getCartListId(cartID);
        // WRITE part of transaction
        // moving grocery list to history
        String copyQuery = "INSERT " + GroceriesListsHistoryTable.table.getTableNameSQL() + "( " + GroceriesListsHistoryTable.listIDCol.getColumnNameSQL() + " , " + GroceriesListsHistoryTable.barcodeCol.getColumnNameSQL() + " , " + GroceriesListsHistoryTable.expirationDateCol.getColumnNameSQL() + " , " + GroceriesListsHistoryTable.amountCol.getColumnNameSQL() + " ) " + new SelectQuery().addColumns(GroceriesListsTable.listIDCol, GroceriesListsTable.barcodeCol, GroceriesListsTable.expirationDateCol, GroceriesListsTable.amountCol).addCondition(BinaryCondition.equalTo(GroceriesListsTable.listIDCol, PARAM_MARK)).validate();
        copyStatement = getParameterizedQuery(copyQuery, listID);
        deleteGroceryList = getParameterizedQuery(generateDeleteQuery(GroceriesListsTable.table, BinaryCondition.equalTo(GroceriesListsTable.listIDCol, PARAM_MARK)), listID);
        log.debug("cartCheckout: move groceryList " + listID + " to history.\n by run query: " + copyStatement + "\n and: " + deleteGroceryList);
        copyStatement.executeUpdate();
        deleteGroceryList.executeUpdate();
        // logout cart
        logoutAsCart(cartID);
        // COMMIT transaction
        connectionCommitTransaction();
    } catch (SQLException | CriticalError e) {
        connectionRollbackTransaction();
        throw new CriticalError();
    } finally {
        connectionEndTransaction();
        closeResources(copyStatement, cartGroceryList, deleteGroceryList);
    }
}

Example 2 with SelectQuery

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

the class SQLDatabaseConnection method getIngredientsList.

@Override
public String getIngredientsList() throws CriticalError {
    try {
        ResultSet ingredientsResultSet = getParameterizedReadQuery(new SelectQuery().addAllTableColumns(IngredientsTable.table).validate() + "", (Object[]) null).executeQuery();
        ingredientsResultSet.first();
        return SQLJsonGenerator.allIngredientsListToJson(ingredientsResultSet);
    } catch (SQLException e) {
        throw new CriticalError();
    }
}

Example 3 with SelectQuery

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

the class SQLQueryGenerator method generateSelectInnerJoinWithQuery2Tables.

/**
	 * 
	 * Generate string of select query table inner join with other table
	 * 
	 * (note: inner join match only the rows that exists on the both table on the desired column value)
	 * 
	 * @param tabel
	 *            The table to select
	 * @param joinWithTable
	 *            The table to join with.
	 * @param joinByCol
	 *            join by this column
	 * @param orderByCol
	 *            order results by this column
	 * @param cs
	 *            Set of conditions
	 * @return string of select join query.
	 */
public static String generateSelectInnerJoinWithQuery2Tables(DbTable tabel, DbTable joinWithTable, DbColumn joinByCol, DbColumn orderByCol, Condition... cs) {
    DbJoin custJoin = SQLDatabaseEntities.spec.addJoin(null, tabel.getAbsoluteName(), null, joinWithTable.getAbsoluteName(), joinByCol.getColumnNameSQL());
    SelectQuery resultQuery = new SelectQuery().addAllTableColumns(tabel).addAllTableColumns(joinWithTable).addJoins(SelectQuery.JoinType.INNER, custJoin).addOrderings(orderByCol);
    for (int ¢ = 0; ¢ < cs.length; ++¢) resultQuery.addCondition(cs[¢]);
    return resultQuery.validate() + "";
}

Example 4 with SelectQuery

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

the class SQLDatabaseConnection method getManufacturersList.

@Override
public String getManufacturersList(Integer sessionID) throws ClientNotConnected, CriticalError {
    validateSessionEstablished(sessionID);
    try {
        ResultSet manufacturerResultSet = getParameterizedReadQuery(new SelectQuery().addAllTableColumns(ManufacturerTable.table).validate() + "", (Object[]) null).executeQuery();
        manufacturerResultSet.first();
        return SQLJsonGenerator.manufaturersListToJson(manufacturerResultSet);
    } catch (SQLException e) {
        throw new CriticalError();
    }
}

Example 5 with SelectQuery

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

the class SQLDatabaseConnection method removeCatalogProduct.

/**
	 * Remove product from the SQL database (erase all associate entries in
	 * tables: Product catalog, Ingredients, Locations NOTE: other traces of the
	 * product will not be removed
	 * 
	 * @param p
	 *            - product to remove (only the barcode is used)
	 * @throws CriticalError
	 * @throws SQLException
	 */
private void removeCatalogProduct(SmartCode p) throws CriticalError, SQLException {
    // remove all ingredients of product
    PreparedStatement statement = getParameterizedQuery(generateDeleteQuery(ProductsCatalogIngredientsTable.table, BinaryCondition.equalTo(ProductsCatalogIngredientsTable.barcodeCol, PARAM_MARK)), p.getBarcode());
    statement.executeUpdate();
    closeResources(statement);
    String selectAllLocationsQuery = new SelectQuery().addColumns(ProductsCatalogLocationsTable.locationIDCol).addCondition(BinaryCondition.equalTo(ProductsCatalogLocationsTable.barcodeCol, PARAM_MARK)).validate() + "", deleteLocationsQuery = new DeleteQuery(LocationsTable.table).addCondition(new CustomCondition(LocationsTable.locationIDCol.getColumnNameSQL() + " IN (" + selectAllLocationsQuery + " ) ")).validate() + "";
    PreparedStatement LocationsStatement = getParameterizedQuery(deleteLocationsQuery, p.getBarcode());
    LocationsStatement.executeUpdate();
    closeResources(LocationsStatement);
    // remove barcode form ProductsLocations Table
    PreparedStatement productLocationsStatement = getParameterizedQuery(generateDeleteQuery(ProductsCatalogLocationsTable.table, BinaryCondition.equalTo(ProductsCatalogLocationsTable.barcodeCol, PARAM_MARK)), p.getBarcode());
    productLocationsStatement.executeUpdate();
    closeResources(productLocationsStatement);
    // remove product itself
    PreparedStatement productStatement = getParameterizedQuery(generateDeleteQuery(ProductsCatalogTable.table, BinaryCondition.equalTo(ProductsCatalogTable.barcodeCol, PARAM_MARK)), p.getBarcode());
    productStatement.executeUpdate();
    closeResources(productStatement);
}