SQLite ORM

The SQLite ORM framework turns a plain Java class into a typed data access object at build time. The Maven plugin reads @Entity / @Id / @Column annotations from the project’s compiled bytecode, generates one <SimpleName>Cn1Dao per entity in the source class’s package, and hands the dao to com.codename1.orm.EntityManager for typed retrieval. The dao issues prepared statements through com.codename1.db.Database, the same surface every cn1 SQLite port exposes.

The framework is a JPA-inspired, simplified alternative to the existing imperative SQLMap. It doesn’t replace SQLMap; both can be used side by side.

Property fields work the same way; the dao calls Property#get and Property#set for read and write.

EntityManager is a thin façade over Database. The underlying connection is reachable through em.database() for raw SQL when the dao surface isn’t enough. Transactions:

The managed ORM layer uses the same entity annotations on the client and the backend. New mappings, including relationships, @Version, and @GeneratedValue, live in com.codename1.annotations.db. The existing @Entity, @Id, @Column, and @DbTransient annotations remain in com.codename1.annotations. Open a com.codename1.orm.session.Session with EntityManager.openSession(). Keep one session per unit of work. A session is not thread-safe; a backend pooled entity manager can still be shared between requests, with a separate session for each request.

The existing scalar Dao API keeps its immediate-write behavior. Entities using relationships, @Version, composite IDs, generated strategies, inheritance, converters, indexes, or embedded values use sessions; asking for a legacy DAO for these mappings throws an error rather than dropping managed behavior.

A managed session maintains one instance per entity identity, detects changes at flush time, and flushes before commit and before queries in a transaction. persist, merge, and remove require an active transaction. merge returns the managed copy; it doesn’t attach the supplied detached instance. Merging an entity already scheduled for removal is rejected.

The public Session, Query, and JpqlQuery interfaces describe application operations. Generated metadata, field-access hooks, and SQL adapters live under com.codename1.impl.orm; application code shouldn’t depend on those classes.

Closing a session rolls back any active transaction. It never commits. A rollback clears the persistence context. An optimistic-lock or database failure requires rollback before the session can continue writing if the database still has an active transaction. When a failed client commit has already ended the underlying transaction, the session detaches its entities and becomes inactive. Check isTransactionActive() before rolling back; an inactive session can start a new transaction. Closing the session doesn’t close its entity manager’s database or pool. Backend Database.beginTransaction() reserves the connection to the initiating thread until commit, rollback, or close. Other threads wait; the initiating thread must finish the transaction.

Use @ManyToOne, @OneToOne, @OneToMany, and @ManyToMany on public entity fields. To-many fields use List<T>, Set<T>, or Map<K,T>. @JoinColumn names an owning foreign-key column. mappedBy identifies the owning field on the other entity. An owning to-many relationship uses a join table; @JoinTable customizes its name and key columns.

To-one relationships default to FetchType.EAGER; to-many relationships default to FetchType.LAZY. Override these defaults with the annotation’s fetch member. CascadeType selects the persistence operations to propagate. orphanRemoval=true removes children dropped from an initialized relationship. Application code maintains both sides of bidirectional object relationships. Removal cascades on inverse to-one and to-many relationships account for pending owning-side moves, including join-table changes, before choosing which children to remove.

The build enhances entity classes and field-access callers so a normal public field read can initialize a lazy relationship. No runtime proxy generator or reflection is needed. session.isLoaded(entity, "relationship") inspects load state without fetching; session.initialize(entity, "relationship") fetches explicitly. An unloaded relationship can’t be read after detaching its entity or closing its session: this throws LazyInitializationException. Generated @Mapped serialization code rejects unloaded associations rather than fetching them during serialization; initialize the relationships needed for the output first.

Fetching is synchronous. On the client, perform database work off the EDT and initialize the data needed by the UI before closing the session. Already-loaded data remains readable after the session closes.

The session query builder uses Java field paths, including relationship paths:

fetch overrides laziness for a direct relationship. To-one and inverse foreign-key collection fetches with single-column keys are batched. Ordered, map, composite-key, and join-table collections currently use individual loaders. Root pagination happens before loading fetched collections. query.count() counts matching entities without loading them and ignores pagination, ordering, and joins needed only by ordering. session.count(entity, "relationship") counts a relationship without materializing it.

