Advanced Java

Programming
Dr. SIBO PRASAD PATRO
Assistant Professor
Department of Computer Science and Engineering
School of Engineering and Technology
GIET University, Gunupur, Odisha – 765022
MOB: 9437234031 EMAIL: sibofromgiet@giet.edu
 Class-01
Topics to be Covered

• Basics of JDBC:
• Introduction to JDBC
• Need of JDBC
• JDBC Drivers (4 types)
• Architecture of JDBC
• Components of JDBC (Classes and Interfaces).
• Programming with JDBC:
• Creating a DATABASE (ORACLE)
• First Program to connect to the DATABASE created
• Loading the Driver, Establishing the Connection
• Creating Statements (Statement/ PreparedStatement/
CallableStatement), Executing a SQL Query, Different types of SQL
Queries.
05/26/2025 Prof. Sibo Prasad Patro 2
 Introduction
• Advanced Java includes extra tools and technologies beyond the basics of
Core Java.
• Advanced Java is the collection of technologies and tools that enable
developers to create dynamic and secure applications, including features
such as JDBC (Java Database Connectivity), Servlets , and JSP (JavaServer
Page) are being used for generating and making interactive dynamic content,
the most important feature of advanced Java is JPA (Java Persistence API),
that is used for managing relational databases, Spring Framework that
encompasses the modules for dependency injection, Spring MVC
Framework, and AOP (Aspect-Oriented Programming), and Spring Security
that ensures the authentication, and authorization of the application.
• Moreover, It is important for creating dynamic websites, big software for
businesses, and connecting Java programs with databases.
 Prerequisites For Learning Advanced Java

• Core Java Proficiency

• Object-Oriented Programming (OOP)
• Web Development Basics
• Database Fundamentals
• Java Standard Libraries
• Basic Programming Logic
• Understanding of Networking
 What is the Difference Between
Core Java and Advanced Java?
• Core Java covers the basic fundamentals of the • Advanced Java covers technologies like
Java Programming Language and makes your JavaServer Pages (JSP) and servlets for
foundation strong including syntax, data types, creating dynamic web applications.
and basic programming constructs. • Advanced Java introduces its frameworks like
• Core Java is the Object-Oriented Spring, and Hibernate.
Programming ( OOP ) that emphasises class, • Advanced Java establishes Java Database
object, inheritance, and encapsulation. Connectivity ( JDBC ) for interacting with the
• Core Java establishes some basic libraries like databases.
‘java.lang’ package, string manipulation, and • Advanced Java uses technologies like SOAP
exception handling for the basic operations. (Simple Object Access Protocol) and REST
• Core Java introduces multithreading, that (Representational State Transfer) for creating
includes the creation of thread and basic and consuming web services.
synchronization. • Advanced Java uses Design Patterns that
• Core Java handles exceptions by using ‘try-catch’ create well-structured and maintainable code.
blocks as well as, you can create custom
exceptions.
 What is the Difference Between
Core Java and Advanced Java?
• Core Java covers the basic fundamentals of the • Advanced Java covers technologies like
Java Programming Language and makes your JavaServer Pages (JSP) and servlets for
foundation strong including syntax, data types, creating dynamic web applications.
and basic programming constructs. • Advanced Java introduces its frameworks like
• Core Java is the Object-Oriented Spring, and Hibernate.
Programming ( OOP ) that emphasises class, • Advanced Java establishes Java Database
object, inheritance, and encapsulation. Connectivity ( JDBC ) for interacting with the
• Core Java establishes some basic libraries like databases.
‘java.lang’ package, string manipulation, and • Advanced Java uses technologies like SOAP
exception handling for the basic operations. (Simple Object Access Protocol) and REST
• Core Java introduces multithreading, that (Representational State Transfer) for creating
includes the creation of thread and basic and consuming web services.
synchronization. • Advanced Java uses Design Patterns that
• Core Java handles exceptions by using ‘try-catch’ create well-structured and maintainable code.
blocks as well as, you can create custom
exceptions.
 Introduction to JDBC (Java
Database Connectivity)
• JDBC stands for Java Database Connectivity.
JDBC is a Java API to connect and execute the
query with the database.
• It is used for accessing databases from Java
applications.
• It is a specification from Sun Microsystems that
provides a standard abstraction(API or Protocol)
for Java applications to communicate with various
databases.
• It provides the language with Java database
connectivity standards.
• It is used to write programs required to access
databases. JDBC, along with the database driver,
can access databases and spreadsheets.
• The enterprise data stored in a relational
database(RDB) can be accessed with the help of
JDBC APIs.
 Purpose of JDBC
• Enterprise applications created using the JAVA EE
technology need to interact with databases to store
application-specific information. To interact with a
database we need efficient database connectivity, which can
be achieved by using the ODBC(Open database
connectivity) driver.
• JDBC driver is used for interacting with the database server
which implements the stated interfaces in the JDBC API.
For example, JDBC drivers interact with the database to
open database connections by sending SQL or database
commands and receive the results with Java:
• Any proprietary APIs are implemented by a JDBC driver.
• This driver is used with JDBC to interact or communicate
with various kinds of databases such as Oracle, MS Access,
Mysql, and SQL server database.
 ODBC vs. JDBC
1. ODBC Stands for Open Database Connectivity. 1. JDBC Stands for Java database connectivity.
2. Introduced by Microsoft in 1992. 2. Introduced by SUN Micro Systems in 1997.
3. We can use ODBC for any language like C, C++, 3. We can use JDBC only for Java languages.
Java etc. 4. We can use JDBC on any platform.
4. We can choose ODBC only Windows platform. 5. For Java applications it is highly recommended to use
5. Mostly ODBC Driver is developed in native JDBC because there are no performance & platform
languages like C, and C++. dependent problems.
6. For Java applications it is not recommended to use 6. JDBC is object-oriented.
ODBC because performance will be down due to 7. Programs that are developed using Java-JDBC API are
internal conversion and applications will become platform and vendor independent.
platform-dependent. 8. JDBC involves the logical quote of “write once, compile
7. ODBC is procedural. once, run anywhere”.
8. ODBC API can be used to access relational 9. It provides a standard API for tool or database developers
databases. and makes it possible to write database applications using
9. ODBC can be used with Java applied as the form pure Java API.
of JDBC-ODBC Bridge. 10. JDBC driver manager and JDBC drivers provide the
10. There occur a number of drawbacks in the security, bridge between the database and Java world.
implementation, robustness, automatic portability
during the calls from Java to native C code.
 Need of JDBC
• JDBC (Java Database Connectivity) is a crucial part of the Java programming language
that allows Java applications to interact with databases. The JDBC classes are
contained in the Java Package java.sql and javax.sql.
• Database Independence, Platform Independence, Easy to use, Support wide range of
databases, Efficient data access, Security, Scalability, Integration with java EE
• Database independent API
• Platform independent technology
• We can perform CRUD operation very easily
1. Create 2. Retrieve 3. Update 4. Delete
• We can also perform complex operation like joins, stored procedure etc.
• Huge vendor support for JDBC.
 Architecture of JDBC
• Application: It is a java applet or a servlet that
communicates with a data source.
• The JDBC API: The JDBC API allows Java programs to
execute SQL statements and retrieve results. Some of the
important interfaces defined in JDBC API are as follows:
Driver interface , ResultSet Interface , RowSet Interface ,
PreparedStatement interface, Connection interface, and
cClasses defined in JDBC API are as follows:
DriverManager class, Types class, Blob class, clob class.
• DriverManager: It plays an important role in the JDBC
architecture. It uses some database-specific drivers to
effectively connect enterprise applications to databases.
• JDBC drivers: To communicate with a data source
through JDBC, you need a JDBC driver that intelligently
communicates with the respective data source.
 Architecture (2-tier and 3-tier)
