Java JDBC – Complete Guide

1. What is JDBC?

JDBC (Java Database Connectivity) is a Java API that allows Java programs to connect and execute SQL queries with relational databases. 


java.sql
javax.sql

2. JDBC Architecture

+----------------------+
|   Java Application   |
+----------+-----------+
           |
           v
+----------------------+
|      JDBC API        |
+----------+-----------+
           |
           v
+----------------------+
|   JDBC Driver        |
+----------+-----------+
           |
           v
+----------------------+
|       Database       |
+----------------------+

3. JDBC Workflow

1. Load Driver
2. Establish Connection
3. Create Statement
4. Execute Query
5. Process Result
6. Close Resources

4. Maven Dependency (MySQL)


<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.3.0</version>
</dependency>

5. JDBC Core Interfaces

Interface Purpose
DriverManager Manages JDBC drivers
Connection Database connection
Statement Execute SQL queries
PreparedStatement Parameterized SQL queries
CallableStatement Call stored procedures
ResultSet Stores query results

6. Load JDBC Driver


Class.forName("com.mysql.cj.jdbc.Driver");

Java Example


public class LoadDriverExample {

    public static void main(String[] args) {

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("Driver Loaded Successfully");

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
}

7. Establish Database Connection


Connection connection =
DriverManager.getConnection(
"jdbc:mysql://localhost:3306/testdb",
"root",
"root");

8. Create Table Example


Statement stmt = con.createStatement();

String sql = "CREATE TABLE student(" +
"id INT PRIMARY KEY," +
"name VARCHAR(50)," +
"age INT)";

stmt.executeUpdate(sql);

9. Insert Example


INSERT INTO student VALUES (1,'Rahul',22)

Java Insert Code


Statement stmt = con.createStatement();

String sql =
"INSERT INTO student VALUES (1,'Rahul',22)";

stmt.executeUpdate(sql);

10. Select Example


ResultSet rs =
stmt.executeQuery("SELECT * FROM student");

while(rs.next()) {

int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");

System.out.println(id + " " + name + " " + age);
}

11. ResultSet Methods

Method Purpose
next() Move to next row
getInt() Get integer value
getString() Get string value
getDouble() Get double value
getDate() Get date value

12. PreparedStatement Example


String sql =
"INSERT INTO student VALUES (?,?,?)";

PreparedStatement ps =
con.prepareStatement(sql);

ps.setInt(1,2);
ps.setString(2,"Anita");
ps.setInt(3,23);

ps.executeUpdate();

13. Transaction Management


con.setAutoCommit(false);

stmt.executeUpdate("INSERT INTO student VALUES(5,'Alex',22)");
stmt.executeUpdate("INSERT INTO student VALUES(6,'Mike',23)");

con.commit();

14. SQL Injection

SQL Injection is a vulnerability where attackers manipulate SQL queries through user input.

This commonly occurs when using Statement with string concatenation.

Example Table


CREATE TABLE users(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50),
password VARCHAR(50)
);

Vulnerable Java Code


String sql =
"SELECT * FROM users WHERE username='" + username +
"' AND password='" + password + "'";

Injection Input

Username: admin
Password: ' OR '1'='1

Generated Query

SELECT * FROM users
WHERE username='admin'
AND password='' OR '1'='1'

Since '1'='1' is always true, login becomes successful.

15. Secure Solution (PreparedStatement)


String sql =
"SELECT * FROM users WHERE username=? AND password=?";

PreparedStatement ps =
con.prepareStatement(sql);

ps.setString(1, username);
ps.setString(2, password);

ResultSet rs = ps.executeQuery();

Statement vs PreparedStatement

Feature Statement PreparedStatement
SQL Injection Safe No Yes
Performance Slower Faster
Precompiled No Yes

Best Practices