JDBC & Database Connectivity

What you will be able to do

• Create and run a Maven project and add libraries as dependencies
• Connect a Java program to PostgreSQL and run queries with JDBC
• Prevent SQL injection with prepared statements
• Manage transactions and close resources correctly from Java
• Organise database code with the DAO pattern and a connection pool
• Build a console version of ShopLite on top of the SQL schema

What it will cover

1. 13.1Maven Projects & Dependencies

   Use Maven to build projects and download libraries instead of copying JAR files by hand.

   Show 7 topics
   • What a build tool does and why Maven
   • Create a Maven project in your IDE and from the terminal
   • pom.xml: groupId, artifactId, version and packaging
   • Adding dependencies and the local repository
   • The build lifecycle: compile, test, package
   • Running your app and building a JAR
   • Maven vs Gradle in one lesson

2. 13.2Connecting to a Database

   Open a connection from Java to PostgreSQL and understand what the JDBC driver does.

   Show 6 topics
   • What JDBC is: API and driver
   • Adding the PostgreSQL driver with Maven
   • JDBC URL, user and password
   • DriverManager and Connection
   • Keeping credentials out of code with properties and environment variables
   • Common connection errors

3. 13.3Running Queries with JDBC

   Send SQL from Java and turn the results into Java objects.

   Show 5 topics
   • Statement and executeQuery
   • Reading a ResultSet row by row
   • Column types to Java types, including dates and NULL
   • executeUpdate for INSERT, UPDATE and DELETE
   • Mapping rows to your own classes and records

4. 13.4Prepared Statements & SQL Injection

   Pass user input to SQL safely and see how injection attacks work.

   Show 5 topics
   • SQL injection demonstrated on a login query
   • PreparedStatement and ? placeholders
   • Setting parameters of each type
   • Getting generated keys after INSERT
   • Batch inserts with addBatch

5. 13.5Resource Handling & SQL Errors

   Close connections and statements reliably and handle database errors properly.

   Show 4 topics
   • Why unclosed connections break applications
   • try-with-resources for Connection, Statement and ResultSet
   • SQLException, SQL state and error codes
   • Turning low-level errors into meaningful application exceptions

6. 13.6Transactions in JDBC

   Control commit and rollback from Java so related changes stay consistent.

   Show 5 topics
   • Auto-commit and turning it off
   • commit and rollback in a try/catch
   • Placing an order: stock update plus order insert
   • Setting the isolation level
   • Preventing overselling with SELECT ... FOR UPDATE

7. 13.7The DAO Pattern

   Keep SQL in one layer so the rest of the program works with plain Java objects.

   Show 6 topics
   • Why mixing SQL with business logic hurts
   • DAO interface and JDBC implementation
   • Entity classes vs DTOs
   • A service layer on top of DAOs
   • Paging results with LIMIT and OFFSET from Java
   • Swapping implementations behind an interface

8. 13.8Connection Pooling & Configuration

   Reuse database connections with a pool, the way real applications do.

   Show 5 topics
   • Why opening a connection per query is slow
   • DataSource vs DriverManager
   • HikariCP setup and key settings
   • Loading configuration from a properties file
   • Running SQL scripts at startup

9. 13.9JDBC Mini Project: ShopLite Console App

   Build a menu-driven ShopLite app with Maven, JDBC and DAOs on the database from the SQL section.

   Show 6 topics
   • Project structure: model, dao, service, app
   • Product and customer management
   • Placing orders inside a transaction
   • Order history and sales reports
   • Input validation and clear error messages
   • Pushing the project to GitHub with a README