• Two-tier model: A java application • Three-tier model: In this, the user’s queries
communicates directly to the data are sent to middle-tier services, from which
source. The JDBC driver enables the the commands are again sent to the data
communication between the source. The results are sent back to the
application and the data source. When middle tier, and from there to the user.
a user sends a query to the data source, This type of model is found very useful by
the answers for those queries are sent management information system directors.
back to the user in the form of results.
The data source can be located on a
different machine on a network to
which a user is connected. This is
known as a client/server configuration,
where the user’s machine acts as a
client, and the machine has the data
source running acts as the server.
Components of JDBC (Classes and
Interfaces).
JDBC API is available in two packages java.sql, core API and javax.sql JDBC optional
packages. Following are the important classes and interfaces of JDBC.
Class/interface Description
This class manages the JDBC drivers. You need to register your drivers to this.
DriverManager
It provides methods such as registerDriver() and getConnection().
This interface is the Base interface for every driver class i.e. If you want to
create a JDBC Driver of your own you need to implement this interface. If you
Driver
load a Driver class (implementation of this interface), it will create an instance
of itself and register with the driver manager.

This interface represents a static SQL statement. Using the Statement object
and its methods, you can execute an SQL statement and get the results of it.
Statement
It provides methods such as execute(), executeBatch(), executeUpdate() etc. To
execute the statements.
Components of JDBC (Classes and
Interfaces).
This represents a precompiled SQL statement. An SQL statement is compiled and
stored in a prepared statement and you can later execute this multiple times. You can
get an object of this interface using the method of the Connection interface named
PreparedStatement prepareStatement(). This provides methods such as executeQuery(),
executeUpdate(), and execute() to execute the prepared statements and getXXX(),
setXXX() methods to set and get the values of the bind variables of the prepared
statement.
Using an object of this interface you can execute the stored procedures. This returns
single or multiple results. It will accept input parameters too. You can create a
CallableStatement CallableStatement using the prepareCall() method of the Connection interface.
Just like Prepared statement, this will also provide setXXX() and getXXX() methods
to pass the input parameters and to get the output parameters of the procedures.

This interface represents the connection with a specific database. SQL statements are
executed in the context of a connection. This interface provides methods such as
Connection
close(), commit(), rollback(), createStatement(), prepareCall(), prepareStatement(),
setAutoCommit() setSavepoint() etc.
Components of JDBC (Classes and
Interfaces).
This interface represents the database result set, a table which is generated by
ResultSet executing statements. This interface provides getter and update methods to retrieve
and update its contents respectively.
This interface is used to get the information about the result set such as, number of
columns, name of the column, data type of the column, schema of the result set,
ResultSetMetaData
table name, etc It provides methods such as getColumnCount(), getColumnName(),
getColumnType(), getTableName(), getSchemaName() etc.
Example of creating JDBC
Application overview
https://dev.mysql.com/downloads/file/?
id=531940
https://dev.mysql.com/downloads/file/?id=530070
 JDBC Drivers (4 Types)
• JDBC drivers are client-side adapters (installed on the client machine, not
on the server) that convert requests from Java programs to a protocol that
the DBMS can understand. JDBC drivers are the software components
which implements interfaces in JDBC APIs to enable java application to
interact with the database.
• There are four types of JDBC Drivers listed below,
• Type I: Bridge
• Type II: Native
• Type III: Middleware
• Type IV: Pure
 JDBC Drivers (4 Types)
1. Type 1: JDBC-ODBC Bridge Driver
• Description: This driver translates JDBC calls into ODBC (Open Database Connectivity)
calls and then communicates with the database using the ODBC driver.
• Advantages: Easy to use and can connect to any database that supports ODBC.
• Disadvantages: Slower performance due to the added layer of ODBC. Requires ODBC
driver installation on the client machine.
(deprecated, adds overhead).
2. Type 2: Native-API Driver (Partly Java Driver)
• Description: This driver converts JDBC calls into database-specific native calls using the
database's native client API (e.g., Oracle OCI, MySQL C API).
• Advantages: Faster than Type 1 because it uses native code.
• Disadvantages: Requires native database client libraries on the client machine. Not
portable as it is specific to a particular database.
(faster but requires native libraries).
 JDBC Drivers (4 Types)
3. Type 3: Network Protocol Driver (Middleware Driver)
• Description: This driver sends JDBC calls to a middleware server, which then translates
them to database-specific calls. The middleware can handle connections to multiple
databases.
• Advantages: No need for database-specific client libraries on the client machine. Suitable
for internet-based applications.
• Disadvantages: Requires an additional middleware server, which can add complexity and
overhead.
(requires middleware, flexible).
4. Type 4: Thin Driver (Pure Java Driver)
• Description: This driver directly converts JDBC calls to database-specific protocol calls. It
is entirely written in Java and communicates directly with the database.
• Advantages: Platform-independent, no need for additional client software or middleware.
High performance.
• Disadvantages: Specific to a particular database, so a different driver is needed for each
database type.
pure Java, platform-independent, preferred choice).
 1. JDBC-ODBC Driver
The JDBC-ODBC bridge driver uses ODBC driver to connect to the database. The JDBC-ODBC bridge driver
converts JDBC method calls into the ODBC function calls. These drivers uses bridging technology. They require
installation/configuration on client machines. These drivers are not recommended for Web. Example of Type I Driver
is ODBC Bridge.

Advantages:
•easy to use.
•can be easily connected to any database.
Disadvantages:
•Performance degraded because JDBC method call is converted
into the ODBC function calls.
•The ODBC driver needs to be installed on the client machine.

Note: Oracle does not support the JDBC-ODBC

Bridge from Java 8. Oracle recommends that you use
JDBC drivers provided by the vendor of your
database instead of the JDBC-ODBC Bridge.
 2. NATIVE-API Driver
The Native API driver uses the client-side libraries of the database. The driver converts JDBC
method calls into native calls of the database API. It is not written entirely in java. They require
installation/configuration on client machines. Type II drivers are used to leverage existing CLI
libraries and are usually not thread-safe, mostly obsolete now. Examples include Intersolv Oracle
Driver, WebLogic drivers.

Advantage:
•performance upgraded than JDBC-ODBC bridge driver.
Disadvantage:
•The Native driver needs to be installed on the each client
machine.
•The Vendor client library needs to be installed on client
machine.
 3. NETWORK PROTOCOL Driver
• The Network Protocol driver uses middleware (application server) that converts
JDBC calls directly or indirectly into the vendor-specific database protocol. It is fully
written in java.They are very flexible and allow access to multiple databases using
one driver. There is only the need to download one driver, but it is another server
application to install and maintain. An example of it is Symantec DBAnywhere.

Advantage:
•No client side library is required because of application
server that can perform many tasks like auditing, load
balancing, logging etc.
•Platform independent and Database independent driver.
Disadvantages:
•Network support is required on client machine.
•Requires database-specific coding to be done in the
middle tier.
•Maintenance of Network Protocol driver becomes
costly because it requires database-specific coding to be
done in the middle tier.
 4. THIN Driver
The thin driver converts JDBC calls directly into the vendor-specific database
protocol. That is why it is known as thin driver. It is fully written in Java
language. These drivers use Java networking libraries to talk directly to database
engines. A disadvantage here is the need to download a new driver for each
database engine. Examples include Oracle, mySQL etc.

Advantage:
•Better performance than all other drivers.
•Platform independent (driver purely written in
java)
Disadvantage:
•Drivers depend on the Database.
 Which Driver should be used

• If you are accessing one type of database, such as Oracle, Sybase, or IBM, the
preferred driver type is 4.

• If your Java application is accessing multiple types of databases at the same

time, type 3 is the preferred driver.

• Type 2 drivers are useful in situations, where a type 3 or type 4 driver is not
available yet for your database.

• The type 1 driver is not considered a deployment-level driver, and is typically

used for development and testing purposes only.
 Creating JDBC Application
There are following six steps involved in building a JDBC application −
1. Import the packages: Requires that you include the packages containing the JDBC
classes needed for database programming. Most often, using import java.sql.* will
suffice.
2. Register the JDBC driver: Requires that you initialize a driver so you can open a
communication channel with the database.
3. Open a connection: Requires using the DriverManager.getConnection() method to
create a Connection object, which represents a physical connection with the
database.
4. Execute a query: Requires using an object of type Statement for building and
submitting an SQL statement to the database.
5. Extract data from result set: Requires that you use the appropriate
ResultSet.getXXX() method to retrieve the data from the result set.
6. Clean up the environment: Requires explicitly closing all database resources versus
 SQL Basics
