Databases & JDBC

Database - ma’lumotlarni tartibli saqlash joyi.
JDBC - Java application bilan database o‘rtasida aloqa qilish uchun Java’ning standart API’si.

Bu bo‘lim quyidagilarni o‘z ichiga oladi: JDBC basics, Connection, Statement, PreparedStatement, HikariCP, SQL basics: SELECT, JOIN, INDEX.

1. Database nima uchun kerak?

Java application RAM ichida ishlaydi. Agar siz obyekt yaratib, dasturni o‘chirsangiz - data yo‘qoladi.

Masalan:

User user = new User(1L, "Ali");

Dastur yopilsa, bu user ham yo‘qoladi.

Shuning uchun data database’da saqlanadi:

Java application -> Database

Database’da saqlanadigan narsalar:

• users;

• products;

• orders;

• payments;

• roles;

• logs;

• settings;

• transactions.

2. Relational database nima?

Relational database - ma’lumotlarni jadval ko‘rinishida saqlaydigan database.

Mashhur relational database’lar:

Jadval misoli:

users
+----+-------+----------------+
| id | name  | phone          |
+----+-------+----------------+
| 1  | Ali   | +998901112233  |
| 2  | Vali  | +998902223344  |
+----+-------+----------------+

3. SQL nima?

SQL - database bilan gaplashish tili.

SQL orqali:

• data qo‘shamiz;

• data o‘qiymiz;

• data yangilaymiz;

• data o‘chiramiz;

• jadvallar yaratamiz;

• join qilamiz;

• index qo‘shamiz.

Asosiy SQL commandlar:

4. Oddiy table yaratish

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) UNIQUE,
    age INT
);

Bu yerda:

5. INSERT

Database’ga data qo‘shish:

INSERT INTO users (id, name, phone, age)
VALUES (1, 'Ali', '+998901112233', 25);

Yana bir user:

INSERT INTO users (id, name, phone, age)
VALUES (2, 'Vali', '+998902223344', 30);

6. SELECT

Data o‘qish:

SELECT * FROM users;

Bu users jadvalidagi hamma ustunlarni chiqaradi.

Faqat kerakli columnlarni olish:

SELECT id, name, phone
FROM users;

Shart bilan olish:

SELECT *
FROM users
WHERE age >= 18;

7. UPDATE

Data yangilash:

UPDATE users
SET age = 26
WHERE id = 1;

Muhim: WHERE yozilmasa, hamma user yangilanadi.

Xavfli:

UPDATE users
SET age = 26;

Bu barcha userlarning yoshini 26 qilib qo‘yadi.

8. DELETE

Data o‘chirish:

DELETE FROM users
WHERE id = 1;

Muhim: WHERE yozilmasa, hamma data o‘chadi.

Xavfli:

DELETE FROM users;

9. Foreign Key

Real database’da jadvallar bir-biri bilan bog‘lanadi.

Masalan:

users
orders

Bitta userda ko‘p order bo‘lishi mumkin.

orders table:

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    amount NUMERIC(12, 2) NOT NULL,
    status VARCHAR(30) NOT NULL,

    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
);

Bu yerda:

FOREIGN KEY (user_id) REFERENCES users(id)

degani:

orders jadvalidagi user_id users jadvalidagi mavjud idga bog‘langan bo‘lishi kerak.

10. JOIN

JOIN - bir nechta jadvaldagi datani birga olish.

Masalan:

users:

id | name
1  | Ali
2  | Vali

orders:

id | user_id | amount
1  | 1       | 100000
2  | 1       | 250000
3  | 2       | 50000

User va orderlarni birga olish:

SELECT
    u.id,
    u.name,
    o.id AS order_id,
    o.amount
FROM users u
JOIN orders o ON o.user_id = u.id;

Natija:

id | name | order_id | amount
1  | Ali  | 1        | 100000
1  | Ali  | 2        | 250000
2  | Vali | 3        | 50000

11. JOIN turlari

Eng ko‘p ishlatiladigani:

JOIN
LEFT JOIN

11.1 INNER JOIN

SELECT u.name, o.amount
FROM users u
JOIN orders o ON o.user_id = u.id;

Faqat orderi bor userlar chiqadi.

11.2 LEFT JOIN

SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

Orderi bo‘lmasa ham user chiqadi. amount NULL bo‘ladi.

