Java& Postgres枚举 - 如何让它们一起工作以进行更新？ [英] Java & Postgres enums - How do I make them work together for update?

问题描述

acttype是一个枚举，因此您不能将其作为普通字符串
插入而不将其转换为枚举 INSERT INTO dir_act（actcode，actname，acttype，national_code）VALUES（？， ？，？:: enumcs，？）

acttype is an enumcs so you can't insert it as a normal string without casting it to an enumcs INSERT INTO dir_act (actcode,actname,acttype,national_code) VALUES (?,?,?::enumcs,?)

对于更新，我尝试使用相同的类型转换，但不起作用。

Where as for updation i do tried with same typecasting as follows but it does not worked.

update dir_act set actname=?,acttype=?::enumcs,national_code=? where actcode=?

推荐答案

从JDBC的角度来看，只需要对待PostgreSQL枚举就像一个字符串。

From JDBC's point-of-view, just treat the PostgreSQL enum like a string.

引用博客在Java枚举和PostgreSQL枚举之间进行转换：

PostgreSQL允许您使用以下语法创建枚举类型：

PostgreSQL allows you to create enum types using the following syntax:

CREATE TYPE animal_type AS ENUM('DOG', 'CAT', 'SQUIRREL');

您现在可以在表格中使用'animal'作为数据类型，例如：

You can now use ‘animal’ as a datatype in your tables, for example:

create table pet (                         
    pet_id        integer         not null,
    pet_type      animal_type     not null,
    name          varchar(20)     not null
);

在Java中，你有一个相应的枚举类型：

In Java, you’d have a corresponding enum type:

public enum AnimalType {
    DOG,
    CAT,
    SQUIRREL;
}

在Java和PostgreSQL枚举之间进行转换非常简单。例如，要插入或更新枚举字段，您可以在SQL PreparedStatement中使用CAST语法：

Converting between Java and PostgreSQL enums is straightforward. For example, to insert or update an enum field you could use the CAST syntax in your SQL PreparedStatement:

INSERT INTO pet (pet_id, pet_type, name) VALUES (?, CAST(? AS animal_type), ?);

--or

INSERT INTO pet (pet_id, pet_type, name) VALUES (?, ?::animal_type, ?);

Postgres还允许您通过将其值作为字符串传递来插入/更新枚举

无论是否投射，Java方面都是相同的。您可以设置如下字段：

Whether casting or not, the Java side is the same. You would set the fields like this:

stmt.setInt(1, 1);
stmt.setString(2, AnimalType.DOG.toString());
stmt.setString(3, 'Rex');

从SELECT语句中检索枚举如下所示：

Retrieving the enum from a SELECT statement looks like this:

AnimalType.valueOf(stmt.getString("pet_type"));

考虑到枚举区分大小写，因此Postgres枚举和Java枚举之间的任何大小写不匹配必须考虑到。另请注意，PostgreSQL枚举类型是非标准SQL，因此不可移植。

Take into consideration that enums are case-sensitive, so any case mismatches between your Postgres enums and Java enums will have to be accounted for. Also note that the PostgreSQL enum type is non-standard SQL, and thus not portable.

这篇关于Java& Postgres枚举 - 如何让它们一起工作以进行更新？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！