CSCI 6962: Server-side Design and Programming

1. CSCI 6962: Server-side Design and Programming JDBC Database Programming

2. JDBC Definition • Java Database Connectivity (JDBC): set of classes that provide methods to • Connect to a database through a database server (using a driver) • Query database using SQL syntax, getting “list” of records that match query • Manipulate database by executing SQL commands to modify, insert, and delete records web container database server database driver JDBC database JSF page Managed bean DBMS

3. JDBC Components • Major objects involved: • Connection: represents connection to a database through a server • Statement: represents SQL statementexecuted on database via that connection • ResultSet: represents “list” of records matching a query Database server Statement objectselect * from books database ResultSet objectISBN title price ISBN title price ISBN title price

4. Connecting to the Database Server • Load the database driver • Not necessary in most recent version, but safe thing to do Syntax: Class.forName("driver class").newInstance(); • Name of driver class based on provider (see their documentation) • Derby:org.apache.derby.jdbc.ClientDriver • MySQL: com.mysql.jdbc.Driver

5. Connecting to the Database Server • Need to provide url of database • Form: jdbc:servertype:serverURL:port/databasename • Derby:jdbc:derby://localhost:1527/DBname • MySQL: jdbc:mysql://localhost:3306/Dbname

6. Connecting to the Database Server • Syntax:connectionobject = DriverManager.getConnection("databaseURL", "username", "password"); • Derby example: • Should close connection when doneconnectionobject.close();

7. Exception Handling in JDBC • Any database-related statement may throw SQLException • Your code must put in try/catch block • May also need to catch other exceptions • ClassNotFoundExceptionfor missing database driver Diagnostic message displayed Better idea: Redirect to an error page

8. Connecting to the Database Server

9. Executing Queries • Create new statement object using the connection • Execute an SQL query using that statement • Store results in a ResultSet object • Syntax:statement = connection.createStatement();statement.executeQuery(“SQL query”);

10. Reading ResultSets • Can only do simple access: • Read in field values from current record • Move to next record • Syntax to move to next record: ResultSetObject.next(); • Returns false if no next record, true otherwise • Must execute once before reading first record • Usually while loop to read until no more recordswhile(ResultSetObject.next()) {code to read in current record}

11. Reading ResultSets • Syntax to read field from current record: value = ResultSetObject.getType(fieldname); Specify type data is to be read in as varChar getStringintgetIntdouble  getDouble Specify field name used in database

12. ArrayList Data Structure • Problem with storing ResultSet results in array: • Need to know number of results to construct array • Cannot find without reading through entire ResultSet first • ArrayList<type> • Dynamic data structure (like linked list) • Type stored in list declared when constructed • Most useful methods: • add(object) adds object to the end of the list • get(i) returns the ith object in the list

13. BookBean Example • Goal: Read books from database into SelectList • Value: ISBN • Label: Title ($Price) BookBean getBooks returns ArrayList of SelectItems

14. BookBean Example Returns ArrayList of SelectItems Construct new list Execute SQL query for all books Loop through all results Get ISBN, title, and price fields and store in variables Construct SelectItem object and add to end of list Close connection and return list

15. Executing Update Statements • Syntax:Statement statement = connection.createStatement();statement.executeUpdate(“SQL statement”); • Example: Adding new books to the database:

16. Inserting Parameter Values • User often decides how database is updated • Enters parameter on form • Parameters used for update Method to insert linked values into database as new record

17. Inserting Parameter Values • Insert into SQL statement • Will need to use + to append values into SQL statement string • Note that string values must be inside ‘ ’to avoid syntax errors CSS Book 10.99 4 Creates string of form: INSERT INTO books (ISBN, title, price) VALUES (4, ‘CSS Book’, 10.99)

18. Validation and Updates • Usually need to validate update with database to avoid problems • Normal validation for legal values (positive number for price, etc.) • Don’t add item if already in database • Don’t update or remove if not in database • Won’t cause database error, but probably want to inform user • Checking whether item in database involves query • Create query fro item in question • If no results then not in database • Key idea: use statement of form if(ResultSetObject.next()) • True if at least one result • False if no matches

19. Validation and Updates • Example: Validating that no book with given ISBN exists in database before adding it • Query for book with that ISBN • Redirect back to input page if books.next()is true by returning null • Otherwise, add book and redirect to index.xhtml to display books

20. Prepared Statements • Executing a statement takes time for database server • Parses SQL statement and looks for syntax errors • Determines optimal way to execute statement • Most database statements are similar in form • Example: Adding books to database • All statements of form:"SELECT * FROM books WHERE productCode = ____“"INSERT INTO books (productCode, title, price) VALUES (_____, _____, ______)"

21. Prepared Statements • Tell database server basic form of statements in advance • Database server can do all work for that type of statement once • “Fill in blanks” for actual values when actually execute statement • Syntax: • Define PreparedStatementobject instead of Statement PreparedStatementcheck = null;PreparedStatement insert = null;

22. Prepared Statements • Define prepared statement usingconnection.prepareStatement • Place ‘?’ where actual values will be inserted check = connection.prepareStatement("SELECT * FROM books WHERE productCode = ?"); insert = connection.prepareStatement("INSERT INTO books (ISBN, title, price) VALUES (?, ?, ?)");

23. Prepared Statements • Use setType(index, value) to insert values into statement check.setInt(1, Intger.parseInt(ISBN)); insert.setInt(1, Intger.parseInt(ISBN)); insert.setString(2, title); insert.setDouble(3, Double.parseDouble(Price); Type of field (like get method in ResultSet) Which ‘?’ to insert the value into Insert ISBN into first (and only) ‘?’ in check Insert productCode, title, and price into first, second, and third ‘?’s respectively in insert

24. Prepared Statements • Execute statements as before • No parameters for SQL, since form already set

25. Synchronized Database Access • Database updates occur “simultaneously” on busy sites • Can interfere with one another • Example: Quantity update after purchase • Query for previous quantity • Subtract 1 • Update database with new quantity

26. Synchronized Database Access • Java runs separate clients as “parallel” threads which execute “simultaneously” • Processor swaps back and forth between threads • Problem if following sequence occurs: • Current quantity = 100 • Client 1 code to get current quantity executes (value = 100) • Processor swaps to client 2 thread • Client 2 code to get current quantity (value still = 100) • Client 2 code sets new quantity to 99 and stores in database • Processor swaps back to client 1 thread • Client 1 code also sets new quantity to 99 and stores in database!

27. Synchronized Database Access Problem: this code should not be interrupted! Client 1 thread Set quantity = 99 Store 99 in database Get quantity Quantity = 100 Client 2 thread Get quantity Quantity = 100 Set quantity = 99 Store 99 in database

28. Synchronized Database Access • Can declare sections of code to be synchronized • Only one thread may execute it at a time • Another thread cannot start the code until the first has finished it • Syntax: synchronized(object) { code } Only one thread at a time should be able to execute this code on this object

29. Synchronized Database Access