Database Management System

18CS53

1
Course objectives: This course will enable students to
1. Provide a strong foundation in database concepts, technology, and
practice.
2. Practice SQL programming through a variety of database problems.
3. Demonstrate the use of concurrency and transactions in database
4. Design and build database applications for real world problems.

Course Outcome
Course Code Course Outcome

C303.1 Identify, analyze and define database objects, enforce integrity

constraints on a database using RDBMS.
C303.2 Use Structured Query Language (SQL) for database manipulation.

C303.3 Design and build simple database systems

C303.4 Apply the concepts of Normalization and design database which

possess no anomalies.
C303.5 Develop application to interact with databases

2
Textbooks:
1. Fundamentals of Database Systems, Ramez Elmasri and
Shamkant B. Navathe, 7th Edition, 2017,Pearson.
2. Database management systems, Ramakrishnan, and Gehrke,
3rd Edition, 2014, McGraw Hill
Reference Books:
1. Silberschatz Korth and Sudharshan, Database System
Concepts, 6th Edition, Mc-GrawHill, 2013.
2. Coronel, Morris, and Rob, Database Principles Fundamentals
of Design, Implementation and Management, Cengage
Learning 2012.

3
 Prerequisite to learn
JDBC CLASSES AND INTERFACES
Students Should have the prior knowledge in
1. Basics of Java Programming language
2. DDL,DML Statement

4
JDBC CLASSES AND INTERFACES

5
 JDBC CLASSES AND INTERFACES

• JDBC is a collection of Java classes and interfaces that enables

database access from programs written in the Java language.
• It contains methods for connecting to a remote data source,
executing SQL statements, examining sets of results from SQL
statements, transaction management, and exception handling.
• The classes and interfaces are part of the java.sql package.
• Thus, all code fragments in the remainder of this section
should include the statement import java. sql .* at the
beginning of the code;

6
 Topics to be covered under JDBC Classes and
Interfaces

• JDBC Driver Management

• Connections
• Executing SQL Statements
• ResultSets
• Exceptions and Warnings
• Examining Database Metadata

7
Steps for JDBC Connectivity

8
 JDBC Classes and Interfaces
Steps to submit a database query:
1.Load the JDBC driver: Driver helps to make a connection to the database
hence driver must be loaded once in the program.
Class.forName()- By using this, the driver’s class file is loaded in the
memory during run time.Ex.
Class.forName(“oracle.jdbc.driver.OracleDriver”);
DriverManager.registerDriver()-DriverManager is an inbuilt Java class
where the register is its static member.
Ex.:DriverManager.registerDriver(new
oracle.jdbd.driver.OracleDriver());
2.Connect to the data source. -The connection object uses username,
password, and URL to set up the connection.
Connection con = DriverManager.getConnection(URL, user,
password);

9
 Continue…
3.Creating Statement- The interfaces such as JDBC statement,
PreparedStatement, CallableStatement provides methods that allow a user
to send SQL statements and get data from the database
Ex. Statement stmt = con.createStatement();
4.Execute SQL statements- The SQL query is executed to interact with the
database. A query can be for updating/inserting in the database or for
retrieving data
Ex.
int n = stmt.executeUpdate(“DELETE TABLENAME”);
if(n==1)
System.out.println(“Success”);
else
System.out.println(“Failed”);
5.Closing Connection- The connection interface provides a method close() to
close the connection. Ex. con.close();

10
 1.JDBC Driver Management

• All drivers are managed by the DriverManager class

• Loading a JDBC driver:
– In the Java code:
Class.forName(“oracle/jdbc.driver.Oracledriver”);
– When starting the Java application:
-Djdbc.drivers=oracle/jdbc.driver

11
 Contd…

• In JDBC, data source drivers are managed by the

Drivermanager class, which maintains a list of all currently
loaded drivers.
• The Drivermanager class has methods registerDriver,
deregisterDriver, and
• getDrivers to enable dynamic addition and deletion of drivers.
• The first step in connecting to a data source is to load the
corresponding JDBC driver.
• This is accomplished by using the Java mechanism for
dynamically loading classes.

12
 Contd…

• The static method forName in the Class class returns the Java
class as specified in the argument string and executes its static
constructor.
• The static constructor of the dynamically loaded class loads an
instance of the Driver class, and this Driver object registers
itself with the DriverM
• anager class.
• The following Java example code explicitly loads a JDBC
driver:
• Class.forName("oracle/jdbc.driver.OracleDriver");

13
 2.Connections

• A session with a data source is started through creation of a

Connection object;
• A connection identifies a logical session with a data source;
multiple connections within the same Java program can refer
to different data sources or the same data source.
• Connections are specified through a JDBC URL, a URL that
uses the jdbc protocol.
• Such a URL has the form
• jdbc:<subprotocol>:<otherParameters>

