Java II

1. Java II

2. Chapter 18 JDBC

3. Background: databases

4. Background: databases • Types of databases --Hierarchical --Relational --Object Relational

5. Background: databases • Types of databases --Hierarchical-the first database, invented by IBM, called IMS.

6. Background: databases • Types of databases --Relational-the most common, invented by IBM but first marketed by Oracle. Examples: Oracle DB2 Sybase Access* * Toy Database

7. Background: databases • Types of databases --Object Relational-uncommon, attempts to place objects in the database.

8. Relational Databases

9. Relational Databases: Entity Integrity • Unit of the Table, smallest unit in a relational database • For a table to be useful, it must enforce Entity Integrity. Entity Integrity—each row in a table can be located by using its Primary Key. 1st Law of Relational Databases Each row in a table must have an attribute(s) that uniquely locates one row. Values in this attribute must be unique. 2nd Law of Relational Databases The primary key attribute(s) cannot contain a null value

10. Relational Databases: Entity Integrity • Here is a sample table, USER Primary Key—must uniquely identify a row. No nulls allowed. • ID LastName FirstName Age • Jones Sam 32 • Jones Angela 27 • Smith Ann 22 • Doe Jack 44

11. Relational Databases: Entity Integrity • Here is another sample table, CODE Primary Key—must uniquely identify a row. No nulls. Code Message A Bill Paid B Bill Overdue C Account written off D Account closed

12. Relational Databases: Referential Integrity • A “foreign key” is when the primary key of one table is repeated in a second table as a non primary key. • Using foreign keys, or “referential integrity” allows us to link tables. 3rd Law of Relational Databases If you link two tables with a foreign key, any values present in the foreign-key attribute column must link back to existing primary-key values. 4th Law of Relational Databases It is okay for a foreign key column to contain nulls.

13. Relational Databases: Referential Integrity This is the primary key for another table. This column can contain nulls. However, any values present must exist in the table that is referred to. This is a “foreign key”. • ID LastName FirstName Age • Jones Sam 32 • Jones Angela 27 • Smith Ann 22 • Doe Jack 44 • ID LastName FirstName Age Code • Jones Sam 32 A • Jones Angela 27 B • Smith Ann 22 A • Doe Jack 44

14. SQL Basics

15. SQL Basics: Structure of a SQL Statement • When accessing a relational database, you must use the “Structured Query Language” (SQL) • Several types of SQL: queries—for asking questions updates—for making changes insert—for adding new data DDL—for creating tables

16. SQL Basics: Structure of a SQL Statement • Queries: SELECT statements SELECT columns FROM table; Or if we wish not to select all columns: SELECT columns FROM table WHERE expression

17. SQL Basics: Structure of a SQL Statement • Queries: SELECT statements SELECT FirstName, LastName FROM USER WHERE ID = 2;

18. SQL Basics: Structure of a SQL Statement • Updates: UPDATE statements UPDATE table SET column = value; Example: UPDATE table SET LastName = ‘Jones’ WHERE ID = 2;

19. SQL Basics: Structure of a SQL Statement • Insert: INSERT statements INSERT INTO table VALUES(values); Example: INSERT INTO USER VALUES( ‘6’, ‘Anderson’, ‘Joe’, 44, ‘A’)

20. JDBC Basics: Connection

21. JDBC Basics: Connection • The first step toward connecting to a database is getting a database connection. • Before you can get a connection, you need a database driver. • The driver makes the connection between a particular database and our Java program. • These drivers are individual to each vendor’s database.

22. JDBC Basics: Connection • To make sure your driver is available, you use the following: Class.forName( “sun.jdbc.odbc.JdbcOdbcDriver” ); • The above statement will merely ensure that the Java class containing the driver is available to our program.

23. JDBC Basics: Connection • The statement below results in a connection to the database. import java.sql.Connection; … Connection con = DriverManager.getConnection( url, user, pass ); • To create a connection using this method, it is necessary to pass three arguments to the method: username password url

