XML Basics

XML stands for Extensible Markup Language and is a text-based markup language
derived from Standard Generalized Markup Language (SGML).

XML is a software- and hardware-independent tool for storing and transporting data.

 XML is a markup language much like HTML

 XML was designed to store and transport data
 XML was designed to be self-descriptive
 XML is a W3C Recommendation

XML tags identify the data and are used to store and organize the data, rather than
specifying how to display it like HTML tags, which are used to display the data. XML
is not going to replace HTML in the near future, but it introduces new possibilities by
adopting many successful features of HTML.

There are three important characteristics of XML that make it useful in a variety of
systems and solutions:

 XML is extensible: XML allows you to create your own self-descriptive tags, or
language, that suits your application.

 XML carries the data, does not present it: XML allows you to store the data
irrespective of how it will be presented.

 XML is a public standard: XML was developed by an organization called the

World Wide Web Consortium (W3C) and is available as an open standard.

XML Usage

A short list of XML usage says it all:

 XML can work behind the scene to simplify the creation of HTML documents for
large web sites.
 XML can be used to exchange the information between organizations and
systems.
 XML can be used for offloading and reloading of databases.
 XML can be used to store and arrange the data, which can customize your data
handling needs.
 XML can easily be merged with style sheets to create almost any desired
output.
 Virtually, any type of data can be expressed as an XML document.

1
The Difference between XML and HTML

XML and HTML were designed with different goals:

 XML was designed to carry data - with focus on what data is

 HTML was designed to display data - with focus on how data looks
 XML tags are not predefined like HTML tags
 XML documents form a tree structure that starts at "the root" and branches to
"the leaves".

XML Syntax:

Following is a complete XML document:

<?xml version="1.0"?>
<contact_info>
<name>Rajesh</name>
<company>TCS</company>
<phone>9333332354</phone>
</contact_info>

You can notice there are two kinds of information in the above example:

 markup, like <contact-info> and

 the text, like Rajesh etc.

The following diagram depicts the syntax rules to write different types of markup and
text in an XML document.

2
 )

Let us see each component of the above diagram in detail:

XML Declaration
The XML document can optionally have an XML declaration. It is written as below:

<?xml version="1.0" encoding="UTF-8"?>

Where version is the XML version and encoding specifies the character encoding used
in the document.

Syntax Rules for XML declaration

 The XML declaration is case sensitive and must begin with "<?xml>" where
"xml" is written in lower-case.

 If document contains XML declaration, then it strictly needs to be the first

statement of the XML document.

 The XML declaration strictly needs be the first statement in the XML document.

 An HTTP protocol can override the value of encoding that you put in the XML
declaration.

Tags and Elements

An XML file is structured by several XML-elements, also called XML-nodes or XML-

tags. XML-elements' names are enclosed by triangular brackets < > as shown below:

<element>

Syntax Rules for Tags and Elements

Element Syntax: Each XML-element needs to be closed either with start or with end
elements as shown below:

<element>....</element>

or in simple-cases, just this way:

<element/>

Nesting of elements:

An XML-element can contain multiple XML-elements as its children, but the children
elements must not overlap. i.e., an end tag of an element must have the same name
as that of the most recent unmatched start tag.

Following example shows incorrect nested tags:

3
<?xml version="1.0"?>
<contact_info>
<company>TCS
<contact_info>
</company>

Following example shows correct nested tags:

<?xml version="1.0"?>
<contact_info>
<company>TCS</company>
<contact_info>

Root element:

An XML document can have only one root element. For example, following is not a
correct XML document, because both the x and y elements occur at the top level
without a root element:

<x>...</x>
<y>...</y>

The following example shows a correctly formed XML document:

<root>
<x>...</x>
<y>...</y>
</root>

Case sensitivity:

The names of XML-elements are case-sensitive. That means the name of the start
and the end elements need to be exactly in the same case.

