Database Connectivity in Java
Introduction
A Database is an organized collection of data that can be stored, managed, and retrieved efficiently.
Java can connect to databases using JDBC (Java Database Connectivity).
JDBC allows a Java program to:
- Connect to a database.
- Insert data.
- Retrieve data.
- Update data.
- Delete data.
- Execute SQL queries.
What is JDBC?
JDBC stands for Java Database Connectivity.
It is a Java API that provides classes and interfaces for communicating with databases.
Java Application | ↓ JDBC | ↓ JDBC Driver | ↓ Database
Common Databases
Java applications can connect to different database systems, such as:
- MySQL
- PostgreSQL
- Oracle Database
- Microsoft SQL Server
- SQLite
JDBC Package
JDBC classes and interfaces are mainly available in:
import java.sql.*;
Important JDBC Components
| Component | Purpose |
|---|---|
DriverManager | Establishes a database connection |
Connection | Represents a connection to the database |
Statement | Executes SQL statements |
PreparedStatement | Executes parameterized SQL statements |
ResultSet | Stores results returned by a query |
SQLException | Handles database-related errors |
JDBC Steps
The basic steps for connecting Java to a database are:
1. Import JDBC package ↓ 2. Load/Register JDBC driver ↓ 3. Establish database connection ↓ 4. Create Statement or PreparedStatement ↓ 5. Execute SQL query ↓ 6. Process the result ↓ 7. Close resources
Connecting Java with MySQL
For a MySQL database, the application needs the appropriate MySQL JDBC driver available in its project.
Example connection information:
String url = "jdbc:mysql://localhost:3306/studentdb"; String username = "root"; String password = "password";
Here:
localhost→ Database server running on the local computer.3306→ Common MySQL port.studentdb→ Database name.root→ Database username.
Basic Database Connection
import java.sql.*; public class DatabaseConnection { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/studentdb"; String username = "root"; String password = "password"; try { Connection connection = DriverManager.getConnection( url, username, password ); System.out.println("Database connected successfully."); connection.close(); } catch (SQLException e) { System.out.println("Database connection failed."); e.printStackTrace(); } } }
Output
If the connection is successful:
Database connected successfully.
Creating a Table
Example SQL:
CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(50), age INT );
The table contains:
| id | name | age |
|---|---|---|
| 1 | Anu | 20 |
| 2 | Ram | 21 |
Insert Data
SQL:
INSERT INTO students (id, name, age) VALUES (1, 'Anu', 20);
Java example:
String sql = "INSERT INTO students (id, name, age) " + "VALUES (1, 'Anu', 20)"; Statement statement = connection.createStatement(); statement.executeUpdate(sql);
executeUpdate() is commonly used for INSERT, UPDATE, and DELETE statements.
Retrieve Data
SQL:
SELECT * FROM students;
Java example:
String sql = "SELECT * FROM students"; Statement statement = connection.createStatement(); ResultSet result = statement.executeQuery(sql); while (result.next()) { int id = result.getInt("id"); String name = result.getString("name"); int age = result.getInt("age"); System.out.println( id + " " + name + " " + age ); }
Output
1 Anu 20 2 Ram 21
PreparedStatement
PreparedStatement is used to execute parameterized SQL statements.
Example:
String sql = "INSERT INTO students (id, name, age) " + "VALUES (?, ?, ?)"; PreparedStatement statement = connection.prepareStatement(sql); statement.setInt(1, 3); statement.setString(2, "Sita"); statement.setInt(3, 22); statement.executeUpdate();
The ? symbols are placeholders for values.
Statement vs PreparedStatement
| Statement | PreparedStatement |
|---|---|
| Used for simple SQL statements | Used for parameterized SQL |
| SQL is usually constructed directly | Uses ? placeholders |
| Less suitable for repeated parameterized queries | Useful for repeated queries |
| More prone to unsafe string-based SQL construction | Helps prevent SQL injection when used correctly |
CRUD Operations
CRUD represents the four basic database operations.
| Operation | SQL Command | Purpose |
|---|---|---|
| Create | INSERT | Adds data |
| Read | SELECT | Retrieves data |
| Update | UPDATE | Changes existing data |
| Delete | DELETE | Removes data |
Example
CREATE → INSERT student READ → SELECT student UPDATE → UPDATE student DELETE → DELETE student
JDBC Architecture
+----------------------+ | Java Application | +----------+-----------+ | ↓ +----------------------+ | JDBC | +----------+-----------+ | ↓ +----------------------+ | JDBC Driver | +----------+-----------+ | ↓ +----------------------+ | Database | +----------------------+
Advantages of JDBC
- Allows Java applications to communicate with databases.
- Supports multiple database systems through appropriate JDBC drivers.
- Supports SQL queries.
- Provides CRUD operations.
- Supports parameterized queries through
PreparedStatement. - Provides transaction management through
Connection. - Works with Java applications such as web and desktop applications.
Important Points
- JDBC stands for Java Database Connectivity.
- JDBC is used to connect Java applications with databases.
- The main package is
java.sql. DriverManageris used to obtain database connections.Connectionrepresents the database connection.StatementandPreparedStatementexecute SQL statements.ResultSetstores the result of aSELECTquery.executeQuery()is commonly used forSELECT.executeUpdate()is commonly used forINSERT,UPDATE, andDELETE.- Database resources should be properly closed after use.
Conclusion
JDBC provides a standard way for Java applications to communicate with databases. Using JDBC, a Java program can connect to a database and perform operations such as insert, read, update, and delete (CRUD). It is an important technology for developing database-driven Java applications.