Bu real loyihada ko‘p kerak bo‘ladi:

• user bor, order bo‘lmasligi mumkin;

• category bor, product bo‘lmasligi mumkin;

• client bor, payment bo‘lmasligi mumkin.

12. Aggregate functions

SQL’da hisoblash uchun tayyor functionlar bor.

Misollar:

SELECT COUNT(*)
FROM users;

SELECT SUM(amount)
FROM orders;

SELECT AVG(amount)
FROM orders;

13. GROUP BY

GROUP BY - datani guruhlab hisoblash.

Masalan, har bir userning jami order summasi:

SELECT
    user_id,
    SUM(amount) AS total_amount
FROM orders
GROUP BY user_id;

Status bo‘yicha order soni:

SELECT
    status,
    COUNT(*) AS order_count
FROM orders
GROUP BY status;

Natija:

status     | order_count
NEW        | 10
PAID       | 25
CANCELLED  | 3

14. ORDER BY

Saralash:

SELECT *
FROM users
ORDER BY age ASC;

ASC - o‘sish tartibi.

SELECT *
FROM users
ORDER BY age DESC;

DESC - kamayish tartibi.

15. LIMIT / OFFSET

Pagination uchun ishlatiladi.

SELECT *
FROM users
ORDER BY id
LIMIT 10 OFFSET 0;

Keyingi sahifa:

SELECT *
FROM users
ORDER BY id
LIMIT 10 OFFSET 10;

Lekin katta table’larda OFFSET sekinlashishi mumkin. Keyinroq production uchun keyset pagination yaxshi yondashuv bo‘ladi.

16. INDEX

Index - database’da qidirishni tezlashtiradigan tuzilma.

Masalan, phone bo‘yicha user tez-tez qidiriladi:

SELECT *
FROM users
WHERE phone = '+998901112233';

Index qo‘shamiz:

CREATE INDEX idx_users_phone
ON users(phone);

Endi database phone bo‘yicha tezroq topadi.

16.1 Index qachon kerak?

Index foydali bo‘ladi:

• WHEREda ko‘p ishlatiladigan columnlar;

• JOINda ishlatiladigan foreign key columnlar;

• ORDER BYda tez-tez ishlatiladigan columnlar;

• unique bo‘lishi kerak bo‘lgan columnlar.

Misollar:

CREATE INDEX idx_orders_user_id
ON orders(user_id);

CREATE INDEX idx_orders_status
ON orders(status);

CREATE UNIQUE INDEX idx_users_phone_unique
ON users(phone);

16.2 Indexning kamchiligi

Index har doim ham "ko‘p bo‘lsa yaxshi" degani emas.

Kamchiliklari:

• diskdan joy oladi;

• INSERT sekinlashishi mumkin;

• UPDATE sekinlashishi mumkin;

• DELETE sekinlashishi mumkin;

• noto‘g‘ri index foyda bermaydi.

Boshlang'ich developerlar uchun qoida:

Qidiriladigan, join qilinadigan, sort qilinadigan columnlarga index qo‘yiladi. Har bir columnga index qo‘yilmaydi.

17. JDBC nima?

JDBC - Java Database Connectivity.

Bu Java’dan database bilan ishlash uchun standart API.

JDBC orqali Java quyidagilarni qiladi:

Java code -> JDBC Driver -> Database

Masalan PostgreSQL bilan ishlasangiz:

Java application
   ↓
PostgreSQL JDBC Driver
   ↓
PostgreSQL Database

18. JDBC Driver

Har database uchun alohida JDBC driver kerak.

Masalan:

Maven PostgreSQL dependency:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.3</version>
</dependency>

Gradle:

implementation("org.postgresql:postgresql:42.7.3")

19. JDBC URL

Database’ga ulanish uchun URL kerak.

PostgreSQL:

jdbc:postgresql://localhost:5432/mydb

MySQL:

jdbc:mysql://localhost:3306/mydb

H2:

jdbc:h2:mem:testdb

Tarkibi:

jdbc:postgresql://host:port/database

20. JDBC Connection

Connection - Java bilan database orasidagi ulanish.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Main {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:postgresql://localhost:5432/mydb";
        String username = "postgres";
        String password = "password";

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            System.out.println("Database connected");
        }
    }
}

Muhim:

try (Connection connection = ...)