• MySQL is currently the most popular database management system software
used for managing the relational database. It is open-source database software,
which is supported by Oracle Company. It is fast, scalable, and easy to use
database management system in comparison with Microsoft SQL Server and
Oracle Database. SQL is a standardized language that allows you to perform
several operations on a database.
1. Create user <username> identified by ‘pwd’;
2. Select user from mysql.user;
3. Grant all privileges on *.* to <username>;
4. Show grants for <username>
5. Flush priviliges;
6. Drop user ‘username’;
7. Select user,host,account_locked,password_expired from mysql.user;
8. Select user() or current_user();
9. Update user set password=PASSWORD(‘new pwd’) where user=‘username’ and host=‘localhost’;
 SQL Basics
• Create Database
1. Create database gietu;
2. Show create database giet;
3. Show databases or show schemas;
DATABASE: GIETU
4. Show databases like”%sakila”;
TABLE NAME: F&H
5. Use giet; Name Roll number Marks
6. Drop database <if exists> databasename;
7. Drop schema <if exists> databasename;
8. Show tables(); //to fetch table under current user;
9. Create table emp(
id int not null auto_increment, name varchar(45) not null, jobid varchar(35) not null, age int not null,
primary key(id) );
10. Describe emp;
11. Alter table emp mob add mob varchar(10) not null after jobid, add email varchar(35) not null after age;
12. Alter table emp modify mob varchar(20) null;
13. Alter table emp drop column age;
 SQL Basics
1. Alter table emp change column mob mobile varchar(20) not null;
2. Alter table emp rename to employee;
3. Show tables in sakila;
4. Rename employee to employees;
5. Truncate table employees;
6. Insert into tablename(atr…) values(….);
7. Selct * from tablename;
8. Describe tablename;
9. Show columns from <databasename>.tablename;
10. Show full columns from <database>.tablename;
11. Explain select * from a;
 SQL Basics
• INSERT INTO my_table(column1, column2) VALUES ('value1', 'value2');
• INSERT INTO my_table(column_name, column_name_2) VALUES ('value', 'value2'), ('value3',
'value4'), ('value5', 'value6');
• DELETE FROM <table> WHERE <condition>;
• DELETE FROM employee WHERE first_name = 'Abigail';
• UPDATE <table> SET <column1> = <value1>, <column2> = <value2> WHERE
<match_condition>;
• Commit;
• Set aucommit=0;
• Rollback;
• Datatypes in mysql (char(size), varchar(size), binary(size), tinyblob, tinytext, text(size), blob(size),
longtext(size), longblob(size), bit(size), tinyint(size), bool / boolean, mediumint(size), int(size),
bigint(size), float(size,d), double(size,d), date [1000 yr to 9999 yyyy-mm-dd], datetime(sp),
timestamp(sp), time(sp)
• Inbuilt function operations : https://www.w3schools.com/mysql/mysql_ref_functions.asp
Java database connectivity with 5
steps
 Import JDBC package

• import java.sql.* ; // for standard JDBC programs

• import java.math.* ; // for BigDecimal and BigInteger support
 1. Register the driver class

The forName() method of Class class is used to register the driver

class. This method is used to dynamically load the driver class.

Syntax of forName() method

public static void forName(String className)throws ClassNotFoundException

Example to register the OracleDriver class

Class.forName("oracle.jdbc.driver.OracleDriver");

Example to register the MySQL class

Class.forName("com.mysql.cj.jdbc.Driver");
 2. Create the connection object
The getConnection() method of DriverManager class is used to establish connection with the database.

Syntax of getConnection() method

1) public static Connection getConnection(String url)throws SQLException

2) public static Connection getConnection(String url,String name,String password)
throws SQLException

Example to establish connection with the MySQL database

Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila","root","sibo");
RDBMS JDBC driver name URL format
MySQL com.mysql.jdbc.Driver jdbc:mysql://hostname/ databaseName
ORACLE oracle.jdbc.driver.OracleDriver jdbc:oracle:thin:@hostname:port
Number:databaseName

DB2 COM.ibm.db2.jdbc.net.DB2Driver jdbc:db2:hostname:port Number/databaseName

Sybase com.sybase.jdbc.SybDriver jdbc:sybase:Tds:hostname: port

Number/databaseName
 3. Create the statement object
The createStatement() method of Connection interface is used to create statement. The object of
statement is responsible to execute queries with the database.
Syntax of createStatement() method
public Statement createStatement()throws SQLException
Commonly used methods of Statement interface:
Statement stmt=conn.createStatement();
There are 3 types of Statements, as given below:
Statement: It can be used for general-purpose access to the database. It is useful when you are
using static SQL statements at runtime.
PreparedStatement: It can be used when you plan to use the same SQL statement many times. The
PreparedStatement interface accepts input parameters at runtime.
CallableStatement: CallableStatement can be used when you want to access database stored
procedures.
 4. Execute the query
The executeQuery() method of Statement interface is used to execute queries to the database.
This method returns the object of ResultSet that can be used to get all the records of a table.

Syntax of executeQuery() method • boolean execute (String SQL): Returns a boolean

value of true if a ResultSet object can be retrieved;
public ResultSet executeQuery(String sql)throws SQLException otherwise, it returns false. Use this method to execute
SQL DDL statements or when you need to use truly
dynamic SQL.
Example to execute query • int executeUpdate (String SQL): Returns the number
of rows affected by the execution of the SQL
ResultSet rs=stmt.executeQuery("select * from emp" statement. Use this method to execute SQL
); statements for which you expect to get a number of
rows affected - for example, an INSERT, UPDATE,
while(rs.next()){ or DELETE statement.
System.out.println(rs.getInt(1)+" "+rs.getString(2)); • ResultSet executeQuery (String SQL): Returns a
} ResultSet object. Use this method when you expect to
get a result set, as you would with a SELECT
statement.
 5. Close the connection object
By closing connection object statement and ResultSet will be closed automatically. The close()
method of Connection interface is used to close the connection.
Syntax of close() method
public void close()throws SQLException
Example to close connection
con.close();
 Java database connectivity with
Oracle
• To connect java application with the oracle database, we need to follow 5
following steps. In this example, we are using Oracle 10g as the database. So we
need to know following information for the oracle database:Driver class: The
driver class for the oracle database is oracle.jdbc.driver.OracleDriver.
• Connection URL: The connection URL for the oracle10G database
is jdbc:oracle:thin:@localhost:1521:xe where jdbc is the API, oracle is the
database, thin is the driver, localhost is the server name on which oracle is
running, we may also use IP address, 1521 is the port number and XE is the
Oracle service name. You may get all these information from the tnsnames.ora
file.
• Username: The default username for the oracle database is system.
• Password: It is the password given by the user at the time of installing the oracle
database.
Example to connect java
application with oracle database
• In this example, we are connecting to an Oracle database
and getting data from emp table.
• Here, system and oracle are the username and password
of the Oracle database.

The example will fetch all the records of emp table.

Note: To connect java application with the Oracle database

ojdbc14.jar file is required to be loaded.
 Connecting java application with
mysql database
To connect Java application with the MySQL database, we need to follow 5 following steps.
In this example we are using MySql as the database. So we need to know following information
for the mysql database:
1. Driver class: The driver class for the mysql database is com.mysql.jdbc.Driver.
2. Connection URL: The connection URL for the mysql database is
jdbc:mysql://localhost:3306/sonoo where jdbc is the API, mysql is the database, localhost is
the server name on which mysql is running, we may also use IP address, 3306 is the port
number and sonoo is the database name. We may use any database, in such case, we need to
replace the sonoo with our database name.
3. Username: The default username for the mysql database is root.
4. Password: It is the password given by the user at the time of installing the mysql database. In
this example, we are going to use root as the password.
Example of jdbc for mysql

Note: To connect java application

with the mysql
database, mysqlconnector.jar file is
required to be loaded.
 DriverManager Class
• The DriverManager class acts as an interface between user and drivers. It keeps track of the
drivers that are available and handles establishing a connection between a database and the
appropriate driver. The DriverManager class maintains a list of Driver classes that have
registered themselves by calling the method DriverManager.registerDriver().
Method Description

1) public static void registerDriver(Driver is used to register the given driver with
driver): DriverManager.

2) public static void is used to deregister the given driver (drop

deregisterDriver(Driver driver): the driver from the list) with
DriverManager.

3) public static Connection is used to establish the connection with the

