Database Programming

Contents: Using SQL statements on a database Overview of JDBC Connecting to a database Working with result sets

Database Programming
Using SQL statements on a database
Creating a table/relation Adding or removing an attribute/field Updating data/record
Inserting/populating data Modifying Deleting

Reading data/record

Database Programming
Creating table - Syntax
CREATE TABLE table_name ( column_name column_data_type, column_name column_data_type, column_name column_data_type
...

);

Example:
CREATE TABLE Employee ( number INT NOT NULL, payRate DOUBLE PRECISION NOT NULL, first VARCHAR(255) , last VARCHAR(255) );

Database Programming
Adding an attribute/field syntax
ALTER TABLE table_name

ADD column_name column_data_type;

Example
ALTER TABLE Employee ADD sex char(1);

Removing an attribute/field - syntax

ALTER TABLE table_name

DROP column_name; Example

ALTER TABLE Employee DROP sex;

Inserting/populating data syntax INSERT INTO table_name VALUES (column1, column2, column3, ...); Example

INSERT INTO Employee VALUES (101, 20.00, Rich, Raposa);

INSERT INTO Employee (number, first, last) VALUES (102, Rich, Little);

Database Programming
Modifying data syntax
UPDATE table_name SET column_name = value, column_name = value, ... WHERE conditions;

Example
UPDATE Employee SET payRate=40.00 WHERE number=101;

Deleting data syntax

DELETE FROM table_name WHERE conditions;

Example
DELETE FROM Employee WHERE number=101;

Database Programming
Reading data syntax
SELECT column_name, column_name, ... FROM table_name WHERE conditions;

Example
SELECT first, last, payRate FROM Employee WHERE number =

101; SELECT * FROM Employee WHERE number = 101; SELECT number FROM Employee WHERE last LIKE R%; SELECT * FROM Employees WHERE payRate > 10.0 AND payRate <= 20.0;

Database Programming
Overview of JDBC
JDBC is a Java database connectivity API. Allows Java programs to contain database-independent code.
Provides a mechanism for Java code to be potable across databases.

Simplifies the creation and execution of SQL statements. Uses java.sql package.
java.sql.DriverManager - Manages the loading and unloading of

database drivers from the underlying system. java.sql.Connection - Handles the connection to a specific database. java.sql.Statement - Contains an SQL statement to be passed to the database. java.sql.ResultSet - Contains the record result set from the SQL statement passed to the database.

To connect to a database and access its contents, JDBC driver that works with that particular database is required.

Database Programming