170 likes | 178 Views
Learn about MySQL database programming, relational database model, SQL query writing, and JDBC programming in Java. Explore DBMS functionalities and how to avoid race conditions.
E N D
Database Programming in Java http://flic.kr/p/ar4nLn
What are you goingto learn about today? • MySQL database programming • Relational DB model • How to write SQL queries http://flic.kr/p/8JpkTg
Database (DB): Organized collection of data Database Management System (DBMS): Controls the creation, maintenance, and use of a DB http://flic.kr/p/ar4nLn
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
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
DB Tools • DB client tools • Example: MySQL Workbench • Code using DB library • Lib example: Java Database Connectivity (JDBC)
Relational Model Concepts http://en.wikipedia.org/wiki/File:Relational_model_concepts.png
Example Tables Authors AuthorISBN Publishers Titles
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
CRUD-to-SQL Mapping For complete documentation, see: http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-data-manipulation.html
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
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
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
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/
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
Summary • Relational DBs • CRUD to SQL mapping • JOIN operation • JDBC programming • Watch out for race conditions! http://flic.kr/p/YSY3X