SQLite 与 SQLModel

try (var db = DriverManager.getConnection("jdbc:sqlite::memory:")) {
    db.createStatement().execute(
        "CREATE TABLE task (id INTEGER PRIMARY KEY, title TEXT)");

    var insert = db.prepareStatement(
        "INSERT INTO task (title) VALUES (?)");
    insert.setString(1, "写周报");
    insert.executeUpdate();

    var query = db.prepareStatement(
        "SELECT title FROM task WHERE id = ?");
    query.setInt(1, 1);
    var rows = query.executeQuery();
    rows.next();
    System.out.println(rows.getString(1));
}

val db = helper.writableDatabase
db.execSQL("CREATE TABLE task (id INTEGER PRIMARY KEY, title TEXT)")
db.execSQL("INSERT INTO task (title) VALUES (?)", arrayOf("写周报"))

db.rawQuery(
    "SELECT title FROM task WHERE id = ?",
    arrayOf("1"),
).use { cursor ->
    cursor.moveToFirst()
    println(cursor.getString(0))
}

@Entity
@Table(indexes = @Index(columnList = "title"))
class Task {
    @Id @GeneratedValue Long id;
    String title;
    boolean done = false;
}

@Entity(tableName = "task")
data class Task(
    @PrimaryKey(autoGenerate = true) val id: Int = 0,
    @ColumnInfo(index = true) val title: String,
    val done: Boolean = false,
)

@SpringBootApplication
public class App {
    public static void main(String[] args) {
        SpringApplication.run(App.class, args);
    }
}

// application.properties
// spring.datasource.url=jdbc:sqlite:app.db
// spring.jpa.hibernate.ddl-auto=update

@Database(entities = [Task::class], version = 1)
abstract class AppDatabase : RoomDatabase() {
    abstract fun taskDao(): TaskDao
}

val db = Room.databaseBuilder(
    context,
    AppDatabase::class.java,
    "app.db",
).build()

interface TaskRepository extends JpaRepository<Task, Long> {
    Optional<Task> findByTitle(String title);
}

@Service
class TaskService {
    private final TaskRepository tasks;

    TaskService(TaskRepository tasks) {
        this.tasks = tasks;
    }

    Task create(String title) {
        var task = new Task();
        task.title = title;
        return tasks.save(task);
    }

    @Transactional
    void finish(String title) {
        var task = tasks.findByTitle(title).orElseThrow();
        task.done = true;  // 事务提交时自动写回
    }

    void remove(long id) {
        tasks.deleteById(id);
    }
}

@Dao
interface TaskDao {
    @Insert
    suspend fun insert(task: Task): Long

    @Query("SELECT * FROM task WHERE title = :title")
    suspend fun find(title: String): Task?

    @Query("SELECT * FROM task WHERE id = :id")
    suspend fun get(id: Int): Task?

    @Update
    suspend fun update(task: Task)

    @Delete
    suspend fun delete(task: Task)
}

@Entity
class Team {
    @Id @GeneratedValue Long id;
    String name;

    @OneToMany(mappedBy = "team", cascade = CascadeType.ALL)
    List<Member> members = new ArrayList<>();
}

@Entity
class Member {
    @Id @GeneratedValue Long id;
    String name;

    @ManyToOne
    Team team;
}

@Entity
data class Team(
    @PrimaryKey val id: Int,
    val name: String,
)

@Entity(
    foreignKeys = [ForeignKey(
        entity = Team::class,
        parentColumns = ["id"],
        childColumns = ["teamId"],
    )],
)
data class Member(
    @PrimaryKey val id: Int,
    val name: String,
    @ColumnInfo(index = true) val teamId: Int?,
)

data class TeamWithMembers(
    @Embedded val team: Team,
    @Relation(parentColumn = "id", entityColumn = "teamId")
    val members: List<Member>,
)

public class V2__AddPriority extends BaseJavaMigration {
    @Override
    public void migrate(Context context) throws Exception {
        try (var statement = context.getConnection().createStatement()) {
            statement.execute("ALTER TABLE task ADD COLUMN "
                + "priority INTEGER NOT NULL DEFAULT 0");
        }
    }
}

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL(
            "ALTER TABLE task ADD COLUMN " +
                "priority INTEGER NOT NULL DEFAULT 0"
        )
    }
}

val db = Room.databaseBuilder(
    context,
    AppDatabase::class.java,
    "app.db",
).addMigrations(MIGRATION_1_2).build()