java and databases
Download
Skip this Video
Download Presentation
Java and Databases

Loading in 2 Seconds...

play fullscreen
1 / 25

Java and Databases - PowerPoint PPT Presentation


  • 91 Views
  • Uploaded on

Java and Databases. CS-328 Dick Steflik. Database Drivers. Think of a database as just another device connected to your computer like other devices it has a driver program to relieves you of having to do low level programming to use the database

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Java and Databases' - haven


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
java and databases

Java and Databases

CS-328

Dick Steflik

database drivers
Database Drivers
  • Think of a database as just another device connected to your computer
  • like other devices it has a driver program to relieves you of having to do low level programming to use the database
  • the driver provides you with a high level api to the database
slide3
ODBC
  • Open Data Base Connectivity
  • Developed by Microsoft for the Windows platform as the way for Windows applications to access Microsoft databases (SQL Server, FoxPro, Access)
  • Has become an industry standard
  • Most data base vendors supply native, odbc, and jdbc drivers for their data base products
jdbc architecture
JDBC Architecture

Java Application

JDBC API

Data Base Drivers

Access

SQL

Server

DB2

Informix

MySQL

Sybase

jdbc driver types
JDBC Driver Types
  • Type 1
    • JDBC-ODBC Bridge
  • Type 2
    • Native API, partially java
  • Type 3
    • JDBC Network Driver, partially java
  • Type 4
    • 100% Java
type 1 drivers
Type 1 Drivers
  • Translate JDBC into ODBC and use Windows ODBC built in drivers
  • ODBC must be set up on every client
    • driver must be physically on each machine for both java applications and applets
    • for server side servlets ODBC must be set up on web server
  • driver sun.jdbc.odbc.JdbcOdbc provided by JavaSoft with JDK
type 1 driver cont
Type 1 Driver (cont.)

Client

JDBC-ODBC

ODBC

DB

Vendor Library

type 2 drivers
Type 2 Drivers
  • Converts JDBC to data base vendors native SQL calls
  • like Type 1 drivers; requires installation of binaries on each client
type 2 drivers cont
Type 2 Drivers (cont.)

JDBC

Client

DB

Vendor Library

type 3 drivers
Type 3 Drivers
  • Translates JDBC to a DBMS independent network protocol
  • Typically talks directly with a middleware product which in turn talks to the RDBMS
    • Jaguar, DBAnywhere, SequeLink
  • Most flexible driver type
  • all java
type 3 drivers cont
Type 3 Drivers (cont.)

Vendor Middleware

Client

JDBC

DB

Tier 1

Tier 2

Tier 3

type 4 drivers
Type 4 Drivers
  • Converts JDBC directly to native API used by the RDBMS
  • compiles into the application , applet or servlet; doesn’t require anything to be installed on client machine, except JVM
  • handiest driver type
structured query language
Structured Query Language
  • Developed by C. Date for Relational Data Base Management Systems (RDBMS)
  • Simple Declarative Language
    • has no program control statements
slide15
SQL
  • Two categories of commands
    • Data Manipulation Commands
      • deal with:
        • RETRIEVING DATA
        • MAINTAINING DATA (ADDING, UPDATING,DELETING
    • Data Definition Commands
      • DEAL WITH:
        • CREATING DATABASE OBJECTS (TABLES,VIEWS)
        • Object organization and attributes
referential integrity
Referential Integrity
  • Rules to insure that table data stays accurate and accessible
    • rows in a table should be unique
      • one column should contain no duplicate data
      • primary key
    • column values cannot contain repeating groups or arrays
    • null is different than space and zero, 2 null values are not considered equal
data manipulation commands
Data Manipulation Commands
  • Select - query and display data from a database
  • Insert - a new row into a table
  • Update - modify a column in a table
  • Delete a row from a table
select
Select
  • Select (column list) from (sources) where (conditions) order by (ocolumn list)
  • column list - comma separated list of names of columns to be in output
    • Ex. ssn , lastname , firstname,gpa
    • can contain literals to be included in output
      • Ex. Ssn,”~”,lastname,”~”,firstname
  • sources - name(s) of table(s) to retrieve data from
  • conditions (optional) - conditions for selections
    • lastname like “S%”
    • (lastname = “Steflik) and (firstname like “R%”)
  • ocolumn list (optional)- list of columns that output should be ordered by
  • Ex
    • select * from student
    • select lastname,fristname from student where gpa > 3.0
    • select lastname,firstname from student order by lastname,firstname
select joined tables
Select - joined tables
  • Two tables may be joined and viewed as a single data source is the both have a common column
  • suppose we have 2 tables: Inventory and category and each has a column called catg_code
  • In category catg_code is unique and is the primary key
select joined tables cont
Select - joined tables (cont.)

join

Inventory

Category

P_no

Catg_code

qty

Catg_code

descr

select joined tables cont1
Select - joined tables (cont.)
  • To retrieve all of the part numbers and the name of the category to which the part belongs:
    • select inventory.p_no category.descr from inventroy , category
    • select a.p_no,” “,b.descr from a inventory , b category
insert
Insert
  • Add a row to a table
    • insert into category values(“IGN”,”Ignition System”)
  • use jdbc execute Update method
    • ex. Stmt.executeUpdate(“insert into Category values(‘IGN’,’Ignition System’)”);
update
Update
  • Modify an existing row in a table
    • update category set descr = “Ignition Subsystem” where catg_code = “IGN”
  • Use jdbc executeUpdate method
    • String s = “update category set descr = ‘Ignition Subsystem’ where catg_code = ‘IGN’ ”;
    • stmt.executeUpdate(s);
delete
Delete
  • Remove a row from a table
    • delete from category where catg_code = “IGN”
      • remove the IGN category
    • delete from category where catg_code like “I%”
      • remove all rows where catg_code starts with an “I”
data definition commands
Data Definition Commands
  • Create table
    • add a table to a database
  • Drop Table
    • remove a table from a database
  • Alter Table
    • add or delete column(s)
ad