CQM CQM - 7 months ago 41
Java Question

ormlite creates tables with columns in wrong order

Ormlite is creating my table from the same model object in two different ways

expected:

create table statement changed 0 rows: CREATE TABLE `clientspace`
(`id` INTEGER PRIMARY KEY AUTOINCREMENT , `user_is_following` INTEGER ,
`space_id` INTEGER , `client_id` INTEGER , `name` VARCHAR , `state` VARCHAR ,
`latitude` FLOAT , `longitude` FLOAT , `url_twitter` VARCHAR ,.....


wrong:

executed create table statement changed 1 rows: CREATE TABLE `clientspace`
(`about_text` VARCHAR , `website` VARCHAR , `app_name` VARCHAR ,
`url_twitter` VARCHAR , `color` VARCHAR , `url_facebook` VARCHAR ,
`tagline` VARCHAR , `state` VARCHAR , `logo` VARCHAR , `skyline` VARCHAR ,
`name` VARCHAR .....


I cannot retrieve rows by queryId without knowing which column index the id field will be!

why does this happen and how can I make the column order consistent.

There are some instances where I drop tables and recreate them, instead of deleting, this shouldn't affect the column order

TableUtils.createTable(getConnectionSource(), ClientSpace.class);


I am also using Android

the structure of my
ormlite_config
file is in the format of the expected table

edit: found this topic, still haven't come to a conclusion yet https://groups.google.com/forum/#!topic/ormlite-android/N-tJ-2OVKL8

where one tip was
"Don't ever rely on the defined order of fields in any database. This
is not just an Android tip, it's just good database practice in
general."
which I definitely wouldn't do if I didn't have to use
queryforId(
and give it the EXACT INDEX of a row result.

example:

final GenericRawResults<String[]> rawCustomFieldResults =
customFieldDao.queryRaw(customFieldQuery.prepareStatementString());
for(final String[] resultArray : rawCustomFieldResults) {
CustomFieldVO mCustomField =
customFieldDao.queryForId(Integer.valueOf(resultArray[2]));


where
queryForId(Integer.valueOf(resultArray[2])
requires me to have only the id and know EXACTLY what index the id will be in.

Answer

I cannot retrieve rows by queryId without knowing which column index the id field will be! Why does this happen and how can I make the column order consistent?

ORMLite is using reflection (or the table configuration file) to determine the field order. For some reason when it goes through your entities, reflection is generating the field list in an order you aren't expecting.

"Don't ever rely on the defined order of fields in any database. This is not just an Android tip, it's just good database practice in general."

This is a very good tip.

where queryForId(Integer.valueOf(resultArray[2]) requires me to have only the id and know EXACTLY what index the id will be in.

Sure but since you are doing your own custom query, you can specify the SELECT list in a particular order so you will know which field is in which position of the resultArray. You should not be doing SELECT * there.

Also, GenericRawResults has a getColumnNames() method which returns the column names with which you can verify which column name is in which position.

Comments