CENG 352

1
CENG 352

• Homework 5

2
Steps

• Step 1
• Parse XML File
• Connect to DB (JDBC Java Database Connectivity)
• Create Table
• Insert Values
• Step 2
• Connect to DB (specify the table)
• Obtain the values and create XML

3
Step Zero

• Learn JAVA ?
• similar to C, C

4
XML (eXtensible Markup Language)

• XML Content Description
• See the document as a Tree

5
Example - XML

• ltDatabase hostname"144.122.230.17" port"5432"
  dbname"stajer" id"postgres" passwd""gt ltStudent
  gt ltRow_1gt ltid typeintegergt 1198521 lt/
  idgt ltname typechar(50)gt Seda
  Unal lt/namegt ltcourse typechar(20)gt 5710
  490 lt/coursegt ltgrade typechar(2)gt AA
  lt/gradegt lt/Row_1gt ltRow_2gt ltid
  typeintegergt 1190362 lt/idgt ltname
  typechar(50)gt Veli Bicer lt/namegt ltcours
  e typechar(20)gt 5710373 lt/coursegt ltgrad
  e typechar(2)gt AA lt/gradegt lt/Row_2gt lt/S
  tudentgt
• lt/Databasegt

6
Required SQL Stmts

• CREATE TABLE STUDENT(id integer, name char(50),
  course char(20), grade char(2))
• INSERT INTO STUDENT VALUES(1198521, 'Seda Unal',
  '5710490', 'AA')
• INSERT INTO STUDENT VALUES(1190362, 'Veli Bicer',
  '5710373', 'AA')

7
Tree Representation
Database
Student
Row_2
Row_1
id
name
course
grade
1198521
Seda Unal
5710490
AA
NODE
Element Node
Text Node
8
How to parse?

• Parsing means
• XMLString ? Object
• Manually (Not recommended ?)
• Java Libraries
• xml4j
• dom4j
• xerces ? recommended

9
Xerces Example

• import org.apache.xerces.parsers.DOMParser
• import org.w3c.dom.Document
• import org.w3c.dom.Element
• import org.w3c.dom.NamedNodeMap
• import org.w3c.dom.Node
• import org.w3c.dom.NodeList
• import java.util.

10
Xerces Example

• In order to get the root element of the tree
• DOMParser parsernew DOMParser()
• parser.parse(input.xml)
• Document documentparser.getDocument()
• Element elementdocument.getDocumentElement()

11
Xerces Example

• In order to traverse over the tree
• public static String getBody(Node node)
• StringBuffer resultnew StringBuffer()
• switch(node.getNodeType())
• case Node.ELEMENT_NODE
• NodeList nodelist node.getChildNodes()
• int lengthnodelist.getLength()
• result.append("lt").append(((Element)
  node).getTagName())
• NamedNodeMap attributesnode.getAttributes()
• int attrlenattributes.getLength()
• for(int i0iltattrleni)
• result.append(" ").append(attributes.item(i).getN
  odeName()).append("\'"attributes.item(i).getNode
  Value()).append("\'")
• result.append("gt")
• for(int i0iltlengthi)
• result.append(getBody(nodelist.item(i)))
• result.append("lt/").append(((Element)
  node).getTagName()).append("gt")
• break

12
JDBC - Connect

• import java.sql.
• Class.forName(postgresql.Driver)
• Connection connectionDriverManager.getConnection(
  
• jdbcpostgresql//localhost5432/stajer,
• postgres,)

13
JDBC insert data (INSERT/CREATE/UPDATE)

• Statement stmtcon.createStatement()
• stmt.executeUpdate(INSERT/CREATE ....)

14
JDBC Query (SELECT)

• ResultSet resultstmt.executeQuery(query)
• ResultSetMetaData rsmdres.getMetaData()
• int clengthrsmd.getColumnCount()
• while(res.next())
• for(int j1jltclengthj)
• String columnTypersmd.getColumnTypeName(j)
• String columnNamersmd.getColumnName(i)
• if(columnType.startsWith("varchar"))
  
• System.out.println((String) res.getString(j))
• else if(columnType.startsWith("int"))
  
• System.out.println(""res.getInt(j))
• else if(columnType.startsWith("float"))
  
• System.out.println(""res.getFloat(j))
• else if(columnType.startsWith("bool"))
  System.out.println(""res.getBoolean
  (j))
• else if(columnType.startsWith("text"))
  
• System.out.println(res.getString(j))
• else System.out.println(res.getString(j)
  )

15
Construct the XML

• Manually, that is String concatenation ?
• or Xerces API
• import org.w3c.dom.
• import org.apache.xerces.dom.DocumentImpl
• import org.apache.xerces.dom.DOMImplementationImp
  l
• import org.w3c.dom.Document
• import org.apache.xml.serialize.OutputFormat
• import org.apache.xml.serialize.Serializer
• import org.apache.xml.serialize.SerializerFactory
  
• import org.apache.xml.serialize.XMLSerializer
• import java.io.

16
Example Code

• public class DOMGenerate
• public static void main( String argv )
• try
• Document doc new DocumentImpl()
• Element root doc.createElement("pers
  on") // Create Root Element
• root.setAttribute("id","109750")
• Element item doc.createElement("name
  ") // Create element
• item.appendChild( doc.createTextNode("
  Jeff") )
• root.appendChild( item )
  // atach element to Root element
• item doc.createElement("age")
  // Create another Element
• item.appendChild( doc.createTextNode("
  28" ) )
• root.appendChild( item )
  // Attach Element to previous element
  down tree
• item doc.createElement("height")
  
• item.appendChild( doc.createTextNode("
  1.80" ) )
• root.appendChild( item )
  // Attach another Element -
  grandaugther
• doc.appendChild( root )
  // Add Root to Document
• OutputFormat format new
  OutputFormat( doc ) //Serialize DOM
• StringWriter stringOut new
  StringWriter() //Writer will be a String

17
Output

• lt?xml version"1.0" encoding"UTF-8"?gt
• ltperson id"109750"gt
• ltnamegtJefflt/namegt
• ltagegt28lt/agegt
• ltheightgt1.80lt/heightgt
• lt/persongt

18
Summary

• XERCES.JAR
• POSTGRESQL.JAR
• MYSQL.jar
• is available at
• http//www.srdc.metu.edu.tr/yildiray/ceng352
• Use API
• README.txt
• mailto yildiray_at_srdc.metu.edu.tr
• NameSurname.zip
• HW.java
• README.txt
• example xml files
• javac classpath .xercesImpl.jarpostgresql.jar
  HW.java
• java cp .xercesImpl.jarpostgresql.jar HW
  xmlFile.xml
• java cp .xercesImpl.jarpostgresql.jar HW
  jdbcURI login pass tableName