DataBase Concepts
JDBC, Navigation Controls
Compiled By: Umm-e-Laila & Aneeta Siddiqui
Lecture # 15 &16
1
Course Books
Text Book:
Herbert Schildt, Java: The Complete Reference, McGraw-Hill
Education, Eleventh Edition
Craig Larman, Applying UML & patterns, 2 edition
Reference Books:
Cay S. Horstmann, Big Java: Early Objects, Wiley, 7th Edition
Herbert Schildt, Java: A Beginner's Guide, McGraw-Hill Education,
Eighth Edition
2
Course Instructors
Umm-e-Laila ulaila@ssuet.edu.pk
Assistant Professor, CED
Room Number: BS-04
Tel: 111-994-994, Ext. 536
Aneeta Siddiqui aarshad@ssuet.edu.pk
Assistant Professor, CED
Room Number: BS-03
Tel: 111-994-994,
3
Course Website
http://sites.google.com/site/ulaila206
4
Relational Database Model
A relational database uses tables to store
and manipulate data.
Each table contains one or more records, or rows, that contain the data for a
single entry.
Each row contains one or more fields, or columns, with each column representing
a single item of data. (attributes)
Most tables contain a primary key that uniquely
identifies each row in the table.First field usually
primary key
Relational Database Model
Relational Database Structure
Table: Employee
EID EName Department Salary Location
35761 MYERS, B. 611 1400 ORLANDO
A record 47132 NEUMANN, C. 413 9000 NEW JERSEY
7832 STEPHENS, T. 611 8000 ORLANDO
1
Primary A column
Key
Database management
system (DBMS)
The software that manages a relational
database is called a database management
system (DBMS).
Four of the most popular database
management systems today are Oracle,
Microsoft SQL Server, IBM’s DB2, and
Microsoft Access.
The tables in a relational database are
related to each other through their key fields.
7
For example, the BookCode
field is used to relate the Books
and BookOrders tables. The
BookCode field in the
BookOrders table is called a
foreign key because it identifies
a related row in the Books
table.
Three types of relationships
can exist between tables. The
most common type is a one-to-
many relationship as illustrated
above.However, two tables
can also have a one-to-one
relationship or a many-to-many
relationship.
8
Structured Query Language
Used to
Query a database
Insert records into a database
Update existing records in a database
Data Definition Language (DDL)
CREATE relations, attributes, etc.
Data Manipulation Language (DML)
INSERT, UPDATE or DELETE data
Data Query Language (DQL)
SELECT data from relations
DQL: SELECT-ing Results
SQL keywords
SELECT, FROM, WHERE, GROUP BY,
HAVING, ORDER BY
SELECT {attr-list}
FROM {table-list}
[ WHERE {pred-list} ];
Logically:
Computes cross-product of all tables
Discards results that don’t match predicates
Returns listed attributes
DQL: SELECT-Query
SELECT Query
Selects information from one more tables
Format
SELECT * FROM TableName
Asterisk * - select all
SELECT * FROM Employee
Selects entire Employee table
Selecting specific fields
Replace asterisk (*) with comma separated list
SELECT EID, EName FROM Employee
Ordered left to right
WHERE Clause
Selection with criteria
Only select data that meets requirement
SELECT * FROM TableName WHERE criteria
Example
SELECT * FROM Employee WHERE Salary > 5000
EID EName Department Salary Location
47132 NEUMANN, C. 413 9000 NEW JERSEY
78321 STEPHENS, T 611 8000 ORLANDO
WHERE Clause
Conditions
Can use <, >, <=, >=, =, <> and LIKE
LIKE - used for pattern matching
Search for similar strings
Wildcard characters * and ?
* - Any number of consecutive characters at
asterisk's location
SELECT * FROM Authors WHERE EName LIKE ‘N*'
EID EName Department Salary Location
47132 NEUMANN, C. 413 9000 NEW JERSEY
LastName starts with 'd' followed by any number of
characters
WHERE Clause
Conditions
? - any single character at location
SELECT * FROM Authors WHERE LastName LIKE ‘?T*'
EID EName Department Salary Location
78321 STEPHENS, T 611 8000 ORLANDO
EName begins with any character, ‘T' for second
character, followed by any number of characters
WHERE Clause
Conditions
Range of characters
[startValue-endValue]
SELECT * FROM Authors WHERE LastName LIKE ‘[N-S]*'
EID EName Department Salary Location
47132 NEUMANN, C. 413 9000 NEW JERSEY
78321 STEPHENS, T 611 8000 ORLANDO
35761 MYERS, B 611 1400 ORLANDO
Start with letter between N and S, followed by any
number of characters
ORDER BY Clause
Arrange results in order
SELECT * FROM TableName ORDER BY field ASC
SELECT * FROM TableName ORDER BY field DESC
field - field used to order
ASC/DESC - ascending/descending sort
ASC default
SELECT * FROM Authors ORDER BY Salary DESC
EID EName Department Salary Location
47132 NEUMANN, C. 413 9000 NEW JERSEY
78321 STEPHENS, T 611 8000 ORLANDO
35761 MYERS, B 611 1400 ORLANDO
ORDER BY Clause
Multiple fields
ORDER BY field1 SortingOrder, field2
SortingOrder, ...
SortingOrder does not have to be same
If field1 identical for two records, sorts by
field2 in order specified
SELECT * FROM Employee ORDER BY EID, EName
EID EName Department Salary Location
35761 MYERS, B 611 1400 ORLANDO
47132 NEUMANN, C. 413 9000 NEW JERSEY
78321 STEPHENS, T 611 8000 ORLANDO
ORDER BY Clause
Combining clauses
SELECT * FROM Employee
WHERE Ename LIKE ‘*N*'
ORDER BY Salary ASC
Multiple lines for readability
EID EName Department Salary Location
47132 NEUMANN, C. 413 9000 NEW JERSEY
78321 STEPHENS, T 611 8000 ORLANDO
What is JDBC?
“An API that lets you access virtually any tabular data source from
the Java programming language”
JDBC Data Access API
What’s a tabular data source?
“… access virtually any data source, from relational databases to
spreadsheets and flat files.”
JDBC provides Java applications with access to most database
systems via SQL
The architecture and API closely resemble Microsoft's ODBC
JDBC 1.0 was originally introduced into Java 1.1
JDBC 2.0 was added to Java 1.2
JDBC is based on SQL-92
JDBC classes are contained within the java.sql package
There are few classes
There are several interfaces
19
20
How to access a database
with
Javacan use JDBC (Java Database Connectivity)
Before an application
to manipulate the data in a database, you need to connect the
application to the database.
The Java application uses the JDBC driver manager to load a
database driver. Then, the Java application can use one or more of
the driver types to connect to the database and manipulate the data.
JDBC-ODBC bridge driver to connect to a database through ODBC
(Open Database Connectivity), which is a standard way to access
databases that were developed by Microsoft. Since ODBC drivers
exist for most modern databases,
JDBC-ODBC bridge driver provides a way to connect Java with
almost any database type. And this driver is included as a part of the
Java.
21
A JDBC-ODBC bridge driver converts JDBC calls into
ODBC calls that access the DBMS protocol. This data
access method requires that the ODBC drivers be
installed on the client machines
22
JDBC Classes
DriverManager
Manages JDBC Drivers
Used to Obtain a connection to a Database
• Types
Defines constants which identify SQL types
Date
Used to Map between java.util.Date and the SQL DATE type
• Time
Used to Map between java.util.Date and the SQL TIME type
TimeStamp
Used to Map between java.util.Date and the SQL TIMESTAMP type
JDBC Interfaces
Driver
All JDBC Drivers must implement the Driver interface. Used to obtain a
connection to a specific database type
• Connection
Represents a connection to a specific database
Used for creating statements
Used for managing database transactions
Used for accessing stored procedures
Used for creating callable statements
Statement
Used for executing SQL statements against the database
JDBC Interfaces
ResultSet
Represents the result of an SQL statement
Provides methods for navigating through the resulting data
• PreparedStatement
Similar to a stored procedure
An SQL statement (which can contain parameters) is compiled and
stored in the database
CallableStatement
Used for executing stored procedures
DatabaseMetaData
Provides access to a database's system catalogue
ResultSetMetaData
Provides information about the data contained within a ResultSet
JDBC
Conceptual Components
Driver Manager: Loads database drivers and manages
connections between the application and the driver
Driver: Translates API calls into operations for specific
database
Connection: Session between application and data
source
Statement: SQL statement to perform query or update
Metadata: Information about returned data, database,
& driver
Result Set: Logical set of columns and rows of data
Creates Creates Creates
returned by executing
DriverManager Connectiona statementStatement Result Set
Driver
Established
Link to DB
Database
11/25/2024 26
Basic steps to use a database
inImport
Java the necessary classes
Load the driver (Only performed once)
Identify the data source (Define the Connection
URL)
Establish a connection
Create JDBC Statements object from the Connection
Execute query string using SQL Statements.
Updates, inserts and deletes return Boolean. Selects return a
ResultSet
GET ResultSet
Navigate ResultSet, using data as required
Close connections
Close ResultSet, Close Statement & Connection Object in order
27
1. Import the necessary
classes
Import the packages:
Requires that you include the packages
containing the JDBC classes needed for
database programming. Most often,
using import java.sql.*
We need the MySQL Connector/J driver. If
you are using NetBeans IDE, than you have
already the driver at hand. Inside the Projects
tab, right click on the Libraries node and
select Add Library option. From the list of
options, select MySQL JDBC Driver.
28
2. Loading Drivers
The DriverManager is a singleton
Each JDBC Driver is also a singleton
When a JDBC Driver class is loaded, it must create an
instance of itself and register that instance with the JDBC
DriverManager
Requires that you initialize a driver so you can open a
communication channel with the database.
To start, we use the forName method of the Class class to
load the driver.
• Use the static method Class.forName()
• For MySQL:
Class.forName("com.mysql.jdbc.Driver");
• For Derby Db:
Class.forName("org.apache.derby.jdbc.ClientDriver");
3. Establish a connection
Once a Driver is loaded, a connection can be made to the database
Requires using the getConnection method of the DriverManager
class to return a connection object, which represents a physical
connection with the database.
Required to communicate with a database via JDBC
Three separate methods:
public static Connection getConnection(String url)
public static Connection getConnection(String url, Properties info)
public static Connection getConnection(String url, String user, String
password)
The connection is defined by URL
The URL has the following form:
jdbc:driver:databasename
The URL for these drivers starts with “jdbc”. Then, for JDBC-ODBC bridge
drivers, the subprotocol is “odbc or mysql or derby ” and the database URL is
the name that you used when you configured your database.
jdbc:<subprotool>:databasename
:
A connection is obtained in the following manner:
Connection aConnection = DriverManager.getConnection("jdbc:odbc:myDatabase");
DriverManager.getConnection("jdbc:mysql://hostname:port/
dbname","username", "password");
• For MySQl:
• DriverManager.getConnection("jdbc:mysql://localhost:3306/
dbname","username", "password");
• For Built In Derby Db:
• DriverManager.getConnection("jdbc:derby://localhost:1527/
Registration","laila", "123");
31
Sample code for driver loading and
establishing connection
try {
Class.forName("org.apache.derby.jdbc.ClientDriver"
);
// Class.forName("com.mysql.jdbc.Driver");
String myDb
="jdbc:derby://localhost:1527/AddressBook";}
DBconn =
DriverManager.getConnection(myDb,"app"," ");
catch (ClassNotFoundException e) {
System.out.println(“Driver not found”);
e.printStackTrace();
}
32
4.Create JDBC statement(s)
Requires using an object of type Statement for building and
submitting an SQL statement to the database.
The Connection interface defines many methods for
managing and using a connection to the database
public Statement createStatement()
public PreparedStatement prepareStatement(String sql)
public void setAutoCommit(boolean)
public void commit()
public void rollback()
public void close()
• The most commonly used method is createStatement()
When an SQL statement is to be issued against the database, a
Statement object must be created through the Connection
• Statement stmt = con.createStatement() ;
Creates a Statement object for sending SQL statements to the
database
Transactions and JDBC
JDBC allows SQL statements to be grouped together into a
single transaction
Transaction control is performed by the Connection object,
default mode is auto-commit, I.e., each sql statement is treated
as a transaction
We can turn off the auto-commit mode with
con.setAutoCommit(false);
And turn it back on with con.setAutoCommit(true);
Once auto-commit is off, no SQL statement will be committed
until an explicit is invoked con.commit();
At this point all changes done by the SQL statements will be
made permanent in the database.
34
5.Executing SQL Statements
The Statement interface defines two methods for executing
SQL against the database
public ResultSet executeQuery(String sql)
public int executeUpdate(String sql)
• executeQuery returns a ResultSet
• All rows and columns which match the query are contained within the
ResultSet
• The developer navigates through the ResultSet and uses the data as
required.
• executeUpdate returns the number of rows changed by the
update statement
This is used for insert statements, update statements and delete
statements
executeUpdate
String insertLehigh = "Insert into
Employees values“ +
"(123456789,Aslam,Siddiqui,40)";
stmt.executeUpdate(insertLehigh);
36
executeQuery
Then, the executeQuery method is called from the Statement object to
execute an SQL SELECT statement that’s coded as a string. Since this
SELECT statement only identifies a single record (the book with the
book code equal to WARP), this result set will be a table that contains
only one row. This type of query lets a user search for a book by its
book code.
By default, the createStatement method creates a forward-only, read-
only result set.
Statement stmt = conn.createStatement();
String sql;
sql = "SELECT id, first, last, age FROM Employees";
ResultSet rs = stmt.executeQuery(sql);
However, you can set the type and concurrency of a Statement object
by coding the constants above for the two arguments of the
createStatement method.
Statement statement = connection.createStatement(ResultSet.
TYPE_SCROLL_SENSITIVE, ResultSet.Concur_Update); 37
Resultsets
These Constants are provide so that we can
scroll result set easily and any update in the
data will be reflected
The result of a SQL query is available in
Resultset object. Resultsets are of 3 types.
Type Description
ResultSet.TYPE_FORWARD_ONLY The cursor can only move forward in the result set.
ResultSet.TYPE_SCROLL_INSENSITIVE The cursor can scroll forward and backward, and the result set
is not sensitive to changes made by others to the database that
occur after the result set was created.
ResultSet.TYPE_SCROLL_SENSITIVE. The cursor can scroll forward and backward, and the result set
is sensitive to changes made by others to the database that
occur after the result set was created.
38
Concurrency of ResultSet
By default get one that is CONCUR_READ_ONLY.
Concurrency Description
ResultSet.CONCUR_READ_ONLY Creates a read-only result set. This is the default
ResultSet.CONCUR_UPDATABLE Creates an updateable result set.
ResultSet results = statement.executeQuery( "SELECT * FROM
contacts");
rows= results.getRow();
Both the createStatement and executeQuery methods
throw an exception of theSQLException type. As a result,
any code that returns a result set will need to catch or
throw this exception.
39
6.Using a
ResultSet(Navigation)
The ResultSet interface defines many navigation
methods
40
6.Using a
ResultSet(DataAccess)
public boolean previous()
The ResultSet interface also defines data access methods
public int getInt(int columnNumber) -- Note: Columns are numbered
public int getInt(String columnName) -- from 1 (not 0)
public long getLong(int columnNumber)
public long getLong(String columnName)
public String getString(int columnNumber)
public String getString(String columnName)
There are MANY more methods. Check the API
documentation for a complete list
6.Get ResultSet
String queryLehigh = "select * from
Employees";
ResultSet rs =
Stmt.executeQuery(queryLehigh);
//What does this statement do?
while (rs.next()) {
int ssn = rs.getInt("SSN");
String name = rs.getString("NAME");
42
7. Close connection
rs.close();
stmt.close();
con.close();
43
SQL Types/Java Types Mapping
SQL Type Java Type
CHAR String
VARCHAR String
LONGVARCHAR String
NUMERIC java.Math.BigDecimal
DECIMAL java.Math.BigDecimal
BIT boolean
TINYINT int
SMALLINT int
INTEGER int
BIGINT long
REAL float
FLOAT double
DOUBLE double
BINARY byte[]
VARBINARY byte[]
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP java.sql.Timestamp
MetaData
Meta data is the information about the
database: e.g.
the number of columns,
the types of the columns meta data is the
schema information
45
Accessing Meta Data
The getMetaData() method can be used on a
ResultSet object to create its meta data
object.
e.g.
ResultSetMetaData md = rs.getMetaData();
int numCols = md.getColumnCount();
for (int i = 0; i <= numCols; i++) {
if (md.getColumnType(i) == Types.CHAR)
System.out.println( md.getColumnName(i) )
}
46
Example
import java.sql.*; //needed for JDBC
class MakeDB {
public static void main (String args[]) {
try {
/load the driver needed by the application
Class.forName("specialdb.Driver");
//Construct the database address
String dbaseURL = "jdbc:mysubprotocol://dbasehost/dbasename";
//Make the database connection
Connection dbConnection =
DriverManager.getConnection(dbaseURL, "dbaseuser", "dbasepasswd");
//Create a statement and execute the SQL query
Statement query = dbConnection.getStatement();
ResultSet results =
query.executeQuery("SELECT first_name, last_name from Employees);
47
//Iterate through the results and print them to standard output
while(results.next()) {
String fname = results.getString("first_name);
String lname = results.getString("last_name");
System.out.println("Found user " + fname + " " +
lname);
}
catch (SQLException e) {
System.out.println("SQLException: " + e.);
}
catch(ClassNotFoundException e) {
System.out.println("ClassNotFoundException: " + e);
}
}
}
48
Exercise
Design the login form and connect it with
Database to authenticate the user.
49
Navigation Controls in JDBC
Fetch first Record
String id="";
String pass="";
try{
r.first();
id = r.getString(1);
pass = r.getString(2);
System.out.println(id);
jTextArea1.setText( id);
//jTextArea1.setText(jTextArea1.getText()+"\n"+ name);
txtName.setText(id);
txtPass.setText(pass);
}catch(Exception e){e.printStackTrace();}
}
50
Fetch Last Record
String id="";
String pass="";
try{
r.last();
id = r.getString(1);
pass = r.getString(2);
System.out.println(id);
jTextArea1.setText( id);
//jTextArea1.setText(jTextArea1.getText()+"\n"+
name);
txtName.setText(id);
txtPass.setText(pass);
}catch(Exception e){e.printStackTrace();}
51
Fetch Previous Record
String id="";
String pass="";
try{
if(!r.isFirst()){
r.previous();
id = r.getString(1);
pass = r.getString(2);
System.out.println(id);
jTextArea1.setText( id);
//jTextArea1.setText(jTextArea1.getText()+"\n"+ name);
txtName.setText(id);
txtPass.setText(pass);
} //jTextArea1.setText(name);
}catch(Exception e){e.printStackTrace();}
52
Fetch Next Record
String id="";
String pass="";
try{ if(!r.isLast()){
r.next();
id = r.getString(1);
pass = r.getString(2);
System.out.println(id);
jTextArea1.setText( id);
//jTextArea1.setText(jTextArea1.getText()+"\n"+
name);
txtName.setText(id);
txtPass.setText(pass);
}
//jTextArea1.setText(name);
}catch(Exception e){e.printStackTrace();}
53
