See discussions, stats, and author profiles for this publication at: [Link]

net/publication/358638965

Chapter-7 JDBC (Java Database Connectivity)

Presentation · February 2022

CITATIONS READS

0 1,427

1 author:

Naol Getachew
Mattu University
27 PUBLICATIONS 0 CITATIONS

SEE PROFILE

All content following this page was uploaded by Naol Getachew on 16 February 2022.

The user has requested enhancement of the downloaded file.

1

METTU UNIVERSITY
FACULTY OF ENGINEERING & TECHNOLOGY
DEPARTMENT OF COMPUTER SCIENCE
Java programming course

Chapter-7
Java Database Connectivity (JDBC)

1 By Naol G. 1/26/2022
 Outline
 Introduces database systems
 SQL Queries
 How to develop database based applications using Java.

2 By Naol G. 1/26/2022
 Introduction to Database System
 Database is is an organized collection of structured information, or data,
typically stored electronically in a computer system.
 A database is controlled by a database management system (DBMS).
 Database systems are everywhere.
 If you shop online, your purchase information is stored in a database by the
company.
 If you attend a university, your academic information is stored in a database by
the university.
 Database systems not only store data, they also provide means of
accessing, updating, manipulating, and analyzing data.
 Your academic information is updated periodically
 Database systems play an important role in society and in commerce.

3 By Naol G. 1/26/2022
  Relational Database Systems
 A database system consists of:
 a database, the software that stores and manages data in the Database, and
 the application programs that present data and enable the user to interact with
the database system.
 Most of today’s database systems are relational database systems.
 They are based on the Relational data model, which has three key components:
structure, integrity, and language.
 Structure defines the representation of the data.
 Integrity imposes constraints on the data.
 Language provides the means for accessing and manipulating data.
 The relational model is built around a simple structure called
relation/table that consists of non-duplicate rows and columns.
 Tables are easy to understand and easy to use.

4 By Naol G. 1/26/2022
 System user

Fig1. A database System

5 By Naol G. 1/26/2022
  SQL
 Structured Query Language (SQL) is the language for defining tables and
integrity constraints and for accessing and manipulating data.
 SQL (pronounced “S-Q-L” or “sequel”) is the universal language for accessing
relational database systems.
 Note
 There are many relational database management systems.
 They share the common SQL language but do not all support every
feature of SQL.
 Some systems have their own extensions to SQL.

6 By Naol G. 1/26/2022
  Basic SQL Queries
 Using SQL statements on a database for:
 Creating a table/relation
 Adding or removing an attribute/field
 Updating data/record
 Inserting/populating
 Modifying
 Deleting data
 Reading data/record

7 By Naol G. 1/26/2022
  Creating a Database
 Here are the command to create a database (on mysql):
 CREATE DATABASE dbname;
 Ex.: CREATE DATABASE javabook

 Creating and Dropping Tables

 Tables are the essential objects in a database.
 To create a table, use the create table statement to specify a table name, attributes,
and types, as in the following example:

Creating table – Syntax: Ex.: CREATE TABLE Course (

CREATE TABLE table_name( courseId char(5),
column_name column_data_type, subjectId char(4) not null,
column_name column_data_type, courseNumber integer,
column_name column_data_type title varchar(50) not null,
... numOfCredits integer,
); G. primary key (courseId)
8 By Naol 1/26/2022
);
  Dropping table: If a table is no longer needed, it can be dropped permanently
using below command.
 Syntax: DROP TABLE table_name;
 Ex.: DROP TABLE Course
 Adding an attribute/field:
 Syntax: ALTER TABLE table_name ADD column_name column_data_type;
 Ex.: ALTER TABLE Course ADD Coursename char(30);
 Removing an attribute/field:
 Syntax: ALTER TABLE table_name DROP column_name;
 Ex.: ALTER TABLE Course DROP Coursename;
 Inserting/populating data:
 Syntax: INSERT INTO table_nameVALUES (column1, column2, column3, ...);
 Ex.: INSERT INTO EmployeeVALUES (101, 20.00, ‘Rich’, ‘Raposa’);
or
9
 ByINSERT
Naol G. INTO Employee (number, first, last) VALUES (102, ‘Rich’, ‘Little’); 1/26/2022
  Modifying data:
 Syntax:
 UPDATE table_name SET column_name = value, column_name = value, ... WHERE conditions;
 Ex.: UPDATE Employee SET payRate = 40.00 WHERE number = 101;
 Deleting data
 Syntax: DELETE FROM table_name WHERE conditions;
 Ex.: DELETE FROM Employee WHERE number=101;
 Reading data:
 Syntax:
 SELECT column_name, column_name,.. FROM table_name WHERE conditions;
 Ex.: different methods:
 SELECT first, last, payRate FROM Employee WHERE number = 101;
 SELECT * FROM Employee WHERE number = 101;
 SELECT * FROM Employees WHERE payRate > 10.0 AND payRate <= 20.0;