Bu connectionni avtomatik yopadi.

21. Statement

Statement - SQL query’ni database’ga yuborish uchun ishlatiladi.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Main {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:postgresql://localhost:5432/mydb";
        String username = "postgres";
        String password = "password";

        try (Connection connection = DriverManager.getConnection(url, username, password);
             Statement statement = connection.createStatement()) {

            String sql = """
                    CREATE TABLE IF NOT EXISTS users (
                        id BIGINT PRIMARY KEY,
                        name VARCHAR(100),
                        age INT
                    )
                    """;

            statement.execute(sql);
        }
    }
}

Bu table yaratadi.

22. Statement bilan INSERT

try (Connection connection = DriverManager.getConnection(url, username, password);
     Statement statement = connection.createStatement()) {

    String sql = "INSERT INTO users (id, name, age) VALUES (1, 'Ali', 25)";

    int rows = statement.executeUpdate(sql);

    System.out.println("Inserted rows: " + rows);
}

executeUpdate() quyidagilar uchun ishlatiladi:

• INSERT;

• UPDATE;

• DELETE.

U nechta row o‘zgarganini qaytaradi.

23. Statement muammosi: SQL Injection

Yomon kod:

String name = "' OR '1'='1";

String sql = "SELECT * FROM users WHERE name = '" + name + "'";

Natijada query buzilib ketishi mumkin:

SELECT * FROM users WHERE name = '' OR '1'='1'

Bu xavfli. Bunday hujum SQL Injection deyiladi.

Shuning uchun user input bilan ishlaganda Statement emas, PreparedStatement ishlatiladi.

24. PreparedStatement

PreparedStatement - parametrli SQL yozish uchun ishlatiladi.

String sql = "SELECT * FROM users WHERE name = ?";

? - parametr joyi.

try (Connection connection = DriverManager.getConnection(url, username, password);
     PreparedStatement preparedStatement = connection.prepareStatement(sql)) {

    preparedStatement.setString(1, "Ali");

    ResultSet resultSet = preparedStatement.executeQuery();
}

Bu xavfsizroq va professional yondashuv.

25. PreparedStatement bilan INSERT

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Main {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:postgresql://localhost:5432/mydb";
        String username = "postgres";
        String password = "password";

        String sql = "INSERT INTO users (id, name, age) VALUES (?, ?, ?)";

        try (Connection connection = DriverManager.getConnection(url, username, password);
             PreparedStatement ps = connection.prepareStatement(sql)) {

            ps.setLong(1, 1L);
            ps.setString(2, "Ali");
            ps.setInt(3, 25);

            int rows = ps.executeUpdate();

            System.out.println("Inserted rows: " + rows);
        }
    }
}

Bu yerda:

Index 1dan boshlanadi.

26. SELECT va ResultSet

ResultSet - SELECT query natijasini o‘qish uchun ishlatiladi.

import java.sql.*;

public class Main {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:postgresql://localhost:5432/mydb";
        String username = "postgres";
        String password = "password";

        String sql = "SELECT id, name, age FROM users WHERE age >= ?";