For example <contact_info> is different from <Contact_Info>.

Attributes
An attribute specifies a single property for the element, using a name/value pair.
An XML-element can have one or more attributes. For example:

<a href="http://www.tutorialspoint.com/">Tutorialspoint!</a>

Here href is the attribute name and http://www.tutorialspoint.com/ is attribute

value.

4
Syntax Rules for XML Attributes

Attribute names in XML (unlike HTML) are case sensitive. That is, HREF and href are
considered two different XML attributes.

Same attribute cannot have two values in a syntax. The following example shows
incorrect syntax because the attribute b is specified twice:
<a b="x" c="y" b="z">....</a>

Attribute names are defined without quotation marks, whereas attribute values must
always appear in quotation marks. Following example demonstrates incorrect xml
syntax:
<a b=x>....</a>

In the above syntax, the attribute value is not defined in quotation marks.

XML References
References usually allow you to add or include additional text or markup in an XML
document. References always begin with the symbol "&" ,which is a reserved
character and end with the symbol ";". XML has two types of references:

Entity References: An entity reference contains a name between the start and the
end delimiters. For example &amp; where amp is name. The name refers to a
predefined string of text and/or markup.

Character References: These contain references, such as &#65;, contains a hash

mark (“#”) followed by a number. The number always refers to the Unicode code of
a character. In this case, 65 refers to alphabet "A".

XML Text

 The names of XML-elements and XML-attributes are case-sensitive, which

means the name of start and end elements need to be written in the same
case.
 To avoid character encoding problems, all XML files should be saved as Unicode
UTF-8 or UTF-16 files.
 Whitespace characters like blanks, tabs and line-breaks between XML-elements
and between the XML-attributes will be ignored.
 Some characters are reserved by the XML syntax itself. Hence, they cannot be
used directly. To use them, some replacement-entities are used, which are
listed below:

5
not allowed character replacement-entity character description

< &lt; less than

> &gt; greater than

& &amp; ampersand

' &apos; apostrophe

" &quot; quotation mark

XML Tree Structure:

6
An Example XML Document

The image above represents books in this XML:

--------------------------------------------------------------------------

<?xml version="1.0" encoding="UTF-8"?>

<bookstore>
<book category="cooking">
<title lang="en">Everyday Italian</title>
<author>Giada De Laurentiis</author>
<year>2005</year>
<price>30.00</price>
</book>
<book category="children">
<title lang="en">Harry Potter</title>
<author>J K. Rowling</author>
<year>2005</year>
<price>29.99</price>
</book>
<book category="web">
<title lang="en">Learning XML</title>
<author>Erik T. Ray</author>
<year>2003</year>
<price>39.95</price>
</book>
</bookstore>

---------------------------------------------------------------

XML documents are formed as element trees.

An XML tree starts at a root element and branches from the root to child elements.

All elements can have sub elements (child elements):

<root>
<child>
<subchild>.....</subchild>
</child>
</root>

The terms parent, child, and sibling are used to describe the relationships between
elements.

Parents have children. Children have parents. Siblings are children on the same
level (brothers and sisters).

7
 XML Namespaces
Name Conflicts

In XML, element names are defined by the developer. This often results in a conflict when
trying to mix XML documents from different XML applications.

This XML carries HTML table information:

<table>
<tr>
<td>Apples</td>
<td>Bananas</td>
</tr>
</table>

This XML carries information about a table (a piece of furniture):

<table>
<name>African Coffee Table</name>
<width>80</width>
<length>120</length>
</table>

If these XML fragments were added together, there would be a name conflict. Both
contain a <table> element, but the elements have different content and meaning.

A user or an XML application will not know how to handle these differences.

Solving the Name Conflict Using a Prefix

Name conflicts in XML can easily be avoided using a name prefix.

This XML carries information about an HTML table, and a piece of furniture:

<h:table>
<h:tr>
<h:td>Apples</h:td>
<h:td>Bananas</h:td>
</h:tr>
</h:table>

<f:table>
<f:name>African Coffee Table</f:name>
<f:width>80</f:width>
<f:length>120</f:length>
</f:table>

In the example above, there will be no conflict because the two <table> elements have
different names.

8
XML Namespaces - The xmlns Attribute

When using prefixes in XML, a namespace for the prefix must be defined.

The namespace can be defined by an xmlns attribute in the start tag of an element.

The namespace declaration has the following syntax. xmlns:prefix="URI".

-----------------------------------------------------

<root>
<h:table xmlns:h="http://www.w3.org/TR/html4/">
<h:tr>
<h:td>Apples</h:td>
<h:td>Bananas</h:td>
</h:tr>
</h:table>

<f:table xmlns:f="https://www.w3schools.com/furniture">
<f:name>African Coffee Table</f:name>
<f:width>80</f:width>
<f:length>120</f:length>
</f:table>
</root>

--------------------------------------------------

In the example above:

The xmlns attribute in the first <table> element gives the h: prefix a qualified
namespace.

The xmlns attribute in the second <table> element gives the f: prefix a qualified
namespace.

When a namespace is defined for an element, all child elements with the same prefix are
associated with the same namespace.

9
 XML Validator

Use our XML validator to syntax-check your XML.

Well Formed XML Documents

An XML document with correct syntax is called "Well Formed".

The syntax rules were described in the previous chapters:

 XML documents must have a root element

 XML elements must have a closing tag
 XML tags are case sensitive
 XML elements must be properly nested
 XML attribute values must be quoted

Example 1:

<?xml version=”1.0”?>

<book>
<title>Java</Title>
<author>James</book>
<pirce>570
</author>

The above XML document is not a well formed document. Reasons given below...
 tags are not matching <title> … </Title>
 There is no proper nesting <author>….</book>
 Tag doesn’t closed <price>

Example 2:

<?xml version=”1.0”?>
<book>
<title>Java</title>
<author>James</author>
<price>500</price>
</book>

The above XML document is a well formed document.

Valid XML Documents

A "well formed" XML document is not the same as a "valid" XML document.

A "valid" XML document must be well formed. In addition, it must conform to a document
type definition.

10
There are two different document type definitions that can be used with XML:

 DTD - The original Document Type Definition

 XML Schema - An XML-based alternative to DTD

A document type definition defines the rules and the legal elements and attributes for an
XML document.

XML DTD: (Document Type Definition)

 An XML document with correct syntax is called "Well Formed".

 An XML document validated against a DTD is both "Well Formed" and "Valid".
 A "Valid" XML document is a "Well Formed" XML document, which also conforms to
the rules of a DTD.
 DTD is the basic building block of XML.

 The purpose of a DTD is to define the structure of an XML document. It defines the
structure with a list of legal elements.

--------------------------------------------------------------------------

<!DOCTYPE book
[
<!ELEMENT book
(title,author,price)> <!ELEMENT
title (#PCDATA)> <!ELEMENT author
(#PCDATA)> <!ELEMENT price
(#PCDATA)> ]>

-------------------------------------------------------------

The DTD above is interpreted like this:

 !DOCTYPE book defines that the root element of the document is book
 !ELEMENT book defines that the book element must contain the elements:
"title, author, price”
 !ELEMENT title defines the title element to be of type "#PCDATA"
 !ELEMENT author defines the author element to be of type "#PCDATA"
 !ELEMENT price defines the price element to be of type "#PCDATA"

Note: PCDATA: Parse able Character Data, CDATA: Character Data.

There are two types of DTDs:

1) Internal / Embedded DTD.

2) External DTD.

11
1) Internal / Embedded DTD.

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE student [
<!ELEMENT student (id,name,age,addr,email,ph)>
<!ELEMENT id (#PCDATA)>
<!ELEMENT name (#PCDATA)>
<!ELEMENT age (#PCDATA)>
<!ELEMENT addr (#PCDATA)>
<!ELEMENT email (#PCDATA)>
<!ELEMENT ph (#PCDATA)> ]>

<student>
<id>543</id>
<name>Ravi</name>
<age>21</age>
<addr>Guntur</addr>
<email>[email protected]</email>
<ph>9855555</ph>
<gender>male</gender>
</student>

2) External DTD.

<!ELEMENT student (id,name,age,addr,email)>

<!ELEMENT id (#PCDATA)>
<!ELEMENT name (#PCDATA)>
<!ELEMENT age (#PCDATA)>
<!ELEMENT addr (#PCDATA)>
<!ELEMENT email (#PCDATA)>

Save the above code as “student.dtd” and prepare “student.xml” as follows...

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE student SYSTEM "student.dtd">
<student>
<id>543</id>
<name>Ravi</name>
<age>21</age>
<addr>Guntur</addr>
<email>[email protected]</email>
</student>

In the above example we are using <!DOCTYPE student SYSTEM "student.dtd">

which is used to provide “student.dtd” code in our “student.xml” file.

If the above xml code follows the exact rules defined in DTD then we can conclude
that our xml document is a valid document. Otherwise it is an invalid document.

12
When to Use a DTD/Schema?

 With a DTD, independent groups of people can agree to use a standard DTD for
interchanging data.
 With a DTD, you can verify that the data you receive from the outside world is valid.
 You can also use a DTD to verify your own data.

XML Schema

An XML Schema describes the structure of an XML document, just like a DTD.

An XML document with correct syntax is called "Well Formed".

An XML document validated against an XML Schema is both "Well Formed" and
"Valid".

XML Schema is commonly known as XML Schema Definition (XSD). It is used to

describe and validate the structure and the content of XML data. XML schema defines
the elements, attributes and data types. Schema element supports Namespaces. It is
similar to a database schema that describes the data in a database.

XML Schema is an XML-based alternative to DTD:

Syntax
You need to declare a schema in your XML document as follows:

<xs:schema>

<xs:element name="book">
<xs:complexType>
<xs:sequence>
<xs:element name="title" type="xs:string"/>
<xs:element name="author" type="xs:string"/>
<xs:element name="price" type="xs:integer"/>
<xs:element name="edition" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

The Schema above is interpreted like this:

 <xs:element name="book"> defines the element called "book" is a root.

 <xs:complexType> the "book" element is a complex type i.e. root
 <xs:sequence> the complex type is a sequence of elements i.e. childrens
 <xs:element name="title" type="xs:string"> the element "title" is of type string (text)
 <xs:element name="author" type="xs:string"> the element "author" is of type string
 <xs:element name="price" type="xs:integer"> the element "price" is of type integer.
 <xs:element name="edition" type="xs:string"> the element "edition" is of type string

13
Example:

---------------------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.w3schools.com" elementFormDefault="qualified">
<xs:element name="student">
<xs:complexType>
<xs:sequence>
<xs:element name="name" type="xs:string"/>
<xs:element name="age" type="xs:integer"/>
<xs:element name="addr">
<xs:complexType>
<xs:sequence>
<xs:element name="city" type="xs:string"/>
<xs:element name="pincode" type="xs:long"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="ph" type="xs:integer"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
-----------------------------------------------------------------------------------------------------

Save the above code as “student.xsd”

Prepare “student.xml” as follows…

-----------------------------------------------------------------------

<?xml version="1.0" encoding="UTF-8"?>

<student xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.w3schools.com student.xsd">
<name>rajesh</name>
<age>25</age>
<addr>
<city>mylavaram</city>
<pincode>53333</pincode>
</add>
<ph>9343434</ph>
</student>
--------------------------------------------------------------------------------------------------------

If the above xml code follows the exact rules defined in “student.xsd” then we can
conclude that our xml document is a valid document. Otherwise it is an invalid
document.

14
 XSLT Introduction

XSL (EXtensible Stylesheet Language) is a styling language for XML.

XSLT stands for XSL Transformations. CSS = Style Sheets for HTML

HTML uses predefined tags. The meaning of, and how to display each tag is well
understood.

CSS is used to add styles to HTML elements.

XSL = Style Sheets for XML

XML does not use predefined tags, and therefore the meaning of each tag is not well
understood.

A <table> element could indicate an HTML table, a piece of furniture, or something else -
and browsers do not know how to display it!

So, XSL describes how the XML elements should be displayed.

What is XSLT?

 XSLT stands for XSL Transformations

 XSLT is the most important part of XSL
 XSLT transforms an XML document into another XML document
 XSLT uses XPath to navigate in XML documents
 XSLT is a W3C Recommendation

Example:

<?xml version="1.0" encoding="UTF-8"?>

<?xml-stylesheet type="text/xsl" href="book.xsl"?>

<book_store>
<book>
<title>JAVA</title>
<author>James</author>
</book>
<book>
<title>DBMS</title>
<author>Raghu</author>

</book>
</book_store>

Save the above code as “book.xml” and prepare the style sheet for this xml file.

15
<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet
version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<html>
<body>
<h2>Book Details</h2>
<table border="1">
<tr>
<th>Title</th>
<th>Author</th>
</tr>
<xsl:for-each select="book_store/book">
<tr>
<td><xsl:value-of select="title"/></td>
<td><xsl:value-of select="author"/></td>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>

Save the above file as “book.xsl”

Now open “book.xml” file through any browser, observe the output as given below

Title Author
Java James
DBMS Raghu

XML DOM Parser

The DOM defines a standard for accessing and manipulating documents:

The HTML DOM defines a standard way for accessing and manipulating HTML documents.
It presents an HTML document as a tree-structure.

The XML DOM defines a standard way for accessing and manipulating XML documents. It
presents an XML document as a tree-structure.

The HTML DOM

All HTML elements can be accessed through the HTML DOM.

This example changes the value of an HTML element with id="demo":

16
Example:
<!DOCTYPE html>
<html>
<body>

<h1 id="demo">This is a Heading</h1>

<button type="button"
onclick="document.getElementById('demo').innerHTML = 'Hello World!'">Click Me!
</button>

</body>
</html>

The XML DOM

All XML elements can be accessed through the XML DOM.

The XML DOM is:

 A standard object model for XML

 A standard programming interface for XML
 Platform- and language-independent
 A W3C standard

In other words: The XML DOM is a standard for how to get, change, add, or
delete XML elements.

Programming Interface

The DOM models XML as a set of node objects. The nodes can be accessed with
JavaScript or other programming languages.

The programming interface to the DOM is defined by a set standard properties and
methods.

Properties are often referred to as something that is (i.e. nodename is "book").

Methods are often referred to as something that is done (i.e. delete "book").

XML DOM Properties

These are some typical DOM properties:

 x.nodeName - the name of x

 x.nodeValue - the value of x
 x.parentNode - the parent node of x
 x.childNodes - the child nodes of x
 x.attributes - the attributes nodes of x

Note: In the list above, x is a node object.

17
XML DOM Methods

 x.getElementsByTagName(name) - get all elements with a specified tag name

 x.appendChild(node) - insert a child node to x
 x.removeChild(node) - remove a child node from x

Note: In the list above, x is a node object.

DOM Example:

<html>
<body>

<p id="demo">this is paragraph text</p>

<button type="button" onclick="myfun()">click me</button>

<script>
function myfun()
{
var text, parser, xmlDoc;

text = "<bookstore><book>" +
"<title>Java</title>" +
"<author>James</author>" +
"<year>1991</year>" +
"<book>" +
"<title>DBMS</title>" +
"<author>Raghu</author>" +
"<year>1970</year>" +
"</book>"+
"</book></bookstore>";

parser = new DOMParser();

xmlDoc = parser.parseFromString(text,"text/xml");

document.getElementById("demo").innerHTML =
xmlDoc.getElementsByTagName("year")[0].childNodes[0].nodeValue;
}
</script>
</body>
</html>
Output:
1991

Example Explained

 xmlDoc - the XML DOM object created by the parser.

 getElementsByTagName("year")[0] - get the first <year> element
 childNodes[0] - the first child of the <year> element (the text node)
 nodeValue - the value of the node (the text itself)

18
 CRUD Operations in Java using JDBC:
CRUD is the acronym for the following four operations.
1. C- INSERTION
2. R- RETRIEVAL
3. U- UPDATION
4. D- DELETION

1. Creating a sample MySQL database

Let’s create a MySQL database called SampleDB with one
table Users. Execute the following SQL script inside MySQL
Workbench:
create database SampleDB;

use SampleDB;

CREATE TABLE `users` (

`user_id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(45) NOT NULL,
`password` varchar(45) NOT NULL,
`fullname` varchar(45) NOT NULL,
`email` varchar(45) NOT NULL,
PRIMARY KEY (`user_id`)
);

2.Connecting to the database

Supposing the MySQL database server is listening on the default port 3306 at localhost. The
following code snippet connects to the database name SampleDB by the user root and
password secret:
String dbURL = "jdbc:mysql://localhost:3306/ampledb";
String username = "root";
String password = "secret";

try {

Connection conn = DriverManager.getConnection(dbURL, username,

password);

if (conn != null) {
System.out.println("Connected");
}
 } catch (SQLException ex) {
ex.printStackTrace();
}

Once the connection was established, we have a Connection object which can be used to
create statements in order to execute SQL queries. In the above code, we have to close the
connection explicitly after finish working with the database:
conn.close();

we can take advantage of the try-with-resources statement which will close the connection
automatically, as shown in the following code snippet:

try (Connection conn = DriverManager.getConnection(dbURL, username, password)) {

// code to execute SQL queries goes here...

} catch (SQLException ex) {

ex.printStackTrace();
}

3. JDBC Execute INSERT Statement Example

Let’s write code to insert a new record into the table Users with following details:

username: bill
password: secretpass
fullname: Bill Gates
email: [email protected]

Here’s the code snippet:

String sql = "INSERT INTO Users (username, password, fullname, email) VALUES (?, ?,
?, ?)";

PreparedStatement statement = conn.prepareStatement(sql);

statement.setString(1, "bill");
statement.setString(2, "secretpass");
statement.setString(3, "Bill Gates");
statement.setString(4, "[email protected]");

int rowsInserted = statement.executeUpdate();

if (rowsInserted > 0) {
System.out.println("A new user was inserted successfully!");
 }

In this code, we create a parameterized SQL INSERT statement and create

a PreparedStatement from the Connection object. To set values for the parameters in the
INSERT statement, we use the PreparedStatement‘s setString() methods because all
these columns in the table Users are of type VARCHAR which is translated to String type in
Java. Note that the parameter index is 1-based (unlike 0-based index in Java array).
The PreparedStatement interface provides various setXXX() methods corresponding to each
data type, for example:

o setBoolean(int parameterIndex, boolean x)

o setDate(int parameterIndex, Date x)
o setFloat(int parameterIndex, float x)
o …

And so on. Which method to be used is depending on the type of the corresponding column in
the database table.
Finally we call the PreparedStatement’s executeUpdate() method to execute the INSERT
statement. This method returns an update count indicating how many rows in the table were
affected by the query, so checking this return value is necessary to ensure the query was
executed successfully. In this case, executeUpdate() method should return 1 to indicate one
record was inserted.
4. JDBC Execute SELECT Statement Example

The following code snippet queries all records from the Users table and print out details for each
record:
String sql = "SELECT * FROM Users";

Statement statement = conn.createStatement();

ResultSet result = statement.executeQuery(sql);

int count = 0;

while (result.next()){
String name = result.getString(2);
String pass = result.getString(3);
String fullname = result.getString("fullname");
String email = result.getString("email");

String output = "User #%d: %s - %s - %s - %s";

System.out.println(String.format(output, ++count, name, pass, fullname, email));
}

Output:
User #1: bill - secretpass - Bill Gates - [email protected]
Because the SQL SELECT query here is static so we just create a Statement object from the
connection. The while loop iterates over the rows contained in the result set by repeatedly
checking return value of the ResultSet’s next() method. The next() method moves a cursor
forward in the result set to check if there is any remaining record. For each iteration, the result
set contains data for the current row, and we use the ResultSet’s getXXX(column
index/column name) method to retrieve value of a specific column in the current row.

For other data types, the ResultSet provide appropriate getter methods:

o getString()
o getInt()
o getFloat()
o getDate()
o getTimestamp()
o …

5.JDBC Executing UPDATE Statement Example

The following code snippet will update the record of “Bill Gates” as we inserted previously

String sql = "UPDATE Users SET password=?, fullname=?, email=? WHERE username=?";

PreparedStatement statement = conn.prepareStatement(sql);

statement.setString(1, "123456789");
statement.setString(2, "William Henry Bill Gates");
statement.setString(3, "[email protected]");
statement.setString(4, "bill");

int rowsUpdated = statement.executeUpdate();

if (rowsUpdated > 0) {
System.out.println("An existing user was updated successfully!");
}

This code looks very similar to the INSERT code above, except the query type is UPDATE.

6.JDBC Execute DELETE Statement Example

The following code snippet will delete a record whose username field contains “bill”:

String sql = "DELETE FROM Users WHERE username=?";

PreparedStatement statement = conn.prepareStatement(sql);

statement.setString(1, "bill");

int rowsDeleted = statement.executeUpdate();

if (rowsDeleted > 0) {
System.out.println("A user was deleted successfully!");
}
So far we have one through some examples demonstrating how to use JDBC API to execute
SQL INSERT, SELECT, UPDATE and DELETE statements. The key points to remember are:

o Using a Statement for a static SQL query.
o Using a PreparedStatement for a parameterized SQL query and
using setXXX() methods to set values for the parameters.
o Using execute() method to execute general query.
o Using executeUpdate() method to execute INSERT, UPDATE or
DELETE query
o Using executeQuery() method to execute SELECT query.
o Using a ResultSet to iterate over rows returned from a SELECT query,
using its next() method to advance to next row in the result set, and
using getXXX() methods to retrieve values of columns.

Statement Vs PreparedStatement Vs CallableStatement In Java

1) Statement – Used to execute normal SQL queries.

2) PreparedStatement – Used to execute dynamic or parameterized SQL queries.

3) CallableStatement – Used to execute the stored procedures.

1) Statement

Statement interface is used to execute normal SQL queries. You can’t pass the parameters to SQL
query at run time using this interface. This interface is preferred over other two interfaces if you
are executing a particular SQL query only once. The performance of this interface is also very less
compared to other two interfaces. In most of time, Statement interface is used for DDL statements
like CREATE, ALTER, DROP etc. For example,

//Creating The Statement Object

Statement stmt = con.createStatement();

//Executing The Statement

stmt.executeUpdate("CREATE TABLE STUDENT(ID NUMBER NOT NULL, NAME VARCHAR)");

2) PreparedStatement

PreparedStatement is used to execute dynamic or parameterized SQL queries.

PreparedStatement extends Statement interface. You can pass the parameters to SQL query at
run time using this interface. It is recommended to use PreparedStatement if you are executing a
particular SQL query multiple times. It gives better performance than Statement interface.
Because, PreparedStatement are precompiled and the query plan is created only once irrespective
of how many times you are executing that query. This will save lots of time.

Creating PreparedStatement object

PreparedStatement pstmt = con.prepareStatement("update STUDENT set NAME = ?

where ID = ?");

//Setting values to place holders using setter methods of PreparedStatement

object

pstmt.setString(1, "MyName"); //Assigns "MyName" to first place holder

pstmt.setInt(2, 111); //Assigns "111" to second place holder

//Executing PreparedStatement

pstmt.executeUpdate();

3) CallableStatement:
CallableStatement in JDBC is an interface present in a java.sql package and it is the child interface of
Prepared Statement. Callable Statement is used to execute the Stored Procedure and functions.
Similarly to method stored procedure has its own parameters. Stored Procedure has 3 types of
parameters.

1) IN parameter: IN parameter is used to provide input values.

2) OUT parameter: OUT parameter is used to collect output values.
3) INOUT parameter: It is used to provide input and to collect output values.

Before calling the stored procedure, you must register OUT parameters using registerOutParameter()
method of CallableStatement. let's take an example, a stored procedure called addpro() is created
which can take 2 input values and provides the addition of 2 number as an output.

we will call the stored procedure addpro() and set the input values by setter method and
execute the stored procedure. This procedure will give the addition of 2 numbers.
 Transaction Management in JDBC

A transaction is a group of operation used to performed one

task if all operations in the group are success then the task is
finished and the transaction is successfully completed. If any
one operation in the group is failed then the task is failed and
the transaction is failed.
Suppose a movie ticket booking at online is a transaction. This task contains four operation.

 Verify the seats

 Reserve the seats

 Payment

 Issue tickets

If all the above four operations are done successfully then a transaction is finished successfully.

In the middle, if any one operation is failed then all operation are canceled and finally a

transaction is failed.

Properties of Transaction managements

Every transaction follows some transaction properties these are called ACID properties.
Atomicity: Atomicity of a transaction is nothing but in a transaction either all operations can be

done or all operation can be undone, but some operations are done and some operation are

undone should not occure.

Consistency: The database should be in a valid state before and after the performed

transaction.
Isolation: Each transaction should execute in complete isolation without knowing the

existence of other transactions.

Durability: Once the transaction is complete, the changes made by the transaction are

permanent (even in the occurrence of unusual events such as power loss).

To do transaction management in Jdbc, we need to follow the below

steps.

 Step 1: Disable auto commit mode of Jdbc

 Step 2: Put all operation of a transaction in try block.
 Step 3: If all operation are done successfully then commit in
try block, otherwise rollback in catch block.

By default in Jdbc autocommit mode is enabled but we need to disable it. To disable call

setAutoCommit() method of connection Interface.

Example

con.setAutoCommit(false);

To commit a transaction, call commit() and to rollback a transaction, call rollback() method of

connection Interface respectively.

Example

con.commit();
con.rollback();
Example Transaction Management in JDBC

import java.sql.*;
class TrxaExample
{
public static void main(String[] args)throws Exception
{
Class.forName("oracle.jdbc.OracleDriver");
Connection con =DriverManager.getConnection("jdbc:oracle:thin:@rama-
pc:1521:xe","system","system");
System.out.println("driver is loaded");
Statement stmt=con.createStatement();
con.setAutoCommit(false);
try
{
int i1=stmt.executeUpdate("insert into student values(110,'rama',685)");
int i2=stmt.executeUpdate("update customer set custadd='Hyderabad'where custid=111");
int i3=stmt.executeUpdate("delete from student where sid=101");
con.commit();
System.out.println("Transaction is success");
}//end of try
catch (Exception e)
{
try
{
con.rollback();
System.out.println("Trasaction is failed");
}
catch (Exception ex)
{
System.out.println(ex);
}
}//end of catch
stmt.close();
con.close();
System.out.println("connection is closed");
} //end of main
} //end of class