createQuery accepts an explicit JPQL subset: entity or scalar selects, relationship joins, named parameters, comparisons and boolean predicates, aggregates, grouping/having, ordering, nested queries in scalar predicates, and bulk update/delete. String and numeric literals and named values become bound SQL parameters. IN :values and IN (:values) accept an Iterable or object array, including an empty collection. Unsupported syntax is rejected. Multiple scalar projections return Object[]. Entity names, field names, aliases, and named parameters accept Java identifier characters, including supplementary Unicode letters. Comparisons require at least one operand with a known storage type; two untyped parameters can’t be compared directly. IS NULL and IS NOT NULL also require a known operand type; a standalone untyped parameter is rejected. A scalar nested query requires an aggregate without GROUP BY, so it returns at most one row. IN and EXISTS may use queries that return multiple rows. Searched CASE WHEN predicate THEN value …​ ELSE value END and simple CASE expression WHEN value THEN value …​ ELSE value END work in scalar expressions, including SUM(CASE WHEN …​ THEN …​ ELSE …​ END). The first matching branch wins. An omitted ELSE yields SQL NULL; a NULL condition is not true, and simple CASE uses SQL equality (NULL doesn’t match NULL). Aggregates ignore NULL inputs; SUM over no non-null inputs yields NULL, while COUNT yields zero. Use a boxed scalar return type when NULL is possible.

Result branches must have compatible scalar storage types. Integral branches promote to Long when combined with Long literals; any real branch promotes the result to Double. At least one branch must have a known type; CASE with only untyped parameters or NULL branches is rejected. Entity and predicate result branches are unsupported; Boolean values and literals are supported. Converter mappings must agree across mapped result branches. Parameters in WHEN conditions and result branches remain bound parameters.

A DISTINCT query can order only by selected expressions. Text range comparisons and LIKE patterns are case-sensitive across supported databases. An explicit LIKE …​ ESCAPE uses a literal or named parameter for both the pattern and the escape character.

Bulk mutations use executeUpdate() inside a transaction. Repeated assignment targets are rejected during query creation. Aggregate functions are allowed inside scalar nested queries, but not directly in assignments or bulk predicates. On MySQL and MariaDB, nested queries that read the bulk mutation target table are rejected during query creation. They flush pending managed changes, bypass per-entity callbacks and cascades, and clear the context so later reads can’t reuse stale entities. Bulk assignments to required subtype fields need a non-null literal or named parameter. Other assignment expressions are rejected for those fields. Literal and bound null assignments to primitive Java fields are rejected on both runtimes. Primitive assignments also reject expressions that may return null. Use COALESCE(expression, 0) to supply a non-null fallback for nullable numeric fields, division by a variable or zero, and nullable aggregate results. A scalar aggregate without GROUP BY or HAVING can be assigned directly when its result is non-null, such as COUNT or COALESCE(SUM(…​), 0). Parameters needed to keep an expression non-null are checked before execution. Field-to-field assignments require matching enum types and converter mappings, including fields inside expressions. Integral attributes reject real-valued assignment expressions and floating-point parameter values. Assignments also check the mapped integer range, including computed results and the narrower ranges of byte, short, and char fields. Builder predicates and JPQL parameters also check the mapped integer range. Assignments to float and Float reject finite overflow and nonzero results that would round to zero, including arithmetic expressions and nested query results. Floating-point literals that overflow or underflow double precision are rejected when the query is created.

An int or long field annotated @Version is maintained by the session. Updates and deletes compare its previous value and throw OptimisticLockException when another writer changed or deleted the row. session.increment(Entity.class, id, "counter", delta) performs database arithmetic, advances the version when present, and refreshes an already-managed instance. It returns false for a missing row or an increment outside the counter/version range. It requires a transaction and an int, Integer, long, or Long attribute without a converter, including properties holding those numeric types. Other values stored as integers, such as booleans and dates, aren’t counters. Invalid entity types, fields, or identifiers are rejected before pending changes are flushed.

