JDBC

1
Java Database Connectivity
JDBC
2
Database Fundamentals
Database A collection of different types of
data organized to facilitate the
manipulation of the data
Database Management System Software that
organizes, manipulates, and retrieves data in
a database Provides - access protection,
concurrency,
integrity, consistency, and a more.
3
Database Fundamentals
Clients
Server
PC
DBMS
MAC
UNIX
4
Database Fundamentals

• SQL (Structured Query Language)
• A language to interact with a DBMS
• to define, store, manipulate, and retrieve data

5
SQL
To create a table CREATE TABLE table ( column
type , column type . . .
) Example CREATE TABLE employees (id int
PRIMARY KEY,
name char(25) NOT NULL,

salary int )
6
SQL
To drop a table DROP TABLE table
Example DROP TABLE employees
7
SQL
To drop a table DROP TABLE table
Example DROP TABLE employees
8
SQL
To supply a new record INSERT INTO table
VALUES (expr, expr,.. ) Example INSERT INTO
employees VALUES(1,JONES,60000)
1 JONES 60000
9
SQL
To delete rows DELETE FROM table WHERE
column expr Example DELETE FROM employees
where salary gt 75000
10
SQL
To retrieve data SELECT FROM table WHERE . .
. Examples SELECT name FROM employees SELECT
id, name, salary FROM employees WHERE salary gt
75000
11
JDBC Components
12
Using JDBC
STEPS
13
Registering a Driver
There are two ways to load a driver (DERBY with
Eclipse) Class.forName(org.apache.derby.jdbc.C
lientDriver) OR Driver myDriver new
org.apache.derby.jdbc.ClientDriver(
) DriverManager.registerDriver(myDriver)
14
Registering a Driver
There are two ways to load a driver (ORACLE in
CIS) Class.forName(oracle.jdbc.driver.OracleDr
iver) OR Driver myDriver new
oracle.jdbc.driver.OracleDriver(
) DriverManager.registerDriver(myDriver)
15
Specifying a Database
JDBC uses a particular syntax to name a
database jdbcltsubprotocolgtltsubnamegt jdbc
means the protocol is JDBC ltsubprotocolgt is the
name of the driver to use ltsubnamegt is a
parameter for the subprotocol
( Apache Derby Database with Eclipse )
jdbcderby//localhost1527/myDBcreatetrueuser
mepasswordmine
JDBC
employees
16
Specifying a Database
JDBC uses a particular syntax to name a
database jdbcltsubprotocolgtltsubnamegt jdbc
means the protocol is JDBC ltsubprotocolgt is the
name of the driver to use ltsubnamegt is a
parameter for the subprotocol
( Oracle Database in CIS )
jdbcoraclethincisjava/allen_at_hannibal1521cis
JDBC
cis
17
Sending an SQL statement
Once you have a statement object, you can send an
SQL statement to the database
import java.sql. public class
CreateAccountTable public static void
main(String args ) String url
Connection myConnection Statement mySql
try Class.forName(org.apache.derby
.jdbc.ClientDriver) // register
a driver url jdbcderby//localhost15
27/companyDBcreatetrueusercop3060passwordall
en' // specify the database
myConnection DriverManager.getConnection(url)
// make a connection mySql
myConnection.createStatement( )
// create a sql statement
// exectute statement mySql.executeUpdate(
CREATE TABLE employees (id int PRIMARY KEY, name
char(25) NOT NULL,salary int))
myConnection.close( ) catch( Exception
ex ) ex.printStackTrace( )
18
Sending an SQL statement
There are three ways to send a SQL statement to a
database
int executeUpdate(String sql) ResultSet
executeQuery(String sql) boolean execute(String
sql)
executeUpdate -- if the statement returns nothing
or an integer executeQuery -- if the query
returns rows of data execute -- if you are
unsure what the query returns
19
Sending an SQL statement
import java.sql. public class InsertEmployee
public static void main(String args )
String url Connection
myConnection Statement myStatement
int r try
Class.forName(org.apache.derby.jdbc.ClientDriv
er) url jdbcderby//localhost
1527/companyDBcreatetrueusercop3060password
allen myConnection
DriverManager.getConnection(url )
myStatement myConnection.createStatement( )
r myStatement.executeUpdate(INSERT
INTO employees (id,name,salary)
VALUES(6,chen,40000)) catch(
Exception ex ) e.printStackTrace( )
20
Example of AddEmployee Application
This is an Application that accepts an Id, Name,
and Salary, and adds it to the table employees
21
import java.awt. import java.awt.event. import
javax.swing. import java.sql. public class
AddEmployee extends JFrame implements
ActionListener private String url
private Connection myConnection
private Statement myStatement
private JTextField id, name, salary
private JLabel aLabel private JButton
submit public AddEmployee ( )
Container cont getContentPane( )
aLabel new JLabel(Id) id
new JTextField(5) cont.add(aLabel)
cont.add(id) aLabel new
JLabel(Name) name new
JTextField(10) cont.add(aLabel)
add(name) aLabel new
JLabel(Salary) salary new
JTextField(10) cont.add(aLabel)
cont.add(salary) submit new
JButton(Submit) submit.addActionListe
ner(this) cont.add(submit)
setSize(400,400) setVisible(true)
try Class.forName(org.apache.derby.j
dbc.ClientDriver) urljdbcderby//l
ocalhost1527/companyDBcreatetrue
/ usercop3060passwo
rdallen /
/ is added at the end of the url /
myConnection DriverManager.getConnection(url
) myStatement
myConnection.createStatement( )
catch( Exception ex )
e.printStackTrace( ) // end AddEmployee
constructor public void actionPerformed(ActionEven
t e) String sql sql INSERT INTO
employees (id,name,salary) VALUES( String
addId id.getText( ) String addName
name.getText( ) String addSalary
salary.getText( ) sql sql addId ,
sql sql addName ,
sql sql addSalary ) try
myStatement.executeUpdate(sql)
catch( Exception ex) ex.printStackTrace( )

