CODEWITHSUNDEEP
androidarrayswhere-clauseormlite
MARM
MARM

Reputation: 363

ORMLite where clausule in String Array

I use ormlite and I have a db with a field:

public static final String NAME = "name"; 
@DatabaseField (canBeNull = false, dataType = DataType.SERIALIZABLE, columnName = NAME)
private String[] name = new String[2];

And I would like to get all elements that name[0] and name[1] are "car". I try to add a where clausule like:

NAMEDB nameDB = null;
Dao<NAMEDB, Integer> daoName = this.getHelper().getDao(NAMEDB.class);
QueryBuilder<NAMEDB, Integer> queryName = daoName.queryBuilder();
Where<NAMEDB, Integer> where = queryName.where();
where.in(nameDb.NAME, "car");

But it doesn't work because it's an array string.

I have other fields: 

public static final String MARK = "mark";

@DatabaseField (canBeNull = false, foreign = true, index = true, columnName = MARK)
private String mark = null;

And I can do this:

whereArticulo.in(nameDB.MARK, "aaa");

How can I solve my problem? Thanks.

Upvotes: 2

Views: 3282

Answers (3)

Elad Nava
Elad Nava

Reputation: 7906

As ronbo4610 suggested, it is a good idea to use a custom data persister in this case, to store the array as a string in the database separated by some kind of delimiter. You can then search the string in your WHERE clause just as you would any other string. (For example, using the LIKE operator)

I have implemented such a data persister. In order to use it, you must add the following annotation above your String[] object in your persisted class:

@DatabaseField(persisterClass = ArrayPersister.class)

In addition, you must create a new class called "ArrayPersister" with the following code:

import com.j256.ormlite.field.FieldType;
import com.j256.ormlite.field.SqlType;
import com.j256.ormlite.field.types.StringType;
import org.apache.commons.lang3.StringUtils;

public class ArrayPersister extends StringType {
    private static final String delimiter = ",";
    private static final ArrayPersister singleTon = new ArrayPersister();

    private ArrayPersister() {
        super(SqlType.STRING, new Class<?>[]{ String[].class });
    }

    public static ArrayPersister getSingleton() {
        return singleTon;
    }

    @Override
    public Object javaToSqlArg(FieldType fieldType, Object javaObject) {
        String[] array = (String[]) javaObject;

        if (array == null) {
            return null;
        }
        else {
            return StringUtils.join(array, delimiter);
        }
    }

    @Override
    public Object sqlArgToJava(FieldType fieldType, Object sqlArg, int columnPos) {
        String string = (String)sqlArg;

        if (string == null) {
            return null;
        }
        else {
            return string.split(delimiter);
        }
    }
}

Upvotes: 2

ronbo4610
ronbo4610

Reputation: 101

It seems to me that a third option to store a string array (String[] someStringArray[]) in the database using Ormlite would be to define a data persister class that converts the string array to a single delimited string upon storage into the database and back again to a string array after taking it out of the database.

E.g., persister class would convert ["John Doe", "Joe Smith"] to "John Doe | Joe Smith" for database storage (using whatever delimiter character makes sense for your data) and converts back the other way when taking the data out of the database.

Any thoughts on this approach versus using Serializable or a foreign collection? Anyone tried this?

I just wrote my first persister class and it was pretty easy. I haven't been able to identify through web search or StackOverflow search that anyone has tried this.

Thanks.

Upvotes: 4

Gray
Gray

Reputation: 116888

Unfortunately ORMLite does not support querying fields that are the type SERIALIZABLE. It is storing the array as a serialized byte[] so you cannot query against the values with an IN query like:

where.in(nameDb.NAME, "car");

ORMLite does support foreign collections but you have to set it up yourself with another class holding the names. See the documentation with sample code:

http://ormlite.com/docs/foreign-collection

Upvotes: 1

Related Questions