JDBC in Java
Introduction
JDBC (Java Database Connectivity) is a Java API used to connect Java applications with databases. It allows a Java program to insert, update, delete, and retrieve data from a database.
JDBC can work with databases such as MySQL, Oracle, PostgreSQL, and SQL Server.
What is JDBC?
JDBC provides classes and interfaces that allow Java programs to communicate with databases using SQL (Structured Query Language).
Simple Flow
Java Application ↓ JDBC ↓ JDBC Driver ↓ Database
Main JDBC Components
| Component | Purpose |
|---|---|
DriverManager | Manages database drivers and connections |
Connection | Creates a connection with the database |
Statement | Executes SQL statements |
PreparedStatement | Executes parameterized SQL statements |
CallableStatement | Calls stored procedures |
ResultSet | Stores data returned by a SELECT query |
SQLException | Handles database-related errors |
Steps to Connect Java with Database
There are mainly 5 steps:
- Load/Register the JDBC driver
- Establish a database connection
- Create a statement
- Execute SQL query
- Close the connection
JDBC Syntax
Connection con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/database_name", "username", "password" );
Simple JDBC Example
The following example connects Java with a MySQL database and displays records.
import java.sql.*; public class JDBCExample { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/studentdb"; String username = "root"; String password = "1234"; try { Connection con = DriverManager.getConnection( url, username, password ); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT * FROM students" ); while (rs.next()) { System.out.println( rs.getInt("id") + " " + rs.getString("name") ); } con.close(); } catch (SQLException e) { System.out.println(e.getMessage()); } } }
Example Output
1 Anuradha 2 Sita 3 Ram
JDBC Connection URL
For MySQL:
jdbc:mysql://localhost:3306/studentdb
Here:
jdbc→ JDBC protocolmysql→ Database typelocalhost→ Database server3306→ Default MySQL portstudentdb→ Database name
Statement
Statement is used to execute SQL queries.
Example
Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT * FROM students" );
PreparedStatement
PreparedStatement is used to execute parameterized SQL queries.
It is generally preferred when user-provided values are involved.
Example
String sql = "INSERT INTO students(id, name) VALUES (?, ?)"; PreparedStatement ps = con.prepareStatement(sql); ps.setInt(1, 1); ps.setString(2, "Anuradha"); ps.executeUpdate();
Advantages
- More secure
- Reusable
- Easier to pass parameters
- Helps prevent SQL injection
ResultSet
ResultSet contains the data returned by a SELECT query.
ResultSet rs = stmt.executeQuery( "SELECT * FROM students" ); while (rs.next()) { System.out.println(rs.getInt("id")); System.out.println(rs.getString("name")); }
Common methods:
| Method | Purpose |
|---|---|
next() | Moves to the next row |
getInt() | Gets an integer value |
getString() | Gets a string value |
getDouble() | Gets a double value |
JDBC CRUD Operations
JDBC can perform four main database operations:
| Operation | SQL Command | JDBC Method |
|---|---|---|
| Create | INSERT | executeUpdate() |
| Read | SELECT | executeQuery() |
| Update | UPDATE | executeUpdate() |
| Delete | DELETE | executeUpdate() |
Example
// Insert stmt.executeUpdate( "INSERT INTO students VALUES (1, 'Anuradha')" ); // Update stmt.executeUpdate( "UPDATE students SET name='Sita' WHERE id=1" ); // Delete stmt.executeUpdate( "DELETE FROM students WHERE id=1" );
JDBC Architecture
+----------------------+ | Java Application | +----------+-----------+ | ↓ +----------------------+ | JDBC API | +----------+-----------+ | ↓ +----------------------+ | JDBC Driver | +----------+-----------+ | ↓ +----------------------+ | Database | +----------------------+
Statement vs PreparedStatement
| Statement | PreparedStatement |
|---|---|
| Used for simple SQL | Used for parameterized SQL |
| SQL is usually written directly | Uses ? placeholders |
| Less suitable for user input | Safer for user input |
| Not usually reused with parameters | Can be reused |
Example: Statement | Example: PreparedStatement |
Advantages of JDBC
- Connects Java applications to databases.
- Supports different database systems.
- Allows SQL queries to be executed from Java.
- Supports CRUD operations.
- Supports transactions.
- Provides exception handling through
SQLException. PreparedStatementimproves security when handling parameters.
Key Points
- JDBC stands for Java Database Connectivity.
- JDBC is used to connect Java applications with databases.
Connectionestablishes the database connection.StatementandPreparedStatementexecute SQL commands.ResultSetstores results ofSELECTqueries.executeQuery()is commonly used forSELECT.executeUpdate()is commonly used forINSERT,UPDATE, andDELETE.- Always close database resources after use.
Conclusion
JDBC is an important Java API for database connectivity. It allows Java applications to communicate with databases, execute SQL commands, and perform CRUD operations. Using Connection, PreparedStatement, Statement, and ResultSet, developers can build Java applications that work with database data.