intro to jdbc to effectively use java data base connectivity we must understand: 1.relational...

Intro to JDBC

To effectively use Java Data Base Connectivity we must understand:

1. Relational Database Management Systems (RDBMS)

2. JDBC Drivers

3. SQL (Structured Query Language)

4. Methods in packages java.sql & javax.sql

Intro to JDBC

Popular Relational Databases:

• Oracle

• Microsoft SQL Server

• Sybase

• Informix

• Microsoft Access

• mySQL

• CloudScape

… and many, many more!

RDBMS (p.1)

Intro to JDBC

A Database consists of:

Tables, which store…

records, which contain fields of information.

Relational Databases:

store data in multiple tables that can be related (or linked) to one another via key fields.

RDBMS (p.2)

Intro to JDBC

An example:

let’s look at the Microsoft Access database we just used.

RDBMS (p.3)

Number Name Department Salary Location

23603 Jones 413 1100 New Jersey

24568 Kerwin 413 2000 New Jersey

34589 Larson 642 1800 Los Angeles

35761 Myers 611 1400 Orlando

47132 Neumann 413 9000 New Jersey

78321 Stephens 611 8500 Orlando

Row

ColumnPrimary key

Intro to JDBCRDBMS (p.4)

A Sample Relational Database:

Intro to JDBCRDBMS (p.5)

In this example, a one-to-many relationship exists between the Contacts table and the Calls table. Contacts.ContactID is the primary key; Calls.ContactID is the foreign key. Primary and foreign keys need not have the same name, but must be of the same data type.

Intro to JDBCJDBC Drivers (p.1)

Type Description 1 The JDBC-to-ODBC bridge driver connects Java programs to Microsoft ODBC

(Open Database Connectivity) data sources. The Java 2 Software Development Kit from Sun Microsystems, Inc. includes the JDBC-to-ODBC bridge driver (sun.jdbc.odbc.JdbcOdbcDriver). This driver typically requires the ODBC driver to be installed on the client computer and normally requires configuration of the ODBC data source. The bridge driver was introduced primarily for development purposes and should not be used for production applications.

2 Native-API, partly Java drivers enable JDBC programs to use database-specific APIs (normally written in C or C++) that allow client programs to access databases via the Java Native Interface. This driver type translates JDBC into database-specific code. Type 2 drivers were introduced for reasons similar to the Type 1 ODBC bridge driver.

3 JDBC-Net pure Java drivers take JDBC requests and translate them into a network protocol that is not database specific. These requests are sent to a server, which translates the database requests into a database-specific protocol.

4 Native-protocol pure Java drivers convert JDBC requests to database-specific network protocols, so that Java programs can connect directly to a database.

Fig. 23.26 JDBC driver types.


Sun’s JDBC-ODBC Bridge – a type 1 driver

Tips:- Always use System DSN, so DSN is available to all

- ODBC Data Sources are only available on LAN/WAN (Not Internet accessible)

- Use a Java/NativeAPI driver to access databases via internet

- More info: JDBC-ODBC Bridge Driver

http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/bridge.doc.html


More drivers available at: http://industry.java.sun.com/products/jdbc/drivers

Lab: Find a type 2, 3 or 4 driver to use with Microsoft Access

Your driver documentation will give you the class name to use. For instance, if the class name is jdbc.DriverXYZ , you would load the driver with the following line of code:

Class.forName("jdbc.DriverXYZ");

Another driver lab: Set up distributed DB at \\B122-1

http://industry.java.sun.com/products/jdbc/drivers

Intro to JDBCSQL (Structured Query Language) (p.1)

SQL is an industry standard for querying relational databases.

Although SQL, like everything else in the software industry, exists in several versions and flavors, basic syntax is consistent and reliable across all systems and software.

Database languages, such as SQL, ordinarily include two sublanguages:

• Data Definition Language (DDL)

• Data Manipulation Language (DML)

Intro to JDBCSQL (p.2)

• Data Definition Language (DDL): Enables developer to create and modify database architecture (tables, records, fields).

Query keywords:

CREATE TABLE

ALTER TABLE

DROP TABLE


• Data Manipulation Language (DML): Enables developer to add, edit and delete records and retrieve data.

Query keywords:

SELECT – retrieves data from table(s)

INSERT – populates table with data

UPDATE – changes existing data in table

DELETE – removes data from table


SELECT statement clauses:

• SELECT – Select and retrieve records

• FROM – Tables from which to get fields

• WHERE – Criteria for filtering selections

