Java Where類代碼示例

import com.j256.ormlite.stmt.Where; //導入依賴的package包/類
public List<MerchantService> getByInterval(Date begin, Date end, boolean orderAscending) throws IOException {

        try {
            QueryBuilder<MerchantService, String> queryBuilder = dao.queryBuilder();
            queryBuilder.orderBy(MerchantService.PURCHASE_DATE_FIELD_NAME, orderAscending);
            Where<MerchantService, String> where = queryBuilder.where();
            where.between(MerchantService.PURCHASE_DATE_FIELD_NAME, begin, end);

            List<MerchantService> results = queryBuilder.query();

            for (MerchantService r : results) {
                refresh(r);
            }

            return results;
        } catch (Exception e) {
            throw new IOException(e);
        }
    }
 

import com.j256.ormlite.stmt.Where; //導入依賴的package包/類
public Tag getTag(final String aTag) {
    try {
        QueryBuilder<Tag, Long> queryBuilder = dbHelper.getTagDao().queryBuilder();
        Where<Tag, Long> where = queryBuilder.where();
        SelectArg selectArg = new SelectArg();

        // define our query as 'name = ?'
        where.eq(Tag.COLUMN_NAME_TITLE, selectArg);

        // prepare it so it is ready for later query or iterator calls
        PreparedQuery<Tag> preparedQuery = queryBuilder.prepare();

        // later we can set the select argument and issue the query
        selectArg.setValue(aTag);
        List<Tag> tgs = dbHelper.getTagDao().query(preparedQuery);
        if (!tgs.isEmpty()) {
            return tgs.get(0);
        }
    } catch (java.sql.SQLException pE) {
        pE.printStackTrace();
    }
    return null;
}
 

import com.j256.ormlite.stmt.Where; //導入依賴的package包/類
public EBook getBook(final String fullFilename) {
    EBook ebk = null;
    try {
        QueryBuilder<EBook, Long> queryBuilder = dbHelper.getEBookDao().queryBuilder();
        Where<EBook, Long> where = queryBuilder.where();
        SelectArg selectArg = new SelectArg();

        // define our query as 'name = ?'
        where.eq(EBook.COLUMN_FULL_FILE_NAME, selectArg);

        // prepare it so it is ready for later query or iterator calls
        PreparedQuery<EBook> preparedQuery = queryBuilder.prepare();

        // later we can set the select argument and issue the query
        selectArg.setValue(fullFilename);
        List<EBook> result = dbHelper.getEBookDao().query(preparedQuery);

        if (result.size() > 0) {
            ebk = result.get(0);
        }
    } catch (java.sql.SQLException pE) {
        BookLibApplication.getInstance().e("Exception reading ebooks", pE);
    }
    return ebk;
}
 

import com.j256.ormlite.stmt.Where; //導入依賴的package包/類
public FileType getFileType(final String aFileType) {
    try {
        QueryBuilder<FileType, Long> queryBuilder = dbHelper.getFileTypeDao().queryBuilder();
        Where<FileType, Long> where = queryBuilder.where();
        SelectArg selectArg = new SelectArg();

        // define our query as 'name = ?'
        where.eq(FileType.COLUMN_NAME_TITLE, selectArg);

        // prepare it so it is ready for later query or iterator calls
        PreparedQuery<FileType> preparedQuery = queryBuilder.prepare();

        // later we can set the select argument and issue the query
        selectArg.setValue(aFileType);
        List<FileType> tgs = dbHelper.getFileTypeDao().query(preparedQuery);
        if (!tgs.isEmpty()) {
            return tgs.get(0);
        }
    } catch (java.sql.SQLException pE) {
        pE.printStackTrace();
    }
    return null;
}
 

import com.j256.ormlite.stmt.Where; //導入依賴的package包/類
public Author getAuthor(final String pFirstname, final String pLastname) {
    try {
        QueryBuilder<Author, Long> queryBuilder = dbHelper.getAuthorDao().queryBuilder();
        Where<Author, Long> where = queryBuilder.where();
        SelectArg selectArg = new SelectArg();

        // define our query as 'name = ?'
        where.eq(Author.COLUMN_NAME_FIRSTNAME, selectArg);

        // prepare it so it is ready for later query or iterator calls
        PreparedQuery<Author> preparedQuery = queryBuilder.prepare();

        // later we can set the select argument and issue the query
        selectArg.setValue(pFirstname);
        List<Author> tgs = dbHelper.getAuthorDao().query(preparedQuery);
        if (!tgs.isEmpty()) {
            return tgs.get(0);
        }
    } catch (java.sql.SQLException pE) {
        pE.printStackTrace();
    }
    return null;
}
 