10 By Naol G. 1/26/2022
 Basic JDBC programming concept
 JDBC is the Java API for developing Java database applications that
supports Java programs that access relational databases.
 The JDBC API is a set of Java interfaces and classes used to write
Java programs for accessing and manipulating relational databases.
 Using the JDBC API, applications written in the Java
programming language can
 execute SQL statements,
 retrieve results,
 present data in a user-friendly interface, and
 propagate changes back to the database.

11 By Naol G. 1/26/2022
  JDBC API uses [Link] package.
 Common JDBC components are:
 [Link] - Manages the loading and unloading of database
drivers from the underlying system.
 [Link] - Handles the connection to a specific database.
 [Link] - Contains an SQL statement to be passed to the
database.
 [Link] - 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.

12 By Naol G. 1/26/2022
  Since a JDBC driver serves as the interface to facilitate
communications between JDBC and a proprietary database,
 JDBC drivers are database specific and are normally provided by the database
vendors.
 You need MySQL JDBC drivers to access the MySQL database, and Oracle
JDBC drivers to access the Oracle database.
 For the MS Access database, use the JDBC-ODBC bridge driver included in
JDK.
 ODBC is a technology developed by Microsoft for accessing databases on the
Windows platform.
 An ODBC driver is preinstalled on Windows.
 The JDBC-ODBC bridge driver allows a Java program to access any ODBC data
source.

13 By Naol G. 1/26/2022
 JDBC consists of two parts:
 The JDBC API:
 a purely Java-based API
 JDBC Driver Manager:
 which communicates with vendor-specific
drivers that interact with the database
translation to vendor format is performed
on the client
 No changes needed to server
 Driver (translator) needed on client

14 By Naol G. 1/26/2022
  What are JDBC drivers?
 A JDBC driver is a software component enabling a Java application to
interact with a database.
 To connect with individual databases, JDBC (the Java Database Connectivity
API) requires drivers for each database.
 The JDBC driver gives out the connection to the database and implements
the protocol for transferring the query and result between client and
database.

15 By Naol G. 1/26/2022
  Types of JDBC drivers:
 JDBC technology drivers fit into one of four categories.
1. JDBC-ODBC bridge
2. Native-API driver
3. Network-Protocol driver (Middleware driver)
4. Thin driver (Pure Java driver) .

16 By Naol G. 1/26/2022
 Type 1 driver – JDBC-ODBC bridge:
 Also known as the JDBC-ODBC bridge, is a database driver implementation that
employs the ODBC driver to connect to the database.
 The driver converts JDBC method calls into ODBC function calls.
 The driver is platform-dependent as it makes use of ODBC depends on native
libraries of the underlying operating system the JVM is running upon.
 Sun (now Oracle) provided a JDBC-ODBC Bridge driver:
 [Link].
 This driver is native code and not Java, and is closed source.

Java application Type-1

ODBC Driver Database
JDBC Driver

17 By Naol G. 1/26/2022
 Cont…
 Advantages
 Almost any database for which an ODBC driver is installed can be accessed,
and data can be retrieved.
 Disadvantages
 Performance overhead:
 since the calls have to go through the JDBC( java database connectivity) bridge to
the ODBC(open database connectivity) driver, then to the native database
connectivity interface (thus may be slower than other types of drivers).
 The ODBC driver needs to be installed on the client machine.
 Specific ODBC drivers are not available on all platforms (only for windows);
 hence, portability of this driver is limited.
 No support from JDK 1.8 (Java 8).

18 By Naol G. 1/26/2022
 Type 2 driver – Native-API driver Java application
 The JDBC type 2 driver, also known as the Native- JDBC
API driver, is a database driver implementation
that uses the client-side libraries of the database.
 The driver converts JDBC method calls into native Type-2
Driver
calls of the database API.
 For example: Oracle OCI driver is a type 2 driver.
 Advantages
 As there is no implementation of JDBC-ODBC Vendor provided
bridge, it may be considerably faster than a Type 1 database Specific
driver. Native API library
 Disadvantages
 The vendor client library needs to be installed on the
client machine.
Database
 This driver is platform + database dependent, since it
uses native library written by C or C++.
19 By Naol G. 1/26/2022
 Type 3 driver – Network-Protocol
