Advanced Programming (ITec3054)

Java Database Connectivity

Tuesday, December 24, 2024 By Melese E., Department of Computer Science 1

JDBC
• A database is an organized collection of data
• A database management system (DBMS) provides mechanisms for storing, organizing, retrieving
and modifying data for many users
• Today’s most popular database systems are relational databases
• Some popular proprietary relational database management systems (RDBMSs) are
• Microsoft SQL Server, Oracle, Sybase and IBM DB2, PostgreSQL, MariaDB and MySQL
• Structured Query Language (SQL)
• A language called SQL—pronounced “sequel,” or as its individual letters—is the international standard
language used almost universally with relational databases to perform queries and to manipulate data
• Java programs interact with databases using the Java Database Connectivity (JDBC™) API
• A JDBC driver enables Java applications to connect to a database in a particular DBMS and allows
you to manipulate that database using the JDBC API
• Most popular database management systems provide JDBC drivers
• The JDBC API is portable—the same code can manipulate databases in various RDBMSs
• In this course we will use MySQL; but same code can be used to work with other RDBMSs
• Driver configuration is also same except the driver file

Tuesday, December 24, 2024 By Melese E., Department of Computer Science 2

JDBC – JDBC Driver
• The JDBC driver builds a bridge between your Java applications and your desired
database, and works as an intermediate-level translator to perform a bidirectional
conversion
• convert your high-level Java codes to the low-level native codes to interface to the database, and
• convert the low-level native commands from the database to your high-level Java codes
• The JDBC API will not contain any JDBC driver
• As a result, you need to download a desired JDBC driver from the corresponding vendor
• Store the downloaded driver in a directory of your choice
• Then configure environment variable for the driver – so that Java applications know where the driver
is – for MySQL on windows
• set CLASSPATH="C:\mysql-connector-j-8.4.0\[Link]";%CLASSPATH%
• Alternatively; go to computer properties -> Advanced System Setting -> Environment Variables -> Then add the
path under system variables
• Then when running the application using command line, use the following
• java -classpath "%CLASSPATH%"; DBConnection
• Where DBConnection is the name of the byte code file that tries to connect to the database
• If you want to configure an IDE such as Netbeans
• Add the [Link] into your IDE then configure your projects library to include the
driver

Tuesday, December 24, 2024 By Melese E., Department of Computer Science 3

JDBC – Connecting to a Database
• After successful configuration of the JDBC Driver – (setting up the classpath)
• Then you can write a Java code to connect to the database
• If you are using a driver that do not support automatic loading of the driver class, you
need to load the driver before attempting to connect to the database. Use either
• [Link]() method or
• [Link]() method
• Using the [Link]() method
• [Link](“[Link]”); where [Link] is the name of the
MySQL driver
• Using the [Link]() method
• [Link](new [Link]());
• If either of the two fails, you have to check the classpath configuration or the name of
the Driver
• Older versions of the driver may use a different name
• This loading step is not necessary if you are working with the latest JDBC driver of your
choice – if the following file exists in the JAR file, you need not to load the driver class
• META-INF/services/[Link] – to check if it exists, extract the JAR and search for it

Tuesday, December 24, 2024 By Melese E., Department of Computer Science 4

JDBC – Connecting to a Database
• The following are the driver class names of some of the DBMSs

DBMS Driver Class Name

DB2 [Link].DB2Driver
Microsoft SQL Server [Link]
Oracle [Link]
PostgreSQL [Link]
MySQL [Link]

Tuesday, December 24, 2024 By Melese E., Department of Computer Science 5

JDBC – Connecting to a Database
• The following simple steps can be used to connect to a database after
getting connected to the DBMS (either manually or through automatic
loading if supported)
• Import the classes as import [Link].*; or import each class one by one
• The classes for working with JDBC are found in the [Link] package
• Specify the database URL, username and password
String dbURL = "jdbc:mysql://localhost:3306/database_name"; //a different URL is used for a
different DBMS
String username = "username";
String password = "password";
• Then try to connect to the database using the DriverManager class
• Connection conn = [Link](dbURL, username, password);
• The connection object has to be closed when you finished with it
• void close() – call this method to do so
• Most of the JDBC methods throw SQLException – you need to handle it
Tuesday, December 24, 2024 By Melese E., Department of Computer Science 6
JDBC – Connecting to a Database
• The URL format for some of the DBMSs

