BEG-055

أساسيات JDBC

7 درس
70 دقيقة قراءة
3 ساعة تدريب (تقدير)

ابدأ الوحدة

في هذه الصفحة 8 موضوعات

نظرة عامة
مخرجات التعلم
الدروس
المختبر والتمارين
الاختبار
أخطاء شائعة
التقييم
الصلة بالمسار الوظيفي

نص الدروس والتمارين باللغة الإنجليزية، بينما تبقى واجهة الموقع وعناوين الدروس بالعربية.

نظرة عامة

الموضوعات

اعتمادية المشغّل (Driver)
DriverManager
Connection
PreparedStatement
ResultSet
try-with-resources
حقن SQL (SQL injection)

تعلّم أولًا

70 دقيقة قراءة · 3 ساعة تدريب (تقدير)

مخرجات التعلم

Add the PostgreSQL JDBC driver with Maven and open a connection from configuration (not hard-coded credentials).
Execute queries and updates with PreparedStatement and map ResultSet rows to objects.
Close connections, statements, and result sets with try-with-resources.
Demonstrate a SQL injection with string concatenation and prevent it with parameters.
Group related statements in a transaction with setAutoCommit(false), commit, and rollback.

الدروس

المختبر والتمارين

Lab

BEG-055-LAB1 · Library DAO

Outcomes: 1–5 · Time: ~3 h Write a BookRepository interface with a JDBC implementation (save, findById returning Optional, findByTitle, delete) and integration tests against a local test database. Acceptance: no string-concatenated SQL; resources closed; tests pass.

Exercises (assessment: implement a new repository method with a test; explain why parameters prevent injection)

ID	Outcome	Task
BEG-055-EX01	1	Add the PostgreSQL driver with Maven and connect using `DB_URL`, `DB_USER`, `DB_PASSWORD` environment variables.
BEG-055-EX02	2	Write `findAll()` returning a `List<Book>` mapped from a `ResultSet`.
BEG-055-EX03	2	Write `findById(int)` returning `Optional<Book>`.
BEG-055-EX04	3	Rewrite a method that closes resources manually using try-with-resources.
BEG-055-EX05	4	Demonstrate injection against a concatenated query, then fix it with parameters.
BEG-055-EX06	5	Write a method that inserts a loan and reduces `copies` in one transaction with rollback on error.
BEG-055-EX07	1	Check that no password appears in the code or the Git history of your project.
BEG-055-EX08	2	Implement a new method `findByTitleContaining(String)` using `LIKE ?` and write an integration test for it. (A lookup by author needs a join through `book_authors`; try it only as a stretch task.)

أظهر الحلول

EX01 Dependency org.postgresql:postgresql (verify version); DriverManager.getConnection(System.getenv("DB_URL"), ...).
EX02 Loop while (rs.next()), add new Book(rs.getInt("id"), rs.getString("title")) to a list.
EX03 if (rs.next()) return Optional.of(map(rs)); return Optional.empty();
EX04 try (Connection c = ...; PreparedStatement ps = c.prepareStatement(sql); ResultSet rs = ps.executeQuery()) { ... }
EX05 Input x' OR '1'='1 returns all rows with concatenation; with ? it returns none because the value is data.
EX06 setAutoCommit(false); two executeUpdate calls; commit(); catch calls rollback() and rethrows.
EX07 Credentials come from environment variables; git log -p and a search for the password show no match.
EX08 SELECT ... WHERE title LIKE ? with the parameter "%" + text + "%" (bound through setString, never concatenated); the test inserts two books, queries, and asserts the result. Stretch: join books, book_authors, and authors to filter by author name.

الاختبار

Outcomes: O1 driver and connection · O2 queries and mapping · O3 try-with-resources · O4 SQL injection · O5 transactions.

أظهر الإجابات

Q01: C
Q02: D
Q03: A
Q04: B
Q05: C
Q06: B
Q07: A
Q08: C
Q09: B
Q10: A

الجزء العملي

Implement a new repository method with an integration test and explain why parameters prevent injection (EX08 and the lab). Pass: at least 8 of 10 quiz questions, no concatenated SQL, resources closed, tests pass.

أخطاء شائعة

Concatenating user input into SQL; leaking connections; credentials committed to Git.

التقييم

Implement a new repository method with a test; explain why parameters prevent injection.

الصلة بالمسار الوظيفي

JNR-025 to JNR-029 build on this toward Spring Data JPA.