        try (Connection connection = DriverManager.getConnection(url, username, password);
             PreparedStatement ps = connection.prepareStatement(sql)) {

            ps.setInt(1, 18);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Long id = rs.getLong("id");
                    String name = rs.getString("name");
                    int age = rs.getInt("age");

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

rs.next() keyingi rowga o‘tadi.

Agar row bo‘lsa true, bo‘lmasa false.

27. ResultSet’dan obyekt yasash

Real loyihada ResultSet’dan entity yoki DTO yasaladi.

public class User {
    private Long id;
    private String name;
    private int age;

    public User(Long id, String name, int age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }
}

Mapping:

private static User mapRow(ResultSet rs) throws SQLException {
    Long id = rs.getLong("id");
    String name = rs.getString("name");
    int age = rs.getInt("age");

    return new User(id, name, age);
}

Ishlatish:

List<User> users = new ArrayList<>();

while (rs.next()) {
    users.add(mapRow(rs));
}

28. UPDATE with PreparedStatement

String sql = "UPDATE users SET name = ?, age = ? WHERE id = ?";

try (Connection connection = DriverManager.getConnection(url, username, password);
     PreparedStatement ps = connection.prepareStatement(sql)) {

    ps.setString(1, "Ali Valiyev");
    ps.setInt(2, 26);
    ps.setLong(3, 1L);

    int rows = ps.executeUpdate();

    System.out.println("Updated rows: " + rows);
}

29. DELETE with PreparedStatement

String sql = "DELETE FROM users WHERE id = ?";

try (Connection connection = DriverManager.getConnection(url, username, password);
     PreparedStatement ps = connection.prepareStatement(sql)) {

    ps.setLong(1, 1L);

    int rows = ps.executeUpdate();

    System.out.println("Deleted rows: " + rows);
}

30. JDBC Repository pattern

Real loyihada JDBC kodni alohida repository classga chiqarish kerak.

public class UserRepository {
    private final String url;
    private final String username;
    private final String password;

    public UserRepository(String url, String username, String password) {
        this.url = url;
        this.username = username;
        this.password = password;
    }

    public User findById(Long id) {
        String sql = "SELECT id, name, age FROM users WHERE id = ?";

        try (Connection connection = DriverManager.getConnection(url, username, password);
             PreparedStatement ps = connection.prepareStatement(sql)) {

            ps.setLong(1, id);

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return mapRow(rs);
                }

                return null;
            }

        } catch (SQLException e) {
            throw new RuntimeException("Database error", e);
        }
    }

    private User mapRow(ResultSet rs) throws SQLException {
        return new User(
                rs.getLong("id"),
                rs.getString("name"),
                rs.getInt("age")
        );
    }
}

Lekin null qaytarish o‘rniga Optional<User> yaxshi.

31. Optional bilan repository

public Optional<User> findById(Long id) {
    String sql = "SELECT id, name, age FROM users WHERE id = ?";

    try (Connection connection = DriverManager.getConnection(url, username, password);
         PreparedStatement ps = connection.prepareStatement(sql)) {

        ps.setLong(1, id);

        try (ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                return Optional.of(mapRow(rs));
            }

            return Optional.empty();
        }

    } catch (SQLException e) {
        throw new RuntimeException("Database error", e);
    }
}

Service’da ishlatish:

User user = userRepository.findById(1L)
        .orElseThrow(() -> new RuntimeException("User topilmadi"));

32. Connection Pool nima?

Har query uchun yangi connection ochish qimmat operatsiya.

Yomon:

request 1 -> yangi connection ochish -> query -> yopish
request 2 -> yangi connection ochish -> query -> yopish
request 3 -> yangi connection ochish -> query -> yopish

Bu sekin.

Connection pool tayyor connectionlarni saqlab turadi.

Application
   ↓
Connection Pool
   ↓
Database

Request kelganda yangi connection ochmaydi, pool’dan tayyor connection oladi.

33. HikariCP

HikariCP - Java’da eng mashhur connection pool’lardan biri.

Maven dependency:

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.1.0</version>
</dependency>

Oddiy config:

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import javax.sql.DataSource;

public class DataSourceConfig {

    public static DataSource createDataSource() {
        HikariConfig config = new HikariConfig();

        config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
        config.setUsername("postgres");
        config.setPassword("password");
        config.setMaximumPoolSize(10);

        return new HikariDataSource(config);
    }
}

Repository’da DriverManager o‘rniga DataSource ishlatiladi.

34. DataSource bilan repository

import javax.sql.DataSource;
import java.sql.*;
import java.util.Optional;

public class UserRepository {
    private final DataSource dataSource;

    public UserRepository(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public Optional<User> findById(Long id) {
        String sql = "SELECT id, name, age FROM users WHERE id = ?";

        try (Connection connection = dataSource.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {

            ps.setLong(1, id);

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return Optional.of(mapRow(rs));
                }

                return Optional.empty();
            }

        } catch (SQLException e) {
            throw new RuntimeException("Database error", e);
        }
    }

    private User mapRow(ResultSet rs) throws SQLException {
        return new User(
                rs.getLong("id"),
                rs.getString("name"),
                rs.getInt("age")
        );
    }
}

Bu professionalroq yondashuv.

35. Transaction

Transaction - bir nechta database amalini bitta butun operatsiya sifatida bajarish.

Masalan, pul o‘tkazish:

1. Ali hisobidan 100 000 yechish.

2. Vali hisobiga 100 000 qo‘shish.

