EE557: Server-Side Development

EE557: Server-Side Development Lecturer: David Molloy Room: XG19 Mondays 10am-1pm Notes: http://www.eeng.dcu.ie/~ee557 Mailing List: ee557@list.dcu.ie

EE557: Server-Side Development Database Connectivity • Majority of e-Commerce sites have a database tier • Data definition and manipulation is handled through Structured • Query Language (SQL) • Application tier handled through the Java Programming Language • Database doesn’t “speak Java” and Java isn’t SQL • -> We need some form of interface between the two tiers to allow • them to communicate • -> JDBC

EE557: Server-Side Development JDBC • JDBC (Java Database Connectivity?) was developed by Sun • Microsystems in the late 90s • JDBC based largely on Microsoft’s ODBC (Open Database Connectivity) • JDBC provides all the benefits of ODBC and adds to them, providing • more flexible APIs and the platform independence of Java • JDBC provides Java developers with an industry standard for database • independent connectivity between Java Applications and a wide range • of relational databases • JDBC uses a native API that translates Java methods to native calls

EE557: Server-Side Development JDBC • Therefore using JDBC we can do the following (and more): • Connect to a database • Execute SQL statements to query the database • Generate query results • Perform updates, inserts and deletions • Execute Stored Procedures

EE557: Server-Side Development JDBC Benefits • Developer only writes one API for access any database • No need to rewrite code for different databases • No need to know the database vendor’s specific APIs • It provides a standard API and is vendor independent • Virtually every database has some sort of JDBC driver • JDBC is part of the standard J2SE platform

EE557: Server-Side Development JDBC Architecture • Drivers written either in Java (used on any platform/applets) or • using native methods, tied to the underlying platform

EE557: Server-Side Development JDBC 2-Tier Model • JDBC driver and application located at client -> responsible for • presentation, business logic and the JDBC interface to the database • Driver receives request from application and transforms the request • to vendor-specific database calls -> passed to the database • Fat client -> heavy burden / PAD -> inefficient use of connections

EE557: Server-Side Development JDBC 3-Tier Model • PAD format - Middle tier takes care of business logic and • communicating with the data source on 3rd tier • Middle tier = Application Server, Application, JDBC driver • Advantages – as discussed before, scalable, usability, maintainance, • security, performance etc.

EE557: Server-Side Development JDBC Drivers • Databases are accessed via a specific JDBC driver that implements • the java.sql.Driver interface. There are four different driver types: Type 1: JDBC-ODBC Bridge Driver – have a JDBC front-end but actually call into an ODBC driver. ODBC is normally implemented in C, C++ or another language. Actual communication with database occurs through ODBC Type 2: Native-API Partly Java Driver – these drivers wrap a thin layer of Java around an underlying database-specific set of native code libraries. Oracle type 2 drivers based around OCI (Oracle Call Interface) libraries, originally designed for C/C++ programmers. Often better performance -> native code Type 3: Net-Protocol All-Java Driver – communicate via a generic network protocol to a piece of custom middleware. Requests are sent to the middleware component, which passes it to the specific DBMS. Middleware component can use any type of driver to perform access. Written in Java. Type 4: Native-Protocol/All-Java Driver – purely Java based, otherwise known as thin drivers. Translate database requests into a specific DBMS understandable format. Direct call on the DBMS from the client -> no intervening layers. Can run virtually unchanged on any platform.

EE557: Server-Side Development JDBC Drivers

EE557: Server-Side Development Connecting to a Database • Connecting to a database consists of the following steps: • Load the JDBC Driver:driver must be in the CLASSPATH environment • variable and/or within the application server/containers relevant libraries. • A class is loaded into the JVM for use later, when we want to open a • connection. • Class.forName(<driver class>); • Eg. • Class.forName(“oracle.jdbc.driver.OracleDriver”); • When the driver is loaded into memory, it registers itself with the • java.sql.DriverManager classes as an available database driver.

EE557: Server-Side Development Connecting to a Database • Connect to the Database:by using the getConnection() method • of the DriverManager object. Database URL contains the address • of the database residing on the network and any other info such as • sub-protocol (such as ODBC) and port number. • DriverManager.getConnection(<dburl>,username,password); • Eg. • Connection conn = null; • conn = DriverManager.getConnection(“jdbc:oracle:thin@ • 136.206.35.131:1521:DBName”, “username”, “password”); • DriverManager asks each registered driver if it recognises the URL. • If yes – driver manager uses that driver to create the Connection • object.

