1 / 42

Using Relational Databases

Using Relational Databases. Chapter 9. Objectives. Discover what Java DataBase Connectivity means and what composes the JDBC API Learn some best practices for programming relational databases See what Java types map to SQL types or encapsulate database concepts

tansy
Download Presentation

Using Relational Databases

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. Using Relational Databases Chapter 9 Java Programming: Advanced Topics

  2. Objectives • Discover what Java DataBase Connectivity means and what composes the JDBC API • Learn some best practices for programming relational databases • See what Java types map to SQL types or encapsulate database concepts • Learn how to connect to relational databases through JDBC drivers Java Programming: Advanced Topics

  3. Objectives (Cont.) • Create and execute SQL statements using the java.sqlpackage • Perform advanced operations such as batch updates and updatable result sets • Program for transactional integrity • Consider the advantages of data sources, connection pooling, and distributed transactions as supported by the javax.sqlpackage Java Programming: Advanced Topics

  4. Best Practices for Programming for Databases • Extend the design pattern of model-view separation • Design for model-persistence separation • Define a mapping between Java objects and data elements • Use the power of the Database Manager • Design your application in terms of transactions • Use Enterprise JavaBeans Java Programming: Advanced Topics

  5. View-Model-Persistence Separation Java Programming: Advanced Topics

  6. Mapping between Java Objects and Data Elements • The schemais the design of the database—its tables, columns, and relationships between them • Entity relationships are foreign-key relationships between tables • A table can map directly onto a class or JavaBean Java Programming: Advanced Topics

  7. Database Schema and Object-Oriented Concepts Java Programming: Advanced Topics

  8. Database Schema and Object-Oriented Concepts (Cont.) Java Programming: Advanced Topics

  9. Sample Database Schema Java Programming: Advanced Topics

  10. Leverage the Power of the Database Manager • The primary key or identifier for a row must be unique • Use foreign key constraints to signal an error if a program tries to insert a row in a dependent table for which there is no row in the parent table • Set rules of referential integrity Java Programming: Advanced Topics

  11. Design Your Application in Terms of Transactions • Transaction: a logical unit of work containing all changes to persistent data during the process of completing a task (and all that must be undone if the task fails at any point during processing) • To commitis to send an instruction to the database to confirm updates and make them permanent • To roll backis to send an instruction to the database to cancel updates and revert to the previous data Java Programming: Advanced Topics

  12. Design Your Application in Terms of Transactions (Cont.) • The goal is to create components that implement transactions with the following ACID properties: • Atomic • Consistent • Isolated • Durable Java Programming: Advanced Topics

  13. JDBC Drivers for RDBM Systems • Java Database Connectivity (JDBC) driver: a set of classes that provide the bridge between a Java program running on the Java platform and an RDBM system that is usually running on a native operating system • JDBC is composed of two packages: • java.sql • javax.sql Java Programming: Advanced Topics

  14. SQL to Java Type Mapping Java Programming: Advanced Topics

  15. SQL to Java Type Mapping (Cont.) Java Programming: Advanced Topics

  16. Understanding the Database Used in This Chapter • The SKICLUB database contains three tables: Java Programming: Advanced Topics

  17. Using the java.sql API • The package java.sql contains JDBC classes • The SQL package also defines exception types: • BatchUpdateException • DataTruncation • SQLException • SQLWarning Java Programming: Advanced Topics

  18. java.sql Package Java Programming: Advanced Topics

  19. java.sql Package (Cont.) Java Programming: Advanced Topics

  20. Creating and Executing SQL Statements • Get a connection to the database by calling the DriverManager • Get a Statement object from the Connection object acquired in Step 1 • Build the SQL statement in a string • Call a method for the Statement object created in Step 2, passing the SQL command as its argument • Receive results as the return value of the method of the Statement interface • Process the results • Implicitly or explicitly close the Statement object Java Programming: Advanced Topics

  21. Establishing a Database Connection • The Connection interface defines the behavior of the context within which you issue SQL statements and receive results • The DriverManager class is a service class that manages JDBC 1 drivers • The getConnection method establishes a connection to the database at the given URL Java Programming: Advanced Topics

  22. Issuing Dynamic SQL Statements • The Statement interface encapsulates the behavior of dynamic SQL statements • Use Statement objects to execute Dynamic SQL queries and obtain results • Call the Connection.createStatement method to create a Statement object Java Programming: Advanced Topics

  23. Processing a ResultSet • Objects of type ResultSet encapsulate the data retrieved from the database • A ResultSet is a collection of rows • The ResultSet interface defines methods to get and update column values • All ResultSet objects have an associated cursor object that points to one row at a time, known as the current row Java Programming: Advanced Topics

  24. Code that Uses a Scrollable ResultSet Java Programming: Advanced Topics

  25. Using Precompiled SQL • Some or all of the literal values of the precompiled SQL statement are replaced by question marks when the statement is compiled • Use the class PreparedStatement for precompiled SQL • Call the Connection.prepareStatement method to create a PreparedStatement object Java Programming: Advanced Topics

  26. Using Stored Procedures • Use CallableStatement objects to execute stored procedures and obtain results • Call the Connection.prepareCall method to create a CallableStatement object • The argument of Connection.prepareCall is a string holding a parameterized SQL statement in which question marks represent parameters Java Programming: Advanced Topics

  27. Updating the Database Using a ResultSet • To update an existing row, follow these steps: • Make sure the cursor is positioned at the row to be changed • Call one of the updateXxx methods to update each column that you want to change • Call updateRow to write the change to the database Java Programming: Advanced Topics

  28. Coding Transactions • The Connection class controls the transactional properties of database operations • By default, connections are opened in autoCommit mode • AutoCommit means that the changes made by an INSERT, UPDATE, or DELETE statement immediately become permanent Java Programming: Advanced Topics

  29. Coding Transactions (Cont.) • To undo a database operation in autoCommit mode, use the process called one-phase commit • A two-phase commitis used in distributed transactions that involve more than one RDBM system or other enterprise information systems and is available only when you acquire connections from DataSource objects Java Programming: Advanced Topics

  30. Connection Methods for Transactions Java Programming: Advanced Topics

  31. Transaction Isolation Levels • The isolation level controls the way the database manager behaves when two or more programs try to access the same database at the same time • The isolation level is typically set when an application is bound to the database, but can be changed programmatically by calling Connection.setTransactionLevel Java Programming: Advanced Topics

  32. Transaction Levels Defined in the Connection Class Java Programming: Advanced Topics

  33. Using the javax.sql API • The javax.sql types implement an architecture in which the application components interact with application servers • Quality-of-service features include the following services: • Connection pooling • Transaction management • Security services • Activity tracing and logging error, warning, and informational messages Java Programming: Advanced Topics

  34. Interfaces Defined in the javax.sql Package Java Programming: Advanced Topics

  35. Interfaces Defined in the javax.sql Package (Cont.) Java Programming: Advanced Topics

  36. Classes Defined in the javax.sql Package Java Programming: Advanced Topics

  37. The DataSource Architecture • JNDI namespace: a registry in which objects are stored and retrieved by name • A program that uses a datasource accesses the datasource from the JNDI service provided by an application server • An object of type DataSource is a factory for creating connections and contains all the information that the factory needs to open a connection (such as the name of the database, the database user ID, and the password for that user) Java Programming: Advanced Topics

  38. Using a Datasource with WebSphere Application Server • To develop and test code that uses a datasource with WebSphere Application Server you can use Application Developer • Install the datasource on the application server and bind it to the server’s JNDI service • Create a Java application that uses the datasource • Use the datasource to get a java.sql.Connection object • Use the connection with the classes and interfaces defined in the java.sql package Java Programming: Advanced Topics

  39. Connection Pooling • A connection pool is a set of connections opened and maintained by an application server • A JDBC driver that supports connection pooling must provide a class that implements javax.sql.ConnectionPoolDataSource • When a program calls the getConnection method on a DataSource object, the server assigns one of the connections in the pool Java Programming: Advanced Topics

  40. Distributed Transactions • The transactions managed by the RDBM systems are called local transactions because they are local to one RDBM system • A distributed or global transaction is managed not by one RDBM or resource manager, but by the application server • Global transactions are transactions that conform to JTA or XA architecture and extend the commit and rollback processes to a distributed environment Java Programming: Advanced Topics

  41. Summary • The Java Database Connectivity (JDBC) classes communicate with the database through the driver • JDBC is composed of two packages: java.sql and javax.sql • When designing applications that access databases, use the model-persistence separation • Call methods of the java.sql.Statement class to run dynamic SQL, use the class PreparedStatement for precompiled SQL, and the class CallableStatement - for stored procedures Java Programming: Advanced Topics

  42. Summary (Cont.) • Update the database by updating the ResultSet object produced by calling executeQuery • The java.sql.Connection class supports transactional processing with methods setAutoCommit, commit, and rollback • The javax.sql package adds the ability to work in an enterprise environment and use the services of a J2EE-compliant application server • Local transactions are managed by RDMS, while distributed or global transactions are managed by the application server Java Programming: Advanced Topics

More Related