JDBC II

1. JDBC II IS 313 1.23.2003

2. Types • Java types ≠ SQL types • SQL types • historical • need for backwards compatibility

3. Numeric SQL types • Integers • tiny int • small int • integer • big int • Real numbers • real • float • decimal • numeric

4. More SQL types • Boolean • bit • String • char • varchar • long var char • Binary • binary • var binary • long var binary • Time • Date • Time • Timestamp

5. New SQL (99) Types • Binary Large Object • BLOB • Character Large Object • CLOB • Array • can manipulate without copying

6. Types in JDBC • Accessors to ResultSet • With column name String name = rs.getString (“Name”); • With column number int id = rs.getInt(1);

7. Data accessors

8. INSERT statement INSERT INTO {table} VALUES ( {value1, …, valuen} ); INSERT INTO Reservations VALUES ( 1212, #1/23/2003#, ‘Sosa’, ‘Sammy’, 2, 14, 2 );

9. UPDATE statement UPDATE {table} SET {column} = {value} WHERE { criteria } UPDATE Reservations SET RoomType = 4 WHERE ID = 1234;

10. DELETE statement DELETE FROM {table} WHERE { criteria } DELETE FROM Reservations WHERE ID = 9998;

11. JDBC Update queries • No ResultSet returned • Example 1 String sql = “INSERT INTO Reservations “ + “VALUES (100, #12/11/2003#, ‘L.L.’, ‘Bean’, 2, 2, 2);”; int rows = stmt.executeUpdate(sql); // always 1 • Example 2 String sql = “DELETE FROM Reservations WHERE (Date = #1/21/2003#);”; int rows = stmt.executeUpdate(sql); // how many rows deleted?

12. Assembling queries • Use String operators (+) to assemble queries String sql = “INSERT INTO Reservations “ + “VALUES (100, #12/11/2003#, “ + firstName + “, “ + lastName + “, 2, 2, 2);”; int rows = stmt.executeUpdate(sql);

13. Program Development how do I start?

14. Steps • Identify classes • Identify properties • Identify responsibilities • Identify connection / communication • Then • Top-down elaboration • Bottom-up implementation

15. Identify classes • What sorts of “things” present themselves • problem description • real-world activities

16. Example • Write a program • reads a student id # from the command line • retrieves student information from a database • prints out the student’s enrollment information

17. Database • Students table • Id • First Name • Last Name • Enrollments table • Enrollment Id • Student Id • Date • Course Number

18. Identify properties • What constitutes the “state” of something? • what distinguishes it from other instances? • what does it need to “know” in order to function?

19. Identify responsibilities • What does the object do? • Always • expose properties • Other operations?

20. Identify connection / communication • What other objects need to be connected to this one? • Examples • object A collects other objects • object A calls methods of object B • object A creates object B • object A contains object B

21. Class design • Arrive at • a set of classes • Each with • coherent properties • distinct reponsibilities • well-defined relationships

22. Implementation • Strategies • Top-down • work from the problem to the steps of its solution • Bottom-up • implement the methods of each class

23. Top-down • Write down how the problem can be solved • using the classes you have outlined • Turn this into Java • then make each line function

24. Bottom-up • For each class • write instance variable for properties • implement methods