java - How to write in a SqlDescriptor with a jdbc prepared statement into a tsrange column?
问题描述
I followed the excellent guide How to map Java and SQL arrays with JPA and Hibernate to map the special sql type tsrange
to hibernate. I decided to use Java and SQL descriptors and not a user type, because the jdbc sql handling should be better.
When I try to persist an entity with a column named time range and the type tsrange, I got always: ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) ERROR: column "time_range" is of type tsrange but expression is of type character varying
Hinweis: You will need to rewrite or cast the expression.
My understanding is, that I need to write a special/non standard sql type with the setObject method and the type Type.OTHER or Type.JAVA_OBJECT. What is the prefered way, to put a range sql type into a PreparedStatement?
The BasicBinder source, where I fill the jdbc prepared statement, sqlString contains "[2019-01-14 13:06:26, 2019-01-14 13:12:39]"
:
@Override
public <X> ValueBinder<X> getBinder(JavaTypeDescriptor<X> javaTypeDescriptor) {
return new BasicBinder<X>(javaTypeDescriptor, this) {
@Override
protected void doBind(PreparedStatement st, X value, int index, WrapperOptions options) throws SQLException {
String sqlString = javaTypeDescriptor.toString(value);
// I tried the following statements:
//st.setObject(index, sqlString, getSqlType());
//st.setObject(index, sqlString);
//st.setString(index, sqlString+"::tsrange");
st.setString(index, sqlString);
}
@Override
protected void doBind(CallableStatement st, X value, String name, WrapperOptions options) throws SQLException {
st.setObject(name, javaTypeDescriptor.toString(value));
}
};
}
Here are the column definition of the entity:
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Basic
@Column(nullable = false, name = "time_range", columnDefinition = "tsrange")
@Type(type="com.example.model.types.TsRange")
private PgTsRange timeRange;
解决方案
Hibernate Types 项目提供了一个
PostgreSQLRangeType
支持 PostgreSQLTSRANGE
列类型。
您需要做的第一件事是使用以下 Maven 依赖项:
<dependency>
<groupId>com.vladmihalcea</groupId>
<artifactId>hibernate-types-52</artifactId>
<version>${hibernate-types.version}</version>
</dependency>
之后,您可以像这样映射您的 PostgreSQL 范围:
@Entity(name = "Book")
@Table(name = "book")
@TypeDef(
typeClass = PostgreSQLRangeType.class,
defaultForType = Range.class
)
public class Book {
@Id
@GeneratedValue
private Long id;
@NaturalId
private String isbn;
private String title;
@Column(
name = "price_cent_range",
columnDefinition = "numrange"
)
private Range<BigDecimal> priceRange;
@Column(
name = "discount_date_range",
columnDefinition = "daterange"
)
private Range<LocalDate> discountDateRange;
//Getters and setters omitted for brevity
}
和Range
类PostgreSQLRangeType
来自 Hibernate Types 项目。
推荐阅读
- rest - 在 REST 响应中使用非标准 HTTP 状态代码是否很好?
- php - PHP MySqli 如何修复数据库中带有路径的插入文件?
- database - 如何获取neo4j中节点属性的最频繁值
- python-3.x - 无法运行 uwsgi-nginx-flask 容器
- java - 将一个字符串分成两部分,第一部分是数字部分,其余部分?
- python - 如何使用 Python 从 dbus 插件(一个 systemd 服务)启动 gnome-terminal
- kubernetes - 构建 kubernetes 配置文件
- firefox - Web 组件不适用于 Firefox 扩展
- bash - 在 bash 中循环使用多个源的参数
- javascript - 从表单向框架窗口添加文本