Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E Roll no: 01
Assignment No:12
Title: Two-tier application
Problem Statement: Design a two-tier application using Java, Python, or PHP as the front-end,
with MySQL or MongoDB as the back-end for data storage and retrieval.
Course Objective: To gain knowledge of NoSQL, MongoDB, and modern data storage trends.
Course Outcome: Use knowledge of NoSQL databases, MongoDB operations, and distributed
data systems to address modern storage requirements.
Tools Required: Java, MySql, Eclipse
Theory:
1. What is JDBC?
JDBC stands for Java Database Connectivity, which is a standard Java API for database-
independent connectivity between the Java programming language and a wide range of
databases.
2. Common JDBC Components
The JDBC API provides the following interfaces and classes −
DriverManager: This class manages a list of database drivers. Matches connection
requests from the java application with the proper database driver using communication
sub protocol. The first driver that recognizes a certain sub protocol under JDBC will be
used to establish a database Connection.
Driver: This interface handles the communications with the database server. You will
interact directly with Driver objects very rarely. Instead, you use DriverManager objects,
which manages objects of this type. It also abstracts the details associated with working
with Driver objects.
Connection: This interface with all methods for contacting a database. The connection
object represents communication context, i.e., all communication with database is
through connection object only.
Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 1
Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E Roll no: 01
Statement: You use objects created from this interface to submit the SQL statements to
the database. Some derived interfaces accept parameters in addition to executing stored
procedures.
ResultSet: These objects hold data retrieved from a database after you execute an SQL
query using Statement objects. It acts as an iterator to allow you to move through its data.
SQLException: This class handles any errors that occur in a database application.
3. 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 Java.sql 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
implements the java.sql.Driver interface in their database driver.
Type 1: JDBC-ODBC Bridge Driver
In a Type 1 driver, a JDBC bridge is used to access ODBC drivers installed on each client
machine. Using ODBC, requires configuring on your system a Data Source Name (DSN) that
represents the target database.
When Java first came out, this was a useful driver because most databases only supported
ODBC access but now this type of driver is recommended only for experimental use or when no
other alternative is available.
Type 2: JDBC-Native API
In a Type 2 driver, JDBC API calls are converted into native C/C++ API calls, which are
unique to the database. These drivers are typically provided by the database vendors and used in
the same manner as the JDBC-ODBC Bridge. The vendor-specific driver must be installed on
each client machine.
If we change the Database, we have to change the native API, as it is specific to a
database and they are mostly obsolete now, but you may realize some speed increase with a Type
2 driver, because it eliminates ODBC's overhead.
Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 2
Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E Roll no: 01
Type 3: JDBC-Net pure Java
In a Type 3 driver, a three-tier approach is used to access databases. The JDBC clients
use standard network sockets to communicate with a middleware application server. The socket
information is then translated by the middleware application server into the call format required
by the DBMS, and forwarded to the database server.
This kind of driver is extremely flexible, since it requires no code installed on the client
and a single driver can actually provide access to multiple databases.
Type 4: 100% Pure Java
In a Type 4 driver, a pure Java-based driver communicates directly with the vendor's
database through socket connection. This is the highest performance driver available for the
database and is usually provided by the vendor itself.
kind of driver is extremely flexible, you don't need to install special software on the
client or server. Further, these drivers can be downloaded dynamically.
Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 3
Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E Roll no: 01
3. Database Connection Steps:
Step 1: Create new Java Project in Eclipse
Step 2: Add MySQL Connector (JAR file)
JDBC driver allows Java to talk to MySQL.
In Eclipse / STS:
1. Right-click project → Build Path → Configure Build Path
2. Click Add External JARs
3. Browse and select:
mysql-connector-j-<version>.jar
(download from: https://dev.mysql.com/downloads/connector/j/)
Step 3: Import Required Packages
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
Step 4: Load and Register Driver
Class.forName("com.mysql.cj.jdbc.Driver");
Step 5: Establish Connection
Use DriverManager.getConnection() with database URL, username, and password.
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/studentdb", // Database URL
"root", // MySQL username
"password" // MySQL password
);
Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 4
Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E Roll no: 01
Step 6: Create a Statement
Used to send SQL queries to the database.
Statement stmt = con.createStatement();
Step 7: Execute Query
For INSERT/UPDATE/DELETE → use executeUpdate()
For SELECT → use executeQuery()
ResultSet rs = stmt.executeQuery("SELECT * FROM student");
while(rs.next()) {
System.out.println(rs.getInt("id") + " " + rs.getString("name") + " " + rs.getInt("age"));
}
Step 8: Close Connection
Always close database resources to prevent memory leaks.
rs.close();
stmt.close();
con.close();
Example
import java.sql.*;
public class JDBCExample
{
// JDBC driver name and database URL
String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
String DB_URL = "jdbc:mysql://localhost/STUDENTS";
// Database credentials
String USER = "username";
String PASS = "password";
public static void main(String[] args)
{
Connection conn = null;
Statement stmt = null;
Try
{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.cj.jdbc.Driver");
//STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected successfully...");
//STEP 4: Execute a query
System.out.println("Inserting records into the table...");
stmt = conn.createStatement();
Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 5
Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E Roll no: 01
String sql = "INSERT INTO Registration " +
"VALUES (100, 'Zara', 'Ali', 18)";
stmt.executeUpdate(sql);
sql = "INSERT INTO Registration " +
"VALUES (101, 'Mahnaz', 'Fatma', 25)";
stmt.executeUpdate(sql);
sql = "INSERT INTO Registration " +
"VALUES (102, 'Zaid', 'Khan', 30)";
stmt.executeUpdate(sql);
sql = "INSERT INTO Registration " +
"VALUES(103, 'Sumit', 'Mittal', 28)";
stmt.executeUpdate(sql);
System.out.println("Inserted records into the table...");
String sql = "SELECT id, first, last, age FROM Registration";
ResultSet rs = stmt.executeQuery(sql);
//STEP 5: Extract data from result set
while(rs.next())
{
//Retrieve by column name
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
//Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
}
}
catch(Exception e)
{
//Handle errors for Class.forName
e.printStackTrace();
}
finally
{
//finally block used to close resources
conn.close();
}
}
}
}
Conclusion: We have successfully implemented 2-tier application using java and Mysql.
Menu Bar :
Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 6
Subject Name- CI2012: Database Management Systems Class: SY-CSAI-E Roll no: 01
Insert And display the data:
Delete the data:
Computer Science and Engineering (Artificial Intelligence), VIT Pune. Page 7
