EE448: Server-Side Development

1. EE448: Server-Side Development Lecturer: David Molloy Time: Tuesdays 3pm-5pm Notes: http://wiki.eeng.dcu.ie/ee448 Mailing List: ee448@list.dcu.ie

2. 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

3. 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

4. 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

5. 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

6. JDBC Architecture • Drivers written either in Java (used on any platform/applets) or • using native methods, tied to the underlying platform

7. 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

8. 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.

9. 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.

10. JDBC Drivers

11. 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.

12. 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.

13. 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)

14. 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();

15. 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!

16. 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();

17. 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:

18. 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

19. 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

20. 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!"); } }

21. Transaction Control • Last snippet part of larger example – show code

22. 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

23. 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

24. 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

25. 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)