Ikkalasi ham bajarilishi kerak. Bittasi bajarilib, ikkinchisi xato bersa, hammasi bekor qilinishi kerak.

Transaction xususiyatlari ACID deyiladi:

35.1 JDBC transaction misol

Default holatda connection autoCommit = true.

Ya’ni har bir query avtomatik commit bo‘ladi.

Transaction uchun:

connection.setAutoCommit(false);

Misol:

try (Connection connection = dataSource.getConnection()) {
    try {
        connection.setAutoCommit(false);

        // 1. Ali hisobidan pul yechish
        try (PreparedStatement ps = connection.prepareStatement(
                "UPDATE accounts SET balance = balance - ? WHERE id = ?"
        )) {
            ps.setBigDecimal(1, new BigDecimal("100000"));
            ps.setLong(2, 1L);
            ps.executeUpdate();
        }

        // 2. Vali hisobiga pul qo‘shish
        try (PreparedStatement ps = connection.prepareStatement(
                "UPDATE accounts SET balance = balance + ? WHERE id = ?"
        )) {
            ps.setBigDecimal(1, new BigDecimal("100000"));
            ps.setLong(2, 2L);
            ps.executeUpdate();
        }

        connection.commit();

    } catch (Exception e) {
        connection.rollback();
        throw e;
    }
}

Agar xato chiqsa:

connection.rollback();

barcha o‘zgarishlar bekor qilinadi.

Spring Boot’da buni keyinroq @Transactional qiladi.

36. Batch insert

Ko‘p data insert qilganda har birini alohida query qilish sekin.

Yomon:

for (User user : users) {
    insert(user);
}

Yaxshiroq: batch.

String sql = "INSERT INTO users (id, name, age) VALUES (?, ?, ?)";

try (Connection connection = dataSource.getConnection();
     PreparedStatement ps = connection.prepareStatement(sql)) {

    for (User user : users) {
        ps.setLong(1, user.getId());
        ps.setString(2, user.getName());
        ps.setInt(3, user.getAge());

        ps.addBatch();
    }

    int[] result = ps.executeBatch();
}

Bu katta importlarda foydali.

37. SQL NULL va Java

Database’da column NULL bo‘lishi mumkin.

age INT NULL

Java’da ehtiyot bo‘lish kerak.

int age = rs.getInt("age");

Agar age NULL bo‘lsa, 0 qaytishi mumkin. Bu chalkashlik beradi.

Tekshirish:

int age = rs.getInt("age");

if (rs.wasNull()) {
    // age aslida NULL edi
}

Yoki wrapper ishlatish:

Integer age = rs.getObject("age", Integer.class);

Bu NULL bo‘lsa null qaytaradi.

38. LocalDateTime bilan ishlash

Database’da odatda:

created_at TIMESTAMP

Java’da:

LocalDateTime createdAt = rs.getObject("created_at", LocalDateTime.class);

Insert qilish:

ps.setObject(1, LocalDateTime.now());

Bu Java 8+ java.time API bilan yaxshi ishlaydi.

39. Juniorlar qiladigan JDBC xatolari

Xato 1: Statement bilan user input ishlatish

Yomon:

String sql = "SELECT * FROM users WHERE phone = '" + phone + "'";

To‘g‘ri:

String sql = "SELECT * FROM users WHERE phone = ?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, phone);

Xato 2: Connection yopmaslik

Yomon:

Connection connection = DriverManager.getConnection(url, username, password);

To‘g‘ri:

try (Connection connection = DriverManager.getConnection(url, username, password)) {
    // query
}

Xato 3: Har requestda yangi connection ochish

Real app’da DriverManager emas, DataSource + connection pool ishlatish kerak.

Xato 4: SQL exceptionni yutib yuborish

Yomon:

catch (SQLException e) {
}

To‘g‘ri:

catch (SQLException e) {
    throw new RuntimeException("Database error", e);
}

Yoki log qilish.

Xato 5: Transaction ishlatmaslik

Pul o‘tkazish, order yaratish, payment qilish kabi operatsiyalarda transaction zarur.

Xato 6: SELECT *ni hamma joyda ishlatish

Yomon:

SELECT * FROM users;

Yaxshiroq:

SELECT id, name, phone FROM users;

Faqat kerakli columnlarni olish performance va o‘qilishi uchun yaxshi.

