Database Programming in Java

1. Database Programming in Java http://flic.kr/p/ar4nLn

2. What are you goingto learn about today? • MySQL database programming • Relational DB model • How to write SQL queries http://flic.kr/p/8JpkTg

3. Database (DB): Organized collection of data Database Management System (DBMS): Controls the creation, maintenance, and use of a DB http://flic.kr/p/ar4nLn

4. Why use a DBMS? • Data independence: Applications need not be concerned with how data is stored or accessed • Provides a lot of functionality that would be silly to implement yourself: • Sharing (network) • Customizable security • Integrity

5. Two key aspects of a DBMS • Database model: How DB is structured and used • Examples: Relational, Object-Oriented, Hierarchical • Query language: Types of questions you can ask • Examples: SQL, XQuery Relational + SQL is most common,so we’ll use MySQL

6. DB Tools • DB client tools • Example: MySQL Workbench • Code using DB library • Lib example: Java Database Connectivity (JDBC)

7. Relational Model Concepts http://en.wikipedia.org/wiki/File:Relational_model_concepts.png

8. Example Tables Authors AuthorISBN Publishers Titles

9. Primary versus Foreign Keys • Primary key: Uniquely identifies each record in table • Foreign key: Field in table A such that the field is a primary key in one other table B Authors AuthorISBN

10. CRUD-to-SQL Mapping For complete documentation, see: http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-data-manipulation.html

11. Tip: Don’t create/remove tables on the fly • Only perform CRUD operations on rows • Example: 1 table per book—bad! • Set up the tables before you deploy

12. Example SELECT Queries—Let’s try them! • SELECT * FROM Authors • SELECT AuthorID, LastName FROM Authors • SELECT * FROM Authors WHERE YearBorn > 1910 • SELECT * FROM Authors WHERE LastName LIKE ‘r%’ • SELECT * FROM Authors WHERE LastName LIKE ‘_e%’ • SELECT * FROM Authors WHERE LastName REGEXP ‘[a-r]*’ • SELECT * FROM Authors WHERE LastName REGEXP ‘[a-r]*’ORDER BY LastName ASC For complete documentation, seehttp://dev.mysql.com/doc/refman/5.5/en/select.html http://dev.mysql.com/doc/refman/5.5/en/pattern-matching.html

13. Use JOIN to merge data from multiple tables • SELECTFirstName, LastName, ISBNFROM Authors INNER JOIN AuthorISBN ON Authors.AuthorID = AuthorISBN.AuthorIDORDER BY LastName, FirstName • SELECT Titles.Title, Authors.LastName, Publishers.PublisherNameFROM (Publishers INNER JOIN Titles ON Publishers.PublisherID = Titles.PublisherID)INNER JOIN (Authors INNER JOIN AuthorISBN ON Authors.AuthorID = AuthorISBN.AuthorID)ON Titles.ISBN = AuthorISBN.ISBN For complete documentation, seehttp://dev.mysql.com/doc/refman/5.5/en/join.html

14. Basic steps for performing query with JDBC • Load driver class (only needs to be done once) • Connect to DB • Create/initialize statement • Execute statement (returns ResultSet) • Process ResultSet • Close connection (closes statement and ResultsSet) For more complete documentation, see: http://docs.oracle.com/javase/tutorial/jdbc/basics/

15. Let’s take a tour ofa web app that uses JDBC

16. What could go wrong with this code?(Hint: Recall last lecture) // Add 1 to each player’s Score Statement qst = con.createStatement(); rs = qst.executeQuery("SELECT * FROM Players"); while (rs.next()) { int playerID = rs.getInt("PlayerID"); int score = rs.getInt("Score"); Statement ust = con.createStatement(); ust.executeUpdate("UPDATE Players SET Score = '" + (score+1) + "' WHERE PlayerID = '" + playerID + "'"); } Check Scorecouldchange Use TOCTOU race condition! To avoid races, see transactions and/or table locks: http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-transactions.html

17. Summary • Relational DBs • CRUD to SQL mapping • JOIN operation • JDBC programming • Watch out for race conditions! http://flic.kr/p/YSY3X