CS 160: Software Engineering October 1 Class Meeting

1. CS 160: Software EngineeringOctober 1 Class Meeting Department of Computer ScienceSan Jose State UniversityFall 2014Instructor: Ron Mak www.cs.sjsu.edu/~mak

2. JDBC • Use theJDBC (Java Database Connectivity) API in the java.sqlpackage to make your Java program communicate with a database. • Requires the use of a databasedriver. • Download Connector/J fromhttp://dev.mysql.com/downloads/connector/j/ • Jar file: mysql-connector-java-5.1.33-bin.jar

3. JDBC Connection • Make a connection to the database using a URL, username, and password. • import java.sql.*;...private static String DB_URL ="jdbc:mysql://localhost:3306/school";private static String USERNAME = "root";private static String PASSWORD = "sesame";...Connection conn = DriverManager.getConnection(DB_URL, USERNAME, PASSWORD);

4. JDBC Query • Create a statement and then generate a result set by executing a query. • String QUERY = "SELECT * FROM teacher";Statement stmt = conn.createStatement();ResultSetrs = stmt.executeQuery(QUERY);

5. Iterate over a JDBC Result Set Teacher int id;String lastName;String firstName;while (rs.next()) { id = rs.getInt("id");lastName = rs.getString("last");firstName = rs.getString("first"); ...} Instead of the database field names, you can use 1, 2, 3, ...

6. Object-Relational Mapping • Create Java objects from relational database tables. • public class Teacher{int id; String lastName; String firstName;}...while (rs.next()) { Teacher teacher = new Teacher(rs.getInt("id"),rs.getString("last"),rs.getString("first")); ...} The Java Persistence Architecture (JPA) and open-source tools such as Hibernate do object-relational mapping between a Java program and a relational database.

7. SQL Query Example • Who are John Lane’s students? Student_Class SELECT student.first, student.last, subject FROM student, teacher, class, student_class WHERE teacher.last = 'Lane' AND teacher.first = 'John' AND teacher_id = teacher.id AND code = class_code AND student.id = student_id ORDER BY subject, student.last +-------+-------+----------------------+ | first | last | subject | +-------+-------+----------------------+ | Tim | Novak | Operating systems | | Kim | Smith | Operating systems | | John | Doe | Software engineering | +-------+-------+----------------------+ Teacher Class Student

8. JDBC Prepared Statement • A query statement in a loop is inefficient, because the database server has to reparse the statement and build an execution plan each time, even if the statement doesn’t change. • Use a prepared statement instead. String query = "SELECT student.first, student.last, subject " + "FROM student, teacher, class, student_class " + "WHERE teacher.last = ? AND teacher.first = ? " + "AND teacher_id = teacher.id " + "AND code = class_code AND student.id = student_id " + "ORDER BY subject, student.last"; PreparedStatementps = conn.prepareStatement(query); • Note the two • ?parameters.

9. JDBC Prepared Statement, cont’d • You can do repeated queries on different teachers by using a prepared statement and parameter substitution. for (Teacher teacher : teachers) { String lastName = teacher.getLastName(); String firstName = teacher.getFirstName(); ps.setString(1, lastName); ps.setString(2, firstName); ResultSetrs = ps.executeQuery(); while (rs.next()) { ... } } Count the ?’s from 1, not 0.

10. JDBC Result Set Metadata • Each result set has metadata that containsuseful information about the query. • number of columns • column labels • etc. • ResultSetrs = ps.getResultSet();ResultSetMetaDatarsmd = rs.getMetaData();...intcolCount = rsmd.getColumnCount();String label1 = rsmd.getColumnLabel(1);String label2 = rsmd.getColumnLabel(2);

11. Database Record Insert, Update, and Delete • There are SQL statements to insert, update, and delete records. • See the Servlet/JSP book. • INSERT INTO teacher (id, last, first)VALUES (7088, 'Mak', 'Ron'), (7090, 'Wilson', 'Brian') UPDATE teacherSET first = 'Ronald'WHERE first = 'Ron'DELETE FROM teacherWHERE id = 7090 This can update multiple records!