EE557: Server-Side Development Connecting to a Database • Perform Database Operations:desired operations can then be executed • Creating statements, executing statements, manipulating the ResultSet • object etc. Connection must remain open. Statement.executeQuery() • returns a java.sql.ResultSet object containing the data -> Enumerate • Statement stmt = con.createStatement(); • ResultSet rs = stmt.executeQuery(“SELECT SURNAME FROM CUSTOMERS”); • while (rs.next()) { • System.out.println(“Surname=“ + rs.getString(“SURNAME”)); • } • Expected output: • Surname=Corcoran • Three types of statement: Statement, Prepared Statement and Callable • Statement (executing Stored Procedures -> don’t cover)

EE557: Server-Side Development Connecting to a Database • Release Resources:Connections are costly and there are often limits • imposed by databases -> close statements, ResultSets and Connections • if (rs != null) rs.close(); • if (stmt != null) stmt.close(); • if (con != null) con.close();

EE557: Server-Side Development JDBC Example • Few things required: • Sample Database Structure – DDL, schema, security access etc. • Java Compiler – eg. JSE • JDBC Drivers – vendor-specific, required at compile and execution • Source Code – your java application • Work through program source for JDBCExample and JDBCExample2 • Note: EE557 is a shared account – general work/assignment!

EE557: Server-Side Development Prepared Statements • Second type of Statement type we can use in JDBC • PreparedStatement used for precompiling an SQL statement • Statement can subsequently by used whenever needed • Use the setXXX() methods in the PreparedStatement interface, • where XXX identifies the type of parameter • PreparedStatement pstmt = con.prepareStatement("INSERT INTO CUSTOMERS (ID,SURNAME,FIRSTNAME,EMAIL,COUNTRY,PHONE) VALUES (?,?,?,?,?,?)"); pstmt.clearParameters(); // Clears any previous parameters • pstmt.setInt(1, 3054); • pstmt.setString(2, "Darcy"); • pstmt.setString(3, "Simon"); • pstmt.setString(4, "darcys@marconi.com"); • pstmt.setString(5, "United States"); • pstmt.setString(6, "+44 213 5553343"); • pstmt.executeUpdate();

EE557: Server-Side Development Prepared Statements • For a statement, such as UPDATE, DELETE or other DDL, the method • to use is executeUpdate() • For a SELECT statement, returning a ResultSet object, you use • executeQuery() • PreparedStatements are precompiled by the database for faster • execution. Once compiled it can be customised by predefining • parameters -> same SQL statement run over and over again • Added important usefulness -> Consider: • INSERT INTO CUSTOMERS • VALUES (3843, ‘O’Riordan’, ‘Sally’,’sals@yahoo.com’,’Ireland’,’3223’) • Java example overleaf:

EE557: Server-Side Development Prepared Statements private boolean addCustomer(Integer id, String lastname, String fname, String email, String country, String ph) { //...... assuming that drivers/connection had been set up etc. Statement stmt = con.createStatement(); stmt.executeUpdate("INSERT INTO CUSTOMERS (ID,SURNAME,FIRSTNAME,EMAIL,COUNTRY,PHONE) VALUES (id,lastname,fname,email,country,ph) stmt.close(); // ...... etc... } • Instead we use the PreparedStatement, therefore -> • pstmt.setString(2, lastname); • -> Problem Solved

EE557: Server-Side Development Transaction Control • Important to ensure that a series of UPDATE or INSERT statements • (which are part of a transaction) either all suceed or all fail. • Therefore if database is transaction aware: • - can commit the results to the database or • - rollback all of our SQL statements • Transaction control is handled by our Connection object • By default Connection object is set to auto commit mode • You can explicitly control a transaction and commit or rollback: • void setAutoCommit(boolean autoCommit) throws SQLException • void commit() throws SQLException • void rollback() throws SQLException • Example on next slide

EE557: Server-Side Development Transaction Control try { con.setAutoCommit(false); stmt = con.createStatement(); stmt1 = con.createStatement(); stmt2 = con.createStatement(); stmt3 = con.createStatement(); stmt1.execute("DROP TABLE NEWTESTTABLE"); stmt.execute("CREATE TABLE NEWTESTTABLE (ID INTEGER, NAME VARCHAR(50), PRIMARY KEY(ID))"); stmt2.executeUpdate("INSERT INTO NEWTESTTABLE VALUES (25, 'John Doe')"); stmt3.executeUpdate("INSERT INTO NEWTESTTABLE VALUES (23, 'Ann Jones')"); con.commit(); } catch (SQLException e) { try { con.rollback(); } catch (Exception excp) { System.out.println("Error occurred during rollback!"); } }

EE557: Server-Side Development Transaction Control • Last snippet part of larger example – show code

EE557: Server-Side Development Servlets and Databases • Although the example are created as applications – we are • typically implementing 3/n-tier systems • Therefore, we use middle tier applications such as Servlets/JSPs/ • standard support classes to implement our JDBC • Concepts and coding remain the same • JDBCServlet.java Example

EE557: Server-Side Development Connection Pooling • 3 tier systems typically have a lot of communication with database • tier. Connections are time-consuming/heavy on resources for each • new connection, maintainance, use and release. • Reusing same Connection multiple times, dramatic performance bonus

EE557: Server-Side Development Connection Pooling • Connection Pool provides huge resource improvements. Application • servers typically provide a vendor-specific Connection Pool • Consider the example results on the next slide

EE557: Server-Side Development Connection Pooling Example • VCP Link Checker without Connection Pooling • JDBC Driver Version is: 8.1.7.1.0 • Data = molloyda • Database Query/Connection Total Time = 20 ms • Database Connection Time Only = 16 ms • VCP Link Checker with Connection Pooling • JDBC Driver Version is: 8.1.7.1.0 • Data = molloyda • Database Connection/Query Time = 4 ms • Database Connection Time Only = 0 ms • Assuming our database could only handle a maximum of 100 • possible connections. Therefore from the above: • 100 * 1000/20 = 5,000 connections per second maximum (non pooled) • or • 100 * 1000/4 = 25,000 connections per second maximum (using connection pooling)

EE557: Server-Side Development Lecturer: David Molloy Room: XG19 Mondays 10am-1pm Notes: http://www.eeng.dcu.ie/~ee557 Mailing List: ee557@list.dcu.ie

EE557: Server-Side Development JAXP – Java API for XML Processing • To use XML data in our applications -> we must parse XML documents • Parsing is dissecting a body of text into its individual components • <para>This is <emphasis>very important</emphasis> info!</para> • JAXP uses parser standards for: • - Simple API for XML Parsing (SAX) • - Document Object Model (DOM) • - XML Stylesheet Language Transformations (XSLT) • Note: Streaming API for XML (StAX) (added in JDK6, not covered) • (provides a median between tree and event based parsing) • JAXP allows you to use any XML-compliant parser (such as Xerces) • from within your application. • JAXP Specifications -> Standard • JAXP Implementations -> Number available, differing implementation • bugs and specification deviations

Name Parser Implementation XSLT Implementation Comment Apache Xalan Xerces Xalan XSLT - JAXP Reference Implementation Xerces or Crimson Xalan XSLT - Java 2 Platform, SE 1.4 Java 2 Platform, SE 1.5 Crimson Xerces 2 Xalan XSLT - Saxon Oracle parser implementation Saxon XSLT No DOM Support EE557: Server-Side Development JAXP – Java API for XML Processing • JAXP can be divided into two main parts: • - a parsing API • - a transforming API • Typically transforming implementations require a parser imp. to read input files

EE557: Server-Side Development JAXP – Parsing • JAXP API included in J2SDK, Standard Edition • The main JAXP APIs are defined in the javax.xml.parsers package • javax.xml.parsers contains two vendor-independent factory classes: • - SAXParserFactory – gives you a SAXParser (used for SAX) • - DocumentBuilderFactory – gives you a DocumentBuilder, which in turn • creates a DOM-compliant Document Object (used for DOM) SAX DOM • Provides a tree structure of objects • Powerful capabilities/Complex coding • Higher memory/CPU requirements • Entire XML structure read in first • Suitable for interactive modification of • XML • Serial, event driven mechanism • Fast, efficient • Requires little memory • Handles data when encountered • Typically more suitable for server-side • reading of data

EE557: Server-Side Development Simple API for XML (SAX) • Standard Interface for event-based XML Parsing • Identifies the elements as the parser reads them • Informs the application of events, such as the start and end of elements • SAX works very well when you simply want to read data and use it • It is less suitable for modification of XML data

EE557: Server-Side Development Simple API for XML (SAX) • Instance of SAXParserFactory -> Instance of SAXParser • SAXParser – in general you pass an XML data source and a DefaultHandler object • to the parser (we write a custom Handler class) • As we parse through the XML file the implemented methods in our Handler • implementation are called. The most common of these methods are: • - startDocument(..) • - endDocument(..) • - startElement(..) • - endElement(..) • - characters(..) • We can implement these methods to perform whatever functionality we wish to • provide in our application • public void endElement(String namespaceURI, String sName, String qName) throws SAXException { • ..... • if (eName.equals("firstname")) { • System.out.println("Firstname = " + textBuffer.toString().trim()); • textBuffer = null; • } • }

EE557: Server-Side Development personnel.xml <?xml version='1.0'?><personnel><person> <firstname>David</firstname> <lastname>Molloy</lastname></person><person> <firstname>John</firstname> <lastname>Smith</lastname></person><person> <firstname>Jane</firstname> <lastname>Grogan</lastname></person></personnel>

EE557: Server-Side Development Document Object Model (DOM) • SAX is public-domain software, developed through collaboration • DOM = World Wide Web Consortium (W3C) standard • DOM not specifically designed for Java -> designed to represent • XML documents across all programming languages and tools • such as C++ and JavaScript • DOM models an XML document -> complete model representing • every aspect of an XML object • With DOM programmers can build documents, navigate their • structure, add, modify and delete elements and content • DOM is a garden-variety tree structure made up of nodes • Two most common: element nodes & text nodes

EE557: Server-Side Development Document Object Model (DOM) • While SAX is event-driven, reporting events during parsing, • DOM supplies a complete-in-memory representation of the doc • Document is supplied to you in a tree structure, complete with • individual nodes representing elements, text, attributes etc.

EE557: Server-Side Development Document Object Model (DOM) • Tree model followed in every sense -> instead of having a • getText() method, there is a child node of type Text • -> We get the child and the value of the element from the • child node itself, rather than from the parent element • This structure preserves the strict tree structure of DOM • All DOM structures can be treated as their generic type, Node • or as their specific type, such as Element, Attr etc. • Can walk up and down the tree using methods such as • getParentNode() and getChildNodes() without worrying • about the structure since all are nodes

EE557: Server-Side Development Document Object Model (DOM) • Work through the DOM Basic Example • Steps • 1) import required classes • 2) instantiate the DocumentBuilderFactory • DocumentBuilderFactory factory = Document BuilderFactory.newInstance(); • 3) Use the factory to get an instance of a builder • DocumentBuilder builder = factory.newDocumentBuilder(); • 4) Use the builder to parse the file • document = builder.parse(new File(argv[0])); • 5) Error handling logic • catch (SAXException sxe) etc.