Java application
driver (middleware driver) JDBC
 Also known as the Pure Java driver for
database middleware.
 Is a database driver implementation which
makes use of a middle tier between the calling Type-3
program and the database. Driver
 The middle-tier (application server) converts
JDBC calls directly or indirectly into a vendor-
specific database protocol.
 This differs from the type 4 driver in that the Middleware Server
protocol conversion logic resides not at the
client, but in the middle-tier.
 Like type 4 drivers, the type 3 driver is Using Type-1/2/3
written entirely in Java. Driver
 It is platform + database independent as the
differences are taken care of by the Database
middleware.
20 By Naol G. 1/26/2022
  Functions
 Sends JDBC API calls to a middle-tier net server that translates the calls into the DBMS-
specific network protocol. The translated calls are then sent to a particular DBMS.
 The JDBC Client driver written in java, communicates with a middleware-net-server using
a database independent protocol, and then this net server translates this request into
database commands for that database.
 Thus the client driver to middleware communication is database independent.

 Advantages
 Since the communication between client and the middleware server is database
independent, there is no need for the database vendor library on the client.
 The middleware server can provide typical middleware services like caching (of
connections, query results, etc.), logging and auditing.
 A single driver can handle any database, provided the middleware supports it. E.g.: IDA
Server, internet database access server.
 Disadvantages
 Requires database-specific coding to be done in the middle tier.
 The middleware layer added may result in additional latency, but is typically overcome by
21 Byusing better middleware services.
Naol G. 1/26/2022
 Type 4 driver – Thin Driver(Pure Java driver): Java application
 is a database driver implementation that converts JDBC
JDBC calls directly into a vendor-specific database
protocol.
 written completely in Java, thus platform
independent. Type-4
 Unlike the type 3 drivers, it does not need Driver
associated software to work so called thin driver.

Database

22 By Naol G. 1/26/2022
  Advantages
 Completely implemented in Java to achieve platform independence.
 These drivers don't translate the requests into an intermediary format (such
as ODBC).
 The client application connects directly to the database server.
 No translation or middleware layers are used, improving performance.
 The JVM can manage all aspects of the application-to-database connection;
 Disadvantages
 Drivers are database specific, as different database vendors use widely
different (and usually proprietary) network protocols.

23 By Naol G. 1/26/2022
 Simplified JDBC terminology
 Case study: Naol is in Mettu City and wants to buy 10kg of Golds and 5kg of Diamonds
from Yabelo for some reason (e.g.: cheap).
Vehicle:
My requirement
--------------------------------> Yabelo:
Naol in Mettu City:
Mr. Abc:
Vehicle: 10kg Golds
Language translator
Talk English! Result of my req. 5kg Diamonds
<--------------------------------
Road: Talk Afaan Oromo

Where:
Naol in Mettu City: Java Application.
Yabelo: Database , and Golds and Diamonds are records in database.
Mr. Abc (translator) : JDBC Driver Software (eg.: [Link])
Road: Connection
Vehicles: Statement object and My requirement and Results is (SQL queries)

24 By Naol G. 1/26/2022
 Developing Database Applications Using JDBC

 The JDBC API consists of classes and interfaces for establishing

connections with databases, sending SQL statements to databases,
processing the results of the SQL statements, and obtaining
database metadata.
 Four key interfaces are needed to develop any database
application using Java:
 Driver, Connection, Statement, and ResultSet.
 The JDBC interfaces and classes are the building blocks in the
development of Java database programs.
 A typical Java program takes the steps outlined below to access the
database.

25 By Naol G. 1/26/2022
  Steps in using JDBC:
1. Load the JDBC driver
2. Establish the Connection
3. Create a Statement object
4. Execute a query
5. Process the results
6. Close the connection

Note: don’t forget import “[Link].” before all!

26 By Naol G. 1/26/2022
  1. Loading drivers.
 An appropriate driver must be loaded using the statement shown below
before connecting to a database.
 [Link]("JDBCDriverClass");
 A driver is a concrete class that implements the [Link] interface.
 The drivers for Access, MySQL, and Oracle are listed in Table

Database Driver Class Source

Access [Link] Already in JDK

MySQL [Link] Companion website

Oracle [Link] Companion website

 To use the MySQL, we have to add [Link] in the classpath.

 Note: [Link] is a class in [Link]

27 By Naol G. 1/26/2022
  2. Establishing connections.
 To connect to a database, use the static method getConnection(databaseURL)
in the DriverManager class, as follows:
 Connection connection = [Link](databaseURL);
 where databaseURL is the unique identifier of the database on the Internet.
 The Table below lists the URLs for the MySQL, Oracle, and Access databases.

