Department of Collegiate and Technical Education Diploma in CS&E

Course: OOP and Design with Java Code: 20CS43P

WEEK-13: Database Connectivity
Session -2

How Does JDBC Work?

JDBC makes it possible to establish a connection with a data source, send queries and update
statements, and process the results.
Simply, JDBC makes it possible to do the following things within a Java application:

 Establish a connection with a data source

 Send queries and update statements to the data source
 Process the results

The following figure shows the components of the JDBC model.

[OOP and Design with Java-20CS43P] Page 1

 Department of Collegiate and Technical Education Diploma in CS&E

The Java application calls JDBC classes and interfaces to submit SQL statements and retrieve results.
The JDBC API is implemented through the JDBC driver. The JDBC Driver is a set of classes that
implement the JDBC interfaces to process JDBC calls and return result sets to a Java application. The
database (or data store) stores the data retrieved by the application using the JDBC Driver.

The main objects of the JDBC API include:

 A Data Source object is used to establish connections. Although the Driver Manager can also be used
to establish a connection, connecting through a Data Source object is the preferred method.

 A Connection object controls the connection to the database. An application can alter the behavior of
a connection by invoking the methods associated with this object. An application uses the connection
object to create statements.

 Statement, Prepared Statement, and Callable Statement objects are used for executing SQL
statements. A Prepared Statement object is used when an application plans to reuse a statement multiple
times. The application prepares the SQL it plans to use. Once prepared, the application can specify
values for parameters in the prepared SQL statement. The statement can be executed multiple times
with different parameter values specified for each execution. A Callable Statement is used to call stored
procedures that return values. The Callable Statement has methods for retrieving the return values of
the stored procedure.

 A Result Set objects contains the results of a query. A Result Set is returned to an application when a
SQL query is executed by a statement object. The Result Set object provides methods for iterating
through the results of the query.

[OOP and Design with Java-20CS43P] Page 2

Department of Collegiate and Technical Education Diploma in CS&E

What is JDBC Driver?

JDBC drivers implement the defined interfaces in the JDBC API, for interacting with your
database server.
For example, using JDBC drivers enable you to open database connections and to interact with it by
sending SQL or database commands then receiving results with Java.
The [Link] package that ships with JDK, contains various classes with their behaviors defined and
their actual implementations are done in third-party drivers. Third party vendors implement
the [Link] interface in their database driver.

JDBC Drivers Types

JDBC driver implementations vary because of the wide variety of operating systems and hardware
platforms in which Java operates. Sun has divided the implementation types into four categories,

There are 4 types of JDBC drivers:

• JDBC-ODBC bridge driver

• Native-API driver (partially java driver)
• Network Protocol driver (fully java driver)
• Thin driver (fully java driver)

JDBC-ODBC Bridge Driver

JDBC-ODBC bridge driver is a native code driver which uses ODBC driver to connect with the
database. It converts JDBC method calls into ODBC function calls. It is also known as Type 1 driver.

Advantages:

1. It can be used with any database for which an ODBC driver is installed.

Disadvantages:

1. Performance is not good as it converts JDBC method calls into ODBC function calls.

2. ODBC driver needs to be installed on the client machine.

3. Platform dependent.

[OOP and Design with Java-20CS43P] Page 3

Department of Collegiate and Technical Education Diploma in CS&E

The JDBC-ODBC Bridge that comes with JDK 1.2 is a good example of this kind of driver.

Native API-Driver

Native-API driver uses the client-side libraries of the database. It converts JDBC method calls into
native calls of the database API. It is partially written in java. It is also known as Type 2 driver.

Advantages:

1. It is faster than a JDBC-ODBC bridge driver.

Disadvantages:

1. Platform dependent.
2. The vendor client library needs to be installed on the client machine.

[OOP and Design with Java-20CS43P] Page 4

Department of Collegiate and Technical Education Diploma in CS&E

The Oracle Call Interface (OCI) driver is an example of a Type 2 driver.

Network Protocol Driver

Network-Protocol driver is a pure java driver which uses a middle-tier to converts JDBC calls directly
or indirectly into database specific calls. It is also known as Type 3

Advantages:

1. Platform independent.
2. Faster from Type1 and Type2 drivers.
3. It follows a three tier communication approach.
4. Multiple types of databases can be accessed at the same time.

Disadvantages:

1. It requires database-specific coding to be done in the middle tier.

[OOP and Design with Java-20CS43P] Page 5

