JodaTime 和 BeanPropertySqlParameterSource

发布于 2024-12-09 06:05:52 字数 925 浏览 3 评论 0原文

情况是这样的：

PostgreSQL 数据库表有一个字段 dateAdded ，它是 timestamp

Pojo 模型对象将此字段映射为

class MyModel{
   org.joda.time.DateTime dateAdded;

}

My Dao 实现在 Spring JDBC 模板中，它会如：

SqlParameterSource parameters = new BeanPropertySqlParameterSource(patient);
jdbcInsert.execute(parameters);

我从客户端读取模型并使用 @Model 构建对象。到目前为止一切都很好。当我执行此操作时，数据库抛出异常：
_{[Edit Erwin]：事实证明异常不是来自数据库。}

org.postgresql.util.PSQLException: Bad value for type timestamp : 2011-10-10T21:55:19.790+03:00

我不想通过实现完整的 INSERT 语句来手动进行格式化，因为涉及很多字段。

这里最好的解决方案是什么？有没有办法在所有调用上配置 DateTime 的 toString() 。我还考虑过创建一个从 DateTime 继承的类，但是......嗯......这是一个最终的。

根据 Erwin编辑

，我通过插入虚拟表来测试日期时间值 '2011-10-10T21:55:19.790+03:00' 并且它正在工作。但无法使用 JDBC。与 JDBC 驱动程序有关的东西吗？

原文

The situation is something like this:

PostgreSQL Database table has a field dateAdded which is timestamp

The Pojo Model object maps this field as

class MyModel{
   org.joda.time.DateTime dateAdded;

}

My Dao Implementation is in Spring JDBC Template and it goes as:

SqlParameterSource parameters = new BeanPropertySqlParameterSource(patient);
jdbcInsert.execute(parameters);

I read model from the client and build the object using @Model. Everything is fine so far. When I execute this, the database throws an exception saying:
_{[Edit Erwin]: Turns out the exception is not coming from the database.}

org.postgresql.util.PSQLException: Bad value for type timestamp : 2011-10-10T21:55:19.790+03:00

I don't want to do the formatting manually by implementing a full INSERT statement as there are many fields involved.

What is the best possible solution here? Is there a way to configure toString() of DateTime on all calls. I also thought about creating an inherited class from DateTime but ... mmhh.. it's a final.

Edit

Per Erwin, I tested DateTime value '2011-10-10T21:55:19.790+03:00' by inserting into a dummy table and it's working. But can't get to work with JDBC. Something related to the JDBC driver?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

看春风乍起 2024-12-16 06:05:52

这里的问题是 JdbcTemplate 使用准备好的语句，然后绑定值。有问题的字段是时间戳类型 - 因此需要将其设置为 java.sql.Date。在这种情况下，spring 正在调用通用的 setObject 方法，并将 Joda Time DateTime 实例传递给它。驱动程序不知道如何将其转换为 java.sql.Date - 因此会出现错误。

要解决此问题，您可以扩展 BeanPropertySqlParameterSource 覆盖 getValue 方法。如果对象的类型是joda.time.DateTime，则将其转换为java.util.Date对象并返回它。这应该可以解决问题。

class CustomBeanPropertySqlParameterSource extends BeanPropertySqlParameterSource {
  @Override
  Object getValue(String paramName) {
     Object result = super.getValue(paramName);
     if (result instanceof DateTime) {
        return ((DateTime) result).toDate();
     } else {
        return result;
     }
  }
}

The problem here is that JdbcTemplate is using a prepared statement and then binding the values. The field in question is a of type timestamp - so it needs to be set as a java.sql.Date. In this case spring is calling the generic setObject method passing it the Joda Time DateTime instance. The driver doesn't know how to convert this into a java.sql.Date - hence the error.

To fix this problem you can extend BeanPropertySqlParameterSource override the getValue method. If the type of object is joda.time.DateTime convert it to a java.util.Date object and return it. That should fix the issue.

class CustomBeanPropertySqlParameterSource extends BeanPropertySqlParameterSource {
  @Override
  Object getValue(String paramName) {
     Object result = super.getValue(paramName);
     if (result instanceof DateTime) {
        return ((DateTime) result).toDate();
     } else {
        return result;
     }
  }
}

回复收藏 0 原文

~没有更多了~