14
Example:
String url=“jdbc:oracle:www.bookstore.com:3083”;
Connection con;
try{
con = DriverManager.getConnection(url,usedId,password);
} catch SQLException excpt { …}

15
 Connection Class Interface

• public int getTransactionIsolation() and

void setTransactionIsolation(int level)
Gets/Sets isolation level for the current connection.
-TRANSACTION_NONE
-TRANSACTION_READ_UNCOMMITTED
-TRANSACTION_READ_COMMITTED
-TRANSACTION_REPEATABLE_READ
-TRANSACTION_SERIALIZABLE
• public boolean getReadOnly() and
void setReadOnly(boolean b)
Specifies if transactions in this connection are read-only

16
 Connection Class Interface

• public boolean getAutoCommit() and

void setAutoCommit(boolean b)
If autocommit is set, then each SQL statement is considered its
own transaction. Otherwise, a transaction is committed using
commit(), or aborted using rollback().

• public boolean isClosed()

Checks whether connection is still open.

17
 3.Executing SQL Statements

• Three different ways of executing SQL statements:

– Statement (both static and dynamic SQL statements)
– PreparedStatement (semi-static SQL statements)
– CallableStatment (stored procedures)

• PreparedStatement Class:
Precompiled, parametrized SQL statements:
– Structure is fixed
– Values of parameters are determined at run-time

18
 Executing SQL Statements (Contd.)

String sql=“INSERT INTO Sailors VALUES(?,?,?,?)”;

PreparedStatment pstmt=con.prepareStatement(sql);
pstmt.clearParameters();
pstmt.setInt(1,sid);
pstmt.setString(2,sname);
pstmt.setInt(3, rating);
pstmt.setFloat(4,age);

// we know that no rows are returned, thus we use executeUpdate()

int numRows = pstmt.executeUpdate();

19
 4.ResultSets

• PreparedStatement.executeUpdate only returns the number of

affected records
• PreparedStatement.executeQuery returns data, encapsulated in
a ResultSet object (a cursor)

ResultSet rs=pstmt.executeQuery(sql);
// rs is now a cursor
While (rs.next()) {
// process the data
}

20
 ResultSets (Contd.)

A ResultSet is a very powerful cursor:

• previous(): moves one row back
• absolute(int num): moves to the row with the specified
number
• relative (int num): moves forward or backward
• first() and last()

21
 Matching Java and SQL Data Types
SQL Type Java class ResultSet get method
BIT Boolean getBoolean()
CHAR String getString()
VARCHAR String getString()
DOUBLE Double getDouble()
FLOAT Double getDouble()
INTEGER Integer getInt()
REAL Double getFloat()
DATE java.sql.Date getDate()
TIME java.sql.Time getTime()
TIMESTAMP java.sql.TimeStamp getTimestamp()

Table 1: Matching Java and SQL Data Types

22
ResultSet rs=stmt.executeQuery(sqIQuery);
String sqlQuerYi
ResultSet rs = stmt.executeQuery(sqIQuery) while (rs.nextO)
{
isbn = rs.getString(l);
title = rs.getString(" TITLE");
/ / process isbn and title
}

23
 5.Exceptions and Warnings

• Most of the methods in java.sql can throw an exception of the

type SQLException if an error occurs.
• The information includes SQLState, a string that describes the
error.
• (e.g., whether the statement contained an SQL syntax error). In
addition to the standard getMessage() method inherited from
Throwable, SQLException has two additional methods that
provide further information, and a method to get (or chain)
additional exceptions:

24
• public String getSQLState 0 returns an SQLState identifier based on
the SQL: 1999 specifications
• public int getErrorCode () retrieves a vendor-specific error code.
• public SQLException getNextExceptionO gets the next exception in
a chain of exceptions associated with the current SQLException
object.

25
try {
stmt = con.createStatement();
warning = con.getWarnings();
while( warning != null) {
/ / handleSQLWarnings / / code to process warning
warning = warning.getNextWarningO; / /get next warning
}
con.clear\Varnings() ;
stmt.executeUpdate( queryString );

26
warning = stmt.getWarnings();
while( warning != null) {
/ / handleSQLWarnings / / code to process warning
warning = warning.getNextWarningO; / /get next warning
}
} / / end try
catch ( SQLException SQLe) {
/ / code to handle exception
} / / end catch

27
 6.Examining Database Metadata

DatabaseMetaData object gives information about the database

system and the catalog.

DatabaseMetaData md = con.getMetaData();
System.out.println("Driver Information:");
// print information about the driver:
System.out.println("Name:" + md.getDriverName() + "; version:"
+ mcl.getDriverVersion());