Department of Collegiate and Technical Education Diploma in CS&E

Thin Driver

Thin driver is a pure java driver which converts JDBC calls directly into the database specific calls. It
is a platform independent driver. It is also known as Type 4 or Database-Protocol driver.

Advantages:

1. Platform independent.
2. Faster than all other drivers.

Disadvantages:

1. It is database dependent.
2. Multiple types of databases can’t be accessed at the same time.

[OOP and Design with Java-20CS43P] Page 6

Department of Collegiate and Technical Education Diploma in CS&E

MySQL's Connector/J driver is a Type 4 driver. Because of the proprietary nature of their network
protocols, database vendors usually supply type 4 drivers.

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.
[OOP and Design with Java-20CS43P] Page 7
Department of Collegiate and Technical Education Diploma in CS&E

Java Database Connectivity with 5 Steps

There are 5 steps to connect any java application with the database using JDBC. These steps are as follows:
 Register the Driver class
 Create connection
 Create statement
 Execute queries
 Close connection

[OOP and Design with Java-20CS43P] Page 8

Department of Collegiate and Technical Education Diploma in CS&E

Steps to connect Java program and database:

1. Loading the Driver

We first need to load the driver or register it before using it in the program. There should be registration
once in your program. We can register a driver in any of the two ways:
a. [Link](): In this, we load the driver’s class file into memory during runtime. There is no
need to use a new operator for the creation of an object. The following shows the use of [Link]()
to load the Oracle driver:
[Link](“[Link]”);

b. [Link](): DriverManager is an inbuilt class of Java that comes with a static

member register. We call the drivers class’ constructor at compile-time. The following example shows
the use of [Link]() to register the Oracle driver:
[Link](new [Link]())
[OOP and Design with Java-20CS43P] Page 9
Department of Collegiate and Technical Education Diploma in CS&E

2. Create the connections

After loading the driver, we need to establish connections using the following code:
Connection con = [Link](url, user, password)
 user: username from which sql command prompt can be accessed.
 password: password from which sql command prompt can be accessed.
 con: reference to Connection interface.
 url : Uniform Resource Locator. We can create it as follows:
 String url = “ jdbc:oracle:thin:@localhost:1521:xe”

3. Create a statement
Once you establish a connection, you can interact with the database. The JDBCStatement,
CallableStatement, and PreparedStatement interfaces define the methods that allow us to send the SQL
commands and receive data from the database.
Use of JDBC Statement is as follows:
Statement statement = [Link]()
Here, con is a reference to the Connection interface that we used in the previous step.

4. Execute the query

The most crucial part is executing the query. Here, Query is an SQL Query. Now, as we know that we
can have multiple types of queries. Some of them are as follows:
 The query for updating or inserting tables in a database.
 The query for retrieving data from the database.
The executeQuery() method of the Statement interface executes queries of retrieving values from the
database. The executeQuery() method returns the object of ResultSet that we can use to get all the
records of a table.

5. Close the connections

Till now, we have sent the data to the specified location. Now, we are about to complete our task. We
need to close the connection. By closing the connection, objects of Statement and ResultSet interface
are automatically closed. The close() method of Connection interface closes the connection.
Example :
[Link]();

[OOP and Design with Java-20CS43P] Page 10

Department of Collegiate and Technical Education Diploma in CS&E

Course: OOP and Design with Java Code: 20CS43P

WEEK-13: Database Connectivity
Session -3

Practical Application of JDBC

Pre-Requirements

Java must be installed

Oracle must be installed

Now moving ahead in this JDBC let us learn working of JDBC. A Java application that communicates
with the database requires programming using JDBC API.
We need to add Supporting data sources of JDBC Driver such as Oracle and SQL server in Java
application for JDBC support. We can do this dynamically at run time. This JDBC driver intelligently
interacts with the respective data source.

Creating a simple JDBC application

import [Link]. * ;
public class JDBCTutorial {
public static void main(String args[]) throws ClassNotFoundException,
SQLException,
{
String driverName = "[Link]";
String url = "jdbc:odbc:XE";
String username = "John";
String password = "john12";
String query1 = "insert into students values (101, 'Pooja')";
//Load the driver class
[Link](driverName);
//Obtaining a connection
Connection con = [Link](url, username, password);
//Obtaining a statement
Statement stmt = [Link]();
[OOP and Design with Java-20CS43P] Page 1
Department of Collegiate and Technical Education Diploma in CS&E

//Executing the query

int count = [Link](query1);
[Link]("The number of rows affected by this query= " + count);
//Closing the connection
[Link]();
}
}

