programming with databases n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Programming with Databases PowerPoint Presentation
Download Presentation
Programming with Databases

Loading in 2 Seconds...

play fullscreen
1 / 11

Programming with Databases - PowerPoint PPT Presentation


  • 40 Views
  • Uploaded on

Programming with Databases.

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 'Programming with Databases' - marvin


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
programming with databases

Programming with Databases

What is JDBC? page 2Database drivers page 3The example Database page 4Establishing database contact page 5The relationship between SQL data types, Java data types and getxxx() methods page 6A bigger example page 7A database application page 8The three-layer architecture page 9Transactions page 10Compiled SQL statements page 11

Only to be used in connection with the book "Java the UML Way", by Else Lervik and Vegard B. Havdal.

ISBN 0-470-84386-1, John Wiley & Sons Ltd 2002The Research Foundation TISIP, http://tisip.no/engelsk/

what is java database connectivity jdbc
What is Java Database Connectivity (JDBC)?
  • JDBC is
    • not a database system, but makes communication with a database system possible
    • not a ”point and click, drag and drop” tool, such as Microsoft Access, but it may be used by the developers of such tools to communicate with the database.
  • JDBC is an API offering classes that makes it possible to send SQL statements to a database, and to interpret the results from the statements.
  • This chapter assumes that you are familiar with relational databases and SQL.

Only to be used in connection with the book "Java the UML Way", by Else Lervik and Vegard B. Havdal.

ISBN 0-470-84386-1, John Wiley & Sons Ltd 2002The Research Foundation TISIP, http://tisip.no/engelsk/

database drivers
Database Drivers

Java

application

Java

application

Java

application

JDBC Driver Manager

JDBC-ODBC Bridge

Oracle Driver

Sybase Driver

ODBC Driver

A database driver

consists of classes

that implement

certain interfaces.

Sybase

Database

Oracle

Database

Microsoft

Access

Microsoft

SQL Server

Only to be used in connection with the book "Java the UML Way", by Else Lervik and Vegard B. Havdal.

ISBN 0-470-84386-1, John Wiley & Sons Ltd 2002The Research Foundation TISIP, http://tisip.no/engelsk/

the example database
The Example Database

create table person(

identno integer primary key,

firstname varchar(30) not null,

lastname varchar(30) not null);

insert into person values (100, 'EDWARD', 'BROWN');

insert into person values (101, 'ANN MARGARET', 'GREEN');

insert into person values (102, 'JOHN', 'JOHNSON');

Only to be used in connection with the book "Java the UML Way", by Else Lervik and Vegard B. Havdal.

ISBN 0-470-84386-1, John Wiley & Sons Ltd 2002The Research Foundation TISIP, http://tisip.no/engelsk/

a little program communicating with the database
A Little Program Communicating With the Database

import javax.swing.*;

import java.sql.*;

class DatabaseContact {

public static void main(String[] args) throws Exception {

String databaseDriver = "oracle.jdbc.driver.OracleDriver";

Class.forName(databaseDriver);

String userName = JOptionPane.showInputDialog("User Name: ");

String password = JOptionPane.showInputDialog("Password: ");

String databaseName = "jdbc:oracle:thin:@loiosh.stud.idb.hist.no:1521:orcl";

Connection conn

= DriverManager.getConnection(databaseName, userName, password);

Statement statement = conn.createStatement();

ResultSet res = statement.executeQuery("select * from person");

while (res.next()) {

int idNo = res.getInt("identNo");

String firstName = res.getString("firstname");

String lastName = res.getString("lastname");

System.out.println(idNo + ": " + firstName + " " + lastName);

}

res.close();

statement.close();

conn.close();

System.exit(0);

}

}

Our database driver is the classes111.zip file. CLASSPATH has to contain classes111.zip.This file contains, among other things oracle.jdbc.driver.OracleDriver.

Only to be used in connection with the book "Java the UML Way", by Else Lervik and Vegard B. Havdal.

ISBN 0-470-84386-1, John Wiley & Sons Ltd 2002The Research Foundation TISIP, http://tisip.no/engelsk/

the relationship between sql data type java data types and getxxx methods
CHAR,

