如何在 Java 中的 PostgreSQL 中插入 XML 文档？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12050945/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I insert an XML document in PostgreSQL in Java?
提问by Kliver Max
I have a table in Postgresql
我在 Postgresql 中有一张表
DROP TABLE xml_docs;
CREATE TABLE xml_docs(
id serial PRIMARY KEY,
cad_number character(50),
gkuzu_name character(50),
gkuzu xml,
rreq_name character(50),
rreq xml
)
I use JDBC for data base connection. And i want to insert whole xml document in table.
How can i make this?
我使用 JDBC 进行数据库连接。我想在表格中插入整个 xml 文档。
我怎样才能做到这一点？
UPDATE
更新
Okey. i try
好的。我试试
String sql = "INSERT INTO xml_docs(cad_number,gkuzu_name,gkuzu,rreq_name,rreq) VALUES(?,?,?,?,?)";
PreparedStatement stmt = ce.prepareStatement(sql);
stmt.setString(1, "11:33:5464563");
stmt.setString(2, xml_gkuzu.getName());
stmt.setString(3, xml_gkuzu.toString());
stmt.setString(4, xml_rreq.getName());
stmt.setString(5, xml_rreq.toString());
stmt.executeQuery();
ce.close();
se.close();
and get exeption
并获得豁免
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: column "gkuzu" is of type xml but expression is of type character varying
Подсказка: You will need to rewrite or cast the expression.
Whats wrong?
怎么了？
UPDATE 2
更新 2
When i try do this
当我尝试这样做时
String sql1 = "INSERT INTO xml_docs(cad_number,gkuzu_name,gkuzu,rreq_name,rreq) VALUES(11335464563,"+xml_gkuzu.getName()+",XMLPARSE("+xml_gkuzu.toString()+"),"+xml_rreq.getName()+",XMLPARSE("+xml_rreq.toString()+"))";
i get exeption
我得到豁免
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: syntax error at or near "bf48e000b0"
回答by user1606528
I'm not sure, but try this:
我不确定，但试试这个：
First convert your XML to a Java String. Then create an insert statement und use the XMLPARSE method of PostgreSQL to convert your value to the xml type of PostgreSQL:
首先将您的 XML 转换为 Java 字符串。然后创建一个插入语句并使用 PostgreSQL 的 XMLPARSE 方法将您的值转换为 PostgreSQL 的 xml 类型：
INSERT INTO xml_docs(id, gkuzu) VALUES (1, XMLPARSE('<foo><bar>Hello</bar></foo>'));
See: http://wiki.postgresql.org/wiki/XML_Support
请参阅：http: //wiki.postgresql.org/wiki/XML_Support
UPDATE:
更新：
Java code example:
Java代码示例：
String sql = "INSERT INTO xml_docs(id, gkuzu) VALUES (?, XMLPARSE(?))";
[...]
stmt.setString(2, "<foo>Hello World!</foo>");
This should create this statement:
这应该创建以下语句：
INSERT INTO xml_docs(id, gkuzu) VALUES (1, XMLPARSE('<foo>Hello World!</foo>'));
回答by Aleksander Blomsk?ld
Instead of rewriting the insert statement using PostgreSQL-proprietary syntax, you could use JDBC 4 SQLXML:
您可以使用JDBC 4 SQLXML，而不是使用 PostgreSQL 专有语法重写插入语句：
String xml = xml_gkuzu.toString();
SQLXML sqlxml = connection.createSQLXML();
sqlxml.setString(xml);
stmt.setSQLXML(3, sqlxml);
回答by Adam Gent
An update to the accepted answerif you do not have Postgres built with libxml support:
如果您没有使用 libxml 支持构建 Postgres，则更新已接受的答案：
Java code example:
Java代码示例：
String sql = "INSERT INTO xml_docs(id, gkuzu) VALUES (?, XML(?))";
[...]
stmt.setString(2, "<foo>Hello World!</foo>");
This should create this statement:
这应该创建以下语句：
INSERT INTO xml_docs(id, gkuzu) VALUES (1, XML('<foo>Hello World!</foo>'));
Thus for version 9.0 and greater you may want to switch XMLPARSE==> XML. Otherwise you will need special support for XMLPARSE
因此，对于 9.0 及更高版本，您可能需要切换XMLPARSE==> XML。否则，您将需要特殊支持XMLPARSE
The function-like expressions xmlparse and xmlserialize for converting to and from type xml are not repeated here. Use of most of these functions requires the installation to have been built with configure --with-libxml.
xmlparse 和 xmlserialize 用于与 xml 类型相互转换的函数式表达式 xmlparse 和 xmlserialize 此处不再赘述。使用大多数这些功能需要使用 configure --with-libxml 构建安装。
回答by RP-
Though postgres has native XML Data type, from java end, You can handle with Plain strings.
You can convert your xml document to String and insert, It should work.
尽管 postgres 具有 native XML Data type，但从 java 端开始，您可以使用普通字符串处理。
您可以将您的 xml 文档转换为字符串并插入，它应该可以工作。
UPDATE:
更新：
After looking at your error, You need to pass an additional variable to the server through driver URL.
查看您的错误后，您需要通过驱动程序 URL 将附加变量传递给服务器。
jdbc:postgresql://localhost/test?stringtype=unspecified
or
jdbc:postgresql://localhost/test?user=user&password=pass&stringtype=unspecified
The extra param stringtype=unspecifiedwill remove the type check for the input strings.
额外的参数stringtype=unspecified将删除输入字符串的类型检查。
