SE241: Advanced Computer

Programming
Lecture # 12: JDBC

Muhammad Imran
(Based on Java, The Complete Reference)
http://www.secscourses.tk

1
 Outline
• Database and DBMS
• Relational Databases
• JDBC
• What Does JDBC Do?
• Case Study: The Books Database
• The DBConnect Class
• Login Database Example
• Store Database Example
2
2
 Database and DBMS

• A database is an organized collection of data.

• There are many different strategies for organizing

data to facilitate easy access and manipulation.

• Database Management System (DBMS)

 A DBMS provides mechanisms for storing, organizing,
retrieving and modifying data.

3
3
 Relational Databases
• Most popular DBMSs are relational databases
 A relational database stores data in tables
 Tables are composed of rows, and rows are composed of columns in
which values are stored
 Primary key: a column (or group of columns) in a table with a unique
value that cannot be duplicated in other rows
• SQL (sequel) is the international standard language used with
relational databases to perform queries
• Some popular RDBMSs: Microsoft Access, Microsoft SQL
Server, Oracle, and MySQL
• In this course, we will use Microsoft Access 4
4
 JDBC
• JDBC (Java Database Connectivity) is a Java API
(Application Programming Interface) used for executing
SQL statements from within a Java program.

5
5
 What Does JDBC Do?
• JDBC makes it possible to do three things:

 establish a connection with a database

 send SQL statements

 process the results

6
6
 Case Study: The Books Database
• The database consists of four tables

Table Relationships in Books 7

7
 Case Study: The Books Database
• authors table
 authorID
• An autoincremented field (primary key)
 firstName
• Author’s first name (a string)
 lastName
• Author’s last name (a string)

8
8
 Case Study: The Books Database
• publishers table
 publisherID
• An autoincremented integer (primary-key)
 publisherName
• The name of the publisher (a string)

9
9
Case Study: The Books Database
• titles table
 Isbn
• ISBN number of the book (a string)
 Title
• Title of the book (a string)
 editionNumber
• Edition number of the book (an integer)
 Copyright
• Copyright year of the book (a string)
 publisherID
• Publisher’s ID number (an integer). This value must correspond to an ID
number in the publishers table (Foreign Key).
 imageFile
• Name of the file containing the book’s cover image (a string)
 Price
• Suggested retail price of the book (a real number)
10
10
Case Study: The Books Database

11
11
Case Study: The Books Database
• authorISBN table
 authorID
• The author’s ID
number, which
allows the
database to
associate each
book with a
specific author.
(foreign key)
 isbn
• The ISBN number
for a book (a
string), foreign
key

12
12
 The DBConnect Class
• The DBConnect class is being written for students/beginners
who want to create database applications in Java
• It encapsulates some basic database connectivity
functionalities
 Connection to a Microsoft Access database (*.mdb)
 Creation of java.sql.Statement object
 Disconnection

13
13
 The DBConnect Class
import java.sql.*;
class DBConnect {
private static Connection connection;
private static Statement statement;
public static void getDBConnection(String path) {
try{
System.out.println("---------------\nRequesting Connection... ");
/* Load class definition for database driver */
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
/* static method getConnection attempts connection to database*/
connection = DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access
Driver (*.mdb)};DBQ="+path);
statement = connection.createStatement();
System.out.println("Connection Established...\n--------------- ");
}

14
14
 The DBConnect Class
catch(SQLException e)
{/*getConnection and createStatement methods throw
java.sql.SQLException*/
System.out.println(e);
e.printStackTrace();
}
catch(ClassNotFoundException e)
{ /*Class.forName method throws java.lang.ClassNotFoundException*/
System.out.println(e);
e.printStackTrace();
}
}
public static Statement getStatement() {
return statement;
}
15
15
 The DBConnect Class
public static void disconnectDB()
{
try
{
connection.close();
System.out.println("---------------\nConnection Closed\n---------------");
}
catch(SQLException e)
{
/*connection.close method throws java.sql.SQLException*/
System.out.println(e);
e.printStackTrace();
}
}
}

16
16
The Books Database: Console Example

• An Example Program

 A program which establishes connection with the books

database using DBConnect class, sends a select query to the

authors table, and displays the results of query onto the

standard output.

17
17
The Books Database: Console Example

