JDBC を使用して DB2 9.5 の GPX XML を操作する

try {
  // Load the IBM DB2 Driver for JDBC and SQLJ with DriverManager
  Class.forName("com.ibm.db2.jcc.DB2Driver");

} catch (ClassNotFoundException e) {
     e.printStackTrace();
}

try
{
    Connection con = null;
    String url ="jdbc:db2://localhost:50000/gpx";
    String userid = "db2admin";
    String passwd = "db2admin";

    con = DriverManager.getConnection(url, userid, passwd);

-- Create a database in UTF-8 codeset and territory US.
   CREATE DATABASE GPX USING CODESET UTF-8 TERRITORY US
-- Connect to the GPX database.
   CONNECT TO GPX
-- Create schema GPXADMIN.
-- Create table GPXADMIN.GPX
   create table GPXADMIN.GPX(ID int not null primary key,
			COMMENT varchar(1000) not null, DOCUMENT xml)
-- Register the XML Schema GPX.xsd in DB2's XSR as GPXADMIN.GPX
-- Complete the registered XML Schema.
-- Import data into the GPX table using the IMPORT utility.
   IMPORT FROM ./GPX_BaseSampledata.del  OF DEL XML FROM ./sampledata
			XMLVALIDATE USING XDS INSERT INTO GPXADMIN.GPX
-- Set the current schema as GPXADMIN.
-- Create procedure GPXADMIN.QUERY1 with this definition,

   CREATE PROCEDURE GPXADMIN.QUERY1(IN sym_param VARCHAR(10))
   SPECIFIC QUERY1
   DYNAMIC RESULT SETS 1
   LANGUAGE SQL
   BEGIN

   DECLARE SQLCODE INTEGER;
   DECLARE stmt_text VARCHAR (4096);
   DECLARE stmt STATEMENT;
   DECLARE cur1 CURSOR WITH RETURN FOR stmt;

   SET stmt_text='XQuery declare default element namespace
   "http://www.topografix.com/GPX/1/1" ; for $y in
   db2-fn:xmlcolumn("GPXADMIN.GPX.DOCUMENT")/gpx/wpt[sym="'||sym_param||'"]
   return <type>{$y}</type>';

   PREPARE stmt FROM stmt_text;
   OPEN cur1;

   END@

// 1. Load the DB2 JDBC Type 4 Driver with DriverManager.

   Class.forName("com.ibm.db2.jcc.DB2Driver");

// 2. Create a Connection object to establish a connection to DB2
//     using the getConnection method.

   Connection con = null;
   String url ="jdbc:db2://localhost:50000/gpx";
   String userid = "db2admin";
   String passwd = "db2admin";

   con = DriverManager.getConnection(url, userid, passwd);

// 3. Prepare a set schema statement.

   String setSchema = "SET CURRENT SCHEMA GPXADMIN";
   PreparedStatement setSchStmt = con.prepareStatement(setSchema);

// 4. Execute the prepared statement.

   setSchStmt.execute();

// 5. Prepare and execute create index statements.

   String crtXMLidx01 = "create index GPXADMIN.xmlIndex1 on GPXADMIN.GPX(DOCUMENT)
   GENERATE KEYS USING XMLPATTERN \'declare default element namespace
   \"http://www.topografix.com/GPX/1/1\" ; /gpx/wpt/sym\'  as sql varchar(40)";

   PreparedStatement crtIdxstmt01 = con.prepareStatement(crtXMLidx01);
   crtIdxstmt01.execute();

   String crtXMLidx02 = "create index GPXADMIN.xmlIndex2 on GPXADMIN.GPX(DOCUMENT)
   GENERATE KEYS USING XMLPATTERN \'declare default element namespace
   \"http://www.topografix.com/GPX/1/1\" ; /gpx/wpt/type\'  as sql varchar(40)";

   PreparedStatement crtIdxstmt02 = con.prepareStatement(crtXMLidx02);
   crtIdxstmt02.execute();

   String crtXMLidx03 = "create index GPXADMIN.xmlIndex3 on GPXADMIN.GPX(DOCUMENT)
   generate key using xmlpattern \'declare default element namespace
   \"http://www.topografix.com/GPX/1/1\" ; /gpx/wpt/@lon\'  as sql DOUBLE";

   PreparedStatement crtIdxstmt03 = con.prepareStatement(crtXMLidx03);
   crtIdxstmt03.execute();

   String crtXMLidx04 = "create index GPXADMIN.xmlIndex4 on GPXADMIN.GPX(DOCUMENT)
   generate key using xmlpattern \'declare default element namespace
   \"http://www.topografix.com/GPX/1/1\" ; /gpx/wpt/@lat\'  as sql DOUBLE";

   PreparedStatement crtIdxstmt04 = con.prepareStatement(crtXMLidx04);
   crtIdxstmt04.execute();

// 1. Load the DB2 JDBC Type 4 Driver with DriverManager.

   Class.forName("com.ibm.db2.jcc.DB2Driver");

// 2. Create a Connection object to establish a connection to DB2
// using the getConnection method.

   Connection con = null;
   String url ="jdbc:db2://localhost:50000/gpx";
   String userid = "db2admin";
   String passwd = "db2admin";

   con = DriverManager.getConnection(url, userid, passwd);

// 3. Set the current schema to GPXADMIN using the prepareStatement method.

   String setSchema = "SET CURRENT SCHEMA GPXADMIN";
   PreparedStatement setSchStmt = con.prepareStatement(setSchema);
   setSchStmt.execute();

// 4. REGISTER XML Schema in the XML Schema repository,
// using the SYSPROC.XSR_REGISTER stored procedure.

   CallableStatement cstmt = con.prepareCall(
   "CALL SYSPROC.XSR_REGISTER (?, ?, ?, ?, ?)");

   String xsrObjectName = "gpx";
   String xmlSchemaLocation = "gpx.xsd";

   File schemaDoc = new File("./schemas/gpx.xsd");
   InputStream inputStrm = new FileInputStream(schemaDoc);

   long length = schemaDoc.length();

   cstmt.setString(1,"GPXADMIN");

   cstmt.setString(2, xsrObjectName);
   cstmt.setString(3, xmlSchemaLocation);
   cstmt.setBinaryStream(4, inputStrm,(int)length);
   cstmt.setNull(5, java.sql.Types.BLOB);
   cstmt.registerOutParameter(1, java.sql.Types.VARCHAR);
   cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);
   cstmt.execute();