Database URL Pattern

Access jdbc:odbc:DataBaseName
MySQL “jdbc:mysql://hostname:portnumber/dbname”,”username”,password”

Oracle jdbc:oracle:thin:@hostname:port#:oracleDBSID

 N.B : for Mysql on my local computer:

the hostname is localhost
the portnumber is 3306 and
user name is root as a default and no password.
28 By Naol G. 1/26/2022
  3. Creating statements.
 If a Connection object can be envisioned as a cable linking your
program to a database, an object of Statement can be viewed as
a cart that delivers SQL statements for execution by the
database and brings the result back to the program.
 Once a Connection object is created, you can create statements
for executing SQL statements as follows:
 Statement statement = [Link]();

29 By Naol G. 1/26/2022
  4. Executing statements.
 An SQL update statement can be executed using executeUpdate(String sql)
 An SQL query statement can be executed using executeQuery(String sql).
 The result of the query is returned in ResultSet.
 For example, the following code executes the SQL statement create table Temp (col1
char(5), col2 char(5)):
[Link]("create table Temp(col1 char(5), col2 char(5))");

 Below code executes SQL query select firstName, mi, lastName from Student
where lastName = 'Smith':

// Select the columns from the Student table

ResultSet resultSet = [Link] ("SELECT firstName, mi, lastName
FROM Student WHERE lastName “ + " = 'Smith'");
30 By Naol G. 1/26/2022
  5. Processing ResultSet.
 The ResultSet maintains a table whose current row can be retrieved.
 The initial row position is null.
 You can use the next() method to move to the next row and the
various get methods to retrieve values from a current row.
 For example, the code given below displays all the results from the
preceding SQL query.
// Iterate through the result and print the student names
while ([Link]()){
[Link]([Link](1) + " " + [Link](2) + ". "
+[Link](3));
}

31 By Naol G. 1/26/2022
  The getString(1), getString(2), and getString(3) methods
retrieve the column values for firstName, mi, and lastName,
respectively.
 Alternatively, you can use getString("firstName"),
getString("mi"), and getString("lastName") to retrieve
the same three column values.
 The first execution of the next() method sets the current row to
the first row in the result set, and subsequent invocations of the
next() method set the current row to the second row, third row,
and so on, to the last row.

32 By Naol G. 1/26/2022
 Using JDBC Statements
– Via statement objects, SQL statements are sent to the database.
– Three types of statement objects are available:
• Statement
– For executing a multiple SQL query
– But, every time we execute the query, it needs to be re-compiled
– It’s the base interface for all other statements.
• PreparedStatement
– For executing a precompiled SQL statement passing in parameters
– Here, every time we execute the query, it compiled once.
– Performance is best than Statement
• CallableStatement
– For executing a database stored procedure
– If we want to execute sql query stored in given function by calling the procedure.
By Naol G.
33 1/26/2022
 Source code Demos

34 By Naol G. 1/26/2022
Demo-1: Console based

Project-name, class-name, imported MySQL-driver jar file

35 By Naol G. 1/26/2022
 Mysql database, table and data: from Xampp server

36 By Naol G. 1/26/2022
 Output

37 By Naol G. 1/26/2022
Demo-2: to insert data in to database:

38 By Naol G. 1/26/2022
Demo-4: To get data stored in database: Data in the database:
method-2: check getConnection() parameter!

Output of program:

39 By Naol G. 1/26/2022
Demo-3: GUI based

 Case study:
 Simple Student Registration Form to store their data in Mysql
database:
 Use Mysql server to store your data
 Create MVC based JavaFX application for Student Registration Form
 Model: [Link]
 View: [Link]
 Controller: [Link]

40 By Naol G. 1/26/2022
 Model file: [Link]

41 By Naol G. 1/26/2022
 View file: [Link] from SceneBuilder

42 By Naol G. 1/26/2022
 Creating database, table and attributes in MySQL

Creating database: registerDB Creating table: registrationdata

43 By Naol G. 1/26/2022
Adding attributes to the table
 Skeleton the controller file: [Link]

44 By Naol G. 1/26/2022
 Add required steps to use JDBC inside [Link]

45 By Naol G. 1/26/2022
 Now take an input from user to be registered

46 By Naol G. 1/26/2022
 Create insert query, statement, execute query and finally close

47 By Naol G. 1/26/2022
 Running application and register user

48 By Naol G. 1/26/2022
 Result when ‘Register’ button clicked: in MySQL

49 By Naol G. 1/26/2022
  Explore more…

End

50 By Naol G. 1/26/2022
View publication stats