Database Processing u The GUI for the application was by coronanlime


									Database Processing
u   The GUI for the application app371 was
    built using JBuilder4 and then connected to
    an Access database
    – the database connection was defined manually
    – if JBuilder4 Professional were used, the
      database connection would be created using a

                      Databases 2
Databases 3
Databases 4
Databases 5
             Database Access
u   Before the program can be run, the database
    must be registered with the ODBC driver
    (which is located in the Windows control

                     Databases 6
             Database Access

u   Click “Add” to register a new database
                      Databases 7
Databases 8
Logical name:           Physical location:

          Databases 9
             Database Access

u   The database is now registered
                     Databases 10
              Database Connection
u   The initial connection to the database
    consists of the following statements:
    static Connection c1;
    static Statement st1;

    String url = "jdbc:odbc:DB371";
    c1 = DriverManager.getConnection(url, "userid", "userpassword");
    st1 = c.createStatement();
                                Databases 11
                SQL Instructions
u   Simple SQL statements are used to modify
    a database:

    Insert into Students Values(500, 'James Bond', 'MI5')

    Update Students Set Name='Jesse James', Address='Dodge
    City' Where StudentID=500

    Delete from Students where StudentID=500

                            Databases 12
                Database Access
u   The SQL statement is then executed using:

    static String cmdString;


u   where cmdString contains the SQL statement

                           Databases 13
                 Database Access
u   The database update statement returns a
    count of the number of tuples that were
    static int updateCount;

    updateCount = st.executeUpdate(cmdString);

u   If the count is zero, there was a problem with the
    request (e.g. a tuple to be deleted does not exist)
                              Databases 14
         Processing Result Sets
u   The “select” statement must be processed in
    a different manner because it may return
    multiple tuples
u   The tuples are returned in a “result set”
u   Information about the result set is returned
    in a system “meta data” structure

                      Databases 15
          Processing Result Sets
u   The following statements select all tuples
    from the Students relation
u   The results are returned in rs2
u   The meta data is returned in md2

     cmdString = "Select * from " +Name;
     java.sql.ResultSet rs2 = st2.executeQuery(cmdString);
     java.sql.ResultSetMetaData md2 = rs2.getMetaData();

                          Databases 16
             Processing Result Sets
u   A simple routine that loops over all tuples in a result set
u   For each tuple, loop over the columns/attributes and print
    each value
    while( {
        System.out.print("TUPLE: | ");
        for(int i=1; i<= md2.getColumnCount(); i++) {
           String value = rs2.getString(i);
             System.out.print(value + " | ");
                                Databases 17
          Processing Result Sets
u   The previous routine used “getString(i)” to
    retrieve the value of the ith column
u   Attributes may also be retrieved by name:
      getString("Name")         getString("Address")
u   If an attribute is not a string, its value can be
    retrieved using:
    getInt("attr") getFloat("attr")       getDouble("attr")

                           Databases 18
Select * from Students
TUPLE: | 100 | Joe Cool | UMSU |
TUPLE: | 200 | Joe Jock | Frank Kennedy |
TUPLE: | 300 | New Nerd | Nowhere |

                          Databases 19
Insert into Students Values(500, 'James Bond', 'MI5')

Select * from Students
TUPLE: | 100 | Joe Cool | UMSU |
TUPLE: | 200 | Joe Jock | Frank Kennedy |
TUPLE: | 300 | New Nerd | Nowhere |
TUPLE: | 500 | James Bond | MI5 |

                           Databases 20
Update Students Set Name='Jesse James', Address='Dodge
  City' Where StudentID=500

Select * from Students
TUPLE: | 100 | Joe Cool | UMSU |
TUPLE: | 200 | Joe Jock | Frank Kennedy |
TUPLE: | 300 | New Nerd | Nowhere |
TUPLE: | 500 | Jesse James | Dodge City |

                          Databases 21
Delete from Students where StudentID=500

Delete from Students where StudentID=400
***Update count is 0

Select * from Students
TUPLE: | 100 | Joe Cool | UMSU |
TUPLE: | 200 | Joe Jock | Frank Kennedy |
TUPLE: | 300 | New Nerd | Nowhere |

                          Databases 22
Select * from Courses
TUPLE: | 74371 | User Interfaces |
TUPLE: | 74338 | Databases |
TUPLE: | 74335 | Software Engineering |

Select * from StudentsCourses
TUPLE: | 74371 | 100 | C+ |
TUPLE: | 74338 | 100 | B |
TUPLE: | 74371 | 200 | A+ |
TUPLE: | 74335 | 300 | A |
                         Databases 23
          Closing the Database
u   A simple “close” command is used to close
    the database:


                    Databases 24
         Database Programming
u   All JDBC statements must be enclosed in a
    try/catch block

       try {...}
       catch(Exception e) {...}
u   One procedure should be used to process all
    SQL exceptions
       catch(Exception e) {processSQLError(e);}

                           Databases 25
        Database Programming

try {
  cmdString="Insert into " +Name +" Values(" +Values +")";
  updateCount = st1.executeUpdate(cmdString);
  } catch(Exception e) {processSQLError(e); }

                        Databases 26
             Database Programming
u    The following routine inserts one tuple into
     any table/relation in the database

    public static void insertTuple(String Name, String Values) {
     try {
       cmdString = "Insert into " +Name +" Values(" +Values +")";
       updateCount = st.executeUpdate(cmdString);
        } catch(Exception e) {processSQLError(e);}
     } // end of insertTuple

                                 Databases 27

u   A header window makes it easy to add options or
    functions at a later time

                       Databases 28

u   TestDB is used to run the test routine
u   Sequential retrieves students sequentially, one student with
    each button click
u   Random retrieves the student whose StudentID is in the ID
                            Databases 29
u   Sequential processing                  involves   several
    database accesses
    – access the next student
       • issue Select * from Students if this is the first time
         that the command has been executed
       • otherwise, get the next tuple from the result set

          Select * from Students
          100 Joe Cool UMSU

                            Databases 30
– get the StudentsCourses tuples for the courses
  that the student is taking; then select the
  corresponding course description from Courses
   Select * from StudentsCourses where StudentID=100

   100 74371 C+
   Select * from Courses where CourseID=74371
   74371 User Interfaces

   100 74338 B
   Select * from Courses where CourseID=74338
   74338 Databases        Databases 31
u   In this example, the SC tuples are retrieved
    separately from the corresponding Course
u   They could, however, have been retrieved
    together using the statement:
    select SC.C#, Courses.CName, SC.Grade
      from SC, Courses
      where SC.C#=Courses.C#
                      Databases 32
u   Currently, the StudentsCourses tuples and
    the Courses tuple are printed but not
    – objects are created and could be processed
    – mySC: collection of SC tuples
    – myCourse: the course description of the current

                       Databases 33
u   If you decide to add fields to the database,
    you will have to make a collection of
    – to the database definition
    – to the corresponding object definition
    – to the processing in the DB class that extracts
      from the database and creates the object
    – to the user interface to add the new fields

                       Databases 34
u   Click the Design button to modify the database schema
                           Databases 35
u   Using “number” for key fields reduces the need for quotes
    in the SQL statements
                           Databases 36
public class Student {

   public String studentID;
   public String Name;
   public String Address;

   public Student(String newID, String newName, String newAddress) {
     studentID = newID;
     Name = newName;
     Address = newAddress;

                              Databases 37
u   In getStudentRandom and getStudentSequential

     if ( {
            myID = rs3.getString("StudentID");
            myName = rs3.getString("Name");
            myAddress = rs3.getString("Address");

                               Databases 38

u   The GUI will also have to be modified to display
    any new items
                       Databases 39
          User Interface Design
u   Layout the interface before adding the
    connection to the database !!
    – it is much easier to make changes to the UI by
      itself than having to change the UI, the
      database, the object class files, and the database
      accessor routines with each modification to the

                        Databases 40

u   A button that displays the courses in a new
    window has been added

                     Databases 41

u   TextFields are used for the display

                         Databases 42
u     10 text fields were dropped onto a new frame; the
      text fields were then collected in an array

JTextField fieldList[] = new JTextField[10];

fieldList[0] = jTextField1;
fieldList[1] = jTextField2;
fieldList[2] = jTextField3;
fieldList[3] = jTextField4;
                              Databases 43
u   The array of text fields could then be set to the
    contents of items in the array of SC objects

for (counter=0; counter<DB.lastCounter; counter++) {
    mySC = DB.myCourses[counter];
  for (counter=DB.lastCounter; counter<10; counter++) {
    fieldList[counter].setText(" ");
                         Databases 44

u   Another frame was added so that the information could be
    displayed in table form instead of in individual fields

                           Databases 45

u   The table permits a more compact display of the

                       Databases 46
u   The table was created by dropping a JTable
    swing component onto a new frame
u   The default constructor was changed so that
    the number of columns and rows was
    specified explicitly
     JTable jTable1 = new JTable();
    JTable jTable1 = new JTable(11, 3);

                       Databases 47
u   The following statements create headings
    for the table:
String[] colNames = {”CourseID", "CourseName", "Grade"};

for (counter=0; counter<3; counter++) {
    jTable1.setValueAt(colNames[counter], 0, counter);

                              Databases 48
u   The following statements take the contents
    of the student’s courses and put them in the

     for (counter=0; counter<DB.lastCounter; counter++) {
         mySC = DB.myCourses[counter];
         jTable1.setValueAt(mySC.courseID, counter+1, 0);
         jTable1.setValueAt(mySC.courseName, counter+1, 1);
         jTable1.setValueAt(mySC.grade, counter+1, 2);

                              Databases 49

To top