1 / 30

COMP201 Java Programming

COMP201 Java Programming. Topic 15: Database Connectivity JDBC Reading: Chapter 4, Volume 2. Objective & Outline. Objective: Introduction: How to connect to and communicate with databases Outline: Database basics Connecting to a database with JDBC

peta
Télécharger la présentation

COMP201 Java Programming

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. COMP201 Java Programming Topic 15: Database Connectivity JDBC Reading: Chapter 4, Volume 2

  2. Objective & Outline • Objective: • Introduction: How to connect to and communicate with databases • Outline: • Database basics • Connecting to a database with JDBC • Querying and manipulating databases with JDBC

  3. Database Basics • A (relational) database: a collection of tables • Attributes: names of columns • Records or entries: rows • Example: testdb.mdb • 4 tables

  4. Database Basics • Database management systems (DBMS): allow user to manipulate data • Many DBMS available. We will use Microsoft Access, which comes with Microsoft Office.

  5. Database Basics • Task: • Find all books by “Fox, David”, list author name, title, publisher name and price • The query involves all four tables and

  6. Database Basics • The query: Select2 in testmd.db

  7. Database Basics • Task: Find all books published by McGraw-Hill • Select3 in testdb

  8. Database Basics • Update: • Modify records • Insert new records • Delete records • Create new Table • ….

  9. Database Basics • So far, interact with database manually • Usually interact with databases via application programs • The structured query language (SQL): • Standard language for accessing databases:. • Query database using SELECT statements • Select all elements in the Books table: SELECT * FROM Books • Select only some columns SELECT ISBN, Price, Title FROM Books

  10. Database Basics • Select from multiple tables • Find all books by “Fox, David”, list author name, title, publisher name and price SELECT Authors.Name, Books.Title, Publishers.Name, Books.Price FROM Books, Authors, Publishers, BooksAuthors WHERE Publishers.Publisher_Id = Books.Publisher_Id AND Books.ISBN = BooksAuthors.ISBN AND BooksAuthors.Author_Id = Authors.Author_Id AND Authors.Name= 'Fox, David' • When we select from multiple tables, a joint table is formed. The joint table consists of ALL possible combinations of rows from all the tables. • The first three conditions in the WHERE clause constrain the query to legal combinations.

  11. Database Basics • Update: • Slash prices for McGraw-Hill books by 0.5 UPDATE Books SET Price = Price -0.5 WHERE Books.Publisher_Id = ‘00791’ • A more intuitive way UPDATE Books SET Price = Price -0.5 WHERE Books.Publisher_Id = ( SELECT Publisher_Id FROM Publishers WHERE Name = 'McGraw-Hill‘)

  12. Objective & Outline • Outline: • Database basics • Connecting to a database with JDBC • Querying and manipulating databases with JDBC

  13. Java Application JDBC Driver Manager JDBC/ODBC bridge Vendor supplied JDBC drivers ODBC Database Connecting to Databases with JDBC • JDBC-to-database communication path Will discuss this path using testdb.mdb Textbook discusses this path using testdb.mdb

  14. Connecting to Databases with JDBC • JDBC -- Java Database Connectivity kit : Programs written according to the JDBC API would talk to the JDBC driver manager, which in turn, would use the drivers that were plugged into it at that moment to talk to the actual database. JDBC consists of two layers. • JDBC API, it communicates with the JDBC manager driver API, sending it the various SQL statements. • The manager should communicate with the various third-party drivers that actually connect to the database and return the information from the query or perform the action specified by the query. • Package: java.sql • DriverManager: class for making connection to databases • Connection: interface for communicating with databases once connection has been established. • Other interfaces: Statement, ResultSet, DatabaseMetaData, ResultSetMetaData, SQLException, ….

  15. Connecting to Databases with JDBC • To establish connection, DBMS must provide JDBC drivers • PointBase Mobile Edition: DBMS in java by PointBase Inc. • JDBC driver: com.pointbase.jdbc.jdbcDriver • Microsoft Access & many other DBMS • Has a C interface called ODBC (Open DataBase Connectivity) • The JDBC/ODBC bridge sun.jdbc.odbc.JdbcOdbcDriver provided by Sun • See java.sun.com/products/jdbc/driverdesc.html for DBMS with JDBC drivers

  16. Java Application JDBC Driver Manager JDBC/ODBC bridge Vendor supplied JDBC drivers ODBC Database Connecting to Databases with JDBC Next: • Register testdb.mdb with the ODBC Data SourceAdministrator on your PC. • Register JDBC driver (sun.jdbc.odbc.JdbcOdbcDriver ) with DriverManager • Making connection

  17. Connecting to Databases with JDBC • Register testdb.mdb with the ODBC Datasource Administer on your PC. Instructions: • Open Control Panel and select ODBC Data Sources [32bit] (underAdministrative Tools).You will get the window shown on the right. Click onAdd.

  18. Connecting to Databases with JDBC • Select Microsoft Access Driver and then click on Finish. You will get the window shown on the right. • Provide a name for the data source, say TestDB. • Click and Select and select testdb.mdb from file system (picture on the next page)

  19. Connecting to Databases with JDBC • Then you see the picture on the right. Simply click on OK and the registration is complete. • Thereafter the database testdb.mdb is known as TestDB to the ODBC Datasource Adminstrator. • For JDBC, URL for the database is: jdbc:odbc:TestDB

  20. Java Application JDBC Driver Manager JDBC/ODBC bridge Vendor supplied JDBC drivers ODBC Database Connecting to Databases with JDBC • Register JDBC driver (sun.jdbc.odbc.JdbcOdbcDriver ) with DriverManager • Method 1: • set system property( ~/.hotjava/properties ) System.setProperty("jdbc.drivers", "sun.jdbc.odbc.JdbcOdbcDriver"); • At initialization, DriverManager loads all driver classes referenced in jdbc.drivers • Method 2: Load driver explicitly at any time Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); A call to forName("X") causes the class named X to be initialized.

  21. Connecting to Databases with JDBC • Making connection Connection con= DriverManager.getConnection("jdbc:odbc:TestDB", "", ""); java.sql.Connection: represents a connection to a DB. • Several drivers might have been registered with the DriverManager. The DriverManager will find one that can use the protocol on the database URL. URL for database DB user name DB password

  22. Connecting to Databases with JDBC • Connection examples • ConnectMe1.java: register JDBC driver using the first method • ConnectMe.java: register JDBC driver using the second method • Note. Examples (including testdb.mdb) in this topic are packaged in the zip. See the code page

  23. Objective & Outline • Outline: • Database basics • Connecting to a database with JDBC • Querying and manipulating databases with JDBC

  24. Querying and Manipulating Databases with JDBC • getConnection returns an object of java.sql.Connection Connection con = DriverManager.getConnection("jdbc:odbc:TestDB","", ""); • To execute SQL queries and commands, we first need to get a java.sql.Statement (interface in java.sql) object Statement stmt = con.createStatement(); The object used for executing a static SQL statement and returning the results it produces. • Next: • Queries • Commands (updates)

  25. Querying and Manipulating Databases with JDBC • Form query as a string String query = "SELECT ISBN, Title, Price " + "FROM Books"; • Call the executeQuery method of the Statement interface ResultSet rs = stmt.executeQuery( query ); • Which returns an object of the java.sql.ResultSet interface. • A table of data representing a database result set, which is usually generated by executing a statement that queries the database. • Analysis of result: while (rs.next()) { for (int i = 1; i <= 3; i++) System.out.print( rs.getString(i) + " | "); System.out.println(""); } Database column number starts at 1 Select1.java

  26. Querying and Manipulating Databases with JDBC • More complex queries • Select2.java: Find all books by David Fox and print out author name, title, publisher name, and price. String query = "SELECT Authors.Name, Books.Title, Publishers.Name, Books.Price " + "FROM Books, Authors, Publishers, BooksAuthors " + "WHERE Publishers.Publisher_Id = Books.Publisher_Id " + "AND Books.ISBN = BooksAuthors.ISBN " + "AND BooksAuthors.Author_Id = Authors.Author_Id " + "AND Authors.Name= 'Fox, David' ";

  27. Querying and Manipulating Databases with JDBC • More complex queries • Select3.java: Find all books published by McGraw-Hill and print out author name, title, publisher name, and price String query = "SELECT Authors.Name, Books.Title, Publishers.Name, Books.Price " + "FROM Books, Authors, Publishers, BooksAuthors " + "WHERE Publishers.Publisher_Id = Books.Publisher_Id " + "AND Books.ISBN = BooksAuthors.ISBN " + "AND BooksAuthors.Author_Id = Authors.Author_Id " + "AND Publishers.Name= 'McGraw-Hill' ";

  28. Querying and Manipulating Databases with JDBC • Commands: modify records, insert new records, delete records, …. • Form command as a string String command = "UPDATE Books " + "SET Price = Price - 0.5" + " WHERE Books.Publisher_Id = 'McGraw-Hill'"; • Call the executeUpdate method of the Statement interface stmt.executeUpdate( command ); Update.java

More Related