// 5. Complete the XML Schema using the SYSPROC.XSR_COMPLETE stored procedure.

   CallableStatement completeXSRstmt =
                  con.prepareCall("CALL SYSPROC.XSR_COMPLETE (?, ?, ?, ?)");
   completeXSRstmt.setString(1,"GPXADMIN");
   completeXSRstmt.setString(2,"GPX");
   completeXSRstmt.setNull(3,java.sql.Types.VARCHAR);
   completeXSRstmt.setInt(4,0);

   completeXSRstmt.execute();
   completeXSRstmt.close();

// 6. Verify that the XML Schema is registered and completed successfully in the XSR.

   String selFromXSR = "select * from sysibm.sysxsrobjects where XSROBJECTNAME='GPX'";

   PreparedStatement pstmt = con.prepareStatement(selFromXSR);
   pstmt.execute();

   ResultSet res = pstmt.executeQuery();

   while (res.next())
   {
      String data = res.getString("XSROBJECTNAME");
      System.out.println(data);
   }

// 7. This demonstrates how to import data into a table from a java program using the
//  SYSPROC.ADMIN_CMD stored procedure which in turn uses the DB2 import utility.

//  This is optional; following lines of code are commented in RegisterXMLSchema.java,
//  as the import command is executed from GPX_configure_environment.db2.

   String importStmt = "IMPORT FROM \"C:/sampledata/GPX_BaseSampledata.del\"
   OF DEL XML FROM \"C:/sampledata\" XMLVALIDATE USING XDS INSERT INTO GPXADMIN.GPX";

   CallableStatement impStmt = con.prepareCall(
              "call SYSPROC.ADMIN_CMD(?)");
   impStmt.setString(1,importStmt);
            impStmt.execute();

// 8. To drop an xsrobject from the XSR, this will drop GPXADMIN.GPX from the XSR;
//  you will have to register the XML Schema again if required.

   String dropXSR = "DROP XSROBJECT GPXADMIN.GPX";
   PreparedStatement dropStmt = con.prepareStatement(dropXSR);
   dropStmt.execute();

// 1. Follow the steps mentioned in any of the previous steps to load DB2
//  JDBC Type 4 Driver and establish a connection to DB2.

// 2. Create a statement object using the connection object.
      Statement stmt = con.createStatement();

