JDBI -> Reading MySql DATETIME into the java.sql.Date or similiar

Question

I have a field in a table in MySQL which is of type of DATETIME. I'm going to simply read and use it via JDBI.

So in Mysql:

> desc user;
....
birth_date      datetime 
.....

In the App layer, I have tried very different scenarios like the following:

    @SqlQuery("select birth_date from user where user_id = :userId")
    java.sql.Date fetchDateOfBirthForUser(@Bind("userId")final Long userId);

or

    @SqlQuery("select birth_date from user where user_id = :userId")
    java.util.Date fetchDateOfBirthForUser(@Bind("userId")final Long userId);

or

    @SqlQuery("select birth_date from user where user_id = :userId")
    java.sql.Timestamp fetchDateOfBirthForUser(@Bind("userId")final Long userId);

or even

    @SqlQuery("select birth_date from user where user_id = :userId")
    Long fetchDateOfBirthForUser(@Bind("userId")final Long userId);

---

UPDATE 1: I'm getting the following error, and not the null value!

    org.jdbi.v3.core.mapper.NoSuchMapperException: No mapper registered for type java.util.Date

And this was when I used java.util.Date. If I use any other type which I mentioned, I get the same error: No mapper registered for type X

Why is that?

---

UPDATE 2:

Using the mappers (Timestamp and java.sql.Date) gives me the NullPointerException.

The value in the DB, as I mentioned before is DATETIME.

Now I have the following code calling the mapper:

@SqlQuery("select birth_date from user where user_id = :userId")
@UseRowMapper(DateMatter.class)
java.sql.Date fetchDateOfBirthForUser(@Bind("userId")final Long userId);

And the DateMapper class:

public class DateMatter implements RowMapper<Date> {
  @Override
    public Date map(ResultSet rs, StatementContext ctx) throws SQLException {
        return rs.getDate("birth");
    }
}

I have used rs.getDate(), rs.getLong(), and rs.getTimestamp() method. All leads to NPE.

Now when I call the method dao.fetchDateOfBirthForUser(<user_id>); I get the NullPointerException

---

lAST UPDATE The solution was much unexpected. I was pointing to another DB from the application, and were querying another DB to verify!

Answer 1

I can confirm, that everything should work as expected, see code below, which works without a problem. You would better check the data in database itself, it is possible that the problem is related to that.

import org.jdbi.v3.core.Jdbi;
import org.jdbi.v3.core.mapper.RowMapper;
import org.jdbi.v3.core.statement.StatementContext;
import org.jdbi.v3.sqlobject.SqlObjectPlugin;
import org.jdbi.v3.sqlobject.config.RegisterRowMapper;
import org.jdbi.v3.sqlobject.customizer.Bind;
import org.jdbi.v3.sqlobject.statement.SqlQuery;
import org.jdbi.v3.sqlobject.statement.SqlScript;
import org.jdbi.v3.sqlobject.statement.SqlUpdate;
import org.junit.jupiter.api.Test;
import org.testcontainers.containers.MySQLContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

@Testcontainers
class DatetimeMysqlTest {

    @Container
    private static final MySQLContainer mysql = new MySQLContainer<>();

    interface Dao {

        @SqlScript("CREATE TABLE user (user_id integer, birth_date datetime)")
        void create();

        @SqlUpdate("INSERT INTO user (user_id, birth_date) VALUES (:userId, now())")
        void insert(@Bind("userId") int userId);

        @SqlQuery("SELECT birth_date from user WHERE user_id = :userId")
        java.sql.Timestamp sqlTimestamp(@Bind("userId") int userId);

        @SqlQuery("SELECT birth_date from user WHERE user_id = :userId")
        java.time.LocalDateTime timeLocalDate(@Bind("userId") int userId);

        @RegisterRowMapper(DateMatter.class)
        @SqlQuery("SELECT birth_date from user WHERE user_id = :userId")
        java.util.Date utilDate(@Bind("userId") int userId);

        class DateMatter implements RowMapper<Date> {
            @Override
            public java.util.Date map(ResultSet rs, StatementContext ctx) throws SQLException {
                return rs.getDate("birth_date");
            }
        }

    }

    @Test
    void testDatetime() {
        var jdbcUrl = mysql.getJdbcUrl();
        var dao = Jdbi.create(jdbcUrl, mysql.getUsername(), mysql.getPassword())
            .installPlugin(new SqlObjectPlugin())
            .onDemand(Dao.class);
        dao.create();
        dao.insert(42);

        System.out.println(dao.sqlTimestamp(42));
        System.out.println(dao.timeLocalDate(42));
        System.out.println(dao.utilDate(42));
    }
}

Output:

2021-02-25 12:34:05.0
2021-02-25T12:34:05
2021-02-25

Project dependencies

<dependencies>
    <dependency>
        <groupId>org.jdbi</groupId>
        <artifactId>jdbi3-core</artifactId>
        <version>3.8.2</version>
    </dependency>
    <dependency>
        <groupId>org.jdbi</groupId>
        <artifactId>jdbi3-sqlobject</artifactId>
        <version>3.8.2</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.23</version>
    </dependency>
    <dependency>
        <groupId>org.junit.jupiter</groupId>
        <artifactId>junit-jupiter</artifactId>
        <scope>test</scope>
        <version>5.7.0</version>
    </dependency>
    <dependency>
        <groupId>org.junit.platform</groupId>
        <artifactId>junit-platform-commons</artifactId>
        <scope>test</scope>
        <version>1.7.0</version>
    </dependency>
    <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>testcontainers</artifactId>
        <version>1.14.3</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>junit-jupiter</artifactId>
        <version>1.14.3</version>
        <scope>test</scope>
        <exclusions>
            <exclusion>
                <groupId>org.junit.jupiter</groupId>
                <artifactId>junit-jupiter-api</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
    <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>mysql</artifactId>
        <version>1.14.3</version>
        <scope>test</scope>
        <exclusions>
            <exclusion>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
</dependencies>