mn create-app example.micronaut.micronautguide \
    --features=data-jdbc,oracle,serialization-jackson \
    --build=gradle \
    --lang=java \
    --test=junit

package example.micronaut;

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;

@Serdeable
@MappedEntity("TASK")
public record Task(
    @Id @GeneratedValue @Nullable Long id,
    String title,
    boolean completed) { (1)
}

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;

import java.util.List;

@JdbcRepository(dialect = Dialect.ORACLE, version = "23.1") (1)
public 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 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;

import java.util.List;

@ExecuteOn(TaskExecutors.BLOCKING) (1)
@Controller("/tasks")
public class TaskController {

    private final TaskRepository taskRepository;

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

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

    @Get("/open")
    public List<Task> open() {
        return taskRepository.findByCompletedFalse();
    }

    @Get("/completed")
    public List<Task> completed() {
        return taskRepository.findByCompletedTrue();
    }

    @Put("/{id}/complete")
    public Task complete(Long id) {
        if (taskRepository.updateCompleted(id, true) == 0) { (3)
            throw new HttpStatusException(HttpStatus.NOT_FOUND, "Task not found");
        }
        return 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.junit5.annotation.MicronautTest;
import io.micronaut.transaction.TransactionOperations;
import jakarta.inject.Inject;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertTrue;

@MicronautTest(transactional = false) (1)
class OracleBooleanTest {

    @Inject
    TaskRepository taskRepository;

    @Inject
    TransactionOperations<Connection> transactionOperations;

    @Inject
    @Client("/")
    HttpClient httpClient;

    @BeforeEach
    void deleteTasks() {
        taskRepository.deleteAll();
    }

    @Test
    void completedColumnIsNativeBoolean() {
        assertEquals("BOOLEAN", columnDataType("TASK", "COMPLETED")); (2)
    }

    @Test
    void queriesBooleanValues() {
        Task write = taskRepository.save(new Task(null, "Write the guide", false));
        Task review = taskRepository.save(new Task(null, "Review the guide", false));

        assertEquals(1, taskRepository.updateCompleted(write.id(), true)); (3)

        assertEquals(List.of("Write the guide"), titles(taskRepository.findByCompletedTrue())); (4)
        assertEquals(List.of("Review the guide"), titles(taskRepository.findByCompletedFalse()));
        assertEquals(List.of("Write the guide"), titles(taskRepository.findByCompleted(true))); (5)

        assertTrue(taskRepository.findById(write.id()).orElseThrow().completed());
        assertFalse(taskRepository.findById(review.id()).orElseThrow().completed());
    }

    @Test
    void completesTaskOverHttp() {
        BlockingHttpClient client = httpClient.toBlocking();
        Task task = client.retrieve(HttpRequest.POST("/tasks", Map.of("title", "Publish the guide")), Task.class);
        assertFalse(task.completed());

        Task completed = client.retrieve(HttpRequest.PUT("/tasks/" + task.id() + "/complete", ""), Task.class); (6)
        assertTrue(completed.completed());

        assertEquals(List.of("Publish the guide"),
            titles(client.retrieve(HttpRequest.GET("/tasks/completed"), Argument.listOf(Task.class))));
        assertTrue(client.retrieve(HttpRequest.GET("/tasks/open"), Argument.listOf(Task.class)).isEmpty());
    }

    private static List<String> titles(List<Task> tasks) {
        return tasks.stream().map(Task::title).toList();
    }

    private String columnDataType(String table, String column) {
        return transactionOperations.executeRead(status -> {
            String sql = "SELECT data_type FROM user_tab_columns WHERE table_name = ? AND column_name = ?";
            try (PreparedStatement statement = status.getConnection().prepareStatement(sql)) {
                statement.setString(1, table);
                statement.setString(2, column);
                try (ResultSet resultSet = statement.executeQuery()) {
                    return resultSet.next() ? resultSet.getString(1) : null;
                }
            } catch (SQLException e) {
                throw new IllegalStateException(e);
            }
        });
    }
}

./gradlew 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