import com.j256.ormlite.stmt.Where; //導入依賴的package包/類
public List<EBook> searchBooksMatching(final String searchStr) {
    List<EBook> result = new ArrayList<>();
    try {
        QueryBuilder<EBook, Long> queryBuilder = dbHelper.getEBookDao().queryBuilder();
        Where<EBook, Long> where = queryBuilder.where();
        SelectArg selectArg2 = new SelectArg();
        selectArg2.setValue("%" + searchStr + "%");
        SelectArg selectArg1 = new SelectArg();
        selectArg1.setValue("%" + searchStr + "%");

        // define our query as 'name = ?'
        where.like(EBook.COLUMN_FULL_FILE_NAME, selectArg1).or()
                .like(EBook.COLUMN_BOOK_TITLE, selectArg2);

        // prepare it so it is ready for later query or iterator calls
        PreparedQuery<EBook> preparedQuery = queryBuilder.prepare();

        // later we can set the select argument and issue the query
        result = dbHelper.getEBookDao().query(preparedQuery);
    } catch (SQLException pE) {
        pE.printStackTrace();
    }

    return result;
}
 

import com.j256.ormlite.stmt.Where; //導入依賴的package包/類
/**
 * Returns all the bookings for a given branch and a given user in the SQLite database
 *
 * @param branch the specified branch
 * @param user the specified user
 * @return a list of the matching bookings
 */
@Override
public List<Booking> getBranchBookingsForUser(Branch branch, User user) {
    try {
        QueryBuilder<Booking, Integer> statementBuilder = dao.queryBuilder();
        Where whereClause = statementBuilder.where();

        whereClause
                .eq(Booking.BRANCH_FIELD_NAME, branch)
                .and()
                .eq(Booking.USER_FIELD_NAME, user);

        statementBuilder.orderBy(Booking.START_DAY_FIELD_NAME, true);

        return dao.query(statementBuilder.prepare());
    } catch (SQLException e) {
        e.printStackTrace();
        return null;
    }
}
 

import com.j256.ormlite.stmt.Where; //導入依賴的package包/類
/**
 * Returns all the bookings for a given branch in the SQLite database
 *
 * @param currentBranch the specified branch
 * @return a list of the matching bookings
 */
@Override
public List<Booking> getBranchBookings(Branch currentBranch) {
    try {
        QueryBuilder<Booking, Integer> statementBuilder = dao.queryBuilder();
        Where whereClause = statementBuilder.where();

        whereClause.eq(Booking.BRANCH_FIELD_NAME, currentBranch);

        statementBuilder.orderBy(Booking.START_DAY_FIELD_NAME, true);

        return dao.query(statementBuilder.prepare());
    } catch (SQLException e) {
        e.printStackTrace();
        return null;
    }
}
 

import com.j256.ormlite.stmt.Where; //導入依賴的package包/類
/**
 * Returns all the vehicles matching a type in the specified branch in the SQLite database
 *
 * @param vehicleTypeId the type ID
 * @param nodeBranch the branch
 * @return a list of the matched vehicles
 */
@Override
public List<Vehicle> searchBranchVehiclesByType(int vehicleTypeId, Branch nodeBranch) {
    try {
        QueryBuilder<Vehicle, Integer> statementBuilder = dao.queryBuilder();

        Where whereClause = statementBuilder.where();

        whereClause
                .eq(Vehicle.TYPE_FIELD_NAME, vehicleTypeId)
                .and()
                .eq(Vehicle.BRANCH_FIELD_NAME, nodeBranch);

        return dao.query(statementBuilder.prepare());
    } catch (SQLException e) {
        return null;
    }
}
 

import com.j256.ormlite.stmt.Where; //導入依賴的package包/類
/**
 * Search for a vehicle with a specific registration number in a given branch in the SQLite database
 * @param registrationNumber the registration number
 * @param nodeBranch the given branch
 * @return the vehicle, if existing, null if not
 */
@Override
public Vehicle getVehicleByRegistrationNumberAndBranch(String registrationNumber, Branch nodeBranch) {
    try {
        QueryBuilder<Vehicle, Integer> statementBuilder = dao.queryBuilder();

        Where whereClause = statementBuilder.where();

        whereClause
                .eq(Vehicle.REGISTRATION_NUMBER_FIELD_NAME, registrationNumber)
                .and()
                .eq(Vehicle.BRANCH_FIELD_NAME, nodeBranch);

        List<Vehicle> vehicles = dao.query(statementBuilder.prepare());

        if (vehicles.size() == 0) {
            return null;
        } else {
            return vehicles.get(0);
        }
    } catch (SQLException e) {
        return null;
    }
}
 