This is an application that accepts an Id, Name,
and Salary, and adds it to the table employees
22
Handling Results

• Different statements return different result
  types

Type of SQL statement Type
returned
CREATE TABLE employees(. . .)
nothing SELECT FROM employees
rows UPDATE
employees . . .
integer INSERT INTO EMPLOYEES . . .
integer DELETE FROM
employees . . .
integer

• Use executeQuery( ) for statements that return
  rows of data.
• Use executeUpdate( ) for statements that return
  an integer
• or nothing

23
Retrieving Rows

• Some SQL statements return an integer (DELETE,
  UPDATE, and
• INSERT) do not need additional processing
• Other statements, such as SELECT, return rows of
  data. The program must fetch
• each row.
• The executeQuery( ) returns a Result set. This
  set contains the rows that satisfy
• the query.
• ResultSet executeQuery(String sql)
• The method next( ) from the ResultSet is used to
  access the next available row.

24
Retrieving Rows
import java.sql. public class ShowEmployees
public static void main(String args )
String url Connection
myConnection Statement mySql
String empName String empSalary
ResultSet rs try
Class.forName(org.apache.derby.jdbc.ClientDriver
) url jdbcderby//localhost1527/companyDB
createtrueusercop3060passwordallen'
myConnection DriverManager.getConnection(url
) mySql myConnection.createStatement( )
rs mySql.executeQuery(SELECT name, salary FROM
employees) while( rs.next( ) )
empName rs.getString(name)
empSalary rs.getString(salary)
System.out.println(Employee empName
earns empSalary)
catch( Exception ex ) ex.printStackTrace( )

This application retrieves the Names and Salaries
from the employees table
25
Example of ShowEmployees application
26
This application accepts a salary and displays
all the employees with a salary higher than that
amount.
27
import java.awt. import java.awt.event. import
javax.swing. import java.sql. public class
FindEmployeeBySalary extends JFrame
implements
ActionListener private String url
private Connection myConnection
private Statement myStatement
private JTextField salaryMax private
JLabel aLabel private JButton
submit private JTextArea
salaryInfo public FindEmployeeBySalary( )
Container cont getContentPane( )
cont.setLayout( new Layout( ) )
aLabel new JLabel("Salary")
salaryMax new JTextField(10)
cont.add(aLabel) cont.add(salaryMax)
aLabel new JLabel("Employees")
salaryInfo new JTextArea(5,30)
cont.add(aLabel) cont.add(salaryInfo)
submit new JButton("Submit")
submit.addActionListener(this)
cont.add(submit) setSize(400.,400)
setVisible(true)
try Class.forName(org.apache.derb
y.jdbc.ClientDriver) url
jdbcderby//localhost1527/companyDBcreatetrue"

/ usercop3060passwordallen /
myConnection DriverManager.getConnection(url
) myStatement
myConnection.createStatement( )
catch( Exception ex ) ex.printStackTrace
( ) // end FindEmployeeBySalary
constructor public void actionPerformed(Actio
nEvent e) String empName int
empSalary String sql "SELECT name, salary
from employees where salary gt " String max
salaryMax.getText( ) ResultSet result
sql sql max salaryInfo.setText("")
try System.out.println("Executing
statement "sql) result
myStatement.executeQuery(sql) while(
result.next() ) empName result.getString(1)
empSalary result.getInt(2)
salaryInfo.append(empName" "empSalary"\n")
catch(Exception ex) ex.printStackTrace(
)
This application displays all employees who are
above a certain salary
28
Example of FindEmployeeBySalary
29
DERBY
http//db.apache.org/derby

• Relational Database implement in Java
• Open source version of IBM Cloudscape
• Small footprint
• Provides an embedded driver

30
Add Apache Derby nature to the Project
31
Start the Derby Database Server
32
A confirmation message appears
33
To define a database, create an SQL script file.
34
Type in a name
35
A blank editor appears.
36
Enter sql.
37
To execute the script, right-mouse click on file,
choose Apache Derby -gt Run SQL
38
The output appears in the Console view.
39
Lets create a Java application to display all
the rows in the table. Create the file
ShowEmployees.
40
(No Transcript)
41
Enter code.
42
Execute code.