1 / 16

Database Programming in Java

Learn about MySQL database programming, relational DB model, writing SQL queries, JDBC programming, and avoiding race conditions. Understand DBMS, database models, SQL queries, CRUD mapping, JOIN operations and JDBC basics.

yvonnelopez
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. 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 REGEX ‘[a-r]*’ • SELECT * FROM Authors WHERE LastName REGEX ‘[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

  12. Use JOIN to merge data from multiple tables • SELECT FirstName, 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

  13. 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/

  14. Let’s take a tour ofa web app that uses JDBC http://flic.kr/p/5dfuqL

  15. 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 PlayerID = '" + (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 http://flic.kr/p/9ksxQa

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

More Related