import java.sql.Statement;
import java.sql.ResultSet;
public class AuthorManager {

public void viewAuthors() {

int col1Value = 0;
String col2Value = null;
String col3Value = null;
/* Creating object of the class DBConnect */
DBConnect dbConnect = new DBConnect();
/* Calling the method getDBConnection of the DBConnect class */
dbConnect.getDBConnection("books.mdb");
/* Calling the getter method to get the statement object of
the DBConnect class */
Statement statement = dbConnect.getStatement(); 18
18
The Books Database: Console Example
String query = "SELECT * FROM authors";
try
{
ResultSet resultSet = statement.executeQuery(query);
while (resultSet.next()) {
col1Value = resultSet.getInt("authorID");
col2Value = resultSet.getString("firstName");
col3Value = resultSet.getString("lastName");
/* Printing the data on the console */
System.out.println(col1Value+ " "+col2Value+" "+col3Value);
} //end of while
}//end of try
catch(Exception exception){
System.out.println("Exception while executing the Query: "+exception);}
finally { dbConnect.disconnectDB(); }
}//end of viewAuthors
19
19
The Books Database: Console Example
public static void main(String[] args)
{
AuthorManager manager = new AuthorManager();
manager.viewAuthors();

} //end of the main

}//end of the class
Requesting
Connection...
Connection
Established...
---------------
1 Harvey Deitel
2 Paul Deitel
3 Tem Nieto
4 Sean Santry 20
---------------
20
Login Database: Console Example

• Another Example

 A program which establishes connection with the login database

using DBConnect class, sends a select query to the Login table

with a user provided login and password to validate user login

and password.

21
21
Login Database: Console Example
import java.sql.*;
public class LoginManager {
public static void main(String[] args) {
boolean validUser = false;
ConsoleReader myConsole = new ConsoleReader(System.in);

System.out.print("Enter Login: ");

String login = myConsole.readLine();

System.out.print("Enter Password: ");

String password = myConsole.readLine();

String query = "SELECT * FROM LoginTable WHERE LoginName='"+login+"' AND

Password='"+password+"'";

22
22
Login Database: Console Example
DBConnect dbConnectObj = new DBConnect();
dbConnectObj.getDBConnection("LoginDB.mdb");
Statement statement = dbConnectObj.getStatement();
try{
ResultSet rs = statement.executeQuery(query);
if(rs.next()){
validUser = true;
} //end while
}//end of try
catch(Exception exp){
validUser = false;
System.out.println("Exception: "+exp.toString());
}//end of catch
finally{
dbConnectObj.disconnectDB();
}
23
23
Login Database: Console Example
if(validUser){
System.out.println("You are a member. ");
}//end if
else{
System.out.println("You are not a member. ");
}//end else

}//end of main
}//end of the class
Enter Login: abc
Enter Password: abc
---------------
Requesting
Connection...
Connection
Established...
---------------
--------------- 24
Connection Closed
24
 Store Database: Console Example
• Example
 A program which establishes connection with the store database
using DBConnect class, sends a select query to the Item table,
and displays the results of query on the standard output.
 The output from this program includes column headings of Item
table.

25
25
Store Database: Console Example
import java.sql.*;
class ItemManager {
public static void main(String[] args) {
DBConnect dbconnect = new DBConnect();
dbconnect.getDBConnection("StoreDB.mdb");
Statement statement = dbconnect.getStatement();
String query = "SELECT * FROM ItemTable";
try{
ResultSet results = statement.executeQuery(query);
ResultSetMetaData rsmetadata = results.getMetaData();
int colcount = rsmetadata.getColumnCount();
for(int i=1; i<=colcount; i++)
{/*displaying col. names*/
System.out.print(rsmetadata.getColumnName(i)+"\t");
}
System.out.println(); 26
26
Store Database: Console Example
while(results.next()){
for(int i=1; i<=rsmetadata.getColumnCount();i++){
System.out.print(results.getString(i)+"\t");
}
System.out.println();
}
}//end of try
catch(SQLException e) {
/*statement.executeQuery method throws java.sql.SQLException*/
System.out.println(e);
e.printStackTrace();
}
finally{ dbconnect.disconnectDB(); }
}
}

27
27
Store Database: Console Example
---------------
Requesting Connection...
Connection Established...
---------------
ItemID Name NoOfItems UnitPrice Description ImgPath
G100 T-Shirt 110 275.5 dfgh c:\images\tshirt.jpg
G101 Shirt 50 599.99 dfgh dfgh c:\images\shirt.jpg
S200 Black Shoes 15 1500.0 hjkl jkl kl c:\images\shoe.jpg

S201 Joggers 15 2500.0 asdfghj asdfghjkl c:\images\jogger.jpg

B300 Lux 100 20.0 abcd abcd c:\images\lux.jpg

B301 Capri 100 20.0 asdf asdf asdf c:\images\capri.jpg
B302 Safe Guard 75 20.0 xyz xyz xyz xy c:\images\soap.jpg

B303 Max Bar 50 25.0 qwer qwer qwer c:\images\max.jpg

---------------
Connection Closed
---------------
28
28
 Recommended Reading
• Chapter # 28: Accessing Databases with JDBC from Deitel
& Deitel, Java: How to Program, 9th Edition

29
29