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!
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>