import com.j256.ormlite.stmt.Where; //導入依賴的package包/類
/**
 * Search for an user matching the given email in the SQLite database
 *
 * @param email the given email
 * @return the found user, or null if not found
 */
@Override
public User getUserByEmail(String email) {
    try {
        QueryBuilder<User, Integer> statementBuilder = dao.queryBuilder();

        Where whereClause = statementBuilder.where();

        whereClause.eq(User.EMAIL_ADDRESS_FIELD_NAME, email);

        //Search for user WHERE the email address matches the provided one
        List<User> matches = dao.query(statementBuilder.prepare());

        if (matches.size() == 0) {
            return null;
        } else {
            return matches.get(0);
        }
    } catch (SQLException e) {
        return null;
    }
}
 

import com.j256.ormlite.stmt.Where; //導入依賴的package包/類
/**
 * Search for an user whose a part of email or full name match a the search term in the SQLite database
 *
 * @param searchTerm a string of minimum 3 characters
 * @return A list of matching users
 */
@Override
public List<User> searchUsers(String searchTerm) {
    try {
        QueryBuilder<User, Integer> statementBuilder = dao.queryBuilder();

        Where whereClause = statementBuilder.where();

        //Search for user WHERE the email address or the full name field looks like the provided term
        whereClause
                .like(User.EMAIL_ADDRESS_FIELD_NAME, "%" + searchTerm + "%")
                .like(User.FULL_NAME_FIELD_NAME, "%" + searchTerm + "%")
            .or(2)
            //The users must not be staff members
            .eq(User.IS_STAFF_FIELD_NAME, false)
        .and(2);

        return dao.query(statementBuilder.prepare());
    } catch (SQLException e) {
        return null;
    }
}
 

import com.j256.ormlite.stmt.Where; //導入依賴的package包/類
/**
 * 通過表列名來刪除
 *
 * @param map key是列名,value是列對應的值
 * @return
 */
public boolean deleteByColumnName(Map<String, Object> map) {
    int result = 0;
    DeleteBuilder deleteBuilder = ormLiteDao.deleteBuilder();
    Where where = deleteBuilder.where();
    try {
        where.isNotNull("id");
        for (Map.Entry<String, Object> entry : map.entrySet()) {
            where.and().eq(entry.getKey(), entry.getValue());
        }
        result = deleteBuilder.delete();
    } catch (SQLException e) {
        LogUtils.e("delete error,delete line:" + result, e);
        return false;
    }
    return result > 0;
}
 

import com.j256.ormlite.stmt.Where; //導入依賴的package包/類
/**
 * 獲取滿足指定條件的記錄數
 *
 * @param map 查詢條件鍵值組合
 * @return
 */
public long getCount(Map<String, Object> map) {
    long count = 0;
    QueryBuilder queryBuilder = ormLiteDao.queryBuilder();
    queryBuilder.setCountOf(true);
    Where where = queryBuilder.where();
    try {
        where.isNotNull("id");
        for (Map.Entry<String, Object> entry : map.entrySet()) {
            where.and().eq(entry.getKey(), entry.getValue());
        }
        PreparedQuery<T> preparedQuery = queryBuilder.prepare();
        count = ormLiteDao.countOf(preparedQuery);
    } catch (SQLException e) {
        LogUtils.e(e);
    }
    return count;
}
 

import com.j256.ormlite.stmt.Where; //導入依賴的package包/類
/**
 * 排序查詢指定條件下，大於指定值的所有記錄
 *
 * @param orderColumn 大於的列
 * @param limitValue  大於的值
 * @param columnName  查詢條件列名
 * @param value       查詢條件值
 * @param ascending   true為升序,false為降序
 * @return
 */
public List<T> queryGeByOrder(String orderColumn, Object limitValue, String columnName, Object value, boolean ascending) {
    List<T> list = null;
    QueryBuilder queryBuilder = ormLiteDao.queryBuilder();
    Where where = queryBuilder.where();
    try {
        where.eq(columnName, value);
        where.and().ge(orderColumn, limitValue);
        queryBuilder.orderBy(orderColumn, ascending);
        list = queryBuilder.query();
    } catch (SQLException e) {
        LogUtils.e(e);
    }
    return list;

}