• GROUP BY – How to group found records

• HAVING – Used with GROUP BY to specify criteria

• ORDER BY – Criteria for ordering found records


SELECT statement syntax:

• Basic SELECT Query :

SELECT FieldName, … FROM TableName, …

• Examples:

SELECT * FROM Authors

SELECT AuthorID, LastName FROM Authors



• SELECT Query with WHERE clause:SELECT FieldName, … FROM TableName, …WHERE criteria

• Examples:SELECT * FROM Authors WHERE YearBorn > 1960

SELECT * FROM Authors WHERE LastName = ‘Dietel’

(WHERE operators: < , > , <= , >= , = , < > , LIKE ( [?, *] )



• ORDER BY Clause:

SELECT FieldName, … FROM TableName, …WHERE criteriaORDER BY FieldName, … ASC | DESC

• Example:

SELECT * FROM AuthorsORDER BY LastName, FirstName DESC



• WHERE with LIKE clause:SELECT * FROM Authors WHERE lastName LIKE ‘D*’ [Or, use ‘D%’ other DBs]

• LIKE clause wildcard operators:? * [Or, _ % other DBs]

authorID firstName lastName 1 Harvey Deitel 2 Paul Deitel Fig. 23.15 Authors whose last name starts with D from

the authors table.



• WHERE with LIKE clause:SELECT authorID, firstName, lastName FROM Authors WHERE lastName LIKE ‘?i*’ [Or, use ‘_i%’ other DBs]

• LIKE clause wildcard operators:? * [Or, _ % other DBs]

authorID firstName lastName 3 Tem Nieto Fig. 23.16 The only author from the authors table

whose last name contains i as the second letter.



• A More Complex Example Using an Implied Join:

SELECT PublisherName, TitleFROM Publishers, TitlesWHERE (Titles.PublisherID = Publishers.PublisherID)ORDER BY PublisherName, Title ASC

ORSELECT Publishers.PublisherName, Titles.TitleFROM Publishers, TitlesWHERE (Titles.PublisherID = Publishers.PublisherID)ORDER BY Publishers.PublisherName, Titles.Title ASC


INSERT statement syntax:

• Basic INSERT Query :

INSERT INTO tableName ( columnName1, .., columnNameN )VALUES ( value1, … , valueN )

• Example:

INSERT INTO authors ( firstName, lastName )

VALUES ( ‘Sue’, ‘Smith’ )


UPDATE statement syntax:

• Basic UPDATE Query :

UPDATE tableName

SET columnName1 = value1, … , columnNameN = valueN

WHERE criteria

• Example:

UPDATE authors SET lastName = ‘Jones’WHERE lastName = ‘Smith’ AND firstName = ‘Sue’


DELETE statement syntax:

• Basic DELETE Query :

DELETE FROM tableName

WHERE criteria

• Example:

DELETE FROM authors WHERE lastName = ‘Jones’ AND firstName = ‘Sue’

Intro to JDBCPackage java.sql (p.1)

Result Sets

• ResultSet (Recordset in ASP):

JDBC returns query results in a ResultSet Object

• ResultSets are retrieved by executing queries:

resultSet = statement.executeQuery( query );


Traversing Result Sets

• A ResultSet can be traversed forward and backward (depending on cursor type) using:

resultSet.first();resultSet.next(); resultSet.previous();resultSet.last();

… and more!


Data types for Result Sets

• Data can be retrieved from a ResultSet by using various getXXX methods:

resultSet.getString(FieldName); // returns data as typeresultSet.getFloat(FieldName); resultSet.getDate(FieldName);resultSet.getInt(FieldName);resultSet.getMetaData(); // returns ResultSetMetaData obj.

… and more!


Result Set Cursors

ResultSet static type constant

Description

TYPE_FORWARD_ONLY Specifies that a ResultSet’s cursor can move only

in the forward direction (i.e., from the first row to the last row in the ResultSet).

TYPE_SCROLL_INSENSITIVE Specifies that a ResultSet’s cursor can scroll in

either direction and that the changes made to the ResultSet during ResultSet processing are not reflected in the ResultSet unless the program queries the database again.

TYPE_SCROLL_SENSITIVE Specifies that a ResultSet’s cursor can scroll in

either direction and that the changes made to the ResultSet during ResultSet processing are reflected immediately in the ResultSet.

Fig. 23.28 ResultSet constants for specifying ResultSet type.

intro to jdbc to effectively use java data base connectivity we must understand: 1.relational...

Documents