getConnection(String url): specified url.

4) public static Connection is used to establish the connection with the

getConnection(String url,String specified url, username and password.
userName,String password):
 Connection Interface
Common methods are:

1) public Statement createStatement(): creates a statement object that can be used to execute SQL
queries.
2) public Statement createStatement(int resultSetType,int resultSetConcurrency): Creates a Statement
object that will generate ResultSet objects with the given type and concurrency.
3) public void setAutoCommit(boolean status): is used to set the commit status.By default it is true.
4) public void commit(): saves the changes made since the previous commit/rollback permanent.
5) public void rollback(): Drops all changes made since the previous commit/rollback.
6) public void close(): closes the connection and Releases a JDBC resources immediately.

• A Connection is the session between java application and database. The

Connection interface is a factory of Statement, PreparedStatement, and
DatabaseMetaData i.e. object of Connection can be used to get the object of
Statement and DatabaseMetaData. The Connection interface provide many
methods for transaction management like commit(), rollback() etc.
 Statement Interface [insupdel.java]
The Statement interface provides methods to execute queries with the
database. The statement interface is a factory of ResultSet i.e. it provides
factory method to get the object of ResultSet.

The important methods of Statement interface are as follows:

1) public ResultSet executeQuery(String sql): is used to execute SELECT
query. It returns the object of ResultSet.
2) public int executeUpdate(String sql): is used to execute specified query,
it may be create, drop, insert, update, delete etc.
3) public boolean execute(String sql): is used to execute queries that may
return multiple results.
4) public int[] executeBatch(): is used to execute batch of commands.
 ResultSet Intereface
• The object of ResultSet maintains a cursor pointing to a row of a table. Initially,
cursor points to before the first row. 1) public boolean next(): is used to move the cursor to the one row
next from the current position.

Statement stmt = con.createStatement(ResultSet.TYPE 2) public boolean previous(): is used to move the cursor to the one row
previous from the current position.
_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPD 3) public boolean first(): is used to move the cursor to the first row in
ATABLE); result set object.
4) public boolean last(): is used to move the cursor to the last row in
result set object.
5) public boolean absolute(int row): is used to move the cursor to the specified
row number in the ResultSet object.

6) public boolean relative(int row): is used to move the cursor to the relative row
number in the ResultSet object, it may be
positive or negative.
7) public int getInt(int columnIndex): is used to return the data of specified column
index of the current row as int.
8) public int getInt(String columnName): is used to return the data of specified column
name of the current row as int.
9) public String getString(int is used to return the data of specified column
columnIndex): index of the current row as String.
10) public String getString(String is used to return the data of specified column
columnName): name of the current row as String
 JDBC
Executing Query & Processing
Results

4
Objectives
At the end of this module, you will be able to:

– Create and execute a query using JDBC

API

– Process the data returned by the database

47
Stage 2:
Query

Connect

Query Create a statement

Process Query the database

results

Clos
e

48
Stage 2:
Query
• Once a connection is established, it is used to pass SQL statements to its
underlying
database.
• A Statement object is used to send SQL statements to a database. The Statement
interface
provides basic methods for executing statements and retrieving results.

• The JDBC API does not put any restrictions on the kinds of SQL statements that can be
sent; this provides a great deal of flexibility, allowing the use of database-specific
statements or even non-SQL statements. It requires, however, that the user be responsible
for making sure that the underlying database can process the SQL statements being sent
and suffer the consequences if it cannot. For example, an application that tries to send a
stored procedure call to a DBMS that does not support stored procedures will be
unsuccessful and will generate an exception.
49
Query: The
Statement Object
 To execute SQL statements use Statement Object.
 You need an active connection to create a JDBC statement
 Statement object has three methods to execute a SQL statements:
 executeQuery() for SELECT statements
 executeUpdate() for INSERT, UPDATE, DELETE, or DDL
statements
 execute() for either type of statement

50
Query: The Statement
Object
 The slide lists the three methods you can call to execute a SQL statement. The following
slides describe how to call each method. execute() is useful for dynamically executing
an unknown SQLstring.
 JDBC provides two other statement objects:
 PreparedStatement, for precompiled SQLstatements, is covered later.
 CallableStatement, for statements that execute stored procedures, is also covered later.
 Objects and Interfaces:
java.sql.Statement is an interface, not an object. When you declare a Statement object
and initialize it using the createStatement() method, you are creating the implementation of
the Statement interface supplied by the Oracle driver or any other driver that you are
using.

51
 How to Query the
Database?
1. To execute SQL statement , we should first create Statement object,
as:
Statement stmt = conn.createStatement();

2. To execute the query on the

database
ResultSet rset =
stmt.executeQuery(statement); int count =
stmt.executeUpdate(statement); boolean
isquery = stmt.execute(statement);

52
How to Query the
Database?
 Once a connection to a particular database is established, that connection can be used to
send SQL statements. A Statement object is created with the Connection method
createStatement, as in the following code fragment:
Statement stmt = conn.createStatement();
 The SQL statement that will be sent to the database is supplied as the argument to one
of
the execute methods on a Statement object.
 This is demonstrated in the following example, which uses the method executeQuery:
ResultSet rset = stmt.executeQuery("SELECT a, b, c FROM Table2");

 The variable rset references a result set discussed in the following sections.

53
 Querying the Database:
Examples
 Following Statements are used to execute Select
statement:
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery
("select NAME, VERTICAL from STUDENT");

 Following Statements are used to execute Select

statement: Statement stmt = conn.createStatement();
int rowcount = stmt.executeUpdate
("delete from STUDENT where ID = 1000");

54
 Querying the
Database
As mentioned earlier, the Statement interface provides three different methods for
executing SQL statements: executeQuery, executeUpdate, and execute. The correct method
to use is determined by what the SQL statement produces.
 The method executeQuery() method is used for statements that produce a result set, such
as
SELECT statements.
 The method executeUpdate() method is used to execute DML statements and also SQL
DDL (Data Definition Language) statements. The return value of executeUpdate() method
is an integer (referred to as the update count) that indicates the number of rows that were
affected. For statements such as CREATE TABLE or DROP TABLE, which do not operate
on rows, the executeUpdate() method is returns always zero.
 The method execute() is used to execute statements that return more than one result
set,
more than one update count, or a combination of the two.
 Process the
Results
Now that we have obtained the results of querying the database in the ResultSet object,
we need to iterate through this object and retrieve its contents for further processing in the
Java program.
Connect

Query
Step through the results

Process Assign results to Java

results variables

Close
Process the Results: The ResultSet
Object
 ResultSet is an object that contains the results of executing a SQL
statement

 A ResultSet maintains a cursor pointing to its current row of data

 Use next() to step through the result set row by row

 To retrieve the data from the columns, we can use getXXX() method
Process the Results: The ResultSet
Object
 The results are available in ResultSet object. In other words, it contains the rows that
satisfy
the conditions of the query.

 The data stored in a ResultSet object is retrieved through a set of get methods that
allows access to the various columns of the current row.

 The ResultSet.next() method moves the cursor to the next row in the ResultSet object.

 The general form of a result set is a table with column headings and the
corresponding values returned by a query.
Process the Results: The ResultSet
Object
For example, if your query is SELECT column_a, column_b, column_c FROM Table1,
your
result set will have the following form:
column_a column_b column_c
------ ------ -------
12345 Cupertino 2459723.495
83472 Redmond 1.0
83492 Boston 35069473.43

We can retrieve the results using the methods of ResultSet interface. Each of these
getXXX() methods attempts to convert the column value to the specified Java type and
returns a suitable Java value. For example, getInt() method gets the column value as an int
value, getString() method gets the column value as a String value, and geLong() method
returns the column value as a long value.
How to Process the
Result?

while (rset.next()) { … }

String val = String val =

rset.getString(colname); rset.getString(colIndex);

