ORM and Entities

The ORM is intended for application code where a table naturally maps to a class and where persistence should follow the domain model. Raw SQL remains the right tool for migrations, reports, bulk updates, driver-specific features, and any path that needs behavior before the ORM execution layer is complete.

Import klyn.databases.entities explicitly, then annotate persistent classes with @Entity. Columns are mapped with @Column, primary keys with @Id, and in-memory-only properties with @Transient.

import klyn.databases.entities

@Entity(tableName="T_Users")
class User:
    @Id(generationType=GenerationType.IDENTITY)
    @Column(name="idUser", nullable=false)
    public property id as Int = 0

    @Unique
    @Column(name="login", nullable=false, length=80)
    public property login as String = ""

    @Column(name="displayName", nullable=false, length=255)
    public property displayName as String = ""

    @Transient
    public property selected as Boolean = false

If @Entity.tableName or @Column.name is empty, the runtime can use the class or property name as the default mapping name. Prefer explicit names when the database schema is shared with other tools or follows a different naming convention.

DatabaseConfig stores the driver, host, port, database, user, password, and ORM options. The MariaDB configuration below mirrors the SQL examples from the previous page.

import klyn.databases.entities

config = DatabaseConfig(
    driver="mariadb",
    host="localhost",
    port=3306,
    database="app",
    username="app_user",
    password="secret"
)

config.poolSize = 10
config.showSql = false
config.mapping2ddl = "update"

emf = EntityManagerFactory(config)

DatabaseConfig.buildConnectionString() currently handles mariadb, mysql, and postgresql. The lower-level SQL package also has a SQL Server driver, but the ORM configuration layer needs an explicit mapping before SQL Server can be used through EntityManager.

An EntityManagerFactory is designed as the long-lived object. Each request, command, or worker should create its own EntityManager, begin a transaction, commit on success, roll back on failure, and close the manager.

em = emf.createEntityManager()
em.begin()

try:
    user = User()
    user.login = "ada"
    user.displayName = "Ada Lovelace"

    em.persist(user)
    em.commit()
catch ex as Exception:
    em.rollback()
    throw ex
finally:
    em.close()

EntityManager is not thread-safe. Keep it scoped to one logical unit of work and keep transactions short so database locks are held for the minimum amount of time.

The query object supports named parameters, positional parameters, pagination, single-result reads, list reads, and update/delete execution. The syntax is JPQL-style for createQuery(...) and SQL for createNativeQuery(...).

query = em.createQuery("from User u where u.login = :login")
query.setParameter("login", "ada")
query.setMaxResults(1)

user = query.getSingleResult()

nativeQuery = em.createNativeQuery(
    "select idUser, login, displayName from T_Users where login = :login",
    User.type
)
nativeQuery.setParameter("login", "ada")

rows = nativeQuery.getResultList()

Entity relationships are described with @ManyToOne, @OneToMany, and @JoinColumn. Use FetchType.LAZY when loading the related object is not always needed.

import klyn.collections
import klyn.databases.entities

@Entity(tableName="T_Roles")
class Role:
    @Id(generationType=GenerationType.IDENTITY)
    @Column(name="idRole", nullable=false)
    public property id as Int = 0

    @Column(name="name", nullable=false, length=80)
    public property name as String = ""

    @OneToMany(mappedBy="role")
    public property users as List<User> = ArrayList<User>()

@Entity(tableName="T_Users")
class User:
    @Id(generationType=GenerationType.IDENTITY)
    @Column(name="idUser", nullable=false)
    public property id as Int = 0

    @ManyToOne(fetch=FetchType.LAZY)
    @JoinColumn(name="idRole")
    public property role as Role = null

In a bidirectional relationship, mappedBy names the property on the owning side. In the example above, User.role owns the foreign key through @JoinColumn(name="idRole").

# Unsafe: login can change the SQL syntax.
login = Application.arguments[0]
query = em.createNativeQuery(
    "select * from T_Users where login = '" + login + "'",
    User.type
)

# Safe shape: the SQL text is static and the value is a parameter.
login = Application.arguments[0]
query = em.createNativeQuery(
    "select * from T_Users where login = :login",
    User.type
)
query.setParameter("login", login)

Placeholders protect values, not identifiers. If a table name, column name, or sort direction must vary, map external input to a small internal whitelist before appending any SQL fragment.

• Keep entity classes small and focused on persisted state.
• Use @Transient for caches, UI flags, and computed values that must not be saved.
• Choose GenerationType.IDENTITY for MariaDB/MySQL auto-increment columns.
• Keep one EntityManager per request, command, or worker, never as a shared singleton.
• Roll back transactions on the exceptional path before rethrowing the error.
• Prefer raw SQL until the ORM TODOs in the persistence backend are implemented and verified.
• Never log database passwords or full connection details in production logs.