[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2. How to Use

This chapter goes into more detail about how to use the various features in ORMLite.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.1 Setting Up Your Classes

To setup your classes to be persisted you need to do the following things:

  1. Add the @DatabaseTable annotation to the top of each class. You can also use @Entity.
  2. Add the @DatabaseField annotation right before each field to be persisted. You can also use @Column and others.
  3. Add a no-argument constructor to each class with at least package visibility.

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.1.1 Adding ORMLite Annotations

Annotations are special code markers have have been available in Java since version 5 that provide meta information about classes, methods, or fields. To specify what classes and fields to store in the database, ORMLite supports either its own annotations (@DatabaseTable and @DatabaseField) or the more standard annotations from the javax.persistence package. See section Using javax.persistence Annotations. Annotations are the easiest way to configure your classes but you can also configure the class using Java code or Spring XML. See section Class Configuration.

With ORMLite annotations, for each of the Java classes that you would like to persist to your SQL database, you will need to add the @DatabaseTable annotation right above the public class line. Each class marked with one of these annotations will be persisted into its own database table. For example:

 
@DatabaseTable(tableName = "accounts")
public class Account {
…

The @DatabaseTable annotations can have an optional tableName argument which specifies the name of the table that corresponds to the class. If not specified, the class name, with normalized case, is used by default. With the above example each Account object will be persisted as a row in the accounts table in the database. If the tableName was not specified, the account table would be used instead.

More advanced users may want to add a daoClass argument which specifies the class of the DAO object that will be operating on the class. This is used by the DaoManager to instantiate the DAO internally. See DaoManager.

Additionally, for each of the classes, you will need to add a @DatabaseField annotation to each of the fields in the class that are to be persisted to the database. Each field is persisted as a column of a database row. For example:

 
@DatabaseTable(tableName = "accounts")
public class Account {

    @DatabaseField(id = true)
    private String name;

    @DatabaseField(canBeNull = false)
    private String password;
    …

In the above example, each row in the accounts table has 2 columns:

The @DatabaseField annotation can have the following fields:

columnName

String name of the column in the database that will hold this field. If not set then the field name, with normalized case, is used instead.

dataType

The type of the field as the DataType class. Usually the type is taken from Java class of the field and does not need to be specified. This corresponds to the SQL type. See section Persisted Data Types.

defaultValue

String default value of the field when we are creating a new row in the table. Default is none.

width

Integer width of the field – mostly used for string fields. Default is 0 which means to take the data-type and database-specific default. For strings that means 255 characters although some databases do not support this.

canBeNull

Boolean whether the field can be assigned to null value. Default is true. If set to false then you must provide a value for this field on every object inserted into the database.

id

Boolean whether the field is the id field or not. Default is false. Only one field can have this set in a class. Id fields uniquely identity a row and are required if you want to use the query, update, refresh, and delete by ID methods. Only one of this, generatedId, and generatedIdSequence can be specified. See section Fields With id.

generatedId

Boolean whether the field is an auto-generated id field. Default is false. Only one field can have this set in a class. This tells the database to auto-generate a corresponding id for every row inserted. When an object with a generated-id is created using the Dao.create() method, the database will generate an id for the row which will be returned and set in the object by the create method. Some databases require sequences for generated ids in which case the sequence name will be auto-generated. To specify the name of the sequence use generatedIdSequence. Only one of this, id, and generatedIdSequence can be specified. See section Fields With generatedId.

generatedIdSequence

String name of the sequence number to be used to generate this value. Same as generatedId but you can specify the sequence name to use. Default is none. Only one field can have this set in a class. This is only necessary for databases which require sequences for generated ids. If you use generatedId instead then the code will auto-generate a sequence name. Only one of this, id, and generatedId can be specified. See section Fields With generatedIdSequence.

foreign

Boolean setting which identifies this field as corresponding to another class that is also stored in the database. Default is false. The field must not be a primitive type. The other class must have an id field (either id, generatedId, or generatedIdSequence) which will be stored in this table. When an object is returned from a query call, any foreign objects will just have the id field set. See section Foreign Object Fields.

useGetSet

Boolean that says that the field should be accessed with get and set methods. Default is false which instead uses direct field access via Java reflection. This may be necessary if the object you are storing has protections around it.

NOTE: The name of the get method must match getXxx() where Xxx is the name of the field with the first letter capitalized. The get must return a class which matches the field’s exactly. The set method must match setXxx(), have a single argument whose class matches the field’s exactly, and return void. For example:

 
@DatabaseField(useGetSet = true)
private Integer orderCount;

public Integer getOrderCount() {
  return orderCount;
}

public void setOrderCount(Integer orderCount) {
  this.orderCount = orderCount;
}

unknownEnumName

If the field is a Java enumerated type then you can specify the name of a enumerated value which will be used if the value of a database row is not found in the enumerated type. If this is not specified and a database row does contain an unknown name or ordinal value then a SQLException is thrown when the row is being read from the database. This is useful to handle backwards compatibility when handling out-of-date database values as well as forwards compatibility if old software is accessing up-to-date data or if you have to roll a release back.

throwIfNull

Boolean that tells ORMLite to throw an exception if it sees a null value in a database row and is trying to store it in a primitive field. By default it is false. If it is false and the database field is null, then the value of the primitive will be set to 0 (false, null, etc.). This can only be used on a primitive field.

persisted

Set this to be false (default true) to not store this field in the database. This is useful if you want to have the annotation on all of your fields but turn off the writing of some of them to the database.

format

This allows you to specify format information of a particular field. Right now this is only applicable for the following types:

unique

Adds a constraint to the table so that this field that it has to be unique across all rows in the table. For example, you might have an Account class which has a generated account-id but you also want the email address to be unique across all Accounts. If more than one field is marked as unique in a table, then each of the fields by themselves must be unique. For example, if you have the firstName and lastName fields, both with unique=true and you have "Bob", "Smith" in the database, you cannot insert "Bob", "Jones" nor "Kevin", "Smith".

To have more than 1 field that are each unique in combination, see the uniqueCombo setting. You can also use the uniqueIndexName to create an index for this field.

uniqueCombo

Adds a constraint to the table so that a combination of all fields that have uniqueCombo set to true has to be unique across all rows in the table. For example, if you have the firstName and lastName fields, both with uniqueCombo=true, and you have "Bob", "Smith" in the database, you cannot insert another "Bob", "Smith" but you can insert "Bob", "Jones" and "Kevin", "Smith".

To have fields be unique by themselves, see the unique setting. You can also use the uniqueIndexName to create an index for this field.

index

Boolean value (default false) to have the database add an index for this field. This will create an index with the name columnName with a "_idx" suffix. To specify a specific name of the index or to index multiple fields, use the indexName field.

uniqueIndex

Boolean value (default false) to have the database add a unique index for this field. Same as index but this will ensure that all of the values in the index are unique. If you just want to make sure of unique-ness then you can use the unique field instead.

indexName

String value (default none) to have the database add an index for this field with this name. You do not need to specify the index boolean as well. To index multiple fields together in one index, each of the fields should have the same indexName value.

uniqueIndexName

String value (default none) to have the database add a unique index for this field with this name. Same as index but this will ensure that all of the values in the index are unique. For example, this means that you can insert ("pittsburgh", "pa") and ("harrisburg", "pa") and ("pittsburgh", "tx") but not another ("pittsburgh", "pa").

foreignAutoRefresh

Set this to be true (default false) to have a foreign field automagically refreshed when an object is queried. The default is to just have the ID field in the object retrieved and for the caller to call refresh on the correct DAO. If this is set to true then, when the object is queried, a separate database call will be made to load of the fields of the foreign object via an internal DAO. NOTE: this will not automagically create the foreign object if you create an object that has this field set.

NOTE: This will create another DAO object internally so low memory devices may want to call refresh by hand.

NOTE: To protect against recursion, there are a couple of places were auto-refreshing has been limited. If you are auto-refreshing a class that itself has field with foreignAutoRefresh set to true or if you are auto-refreshing a class with a foreign collection, in both cases the resulting field will be set to null and not auto-refreshed. You can always call refresh on the field directly if you need it.

NOTE: If you have an auto-refreshed field that is an object that also has an auto-refreshed field, you may want to tune the maxForeignAutoRefreshLevel value. See below.

maxForeignAutoRefreshLevel

This can be used to set the maximum number of levels to configure foreign objects. For example, if you have a Question which has an foreign field of the best Answer, and the Answer has an foreign field to the corresponding question, then the configuration back and forth can get large. This is especially a problem with auto-refreshed fields when you lookup the Question it could cause an infinite loop. By default, ORMLite only goes through 2 levels but you can decrease it to 1 (0 is not valid) or increase it. The higher the number the more database transactions happen when you load in your Question.

In our example, the foreign fields in Question and Answer could be set to auto-refresh. If this is true then with the maxForeignAutoRefreshLevel set to 1, when you query for a Question, the Answer field will be auto-refreshed, but the Question field on the answer will only have its id field set. It will not be auto-refreshed.

allowGeneratedIdInsert

If this is set to true (default is false) then inserting an object with the ID field already set will not override it with a generated-id. This is useful when you have a table where you are inserting items that sometimes have IDs and sometimes need them generated. This only works if the database supports this behavior and if generatedId is also true for the field.

columnDefinition

If this is set with a string then it will be used to define the column in the CREATE TABLE statement. By default the database type is used to auto-generate the SQL necessary to create the column. The column name is provided by ORMLite. For example:

 
@DatabaseField(columnDefinition = "LONGBLOB not null",
  dataType = DataType.BYTE_ARRAY)
public byte[] bigBunchOfBytes;

foreignAutoCreate

Set this to be true (default false) to have the foreign field automatically created using an internal DAO if its ID field is not set (null or 0). So when you call dao.create() on the parent object, any foreign field that has this set to true will possibly generate an additional create call via an internal DAO. By default you have to create the object using its DAO directly. By default you have to create the object using its DAO directly. This only works if generatedId is also set to true.

 
// the account field in Order has foreignAutoCreate=true
Order order1 = new Order();
// account1 has not been created in the db yet, id field is null
order1.account = account1;
// this will create order1 in the order table _and_
// pass order1.account to the internal accountDao.create().
orderDao.create(order1);

version

Set this to true (default false) to have this field be a version for the row. A version field adds restrictions when an object is updated to the datbase that protects against data corruption when multiple entities are updating the row at the same time. This is very useful in distributed database scenarios if (for example) a utility process is updating an Account and you don’t want to protect against overwriting a user update from the web-server.

  1. The current-value of the version field is read from the object
  2. The update statement is augmented with a "WHERE version = current-value"
  3. The new-value being updated in the database is changed by ORMLite to be the current-value + 1 or the current java.util.Date
  4. If the row has been updated by another entity then the update will not change the database since the current-value will not match the version column in the database and 0 rows changed will be returned by the update method
  5. However, if the current-value does match the version field in the database then the row will be updated, the object’s version field will be set by ORMLite to the new-value, 1 will be returned from update

Only the following field types are appropriate for version fields: short, integer, long, and Date fields. Date can also be stored as a string (dataType = DataType.DATE_STRING) or long (dataType = DataType.DATE_LONG).

NOTE: If you define a Date field to be a version then you can also use it as a modification time on the row. When the object is created in the database, the version field will be automatically inserted with the current Date. When the object is updated, the version field will be updated with the current Date. Something like the following works well (the dataType=... is optional):

 
@DatabaseField(version = true, dataType = DataType.DATE_LONG)
private Date lastModified;
foreignColumnName

Name of the foreign object’s field that is tied to this table. This does not need to be specified if you are using the ID of the foreign object which is recommended. For example, if you have an Order object with a foreign Account then you may want to key off of the Account name instead of the ID.

NOTE: Setting this implies foreignAutoRefresh is also set to true because there is no way to refresh the object since the id field is not stored in the database. So when this is set, the field will be automatically refreshed in another database query.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.1.2 Using javax.persistence Annotations

Instead of using the ORMLite annotations (see section Adding ORMLite Annotations), you can use the more standard JPA annotations from the javax.persistence package. In place of the @DatabaseTable annotation, you can use the javax.persistence @Entity annotation. For example:

 
@Entity(name = "accounts")
public class Account {
…

The @Entity annotations can have an optional name argument which specifies the table name. If not specified, the class name with normalized case is used by default.

Instead of using the @DatabaseField annotation on each of the fields, you can use the javax.persistence annotations: @Column, @Id, @GeneratedValue, @OneToOne, @ManyToOne, @JoinColumn, and @Version. For example:

 
@Entity(name = "accounts")
public class Account {

    @Id
    private String name;

    @Column(nullable = false)
    private String password;
    …

The following javax.persistence annotations and fields are supported:

@Entity

Specifies that the class is stored in the database.

name

Used to specify the name of the associated database table. If not provided then the class name is used.

@Column

Specifies the field to be persisted to the database. You can also just specify the @Id annotation. The following annotation fields are supported, the rest are ignored.

name

Used to specify the name of the associated database column. If not provided then the field name is taken.

length

Specifies the length (or width) of the database field. Maybe only applicable for Strings and only supported by certain database types. Default for those that do is 255. Same as the width field in the @DatabaseField annotation.

nullable

Set to true to have a field be able to be inserted into the database with a null value. Same as the canBeNull field in the @DatabaseField annotation.

unique

Adds a constraint to the field that it has to be unique across all rows in the table. Same as the unique field in the @DatabaseField annotation.

@Id

Used to specify a field to be persisted to the database as a primary row-id. If you want to have the id be auto-generated, you will need to also specify the @GeneratedValue annotation.

@GeneratedValue

Used to define an id field as having a auto-generated value. This is only used in addition to the @Id annotation. See the generatedId field in the @DatabaseField annotation for more details.

@OneToOne or @ManyToOne

Fields with these annotations are assumed to be foreign fields. See section Foreign Object Fields. ORMLite does not enforce the many or one relationship nor does it use any of the annotation fields. It just uses the existence of either of these annotations to indicate that it is a foreign object.

@JoinColumn
name

Sets the column name of the field. Same as @Column{name=...}.

nullable

Set to true to have a field be able to be inserted into the database with a null value. Same as @Column{nullable=...}.

@Version

Using this annotation will turn short, integer, long, and Date fields into a version field. See version field.

If the @Column annotation is used on a field that has a unknown type then it is assumed to be a Serializable type field and the object should implement java.io.Serializable. See datatype serializable.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.1.3 Adding a No-Argument-Constructor

After you have added the class and field annotations, you will also need to add a no-argument constructor with at least package visibility. When an object is returned from a query, ORMLite constructs the object using Java reflection and a constructor needs to be called.

 
Account() {
    // all persisted classes must define a no-arg constructor
    // with at least package visibility
}

So your final example Account class with annotations and constructor would look like:

 
@DatabaseTable(tableName = "accounts")
public class Account {
    
    @DatabaseField(id = true)
    private String name;
    
    @DatabaseField(canBeNull = false)
    private String password;
    …

    Account() {
        // all persisted classes must define a no-arg constructor
        // with at least package visibility
    }
    …
}

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.2 Persisted Data Types

The following Java types can be persisted to the database by ORMLite. Database specific code helps to translate between the SQL types and the database specific handling of those types. See section Database Specific Code.

String (DataType.STRING)

Persisted as SQL type VARCHAR.

String (DataType.LONG_STRING)

Persisted as SQL type LONGVARCHAR which handles longer strings.

String (DataType.STRING_BYTES)

A Java String persisted as an array of bytes (byte[]) with the SQL type VARBINARY. Many databases are Unicode compliant (MySQL/Postgres) but some are not (SQLite). To store strings with accents or other special characters, you may have to encode them as an array of bytes using this type. By default the Unicode Charset is used to convert the string to bytes and back again. You can use the format field in DatabaseField to specify a custom character-set to use instead for the field. Comparison and ordering of this type may not be possible depending on the database type.

boolean or Boolean (DataType.BOOLEAN or DataType.BOOLEAN_OBJ)

Persisted as SQL type BOOLEAN.

java.util.Date (DataType.DATE)

Persisted as SQL type TIMESTAMP. This type automatically uses an internal ? argument because the string format of it is unreliable to match the database format. See section Using Select Arguments. See also DATE_LONG and DATE_STRING.

NOTE: This is a different class from java.sql.Date which is handled by DataType.SQL_DATE.

NOTE: Certain databases only provide seconds resolution so the milliseconds will be 0.

java.util.Date (DataType.DATE_LONG)

You can also specify the dataType field to the @DatabaseField annotation as a DataType.DATE_LONG in which case the milliseconds value of the Date will be stored as an LONG. See also DATE and DATE_STRING.

NOTE: This is a different class from java.sql.Date which is handled by DataType.SQL_DATE.

NOTE: Certain databases only provide seconds resolution so the milliseconds will be 0.

java.util.Date (DATE_STRING)

You can also specify the dataType field to the @DatabaseField annotation as a DataType.DATE_STRING in which case the date will be stored as a string in yyyy-MM-dd HH:mm:ss.SSSSSS format. You can use the format field in DatabaseField to set the date to another format. See also DATE and DATE_LONG.

NOTE: This is a different class from java.sql.Date which is handled by DataType.SQL_DATE.

NOTE: Certain databases only provide seconds resolution so the milliseconds will be 0.

NOTE: Because of reentrant issues with SimpleDateFormat, thread locals are used to access the formatters every time a DATE_STRING date is converted to/from the database.

byte or Byte (DataType.BYTE or DataType.BYTE_OBJ)

Persisted as SQL type TINYINT.

byte array (DataType.BYTE_ARRAY)

Array of bytes (byte[]) persisted as SQL type VARBINARY. This is different from the DataType.SERIALIZABLE type which serializes an object as an array of bytes.

NOTE: Because of backwards compatibility, any fields that are of type byte[] must be specified as DataType.BYTE_ARRAY or DataType.SERIALIZABLE using the dataType field and will not be auto-detected. See DatabaseField dataType.

 
@DatabaseField(dataType = DataType.BYTE_ARRAY)
byte[] imageBytes;
char or Character (DataType.CHAR or DataType.CHAR_OBJ)

Persisted as SQL type CHAR.

NOTE: If you are using Derby you should consider using a String instead since comparisons of character fields are not allowed.

short or Short (DataType.SHORT or DataType.SHORT_OBJ)

Persisted as SQL type SMALLINT.

int or Integer (DataType.INTEGER or DataType.INTEGER_OBJ)

Persisted as SQL type INTEGER.

long or Long (DataType.LONG or DataType.LONG_OBJ)

Persisted as SQL type BIGINT.

float or Float (DataType.FLOAT or DataType.FLOAT_OBJ)

Persisted as SQL type FLOAT.

double or Double (DataType.DOUBLE or DataType.DOUBLE_OBJ)

Persisted as SQL type DOUBLE.

Serializable (DataType.SERIALIZABLE)

Persisted as SQL type VARBINARY. This is a special type that serializes an object as a sequence of bytes and then de-serializes it on the way back. The field must be an object that implements the java.io.Serializable interface. Depending on the database type, there will be limits to the size of the object that can be stored. This is different from the DataType.BYTE_ARRAY type which stores the byte array directly.

Some databases place restrictions on this field type that it cannot be the id column in a class. Other databases do not allow you to query on this type of field at all. If your database does support it, you may also have to use a Select Argument to query for this type. See section Using Select Arguments.

NOTE: To use this type, you must specify DataType.SERIALIZABLE using the dataType field. It will not be auto-detected. See DatabaseField dataType.

 
// image is an object that implements Serializable
@DatabaseField(dataType = DataType.SERIALIZABLE)
Image image;

enum or Enum (DataType.ENUM_STRING)

Persisted by default as the enumerated value’s string name as a VARCHAR type. The string name is the default (and recommended over ENUM_INTEGER) because it allows you to add additional enums anywhere in the list without worrying about having to convert data later.

You can also also specify an unknownEnumName name with the @DatabaseField annotation which will be used if an unknown value is found in the database. See unknownEnumName.

enum or Enum (DataType.ENUM_INTEGER)

You specify the dataType field (from the @DatabaseField annotation) as a DataType.ENUM_INTEGER in which case the ordinal of the enum value will be stored as an INTEGER. The name (ENUM_STRING) is the default (and recommended) because it allows you to add additional enums anywhere in the list without worrying about having to convert data later. If you insert (or remove) an enum from the list that is being stored as a number, then old data will be un-persisted incorrectly.

You can also also specify an unknownEnumName name with the @DatabaseField annotation which will be used if an unknown value is found in the database. See unknownEnumName.

UUID (DataType.UUID)

The java.util.UUID class persisted as a VARCHAR type. It saves it as the uuid.toString() and used the UUID.fromString(String) method to convert it back again. You can also mark a UUID field as being generated-id in which case whenever it is inserted, java.util.UUID.randomUUID() is called and set on the field. See section Fields With generatedId.

BigInteger (DataType.BIG_INTEGER)

The java.math.BigInteger class persisted as a VARCHAR type. It is saved using the bigInteger.toString() and uses the string constructor to convert it back again.

BigDecimal (DataType.BIG_DECIMAL)

The java.math.BigDecimal class persisted as a VARCHAR type. It is saved using the bigDecimal.toString() and uses the string constructor to convert it back again.

BigDecimal (DataType.BIG_DECIMAL_NUMERIC)

You specify the dataType field (from the @DatabaseField annotation) as a DataType.BIG_DECIMAL_NUMERIC in which case a java.math.BigDecimal field will be persisted as a NUMERIC type. This is only supported by some database types and with various default precision settings. Android should use the default DataType.BIG_DECIMAL.

DateTime (DataType.DATE_TIME)

This stores the Joda DateTime into the database as long epoch millis. This persister uses reflection to avoid setting up a dependency with Joda jars. You can define a custom data persister if you want to natively use the class. See custom data persister.

java.sql.Date (DataType.SQL_DATE)

Persisted as SQL type TIMESTAMP.

NOTE: This is the java.sql.Date and not the java.util.Date. It is recommended that you use the java.util.Date class handled by DataType.DATE instead.

java.sql.Timestamp (DataType.TIME_STAMP)

Persisted as SQL type TIMESTAMP.

NOTE: It is recommended that you use the java.util.Date class instead which is the DataType.DATE type.

NOTE: ORMLite also supports the concept of foreign objects where the id of another object is stored in the database. See section Foreign Object Fields.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.3 Connection Sources

NOTE: With regards to connection sources, Android users should see the Android specific documentation later in the manual. See section Using With Android.

To use the database and the DAO objects, you will need to configure what JDBC calls a DataSource (see the javax.sql.DataSource class) and what ORMLite calls a ConnectionSource. A ConnectionSource is a factory for connections to the physical SQL database. Here is a code example that creates a simple, single-connection source.

 
// single connection source example for a database URI
ConnectionSource connectionSource =
  new JdbcConnectionSource("jdbc:h2:mem:account");

The package also includes the class JdbcPooledConnectionSource which is a relatively simple implementation of a pooled connection source. As database connections are released, instead of being closed, they are added to an internal list so they can be reused at a later time. New connections are created on demand only if there are no dormant connections available. JdbcPooledConnectionSource is also synchronized and can be used by multiple threads. It has settings for the maximum number of free connections before they are closed as well as a maximum age before a connection is closed.

 
// pooled connection source
JdbcPooledConnectionSource connectionSource =
  new JdbcPooledConnectionSource("jdbc:h2:mem:account");
// only keep the connections open for 5 minutes
connectionSource.setMaxConnectionAgeMillis(5 * 60 * 1000);

JdbcPooledConnectionSource also has a keep-alive thread which pings each of the dormant pooled connections every so often to make sure they are valid – closing the ones that are no long good. You can also enable the testing of the connection right before you get a connection from the pool. See the javadocs for more information.

 
// change the check-every milliseconds from 30 seconds to 60
connectionSource.setCheckConnectionsEveryMillis(60 * 1000);
// for extra protection, enable the testing of connections
// right before they are handed to the user
connectionSource.setTestBeforeGet(true);

There are many other, external data sources that can be used instead, including more robust and probably higher-performance pooled connection managers. You can instantiate your own directly and wrap it in the DataSourceConnectionSource class which delegates to it.

 
// basic Apache data source
BasicDataSource dataSource = new BasicDataSource();
String databaseUrl = "jdbc:h2:mem:account";
dataSource.setUrl(databaseUrl);
// we wrap it in the DataSourceConnectionSource
ConnectionSource connectionSource = 
  new DataSourceConnectionSource(dataSource, databaseUrl);

When you are done with your ConnectionSource, you will want to call a close() method to close any underlying connections. Something like the following pattern is recommended.

 
JdbcConnectionSource connectionSource =
    new JdbcPooledConnectionSource("jdbc:h2:mem:account");
try {
    // work with the data-source and DAOs
    …
} finally {
    connectionSource.close();
}

Unfortunately, the DataSource interface does not have a close method so if you are using the DataSourceConnectionSource you will have to close the underlying DataSource by hand – the close() method on the DataSourceConnectionSource does nothing.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.4 Setting Up the DAOs

Once you have annotated your classes and defined your ConnectionSource you will need to create the Data Access Object (DAO) class(es), each of which will handle all database operations for a single persisted class. Each DAO has two generic parameters: the class we are persisting with the DAO, and the class of the ID-column that will be used to identify a specific database row. If your class does not have an ID field, you can put Object or Void as the 2nd argument. For example, in the above Account class, the "name" field is the ID column (id = true) so the ID class is String.

The simplest way to create your DAO is to use the createDao static method on the DaoManager class to create a DAO class. For example:

 
Dao<Account, String> accountDao =
  DaoManager.createDao(connectionSource, Account.class);
Dao<Order, Integer> orderDao =
  DaoManager.createDao(connectionSource, Order.class);

NOTE: You should use the DaoManager.createDao() method to create your DAO classes so if they are needed by internal ORMLite functionality, they can be reused and not regenerated. Building a DAO can be an expensive operation and for devices with limited resources (like mobile apps), DAOs should be reused if at all possible.

If you want a better class hierarchy or if you need to add additional methods to your DAOs, you should consider defining an interface which extends the Dao interface. The interface isn’t required but it is a good pattern so your code is less tied to JDBC for persistence. The following is an example DAO interface corresponding to the Account class from the previous section of the manual:

 
/** Account DAO which has a String id (Account.name) */
public interface AccountDao extends Dao<Account, String> {
    // empty wrapper, you can add additional DAO methods here
}

Then in the implementation, you should extend the BaseDaoImpl base class. Here’s the example implementation of your DAO interface.

 
/** JDBC implementation of the AccountDao interface. */
public class AccountDaoImpl extends BaseDaoImpl<Account, String>
  implements AccountDao {
    // this constructor must be defined
    public AccountDaoImpl(ConnectionSource connectionSource)
      throws SQLException {
        super(connectionSource, Account.class);
    }
}

To make use of your custom DAO classes, you need to add the daoClass field to the @DatabaseTable on the corresponding entity class:

 
@DatabaseTable(daoClass = AccountDaoImpl.class)
public class Account {
   …
}

That’s all you need to define your DAO classes. You are free to add more methods to your DAO interfaces and implementations if there are specific operations that are needed and not provided by the Dao base classes. More on how to use these DAOs later. See section DAO Usage.

NOTE: If you are using a custom DAO then be sure to add the daoClass argument to the @DatabaseTable annotation which specifies the class of your custom DAO. This is used by the DaoManager to instantiate the DAO internally. See DaoManager.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.5 Supported Databases

ORMLite supports the following database flavors. Some of them have some specific documentation that needs to be obeyed.

MySQL

Tables are created in MySQL with the InnoDB engine by default using CREATE TABLE ... ENGINE=InnoDB. If you want to use another engine, you can instantiate the MysqlDatabaseType directly and use the setCreateTableSuffix() method to use the default or another engine. Also, MySQL does some funky stuff with the last-modification time if a Date is defined as a TIMESTAMP so DATETIME was used instead.

Postgres

No special instructions.

H2

No special instructions. We use this database for all of our internal testing with in-memory and small on-disk databases.

SQLite

There are multiple SQLite drivers out there. Make sure you use the Xerial driver and not the Zentus driver which does not support generated ids.

If you want to use the internal SQLite time/date functions (such as strftime) on Date fields, your fields should be of type DATE_STRING to have the dates match in the internal date formats. For example:

 
@DatabaseField(dataType = DataType.DATE_STRING)
private Date date;
Android SQLite

Android’s SQLite database is accessed through direct calls to the Android database API methods.

Derby

There are two drivers for Derby: one embedded and one client/server. ORMLite makes an attempt to detect the right driver but you may have to set the right database type on your ConnectionSource directly if it doesn’t. See section Database Specific Code.

HSQLDB

No special instructions.

Microsoft SQL Server

No special instructions.

Netezza

As of August 2011, the driver should be considered alpha and possibly with bugs. I do not currently have access to a server running a Netezza database. We will try to keep this driver up to date with the help of contributors. Thanks to Richard Kooijman for the driver. Please contact us if you want to help with development of this driver.

ODBC

As of August 2011, the driver should be considered alpha and possibly with bugs. I do not have access to a server running Microsoft’s Open Database Connectivity. We will try to keep this driver up to date with the help of contributors. Thanks to Dale Asberry for the driver. Please contact us if you want to help with development of this driver.

DB2

I do not have access to an DB2 database so we cannot run any tests to make sure that our support for it works well. Please contact us if you want to help with development of this driver.

Oracle

I do not have access to an Oracle database so we cannot run any tests to make sure that our support for it works well. Please contact us if you want to help with development of this driver.

Please contact the author if your database is not supported.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.6 Tying It All Together

So you have annotated the objects to be persisted, added the no-argument constructor, created your ConnectionSource, and defined your DAO classes. You are ready to start persisting and querying your database objects. You will need to download and add the H2 jar file to your class-path if you want to run the example as-is. See the H2 home page.

The following code ties it all together:

 
// h2 by default but change to match your database
String databaseUrl = "jdbc:h2:mem:account";
JdbcConnectionSource connectionSource =
  new JdbcConnectionSource(databaseUrl);

// instantiate the dao with the connection source
AccountDaoImpl accountDao = new AccountDaoImpl(connectionSource);

// if you need to create the 'accounts' table make this call
TableUtils.createTable(connectionSource, Account.class);

// create an instance of Account
Account account = new Account("Jim Coakley");

// persist the account object to the database
accountDao.create(account);
…

// destroy the data source which should close underlying connections
connectionSource.destroy();

For more examples, see the code later in the manual. See section Example Code.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.7 Table and Schema Creation

There a couple of tools that ORMLite provides to help with creating tables and schema for the classes that you are storing in the database.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.7.1 TableUtils Class

The TableUtils class provides a number of static methods that help with creating and dropping tables as well as providing the schema statements.

createTable(ConnectionSource, Class)

This method takes the ConnectionSource and a class and creates the table associated with the class. It uses the annotations from the class to determine the various fields and characteristics of the table. It returns the number of statements executed to create the table.

 
TableUtils.createTable(connectionSource, Account.class);
createTableIfNotExists(ConnectionSource, Class)

Similar to the last method but it will only create the table if it doesn’t exist. This is not supported on all database types.

createTable(ConnectionSource, DatabaseTableConfig)

Similar to the last method but instead of a class, this method uses a DatabaseTableConfig to determine the various fields and characteristics of the table.

 
ArrayList<DatabaseFieldConfig> fieldConfigs =
   new ArrayList<DatabaseFieldConfig>();
fieldConfigs.add(new DatabaseFieldConfig("id", null,
   DataType.UNKNOWN, null, 0, false, false, true, null,
   false, null, false, null, false, null, false, null,
   null, false));
…
DatabaseTableConfig<Account> tableConfig =
   new DatabaseTableConfig<Account>(Account.class,
     fieldConfigs);
// this returns number of statements executed to create table
TableUtils.createTable(connectionSource, tableConfig);
createTableIfNotExists(ConnectionSource, DatabaseTableConfig)

Similar to the last method but it will only create the table if it doesn’t exist. This is not supported on all database types.

dropTable(ConnectionSource, Class, boolean ignoreErrors)

This method drops the table associated with the class. It uses the annotations from the class to determine the name of the table to drop. This is not undo-able and most likely will be used only in tests since production tables are dropped rarely.

The ignoreErrors argument is useful when you are dropping a table before you are creating it and the table may not already exist. If ignoreErrors is true then any exceptions are swallowed.

dropTable(ConnectionSource, DatabaseTableConfig, boolean ignoreErrors)

Same as the previous method but it will use the DatabaseTableConfig to determine the name of the table to drop.

getCreateTableStatements(ConnectionSource, Class)

This is similar to the createTable method but will return a list of statements that can be used to create a class. This is useful if you want to load the schema during some sort of database initialization process.

getCreateTableStatements(ConnectionSource, DatabaseTableConfig)

Same as the previous method but with a DatabaseTableConfig instead.

clearTable(ConnectionSource, Class)

Clear all data out of the table. For certain database types and with large sized tables, which may take a long time. In some configurations, it may be faster to drop and re-create the table. This is [obviously] very destructive and is unrecoverable.

clearTable(ConnectionSource, DatabaseTableConfig)

Same as the previous method but with a DatabaseTableConfig instead.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.7.2 TableCreator Class

The TableCreator class is engineered for use with Spring but could be useful in other configurations. It is configured with the ConnectionSource and the list of DAOs that are being used by the program.

It will automagically create the tables associated with those DAOs if the system property ormlite.auto.create.tables is set with the value "true". It also will automagically drop the tables that were created if the system property ormlite.auto.drop.tables is set with the value "true". This is especially useful in tests when you are starting with a test database that needs to get the latest schema but in production you want to make specific schema changes by hand. You can set the system properties in your test start scripts but leave them off in the production scripts.

 
List<Dao<?, ?>> daoList = new ArrayList<Dao<?, ?>>();
daoList.add(accountDao);
…

TableCreator creator =
   new TableCreator(connectionSource, daoList);
// create the tables if the right system property is set
creator.maybeCreateTables();
…

// later, we may want to drop the tables that were created 
creator.maybeDropTables();

For a real life example of using TableCreator you can see spring example.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.8 Identity Columns

Database rows can be identified by a particular column which is defined as the identity column. Rows do not need to have an identity column but many of the DAO operations (update, delete, refresh) require an identity column. The identity can either be supplied by the user or auto-generated by the database. Identity columns have unique values for every row in the table and they must exist if you want to query-by-id, delete, refresh, or update a particular row using the DAO. To configure a field as an identity field, you should use one (and only one) of the following three settings from @DatabaseField: id, generatedId, or generatedIdSequence.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.8.1 Fields With id

With our Account example class, the string name field has been marked with id = true. This means that the name is the identity column for the object. Each account stored in the database must have a unique value for the name field – you cannot have two rows with the name "John Smith".

 
public class Account {
    @DatabaseField(id = true)
    private String name;
    …
}

When you use the DAO to lookup an account with a particular name, you will use the identity field to locate the Account object in the database:

 
Account account = accountDao.queryForId("John Smith");
if (account == null) {
    // the name "John Smith" does not match any rows
}

NOTE: If you need to change the value of an object’s id field, you must use the Dao.updateId() method which takes the current object still with its old id value and the new value. ORMLite has to first locate the object by its old id and then update it with the new id. See updateId.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.8.2 Fields With generatedId

You can configure a long or integer field to be a generated identity column. The id number column for each row will then be automatically generated by the database.

 
public class Order {
    @DatabaseField(generatedId = true)
    private int id;
    …
}

When an Order object is passed to create and stored to the database, the generated identity value is returned by the database and set on the object by ORMLite. In the majority of database types, the generated value starts at 1 and increases by 1 every time a new row is inserted into the table.

 
// build our order object without an id
Order order = new Order("Jim Sanders", 12.34);
…
orderDao.create(order);
System.out.println("Order id " + order.getId() +
   " was persisted to the database");
// query for the order with an id of 1372
order = orderDao.queryForId(1372);
if (order == null) {
   // none of the order rows have an id of 1372
}

In the above code example, an order is constructed with name and amount (for example). When it is passed to the DAO’s create method, the id field has not been set. After it has been saved to the database, the generated-id will be set on the id field by ORMLite and will be available when getId() is called on the order after the create method returns.

NOTE: Other special field types such as UUID can also be generated. See UUID.

NOTE: You can use the allowGeneratedIdInsert field setting to allow insert objects into a table with or without an id already set. See allowGeneratedIdInsert.

NOTE: Depending on the database type, you may not be able to change the value of an auto-generated id field.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.8.3 Fields With generatedIdSequence

Some databases use what’s called a sequence number generator to provide the generated id value. If you use generatedId = true with those databases, a sequence name will be auto-generated by ORMLite. If, however, you need to set the name of the sequence to match existing schema, you can used the generatedIdSequence value which takes a string name for the sequence.

 
public class Order {
    @DatabaseField(generatedIdSequence = "order_id_seq")
    private int id;
    …
}

In the above example, the id value is again automatically generated but using a sequence with the name order_id_seq. This will throw an exception if you are working with a database which does not support sequences.

NOTE: Depending on the database type, you may not be able to change the value of an auto-generated id field.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.9 DAO Usage

The following database operations are easily accomplished by using the DAO methods:

create and persist an object to the database

This inserts a new row to the database table associated with the object.

 
Account account = new Account();
account.name = "Jim Coakley";
accountDao.create(account);
query for it’s id column

If the object has an id field defined by the annotations, then we can lookup an object in the database using its id.

 
Account account = accountDao.queryForId(name);
if (account == null) {
  account not found handling … 
}
update the database row associated with the object

If you change fields in an object in memory, you must call update to persist those changes to the database. This requires an id field.

 
account.password = "_secret";
accountDao.update(account);
refreshing an object if the database has changed

If some other entity has changed a row the database corresponding to an object in memory, you will need to refresh that object to get the memory object up-to-date. This requires an id field.

 
accountDao.refresh(account);
delete the account from the database

Removes the row that corresponds to the object from the database. Once the object has been deleted from the database, you can continue to use the object in memory but any update or refresh calls will most likely fail. This requires an id field.

 
accountDao.delete(account);

iterate through all of the rows in a table:

The DAO is also an iterator so you can easily run through all of the rows in the database:

 
// page through all of the accounts in the database
for (Account account : accountDao) {
    System.out.println(account.getName());
}

NOTE: you must page through all items for the iterator to close the underlying SQL object. If you don’t go all of the way through to the loop, ORMLite does not know to close the underlying object, and a connection to the database may be leaked that will only be closed if the garbage collector reaps some time later which may cause bugs in your code. Use the try ... finally wrapped iterator patterns below.

For example, the following for loop is a very bad pattern.

 
for (Account account : accountDao) {
    if (account.getName().equals("Bob Smith")) {
        // you can't return, break, or throw from here
        return account;
    }
}

This also may happen if an exception is thrown out of the loop so for loops should not be used in these cases. This is also the case with lazy loaded collections. See lazy collection iterator.

use the iterator directly

You can also use the iterator directly because the for loops are not optimal. This allows you to use try ... finally which is a much better pattern.

 
CloseableIterator<Account> iterator =
    accountDao.closeableIterator();
try {
    while (iterator.hasNext()) {
        Account account = iterator.next();
        System.out.println(account.getName());
    }
} finally {
    // close it at the end to close underlying SQL statement
    iterator.close();
}
get a "wrapped iterable"

You can also use the "wrapped iterable" which allows you to do the close in the finally while still using for loops.

 
CloseableWrappedIterable<Account> wrappedIterable =
    accountDao.getWrappedIterable();
try {
    for (Account account : wrappedIterable) {
        …
    }
} finally {
    wrappedIterable.close();
}

For a detailed list of the methods in the DAO see section DAO Methods.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.10 Indexing Fields

ORMLite provides some limited support for indexing of various fields in your data classes. First off, it is important to point out that any field marked as an id field is already indexed. Fields that are id fields do not need to have additional indexes built and if they are specified, errors may result with certain database.

To add an index on a non-id field, all you need to do is add the index = true boolean field to the @DatabaseField annotation. See index. This will create a non-unique index after the table is created for the field and will drop the index if the table is then dropped. Indexes help optimize queries and can significantly improve times on queries to medium to large sized tables.

 
public class Account {
    @DatabaseField(id = true)
    private String name;
    // this indexes the city field so queries on city
    // will go faster for large tables
    @DatabaseField(index = true)
    private String city;
    …
}

This example creates the index account_city_idx on the Account table. If you want to use a different name, you can use the indexName = "othername" field instead which allows you to specify the name of the index.

If you often query on (for example) city and state fields together, you might want to create an index on both fields. ORMLite supports creating indexes on multiple fields by specifying the same indexName value for each of the fields you want to be included in the index.

 
@DatabaseField(indexName = "account_citystate_idx")
private String city;
@DatabaseField(indexName = "account_citystate_idx")
private String state;

This example will create one index for both the city and state fields. Note that queries on the city by itself will not be optimized – only queries on both city and state will be. With some databases, it may be better to create a single field index on each field and let the database use both indexes if you are querying on city and state. For other databases, creating an index on multiple fields is recommended. You may need to experiment and use the SQL EXPLAIN command to pinpoint how your database is utilizing your indexes.

To create unique indexes, there is a uniqueIndex = true and uniqueIndexName = "othername" fields also available on the @DatabaseField annotation. These work the same as the above settings but will instead create unique indexes that ensure that no two row has the same value(s) for the indexed field(s).


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.11 Issuing Raw SQL Statements

In a number of instances, using the defined DAO functionality may not be enough to change your database. For this reason, ORMLite has calls which allow you to issue raw query, update, and execute statements to the database.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.11.1 Issuing Raw Queries

The built-in methods available in the Dao interface and the QueryBuilder classes don’t provide the ability to handle all types of queries. For example, aggregation queries (sum, count, avg, etc.) cannot be handled as an object since every query has a different result list. To handle these queries, you can issue raw database queries using the queryRaw methods on DAO. These methods return a GenericRawResults object which represents a result as an array of strings, array of objects, or user mapped objects. See the documentation on the GenericRawResults object for more details on how to use it, or take a look at the following examples.

 
// find out how many orders account-id #10 has
GenericRawResults<String[]> rawResults =
  orderDao.queryRaw(
    "select count(*) from orders where account_id = 10");
// there should be 1 result
List<String[]> results = rawResults.getResults();
// the results array should have 1 value
String[] resultArray = results.get(0);
// this should print the number of orders that have this account-id
System.out.println("Account-id 10 has " + resultArray[0] + " orders");

You can even use the QueryBuilder to build the raw queries for you if you’d lile using the prepareStatementString() method. See section Query Builder Basics.

 
QueryBuilder<Account, Integer> qb = accountDao.queryBuilder();
qb.where().ge("orderCount", 10);
results = accountDao.queryRaw(qb.prepareStatementString());

If you want to use the QueryBuilder with arguments to the raw query then you should do something like:

 
QueryBuilder<Account, Integer> qb = accountDao.queryBuilder();
// we specify a SelectArg here to generate a ? in statement string below
qb.where().ge("orderCount", new SelectArg());
// the 10 at the end is an optional argument to fulfill SelectArg above
results = accountDao.queryRaw(qb.prepareStatementString(), 10);

If you want to use the QueryBuilder with aggregate or other raw/custom arguments do something like the following. Since there is only one result out you can use the genericRawResults.getFirstResult() method:

 
QueryBuilder<Account, Integer> qb = accountDao.queryBuilder();
// select 2 aggregate functions as the return
qb.selectRaw("MIN(orderCount)", "MAX(orderCount)");
// the results will contain 2 string values for the min and max
results = accountDao.queryRaw(qb.prepareStatementString());
String[] values = results.getFirstResult();

For large numbers of results, you should consider using the iterator() method on the GenericRawResults object which uses database paging. For example:

 
// return the orders with the sum of their amounts per account
GenericRawResults<String[]> rawResults =
  orderDao.queryRaw(
    "select account_id,sum(amount) from orders group by account_id");
// page through the results
for (String[] resultArray : rawResults) {
  System.out.println("Account-id " + resultArray[0] + " has "
    + resultArray[1] + " total orders");
}
rawResults.close();

If you are just trying to query for a single value (typically an aggregate function like MAX) then you can use the queryRawValue(...) method:

 
long maxUnits = orderDao.queryRawValue("select max(units) from orders");

If some of your fields cannot be appropriately mapped to strings, you can also return the fields as an Object[] if you pass in the types of the resulting columns. For example:

 
// return the orders with the sum of their amounts per account
GenericRawResults<Object[]> rawResults =
  orderDao.queryRaw(
    "select account_id,sum(amount) from orders group by account_id",
    new DataType[] { DataType.LONG, DataType.INTEGER });
// page through the results
for (Object[] resultArray : rawResults) {
  System.out.println("Account-id " + resultArray[0] + " has "
    + resultArray[1] + " total orders");
}
rawResults.close();

NOTE: select * can return fields in different orders depending on the database type. To make sure that the data-type array matches the returned columns you must specify the fields specifically and not with a SQL *.

You can also map the results into your own object by passing in a RawRowMapper object. This will call the mapping object with an array of strings and allow it to convert the strings into an object. The DAO provides a default RawRowMapper that can be gotten from orderDao.getRawRowMapper() that knows how to convert the string array into the object.

You can also define your own custom mapper if the results are more complex. For example:

 
// return the orders with the sum of their amounts per account
GenericRawResults<Foo> rawResults =
  orderDao.queryRaw(
    "select account_id,sum(amount) from orders group by account_id",
    new RawRowMapper<Foo>() {
            public Foo mapRow(String[] columnNames,
              String[] resultColumns) {
                return new Foo(Long.parseLong(resultColumns[0]),
                    Integer.parseInt(resultColumns[1]));
        }
    });
// page through the results
for (Foo foo : rawResults) {
  System.out.println("Account-id " + foo.accountId + " has "
    + foo.totalOrders + " total orders");
}
rawResults.close();

NOTE: The query and the resulting strings can be very database-type specific. For example:

  1. Certain databases need any column names specified in uppercase – others need lowercase.
  2. You may have to quote your column or table names if they are reserved words.
  3. The resulting column names also could be uppercase or lowercase.
  4. select * can return fields in different orders depending on the database type.

NOTE: Like other ORMLite iterators, you will need to make sure you iterate through all of the results to have the statement closed automatically. You can also call the GenericRawResults.close() method to make sure the iterator, and any associated database connections, is closed.

NOTE: If you are using the QueryBuilder#prepareStatementString() method to build your query, it may have added the id column to the selected column list if the Dao object has an id you did not include it in the columns you selected. So your results might have one more column than you are expecting.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.11.2 Issuing Raw Update Statements

You can also issue raw update statements against the database if the DAO functionality does not give you enough flexibility. Update SQL statements must contain the reserved words INSERT, DELETE, or UPDATE. For example:

 
fooDao.updateRaw("INSERT INTO accountlog (account_id, total) "
   + "VALUES ((SELECT account_id,sum(amount) FROM accounts))

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.11.3 Issuing Raw Execute Statements

You can also issue raw update statements against the database if the DAO functionality does not give you enough flexibility. For example:

 
fooDao.executeRaw("ALTER TABLE accountlog DROP COLUMN partner");

[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.12 Foreign Object Fields

ORMLite supports the concept of "foreign" objects where one or more of the fields correspond to an object are persisted in another table in the same database. For example, if you had an Order objects in your database and each Order had a corresponding Account object, then the Order object would have foreign Account field. With foreign objects, just the id field from the Account is persisted to the Order table as the column "account_id". For example, the Order class might look something like:

 
@DatabaseTable(tableName = "orders")
public class Order {

    @DatabaseField(generatedId = true)
    private int id;
    
    @DatabaseField(canBeNull = false, foreign = true)
    private Account account;
    …
}

When the Order table was created, something like the following SQL would be generated:

 
CREATE TABLE `orders`
   (`id` INTEGER AUTO_INCREMENT , `account_id` INTEGER,
    PRIMARY KEY (`id`)); 

Notice that the name of the field is not account but is instead account_id. You will need to use this field name if you are querying for it. You can set the column name using the columnName field in the DatabaseField annotation. See columnName.

When you are creating a field with a foreign object, please note that the foreign object will not automatically be created for you. If your foreign object has a generated-id which is provided by the database then you need to create it before you create any objects that reference it. For example:

 
Account account = new Account("Jim Coakley");
accountDao.create(account);
// this will create the account object and set any generated ids

// now we can set the account on the order and create it
Order order = new Order("Jim Sanders", 12.34);
order.setAccount(account);
…
orderDao.create(order);

If you want some level of auto creation, then you can use the foreignAutoCreate setting. See foreignAutoCreate.

When you query for an order, you will get an Order object with an account field object that only has its id field set. The rest of the fields in the foreign Account object will have default values (null, 0, false, etc.). If you want to use other fields in the Account, you must call refresh on the accountDao class to get the Account object filled in. For example:

 
Order order = orderDao.queryForId(orderId);
System.out.println("Account-id on the order should be set: " +
   order.account.id);
// this should print null for order.account.name
System.out.println("But other fields on the account should not be set: "
   + order.account.name);

// so we refresh the account using the AccountDao
accountDao.refresh(order.getAccount());
System.out.println("Now the account fields will be set: " +
   order.account.name);

You can have the foreign objects automagically refreshed by using the foreignAutoRefresh setting. See foreignAutoRefresh.

NOTE: Because we use refresh, foreign objects are therefor required to have an id field.

You can query for foreign fields in a couple of different ways. The following examples show code which queries for all orders that match a certain account field. Since the id field is the name field, you can query by the name field of the account:

 
// query for all orders that match a certain account
List<Order> results =
  orderDao.queryBuilder().where().
    eq("account_id", account.getName()).query();

Or you can just let ORMLite extract the id field from the account itself. This will perform an equivalent query to the above:

 
// ORMLite will extract and use the id field internally
List<Order> results =
  orderDao.queryBuilder().where().
    eq("account_id", account).query();

There is example code to show how to use foreign objects. See foreign objects example.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.13 Foreign Collections

In the above section of the manual we gave the example of the Order class having a foreign object field to the Account table. A foreign collection allows you to add a collection of orders on the account table. Whenever an Account object is returned by a query or refreshed by the DAO, a separate query is made over the order table and a collection of orders is set on the account. All of the orders in the collection have a corresponding foreign object that matches the account. For example:

 
public class Account {
    …
    @ForeignCollectionField(eager = false)
    ForeignCollection<Order> orders;
    …
}

In the above example, the @ForeignCollectionField annotation marks that the orders field is a collection of the orders that match the account. The field type of orders must be either ForeignCollection<T> or Collection<T> – no other collections are supported because they are much heavier with many methods to support. The @ForeignCollectionField annotation supports the following fields:

eager

There are two different types of foreign collections: eager or lazy. If eager is set to true then the separate query is made immediately and the orders are stored as a list within the collection. If eager is set to false (the default) then the collection is considered to be "lazy" and will iterate over the database using the Dao.iterator() only when a method is called on the collection.

WARNING: By default, if you have eager collections of objects that themselves have eager collections, the inner collection will be created as lazy for performance reasons. If you need change this see the maxEagerLevel setting below.

maxEagerLevel

This can be set to the number of times to expand an eager foreign collection’s foreign collection. If you query for A and it has an eager foreign-collection of field B which has an eager foreign-collection of field C (…), then a lot of database operations are going to happen whenever you query for A. By default this value is 1 meaning that if you query for A, the collection of B will be eager fetched but each of the B objects will have a lazy collection instead of an eager collection of C. It should be increased only if you know what you are doing.

columnName

The name of the column. This is only used if you want to match the string passed to Dao.assignEmptyForeignCollection(Object, String) or to specify that you want the collection returned when you specify queryBuilder.selectColumns(...). See selectColumns.

orderColumnName

The name of the column in the foreign object that we should order the collection by.

orderAscending

If an order column has been defined with the above orderColumnName, this sets the order as ascending (true which is the default) or descending (false).

foreignFieldName

Name of the field (not the column name) in the class that the collection is holding that corresponds to the collection. This is needed if there are two foreign fields in the class in the collection (such as a tree structure) and you want to identify which column you want in this collection.

WARNING: Due to some internal complexities, this it field/member name in the class and not the column-name.

Remember that when you have a ForeignCollection field, the class in the collection must (in this example Order) must have a foreign field for the class that has the collection (in this example Account). If Account has a foreign collection of Orders, then Order must have an Account foreign field. It is required so ORMLite can find the orders that match a particular account.

WARNING: With lazy collections, even the size() method causes a iteration across the database. You’ll most likely want to just use the iterator() and toArray() methods on lazy collections.

NOTE: Like with the Dao.iterator() method, the iterator returned by a lazy collection must be closed when you are done with it because there is a connection open to the database underneath. A close happens either if you go all of the way through the iterator or if you call close() on it. Only the ForeignCollection returns a closeable iterator. This means that for loops across lazy loaded collections are a bad pattern. For more details about iterating across lazy collections, see the iterator documentation. See iterator.

The foreign collections support the add() and remove() methods in which case the objects will be both added or removed from the internal list if the collection is eager, and DAO calls will be made to affect the order table as well for both eager and lazy collections.

NOTE: When you are adding a new object to a foreign collection, this will also add it to the database by calling through to dao.create(obj). If the object has already been created in the database then you should instead set the foreign field on the object and call dao.update(obj). If you add it here the DAO will try to create it in the database again which will most likely cause an error.

NOTE: When you call update on an object with a foreign collection (like Account above) the objects that are stored in the collection are not automatically written to the database as well. There is no way for ORMLite to be able to detect which of the objects has been updated unfortunately. If you update an object in the collection you should call the update(data) method on the ForeignCollection to make sure that the object is persisted. For example:

 
for (Order order : account.orders()) {
   // if we are changing some field in the order
   order.setAmount(123);
   // then we need to update it in the database
   account.orders.update(order);
}

Lastly, there is example code to show how to use foreign collections. See foreign collections example.


[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

2.14 DAO Enabled Objects

Another ORM pattern is to have the objects perform the database operations on themselves instead of using a Database Access Object (DAO). For example, given a data object foo, you would call foo.refresh() instead of fooDao.refresh(foo). The default pattern is to use the Dao classes which allow your data classes to have their own hierarchy and it isolates the database code in the Daos. However, you are free to use the BaseDaoEnabled class if you prefer this pattern.

All classes that are able to refresh (update, delete, etc.) themselves should extend the BaseDaoEnabled class. For example:

 
@DatabaseTable(tableName = "accounts")
public class Account extends BaseDaoEnabled {

    @DatabaseField(id = true)
    private String name;

    @DatabaseField(canBeNull = false)
    private String password;
    …

To first create the object, you will need to use the DAO object or you will need to set the dao on the object so that it can self create:

 
account.setDao(accountDao);
account.create();

However, whenever an object is returned by ORMLite as query results, the DAO has already been set on the object it extends the BaseDaoEnabled class.

 
Account account = accountDao.queryForId(name);
account.setPassword(newPassword);
account.update();

This will also work for foreign fields.

 
Order order = orderDao.queryForId(orderId);
// load all of the fields from the account
order.getAccount().refresh();

The javadocs for BaseDaoEnabled will have the most up-to-date list of self operations but right now the class can do:

create

To create the object you will need to use the DAO or to call setDao() on the object.

refresh

Refresh the object in case it was updated in the database.

update

After you make changes to the object in memory, update it in the database.

updateId

If you need to update the id of the object, you must use this method. You cannot change the id field in the object and then call the update method because then the object will not be found.

delete

Delete it from the database.

Feedback on this is welcome.


[ << ] [ >> ]           [Top] [Contents] [Index] [ ? ]

This document was generated by Gray Watson on December 16, 2013 using texi2html 1.82.