The Java Crypto API by liamei12345

VIEWS: 4 PAGES: 32

									Java Database Connectivity
          JDBC
       ICW Lecture 12
       Errol Thompson
                Last Time
• URLs

• A reminder of Sockets.

• Threads: running processes at the same time
  on the same computer.

• They can be tricky to use.
            This lecture
• Relational databases

• Accessing relational databases from
  Java using JDBC and SQL

• Manipulating database structure from
  Java
                    References
• Java JDBC Documentation
  http://java.sun.com/javase/6/docs/technotes/guides/jdbc/
• JDBC Basics Tutorial
  http://java.sun.com/docs/books/tutorial/jdbc/basics/index.html
• JDBC Advanced Tutorial
  http://java.sun.com/developer/Books/JDBCTutorial/index.html
• mySQL Connector
  http://dev.mysql.com/downloads/connector/j/3.1.html
• Cay Horstmann (2008) Big Java, 3rd Edition. Wiley.
What is a Relational
   Database?
      Tables and Relationships
                                                    A table contains
                                     Teams          many records.
                                                    Each record has
                                                    a unique
        Riders                                      primary key.
                                                    Tables are
                                     Events         linked using the
                                                    keys.



A rider is a member of a team. A team comprises many riders.
A rider competes in an event. A event has many riders competing.
         Accessing a book
• If I want to read a book, what do I need
  to do?

• What do you need to do to access a
  file?

• Do these same ideas apply to a
  database?
     To access a relational
           database
• Need to know where the database is
• Need to know the structure of the
  database
  – What tables
  – What columns and keys
• Use SQL to form queries and to
  manipulate data
How do you open (connect) to
        a database?
     JDBC Database Access
        Accessing a database
• What do we need to be able to do to access a
  database from our program code?
  •   Obtain access (i.e. Open the database)
  •   Read data from the database
  •   Write data to the database
  •   Relinquish access (i.e. Close the database)
  •   Have transaction control
       • Group a set of updates
       • Rollback changes if errors occur
    Linking to database driver
Set the class path to find the driver
Database Driver
   Where do you find driver names?
      Should be in database vendor documentation
      For driver in assignment: “com.mysql.jdbc.Driver”
        or"org.gjt.mm.mysql.Driver"
Database URL
   How is a database URL constructed?
      “jdbc:<vendor sub protocol name>:<vendor specific options or
         commands>
      For mySQL: “jdbc:mysql://<Server URL>/<database name>”
There are variations
                     Java code
Loading the driver class
   Class.forName(" com.mysql.jdbc.Driver ");
Get a connection object (open the database connection
   Connection con = DriverManager.getConnection(url,
     username, password);
Use try {…} catch {…} around these
Methods available on connection object
   con.isClosed() returns true if connection is closed
   con.close() closes the connection
How do you obtain data from a
         database?
     JDBC Database Access
     Building and executing a
               query
SQL Select statement – Simple form
   Select <fields> from <table> where <condition>
Create a statement
   Statement <variable> = <connectionObject>.createStatement();
   PreparedStatement <variable> =
      <connectionObject>.prepareStatement(<parameterised query>);
   <preparedStatementObject>.set<dataType>(<parameter number>,
      <value>);
Execute query
   ResultSet <variable> = <statementObject>.executeQuery(<SQL
     Select Statement>);
Remember to use try {…} catch {…}
Retrieving data from result set
Stepping through data records
   <resutSetObject>.next();
Retrieving individual fields
   <varaible> = <resutSetObject>.get<DataType>(<column
     number>);
   <varaible> = <resutSetObject>.get<DataType>(<column
     name>);
       dataType can be int, double, String, Date, etc.
Close the result set
   <resutSetObject>.close();
How can you write data to a
       database?
    JDBC Database Access
          SQL commands
To add data
  Insert Into <Table> (<field names>) Values
    (<values>)
To modify data
  Update <Table> Set <Assignments> Where
   <condition>
   JDBC statement execution
Prepare as for SQL Select
Execute
   int <var> = <statementObject>.executeUpdate(<SQL
      statement>);
      Returns number of records changed
   <statementObject>.execute(<SQL statement>)
      Returns true if a result set has been created by SQL statement
   <statementObject>.getResultSet()
      Returns result set
   <statementObject>.getUpdateCount()
      Returns the number of records updated
How do I handle sets of data
  from a database in my
         program?
  Scrollable and updatable result sets
           Changes to the
          createStatement
Additional options on the createStatement and
  prepareStatement
  Type
     TYPE_FORWARD_ONLY
     TYPE_SCROLL_INSENSITIVE
     TYPE_SCROLL_SENSITIVE
  Concurrency
     CONCUR_READ_ONLY
     CONCUR_UPDATEABLE
           Scrollable result set
Step forward and backwards
   rs.next()
   rs.previous()
   rs.relative(n)
Skip to a specific row
   rs.absolute()
   rs.getRow()
Other methods
   first, last, beforeFirst, afterLast
   isFirst, isLast, isBeforeFirst, isBeforeLast
          Updateable result set
Must create or prepare statement with CONCUR_UPDATABLE
Able to update field values
    updateXxx(column, value)
    updateRow()
Able to add rows
    moveToInsertRow()
    updateXxx(column, value)
    insertRow()
    moveToCurrentRow()
Able to delete rows
    deleteRow()
How do I ensure that no other
program is updating the same
    data as my program?
       Transaction control
        What is the issue?
Should we treat a group of database
 updates as though they were one
 update?
  Purchase transaction updates
    Product inventory
    Sales history
    Customer transaction history
  What constitutes a complete transaction?
Implementation of transaction
          control
Switch off automatic commit
  conn.setAutoCommit(false);
Update the result set
Commit the changes
  conn.commit();
Oops! Something has gone wrong.
  conn.rollback();
Why is transaction control
   associated with the
 connection and not the
       statement?
           Batch updates
Improves the performance of update
  operations
  stat.addBatch(command);
  stat.executeBatch();
Might also treat the batch as a single
 transaction
Can I access information
about the structure of the
       database?
         Metadata
         What is Metadata
Data about the structure of the database
  What tables are in the database?
  How many columns are in a table?
  What columns are in the tables?
  What are the data types of the columns?
Metadata is held in a result set like
 conventional data
      SQL data management
           statements
All that are supported by the database software in use.
Includes
   Create Database
   Use <database>
   Create Table
   Create Relationships
   Modify Table
   Drop table
   Drop Database
                Conclusion
•   Reviewed the nature of a relational database
•   Connection to a database using JDBC
•   Retrieving data using SQL and JDBC
•   Using SQL to update the database
•   Using a result set to update the database
•   Database metadata using SQL
•   Using SQL commands to modify the
    database structure
             Next Time
• Servlets

								
To top