1 / 17

Database Programming in Java

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.

pedith
Download Presentation

Database Programming in Java

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related