Ormlite query using date

Question

public GenericRawResults<Object[]> getCountByStatus(Date date,int status){
        Log.info("CallDayPlanningDao",date.toString());
        GenericRawResults<Object[]> rawResults=null;
        Dao callDayPlanningDao = getDao(CallDayPlanning.class);
        QueryBuilder query = callDayPlanningDao.queryBuilder();
        int year = date.getYear();
        int month = date.getMonth();
        Date date1 = new Date(year, month,1);
        Date date2 = new Date(year, month+1,1);

        Date startDate = new Date(date1.getTime()-5);
        Date endDate = new Date(date2.getTime()-5);
        try {
            **query.where().between("calldate", startDate, endDate);**//This line is not working
            if(status==Constant.cnStatus){
                query.where().in("callstatus", status,Constant.ccStatus);
            }else{
                query.where().eq("callstatus", status);
            }
            query.groupBy("calldate");
            query.selectRaw("calldate,count(*)");
            rawResults = callDayPlanningDao.queryRaw(query.prepareStatementString(), new DataType[] {
                            DataType.DATE_STRING, DataType.INTEGER });
            // page through the results

        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        return rawResults;
    }

Well, I want to get the count of the object, but the condition of date is invalid, I get all the data from my database.Somebody could help me?Thanks.

Answer 1

I am new to ORMLite and faced the same problem when accessed SQLite database.

It took me a whole day today to figure it out, here is the summary:

1. I found format "yyyy-M-d H:m:s" works fine in ORMLite for dealing with SQLite DateTime data type, not ORMLite's default format "yyyy-MM-dd HH:mm:ss.SSSSS".

2. For ORMLite to translate between "Java Date" and "SQLite DateTime", a persister class will be needed.

3. Here shows the code of the persister class I use, which override the public functions of DateStringType and use "dateFormatConfig" instead of defaultDateFormatConfig" :

`

public class DateStringSQLiteType extends DateStringType {

protected static final DateStringFormatConfig dateFormatConfig = new DateStringFormatConfig(
        "yyyy-M-d H:m:s");

private static final DateStringSQLiteType singleTon = new DateStringSQLiteType();

public static DateStringSQLiteType getSingleton() {
    return singleTon;
}

private DateStringSQLiteType() {
    super(SqlType.STRING, new Class<?>[0]);
}

/**
 * Convert a default string object and return the appropriate argument to a
 * SQL insert or update statement.
 */
@Override
public Object parseDefaultString(FieldType fieldType, String defaultStr)
        throws SQLException {
    DateStringFormatConfig formatConfig = convertDateStringConfig(
            fieldType, dateFormatConfig);
    try {
        // we parse to make sure it works and then format it again
        return normalizeDateString(formatConfig, defaultStr);
    } catch (ParseException e) {
        throw SqlExceptionUtil.create("Problems with field " + fieldType
                + " parsing default date-string '" + defaultStr
                + "' using '" + formatConfig + "'", e);
    }
}

/**
 * Return the SQL argument object extracted from the results associated with
 * column in position columnPos. For example, if the type is a date-long
 * then this will return a long value or null.
 * 
 * @throws SQLException
 *             If there is a problem accessing the results data.
 * @param fieldType
 *            Associated FieldType which may be null.
 */
@Override
public Object resultToSqlArg(FieldType fieldType, DatabaseResults results,
        int columnPos) throws SQLException {
    return results.getString(columnPos);
}

/**
 * Return the object converted from the SQL arg to java. This takes the
 * database representation and converts it into a Java object. For example,
 * if the type is a date-long then this will take a long which is stored in
 * the database and return a Date.
 * 
 * @param fieldType
 *            Associated FieldType which may be null.
 * @param sqlArg
 *            SQL argument converted with
 *            {@link #resultToSqlArg(FieldType, DatabaseResults, int)} which
 *            will not be null.
 */
@Override
public Object sqlArgToJava(FieldType fieldType, Object sqlArg, int columnPos)
        throws SQLException {
    String value = (String) sqlArg;
    DateStringFormatConfig formatConfig = convertDateStringConfig(
            fieldType, dateFormatConfig);
    try {
        return parseDateString(formatConfig, value);
    } catch (ParseException e) {
        throw SqlExceptionUtil.create("Problems with column " + columnPos
                + " parsing date-string '" + value + "' using '"
                + formatConfig + "'", e);
    }
}

/**
 * Convert a Java object and return the appropriate argument to a SQL insert
 * or update statement.
 */
@Override
public Object javaToSqlArg(FieldType fieldType, Object obj) {
    DateFormat dateFormat = convertDateStringConfig(fieldType,
            dateFormatConfig).getDateFormat();
    return dateFormat.format((Date) obj);
}

/**
 * @throws SQLException
 *             If there are problems creating the config object. Needed for
 *             subclasses.
 */
@Override
public Object makeConfigObject(FieldType fieldType) {
    String format = fieldType.getFormat();
    if (format == null) {
        return dateFormatConfig;
    } else {
        return new DateStringFormatConfig(format);
    }
}

}

`

4. Define you data class with notation: @DatabaseField(..., persisterClass = DateStringSQLiteType.class) private Date date;

5. It worked fine for me, can do "Between" query like:

   list = foo.getDao().queryBuilder().where().between(HistoryStandardView.DATE_FIELD_NAME, new Date(98,1,1), new Date(115,1,1)).query();
   

ORMLite's logger shows the resulting statement:

[DEBUG] StatementExecutor query of 'SELECT * FROM `HistoryStandardView` WHERE `date` BETWEEN '1998-2-1 0:0:0' AND '2015-2-1 0:0:0' ' returned 2 results

Answer 2

Correct me if am wrong is your calldate column's type is DataType.DATE_STRING ? If that's the case it means that the persisted data type is VARCHAR so when you execute your query your doing a String comparison and not a Date comparison. So to solve your problem you can either :

1. Change your calldate column's type to DataType.DATE which is represented as a TIMESTAMP.
2. Change your calldate column's type to DataType.DATE_LONG.
3. Find a way to do a String comparison that matches what your need (for instance calling the sql date(calldate) fonction if your calldate values matches a Time Strings format see http://www.sqlite.org/lang_datefunc.html).

Here is what i did, it's not pretty but works like wanted to:

QueryBuilder<OffreEntity, Integer> qb = this.daoOffre.queryBuilder();
//Need to format the date i want to compare so it can actually be compare with what i have on db
SimpleDateFormat dateFormatter = new SimpleDateFormat("yyMMdd");
String correctFormat = dateFormatter.format(dateLimite);
//In db the date is represented as a VARCHAR with format dd/MM/yy so i need to reformat so it matches yyMMdd
String rawQuery = String.format("substr(%1$s,7)||substr(%1$s,4,2)||substr(%1$s,1,2) > '%2$s'", OffreEntity.COLUMN_NAME_DATE, correctFormat);
qb.where().raw(rawQuery);
offresDept = qb.query();

Hope it helps!

Ps: Thanks to Jack Douglas for the date format query