The above example shows the basic steps to access a database using JDBC. We used the JDBC-
ODBC bridge driver to connect to the database. We have to import the [Link] package that provides
basic SQL functionality.

Principal JDBC Interfaces and Classes

Let us take an overview look at the principal interfaces and classes of JDBC. They are all present in
the [Link] package.

1. [Link]()
This method loads the driver’s class file into memory at runtime. There is no need to use new or
creation of objects.
[Link]("[Link]");

2. DriverManager
The DriverManager class registers drivers for a specific database type. For example, Oracle Database
in this. This class also establishes a database connection with the server using its getConnection()
method.

3. Connection
The Connection interface represents an established database connection. Using this connection, we
can create statements to execute queries and retrieve results. We can also get metadata about the
database, close the connection, etc.
Connection con = [Link]
("jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pwd1");

[OOP and Design with Java-20CS43P] Page 2

Department of Collegiate and Technical Education Diploma in CS&E

4. Statement and PreparedStatement

The Statement and PreparedStatement interfaces execute a static SQL query and parameterized SQL
queries. The statement interface is the super interface of the PreparedStatement interface.

The commonly used methods of these interfaces are:

a. boolean execute(String sql): This method executes a general SQL statement. It returns true if the
query returns a ResultSet and false if the query returns nothing. We can use this method with a
Statement only.

b. int executeUpdate(String sql): This method executes an INSERT, UPDATE, or DELETE

statement. It then returns an updated account showing the number of rows affected. For example, 1
row inserted, or 2 rows updated, or 0 rows affected, etc.

c. ResultSet executeQuery(String sql): This method executes a SELECT statement and returns an
object of ResultSet. This returned object contains results returned by the query.

5. ResultSet
The ResultSet is an interface that contains table data returned by a SELECT query. We use the object
of ResultSet to iterate over rows using the next() method.

6. SQLException
The SQLException class is a checked exception. We declare it to so all the above methods can throw
this exception. We have to provide a mechanism to explicitly catch this exception when we call the
methods of the above classes.

[OOP and Design with Java-20CS43P] Page 3

Department of Collegiate and Technical Education Diploma in CS&E

Implementing Insert Statement in JDBC

import [Link]. * ;
public class InsertStatementDemo {
public static void main(String args[]) {
String id = "id1";
String password = "pswd1";
String fullname = "TechVidvan";
String email = "[Link]";
try {
[Link]("[Link]");
Connection con = [Link]("
jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pswd1");
Statement stmt = [Link]();
// Inserting data in database
String s1 = "insert into userid values('" + id + "', '" + password + "', '" + fullname + "', '" + email + "')";
int result = [Link](s1);
if (result > 0) [Link]("Successfully Registered");
else [Link]("Insertion Failed");
[Link]();
}
catch(Exception e) {
[Link](e);
}
}
}
Output:
Successfully Registered

[OOP and Design with Java-20CS43P] Page 4

Department of Collegiate and Technical Education Diploma in CS&E

Implementing Update Statement in JDBC

package [Link];
import [Link]. * ;
public class UpdateStatementDemo {
public static void main(String args[]) {
String id = "id1";
String password = "pswd1";
String newPassword = "newpswd";
try {
[Link]("[Link]");
Connection con = [Link]("
jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pswd1");
Statement stmt = [Link]();
// Updating database
String s1 = "UPDATE userid set password = '" + newPassword + "' WHERE id = '" + id + "' AND
password = '" + password + "'";
int result = [Link](s1);
if (result > 0) [Link]("Password Updated Successfully ");
else [Link]("Error Occured!!Could not update");
[Link]();
}
catch(Exception e) {
[Link](e);
}
}
}

[OOP and Design with Java-20CS43P] Page 5

Department of Collegiate and Technical Education Diploma in CS&E

To start developing with JDBC, you should setup your JDBC environment by following the steps shown
below. We assume that you are working on a Windows platform.

Install Java
Java SE is available for download for free. To download click here, please download a version
compatible with your operating system.
Follow the instructions to download Java, and run the .exe to install Java on your machine. Once you
have installed Java on your machine, you would need to set environment variables to point to correct
installation directories.

Setting Up the Path for Windows 2000/XP

Assuming you have installed Java in c:\Program Files\java\jdk directory −
 Right-click on 'My Computer' and select 'Properties'.
 Click on the 'Environment variables' button under the 'Advanced' tab.
 Now, edit the 'Path' variable and add the path to the Java executable directory at the end of it.
For example, if the path is currently set to C:\Windows\System32, then edit it the following way
C:\Windows\System32;c:\Program Files\java\jdk\bin

Setting Up the Path for Windows 95/98/ME

Assuming you have installed Java in c:\Program Files\java\jdk directory −
 Edit the 'C:\[Link]' file and add the following line at the end −
SET PATH = %PATH%;C:\Program Files\java\jdk\bin

Setting Up the Path for Linux, UNIX, Solaris, FreeBSD

Environment variable PATH should be set to point to where the Java binaries have been installed. Refer
to your shell documentation if you have trouble doing this.
For example, if you use bash as your shell, then you would add the following line at the end of
your .bashrc −
export PATH = /path/to/java:$PATH'

You automatically get both JDBC packages [Link] and [Link], when you install J2SE
Development Kit.

[OOP and Design with Java-20CS43P] Page 6

Department of Collegiate and Technical Education Diploma in CS&E

Install Database
The most important thing you will need, of course is an actual running database with a table that you
can query and modify.
Install a database that is most suitable for you. You can have plenty of choices and most common are

