import java.sql.*;
import java.io.*;
import java.util.*;
import org.xml.sax.*;
import javax.xml.parsers.*;
import org.apache.xerces.dom.DOMImplementationImpl;
import org.w3c.dom.*;
import org.apache.xml.serialize.*;
public class updatexml
...{
/**//** private members **/
private static Connection conn;
private static PreparedStatement sStmt;
private static PreparedStatement uStmt;
private static ResultSet rs;
/**//*needed for properties file*/
static Properties db2ConnProps = new Properties();
static Properties fileinputProps = new Properties();
/**//* Default Constructor */
public updatexml() ...{}
/**//* Main Driver*/
public static void main(String[] args)
...{
String db,pID, userName, passwd, host, port, pInfo, input, newDoc, xpath, fileName;
String inputfile = " ";
boolean inputfilevalidate=false;
String rollbackCommit="rollback";
String interactive = "";
boolean exit, update;
boolean validate=false;
exit=update=false;
int option;
BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
String [] productInfo = ...{"name", "details", "price", "weight" };
String [] productValues = new String[4];
newDoc=pInfo=host=port=db=pID=userName=passwd=input=null;
/**//** print user options **/
try
...{
/**//* load the contents of properties file in case of missing arguments*/
db2ConnProps.load(new FileInputStream
("F:\\eclipse\\WorkPlace\\DB2XML\\src\\db2Conn.properties"));
db=db2ConnProps.getProperty("databaseName");
userName=db2ConnProps.getProperty("userName");
passwd=db2ConnProps.getProperty("password");
host=db2ConnProps.getProperty("hostName");
port=db2ConnProps.getProperty("portNumber");
rollbackCommit=db2ConnProps.getProperty("rollbackCommit");
interactive=db2ConnProps.getProperty("interactive");
/**//** establish DB connection **/
conn=db2Conn.get(db,userName,passwd,host,port);
try
...{conn.setAutoCommit(false);}
catch (java.sql.SQLException e)
...{
e.printStackTrace();
System.exit(-1);
}
/**//* prints current connection status*/
System.out.println();
System.out.println(" This sample updates xml information from a table.");
System.out.println();
System.out.println(" Connect to '"+db+"' database using JDBC Universal type 4 driver");
System.out.println(" Connection: com.ibm.db2.jcc");
try
...{ db2ConnProps.load(new FileInputStream
("F:\\eclipse\\WorkPlace\\DB2XML\\src\\db2Conn.properties"));}
catch(IOException io) ...{System.out.println(io.getMessage());}
catch(NullPointerException ne) ...{System.out.println("NullPointerException");}
/**//** get product id if not interactive, else request user input**/
if (interactive.equals("no"))
...{
pID = db2ConnProps.getProperty("pID");
System.out.println();
System.out.println(" Using default product ID of "+pID);
}
else
...{
pID=db2ConnProps.getProperty("pID");
System.out.println();
System.out.print(" Enter Product ID #[default is '1000']:");
pID = br.readLine();
}
/**//*If user just presses enter, then use default value*/
if(pID.length()==0)
...{
System.out.println(" Nothing entered, thus, using default value.");
pID=db2ConnProps.getProperty("pID");
}
else;
/**//** prompt for valid product ID **/
while((pInfo=getProduct(pID))==null)
...{
System.out.println();
System.out.println(" No Such Product ID can be found. Do you want to use default");
System.out.print(" value of "+db2ConnProps.getProperty("pID")+" [y/n] or q to quit: ");
input=br.readLine();
/**//** if update, parse and update new product **/
if(input.equals("y") || input.equals("Y"))
...{
pID=db2ConnProps.getProperty("pID");
System.out.println();
System.out.println(" Now using pid="+pID);
}
else if(input.equals("q") || input.equals("Q"))
...{
System.out.println(" Quitting program! ");
System.exit(-1);
}
else
...{
System.out.println();
System.out.print(" Please re-enter Product #: ");
pID = br.readLine();
}
}//end while
/**//** insert product info into a DOM and use DOM API to extract element values **/
domUtility.initializeFromString(pInfo);
/**//** Print the product description currently **/
System.out.println();
System.out.println(" Current contents of DESCRIPTION in the PRODUCTS table");
System.out.println(" for pid='"+pID+"'");
System.out.println();
System.out.println(domUtility.toString("/product"));
/**//** print product update options **/
printProduct(productInfo);
if (interactive.equals("no"))
...{ System.out.println(" Just changing #[4] weight");
input = "4";
}
else
...{ input = ""; }
while(!(input.equals("0") | input.equals("1") | input.equals("2") |
input.equals("3") | input.equals("4")))
...{
System.out.println();
System.out.print(" Enter # of the Item to change (or zero to quit): ");
input =br.readLine();
}
option= Integer.parseInt(input);
/**//** update product value or exit **/
while(!(option == 0))
...{
System.out.println();
switch (option)
...{
case 0: exit=true;
break ;
case 1: System.out.print(" Enter new Value for \"name\": ");
input=br.readLine();
domUtility.setValue("/product/description/name/text()",input);
break;
case 2: System.out.print(" Enter new Value for \"details\": ");
input=br.readLine();
domUtility.setValue("/product/description/details/text()",input);
break;
case 3: System.out.print(" Enter new Value for \"price\": ");
input=br.readLine();
domUtility.setValue("/product/description/price/text()",input);
break;
case 4:
if (interactive.equals("no"))
...{
input="5";
System.out.println();
System.out.println(" Using default Product Weight of 5.");
System.out.println();
option = 0;
}
else
...{ System.out.print(" Enter new Value for \"weight\": ");
input=br.readLine();
}
domUtility.setValue("/product/description/weight/text()",input);
break;
default: System.out.print(" Invalid Entry, please try again");
break;
}//end switch
/**//** select next item to update **/
if (interactive.equals("no"))
...{ input = "0"; }
else
...{ input = "";
printProduct(productInfo);
}
while(!(input.equals("0") | input.equals("1") | input.equals("2") |
input.equals("3") | input.equals("4")))
...{
System.out.print(" Enter # of the Item to change (or zero to quit): ");
input =br.readLine();
}
option= Integer.parseInt(input);
}//end while(!(option == 0))
System.out.println();
System.out.println(" Updating the record in the Products table using the above data");
System.out.println(" updateProd(domUtility.toString(\"/product\"),pID)");
System.out.println();
updateProd(domUtility.toString("/product"),pID);
/**//** Print the product description currently--before rollback **/
pInfo=getProduct(pID);
domUtility.initializeFromString(pInfo);
System.out.println(" After update, contents of DESCRIPTION in the PRODUCTS table");
System.out.println(" for pid='"+pID+"'");
System.out.println();
System.out.println(domUtility.toString("/product"));
//rollback and then print the contents
if (rollbackCommit.equals("rollback"))
...{
System.out.println(" Rollback the transaction.");
try
...{ conn.rollback(); }
catch (Exception e)
...{ JdbcException jdbcExc = new JdbcException(e, conn);
jdbcExc.handle(); }
}
else ;
/**//** Print the product description currently--After rollback **/
pInfo=getProduct(pID);
domUtility.initializeFromString(pInfo);
System.out.println();
System.out.println(" After rollback, contents of DESCRIPTION in the PRODUCTS table");
System.out.println(" for pid='"+pID+"'");
System.out.println();
System.out.println(domUtility.toString("/product"));
/**//** close connections **/
try ...{conn.commit();}
catch(SQLException ex)
...{
System.err.println("SQLException information");
while(ex!=null)
...{
System.err.println ("Error msg: " + ex.getMessage());
System.err.println ("SQLSTATE: " + ex.getSQLState());
System.err.println ("Error code: " + ex.getErrorCode());
ex.printStackTrace();
ex = ex.getNextException(); // For drivers that support chained exceptions
}
}
System.out.println(" Disconnect from the '"+db+"' database");
closeConn();
System.out.println(" Disconnected.");
}
catch(NullPointerException ne)...{System.out.println("NullPointerException main");}
catch(IOException io)...{io.printStackTrace();}
}//main
public static void setConn(Connection c)
...{
conn=c;
}
/**//* Get product from database */
public static String getProduct(String pID)
...{
String pInfo=null;
try
...{
/**//** Prepare Statement **/
sStmt= conn.prepareStatement("Select INFO from CUSTOMER where cid=?");
sStmt.setString(1,pID);
rs=sStmt.executeQuery();
if (rs.next())
...{
pInfo=rs.getString(1);
}
else;
}
catch(SQLException sqle)
...{
System.out.println("Error Msg: "+ sqle.getMessage());
System.out.println("SQLState: "+sqle.getSQLState());
System.out.println("SQLError: "+sqle.getErrorCode());
System.out.println("Rollback the transaction and quit the program");
System.out.println();
try ...{ conn.rollback(); }
catch (Exception e)
...{
JdbcException jdbcExc = new JdbcException(e, conn);
jdbcExc.handle();
}
System.out.println();
System.exit(1);
}
return pInfo;
}//end getProduct
/**//* Update Product */
public static void updateProd(String newDoc, String pID)
...{
int recordUpdated=0;
try
...{
/**//** Prepare Statement **/
uStmt= conn.prepareStatement("Update CUSTOMER set INFO = ? where PID=?");
uStmt.setString(1,newDoc);
uStmt.setString(2,pID);
if(uStmt.executeUpdate()!=1)
System.out.println("Product could not be updated");
else ;
}
catch(SQLException sqle)
...{
System.out.println("Error Msg: "+ sqle.getMessage());
System.out.println("SQLState: "+sqle.getSQLState());
System.out.println("SQLError: "+sqle.getErrorCode());
System.out.println("Rollback the transaction and quit the program");
System.out.println();
try ...{ conn.rollback(); }
catch (Exception e)
...{
JdbcException jdbcExc = new JdbcException(e, conn);
jdbcExc.handle();
}
System.exit(1);
}
}//end updateProd
/**//* Print Product Update Values */
public static void printProduct(String [] pInfo)
...{
String pValue, xpath;
pValue = xpath = null;
System.out.println();
System.out.println(" Product ID="+domUtility.getValue("");
for(int i=0; i < pInfo.length; i++)
...{
xpath="/Client/description/"+pInfo[i]+"/text()";
pValue = domUtility.getValue(xpath);
if(pValue != null)
System.out.println(" ["+(i+1)+"] "+pInfo[i].toString()+" : "+ pValue);
}
}//end printProduct
/**//* Close conections */
public static void closeConn()
...{
try
...{
conn.close();
sStmt.close();
uStmt.close();
}
catch(SQLException sqle)
...{
System.out.println(sqle.getMessage());
System.out.println(sqle.getSQLState());
System.out.println(sqle.getErrorCode());
}
}//end closeConn
}