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

Example 6 with SelectQuery

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

the class SQLDatabaseConnection method allocateIDToTable.

/**
	 * Allocate new ID for new row. (NOTE: used for manufaturerID, ingredientID
	 * and LocationID)
	 * 
	 * @param t
	 *            The table you want to insert new row in it
	 * @param c
	 *            The ID column of that table
	 * @return
	 * @throws CriticalError
	 */
private int allocateIDToTable(DbTable t, DbColumn c) throws CriticalError {
    // search for free id
    String selectId = generateSelectQuery1Table(FreeIDsTable.table, BinaryCondition.equalTo(FreeIDsTable.fromTableNameCol, PARAM_MARK));
    PreparedStatement statement = null;
    ResultSet result = null, maxIDResult = null;
    try {
        statement = getParameterizedReadQuery(selectId, t.getName());
        result = statement.executeQuery();
        int retID;
        if (!isResultSetEmpty(result)) {
            // return id from free_ids_table
            result.first();
            retID = result.getInt(FreeIDsTable.IDCol.getColumnNameSQL());
            // delete that id
            getParameterizedQuery(generateDeleteQuery(FreeIDsTable.table, BinaryCondition.equalTo(FreeIDsTable.fromTableNameCol, PARAM_MARK), BinaryCondition.equalTo(FreeIDsTable.IDCol, PARAM_MARK)), t.getName(), retID).executeUpdate();
        } else {
            // find max id and return the next number
            String maxIDQuery = new SelectQuery().addCustomColumns(FunctionCall.max().addColumnParams(c)).validate() + "";
            maxIDResult = getParameterizedReadQuery(maxIDQuery).executeQuery();
            // if the table is empty - return 1
            if (isResultSetEmpty(result))
                retID = 1;
            else {
                maxIDResult.first();
                retID = maxIDResult.getInt(1) + 1;
            }
        }
    } catch (SQLException e) {
        throw new CriticalError();
    } finally {
        closeResources(statement, result, maxIDResult);
    }
    return 0;
}

Example 7 with SelectQuery

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

the class SQLQueryGenerator method generateSelectLeftJoinWithQuery2Tables.

/**
	 * 
	 * Generate string of select query table left join with other table
	 * 
	 * (note: left join always take all the rows in the left table and match them to the right table. if there is no match 
	 * then the row is matched with null)
	 * 
	 * @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 generateSelectLeftJoinWithQuery2Tables(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.LEFT_OUTER, custJoin).addOrderings(orderByCol);
    for (int ¢ = 0; ¢ < cs.length; ++¢) resultQuery.addCondition(cs[¢]);
    return resultQuery.validate() + "";
}

Example 8 with SelectQuery

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

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

the class SQLDatabaseConnection method getWorkersList.

@Override
public String getWorkersList(Integer sessionID) throws ClientNotConnected, CriticalError {
    log.debug("SQL Public getWorkersList: retreiving workers list");
    HashMap<String, Boolean> result = new HashMap<>();
    validateSessionEstablished(sessionID);
    try {
        log.debug("SQL Public getWorkersList: run sql query to get all workers");
        ResultSet workersResultSet = getParameterizedReadQuery(new SelectQuery().addAllTableColumns(WorkersTable.workertable).validate() + "", (Object[]) null).executeQuery();
        workersResultSet.first();
        HashSet<String> workersNames = SQLJsonGenerator.createWorkersList(workersResultSet);
        log.debug("SQL Public getWorkersList: find if workers are connected");
        for (String name : workersNames) result.put(name, isWorkerConnected(name));
        return Serialization.serialize(result);
    } catch (SQLException e) {
        throw new CriticalError();
    }
}