JDOM and MySQL CS 623 Data Base Management System PRESENTED BY

1. JDOM and MySQL CS 623 Data Base Management System PRESENTED BY Alina Joshi GeetuAggarwal PranayJha 12/06/07

2. Index • Introduction • Installation • Program • References

3. Introduction • MySQL is a multithreaded, multi-user SQL database management system (DBMS). • MySQL databases are available in all major programming languages with language-specific APIs. • For Java, we need MySQL Connector/J that is the official JDBC driver for MySQL. • So, then What is JDBC?

4. Java DataBase Connectivity • JDBC is a Java API for executing SQL statements. • JDBC makes it possible to : • establish a connection with a database • send SQL statements • process the results. • You need to import the java.sql package to use JDBC. • import java.sql.*;

5. Steps for JDBC • Loading drivers • Create a connection • Create a statement (create table, select…) • Execute statement, and • Process the result

6. Loading Drivers • Loading driver involves following line of code: • Class.forName (“DriverClassName”); • Forloading Driver in MySQL • Class.forName("com.mysql.jdbc.Driver");

7. Making Connection • It involves following line of code: • Connection conn = DriverManager.getConnection (url, “UserId”, “Password”); • where URL = jdbc:<subprotocol>:<subname> • For Connection in MySQL • Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql?” • + "user=root&password=geetu");

8. Creating a statement • A statement object is what sends a SQL statement to the DBMS. • A statement object is created with the following code: • Statement stmt = conn.createStatement();

9. Executing Query • Execute query • ResultSetrs = stmt.executeQuery ("select * from student"); • This sends the query to the database and returns the result of the query as a ResultSet.

10. Introduction • JDOM is an open source Java-based document object model for XML that was designed specifically for the Java platform so that it can take advantage of its language features. • JDOM is a pseudo-acronym for Java Document Object Model.

11. Package Structure • org.jdom • org.jdom.adapters • org.jdom.filter • org.jdom.input • org.jdom.output • org.jdom.transform • org.jdom.xpath

12. Description of Package Structure • The org.jdom Package • – Attribute • – CDATA • – Comment • – DocType • – Document •  The org.jdom.adapters Package • –AbstractDOMAdapter • – CrimsonDOMAdapter • –JAXPDOMAdapter • –OracleV1DOMAdapter • –XML4JDOMAdapter

13. Description of Package Structure • The org.jdom.filterPackage • – AbstractFilter • – ContentFilter • – ElementFilter •  The org.jdom.transform Package • – JDOMSource • – JDOMResult • – XSLTransformer • The org.jdom.xpath Package • – XPath

14. Description of Package Structure • The org.jdom.input Package • – SAXBuilder • – DOMBuilder • – ResultSetBuilder • The org.jdom.output Package • – XMLOutputter • – SAXOutputter • – DOMOutputter • – JTreeOutputter

15. Creating a document • Documents are represented by the org.jdom.Document  class. • Document doc  = new Document(new Element("root"));  • Creating a simple document in JDOM • Document doc  = new Document(); • Element e  = new Element("root"); • e.setText("This is the root"); • doc.addContent(e); • This builds:  <root>This is the root</root>

16. XML Outputter • A document can be output to many different formats, but the most common is a stream of bytes and in JDOM using XMLOutputter. • XMLOutputteroutp  = new XMLOutputter(); • outp.output(doc, fileStream); 

17. ResultSetBuilder • Defines a Builder that can be used to construct a DataSet from a ResultSet. This class makes for a quick and easy "import" of data from a JDBC data source such as an MySQL database. • ResultSetBuilder is an extension to JDOM created for people who need to treat a SQL result as an XMLdocument. • The ResultSetBuilder constructor accepts a java.sql.ResultSet as input and returns an org.jdom.Document from its build() method.

18. Installation • Java EE SDK 5 • Download Java EE SDK 5 from this site: • http://java.sun.com/javaee/downloads/index.jsp • Click on Download if you have JDK already in your machine otherwise click on Download with JDK. • Click on windows platform check box for Java EE SDK 5. Save the file in the drive and run the file. • In the System, an environment variable named JAVA_HOME need to be set to point Java SDK root directory.

19. Eclipse Installation • Download Eclipse IDE for Java EE Developers from this site: http://www.eclipse.org/downloads/ • Download Eclipse Classic 3.3.1.1 and save it in the drive and unzip the folder.