// 3. Exports data from a database to one of several external file formats.
//  The user specifies the data to be exported by supplying an SQL SELECT
//  statement, or by providing hierarchical information for typed tables.
//  The data is exported to the server only.

   CallableStatement cstmt = con.prepareCall("CALL SYSPROC.ADMIN_CMD (?)");

   String exportCMD = "export to c:\\data of DEL select * from GPXADMIN.GPX";

   cstmt.setString(1,exportCMD);
   cstmt.execute();

   1. import com.ibm.db2.jcc.DB2Xml;

// 2. Load the DB2 JDBC Type 4 Driver with DriverManager.
   Class.forName("com.ibm.db2.jcc.DB2Driver");

/*
   3. Create a Connection object to establish a connection to DB2
   using the getConnection method.
*/

   Connection con = null;
   String url ="jdbc:db2://localhost:50000/gpx";
   String userid = "db2admin";
   String passwd = "db2admin";
   con = DriverManager.getConnection(url, userid,passwd);

// 4. Create a statement object.
   Statement stmt = con.createStatement();

/*
5. Prepare and execute an insert statement.
   Insert XML data from a file input as binary data
*/

   String insrtStmt = "insert into GPXADMIN.GPX values(?,?,?)";

   PreparedStatement pstmt = con.prepareStatement(insrtStmt);
   pstmt.setInt(1,5000);

   pstmt.setString(2,"Bangalore");

   File binFile = new File("Bangalore.xml");
   InputStream inBin = new FileInputStream(binFile);
   pstmt.setBinaryStream(3,inBin,(int)binFile.length());

   pstmt.execute();

// 6. Retrieve data (XML and Relational) using resultset.getXXX methods.

   String sql2 = "select * from GPXADMIN.GPX where id = ?";
   PreparedStatement pstmt2 = con.prepareStatement(sql2);

   pstmt2.setInt(1,5000);
   ResultSet rs2 = pstmt2.executeQuery();

   while (rs2.next())
   {
       com.ibm.db2.jcc.DB2Xml data = (com.ibm.db2.jcc.DB2Xml) rs2.getObject("DOCUMENT");
       System.out.println(rs2.getString("id")+" "+data.getDB2XmlString());
   }

/*
1. Follow the steps mentioned in any of the previous steps to load
    DB2 JDBC Type 4 Driver and establish a connection to DB2.
*/

// 2. Create a statement object using the connection object.
    Statement stmt = con.createStatement();

// 3. Prepare an insert statement with validation and execute it.

   //To insert XML Document into XML column with validation against a given XML Schema.

   String insrtStmt = "insert into GPXADMIN.GPX values(?,?,XMLVALIDATE(
   XMLPARSE(DOCUMENT(CAST(? as VARCHAR(10000))) strip whitespace)
   according to xmlschema id gpxadmin.gpx))";

   PreparedStatement pstmt = con.prepareStatement(insrtStmt);

/*
   Please make sure that the value passed to parameter one is different each time
   you execute this program, as there is a primary key defined on the ID column.
*/

   pstmt.setInt(1,12345);

   pstmt.setString(2,"Germany");

   // XML data is assigned to string variable insrtstr.

   String insrtStr = "
   <?xml version=\"1.0\" encoding=\"UTF-8\"?>
   <gpx xmlns=\"http://www.topografix.com/GPX/1/1\" creator=\"byHand\" version=\"1.1\"
   xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"
   xsi:schemaLocation=\"http://www.topografix.com/GPX/1/1 ..\\schemas\\gpx.xsd\\\">
	   <wpt lat=\"32.921055008\" lon=\"3.014223107\">
		   <ele>11.862281</ele>
		   <time>2006-05-16T11:49:06Z</time>
		   <name>XYZ Location</name>
		   <sym>City</sym>
	   </wpt>
   </gpx> ";

   pstmt.setString(3,insrtStr);
   pstmt.execute();

1. import com.ibm.db2.jcc.DB2Xml;

// 2. Load the DB2 JDBC Type 4 Driver with DriverManager.

   Class.forName("com.ibm.db2.jcc.DB2Driver");

/*
3. Create a Connection object to establish a connection to DB2
   using the getConnection method.
*/

   Connection con = null;
   String url ="jdbc:db2://localhost:50000/gpx";
   String userid = "db2admin";
   String passwd = "db2admin";
   con = DriverManager.getConnection(url, userid,passwd);

// 4. Prepare and execute a select statement.

   String selStmt = "SELECT ID, DOCUMENT from GPXADMIN.GPX where ID = ?";
   PreparedStatement pstmt = con.prepareStatement(selStmt);
   pstmt.setInt(1,4);

