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:
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".
For ORMLite to translate between "Java Date" and "SQLite DateTime", a persister class will be needed.
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);
}
}
}
`
Define you data class with notation:
@DatabaseField(..., persisterClass = DateStringSQLiteType.class)
private Date date;
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
