Java PreparedQuery類代碼示例

import com.j256.ormlite.stmt.PreparedQuery; //導入依賴的package包/類
/**
 * This is used to query a single Event ({@link VaultEntry} before a given point in time.
 * This is useful to determine why certain events might have happened (correlation of events).
 *
 * @param timestamp The point in time to get the preceding event from.
 * @param type The Type of {@link VaultEntry} to query for.
 * @return The event of the {@link VaultEntryType} preceding the specified point in time.
 */
public VaultEntry queryLatestEventBefore(final Date timestamp, final VaultEntryType type) {
    VaultEntry returnValue = null;
    try {

        PreparedQuery<VaultEntry> query
                = vaultDao.queryBuilder().orderBy("timestamp", false)
                .limit(1L)
                .where()
                .eq(VaultEntry.TYPE_FIELD_NAME, type)
                .and()
                .le(VaultEntry.TIMESTAMP_FIELD_NAME, timestamp)
                .prepare();
        List<VaultEntry> tmpList = vaultDao.query(query);
        if (tmpList.size() > 0) {
            returnValue = tmpList.get(0);
        }
    } catch (SQLException exception) {
        LOG.log(Level.SEVERE, "Error while db query", exception);
    }
    return returnValue;
}
 

import com.j256.ormlite.stmt.PreparedQuery; //導入依賴的package包/類
public List<VaultEntry> queryGlucoseBetween(Date from, Date to) {
    List<VaultEntry> returnValues = null;
    try {
        PreparedQuery<VaultEntry> query
                = vaultDao.queryBuilder().orderBy("timestamp", true)
                .where()
                .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.GLUCOSE_BG)
                .or()
                .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.GLUCOSE_CGM)
                .or()
                .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.GLUCOSE_CGM_ALERT)
                .and()
                .between(VaultEntry.TIMESTAMP_FIELD_NAME, from, to)
                .prepare();
        returnValues = vaultDao.query(query);
    } catch (SQLException ex) {
        LOG.log(Level.SEVERE, "Error while db query", ex);
    }
    return returnValues;
}
 

import com.j256.ormlite.stmt.PreparedQuery; //導入依賴的package包/類
public List<VaultEntry> queryExerciseBetween(Date from, Date to) {
    List<VaultEntry> returnValues = null;
    try {
        PreparedQuery<VaultEntry> query
                = vaultDao.queryBuilder().orderBy("timestamp", true)
                .where()
                .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.EXERCISE_BICYCLE)
                .or()
                .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.EXERCISE_RUN)
                .or()
                .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.EXERCISE_WALK)
                .or()
                .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.EXERCISE_MANUAL)
                .and()
                .between(VaultEntry.TIMESTAMP_FIELD_NAME, from, to)
                .prepare();
        returnValues = vaultDao.query(query);
    } catch (SQLException ex) {
        LOG.log(Level.SEVERE, "Error while db query", ex);
    }
    return returnValues;
}
 

import com.j256.ormlite.stmt.PreparedQuery; //導入依賴的package包/類
public VaultEntry queryLatestEventBefore(Date timestamp, VaultEntryType type) {
    VaultEntry returnValue = null;
    try {

        PreparedQuery<VaultEntry> query
                = vaultDao.queryBuilder().orderBy("timestamp", false)
                .limit(1L)
                .where()
                .eq(VaultEntry.TYPE_FIELD_NAME, type)
                .and()
                .le(VaultEntry.TIMESTAMP_FIELD_NAME, timestamp)
                .prepare();
        List<VaultEntry> tmpList = vaultDao.query(query);
        if (tmpList.size() > 0) {
            returnValue = tmpList.get(0);
        }
    } catch (SQLException ex) {
        LOG.log(Level.SEVERE, "Error while db query", ex);
    }
    return returnValue;
}
 

import com.j256.ormlite.stmt.PreparedQuery; //導入依賴的package包/類
/**
 *
 * @param id
 * @return vault entry with respective id or null
 */
public VaultEntry queryVaultEntryById(long id) {
    List<VaultEntry> returnValues = new ArrayList<>();
    try {
        PreparedQuery<VaultEntry> query
                = vaultDao.queryBuilder().orderBy("timestamp", true)
                .limit(1l)
                .where()
                .eq(VaultEntry.ID_FIELD_NAME, id)
                .prepare();
        returnValues = vaultDao.query(query);
    } catch (SQLException ex) {
        LOG.log(Level.SEVERE, "Error while db query", ex);
        return null;
    }
    return returnValues.get(0);
}
 