Scalar field projections return the mapped Java values, including enums, dates, booleans, property values, and converter-backed objects. Integral SUM results use Long on every database, and AVG returns Double. Joined paths may end in embedded fields. LOWER, UPPER, TRIM, and ABS transform stored values. Their results and comparison operands use SQL storage values. These results can’t be assigned directly to a field with a converter. MIN, MAX, COALESCE, and NULLIF preserve compatible converter mappings. COALESCE field operands must share an enum type or converter mapping so the result can be decoded consistently. Field-to-field comparisons also require matching enum types and converter mappings. Scalar projections need a known storage type when the query is created. Bare parameters and expressions such as coalesce(:a,:b) are rejected. Association projections are also rejected; select an explicit field such as order.customer.id or select the relationship target as a root entity. Queries accept at most 999 bound parameters in total, including expanded IN lists. Larger lists are rejected before query execution. Generated key columns also have a combined limit of 3,072 bytes per key, counting up to four bytes per text character. This applies to primary keys, indexes, and collection keys, and is checked before schema creation. Explicit column type declarations remain the application’s schema contract. MySQL and MariaDB reject default binary primary keys and secondary indexes before schema creation; these keys need an explicit bounded column type declaration.

JPQL arithmetic requires numeric operands. Parameters in integral expressions must be integer values; use a real operand such as 1.0 for fractional arithmetic. Division of integral operands truncates toward zero on every database. Division and remainder by zero return null. Remainder accepts integral operands only. Integral addition, subtraction, multiplication, division, and negation fail if the result exceeds the signed 64-bit range, including in bulk assignments. Aggregate queries require each non-aggregate field to be covered by GROUP BY, including fields in HAVING and ORDER BY and outer fields referenced inside correlated nested queries. LENGTH counts characters on every backend.

@MappedSuperclass contributes public inherited fields to a concrete entity. @Embedded flattens a public @Embeddable value into columns prefixed with the embedding field name. An all-null embedded value reads as null. Embedded fields can be used in query paths. Enums are stored by name. Mapped query parameters and literals accept the declared enum type, a valid constant name, or null. Invalid names and constants from other enum types are rejected before execution. Fluent and JPQL predicates require Boolean inputs for ordinary Boolean fields; fields with converters accept their declared domain values. Boolean values use numeric storage; native BOOL and BOOLEAN column declarations are rejected.

Public no-argument void methods can receive @PrePersist, @PostPersist, @PreUpdate, @PostUpdate, @PreRemove, @PostRemove, and @PostLoad callbacks. Entities with lifecycle callbacks require openSession(), even without relationships or version fields. Relationships added by @PrePersist, @PostPersist, or @PreUpdate follow PERSIST cascades in the current flush, including newly created collections. Flush rechecks entries changed by later callbacks, including newly inserted rows, until the managed state matches the database. If callbacks keep changing state for 1,000 passes, flush fails and the transaction requires rollback.

Inherited callbacks run before subclass callbacks; overriding a callback method replaces the inherited declaration. Client scalar Property fields participate in dirty checking and counters. Backend entities use plain fields because the backend runtime doesn’t contain the client properties package.

@Convert(converter=CodeConverter.class, storageType=String.class) maps a custom basic value to a SQL scalar. The converter implements com.codename1.orm.session.AttributeConverter<Code,String> and has a public no-argument constructor. It receives null values too, including mapped JPQL NULL literals. Query-builder predicates and JPQL comparison parameters and literals apply the field’s converter. The builder converts values before choosing equality or SQL null predicates. JPQL and builder IS NULL tests also apply the mapped null conversion. A converter that stores null as a non-null sentinel uses a comparison with that sentinel. Values must match the converter’s input type; use a named parameter for custom objects. Identifiers and versions can’t use converters. This includes every component of an embedded identifier.