VARCHAR,

LONGVARCHAR String getString()

NUMERIC,

DECIMAL java.math.BigDecimal getBigDecimal()

BIT boolean getBoolean()

TINYINT byte getByte()

SMALLINT short getShort()

INTEGER int getInt()

BIGINT long getLong()

REAL float getFloat()

FLOAT,

DOUBLE double getDouble()

BINARY,

VARBINARY,

LONGVARBINARY byte[] getBytes()

DATE java.sql.Date getDate()

TIME java.sql.Time getTime()

TIMESTAMP java.sql.Timestamp getTimestamp()

The Relationship Between SQL Data Type, Java Data Types, and getxxx() Methods

Only to be used in connection with the book "Java the UML Way", by Else Lervik and Vegard B. Havdal.

ISBN 0-470-84386-1, John Wiley & Sons Ltd 2002The Research Foundation TISIP, http://tisip.no/engelsk/

a bigger example
A Bigger Example

Database

getAll()

updateName(thePerson: Person)

registerNewPerson(firstName: String, lastName: String)

deletePerson(identNo: int)

closeTheConnection()

Show program listing 20.2, pp. 629-634.

Only to be used in connection with the book "Java the UML Way", by Else Lervik and Vegard B. Havdal.

ISBN 0-470-84386-1, John Wiley & Sons Ltd 2002The Research Foundation TISIP, http://tisip.no/engelsk/

a database application
A Database Application

Show program listing 20.3, pp. 635-639.

Only to be used in connection with the book "Java the UML Way", by Else Lervik and Vegard B. Havdal.

ISBN 0-470-84386-1, John Wiley & Sons Ltd 2002The Research Foundation TISIP, http://tisip.no/engelsk/

the three layer architecture
The Three-Layer Architecture

1. The physical database

2. The component which communicates

with the database

3. The user interface component which

communicates with the component above.

Solve the problems, page 641.

Only to be used in connection with the book "Java the UML Way", by Else Lervik and Vegard B. Havdal.

ISBN 0-470-84386-1, John Wiley & Sons Ltd 2002The Research Foundation TISIP, http://tisip.no/engelsk/

transactions
Transactions
  • A transaction is a logical unit of work and can consist of multiple update statements to the database.
  • For example, if money is going to be transferred from one account to another, it’s important that the balances in both of the accounts change. If an error occurs, we can’t risk that only one of the accounts changes its balance. This transaction consists of two update statements.
  • The transaction is worked out in a correct way: Commit!
  • The transaction is not worked out in a correct way: Rollback!
  • As default, every single SQL statement is a transaction unit.
  • The Connection interface let us create transactions consisting of more than one SQL statement.
    • public void setAutoCommit(boolean autocommit) // default is true
    • public void commit()
    • public void rollback()

Solve problem 1, page 645.

Only to be used in connection with the book "Java the UML Way", by Else Lervik and Vegard B. Havdal.

ISBN 0-470-84386-1, John Wiley & Sons Ltd 2002The Research Foundation TISIP, http://tisip.no/engelsk/

compiled sql statements
Compiled SQL Statements
  • The Java interpreter sends the SQL statement to the database system.
  • The database system sets up a plan so that the search can be done in the most efficient way possible.
  • Unnecessary to compile again if only literals are changed:
    • select * from person where firstName like ’ANNE’ and lastName like ’GREEN’;
    • select * from person where firstName like ’JOHN’ and lastName like ’JOHNSON’;
  • The PreparedStatement interface let us create precompiled statement objects. An example:

String sqlStatement = "select * from person where firstName like ? and lastName like ?";

PreparedStatement statement = conn.prepareStatement(sqlStatement);

setning.setString(1, ”ANNE”); // The search criteria may be input,

setning.setString(2, ”GREEN”); // see program listing 20.4 pp. 644-645.

ResultSet res = statement.executeQuery();

while (res.next()) {

…osv….

Only to be used in connection with the book "Java the UML Way", by Else Lervik and Vegard B. Havdal.

ISBN 0-470-84386-1, John Wiley & Sons Ltd 2002The Research Foundation TISIP, http://tisip.no/engelsk/