while (rset.next()) {
String name = rset.getString(“NAME");
String supervisor =
rset.getString(“SUPERVISOR");
… // Process or display the data
}
 How to Process the
Result?
A ResultSet object maintains a cursor, which points to its current row of data. The cursor
moves down one row each time the method next is called. When a ResultSet object is first
created, the cursor is positioned before the first row, so the first call to the next method
puts the cursor on the first row, making it the current row. ResultSet rows can be retrieved
in sequence from top to bottom as the cursor moves down one row with each successive
call to the method next.

 When a cursor is positioned on a row in a ResultSet object (not before the first row or
after the last row), that row becomes the current row. This means that any methods called
while the cursor is positioned on that row will operate on values in that row (methods
such as getXXX).
 A cursor remains valid until the ResultSet object or its parent Statement object is closed.
Setting up
classpath
 Before we can execute any JDBC program, we have to set the classpath for the type IV
driver. We need to know, what is the name of the jar file, which contains type IV driver.
The jar file, which we will be using is classes12.jar. This jar file is found within jlib
folder under oracle folder hierarchy.
 For e.g., In my system, classes12.jar is within the following path :
E:\app\harb\product\11.1.0\db_1\oui\jlib\classes.jar
 Add the above path(including classes12.jar) to the classpath that you have already set.
If you don’t have classpath set in your system, create a new enviroment variable and
add the following as its value :
Variable name : classpath
Variable value : .; E:\app\harb\product\11.1.0\db_1\oui\jlib\classes12.jar;
Setting up classpath
(Contd.).
 Setting up classpath

(Contd.).
If you are using eclipse IDE, then it will not recognize the environment variable,
classpath. You will have to add classes12.jar, as an external jar file to the build path.
Exampl
e java.sql.*;
import
class MakeConnection1{
Connection con;
Statement stmt;
ResultSet rs;
MakeConnection1(){
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection
("Jdbc:Oracle:thin:@localhost:1521:ORCL","scott","tige
r");
stmt=con.createStatement();
rs=stmt.executeQuery("Select ename, sal from emp");
while(rs.next())
System.out.println(rs.getString(1)+"
"+rs.getInt(2));
 Example (Contd.).
conn.close();
} // end of try
block
catch(SQLException e){
System.out.println
(e);
}
catch(ClassNotFoundException e){
System.out.println(e);
}
}
}
public class TestConnection1{
public static void main(String args[]){
new MakeConnection1();
}
Example (Contd.).
Quiz
1. What does the next() method invoked on the result set object return
a) String
b) int
c) boolean
d) float

2. Which one of the following method can be invoked on the connection object to create an empty Statement
object
a) createStatement();
b) getStatement();
c) prepareStatement(); Answers
d) executeStatement(); :
1:
c
2:
Close
Connection
Connect

Query
Close the result set

Process
results Close the statement

Close Close the connection

Close
Connection
 Normally, nothing needs to be done to close a ResultSet object; it is automatically closed
by the Statement object that generated it when that Statement object is closed, is re-
executed, or is used to retrieve the next result from a sequence of multiple results.

 The method close() is provided so that a ResultSet object can be closed explicitly,
thereby immediately releasing the resources held by the ResultSet object.

 This could be necessary when several statements are being used and the automatic close
does not occur soon enough to prevent database resource conflicts.
 How to Close the
Connection?
1. Close the ResultSet
object
rset.close();

2. Close the Statement

object
stmt.close();

3. Close the
connection
conn.close();
How to Close the
Connection?
 When a connection is in auto-commit mode, the statements being executed within it are
committed or rolled back when they are completed. A statement is considered complete
when it has been executed and all its results have been returned.

 For the method executeQuery, which returns one result set, the statement is
completed
when all the rows of the ResultSet object have been retrieved.

 For the method executeUpdate, a statement is completed when it is executed. In the rare
cases where the method execute is called, however, a statement is not complete until all of
the result sets or update counts it generated have been retrieved.
How to Close the
Connection?
 Statement objects will be closed automatically by the Java garbage collector.
Nevertheless, it is recommended as good programming practice that they be closed
explicitly when they are no longer needed. This frees DBMS resources immediately and
helps avoid potential memory problems.
 The same connection object can be used to execute multiple statements and retrieve many
result sets. However, once all the work with the database is over, it is a good programming
practice to close the connection explicitly. If this is not closed, after a particular timeout
period defined at the database, the connection is automatically closed. Nevertheless, this
would mean that till the timeout, this connection is not available to any other users of the
database. Hence, explicit closing of the connection is recommended.
Qui
z
1. Which of the following methods is not used for querying
database?
a. executeQuery()
b. executeStatement()
c. executeUpdate()
d. execute()
2. Which of the method is used to close a connection?
a. connection.close();
b. connection.terminate();
c. connection.exit();
d. None of the above Answer
s:
1. B
2. A
Thank
You
 JDBC
MetaData & PreparedStatement

7
Objective
sAt the end of this module, you will be able to:
– Analyze how to use the Metadata objects to retrieve more information about
the database or the result set

– Create and execute a query using PreparedStatement object

77
The DatabaseMetaData
Object
• Metadata is data about data
• DatabaseMetaData is an interface to get comprehensive information about the database
as a whole

• The Connection object can be used to get a DatabaseMetaData object

• Use the Connection.getMetaData()method to return a DatabaseMetaData object

• This object provides more than 100 methods to obtain information about the database

78
The DatabaseMetaData
Object
The following are some examples of DatabaseMetaData methods:
 getColumnPrivileges(): Get a description of the access rights for a table's columns.
 getColumns(): Get a description of table columns.
 getDatabaseProductName(): Get the name of this database product.
 getDriverName() : Get the name of this JDBC driver.
 storesLowerCaseIdentifiers(): Does the database store mixed-case SQL identifiers in
lower case?
 supportsAlterTableWithAddColumn(): Is ALTER TABLE with add column supported?
 supportsFullOuterJoins(): Are full nested outer joins supported?

79
How to obtain Database
Metadata?
1. To get the DatabaseMetaData
Object
DatabaseMetaData dbmd = conn.getMetaData();

2. Use the object’s methods to get the

metadata DatabaseMetaData dbmd = conn.getMetaData();
String s1 = dbmd getURL();
String s2 = dbmd.getSQLKeywords();
boolean b1 = dbmd.supportsTransactions();
boolean b2 = dbmd.supportsSelectForUpdate();

80
The DatabaseMetaData
Object
 This interface is implemented by driver vendors to let users know the capabilities of a Database Management System (DBMS) in combination
with thedriver based on JDBC technology ("JDBC driver") that is used with it. Different relational
DBMSs often support different features, implement features in different ways, and use different data
types. In addition, a driver may implement a feature on top of what the DBMS offers. Information
returned by methods in this interface applies to the capabilities of a particular driver and a particular
DBMS working together.
 A user for this interface is commonly a tool that needs to discover how to deal with the underlying
DBMS. This is especially true for applications that are intended to be used with more than one DBMS.
For example:
 getURL(): Returns the URL for the DBMS
 getSQLKeywords(): Retrieves a comma-separated list of all of this database's SQL keywords that are
NOT also SQL92 keywords.
 supportsTransactions(): Retrieves whether this database supports transactions. If not, invoking the
method commit is no use, and the isolation level is TRANSACTION_NONE.
 supportsSelectForUpdate(): Retrieves whether this database supports SELECT FOR
UPDATE
statements. 81
Example on
DatabaseMetaData object
import java.sql.*;
import java.io.*;
class MakeConnection3
{ Connection conn;
Statement stmt;
String s1, s2;
DatabaseMetaData dbmd;
MakeConnection3()
{ try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection("Jdbc:Oracle:thin:@localhost:1521:
orcl","scott","tiger");
contd..

82
Example on DatabaseMetadata
(Contd.).
dbmd = conn.getMetaData();
s1 = dbmd.getURL();
s2 =
dbmd.getSQLKeywords();
boolean b1 = dbmd.supportsTransactions();
boolean b2 = dbmd.supportsSelectForUpdate();
System.out.println("URL : "+s1);
System.out.println("SQL Keywords :"+s2);
System.out.println("This supports Transactions : "+b1);
System.out.println("This supports SelectforUpdate : "+b2);
}
contd..
83
Example on DatabaseMetadata
(Contd.).
catch(Exception e)
{ e.printStackTrace
();
}
}
}
public class MetaDataExample {
public static void main(String args[]) {
new MakeConnection3();
}
}

84
Example on DatabaseMetadata
(Contd.).
The ResultSetMetaData
Object
 ResultSetMetaData is an interface which contains methods to get information about