28
 Database Metadata (Contd.)
DatabaseMetaData md=con.getMetaData();
ResultSet trs=md.getTables(null,null,null,null);
String tableName;
While(trs.next()) {
tableName = trs.getString(“TABLE_NAME”);
System.out.println(“Table: “ + tableName);
//print all attributes
ResultSet crs = md.getColumns(null,null,tableName, null);
while (crs.next()) {
System.out.println(crs.getString(“COLUMN_NAME” + “, “);
}
}

29
 A (Semi-)Complete Example
import java.sql.*;
/*
* This is a sample program with jdbc odbc Driver
*/
public class localdemo {
public static void main(String[] args) {
try {
// Register JDBC/ODBC Driver in jdbc DriverManager
// On some platforms with some java VMs, newInstance() is
necessary...
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
// Test with MS Access database (sailors ODBC data source)
String url = "jdbc:odbc:mysailors";
java.sql.Connection c = DriverManager.getConnection(url);

30
 A (Semi-)Complete Example cont
java.sql.Statement st = c.createStatement();
java.sql.ResultSet rs = st.executeQuery("select * from Sailors");
java.sql.ResultSetMetaData md = rs.getMetaData();
while(rs.next()) {
System.out.print("\nTUPLE: | ");
for(int i=1; i<= md.getColumnCount(); i++) {
System.out.print(rs.getString(i) + " | ");
}
}
rs.close();
} catch(Exception e) {
e.printStackTrace();
}
}
};

31
• Which of the following is advantage of using
PreparedStatement in Java?
a) Slow performance
b) Encourages SQL injection
c) Prevents SQL injection
d) More memory usage

32
• Which one of the following contains date information?
a) java.sql.TimeStamp
b) java.sql.Time
c) java.io.Time
d) java.io.TimeStamp
• Which of the following is used to call stored procedure?
a) Statement
b) PreparedStatement
c) CallableStatment
d) CalledStatement

33
Which of the following is used to rollback a JDBC transaction?
a) rollback()
b) rollforward()
c) deleteTransaction()
d) RemoveTransaction()
Which of the following is not a JDBC connection isolation
levels?
a) TRANSACTION_NONE
b) TRANSACTION_READ_COMMITTED
c) TRANSACTION_REPEATABLE_READ
d) TRANSACTION_NONREPEATABLE_READ

34
 Questions

1.Demonstrate the steps involved in JDBC and explain JDBC

Classes and interfaces.(RBT:L2)
2. Explain how the following steps are performed in JDBC:
1. Connect to a data source.
2. Start, commit, and abort transactions.
3. Call a stored procedure. (RBT:L3)
3. Compare exception handling and handling of warnings in
embedded SQL, dynamic SQL, JDBC, and SQLJ. (RBT:L3)
4. What are the differences between JDBC and SQLJ? Why do
they both exist? (RBT:L3)
5. How do we load JDBC drivers in Java code? (RBT:L3)
35
Questions-Contd..

6. How do we manage connections to data sources? What

properties can connections have? (RBT:L2)
7. What alternatives does JDBC provide for executing SQL DML
and DDL statements? (RBT:L2)
8. How do we handle exceptions and warnings in JDBC?
(RBT:L3)
9. What functionality provides the Database MetaDataclass?
(RBT:L2)

36
FDP CONTENT LINK
https://nptel.ac.in/courses/106/105/106105175/ (AICTE Approved FDP
link)
Self Assessment Quiz link
https://forms.gle/juPiZA1N6V2omoDD7
Innovative Content Link
1. https://www.youtube.com/watch?v=CAanqvvDsTw&list=PLEbnT
DJUr_Ic_9b4PcKmlae41cyxEefot&index=11
(GATE TRAINER LINK DBMS)
2. https://www.youtube.com/watch?v=5GQbACGHY_E&list=PLL_L
QvNX4xKyiExzq9GKwORoH6nvaRnOQ&index=24
(PROFESSIONAL TRAINER FOR DBMS)
3. https://www.youtube.com/watch?v=17jcw35uid0 (Practical
Tutorial)

37
 References

1. https://www.digimat.in/nptel/courses/video/106105175/L21.h
tml
(NPTEL Video)
2. https://www.javatpoint.com/java-jdbc
3. Database management systems, Ramakrishnan, and Gehrke,
3rd Edition, 2014, McGraw Hill
4. https://cse.iitkgp.ac.in/~dsamanta/java/ch11.htm
5. https://www.studytonight.com/java/java-sql-package.php
6. https://www.javatpoint.com/steps-to-connect-to-the-database-
in-java

38