Keep @Id for identity-column generation. Managed mappings require int, long, Integer, or Long identity fields; client IntProperty and LongProperty fields are supported too. Use @Id(autoIncrement=false) for assigned keys. @GeneratedValue selects UUID (for String IDs), SEQUENCE, or TABLE (for int/long IDs). PostgreSQL uses native sequences; other engines use a transactional generator table. UUIDs are version 4. Identity IDs become available on flush; the other strategies allocate on persist. Production migrations must provision the selected generators. Mappings with a cycle of required owning to-one links and identity-generated keys are rejected during processing: the first insert can’t supply every required key. SQLite and PostgreSQL support required cycles with keys assigned before insertion because their foreign keys are deferred until commit. MySQL and MariaDB enforce foreign keys immediately, so sessions reject required cycles before creating tables or writing data, even when the keys are known.

Identifier components are normalized and checked against their storage types before a cache lookup or SQL query, including the mapped integer range. Incompatible values raise IllegalArgumentException.

For composite IDs, use several @Id(autoIncrement=false) fields or one @EmbeddedId whose type is @Embeddable. Lookup accepts Identifier.of(component1, component2) in declared-field order; an embedded-ID mapping also accepts its key object. Relationships and join tables include all key components. Generated composite IDs are rejected.

@Inheritance on an entity root enables single-table inheritance, including abstract roots and polymorphic queries. @DiscriminatorValue("customer") assigns a stable discriminator to a concrete subtype. A base-class lookup and a subclass lookup return the same managed instance for the same row. A subtype query can’t address fields or relationships declared only by a sibling subtype. The physical table still includes the complete hierarchy for row loading. Use explicit discriminator values when class names may change. Every hierarchy member uses the root’s identifier generator, including its default generator name.

Owning Lists store positions in a list_position join-table column by default, so repeated many-to-many links and reordering survive reload. @OrderColumn can name that column explicitly. @OrderBy("name ASC, id DESC") orders an entity collection by target fields at load time; the position column still keeps repeated links distinct. An inverse mappedBy collection can use @OrderBy.

For an entity map, @MapKey(name="code") derives keys from a basic field on the target entity. The map key type must match that field’s Java type before conversion, using the boxed type for a primitive field. Keys must be non-null and unique in that collection. When changing the target key field, update the Java map keys as well.

@ElementCollection stores owned scalar values in a separate table. It supports List, Set, and Map with String keys. Values may be String, boxed primitive types, or java.util.Date. Lists preserve order and duplicate values. Map columns can be named with @MapKeyColumn; @JoinTable names the collection table. Element-map keys accept at most 255 Unicode code points on every database. query.containsElement("tags", "important") tests membership without loading the collection. Changing a mutable Date value is detected during flush.

Run session.createTables() outside application transactions for development schemas. It creates missing entity/collection tables, primary and foreign keys, one-to-one uniqueness, and relationship indexes. @Column(unique=true) and @Entity(indexes=@Index(name="by_name", fields={"name"})) declare additional indexes. Generated text keys, indexed fields, and foreign keys accept at most 255 Unicode code points during managed inserts, updates, and bulk assignments on every database. Specify @Column(type=…​) to control the SQL type and its value limits yourself.

session.validateSchema() checks mapped entity columns, storage-type families, whether null values are allowed, and primary-key membership in entity and collection tables. It doesn’t migrate schemas or validate every database-specific constraint. Existing tables are left to migrations, which EntityManager.open(name) applies before it returns. In particular, a legacy client DAO’s text char columns need migration to the managed layer’s numeric UTF-16 storage when moving to a managed schema.

Backend sessions support find(type, id, LockMode.PESSIMISTIC_READ) and PESSIMISTIC_WRITE, or lock(entity, mode), inside a transaction. PostgreSQL and MySQL/MariaDB use their row-lock clauses. SQLite rejects these modes explicitly; it can’t provide row-level pessimistic locks. Invalid or unsupported lock modes are rejected before pending changes are flushed.

This API provides managed persistence through CN1 annotations; it’s not a JPA provider or a Hibernate binary replacement. Entity access uses public fields. Getter/setter access mappings, property-backed associations, joined/table-per-class inheritance, embeddable element collections, and independently stored entity-map keys aren’t supported. Nested fetch graphs and mixed entity/scalar JPQL projections are also outside the current query subset. Use the builder’s count() for distinct composite identities. Native device execution and each backend database version still require application integration testing.

