JDBC_SETUP.md

JDBC Practice Setup for OCP 17 Exam

This guide will help you set up PostgreSQL with Docker and run JDBC practice examples.

Prerequisites

• Docker and Docker Compose installed
• Java 17 (already configured)
• Maven (IntelliJ IDEA has Maven bundled)

Setup Instructions

1. Start PostgreSQL Database

Open a terminal in the project directory and run:

docker-compose up -d

This will:

• Start a PostgreSQL 16 container
• Create a database named ocp_practice
• Initialize tables: employees, departments, projects
• Insert sample data
• Expose PostgreSQL on port 5432

To verify the database is running:

docker-compose ps

To view logs:

docker-compose logs postgres

2. Configure IntelliJ IDEA for Maven

1. Open the project in IntelliJ IDEA
2. IntelliJ should automatically detect the pom.xml and prompt you to import the Maven project
3. If not, right-click on pom.xml → "Add as Maven Project"
4. Wait for Maven to download the PostgreSQL JDBC driver dependency

3. Verify Database Connection

You can connect to the database using:

• Host: localhost
• Port: 5432
• Database: ocp_practice
• Username: ocpuser
• Password: ocppass123

Using psql (if installed):

docker exec -it ch16JDBC-practice-db psql -U ocpuser -d ocp_practice

Or use IntelliJ's Database tool:

1. View → Tool Windows → Database
2. Click "+" → Data Source → PostgreSQL
3. Enter the connection details above

Running JDBC Examples

The examples are organized by topic and located in src/ch16JDBC/:

Example 1: Basic Connection

mvn compile exec:java -Dexec.mainClass="ch16JDBC.Example01_BasicConnection"

Topics covered:

• Opening database connections
• Using try-with-resources
• Statement vs PreparedStatement
• Basic ResultSet iteration

Example 2: CRUD Operations

mvn compile exec:java -Dexec.mainClass="ch16JDBC.Example02_CRUDOperations"

Topics covered:

• INSERT with auto-generated keys
• SELECT with PreparedStatement
• UPDATE operations
• DELETE operations
• executeUpdate() return values

Example 3: ResultSet Navigation

mvn compile exec:java -Dexec.mainClass="ch16JDBC.Example03_ResultSetNavigation"

Topics covered:

• Forward-only vs scrollable ResultSets
• Navigation methods: first(), last(), absolute(), relative()
• Position checking: isFirst(), isLast(), isBeforeFirst()
• Updatable ResultSets

Example 4: Transactions

mvn compile exec:java -Dexec.mainClass="ch16JDBC.Example04_Transactions"

Topics covered:

• Auto-commit mode
• Manual transactions with commit() and rollback()
• Savepoints for partial rollbacks
• Transaction error handling

Example 5: Batch Processing

mvn compile exec:java -Dexec.mainClass="ch16JDBC.Example05_BatchProcessing"

Topics covered:

• addBatch() and executeBatch()
• Batch inserts with PreparedStatement
• Mixed batch operations
• BatchUpdateException handling

Example 6: MetaData

mvn compile exec:java -Dexec.mainClass="ch16JDBC.Example06_MetaData"

Topics covered:

• DatabaseMetaData for database information
• ResultSetMetaData for column information
• Getting table and column details
• Dynamic result processing

Example 7: execute() Method

mvn compile exec:java -Dexec.mainClass="ch16JDBC.Example07_ExecuteMethod"

Topics covered:

• execute() method returning boolean
• Checking if result is ResultSet or update count
• getResultSet() and getUpdateCount() methods
• Handling unknown SQL types at runtime
• Difference between execute(), executeQuery(), and executeUpdate()

Example 8: CallableStatement

First, load the stored procedures into the database:

PowerShell:

Get-Content stored-procedures.sql | docker exec -i ch16JDBC-practice-db psql -U ocpuser -d ocp_practice

Bash/Git Bash:

docker exec -i ch16JDBC-practice-db psql -U ocpuser -d ocp_practice < stored-procedures.sql

Then run the example:

mvn compile exec:java -Dexec.mainClass="ch16JDBC.Example08_CallableStatement"

Topics covered:

• CallableStatement for calling stored procedures/functions
• Functions with NO parameters
• IN parameters only
• OUT parameters
• INOUT parameters (same parameter used for input and output)
• Mixed IN and OUT parameters
• Syntax patterns: {? = call func()} vs {call proc(?)}

Running from IntelliJ IDEA

1. Navigate to the example file (e.g., Example01_BasicConnection.java)
2. Right-click on the file or the main method
3. Select "Run 'Example01_BasicConnection.main()'"

Database Management

Stop the database:

docker-compose down

Stop and remove all data:

docker-compose down -v

Restart the database:

docker-compose restart

Reset the database to initial state:

docker-compose down -v
docker-compose up -d

OCP Exam Topics Covered

This setup covers key JDBC topics for the Java SE 17 Developer (1Z0-829) exam:

1. Connecting to databases using JDBC URLs and DriverManager
2. Constructing and using RowSet objects
3. Performing CRUD operations (Create, Read, Update, Delete)
4. Processing query results using ResultSet
5. Using PreparedStatement and CallableStatement
6. Controlling transactions
7. Using batch updates
8. Working with database metadata

Additional Practice Ideas

• Modify the examples to use different SQL queries
• Create your own tables and practice with them
• Experiment with different ResultSet types and concurrency modes
• Practice error handling and exception scenarios
• Try using connection pooling libraries (HikariCP, C3P0)

Troubleshooting

Connection refused error

• Make sure Docker is running: docker ps
• Check if PostgreSQL container is up: docker-compose ps
• Verify port 5432 is not used by another application

Class not found: org.postgresql.Driver

• Ensure Maven dependencies are downloaded
• In IntelliJ: Right-click on pom.xml → Maven → Reload Project

Can't connect to database from Java

• Verify the database is accessible: docker-compose logs postgres
• Check connection string in DatabaseConfig.java
• Ensure no firewall is blocking port 5432

Resources

• PostgreSQL JDBC Driver Documentation
• Java SE 17 JDBC API Documentation
• OCP Java SE 17 Developer Exam Topics