Tuesday, December 24, 2024 By Melese E., Department of Computer Science 7

JDBC – Connecting to a Database
import [Link]; password);
import [Link]; } catch (SQLException e) {
import [Link]; [Link]([Link]());
import [Link]; } finally {
public class JDBCDemo { try {
public static void main(String[] args) { if (conn != null) {
String dbURL = [Link](); //to close the connection
"jdbc:mysql://localhost:3306/human_resource"; }
String username = "root";
} catch (SQLException e) {
String password = "root"; }
Connection conn = null; }
try {
}
conn = }
[Link](dbURL, username,

Tuesday, December 24, 2024 By Melese E., Department of Computer Science 8

JDBC – Connecting to a Database
• The core JDBC classes and interfaces are
• DriverManager – used to access the JDBC drivers and create database connection
• Connection – is an interface that represents a database connection – an object of a
Connection class is created by [Link]() method upon
successfully connecting to the database
• Statement
• PreparedStatement
• CallableStatement
• ResultSet
• RowSet

Tuesday, December 24, 2024 By Melese E., Department of Computer Science 9

JDBC – Connecting to a Database
• Summary of Steps to Connect and Work with Databases
1. Download the driver for your DBMS
2. Configure the classpath that stores the path to the driver jar file
• Or create environment variable
3. Load the class – optional
• [Link](“nameoftheclass”); or
• [Link](anObjectOftheClass);
4. Connect to the database and get the connection object
• Connection con = [Link](dbURL, uname, password);
5. Use this connection object to create a Statement object to execute statements
• Statement stmt = [Link]();
6. Use the methods of the Statement object to execute SQL statements – you can
execute both
• DML (insert, delete, update, and select statements)
• DDL (create, alter, drop statements)

Tuesday, December 24, 2024 By Melese E., Department of Computer Science 10

JDBC – Executing SQL Statements
• After getting connected to the database
• You can execute any SQL statements (the DDL or the DML) against the database
• Java JDBC library provides the following classes for the purpose of executing SQL
statements
• Statement – used to execute SQL statements and queries, which produce ResultSet objects
– Statement object is created by the Connection object
• PreparedStatement – used to execute parametrized statement – PreparedStatement object
is created by the Connection object
• CallableStatement – used to execute stored procedures – CallableStatement object is
created by the Connection object
• The result of the execution of an SQL statement may be
• A count of the affected rows or tables – is the case of insert, update, and delete statements
• A set of rows – in the case of a select statement
• ResultSet class – is used to store the result of a select statement
• There are different types of result sets

Tuesday, December 24, 2024 By Melese E., Department of Computer Science 11

JDBC – Executing SQL Statements – Data Types
• Which datatype of SQL matches to which datatype of Java and vice versa?
SQL Type Java Type SQL Type Java Type
BINARY byte[] DECIMAL [Link]
BIT boolean NUMERIC [Link]
DATE [Link] VARBINARY byte[]
TIME [Link] LONGVARBINARY byte[]
TIMESTAMP [Link] VARCHAR String
FLOAT double LONGVARCHAR String
DOUBLE double CHAR String
REAL float
TINYINT byte
SMALLINT short
BIGINT long
INTEGER int

Tuesday, December 24, 2024 By Melese E., Department of Computer Science 12

JDBC – Using Statement class
• For the examples used in this chapter, assume we have a database called
human_resource
• In this database, assume there is a table called employee

• And assume, the following variables are part of our program

String dbURL = "jdbc:mysql://localhost:3306/human_resource";
String username = "root";
String password = "root";

Tuesday, December 24, 2024 By Melese E., Department of Computer Science 13

JDBC – Using Statement class
• To get an instance of the Statement class, call the createStatement() method of
the Connection object
Connection conn = [Link](dbURL, username, password);
Statement stmt = [Link]();
• Then you can use the Statement object (stmt) to execute SQL statements
• The following are the important methods provided by Statement
• boolean execute(String sql) – used to execute any type of SQL statements starting from DML
to DDL
• Returns true if the execution succeeds
• You need to use the int getUpdateCount() or ResultSet getResultSet() depending on the type of
statement executed – these are also the methods of the Statement object
• ResultSet executeQuery(String sql) – used to execute select statement
• Returns a ResultSet object that contains the rows of the result of the select statement
• int executeUpdate(String sql) – used to execute any statement except select statement
• Return the count of items affected
• boolean isClosed()
• void close()

Tuesday, December 24, 2024 By Melese E., Department of Computer Science 14

JDBC – Using Statement class – Inserting into Table
• For insert, delete and update operations you can use the Statement class’
• execute(String query) method or
• executeUpdate(String query) method – this one is recommended
• The following code fragment shows how
try{
Connection con = [Link](dbURL, username, password);
String q = "INSERT INTO employee " +
"(emp_id, emp_name, father_name, gfather_name, age, salary, job_title) " +
" VALUES ('wdu4147', 'John', 'Belay', 'Demeke', 33, 7605.8, 'Facilitator');";
Statement stmt = [Link]();
[Link](q);
}catch(SQLException e){
[Link]([Link]());
}

Tuesday, December 24, 2024 By Melese E., Department of Computer Science 15

JDBC – Using Statement class – Updating Table Data
• The following code fragment shows how to update a row of data
try{
Connection con = [Link](dbURL, username, password);
String q = "UPDATE employee SET salary = 1200.45 WHERE (emp_id = 'wdu3467');";
Statement stmt = [Link]();
[Link](q);
}catch(SQLException e){
[Link]([Link]());
}

Tuesday, December 24, 2024 By Melese E., Department of Computer Science 16

JDBC – Using Statement class – Deleting Rows
• The following code fragment shows how to delete a row
try{
Connection con = [Link](dbURL, username, password);
String q = "DELETE FROM employee WHERE (emp_id = 'wdu3434');";
Statement stmt = [Link]();
[Link](q);
}catch(SQLException e){
[Link]([Link]());
}

Tuesday, December 24, 2024 By Melese E., Department of Computer Science 17

JDBC – Using Statement class – Selecting Set of Rows
• The executeQuery(String query) method of the Statement object is used to select
rows from the table
• The result of a select statement is a table data represented by the ResultSet
object
• The ResultSet object has the following methods
• boolean next() – makes the current row pointer in the result set move forward by one.
• Returns false after the last row.
• Note that you must call this method to advance to the first row since the pointer is before the first
row by default
• Xxx getXxx(int columnNumber)
• Xxx getXxx(String columnLabel)
• These two methods are used to get the data of a column specified by either column number or
column label where Xxx is a type such as String, int, long, Date etc
• For example if the column contains a string and if the column name is fname
• String fn = getString(“fname”);
• int findColumn(String columnName) – returns the column index of the specified column
name – column index begins with 1
• void close()
• boolean isClosed()
Tuesday, December 24, 2024 By Melese E., Department of Computer Science 18
JDBC – Using Statement class – Selecting Set of Rows
• The following code fragment shows how to select all rows and columns
from a table
try{
Connection con = [Link](dbURL, username, password);
String query = "SELECT * FROM employee;";
Statement stmt = [Link]();
ResultSet rs = [Link](query);
}catch(SQLException e){
[Link]([Link]());
}
• The executeQuery() method returns the result as a ResultSet object
• Use this result set object to manipulate (for example, display the result)

Tuesday, December 24, 2024 By Melese E., Department of Computer Science 19

JDBC – Using Statement class – Selecting Set of Rows
• To display the content of the result set object
• First Step: make the result set pointer on the firs row
• [Link](); - this will return false if it reaches the end of the result set
• Second Step: Then use the get methods
• String id = [Link](1); - since emp_id is varchar, use getString() – you may use column index
or name
• String firstName = [Link](2);
• String fatherName = [Link](3);
• String grandFatherName = [Link](4);
• int age = [Link](5); - since age is specified as integer in the table
• double salary = [Link](6); - since salary is specified as double in the table
• String jobTitle = [Link](7);
• Repeatedly perform step one and two until the next() method returns false
• It is recommended to use a loop

Tuesday, December 24, 2024 By Melese E., Department of Computer Science 20

JDBC – Using Statement class – Selecting Set of Rows
• Same result can be achieved using column names – but be careful not to
mistype column names
• Using the get methods with column names
• String id = [Link]("emp_id"); - since emp_id is varchar use getString() – you may use
column index or name
• String firstName = [Link]("emp_name");
• String fatherName = [Link]("father_name");
• String grandFatherName = [Link]("gfather_name");
• int age = [Link]("age"); - since age is specified as integer in the table
• double salary = [Link]("salary"); - since salary is specified as double in the table
• String jobTitle = [Link]("job_title");

Tuesday, December 24, 2024 By Melese E., Department of Computer Science 21

JDBC – Using Statement class – Selecting Set of Rows
• The following code fragment shows how to use a loop to display the content of the
result set
try{
Connection con = [Link](dbURL, username, password);
String query = "SELECT * FROM employee;";
Statement stmt = [Link]();
ResultSet rs = [Link](query);
while([Link]()){
[Link]([Link](1) + "\t");
[Link]([Link](2) + "\t");
[Link]([Link](3) + "\t");
[Link]([Link](4) + "\t");
[Link]([Link](5) + "\t");
[Link]([Link](6) + "\t");
[Link]([Link](7) + "\t");
}
}catch(SQLException e){
[Link]([Link]());
}

Tuesday, December 24, 2024 By Melese E., Department of Computer Science 22

JDBC – Using Statement class – Selecting Set of Rows
• The full program that retrieves all the rows while([Link]()){

import [Link].*; [Link]([Link](1) + "\t");

public class JDBCOps { [Link]([Link](2) + "\t");
public static void main(String[] args) { [Link]([Link](3) + "\t");

String dbURL = [Link]([Link](4) + "\t");

"jdbc:mysql://localhost:3306/human_resource"; [Link]([Link](5) + "\t");
String username = "root"; [Link]([Link](6) + "\t");
String password = "root"; [Link]([Link](7) + "\t");
Connection con = null; }
try{ }catch(SQLException e){
con = [Link](dbURL, [Link]([Link]());
username, password);
}
String query = "SELECT * FROM employee;";
}
Statement stmt = [Link]();
}
ResultSet rs = [Link](query);
Tuesday, December 24, 2024 By Melese E., Department of Computer Science 23
JDBC – ResultSet Meta Data
• Sometimes you may encounter yourself interacting with a table that you don’t
know the details about the number of columns, names of the columns and the
data type of each column
• For such situations, Java provides us a class called ResultSetMetaData
• Use the result set method getMetaData() method to retrieve the meta data
• ResultSetMetaData rsmd = [Link]();
• The ResultSetMetaData object has the following methods among others
• int getColumnCount() – returns the number of columns in the table
• String getColumnName(int columnIndex) – returns the column name
• int getColumnType(int columnIndex) – returns the SQL data type index
• String getColumnTypeName(int columnIndex) - returns the SQL data type name
• int getColumnDisplaySize(int columnIndex) – returns the max number of characters
• boolean isAutoIncrement(int columnIndex) – returns true if the column is set to auto
• int isNullable(int columnIndex) – returns 0 if the column cannot be assigned null
Tuesday, December 24, 2024 By Melese E., Department of Computer Science 24
JDBC – ResultSet Meta Data
• The following code fragment shows how to use ResultSetMetaData
try{
Connection con = [Link](dbURL, username, password);
String query = "SELECT * FROM employee;";
Statement stmt = [Link]();
ResultSet rs = [Link](query);
ResultSetMetaData rsmd = [Link]();
[Link]([Link]());
[Link]([Link](1));
[Link]([Link](1));
[Link]([Link](1));
[Link]([Link](1));
[Link]([Link](1)+ " “ + [Link](1));
}catch(SQLException e){
[Link]([Link]());
}
Tuesday, December 24, 2024 By Melese E., Department of Computer Science 25
 The End!

Tuesday, December 24, 2024 By Melese E., Department of Computer Science 26