Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I insert an XML document in PostgreSQL in Java?

I have a table in 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?

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

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"
like image 765
Kliver Max Avatar asked Aug 21 '12 08:08

Kliver Max


2 Answers

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:

INSERT INTO xml_docs(id, gkuzu) VALUES (1, XMLPARSE('<foo><bar>Hello</bar></foo>'));

See: http://wiki.postgresql.org/wiki/XML_Support

UPDATE:

Java code example:

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>'));
like image 160
user1606528 Avatar answered Nov 10 '22 21:11

user1606528


Instead of rewriting the insert statement using PostgreSQL-proprietary syntax, you could use JDBC 4 SQLXML:

String xml = xml_gkuzu.toString();

SQLXML sqlxml = connection.createSQLXML();
sqlxml.setString(xml);
stmt.setSQLXML(3, sqlxml);
like image 24
Aleksander Blomskøld Avatar answered Nov 10 '22 22:11

Aleksander Blomskøld