/*
5. One way to retrieve data is, to execute the above prepared statement using the
   executeQuery method, and then use the resultSet.getString(column-name) method.
*/

   ResultSet resultSet = pstmt.executeQuery();
   while(resultSet.next())
   {
       System.out.println(resultSet.getString("DOCUMENT"));
   }

/*
6. Another way would be to stream an XML value and use the
   getBinrarySteam(String columnName) method, is like this.
*/

   resultSet.next();

   InputStream inputStream = resultSet.getBinaryStream("DOCUMENT");
 
   int c;
   System.out.println(" The following data is retrieved using
                resultSet.getBinaryStream(DOCUMENT) ");

   while((c=inputStream.read())!= -1)
   {
       System.out.print((char)c);
   }

// 7. Another way to retrieve data is to use an object of type DB2Xml.

   ResultSet res = pstmt.executeQuery();

   res.next();
   DB2Xml db2xml = (DB2Xml) res.getObject("DOCUMENT");
   System.out.println(db2xml.getDB2XmlString());

// 8. You can also use a Reader object to retrieve data.

   ResultSet resultSet = pstmt.executeQuery();
   Reader reader = resultSet.getCharacterStream("DOCUMENT");
   do{
       System.out.print((char)reader.read());
   }
   while(reader.read()!=-1);

// 1. Load the DB2 JDBC Type 4 Driver with DriverManager.

   Class.forName("com.ibm.db2.jcc.DB2Driver");

/*
2. Create a Connection object to establish a connection to DB2
   using the getConnection method.
*/

   Connection con = null;
   String url ="jdbc:db2://localhost:50000/gpx";
   String userid = "db2admin";
   String passwd = "db2admin";

   con = DriverManager.getConnection(url, userid, passwd);

// 3. Prepare and execute an XQuery statement.

   String xQuery = "xquery declare default element namespace
   \"http://www.topografix.com/GPX/1/1\" ; for $y in db2-fn:xmlcolumn" +
   "('GPXADMIN.GPX.DOCUMENT')/gpx/wpt[sym=\"Dam\"] " +
   "return <type> { $y } </type>";

   PreparedStatement selectStmt = con.prepareStatement(xQuery);
   ResultSet rs = selectStmt.executeQuery();

   // Print the results of the XQuery using the result set object.
   while(rs.next() )
   {
       System.out.println(rs.getString(1));
   }

// 1. Load the DB2 JDBC Type 4 Driver with DriverManager.

   Class.forName("com.ibm.db2.jcc.DB2Driver");

/*
2. Create a Connection object to establish a connection to DB2
   using the getConnection() method.
*/

   Connection con = null;
   String url ="jdbc:db2://localhost:50000/gpx";
   String userid = "db2admin";
   String passwd = "db2admin";

   con = DriverManager.getConnection(url, userid, passwd);

/*
3. This is to demonstrate how to call a stored procedure.
   Stored procedure GPXADMIN.QUERY1 is registered in DB2 from the CLP using a
   script file GPX_configure_environment.db2.
*/

   CallableStatement clstmt = con.prepareCall("call GPXADMIN.QUERY1(?)");
   clstmt.setString(1, "Dam");
   clstmt.execute();

// 4. To retrieve results returned by the procedure GPXADMIN.QUERY1

   ResultSet rs = clstmt.getResultSet();

   // Print the results of the XQuery using the result set object.
   while(rs.next())
   {
 	   System.out.println(rs.getString(1));
   }

   // 1. Follow the steps mentioned in any of the previous steps to load DB2
   JDBC Type 4 Driver and establish a connection to DB2.

   // 2. Set the current schema to GPXADMIN.

   /*
      3. Insert an XML Document into an XML column from an external file.
   */

    String insrtStmt = "insert into GPXADMIN.GPX values(?,?,?)";
    PreparedStatement pstmt2 = con.prepareStatement(insrtStmt);
    pstmt2.setInt(1,21);
    pstmt2.setString(2,"CalaSantVicenc_insupddel");
    File binFile = new File("./sampledata/CalaSantVicenc.xml");
    InputStream inBin = new FileInputStream(binFile);
    pstmt2.setBinaryStream(3,inBin,(int)binFile.length());
    pstmt2.execute();
    inBin.close();

   /*
      6. INSERT an element in an XML document using the transform expression
         in an XQuery statement.
   */

    String insEleStmt = "update GPXADMIN.GPX
    SET DOCUMENT = XMLQUERY('transform copy $i := $DOCUMENT modify ( do insert
    <climate> Hot </climate> after $i/*:gpx/*:wpt/*:ele) return $i') where id=21";
    System.out.println("\nXQuery to INSERT an element into a XML document :\n");
    System.out.println(insEleStmt);
    stmt.executeUpdate(insEleStmt);
    System.out.println("\n<climate> element inserted successfully\n");
    ResultSet rsInsEle = stmt.executeQuery
    ("SELECT DOCUMENT FROM GPXADMIN.GPX where ID=21");
    while (rsInsEle.next()) {
    com.ibm.db2.jcc.DB2Xml data=(com.ibm.db2.jcc.DB2Xml) rsInsEle.getObject(1);
    System.out.println(data.getDB2XmlString());
    }


   /*
      7. UPDATE the value of an existing element in an XML document using the
         transform expression in an XQuery statement.
   */

    String replaceStmt = "UPDATE GPXADMIN.GPX SET DOCUMENT =
    XMLQUERY('transform copy $j := $DOCUMENT modify (
    do replace value of $j/*:gpx/*:wpt/*:sym/text() with \"Town\")
    return $j') where id = 21";
    System.out.println("\nXQuery to UPDATE an element's value from XML document :\n");
    System.out.println(replaceStmt);
    stmt.executeUpdate(replaceStmt);
    System.out.println("\nValue of <sym> element replaced successfully\n");
    ResultSet rsRplc = stmt.executeQuery
    ("SELECT DOCUMENT FROM GPXADMIN.GPX  where ID=21");
    while (rsRplc.next()) {
    com.ibm.db2.jcc.DB2Xml data=(com.ibm.db2.jcc.DB2Xml) rsRplc.getObject(1);
    System.out.println(data.getDB2XmlString());
    }


   /*
      8. DELETE an element from an XML document using the
         transform expression in an X statement.
   */

    String delStmt2 = "UPDATE GPXADMIN.GPX SET DOCUMENT =
    XMLQUERY('transform copy $k := $DOCUMENT modify
    (do delete $k/*:gpx/*:wpt/*:ele)
    return $k') where id = 21";
    System.out.println("\nXQuery to DELETE an element from XML document :\n");
    System.out.println(delStmt2);
    stmt.executeUpdate(delStmt2);
    ResultSet rs = stmt.executeQuery
    ("SELECT DOCUMENT FROM GPXADMIN.GPX where ID=21");
    System.out.println("\nElement <ele> deleted successfully\n");
    while (rs.next()) {
    com.ibm.db2.jcc.DB2Xml data=(com.ibm.db2.jcc.DB2Xml) rs.getObject(1);
    System.out.println(data.getDB2XmlString());
    }

//   1. Follow steps mentioned in any of the above listings to load DB2 JDBC Type 4
//   Driver and establish a connection to DB2.

//   2. Create table to store XML data and related XSL stylesheet data
//   in two different XML columns.
   create table GPXADMIN.TRNSFRM(ID INT, XMLDOC XML, XSLDOC XML)

/*
   3. Prepare and execute an insert statement to insert an GPX XML document and an XSL
   stylesheet, pass the contents of the XML document and the XSL stylesheet as string
   parameters to the con.prepareStatement method.
*/

// 4. Execute the select statement that uses the XSLTRANSFORM scalar function.
String sql2 =
"SELECT XSLTRANSFORM (xmlDOC USING xslDOC AS CLOB (10M)) FROM GPXADMIN.TRNSFRM";
PreparedStatement pstmt2 = con.prepareStatement(sql2);
ResultSet rs2 = pstmt2.executeQuery();
while (rs2.next())
{System.out.println(rs2.getString(1));}
System.out.println("XSLTRANSFORM Function executed successfully");

// 5. Use ResultSet object to retrieve result of the XSLTRANSFORMATION scalar function.

<html xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdt="http://www.w3.org/2005/xpath-datatypes"
xmlns:n1="http://www.topografix.com/GPX/1/1"
xmlns:fn="http://www.w3.org/2005/xpath-functions">
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title></title>
</head>
<body><br>
<h1><span style="font-weight:bold; ">GPX - GPS Data</span></h1>
<h2><span>Waypoints:</span></h2><br><br><br>
<h3><span style="font-style:italic; font-weight:bold; ">
Cala Sant Vicen&aring;×&shy; Mallorca</span></h3>
<span>Latitude&nbsp;&nbsp; : </span>
<span>39.921055008</span><br>
<span>Longitude: </span>
<span>3.054223107</span><br><br><br>
<span>&nbsp;</span><br>
</body>
</html>