24. JDBC Basics: url • The Url is a special string of characters that finds the database. • Here is a sample Url: url = jdbc:oracle:thin:@myhostname:1521:OurDB jdbc:oracle:thin:@ —This is database specific myhostname —This is the name of the host where the database is located. 1521 —This is the port on the host where the database is listening. OurDB —This is the name of the database.

25. JDBC Basics: Connection • The DriverManager is convenient but not scalable. import java.sql.Connection; … Connection con = DriverManager.getConnection( url, user, pass ); • Once you have opened a connection to the database, you must realize this is a resource. • You must close the connection you opened.

26. JDBC Basics: Statements • After you have a connection, you need to create a statement. • There are three alternatives, each with plusses and minuses. Statement—used for a query that will be executed once. PreparedStatement—used for a query that will be executed multiple times CallableStatement—used for a query that executes a stored procedure.

27. JDBC Basics: Statement • The Statement object is the easiest to work with. • The Statement object is the least efficient. String query = “SELECT * FROM MYTABLE WHERE ID = 2”; Connection con = DriverManager.getConnection( url, user, pass ); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( query );

28. JDBC Basics: PreparedStatement • The PreparedStatement object requires more work. • The PreparedStatement object is the most efficient. • The query contains a question mark that is replaced. String query = “SELECT * FROM MYTABLE WHERE ID = ?”; Connection con = DriverManager.getConnection( url, user, pass ); PreparedStatement pstmt = con.prepareStatement( query); pstmt.setString( 1, 494 ); ResultSet rs = pstmt.executeQuery(); This line substitutes 494 for the first question mark in the query.

29. JDBC Basics: CallableStatement • The CallableStatement object is only appropriate for calling a stored procedure. • The syntax of how you call the stored procedure is database specific. String call = “{ call myProcdure }”; Connection con = DriverManager.getConnection( url, user, pass ); CallableStatement cstmt = con.prepareCall( call ); ResultSet rs = cstmt.executeQuery();

30. JDBC Basics: ResultSet • The ResultSet object receives the results of the query. String query = “SELECT COL1, COL2 FROM MYTABLE WHERE ID = 2”; Connection con = DriverManager.getConnection( url, user, pass ); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( query ); while( rs.next() ) { String myCol1 = rs.getString( “COL1” ); String myCol2 = rs.getString( “COL2” ); } next() returns true while there are results These correspond to columns in the original query.

31. JDBC Basics: ResultSet • No matter which kind of statement you choose, the ResultSet object is used the same way. • As with the Connection object, you must close your ResultSet!

32. try { String output = null; String query = “SELECT username from MYTABLE where pass=‘foo’ ”; Connection con = DriverManager.getConnection( url, us, pass); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( query ); while( rs.next() ) { output = rs.getString( “username” ); } rs.close(); stmt.close(); con.close(); } catch( SQLException sql ) { System.out.println( “Uh oh…” ); } You must close these three items, in the reverse order that you opened them!

33. DataSource

34. DataSource • As I said, the DriverManager is not the best choice for a production system. It doesn’t scale well. • A better alternative is using a DataSource. • A DataSource offers connection pooling, where new connections are not thrown away but are instead set aside for the next time someone needs a connection.

35. DataSource: Need to Lookup in JNDI • To use a DataSource, it is necessary to perform a lookup of the resource in something called JNDI [ JNDI = Java Naming and Directory Interface ] • JNDI stores a list of names that associate with resources

36. DataSource: Need to Lookup in JNDI • First we need to create an InitialContext so we can lookup that DataSource Context ctx = new InitialContext(); String dbJNDI = "java:comp/env/OracleJDBC"; DataSource ds = (DataSource) ctx.lookup( dbJNDI ); Connection con = ds.getConnection(); This is the name I assigned to the DataSource when I created it. Here, I’m just looking it up under the name I stored it

37. DataSource:Complexity of setup • Using a DataSource is very valuable because it allows connection pooling. • The downside of using a DataSource is the complexity of its setup. Also, each Application Server vendor has its own unique setup. You will need to learn these*. * Please refer to “Setting up a DataSource in WebSphere Application Server 5.1”