40. Spring Boot’da JDBC qanday ko‘rinadi?

Spring Boot’da odatda toza JDBC kamroq yoziladi. Quyidagilar ishlatiladi:

Lekin JDBC’ni bilish muhim. Chunki JPA/Hibernate ichida ham database va SQL mantiqi bor.

41. Mini loyiha: User JDBC CRUD

Talab

users table:

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) UNIQUE NOT NULL,
    age INT
);

Java’da quyidagi methodlarni yozing:

public interface UserRepository {
    User save(User user);

    Optional<User> findById(Long id);

    List<User> findAll();

    void update(User user);

    void deleteById(Long id);
}

User class

public class User {
    private Long id;
    private String name;
    private String phone;
    private Integer age;

    public User(Long id, String name, String phone, Integer age) {
        this.id = id;
        this.name = name;
        this.phone = phone;
        this.age = age;
    }

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public String getPhone() {
        return phone;
    }

    public Integer getAge() {
        return age;
    }
}

Save method

public User save(User user) {
    String sql = """
            INSERT INTO users (name, phone, age)
            VALUES (?, ?, ?)
            RETURNING id
            """;

    try (Connection connection = dataSource.getConnection();
         PreparedStatement ps = connection.prepareStatement(sql)) {

        ps.setString(1, user.getName());
        ps.setString(2, user.getPhone());

        if (user.getAge() != null) {
            ps.setInt(3, user.getAge());
        } else {
            ps.setNull(3, Types.INTEGER);
        }

        try (ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                Long id = rs.getLong("id");
                return new User(id, user.getName(), user.getPhone(), user.getAge());
            }

            throw new RuntimeException("User id qaytmadi");
        }

    } catch (SQLException e) {
        throw new RuntimeException("User save error", e);
    }
}

PostgreSQL’da RETURNING id yangi yaratilgan ID’ni qaytaradi.

Find all method

public List<User> findAll() {
    String sql = "SELECT id, name, phone, age FROM users ORDER BY id";

    List<User> users = new ArrayList<>();

    try (Connection connection = dataSource.getConnection();
         PreparedStatement ps = connection.prepareStatement(sql);
         ResultSet rs = ps.executeQuery()) {

        while (rs.next()) {
            users.add(mapRow(rs));
        }

        return users;

    } catch (SQLException e) {
        throw new RuntimeException("User findAll error", e);
    }
}

Map row

private User mapRow(ResultSet rs) throws SQLException {
    return new User(
            rs.getLong("id"),
            rs.getString("name"),
            rs.getString("phone"),
            rs.getObject("age", Integer.class)
    );
}

42. Java developer uchun eng muhim qismlar

43. Amaliy mashq

Vazifa: Product JDBC CRUD

products table yarating:

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(12, 2) NOT NULL,
    active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

Java’da quyidagilarni yozing:

1. Product class.

2. ProductRepository.

3. save(Product product).

4. findById(Long id).

5. findAll().

6. findActiveProducts().

7. updatePrice(Long id, BigDecimal price).

8. deleteById(Long id).

9. findByPriceGreaterThan(BigDecimal price).

10. created_atni LocalDateTime bilan o‘qing.

Qo‘shimcha mashq:

• price bo‘yicha index qo‘ying;

• active bo‘yicha filter query yozing;

• name bo‘yicha qidirish yozing;

• SQL Injection xavfini ko‘rsatib, keyin PreparedStatement bilan tuzating.

Xulosa

Databases & JDBC Java developer uchun backend asosidir.

Eng kerakli eslab qoladigan kodlar:

Connection connection = dataSource.getConnection();

PreparedStatement ps = connection.prepareStatement(
        "SELECT id, name FROM users WHERE id = ?"
);

ps.setLong(1, id);

ResultSet rs = ps.executeQuery();

while (rs.next()) {
    Long id = rs.getLong("id");
    String name = rs.getString("name");
}

Asosiy fikr:

• SQL bilmasdan backend kuchsiz bo‘ladi;

• JDBC database bilan past darajada ishlashni o‘rgatadi;

• PreparedStatement SQL Injectiondan himoya qiladi;

• connection pool performance uchun kerak;

• transaction data yaxlitligini saqlaydi;

• index querylarni tezlashtiradi;

• keyin Spring Boot’da JPA/Hibernate tushunish ancha osonlashadi.