20. MySQL Installation • Download MySQL 5 (Community Server) from this site http://dev.mysql.com/downloads/mysql/6.0.html • Click on Pick a mirror and save the file in the drive and run it. • You need MySQL connector as well. Download MySQL Java Connector from this site: • http://dev.mysql.com/downloads/connector/j/5.0.html • Click on Pick a mirror and download the zip file in the drive and unzip it in a folder.

21. JDOM Installation • Download JDOM from site: http://www.jdom.org. • Click on the jdom-1.1.zip and save it to the disk. C:\ JDOM\jdom-1.1.zip and unzip it. • Click on the jdom-contrib-1.1.zip and save it to the disk. C:\ JDOM\jdom-1.1.zip and unzip it.

22. Creating a database • Step1: Launch MySQL using the following commandmysql –u login –p • Step2: Create a database named as mysql. • CREATE DATABASE mysql • Use the database using following command:USE mysql

23. Creating a database • Step3: Open a notepad and write command for creating a table named as student and populate it. • CREATE TABLE Student(id INTEGER,name CHAR(20) NOT NULL,address CHAR(50),primary key(id)); • Saved the files as student.sqlStep4: Now run the student.sql from the mysql mode using the following command source c:\student.sql

24. Program Flow JDOM XML Database

25. Code Step 1:The JDBC code to retrieve the id and name of the student from Mysql Database. • import java.sql.Connection; • import java.sql.DriverManager; • import java.sql.ResultSet; • import java.sql.SQLException; • public class StudentTransaction1{ • public static void main(String args[]) • throws SQLException, ClassNotFoundException{ • public static void main(String args[]) throws SQLException, ClassNotFoundException {try {//load the mysql driverClass.forName("com.mysql.jdbc.Driver");} catch (ClassNotFoundException e ){}//connect to data base

26. Code • import java.sql.Statement; • Connection conn = DriverManager.getConnection("jdbc:mysql:localhost:3306/mysql?"+ "user=root&password=geetu");conn.setAutoCommit(false);// create the statement object • Statement stmt = null;try {stmt = conn.createStatement(); • //using result to execute query ResultSetrs= stmt.executeQuery("select id ,Name from student");catch (SQLException e) {System.out//.println("SQL exception caught in your code!!!");e.printStackTrace();conn.rollback();stmt.close();conn.close();return;}conn.commit();stmt.close();conn.close();} • }

27. ResultSetBuilder STEP II : using Result set builder to get the XML output • // add the following import statements • import org.jdom.Document; • import java.io.ByteArrayOutputStream; • import org.jdom.JDOMException; • import org.jdom.output.XMLOutputter; • import org.jdom.contrib.input.*; • import org.jdom.output.*; • //Add the ResultSetBuilder to the code • ResultSetBuilder builder = new ResultSetBuilder(rs);

28. Code • builder.setAsAttribute("id");builder.setAsElement("Name", "Fname");// using builder to make document • Document doc = builder.build(); • // calls the getString function to display the XML outputSystem.out.println(getString(doc));}catch (JDOMException e){System.out.println(" JDOM Exception");}

29. Function to display the output • Static public String getString(Document document) { • // returns null if document is empty • if (document==null) { • return ""; • } • // declares the objects of XMLOutputter and ByteArrayOutputStream • XMLOutputterfmt = new XMLOutputter(Format.getPrettyFormat()); • ByteArrayOutputStream output = new ByteArrayOutputStream(); • try { • fmt.output(document ,output); • } catch (java.io.IOException ex) { • return ""; • } • return(output.toString());

30. Output • <?xml version="1.0" encoding="UTF-8"?><result><entry id="1"><Fname>geetu</Fname></entry><entry id="2"><Fname>Mona</Fname></entry><entry id="3"><Fname>Smith</Fname></entry><entry id="4"><Fname>John</Fname></entry></result>

31. Refrences • http://www.topxml.com/tutorials/main.asp?id=jdom&page=3 • http://en.wikipedia.org/wiki/JDOM • http://www.jdom.org/docs/apidocs/ • http://en.wikipedia.org/wiki/MySQL • http://www.jdom.org/downloads/docs.html • http://www.developer.com/java/data/article.php/3417381 • JDBC slides notes, ChristelleScharff , Blackboard- Pace University

32. Questions? • Thank You