EE557: Server-Side Development Document Object Model (DOM) • Work through DomExample2.java • displayInformation(Document document) • - first we get the root element • - print out the root tag name and boolean hasAttributes() • - get the NodeList of childNodes of the root element • NodeList has two main methods • int NodeList.getLength() • Node NodeList.item(int i) • - print out all of the nodes (except when node just whitespace) • - we keep track of how many non-whitespace nodes • - as an example we then show how we can obtain a specific • element <firstname> and we obtain its child node, which is • the data • - could alternatively have used node.getFirstChild() • factory.setValidating(true);

EE557: Server-Side Development DOM Construction/Modification • DOM very suitable for creation and modification of data • SAX the opposite of this! • Rather than parsing, we create a new Document using • builder.newDocument() and add elements and text nodes to it • In example, we create the document in only in memory – could • easily modify the example to write the document to a file • <name><firstname>David</firstname><lastname>Molloy</lastname></name> • Work through code -> we use a reduced displayInformation • method to display the data to System.out • Can modify XML data similarly, but not done in this example • Use methods such as Element.setAttribute(String name, String value)

EE557: Server-Side Development JDOM • JDOM not an acrynom • JDOM open source, tree-based, pure Java API for parsing, creating, • manipulating XML documents • Like DOM, JDOM represents XML documents as a tree composed of • elements, attributes, text nodes etc. • JDOM written explicitly for Java users to simplify and enhance DOM • Does not have its own parser -> uses a SAX parser with a custom • ContentHandler to parse documents and build JDOM model • DOM and JDOM can both access any part of tree at any time • All parts of the tree can be moved, deleted and added to, • subject to usual XML restrictions

EE557: Server-Side Development JDOM vs DOM • JDOM designed to represent XML data to Java developers in a straightforward way • -> DOM concept optimized for Java • JDOM takes the best concepts from existing APIs and creates a new set of classes • and interfaces that provide a new interface expected by the Java programmer • DOM designed to be cross-language. JDOM takes advantage of Java abilities and • features, such as method overloading etc. • JDOM lightweight, fast, less programming effort, efficient document modification, • integrates with DOM and SAX, smaller memory footprint than DOM • <element>This is a sentence</element> • DOM Node node = element.getFirstChild(); • String content = node.getNodeValue(); • JDOM String content = element.getText(); • Much easier way of handling mixed-content model • Work through JDOMExample.java

EE557: Server-Side Development