PersonMapperTest.java

/*
 *    Copyright 2016-2025 the original author or authors.
 *
 *    Licensed under the Apache License, Version 2.0 (the "License");
 *    you may not use this file except in compliance with the License.
 *    You may obtain a copy of the License at
 *
 *       https://www.apache.org/licenses/LICENSE-2.0
 *
 *    Unless required by applicable law or agreed to in writing, software
 *    distributed under the License is distributed on an "AS IS" BASIS,
 *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *    See the License for the specific language governing permissions and
 *    limitations under the License.
 */
package examples.simple;

import static examples.simple.AddressDynamicSqlSupport.address;
import static examples.simple.PersonDynamicSqlSupport.addressId;
import static examples.simple.PersonDynamicSqlSupport.birthDate;
import static examples.simple.PersonDynamicSqlSupport.employed;
import static examples.simple.PersonDynamicSqlSupport.firstName;
import static examples.simple.PersonDynamicSqlSupport.id;
import static examples.simple.PersonDynamicSqlSupport.lastName;
import static examples.simple.PersonDynamicSqlSupport.occupation;
import static examples.simple.PersonDynamicSqlSupport.person;
import static org.assertj.core.api.Assertions.assertThat;
import static org.assertj.core.api.Assertions.assertThatExceptionOfType;
import static org.assertj.core.api.Assertions.entry;
import static org.mybatis.dynamic.sql.SqlBuilder.*;

import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Optional;

import org.apache.ibatis.datasource.unpooled.UnpooledDataSource;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.apache.ibatis.mapping.Environment;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.mybatis.dynamic.sql.SortSpecification;
import org.mybatis.dynamic.sql.delete.DeleteDSLCompleter;
import org.mybatis.dynamic.sql.delete.render.DeleteStatementProvider;
import org.mybatis.dynamic.sql.exception.NonRenderingWhereClauseException;
import org.mybatis.dynamic.sql.insert.render.GeneralInsertStatementProvider;
import org.mybatis.dynamic.sql.render.RenderingStrategies;
import org.mybatis.dynamic.sql.select.CountDSLCompleter;
import org.mybatis.dynamic.sql.select.SelectDSLCompleter;
import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
import org.mybatis.dynamic.sql.update.render.UpdateStatementProvider;

class PersonMapperTest {

    private static final String JDBC_URL = "jdbc:hsqldb:mem:aname";
    private static final String JDBC_DRIVER = "org.hsqldb.jdbcDriver";

    private SqlSessionFactory sqlSessionFactory;

    @BeforeEach
    void setup() throws Exception {
        Class.forName(JDBC_DRIVER);
        InputStream is = getClass().getResourceAsStream("/examples/simple/CreateSimpleDB.sql");
        assert is != null;
        try (Connection connection = DriverManager.getConnection(JDBC_URL, "sa", "")) {
            ScriptRunner sr = new ScriptRunner(connection);
            sr.setLogWriter(null);
            sr.runScript(new InputStreamReader(is));
        }

        UnpooledDataSource ds = new UnpooledDataSource(JDBC_DRIVER, JDBC_URL, "sa", "");
        Environment environment = new Environment("test", new JdbcTransactionFactory(), ds);
        Configuration config = new Configuration(environment);
        config.addMapper(PersonMapper.class);
        config.addMapper(PersonWithAddressMapper.class);
        config.addMapper(AddressMapper.class);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(config);
    }