the
types and properties of the columns in the ResultSet object

 ResultSetMetaData object provides metadata, including:

 Number of columns in the result set
 Column type
 Column name
In JDBC, you use the ResultSet.getMetaData() method to return a ResultSetMetaData
object, which describes the data coming back from a database query. This object can be
used to find out about the types and properties of the columns in your ResultSet.
How to obtain
ResultSetMetadata?
1. To get the ResultSetMetaData
object
ResultSetMetaData rsmd = rset.getMetaData();

2. Use the object’s methods to get the

metadata ResultSetMetaData rsmd =
rset.getMetaData(); for (int i = 1; i <=
rsmd.getColumnCount(); i++) { String
colname = rsmd.getColumnName(i);
int coltype = rsmd.getColumnType(i);
…
}
Example on
ResultSetMetaData
import java.sql.*;
class MakeConnection4{
Connection conn;
Statement stmt;
ResultSet rs;
ResultSetMetaData rsmd;
MakeConnection4(){
try{
Class.forName("oracle
.jdbc.driver.OracleDr
iver");
conn=DriverManager.getConnection("Jdbc:Oracle:thin:@localhost:1521:
orcl","scott","tiger");
stmt=conn.createStatement();
rs=stmt.executeQuery("Select * from emp");
Example on
ResultSetMetaData(Contd.).
rsmd = rs.getMetaData();
int noc = rsmd.getColumnCount();
System.out.println("Number Of Columns : "+noc);

for(int i=1;i<=noc;i++) {
System.out.print("Column "+i+" ="+rsmd.getColumnName(i)+"; ");
System.out.print("Column Type ="+rsmd.getColumnType(i)+"; ");
System.out.println("Column Type Name="+rsmd.getColumnTypeName(i)+";");
}

}
catch(Exception e)
{ e.printStackTrac
e();
}
}
}
Example on
ResultSetMetaData
public class RSMetaDataExample {
(Contd.).
public static void main(String args[]) {
new MakeConnection4();
}
}
 The example shows how to use a ResultSetMetaData object to determine the following information
about the ResultSet:
 The number of columns in the ResultSet.
 The name of each column
 The American National Standards Institute (ANSI) SQL type for each column
 java.sql.Types
The java.sql.Types class defines constants that are used to
identify ANSI SQL types. ResultSetMetaData.getColumnType() returns an integer value that
corresponds to one of these constants.
Example on ResultSetMetaData
(Contd.).
 Mapping Database Types to Java
Types
 ResultSet maps database types to Java types.
 In many cases, you can get all the columns in your result set using the getObject() or
getString() methods of ResultSet. For performance reasons, or because you want to
perform complex calculations, it is sometimes important to have your data in a type that
exactly matches the database column.

Col Name Type

ResultSet rset = stmt.executeQuery
("select ID, DATE_OF_JOIN, SUPERVISOR ID NUMBER
from STUDENT");
DATE_OF_JOIN DATE

int id = rset.getInt(1); SUPERVISOR VARCHAR2

Date rentaldate = rset.getDate(2);
String status = rset.getString(3);
Mapping Database Types to Java
Types (Contd.).
SQL data type
Java data type
Simply mappable Object mappable

CHARACTER String
VARCHAR String
LONGVARCHA R String
NUMERIC java.math.BigDec imal
DECIMAL java.math.BigDec imal
BIT boolean Boolean
TINYINT byte Integer
SMALLINT short Integer
INTEGER int Integer
BIGINT long Long
REAL float Float
FLOAT double Double
DOUBLE PRECISION double Double
BINARY byte[]
VARBINARY byte[]
LONGVARBINARY byte[]
DATE java.sql.Date
TIME java.sql.Time
TIMESTA MP java.sql.Timestamp
Quiz
1. Which of the following java type is mapped to the SQL data type BIT
a) String
b) boolean
c) Int
d) byte

2. What object is returned, when you invoke the getMetaData method on the
Connection
object
a) StatementMetaData Answers
b) ResultSetMetaData :
1:
c) DatabaseMetaData
b
d) ConnectionMetaDat 2:
2
Prepared Statement Interface
[InsertPrepared.java]
• The PreparedStatement interface is a subinterface of Statement. It is used to
execute parameterized query. Let's see the example of parameterized query:
String sql="insert into emp values(?,?,?)";
• As you can see, we are passing parameter (?) for the values. Its value will be set
by calling the setter methods of PreparedStatement.
• Why use PreparedStatement?
• Improves performance: The performance of the application will be faster if
you use PreparedStatement interface because query is compiled only once.
• How to get the instance of PreparedStatement?
• The prepareStatement() method of Connection interface is used to return the
object of PreparedStatement. Syntax:
• public PreparedStatement prepareStatement(String query)throws
 Prepared Statement Interface
• Methods of PreparedStatement interface
• public void setInt(int paramIndex, int value)sets the integer value to the given
parameter index.
• public void setString(int paramIndex, String value)sets the String value to the
given parameter index.
• public void setFloat(int paramIndex, float value)sets the float value to the given
parameter index.
• public void setDouble(int paramIndex, double value)sets the double value to
the given parameter index.
• Exemple: PreparedStatement interface that inserts the record
• First of all create table as given below:
• create table emp(id number(10),name varchar2(50));
 Prepared Statement Interface