 MySQL DB − MySQL is an open source database. You can download it from MySQL Official
Site. We recommend downloading the full Windows installation.

In addition, download and install MySQL Administrator as well as MySQL Query

Browser. These are GUI based tools that will make your development much easier.

Finally, download and unzip MySQL Connector/J (the MySQL JDBC driver) in a convenient
directory. For the purpose we will assume that you have installed the driver at C:\Program
Files\MySQL\mysql-connector-java-5.1.8.

Accordingly, set CLASSPATH variable to C:\Program Files\MySQL\mysql-connector-java-

5.1.8\[Link].

Your driver version may vary based on your installation.

Set Database Credential

When we install MySQL database, its administrator ID is set to root and it gives provision to set a
password of your choice.
Using root ID and password you can either create another user ID and password, or you can use root ID
and password for your JDBC application.
There are various database operations like database creation and deletion, which would need
administrator ID and password.
For rest of the JDBC, we would use MySQL Database with guest as ID and guest123 as password.
If you do not have sufficient privilege to create new users, then you can ask your Database Administrator
(DBA) to create a user ID and password for you.

[OOP and Design with Java-20CS43P] Page 7

Department of Collegiate and Technical Education Diploma in CS&E

Create Database

To create the TUTORIALSPOINT database, use the following steps −

Step 1
Open a Command Prompt and change to the installation directory as follows −
C:\>
C:\>cd Program Files\MySQL\bin
C:\Program Files\MySQL\bin>

Note − The path to [Link] may vary depending on the install location of MySQL on your system.
You can also check documentation on how to start and stop your database server.

Step 2
Start the database server by executing the following command, if it is already not running.
C:\Program Files\MySQL\bin>mysqld
C:\Program Files\MySQL\bin>

Step 3
Create the TUTORIALSPOINT database by executing the following command −
C:\Program Files\MySQL\bin> mysqladmin create TUTORIALSPOINT -u guest -p
Enter password: ********
C:\Program Files\MySQL\bin>

Create Table
To create the Employees table in TUTORIALSPOINT database, use the following steps −
Step 1
Open a Command Prompt and change to the installation directory as follows −
C:\>
C:\>cd Program Files\MySQL\bin
C:\Program Files\MySQL\bin>

[OOP and Design with Java-20CS43P] Page 8

Department of Collegiate and Technical Education Diploma in CS&E

Step 2
Login to the database as follows −
C:\Program Files\MySQL\bin>mysql -u guest -p
Enter password: ********
mysql>

Step 3
Create the table Employees as follows −
mysql> use TUTORIALSPOINT;
mysql> create table Employees
-> (
-> id int not null,
-> age int not null,
-> first varchar (255),
-> last varchar (255)
-> );
Query OK, 0 rows affected (0.08 sec)
mysql>

Create Data Records

Finally you create few records in Employee table as follows −
mysql> INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO Employees VALUES (101, 25, 'Mahnaz', 'Fatma');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Employees VALUES (102, 30, 'Zaid', 'Khan');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Employees VALUES (103, 28, 'Sumit', 'Mittal');

Query OK, 1 row affected (0.00 sec)

[OOP and Design with Java-20CS43P] Page 9

Department of Collegiate and Technical Education Diploma in CS&E

mysql>

Creating JDBC Application

There are following six steps involved in building a JDBC application −
 Import the packages − Requires that you include the packages containing the JDBC classes
needed for database programming. Most often, using import [Link].* will suffice.
 Open a connection − Requires using the [Link]() method to create a
Connection object, which represents a physical connection with the database.
 Execute a query − Requires using an object of type Statement for building and submitting an
SQL statement to the database.
 Extract data from result set − Requires that you use the
appropriate [Link]() method to retrieve the data from the result set.
 Clean up the environment − Requires explicitly closing all database resources versus relying
on the JVM's garbage collection.

Sample Code
This sample example can serve as a template when you need to create your own JDBC application in
the future.
This sample code has been written based on the environment and database setup done in the previous
chapter.
Copy and paste the following example in [Link], compile and run as follows −
import [Link].*;

public class FirstExample {

static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
static final String USER = "guest";
static final String PASS = "guest123";
static final String QUERY = "SELECT id, first, last, age FROM Employees";

public static void main(String[] args) {

// Open a connection
try(Connection conn = [Link](DB_URL, USER, PASS);

[OOP and Design with Java-20CS43P] Page 10

Department of Collegiate and Technical Education Diploma in CS&E

Statement stmt = [Link]();

ResultSet rs = [Link](QUERY);) {
// Extract data from result set
while ([Link]()) {
// Retrieve by column name
[Link]("ID: " + [Link]("id"));
[Link](", Age: " + [Link]("age"));
[Link](", First: " + [Link]("first"));
[Link](", Last: " + [Link]("last"));
}
} catch (SQLException e) {
[Link]();
}
}
}
Now let us compile the above example as follows −
C:\>javac [Link]
C:\>

When you run FirstExample, it produces the following result −

C:\>java FirstExample

Connecting to database...

Creating statement...

ID: 100, Age: 18, First: Zara, Last: Ali

ID: 101, Age: 25, First: Mahnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
C:\>

[OOP and Design with Java-20CS43P] Page 11

Department of Collegiate and Technical Education Diploma in CS&E

Course: OOP and Design with Java Code: 20CS43P

WEEK-13: Database Connectivity
Practical Session -2

In Practical session -1 we have seen what are various software and tools are required to
execute java programs using database with the help of JDBC.

1. Type this program and save as [Link] in a specified folder where your other java programs are
saved.

Creating a simple JDBC application

Program 1: Jdbc application program to insert records into student database.
import [Link]. * ;
public class JDBCTutorial {
public static void main(String args[]) throws ClassNotFoundException,
SQLException,
{
String driverName = "[Link]";
String url = "jdbc:odbc:XE";
String username = "John";
String password = "john12";
String query1 = "insert into students values (101, 'Pooja')";
//Load the driver class
[Link](driverName);
//Obtaining a connection
Connection con = [Link](url, username, password);
//Obtaining a statement
Statement stmt = [Link]();
//Executing the query
int count = [Link](query1);
[Link]("The number of rows affected by this query= " + count);

[OOP and Design with Java-20CS43P] Page 1

Department of Collegiate and Technical Education Diploma in CS&E

//Closing the connection

[Link]();
}
}

The above program is used to access a database using JDBC. We used the JDBC-ODBC bridge driver
to connect to the database. We have to import the [Link] package that provides basic SQL
functionality.

2. This program is used to perform database operations

Implementing Insert Statement in JDBC

import [Link]. * ;
public class InsertStatementDemo {
public static void main(String args[]) {
String id = "id1";
String password = "pswd1";
String fullname = "TechVidvan";
String email = "[Link]";
try {
[Link]("[Link]");
Connection con = [Link]("
jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pswd1");
Statement stmt = [Link]();
// Inserting data in database
String s1 = "insert into userid values('" + id + "', '" + password + "', '" + fullname + "', '" + email + "')";
int result = [Link](s1);
if (result > 0) [Link]("Successfully Registered");
else [Link]("Insertion Failed");
[Link]();
}
catch(Exception e) {
[Link](e);

[OOP and Design with Java-20CS43P] Page 2

Department of Collegiate and Technical Education Diploma in CS&E

}
}
}
Output:
Successfully Registered

Implementing Update Statement in JDBC

package [Link];
import [Link]. * ;
public class UpdateStatementDemo {
public static void main(String args[]) {
String id = "id1";
String password = "pswd1";
String newPassword = "newpswd";
try {
[Link]("[Link]");
Connection con = [Link]("
jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pswd1");
Statement stmt = [Link]();
// Updating database
String s1 = "UPDATE userid set password = '" + newPassword + "' WHERE id = '" + id + "' AND
password = '" + password + "'";
int result = [Link](s1);
if (result > 0) [Link]("Password Updated Successfully ");
else [Link]("Error Occured!!Could not update");
[Link]();
}
catch(Exception e) {

[OOP and Design with Java-20CS43P] Page 3

Department of Collegiate and Technical Education Diploma in CS&E

[Link](e);
}
}
}

Create Database

To create the TUTORIALSPOINT database, use the following steps −

Step 1
Open a Command Prompt and change to the installation directory as follows −
C:\>
C:\>cd Program Files\MySQL\bin
C:\Program Files\MySQL\bin>

Note − The path to [Link] may vary depending on the install location of MySQL on your system.
You can also check documentation on how to start and stop your database server.

Step 2
Start the database server by executing the following command, if it is already not running.
C:\Program Files\MySQL\bin>mysqld
C:\Program Files\MySQL\bin>

Step 3
Create the TUTORIALSPOINT database by executing the following command −
C:\Program Files\MySQL\bin> mysqladmin create TUTORIALSPOINT -u guest -p
Enter password: ********
C:\Program Files\MySQL\bin>

Create Table
To create the Employees table in TUTORIALSPOINT database, use the following steps −

[OOP and Design with Java-20CS43P] Page 4

Department of Collegiate and Technical Education Diploma in CS&E

Step 1
Open a Command Prompt and change to the installation directory as follows −
C:\>
C:\>cd Program Files\MySQL\bin
C:\Program Files\MySQL\bin>

Step 2
Login to the database as follows −
C:\Program Files\MySQL\bin>mysql -u guest -p
Enter password: ********
mysql>

Step 3
Create the table Employees as follows −
mysql> use TUTORIALSPOINT;
mysql> create table Employees
-> (
-> id int not null,
-> age int not null,
-> first varchar (255),
-> last varchar (255)
-> );
Query OK, 0 rows affected
mysql>

Insert values into Employee table

Finally you create few records in Employee table as follows –

mysql> INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');

Query OK, 1 row affected (0.05 sec)

[OOP and Design with Java-20CS43P] Page 5

Department of Collegiate and Technical Education Diploma in CS&E

mysql> INSERT INTO Employees VALUES (101, 25, 'Mahnaz', 'Fatma');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Employees VALUES (102, 30, 'Zaid', 'Khan');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Employees VALUES (103, 28, 'Sumit', 'Mittal');

Query OK, 1 row affected (0.00 sec)

mysql>

Final Program (Sample Code)

This sample example can serve as a template when you need to create your own JDBC application in
the future.
This sample code has been written based on the environment and database setup done in the previous
chapter.

Copy and paste the following example in [Link] compile and run as follows –

import [Link].*;

public class FirstExample {

static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
static final String USER = "guest";
static final String PASS = "guest123";
static final String QUERY = "SELECT id, first, last, age FROM Employees";

public static void main(String[] args) {

// Open a connection
try(Connection conn = [Link](DB_URL, USER, PASS);
Statement stmt = [Link]();

[OOP and Design with Java-20CS43P] Page 6

Department of Collegiate and Technical Education Diploma in CS&E

ResultSet rs = [Link](QUERY);) {
// Extract data from result set
while ([Link]()) {
// Retrieve by column name
[Link]("ID: " + [Link]("id"));
[Link](", Age: " + [Link]("age"));
[Link](", First: " + [Link]("first"));
[Link](", Last: " + [Link]("last"));
}
} catch (SQLException e) {
[Link]();
}
}
}

Now let us compile the above example as follows –

C:\>javac [Link]
C:\>

When you run FirstExample, it produces the following result −

C:\>java FirstExample

Connecting to database...

Creating statement...( Result Output of the program )

ID: 100, Age: 18, First: Zara, Last: Ali

ID: 101, Age: 25, First: Mahnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: KhanID: 103, Age: 28, First: Sumit, Last: Mittal

[OOP and Design with Java-20CS43P] Page 7

Department of Collegiate and Technical Education Diploma in CS&E

[OOP and Design with Java-20CS43P] Page 8