8. Java Databases and JDBC 1. Introduction to Databases

Relational DBMS

A DBMS in which data is stored in tables and

Relational DBMS

Commercial
Oracle
MS SQL Server
DB2

Free
Derby (Java DB)
MySQL

*

Infopulse Training Center

Cash Management System

*

Infopulse Training Center

Merchant Info

Name
Bank
Bank account
Charge percent
Aggregation period
Minimal sum

*

Infopulse Training Center

Customer Info

Name
Address
Email
Credit card No
Credit card type
Credit card maturity date

*

Infopulse Training Center

Payment info

Date
Customer
Merchant
Goods description
Sum

*

Infopulse Training Center

Java DB

Java DB is Oracle's supported distribution of the Apache Derby

Eclipse & Java DB

Creating a Driver Definition for Apache Derby
Creating an

Driver Definition (1 of 2)

Start Eclipse
Menu Window -> Preferences
Expand Data Management

Driver Definition (2 of 2)

Select derby.jar in Jar list tab and

Eclipse & Java DB

Creating a Driver Definition for Apache Derby
Creating an

Connection Profile

Switch to the Database Development perspective
In Data Source Explorer, right-click

Eclipse & Java DB

Creating a Driver Definition for Apache Derby
Creating an

Connecting to the Database

In the Database Development perspective, expand Database Connections

Eclipse & Java DB

Creating a Driver Definition for Apache Derby
Creating an

SQL Query

In the Database Development perspective, expand Database Connections in the

Merchant Info

Name
Bank
Bank account
Charge percent
Aggregation period
Minimal sum

*

Infopulse Training Center

Create Merchant Table

CREATE TABLE merchant
(
id INT NOT NULL GENERATED ALWAYS

Fill Merchant Table

INSERT INTO merchant
(name, charge, period, minSum,
bankName,

Display Merchant Data

*

Infopulse Training Center

select * from merchant;

Create Customer Table

Customer Info
Name
Address
Email
Credit card No
Credit card type
Credit card maturity date

*

Infopulse

Create Customer Table

CREATE TABLE customer
(
id INT NOT NULL GENERATED ALWAYS

Fill Customer Table

INSERT INTO customer
(name, address, email, ccNo, ccType, maturity)
values('Dan

Display Customer Data

select * from customer

*

Infopulse Training Center

Create Payment Table

Payment info
Date
Customer
Merchant
Goods description
Sum

*

Infopulse Training Center

Create Payment Table

CREATE TABLE payment
(
id INT NOT NULL GENERATED ALWAYS

Fill Payment Table

insert into payment
(dt, merchantId, customerId, goods, total)
values('2012-07-12 10:00:14', 3,

Display Payment Data

Select * from payment

*

Infopulse Training Center

CM Database Schema

*

Infopulse Training Center

Select Statement

Don’t use * in select!

*

Infopulse Training Center

Select Statement

SELECT dt, merchantId, customerId, goods, total FROM payment WHERE merchantId

Select Statement

SELECT dt, merchantId, customerId, goods, total FROM payment WHERE merchantId

Select Statement

SELECT dt, merchantId, customerId, goods, total FROM payment ORDER BY

Select Statement

SELECT dt, merchantId, customerId, goods, total FROM payment ORDER BY

Select Statement

SELECT sum(total) FROM payment WHERE customerId = 2;

*

Infopulse Training Center

Select Statement

SELECT sum(total) FROM payment WHERE customerId = 2;
Output is 2134.45

*

Infopulse

Select Statement

SELECT merchantId, count(*) as n, sum(total) as total FROM payment

Select Statement

SELECT merchantId, count(*) as n, sum(total) as total FROM payment

Select Statement

SELECT customerId, sum(total) FROM payment GROUP BY customerId HAVING count(*)>2;

*

Infopulse

Select Statement

SELECT customerId, sum(total) FROM payment GROUP BY customerId HAVING count(*)>2;

*

Infopulse

Join Operations

SELECT p.dt, m.name as merchant, c.name as customer,
p.goods,

Join Operations

*

Infopulse Training Center

Update Payments

*

Infopulse Training Center

Update Statement

UPDATE payment SET charge = total * 0.034 WHERE id

Update Statement

UPDATE payment SET charge = total * 0.034 WHERE id

Update Statement

UPDATE payment
SET charge = (SELECT p.total * m.charge /

Update Statement

*

Infopulse Training Center

Update Statement

UPDATE payment p SET charge = total * (SELECT charge

Update Statement

*

Infopulse Training Center

Update Merchants

*

Infopulse Training Center

Update Merchants

UPDATE merchant m SET total =
(SELECT sum(total - charge)

Update Merchants

*

Infopulse Training Center