The annotation processor fails the build when:

Errors are accumulated so the first build run reports every offending entity at once.

cn1:process-annotations writes one <SimpleName>Cn1Dao per @Entity in the source class’s package (com.example.UserCn1Dao next to com.example.User), plus a single cn1app.DaoBootstrap whose constructor calls UserCn1Dao.register(), OrderCn1Dao.register(), …​ for every accepted @Entity class. At app start:

Projects with no @Entity classes produce no bootstrap; the build server probe falls through and the registry stays empty.

The runtime registry is keyed on Class#getName(); obfuscation renames the call sites and the registered keys together within a single execution. The keys are never persisted across builds, so the renaming has no observable effect on behavior.

A public top-level interface annotated with com.codename1.orm.repository.Repository and extending CrudRepository<Entity, Key> directly gets an InterfaceNameCn1Repository implementation during cn1:process-annotations. Both class literals are explicit; the key must match the mapped identifier’s boxed type (the embedded key class or Identifier for composite keys). Use Repositories.create(Records.class, session) to obtain an implementation. This factory references the interface, so application sources compile on a clean build before process-classes generates the implementation. The generated DAO bootstrap registers direct factories alongside the entity mappings. The implementation makes direct session calls. There is no runtime classpath scan, proxy generation, or Hibernate dependency.

The example’s RepositoryRecord entity has @Id public long id, public String name, and public long score. This operation opens a session, creates its repository, commits the insert, and closes the session:

The inherited CRUD methods are insert, save, findById, findAll, count, existsById, and deleteById. insert calls persist; save calls merge and returns the managed instance, which may differ from the argument. findById returns null for a missing row. deleteById removes a managed entity and observes its cascades and callbacks; deleting a missing key is a no-op in an active transaction.

Derived methods support the following bounded grammar:

@Query declares the existing JPQL subset. Every value argument requires a unique @Param("name") matching the query exactly. SQL values are bound, never interpolated. @Modifying is required for declared UPDATE/DELETE and forbidden for SELECT. Such methods return int or void. Native SQL uses @Query(value="…​", nativeSql=true) with the same named parameters, including repeated occurrences. Native parameters accept primitive and boxed scalar types, String, Date, BigInteger, BigDecimal, and byte[] BLOBs. Date values bind as epoch milliseconds and Character values as numeric code units on both client and backend. Collections and other array types are rejected during generation. Native repository selects return one scalar column of String, Long, Integer, Double, or Boolean, or a List of that type. They don’t hydrate entities. Scalar conversion rejects fractional values requested as integers and integer values requested as String; it preserves SQL NULL. Native SQL is database-specific: use portable SQL or validate each engine. Only SELECT, UPDATE, and DELETE are accepted; comments, multiple statements, positional parameters, dollar quoting, and backtick quoting are unsupported. Use standard quotes and bind values containing backslashes.

Unsupported signatures, unmapped derived fields, and missing or surplus declared parameter names produce build errors naming the repository and method. JPQL syntax and mapped names are validated when the method creates its query, before SQL execution. Native SQL syntax beyond the supported envelope is checked by the database. Generic methods, additional parent interfaces, default methods, dynamic sort arguments, and arbitrary DTO projections aren’t supported.

Repositories never open, commit, roll back, or close transactions or sessions. The caller owns those boundaries, including when the supplied Session is injected into a backend @Transactional service. Writes require an active usable transaction. Reads follow Session’s automatic flush rules. Redeclare only findById(Key) with @Lock to request a lock; pessimistic locks need an active transaction and an engine supporting row locks. SQLite rejects them explicitly.

Derived deletes and declared JPQL/native modifications flush pending changes, execute bulk SQL, then clear the entire persistence context. They bypass entity callbacks, cascades, and automatic optimistic-version updates. Previously held objects are detached and retain their old Java values; reload them before further work. There is no option to disable flush or clear. Validation failures remain IllegalArgumentException; database failures remain PersistenceException, and optimistic conflicts remain OptimisticLockException. Repository code neither swallows nor retries them. Roll back after a failed database write; Session marks the transaction rollback-only. Rollback detaches entities without reverting Java field values.