mn create-app example.micronaut.micronautguide \
    --features=data-jdbc,oracle,serialization-jackson \
    --build=maven \
    --lang=groovy \
    --test=spock

package example.micronaut

import groovy.transform.CompileStatic
import io.micronaut.core.annotation.Nullable
import io.micronaut.data.annotation.GeneratedValue
import io.micronaut.data.annotation.Id
import io.micronaut.data.annotation.MappedEntity
import io.micronaut.serde.annotation.Serdeable

@CompileStatic
@Serdeable
@MappedEntity('TASK')
class Task {
    @Id
    @GeneratedValue
    @Nullable
    Long id
    String title
    boolean completed (1)

    Task() {
    }

    Task(@Nullable Long id, String title, boolean completed) {
        this.id = id
        this.title = title
        this.completed = completed
    }
}

CREATE TABLE "TASK" (
    "ID" NUMBER(19) NOT NULL PRIMARY KEY,
    "TITLE" VARCHAR(255) NOT NULL,
    "COMPLETED" BOOLEAN NOT NULL
)

package example.micronaut

import io.micronaut.data.annotation.Id
import io.micronaut.data.jdbc.annotation.JdbcRepository
import io.micronaut.data.model.query.builder.sql.Dialect
import io.micronaut.data.repository.CrudRepository

@JdbcRepository(dialect = Dialect.ORACLE, version = '23.1') (1)
interface TaskRepository extends CrudRepository<Task, Long> {

    List<Task> findByCompletedTrue() (2)

    List<Task> findByCompletedFalse() (3)

    List<Task> findByCompleted(boolean completed) (4)

    long updateCompleted(@Id Long id, boolean completed) (5)
}

package example.micronaut

import groovy.transform.CompileStatic
import io.micronaut.http.HttpStatus
import io.micronaut.http.annotation.Body
import io.micronaut.http.annotation.Controller
import io.micronaut.http.annotation.Get
import io.micronaut.http.annotation.Post
import io.micronaut.http.annotation.Put
import io.micronaut.http.annotation.Status
import io.micronaut.http.exceptions.HttpStatusException
import io.micronaut.scheduling.TaskExecutors
import io.micronaut.scheduling.annotation.ExecuteOn

@CompileStatic
@ExecuteOn(TaskExecutors.BLOCKING) (1)
@Controller('/tasks')
class TaskController {

    private final TaskRepository taskRepository

    TaskController(TaskRepository taskRepository) {
        this.taskRepository = taskRepository
    }

    @Post
    @Status(HttpStatus.CREATED)
    Task create(@Body('title') String title) { (2)
        taskRepository.save(new Task(null, title, false))
    }

    @Get('/open')
    List<Task> open() {
        taskRepository.findByCompletedFalse()
    }

    @Get('/completed')
    List<Task> completed() {
        taskRepository.findByCompletedTrue()
    }

    @Put('/{id}/complete')
    Task complete(Long id) {
        if (taskRepository.updateCompleted(id, true) == 0L) { (3)
            throw new HttpStatusException(HttpStatus.NOT_FOUND, 'Task not found')
        }
        taskRepository.findById(id).orElseThrow()
    }
}

package example.micronaut

import io.micronaut.core.type.Argument
import io.micronaut.http.HttpRequest
import io.micronaut.http.client.BlockingHttpClient
import io.micronaut.http.client.HttpClient
import io.micronaut.http.client.annotation.Client
import io.micronaut.test.extensions.spock.annotation.MicronautTest
import io.micronaut.transaction.TransactionOperations
import jakarta.inject.Inject
import spock.lang.Specification

import java.sql.Connection
import java.sql.PreparedStatement
import java.sql.ResultSet

@MicronautTest(transactional = false) (1)
class OracleBooleanSpec extends Specification {

    @Inject
    TaskRepository taskRepository

    @Inject
    TransactionOperations<Connection> transactionOperations

    @Inject
    @Client('/')
    HttpClient httpClient

    void setup() {
        taskRepository.deleteAll()
    }

    void 'completed column is native BOOLEAN'() {
        expect:
        columnDataType('TASK', 'COMPLETED') == 'BOOLEAN' (2)
    }

    void 'queries boolean values'() {
        given:
        Task write = taskRepository.save(new Task(null, 'Write the guide', false))
        Task review = taskRepository.save(new Task(null, 'Review the guide', false))

        expect:
        taskRepository.updateCompleted(write.id, true) == 1L (3)

        taskRepository.findByCompletedTrue()*.title == ['Write the guide'] (4)
        taskRepository.findByCompletedFalse()*.title == ['Review the guide']
        taskRepository.findByCompleted(true)*.title == ['Write the guide'] (5)

        taskRepository.findById(write.id).orElseThrow().completed
        !taskRepository.findById(review.id).orElseThrow().completed
    }

    void 'completes a task over HTTP'() {
        given:
        BlockingHttpClient client = httpClient.toBlocking()

        when:
        Task task = client.retrieve(HttpRequest.POST('/tasks', [title: 'Publish the guide']), Task)

        then:
        !task.completed

        when:
        Task completed = client.retrieve(HttpRequest.PUT("/tasks/${task.id}/complete", ''), Task) (6)

        then:
        completed.completed
        client.retrieve(HttpRequest.GET('/tasks/completed'), Argument.listOf(Task))*.title == ['Publish the guide']
        client.retrieve(HttpRequest.GET('/tasks/open'), Argument.listOf(Task)).isEmpty()
    }

    private String columnDataType(String table, String column) {
        transactionOperations.executeRead { status ->
            String sql = 'SELECT data_type FROM user_tab_columns WHERE table_name = ? AND column_name = ?'
            PreparedStatement statement = status.connection.prepareStatement(sql)
            try {
                statement.setString(1, table)
                statement.setString(2, column)
                ResultSet resultSet = statement.executeQuery()
                try {
                    return resultSet.next() ? resultSet.getString(1) : null
                } finally {
                    resultSet.close()
                }
            } finally {
                statement.close()
            }
        }
    }
}

./mvnw test

curl -X POST http://localhost:8080/tasks \
     -H 'Content-Type: application/json' \
     -d '{"title":"Write the guide"}'

curl -X POST http://localhost:8080/tasks \
     -H 'Content-Type: application/json' \
     -d '{"title":"Review the guide"}'

curl -X PUT http://localhost:8080/tasks/1/complete

curl http://localhost:8080/tasks/completed

curl http://localhost:8080/tasks/open