انتقل إلى المحتوى
أمين أبوزيد
Englishالعربية
JNR-025

JDBC

  • 5 درس
  • 2 ساعة تدريب (تقدير)
ابدأ الوحدة
في هذه الصفحة 8 موضوعات

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

نظرة عامة

الموضوعات

  • الاتصالات (Connections)
  • PreparedStatement
  • ResultSet
  • منع حقن SQL (يبني على BEG-055)

تعلّم أولًا

2 ساعة تدريب (تقدير)

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

  1. Obtain connections from a DataSource (connection pool) instead of DriverManager.
  2. Execute batch inserts and retrieve generated keys.
  3. Map SQL types to Java types correctly (numeric ↔ BigDecimal, timestamptz ↔ Instant/OffsetDateTime).
  4. Prevent SQL injection in dynamic queries (e.g., user-chosen sort columns via allow-lists).

الدروس

  1. 01من DriverManager إلى DataSource
  2. 02المفاتيح المولَّدة والتنفيذ الدفعي (Batching)
  3. 03مطبّات تحويل الأنواع
  4. 04SQL ديناميكي دون حقن
  5. 05عرض عملي: استيراد دفعي ومقارنة الأزمنة

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

Lab

JNR-025-LAB1 · Bulk import

Outcomes: 1–4 · Time: ~2 h

  1. Create a product table (id bigint generated always as identity, name text, price numeric(12,2)).
  2. Import 10,000 products from a provided CSV using a pooled DataSource and a batched PreparedStatement.
  3. Import the same file row by row with auto-commit on; time both with the same data.
  4. Print the generated keys of the first batch; map price to BigDecimal.
  5. Add a findAll(sortBy) method whose sort column comes from an allow-list.

Acceptance: measured speed-up shown; no concatenated user input in any SQL.

Exercises (assessment: fix an injectable dynamic query)

IDOutcomeTask
JNR-025-EX011Explain in three sentences why a connection pool beats DriverManager per statement.
JNR-025-EX021Rewrite a method that opens DriverManager connections to use an injected DataSource with try-with-resources.
JNR-025-EX032Insert five rows with addBatch and print each generated id.
JNR-025-EX043Choose the Java type for: numeric(10,2), timestamptz, date, and a nullable bigint.
JNR-025-EX054Fix "select * from product where name = '" + name + "'" using a placeholder.
JNR-025-EX064Fix "... order by " + request.getParameter("sort") with an allow-list and a default.
أظهر الحلول
  • EX01 Opening a connection costs network round trips, login, and setup; a pool keeps connections open and reuses them, so each statement pays almost nothing and the database is protected from too many connections.
  • EX02 try (Connection c = dataSource.getConnection(); PreparedStatement ps = ...) { ... }; the DataSource is a constructor parameter.
  • EX03 Prepare with Statement.RETURN_GENERATED_KEYS, addBatch five times, executeBatch, loop over getGeneratedKeys().
  • EX04 BigDecimal; Instant or OffsetDateTime; LocalDate; Long (read with getObject(col, Long.class)).
  • EX05 prepareStatement("select * from product where name = ?") then ps.setString(1, name).
  • EX06 String col = SORTABLE.getOrDefault(sort, "name"); and concatenate only col.
  • Outcomes 2 and 3 are also checked in the lab (batch timing, BigDecimal price).

الاختبار

Outcomes: O1 DataSource and pool · O2 batches and generated keys · O3 type mapping · O4 injection prevention.

اختبار ذاتي لك: لا توجد درجات، ولا يُحفظ أو يُرسل أي شيء. اختر إجابة ثم تحقق منها، ويمكنك المحاولة مرة أخرى كما تشاء.

  1. 1A connection pool mainly:O1
  2. 2Calling close() on a pooled connection:O1
  3. 3Best way to release JDBC resources:O1
  4. 4executeBatch is faster mainly because of:O2
  5. 5To read database-generated ids you use:O2
  6. 6Java type for a numeric(12,2) price:O3
  7. 7rs.getInt("x") on a NULL column returns:O3
  8. 8Best Java type for timestamptz:O3
  9. 9A user-chosen ORDER BY column should be handled with:O4
  10. 10PreparedStatement placeholders protect:O4

الجزء العملي

Six exercises JNR-025-EX01 to EX06 plus the lab; the assessment is fixing an injectable dynamic query (EX05, EX06). Pass: at least five exercises correct, lab speed-up measured, no concatenated user input.

أخطاء شائعة

double for prices; concatenating ORDER BY columns; one connection per statement.

التقييم

Fix an injectable dynamic query.

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

Understanding what every higher-level abstraction does underneath.

اكتب للبحث في المستويات والمراحل والوحدات والدروس.