import com.j256.ormlite.stmt.PreparedQuery; //導入依賴的package包/類
public List<VaultEntry> queryVaultEntrysBetween(Date from, Date to) {
    List<VaultEntry> returnValues = new ArrayList<>();
    try {
        Date fromTimestamp = TimestampUtils.createCleanTimestamp(from);
        Date toTimestamp = TimestampUtils.createCleanTimestamp(to);

        PreparedQuery<VaultEntry> query
                = vaultDao.queryBuilder().orderBy("timestamp", true)
                .where()
                .between(VaultEntry.TIMESTAMP_FIELD_NAME, fromTimestamp, toTimestamp)
                .prepare();
        returnValues = vaultDao.query(query);
    } catch (SQLException ex) {
        LOG.log(Level.SEVERE, "Error while db query", ex);
    }
    return returnValues;
}
 

import com.j256.ormlite.stmt.PreparedQuery; //導入依賴的package包/類
public List<VaultEntry> queryBasalBetween(Date from, Date to) {
    List<VaultEntry> returnValues = null;
    try {
        PreparedQuery<VaultEntry> query
                = vaultDao.queryBuilder().orderBy("timestamp", true)
                .where()
                .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.BASAL_MANUAL)
                .or()
                .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.BASAL_PROFILE)
                .or()
                .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.BASAL_INTERPRETER)
                .and()
                .between(VaultEntry.TIMESTAMP_FIELD_NAME, from, to)
                .prepare();
        returnValues = vaultDao.query(query);
    } catch (SQLException ex) {
        LOG.log(Level.SEVERE, "Error while db query", ex);
    }
    return returnValues;
}
 

import com.j256.ormlite.stmt.PreparedQuery; //導入依賴的package包/類
/**
 * This method is used to query {@link VaultEntry}s which are of a given type and lie in a specified period.
 * The types to be queried for are glucose types:
 * <ul>
 *     <li>{@link VaultEntryType#GLUCOSE_BG}</li>
 *     <li>{@link VaultEntryType#GLUCOSE_CGM}</li>
 *     <li>{@link VaultEntryType#GLUCOSE_CGM_ALERT}</li>
 * </ul>
 *
 * @param from The start of the period to query entries from.
 * @param to The end of the period to query entries from.
 * @return All {@link VaultEntry} which are of the required type and lie in the specified period.
 */
//TODO OTHER TYPES? Let's ask Jens @next meeting
public List<VaultEntry> queryGlucoseBetween(final Date from, final Date to) {
    List<VaultEntry> returnValues = null;
    try {
        PreparedQuery<VaultEntry> query
                = vaultDao.queryBuilder().orderBy("timestamp", true)
                .where()
                .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.GLUCOSE_BG)
                .or()
                .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.GLUCOSE_CGM)
                .or()
                .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.GLUCOSE_CGM_ALERT)
                .and()
                .between(VaultEntry.TIMESTAMP_FIELD_NAME, from, to)
                .prepare();
        returnValues = vaultDao.query(query);
    } catch (SQLException exception) {
        LOG.log(Level.SEVERE, "Error while db query", exception);
    }
    return returnValues;
}
 

import com.j256.ormlite.stmt.PreparedQuery; //導入依賴的package包/類
/**
 * This method is used to query {@link VaultEntry}s which are of a given type and lie in a specified period.
 * The types to be queried for are exercise types:
 * <ul>
 *     <li>{@link VaultEntryType#EXERCISE_BICYCLE}</li>
 *     <li>{@link VaultEntryType#EXERCISE_RUN}</li>
 *     <li>{@link VaultEntryType#EXERCISE_WALK}</li>
 *     <li>{@link VaultEntryType#EXERCISE_MANUAL}</li>
 * </ul>
 *
 * @param from The start of the period to query entries from.
 * @param to The end of the period to query entries from.
 * @return All {@link VaultEntry} which are of the required type and lie in the specified period.
 */