• Example1: To search a record using prepared statement (InsertPrepared.java)
• Example2: To insert a record using prepared statement (InsertPrepared1.java)
Example 1 on
PreparedStatement
/* This class is executed in the following manner :
if you want to create the table, you will execute as java JCreate table1 where table1 is the name of the
table. The table table1 is created with the following columns empid, empname, dept, joindate, salary
*/
import java.sql.*;
class JCreate {
public static
void main(String
args[]) throws
SQLException {
JdbcCalls e = new JdbcCalls();
e.create(args);
}
}
Example 1 on
PreparedStatement(Contd.).
import java.sql.*;
class ConnectionClass {
Connection con;
Connection connectionFactory() {
try {
Class.forName("oracle.jdbc.dr
iver.OracleDriver");
con=DriverManager.getConn
ection
("Jdbc:Oracle:thin:@localhost:152
1:ORCL","scott","tiger");
}
catch(Exception e) {
System.out.println(e);
}
} return con;
 Example 1 on PreparedStatement
(Contd.).
class JdbcCalls {
Connection
con;
JdbcCalls() {
ConnectionClass x = new ConnectionClass();
con=x.connectionFactory();
}
void create(String[] args) throws SQLException
{
String tablename = args[0];
PreparedStatement pst = con.prepareStatement("Create table "+tablename+" (empid
number(4), empname varchar(20), dept varchar2(10), joindate date, salary
number(10,2))");
pst.executeUpdate();
System.out.println(“Table created successfully”);
} }
Example 1 on PreparedStatement
(Contd.).
Example 2 on
PreparedStatement
/* This class is executed in the following manner :
If you want to insert a row within the table, you will execute as java JInsert jdbcdemotable 1001 anish
admin 23-dec-2008 50000.00 */
import java.sql.*;
class JInsert {
public static void main(String args[]){
try {
JdbcCalls e = new JdbcCalls();
e.insert(args);
}
catch(SQLException e)
{ System.out.println(e.toString(
));
}
}
Example 2 on PreparedStatement
(Contd.).
class JdbcCalls {
Connection
con;
JdbcCalls() {
ConnectionClass x = new ConnectionClass();
con=x.connectionFactory();
}
void insert(String[] args) throws SQLException
{
String tablename = args[0];
int empid = Integer.parseInt(args[1]);
String empname = args[2];
String dept = args[3];
String dat=args[4];
Float salary =
Example 2 on PreparedStatement
(Contd.).
PreparedStatement pst = con.prepareStatement("insert into "+tablename+"
values(?,?,?,?,?)");
pst.setInt(1, empid);
pst.setString(2, empname);
pst.setString(3,
dept); pst.setString(4,
dat); pst.setFloat(5,
salary);
pst.executeUpdate();
System.out.println(“Re
cord inserted
successfully”);
}
}
Example 2 on PreparedStatement
(Contd.).
Example 3 on
PreparedStatement
/* This class is executed in the following manner :
If you want to display all the rows, you will execute as java JDisplay jdbcdemotable
*/
import java.sql.*;
class JDisplay {
public static void main(String args[]) {
try {
JdbcCalls e = new JdbcCalls();
e.display(args);
}
catch(Exception e)
{ System.out.println
(e);
}
}
} 3
Example 3 on PreparedStatement
(Contd.).
class JdbcCalls
{ Connection
con; JdbcCalls()
{
ConnectionClass x = new ConnectionClass();
con=x.connectionFactory();
}
void display(String[] args) throws SQLException {
String tablename = args[0];
PreparedStatement pst = con.prepareStatement("select * from "+tablename);
ResultSet rs= pst.executeQuery();
while(rs.next()) {
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3)+"
"+rs.getDate(4)+" "+rs.getFloat(5)); }
con.close();
Example 3 on PreparedStatement
(Contd.).
Example on Modifying the
row
/* This class is executed in the following manner :
If you want to modify a row, you will execute as Java JModify table1 1001 60000.00 where
modifying a row will allow you to change the salary
*/
import java.sql.*;
class JModify {
public static void main(String args[]) {
try {
JdbcCalls e = new JdbcCalls();
e.modify(args);
}
catch(SQLException e)
{ System.out.println(
e);
}
Example on Modifying the row
(Contd.).
class JdbcCalls {
Connection con;
JdbcCalls() {
ConnectionClass x = new ConnectionClass();
con=x.connectionFactory();
}
void modify(String[] args) throws
SQLException{
String tablename = args[0];
int empid = Integer.parseInt(args[1]);
Float sal =
Float.parseFloat(args[2]);
PreparedStatement pst = con.prepareStatement("update "+tablename+" set
salary="+sal+" where empid="+empid);
int i=
pst.executeUpdate();
} 3
Example on Deleting a
row
/* This class is executed in the following manner : If you want to delete a row, you
will
execute as java JDelete table1 1001
*/
import java.sql.*;
class JDelete{
public static
void
main(String
args[]) {
try {
JdbcCalls e = new JdbcCalls();
e.delete(args);
}
} catch(SQLException e)
} { System.out.println(
Example on Deleting a row
(Contd.).
class JdbcCalls {
Connection con;
JdbcCalls() {
ConnectionClass x = new ConnectionClass();
con=x.connectionFactory();
}
void delete(String[] args) throws SQLException {
String tablename = args[0];
int empid = Integer.parseInt(args[1]);
PreparedStatement pst = con.prepareStatement("delete from "+tablename+"
where empid="+empid);
int i= pst.executeUpdate();
con.close();
}
}
Thank You
 CallableStatement Interface
• A CallableStatement is used to execute stored procedures in the database. Stored
procedures are precompiled SQL statements that can be called with parameters.
They are useful for executing complex operations that involve multiple SQL
statements.
• Syntax: To create a CallableStatement,
• CallableStatement cstmt = con.prepareCall("{call ProcedureName(?, ?)}");{call
ProcedureName(?, ?)}: Calls a stored procedure named ProcedureName with
placeholders ? for input parameters.
• Methods to Execute:
• execute(): Executes the stored procedure and returns a boolean indicating whether the result
is a ResultSet (true) or an update count (false).
• executeQuery(): Executes a stored procedure that returns a ResultSet.
• executeUpdate(): Executes a stored procedure that performs an update and returns the
number of rows affected.
 CallableStatement Interface
• A CallableStatement is used to execute stored procedures in the database. Stored
procedures are precompiled SQL statements that can be called with parameters.
They are useful for executing complex operations that involve multiple SQL
statements.
• Syntax: To create a CallableStatement,
• CallableStatement cstmt = con.prepareCall("{call ProcedureName(?, ?)}");{call
ProcedureName(?, ?)}: Calls a stored procedure named ProcedureName with
placeholders ? for input parameters.
• Methods to Execute:
• execute(): Executes the stored procedure and returns a boolean indicating whether the result
is a ResultSet (true) or an update count (false).
• executeQuery(): Executes a stored procedure that returns a ResultSet.
• executeUpdate(): Executes a stored procedure that performs an update and returns the
number of rows affected.
 Stored Function Vs Procedures
S.NO Function Procedure
Functions always return a value after the execution of The procedure can return a value using “IN OUT” and “OUT”
1.
queries. arguments.

In SQL, those functions having a DML statement can not be

2. called from SQL statements. But autonomous transaction A procedure can not be called using SQL queries.
functions can be called from SQL queries.

Each and every time functions are compiled they provide Procedures are compiled only once but they can be called many
3.
output according to the given input. times as needed without being compiled each time.

4. A Function can not return multiple result sets. A procedure is able to return multiple result sets.

5. The function can be called using Stored Procedure. While procedures cannot be called from function.

6. A function used only to read data. A procedure can be used to read and modify data.

The return statement of a function returns the control and While the return statement of the procedure returns control to
7.
function’s result value to the calling program. the calling program, it can not return the result value.
8. The function does not support try-catch blocks. Procedure supports try-catch blocks for error handling.
9. A function can be operated in the SELECT statement. While it can’t be operated in the SELECT statement.

10. Functions do not permit transaction management. It allows transaction management.

In functions, we can use only a table variable. Temporary In procedures, we can use temporary tables or table variables to
11.
tables can not be created in function. store temporary data.
 Function Example
• To define a stored procedure or function, use CREATE PROCEDURE or CREATE FUNCTION
respectively:
SHOW FUNCTION STATUS WHERE db = 'your_databasename';
DELIMITER &&
CREATE FUNCTION cal ( a int,b int) CREATE FUNCTION
RETURNS INT
func_name (parameters)
DETERMINISTIC
RETURNS
BEGIN
return a+b;
return_type
END&& BEGIN
DELIMITER ; -- function body
-- call the addition function
SELECT cal(10, 12);
END;
 Function Example
DELIMITER &&
CREATE FUNCTION Func_Cube ( Num INT )
RETURNS INT
--DETERMINISTIC
BEGIN
DECLARE TotalCube INT;
SET TotalCube = Num * Num * Num;
RETURN TotalCube;
END&&
DELIMITER ;
-- call the addition function
SELECT Func_Cube(12);
 Function Example
Create a table salary with sal attribute
Insert 4-5 records into salary table
Create a function which will show the average salary for the given employees
DELIMITER //
CREATE FUNCTION Demoavg() RETURNS INT
DETERMINISTIC
BEGIN
DECLARE result INT;
SET result = (SELECT AVG(marks) FROM studinfo);
RETURN result;
END //
DELIMITER ;
Now to show the avg salary we can call
Select Demoavg()
 Stored Procedures
DELIMITER && IN parameter
CREATE PROCEDURE procedure_name It is the default mode. It takes a parameter as input,
[[IN | OUT | INOUT] parameter_name dat such as an attribute. When we define it, the calling
atype [, parameter datatype]) ] program has to pass an argument to the stored
BEGIN procedure. This parameter's value is always protected.
OUT parameters
Declaration_section It is used to pass a parameter as output. Its value can be
Executable_section changed inside the stored procedure, and the changed
END && (new) value is passed back to the calling program. It is
noted that a procedure cannot access the OUT
DELIMITER ;
Commands : parameter's initial value when it starts.
SHOW PROCEDURE STATUS; INOUT parameters
SHOW FUNCTION STATUS; It is a combination of IN and OUT parameters. It
Help show means the calling program can pass the argument, and
SHOW PROCEDURE STATUS WHERE
Db = DATABASE() AND Type = 'PROCEDURE'
the procedure can modify the INOUT parameter, and
then passes the new value back to the calling program.
CALL procedure_name ( parameter(s))
 Stored Procedures
DELIMITER && DELIMITER && DELIMITER &&
CREATE PROCEDURE CREATE PROCEDURE CREATE PROCEDURE
get_merit_student () display_max_mark (OUT display_marks (INOUT var1 INT)
BEGIN highestmark INT) BEGIN
BEGIN SELECT marks INTO var1
SELECT * FROM studinfo SELECT MAX(marks) INTO FROM studinfo WHERE id = var1;
WHERE marks > 70;
highestmark FROM studinfo;
SELECT COUNT(id) AS END && END &&
Total_Student FROM studinfo; DELIMITER ; DELIMITER ;
END &&
DELIMITER ; Call display_max_mark SET @M = '3';
CALL display_marks(@M);
Call get_merit_student(); (@m); SELECT @M;
Show @m;
 Stored Procedures
DELIMITER &&

CREATE PROCEDURE getinfo(IN empId INT)

BEGIN
SELECT id, sub, marks
FROM studinfo
WHERE id = empId;
end &&
DELIMITER ;

Demonstrate Callable2.java
 Stored Procedures
DELIMITER &&
CREATE PROCEDURE getmark (IN var1 INT)
BEGIN
SELECT * FROM studinfo LIMIT var1;
SELECT COUNT(id) AS Total_Student FROM studinfo where marks>var1;
END &&
DELIMITER ;
How to invoke the getmark procedure in mysql
Call procedure(val)
How to drop the procedure in mysql
Demonstrate Callable.java
Drop procedure procedurename
 UNIT:2 Web Architecture
Introduction to HTML
• HTML (HyperText Markup Language) is the standard markup language used to
create web pages. It defines the structure of a web page using a series of
elements and tags. HTML elements are the building blocks of all websites, and
they allow browsers to interpret and display content.
 UNIT:2 Web Architecture
<!DOCTYPE html>
•<!DOCTYPE html>: Defines the document
<html> type and version of HTML.
<head> •<html>: The root element of the HTML
<title>Page Title</title> document.
•<head>: Contains meta-information about
</head> the document, such as the title, links to
<body> stylesheets, and scripts.
•<title>: Sets the title of the document,
<h1>This is a Heading</h1>
displayed in the browser's title bar or tab.
<p>This is a paragraph.</p> •<body>: Contains the visible content of the
</body> document.

</html>
 UNIT:2 Web Architecture
Various HTML Tags and Their Usage
HTML tags are used to define elements within a webpage. Below are some of the most common tags
and their purposes:
Heading Tags (<h1> to <h6>): Define headings of different levels.
<h1>Main Heading</h1><h2>Subheading</h2>
Paragraph Tag (<p>): Defines a paragraph.
<p>This is a paragraph of text.</p>
Link Tag (<a>): Defines a hyperlink.
<a href="https://example.com">Visit Example</a>
Image Tag (<img>): Embeds an image.
<img src="image.jpg" alt="Description of image">
 UNIT:2 Web Architecture
List Tags:
Unordered List (<ul>) and List Item (<li>): <dl>
• <ul style="list-style-type:disc;"> <ol type="I“start="50"> <dt>Coffee</dt>
<li>Coffee</li> <li>Coffee</li> <dd>- black hot drink</dd>
<li>Tea</li> <li>Tea</li> <dt>Milk</dt>
<li>Milk</li> <li>Milk</li> <dd>- white cold drink</dd>
</ul> </ol> </dl>
Bold and Italic Tags:
Bold Text (<b> or <strong>):
<b>This text is bold</b>
Italic Text (<i> or <em>):
<i>This text is italicized</i>
 UNIT:2 Web Architecture
Layout Tags :Layout tags help structure and Semantic Tags : Semantic tags clearly
organize content on the web page. describe the meaning of the content they
enclose. These tags improve the readability of
<div> Tag: Defines a division or section of the the HTML and provide better accessibility and
page, commonly used for layout purposes. SEO benefits.
<div> This is a section of the page.</div> <header>: Defines a header for a
<span> Tag: Used to group inline elements for document or section.
styling. <nav>: Defines navigation links.
<span style="color: red;">This text is red.</span> <article>: Defines an article or piece of
content that could stand alone.
<div style="background- <section>: Defines a section in a
color:black;color:white;padding:20px;"> document.
<h2>London</h2> <aside>: Defines content aside from the
main content (like a sidebar).
<p>London is the capital city of England</p>
<footer>: Defines a footer for a document
</div> div { width:300px; margin:auto; } or section.
 UNIT:2 Web Architecture
Example:
html <section>
<article>
Copy code <h2>Article Title</h2>
<header> <p>This is the main content of the
<h1>Website Title</h1> article.</p>
</header> </article>
</section>
<nav> <aside>
<ul> <p>This is related content, such as ads or
<li><a href="#home">Home</a></li> links.</p>
<li><a href="#about">About</a></li> </aside>
<footer>
</ul> <p>Footer content goes here.</p>
</nav> </footer>
 UNIT:2 Web Architecture
Tables :
Example:
Tables are used to organize data in rows and columns. <table border="1">
<table>: Defines the table structure. <tr>
<tr>: Defines a row in the table. <th>Name</th>
<th>: Defines a header cell in the table. <th>Age</th>
</tr>
<td>: Defines a standard cell in the table. <tr>
<td>John</td>
<td>25</td>
</tr>
<tr>
<td>Jane</td>
<td>30</td>
</tr>
</table>
 UNIT:2 Web Architecture
HTML FORMS: <form action="/submit" method="post">
<label for="name">Name:</label>
• <form>: Defines the form.
<input type="text" id="name"
• <input>: Defines an input field. name="name"><br><br>
• <textarea>: Defines a multi-line text
input field. <label for="age">Age:</label>
<input type="number" id="age"
• <button>: Defines a clickable name="age"><br><br>
button.
• <select> and <option>: Defines a <label for="gender">Gender:</label>
dropdown list. <select id="gender" name="gender">
<option value="male">Male</option>
<option value="female">Female</option>
</select><br><br>
<button type="submit">Submit</button>
</form>
 UNIT:2 Web Architecture
STYLING HTML (USING STYLE)
You can add inline styles to HTML elements using the style attribute or link to
external stylesheets.
Inline CSS:
<p style="color: blue; font-size: 14px;">This is a styled paragraph.</p>
External CSS:
<link rel="stylesheet" type="text/css" href="styles.css">
 UNIT:2 Web Architecture
USING DIV FOR LAYOUT:
The <div> tag is often used for creating layouts, especially with CSS.
Example:
<div style="width: 100%; background-color: lightgray;">
<div style="width: 50%; float: left;">
Left Column Content
</div>
<div style="width: 50%; float: right;">
Right Column Content
</div>
</div>
 HTML
Application
Tags

13
Objectives
At the end of this module, you will be able to:
 Table creation
 Form creation
 Frames and usage

wipro.co
13
HTML
Tables

13
Application Tags:
Tables
Tables are used to display text / Information in tabular format. I.e. in the from of rows and
columns.
Tags Purpose

<TABLE> Begins the table

definition
<TR> Defines a new row
<TD> Defines a single cell

<TH> Defines header cell

13
Tables: Table Attributes
ALIGNMENT Left / center / right

BORDER Defines the border

WIDTH Defines the width of the table

CELLSPACING Space between the cells

CELLPADDING Space between the text and cell border

EXAMPLE:

<TABLE ALIGNMENT=“center” BORDER=“0” WIDTH=“100%”

CELLSPACING=“2” CELLPADDING=“4”>

13
 Tables: Merging
Cells
1. ROWSPAN

Used to merge cells in rows

<TD ROWSPAN=“2”> two cells in consecutive columns are merged

2. COLSPAN

Used to merge cells in Columns

<TD COLSPAN=“2”> two cells in consecutive rows are merged

13
 Demonstration
Demonstration of creating different tables that use the following attributes :

 cellspacing

 cellpadding

 rowspan

 colspan

 table border

14
Exampl
e
EXAMPLE:
<TABLE BORDER="7" CELLPADDING="7" CELLSPACING="10">
<TR BGCOLOR="#00FF00">
<TD>A green row.</TD>
<TD BGCOLOR="#FFFF00">This cell should be yellow, overriding the
row color.</TD> <TD>Back to the row color.</TD>
<TR BGCOLOR="#0000FF">
<TD>A blue row.</TD>
<TD><PRE> </PRE></TD>
<TD ROWSPAN="2">This cell takes the color of the topmost row that
it spans</TD>
</TR>
<TR BGCOLOR=“#FF0000">
<TD colspan=“2” align=“center” >A red row.</TD>
</TR>
</TABLE>
14
 Quiz
1. What are the two table dimension tags?
a. <TL> and TW>
b. <TR> and <TD>
c. <TD> and <RD>
<TR> and
<TD>

2. What does this line of HTML <td height="38"></td> do?

a. Sets the height of the table to 38 pixels
b. Sets the height of the cell to 38 pixels
c. It's not valid

Sets the height of the cell to 38

pixels

14