    @Test
    void testSelect() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);

            List<PersonRecord> rows = mapper.select(c ->
                    c.where(id, isEqualTo(1))
                    .or(occupation, isNull()));

            assertThat(rows).hasSize(3);
        }
    }

    @Test
    void testSelectEmployed() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);

            List<PersonRecord> rows = mapper.select(c ->
                    c.where(employed, isTrue())
                    .orderBy(id));

            assertThat(rows).hasSize(4);
            assertThat(rows.get(0).id()).isEqualTo(1);
        }
    }

    @Test
    void testSelectUnemployed() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);

            List<PersonRecord> rows = mapper.select(c ->
                    c.where(employed, isFalse())
                            .orderBy(id));

            assertThat(rows).hasSize(2);
            assertThat(rows.get(0).id()).isEqualTo(3);
        }
    }

    @Test
    void testSelectWithTypeConversion() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);

            List<PersonRecord> rows = mapper.select(c ->
                    c.where(id, isEqualTo("1").map(Integer::parseInt))
                            .or(occupation, isNull()));

            assertThat(rows).hasSize(3);
        }
    }

    @Test
    void testSelectWithTypeConversionAndFilterAndNull() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);

            List<PersonRecord> rows = mapper.select(c ->
                    c.where(id, isEqualToWhenPresent((String) null).map(Integer::parseInt))
                            .or(occupation, isNull()));

            assertThat(rows).hasSize(2);
        }
    }

    // this example is in the quick start documentation...
    @Test
    void testGeneralSelect() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);

            SelectStatementProvider selectStatement = select(id.as("A_ID"), firstName, lastName, birthDate, employed,
                        occupation, addressId)
                    .from(person)
                    .where(id, isEqualTo(1))
                    .or(occupation, isNull())
                    .build()
                    .render(RenderingStrategies.MYBATIS3);

            List<PersonRecord> rows = mapper.selectMany(selectStatement);
            assertThat(rows).hasSize(3);
        }
    }

    @Test
    void testSelectAll() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);

            List<PersonRecord> rows = mapper.select(SelectDSLCompleter.allRows());

            assertThat(rows).hasSize(6);
            assertThat(rows.get(0).id()).isEqualTo(1);
            assertThat(rows.get(5).id()).isEqualTo(6);
        }
    }

    @Test
    void testSelectAllOrdered() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);

            List<PersonRecord> rows = mapper
                    .select(SelectDSLCompleter.allRowsOrderedBy(lastName.descending(), firstName.descending()));

            assertThat(rows).hasSize(6);
            assertThat(rows.get(0).id()).isEqualTo(5);
            assertThat(rows.get(5).id()).isEqualTo(1);
        }
    }

    @Test
    void testSelectDistinct() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);

            List<PersonRecord> rows = mapper.selectDistinct(c ->
                    c.where(id, isGreaterThan(1))
                    .or(occupation, isNull()));

            assertThat(rows).hasSize(5);
        }
    }

    @Test
    void testSelectWithTypeHandler() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);

            List<PersonRecord> rows = mapper.select(c ->
                    c.where(employed, isEqualTo(false))
                    .orderBy(id));

            assertThat(rows).hasSize(2);
            assertThat(rows.get(0).id()).isEqualTo(3);
            assertThat(rows.get(1).id()).isEqualTo(6);
        }
    }

    @Test
    void testSelectByPrimaryKeyWithMissingRecord() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);

            Optional<PersonRecord> row = mapper.selectByPrimaryKey(300);
            assertThat(row).isNotPresent();
        }
    }

    @Test
    void testFirstNameIn() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);

            List<PersonRecord> rows = mapper.select(c ->
                    c.where(firstName, isIn("Fred", "Barney")));

            assertThat(rows).hasSize(2);
            assertThat(rows.get(0))
                    .isNotNull()
                    .extracting("lastName").isNotNull()
                    .extracting("name").isEqualTo("Flintstone");
            assertThat(rows.get(1))
                    .isNotNull()
                    .extracting("lastName").isNotNull()
                    .extracting("name").isEqualTo("Rubble");
        }
    }

    @Test
    void testOrderByCollection() {
        Collection<SortSpecification> orderByColumns = List.of(firstName);

        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);

            List<PersonRecord> rows = mapper.select(c -> c
                    .where(firstName, isIn("Fred", "Barney"))
                    .orderBy(orderByColumns)
            );

            assertThat(rows).hasSize(2);
            assertThat(rows.get(0))
                    .isNotNull()
                    .extracting("lastName").isNotNull()
                    .extracting("name").isEqualTo("Rubble");
            assertThat(rows.get(1))
                    .isNotNull()
                    .extracting("lastName").isNotNull()
                    .extracting("name").isEqualTo("Flintstone");
        }
    }

    @Test
    void testDelete() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);
            int rows = mapper.delete(c ->
                    c.where(occupation, isNull()));
            assertThat(rows).isEqualTo(2);
        }
    }

    // this test is in the quick start documentation
    @Test
    void testGeneralDelete() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);

            DeleteStatementProvider deleteStatement = deleteFrom(person)
                    .where(occupation, isNull())
                    .build()
                    .render(RenderingStrategies.MYBATIS3);

            int rows = mapper.delete(deleteStatement);
            assertThat(rows).isEqualTo(2);
        }
    }

    @Test
    void testDeleteAll() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);
            int rows = mapper.delete(DeleteDSLCompleter.allRows());

            assertThat(rows).isEqualTo(6);
        }
    }

    @Test
    void testDeleteByPrimaryKey() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);
            int rows = mapper.deleteByPrimaryKey(2);

            assertThat(rows).isEqualTo(1);
        }
    }

    @Test
    void testInsert() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);
            PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1);

            int rows = mapper.insert(row);
            assertThat(rows).isEqualTo(1);
        }
    }

    @Test
    void testGeneralInsert() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);
            int rows = mapper.generalInsert(c ->
                c.set(id).toValue(100)
                .set(firstName).toValue("Joe")
                .set(lastName).toValue(new LastName("Jones"))
                .set(birthDate).toValue(new Date())
                .set(employed).toValue(true)
                .set(occupation).toValue("Developer")
                .set(addressId).toValue(1)
            );

            assertThat(rows).isEqualTo(1);
        }
    }

    @Test
    void testInsertMultiple() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);

            List<PersonRecord> records = List.of(
                    new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1),
                    new PersonRecord(101, "Sarah", new LastName("Smith"), new Date(), true, "Architect", 2)
            );

            int rows = mapper.insertMultiple(records);
            assertThat(rows).isEqualTo(2);
        }
    }

    @Test
    void testInsertSelective() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);
            PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), false, null, 1);

            int rows = mapper.insertSelective(row);
            assertThat(rows).isEqualTo(1);
        }
    }

    @Test
    void testUpdateByPrimaryKeyNullKeyShouldThrowException() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);
            PersonRecord row = new PersonRecord(null, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1);

            assertThatExceptionOfType(NonRenderingWhereClauseException.class).isThrownBy(() -> mapper.updateByPrimaryKey(row));
        }
    }

    @Test
    void testUpdateByPrimaryKey() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);
            PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1);

            int rows = mapper.insert(row);
            assertThat(rows).isEqualTo(1);

            row = row.withOccupation("Programmer");
            rows = mapper.updateByPrimaryKey(row);
            assertThat(rows).isEqualTo(1);

            Optional<PersonRecord> newRecord = mapper.selectByPrimaryKey(100);
            assertThat(newRecord).hasValueSatisfying(r ->
                    assertThat(r.occupation()).isEqualTo("Programmer"));
        }
    }

    @Test
    void testUpdateByPrimaryKeySelective() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);
            PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1);

            int rows = mapper.insert(row);
            assertThat(rows).isEqualTo(1);

            PersonRecord updateRecord = new PersonRecord(100, null, null, null, null, "Programmer", null);
            rows = mapper.updateByPrimaryKeySelective(updateRecord);
            assertThat(rows).isEqualTo(1);

            Optional<PersonRecord> newRecord = mapper.selectByPrimaryKey(100);
            assertThat(newRecord).hasValueSatisfying(r -> {
                assertThat(r.occupation()).isEqualTo("Programmer");
                assertThat(r.firstName()).isEqualTo("Joe");
            });
        }
    }

    @Test
    void testUpdate() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);
            PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1);

            int rows = mapper.insert(row);
            assertThat(rows).isEqualTo(1);

            PersonRecord updateRow = row.withOccupation("Programmer");

            rows = mapper.update(c ->
                PersonMapper.updateAllColumns(updateRow, c)
                .where(id, isEqualTo(100))
                .and(firstName, isEqualTo("Joe")));

            assertThat(rows).isEqualTo(1);

            Optional<PersonRecord> newRecord = mapper.selectByPrimaryKey(100);
            assertThat(newRecord).hasValueSatisfying(r ->
                    assertThat(r.occupation()).isEqualTo("Programmer"));
        }
    }

    @Test
    void testUpdateOneField() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);
            PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1);

            int rows = mapper.insert(row);
            assertThat(rows).isEqualTo(1);

            rows = mapper.update(c ->
                c.set(occupation).equalTo("Programmer")
                .where(id, isEqualTo(100)));

            assertThat(rows).isEqualTo(1);

            Optional<PersonRecord> newRecord = mapper.selectByPrimaryKey(100);
            assertThat(newRecord).hasValueSatisfying(r ->
                    assertThat(r.occupation()).isEqualTo("Programmer"));
        }
    }

    @Test
    void testUpdateAll() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);
            PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1);

            int rows = mapper.insert(row);
            assertThat(rows).isEqualTo(1);

            PersonRecord updateRecord = new PersonRecord(null, null, null, null, null, "Programmer", null);
            rows = mapper.update(c ->
                PersonMapper.updateSelectiveColumns(updateRecord, c));

            assertThat(rows).isEqualTo(7);

            Optional<PersonRecord> newRecord = mapper.selectByPrimaryKey(100);
            assertThat(newRecord).hasValueSatisfying(r ->
                    assertThat(r.occupation()).isEqualTo("Programmer"));
        }
    }

    @Test
    void testUpdateSelective() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);
            PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1);

            int rows = mapper.insert(row);
            assertThat(rows).isEqualTo(1);

            PersonRecord updateRecord = new PersonRecord(null, null, null, null, null, "Programmer", null);
            rows = mapper.update(c ->
                PersonMapper.updateSelectiveColumns(updateRecord, c)
                .where(id, isEqualTo(100)));

            assertThat(rows).isEqualTo(1);

            Optional<PersonRecord> newRecord = mapper.selectByPrimaryKey(100);
            assertThat(newRecord).hasValueSatisfying(r ->
                    assertThat(r.occupation()).isEqualTo("Programmer"));
        }
    }

    @Test
    void testCount() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);
            long rows = mapper.count(c ->
                    c.where(occupation, isNull()));

            assertThat(rows).isEqualTo(2L);
        }
    }

    @Test
    void testCountAll() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);
            long rows = mapper.count(CountDSLCompleter.allRows());

            assertThat(rows).isEqualTo(6L);
        }
    }

    @Test
    void testCountLastName() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);
            long rows = mapper.count(lastName, CountDSLCompleter.allRows());

            assertThat(rows).isEqualTo(6L);
        }
    }

    @Test
    void testCountDistinctLastName() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);
            long rows = mapper.countDistinct(lastName, CountDSLCompleter.allRows());

            assertThat(rows).isEqualTo(2L);
        }
    }

    @Test
    void testTypeHandledLike() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);

            List<PersonRecord> rows = mapper.select(c ->
                    c.where(lastName, isLike(new LastName("Fl%")))
                    .orderBy(id));

            assertThat(rows).hasSize(3);
            assertThat(rows.get(0).firstName()).isEqualTo("Fred");
        }
    }

    @Test
    void testTypeHandledNotLike() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);

            List<PersonRecord> rows = mapper.select(c ->
                    c.where(lastName, isNotLike(new LastName("Fl%")))
                    .orderBy(id));

            assertThat(rows).hasSize(3);
            assertThat(rows.get(0).firstName()).isEqualTo("Barney");
        }
    }

    @Test
    void testJoinAllRows() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonWithAddressMapper mapper = session.getMapper(PersonWithAddressMapper.class);
            List<PersonWithAddress> records = mapper.select(
                    SelectDSLCompleter.allRowsOrderedBy(id)
            );

            assertThat(records).hasSize(6);
            assertThat(records.get(0).getId()).isEqualTo(1);
            assertThat(records.get(0).getEmployed()).isTrue();
            assertThat(records.get(0).getFirstName()).isEqualTo("Fred");
            assertThat(records.get(0).getLastName()).isEqualTo(new LastName("Flintstone"));
            assertThat(records.get(0).getOccupation()).isEqualTo("Brontosaurus Operator");
            assertThat(records.get(0).getBirthDate()).isNotNull();
            assertThat(records.get(0).getAddress().getId()).isEqualTo(1);
            assertThat(records.get(0).getAddress().getStreetAddress()).isEqualTo("123 Main Street");
            assertThat(records.get(0).getAddress().getCity()).isEqualTo("Bedrock");
            assertThat(records.get(0).getAddress().getState()).isEqualTo("IN");
            assertThat(records.get(0).getAddress().getAddressType()).isEqualTo(AddressRecord.AddressType.HOME);

            assertThat(records.get(4).getAddress().getAddressType()).isEqualTo(AddressRecord.AddressType.BUSINESS);
        }
    }

    @Test
    void testJoinOneRow() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonWithAddressMapper mapper = session.getMapper(PersonWithAddressMapper.class);
            List<PersonWithAddress> records = mapper.select(c -> c.where(id, isEqualTo(1)));

            assertThat(records).hasSize(1);
            assertThat(records.get(0).getId()).isEqualTo(1);
            assertThat(records.get(0).getEmployed()).isTrue();
            assertThat(records.get(0).getFirstName()).isEqualTo("Fred");
            assertThat(records.get(0).getLastName()).isEqualTo(new LastName("Flintstone"));
            assertThat(records.get(0).getOccupation()).isEqualTo("Brontosaurus Operator");
            assertThat(records.get(0).getBirthDate()).isNotNull();
            assertThat(records.get(0).getAddress().getId()).isEqualTo(1);
            assertThat(records.get(0).getAddress().getStreetAddress()).isEqualTo("123 Main Street");
            assertThat(records.get(0).getAddress().getCity()).isEqualTo("Bedrock");
            assertThat(records.get(0).getAddress().getState()).isEqualTo("IN");
        }
    }

    @Test
    void testJoinPrimaryKey() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonWithAddressMapper mapper = session.getMapper(PersonWithAddressMapper.class);
            Optional<PersonWithAddress> row = mapper.selectByPrimaryKey(1);

            assertThat(row).hasValueSatisfying(r -> {
                assertThat(r.getId()).isEqualTo(1);
                assertThat(r.getEmployed()).isTrue();
                assertThat(r.getFirstName()).isEqualTo("Fred");
                assertThat(r.getLastName()).isEqualTo(new LastName("Flintstone"));
                assertThat(r.getOccupation()).isEqualTo("Brontosaurus Operator");
                assertThat(r.getBirthDate()).isNotNull();
                assertThat(r.getAddress().getId()).isEqualTo(1);
                assertThat(r.getAddress().getStreetAddress()).isEqualTo("123 Main Street");
                assertThat(r.getAddress().getCity()).isEqualTo("Bedrock");
                assertThat(r.getAddress().getState()).isEqualTo("IN");
            });
        }
    }

    @Test
    void testJoinPrimaryKeyInvalidRecord() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonWithAddressMapper mapper = session.getMapper(PersonWithAddressMapper.class);
            Optional<PersonWithAddress> row = mapper.selectByPrimaryKey(55);

            assertThat(row).isEmpty();
        }
    }

    @Test
    void testJoinCount() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonWithAddressMapper mapper = session.getMapper(PersonWithAddressMapper.class);
            long count = mapper.count(c -> c.where(person.id, isEqualTo(55)));

            assertThat(count).isZero();
        }
    }

    @Test
    void testJoinCountWithSubcriteria() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonWithAddressMapper mapper = session.getMapper(PersonWithAddressMapper.class);
            long count = mapper.count(c -> c.where(person.id, isEqualTo(55), or(person.id, isEqualTo(1))));

            assertThat(count).isEqualTo(1);
        }
    }

    @Test
    void testWithEnumOrdinalTypeHandler() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            AddressMapper mapper = session.getMapper(AddressMapper.class);

            GeneralInsertStatementProvider insertStatement = insertInto(address)
                    .set(address.id).toValue(4)
                    .set(address.streetAddress).toValue("987 Elm Street")
                    .set(address.city).toValue("Mayberry")
                    .set(address.state).toValue("NC")
                    .set(address.addressType).toValue(AddressRecord.AddressType.HOME)
                    .build()
                    .render(RenderingStrategies.MYBATIS3);

            int rows = mapper.generalInsert(insertStatement);
            assertThat(rows).isEqualTo(1);

            SelectStatementProvider selectStatement = select(address.addressType)
                    .from(address)
                    .where(address.id, isEqualTo(4))
                    .build()
                    .render(RenderingStrategies.MYBATIS3);

            Optional<Integer> type = mapper.selectOptionalInteger(selectStatement);
            assertThat(type).hasValueSatisfying(i -> assertThat(i).isZero());
        }
    }

    @Test
    void testMultiSelectWithUnion() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);

            SelectStatementProvider selectStatement = multiSelect(
                    select(id.as("A_ID"), firstName, lastName, birthDate, employed, occupation, addressId)
                            .from(person)
                            .where(id, isLessThanOrEqualTo(2))
                            .orderBy(id)
                            .limit(1)
            ).union(
                    select(id.as("A_ID"), firstName, lastName, birthDate, employed, occupation, addressId)
                            .from(person)
                            .where(id, isGreaterThanOrEqualTo(4))
                            .orderBy(id.descending())
                            .limit(1)
            )
            .orderBy(sortColumn("A_ID"))
            .limit(3)
            .build()
            .render(RenderingStrategies.MYBATIS3);

            String expected =
                    "(select id as A_ID, first_name, last_name, birth_date, employed, occupation, address_id " +
                    "from Person " +
                    "where id <= #{parameters.p1,jdbcType=INTEGER} " +
                    "order by id limit #{parameters.p2}) " +
                    "union " +
                    "(select id as A_ID, first_name, last_name, birth_date, employed, occupation, address_id " +
                    "from Person " +
                    "where id >= #{parameters.p3,jdbcType=INTEGER} " +
                    "order by id DESC limit #{parameters.p4}) " +
                    "order by A_ID " +
                    "limit #{parameters.p5}";

            assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);

            List<PersonRecord> records = mapper.selectMany(selectStatement);

            assertThat(records).hasSize(2);
            assertThat(records.get(0).id()).isEqualTo(1);
            assertThat(records.get(1).id()).isEqualTo(6);
        }
    }

    @Test
    void testMultiSelectWithUnionAll() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PersonMapper mapper = session.getMapper(PersonMapper.class);

            SelectStatementProvider selectStatement = multiSelect(
                    select(id.as("A_ID"), firstName, lastName, birthDate, employed, occupation, addressId)
                            .from(person)
                            .where(id, isLessThanOrEqualTo(2))
                            .orderBy(id)
                            .limit(1)
            ).unionAll(
                    select(id.as("A_ID"), firstName, lastName, birthDate, employed, occupation, addressId)
                            .from(person)
                            .where(id, isGreaterThanOrEqualTo(4))
                            .orderBy(id.descending())
                            .limit(1)
                    ).orderBy(sortColumn("A_ID"))
                    .fetchFirst(2).rowsOnly()
                    .build()
                    .render(RenderingStrategies.MYBATIS3);

            String expected =
                    "(select id as A_ID, first_name, last_name, birth_date, employed, occupation, address_id " +
                            "from Person " +
                            "where id <= #{parameters.p1,jdbcType=INTEGER} " +
                            "order by id limit #{parameters.p2}) " +
                            "union all " +
                            "(select id as A_ID, first_name, last_name, birth_date, employed, occupation, address_id " +
                            "from Person " +
                            "where id >= #{parameters.p3,jdbcType=INTEGER} " +
                            "order by id DESC limit #{parameters.p4}) " +
                            "order by A_ID " +
                            "fetch first #{parameters.p5} rows only";

            assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);

            List<PersonRecord> records = mapper.selectMany(selectStatement);

            assertThat(records).hasSize(2);
            assertThat(records.get(0).id()).isEqualTo(1);
            assertThat(records.get(1).id()).isEqualTo(6);
        }
    }

    @Test
    void testMultiSelectPagingVariation1() {
        SelectStatementProvider selectStatement = multiSelect(
                select(id, firstName, lastName, birthDate, employed, occupation, addressId)
                        .from(person)
                        .where(id, isLessThanOrEqualTo(2))
        ).unionAll(
                select(id, firstName, lastName, birthDate, employed, occupation, addressId)
                        .from(person)
                        .where(id, isGreaterThanOrEqualTo(4))
                )
                .orderBy(id)
                .limit(3).offset(2)
                .build()
                .render(RenderingStrategies.MYBATIS3);

        String expected =
                "(select id, first_name, last_name, birth_date, employed, occupation, address_id " +
                        "from Person " +
                        "where id <= #{parameters.p1,jdbcType=INTEGER}) " +
                        "union all " +
                        "(select id, first_name, last_name, birth_date, employed, occupation, address_id " +
                        "from Person " +
                        "where id >= #{parameters.p2,jdbcType=INTEGER}) " +
                        "order by id " +
                        "limit #{parameters.p3} offset #{parameters.p4}";

        assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
    }

    @Test
    void testMultiSelectPagingVariation2() {
        SelectStatementProvider selectStatement = multiSelect(
                select(id, firstName, lastName, birthDate, employed, occupation, addressId)
                        .from(person)
                        .where(id, isLessThanOrEqualTo(2))
        ).unionAll(
                select(id, firstName, lastName, birthDate, employed, occupation, addressId)
                        .from(person)
                        .where(id, isGreaterThanOrEqualTo(4))
                )
                .orderBy(id)
                .offset(2)
                .build()
                .render(RenderingStrategies.MYBATIS3);

        String expected =
                "(select id, first_name, last_name, birth_date, employed, occupation, address_id " +
                        "from Person " +
                        "where id <= #{parameters.p1,jdbcType=INTEGER}) " +
                        "union all " +
                        "(select id, first_name, last_name, birth_date, employed, occupation, address_id " +
                        "from Person " +
                        "where id >= #{parameters.p2,jdbcType=INTEGER}) " +
                        "order by id " +
                        "offset #{parameters.p3} rows";

        assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
    }

    @Test
    void testMultiSelectPagingVariation3() {
        SelectStatementProvider selectStatement = multiSelect(
                select(id, firstName, lastName, birthDate, employed, occupation, addressId)
                        .from(person)
                        .where(id, isLessThanOrEqualTo(2))
        ).unionAll(
                select(id, firstName, lastName, birthDate, employed, occupation, addressId)
                        .from(person)
                        .where(id, isGreaterThanOrEqualTo(4))
                )
                .orderBy(id)
                .offset(2).fetchFirst(3).rowsOnly()
                .build()
                .render(RenderingStrategies.MYBATIS3);

        String expected =
                "(select id, first_name, last_name, birth_date, employed, occupation, address_id " +
                        "from Person " +
                        "where id <= #{parameters.p1,jdbcType=INTEGER}) " +
                        "union all " +
                        "(select id, first_name, last_name, birth_date, employed, occupation, address_id " +
                        "from Person " +
                        "where id >= #{parameters.p2,jdbcType=INTEGER}) " +
                        "order by id " +
                        "offset #{parameters.p3} rows fetch first #{parameters.p4} rows only";

        assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
    }

    @Test
    void testMultiSelectPagingVariation() {
        SelectStatementProvider selectStatement = multiSelect(
                select(id, firstName, lastName, birthDate, employed, occupation, addressId)
                        .from(person)
                        .where(id, isLessThanOrEqualTo(2))
        ).unionAll(
                select(id, firstName, lastName, birthDate, employed, occupation, addressId)
                        .from(person)
                        .where(id, isGreaterThanOrEqualTo(4))
                )
                .orderBy(id)
                .build()
                .render(RenderingStrategies.MYBATIS3);

        String expected =
                "(select id, first_name, last_name, birth_date, employed, occupation, address_id " +
                        "from Person " +
                        "where id <= #{parameters.p1,jdbcType=INTEGER}) " +
                        "union all " +
                        "(select id, first_name, last_name, birth_date, employed, occupation, address_id " +
                        "from Person " +
                        "where id >= #{parameters.p2,jdbcType=INTEGER}) " +
                        "order by id";

        assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
    }

    @Test
    void gh737() {
        UpdateStatementProvider updateStatement = update(person)
                .set(addressId).equalTo(add(addressId, value(4)))
                .where(id, isEqualTo(5))
                .build()
                .render(RenderingStrategies.MYBATIS3);

        String expected = "update Person " +
                "set address_id = (address_id + #{parameters.p1}) " +
                "where id = #{parameters.p2,jdbcType=INTEGER}";

        assertThat(updateStatement.getUpdateStatement()).isEqualTo(expected);
        assertThat(updateStatement.getParameters()).containsExactly(entry("p1", 4), entry("p2", 5));
    }
}