//TODO OTHER TYPES? Let's ask Jens @next meeting
public List<VaultEntry> queryExerciseBetween(final Date from, final Date to) {
    List<VaultEntry> returnValues = null;
    try {
        PreparedQuery<VaultEntry> query
                = vaultDao.queryBuilder().orderBy("timestamp", true)
                .where()
                .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.EXERCISE_BICYCLE)
                .or()
                .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.EXERCISE_RUN)
                .or()
                .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.EXERCISE_WALK)
                .or()
                .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.EXERCISE_MANUAL)
                .and()
                .between(VaultEntry.TIMESTAMP_FIELD_NAME, from, to)
                .prepare();
        returnValues = vaultDao.query(query);
    } catch (SQLException exception) {
        LOG.log(Level.SEVERE, "Error while db query", exception);
    }
    return returnValues;
}
 

import com.j256.ormlite.stmt.PreparedQuery; //導入依賴的package包/類
/**
 * This is used to retrieve an entry from the database by its identifier.
 *
 * @param id The ID of the {@link VaultEntry} to be retrieved.
 * @return The {@link VaultEntry} with respective ID or null.
 */
public VaultEntry queryVaultEntryById(final long id) {
    List<VaultEntry> returnValues;
    try {
        PreparedQuery<VaultEntry> query
                = vaultDao.queryBuilder().orderBy("timestamp", true)
                .limit(1L)
                .where()
                .eq(VaultEntry.ID_FIELD_NAME, id)
                .prepare();
        returnValues = vaultDao.query(query);
    } catch (SQLException exception) {
        LOG.log(Level.SEVERE, "Error while db query", exception);
        return null;
    }
    return returnValues.get(0);
}
 

import com.j256.ormlite.stmt.PreparedQuery; //導入依賴的package包/類
/**
 * This is used to retrieve all {@link VaultEntry}s from the database which lie in the specified period of time, no matter their type.
 *
 * @param from The start of the time period to query entries from.
 * @param to The end of the time period to query entries from.
 * @return List of all {@link VaultEntry}s which lie in the specified time period.
 */
public List<VaultEntry> queryVaultEntriesBetween(final Date from, final Date to) {
    List<VaultEntry> returnValues = new ArrayList<>();
    try {
        Date fromTimestamp = TimestampUtils.createCleanTimestamp(from);
        Date toTimestamp = TimestampUtils.createCleanTimestamp(to);

        PreparedQuery<VaultEntry> query
                = vaultDao.queryBuilder().orderBy("timestamp", true)
                .where()
                .between(VaultEntry.TIMESTAMP_FIELD_NAME, fromTimestamp, toTimestamp)
                .prepare();
        returnValues = vaultDao.query(query);
    } catch (SQLException exception) {
        LOG.log(Level.SEVERE, "Error while db query", exception);
    }
    return returnValues;
}
 

import com.j256.ormlite.stmt.PreparedQuery; //導入依賴的package包/類
/**
 * This method is used to query {@link VaultEntry}s which are of a given type and lie in a specified period.
 * The types to be queried for are basal types:
 * <ul>
 *     <li>{@link VaultEntryType#BASAL_MANUAL}</li>
 *     <li>{@link VaultEntryType#BASAL_PROFILE}</li>
 *     <li>{@link VaultEntryType#BASAL_INTERPRETER}</li>
 * </ul>
 *
 * @param from The start of the period to query entries from.
 * @param to The end of the period to query entries from.
 * @return All {@link VaultEntry} which are of the required type and lie in the specified period.
 */
public List<VaultEntry> queryBasalBetween(final Date from, final Date to) {
    List<VaultEntry> returnValues = null;
    try {
        PreparedQuery<VaultEntry> query
                = vaultDao.queryBuilder().orderBy("timestamp", true)
                .where()
                .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.BASAL_MANUAL)
                .or()
                .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.BASAL_PROFILE)
                .or()
                .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.BASAL_INTERPRETER)
                .and()
                .between(VaultEntry.TIMESTAMP_FIELD_NAME, from, to)
                .prepare();
        returnValues = vaultDao.query(query);
    } catch (SQLException exception) {
        LOG.log(Level.SEVERE, "Error while db query", exception);
    }
    return returnValues;
}
 

import com.j256.ormlite.stmt.PreparedQuery; //導入依賴的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.PreparedQuery; //導入依賴的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.PreparedQuery; //導入依賴的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;
}