12. executeUpdate() • JDBC API: Use the executeUpdate() method of a statement or prepared statement object to modify the database (insert, update, or delete). • See the Servlet/JSP book. • The return value is the number of records that were affected._

13. Closing JDBC Objects • When you’re done with them, don’t forget to close your JDBC statement, prepared statement, and result set objects, and especially the database connection object. • Note that most JDBC API calls throw an exception if an error occurred, generally SQLException, which you’ll need to catch. • stmt.close();ps.close();rs.close();conn.close(); A database server can support only a limited number of connections.

14. JavaBeans • Use object-relational mapping to create JavaBeans. • A JavaBean is an object instantiated from a class that: • Has no public fields • Has a default (no-argument) constructor • Has public getters and setter methods for its private fields • Optionally implements java.io.Serializable_

15. JavaBeans, cont’d • Use JavaBeans to represent: • Model objects (in the MVC sense) of your application. • Examples: student, teacher, class, etc. • Query results • Not all model objects need to be JavaBeans. • Not all model objects need to be persisted._

16. JavaBeans, cont’d • The Teacher class as a JavaBean: public class Teacher implements java.io.Serializable { privateint id; private String lastName; private String firstName; public Teacher() { this(0, "", ""); } public Teacher(int id, String lastName, String firstName) { this.id = id; this.lastName = lastName; this.firstName = firstName; } public intgetId() { return id; } ... public void setId(int id) { this.id = id; } ...}

17. JavaBeans, cont’d • JSP pages have special features for JavaBeans. • Example: JSP Expression Language (EL) with a teacherobject. <table> <tr> <td>Id</td> <td>${teacher.id}</td> </tr> <tr> <td>First name</td> <td>${teacher.firstName}</td> </tr> <tr> <td>Last name</td> <td>${teacher.lastName}</td> </tr> </table> EL will automatically generate the appropriate getter method calls.

18. Database Connection Pool • Opening and closing a database connection are slow operations. • A running web application may have many simultaneous servlet threads each making data requests, and each request needs a connection. • Solution: Create a pool of open connections. • When a data request needs a connection, it gets an open connection from the pool. • When the request is done, it returns the open connection to the pool for another request to use. • The pool can grow and shrink based on usage.

19. Database Connection Pool (cont’d) • Tomcat provides tomcat-dbcp.jar that contains an implementation of a database connection pool. • Edit your application’s context.xml file to set parameters for the connection pool. • See the Servlet/JSP book. OPEN CONNECTIONS Servlet threads Database Connection pool

20. Data Access Layer • Databases and SQL are extremely powerful. • Let MySQL do what it’s good at doing, and let Java do what it’s good at doing. • For example, don’t write Java code to sort the retrieved records – let the database do that!_

21. Data Access Layer, cont’d • Add a data access layer to your server-side architecture. • The data access layer contains all the JDBC API calls and manages the database connection pool. • Keep the rest of your application loosely-coupled from the database code. • With object-relational mapping, the rest of your application deals only with objects, not result sets._

22. SERVER SIDE Presentation LayerView Objects (JSPs) Application LayerController Objects (Servlets) Data Access LayerFetch and Store Model Objects (JavaBeans) Multilayered Server-Side Architecture Database

23. Your Initial End-to-End Thread • Make the first development goal of your web application be the initial end-to-end round-trip thread of execution. • This thread doesn’t have to do much. • Example: Fetch and display some data from the database based on a user selection. • The user makes a selection on a web page  server code  database access to fetch and create a JavaBean  server code  display JavaBean data on a web page_

24. Your Initial End-to-End Thread, cont’d • Demonstrate that your architectural framework is sound. • Validate that all the framework components work together. • From then on: Always build on code that’s already working._