Module 3 — Working with Databases (Java Backend)

Instructor note: This module introduces relational and non-relational databases for backend applications. Students learn how to design schemas, connect Java applications to databases, and execute CRUD operations securely and efficiently.

Learning outcomes

1. Introduction to Databases

Backend systems rely heavily on databases for data persistence. Two primary types:

• Relational Databases (SQL): MySQL, PostgreSQL, Oracle — structured, schema-based.
• NoSQL Databases: MongoDB, Redis — flexible schema, document or key-value based.

2. Relational Database Concepts

• Tables, rows, and columns
• Primary and foreign keys
• Normalization (1NF, 2NF, 3NF)
• Joins and relationships (one-to-one, one-to-many, many-to-many)

3. Setting up a Local Database

Install and configure MySQL or use the embedded H2 database for simplicity during development.

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.hibernate.ddl-auto=update
spring.h2.console.enabled=true

4. Database Connectivity using JDBC

JDBC (Java Database Connectivity) provides low-level APIs for database interaction.

import java.sql.*;

public class JdbcExample {
  public static void main(String[] args) {
    String url = "jdbc:h2:mem:testdb";
    try (Connection conn = DriverManager.getConnection(url, "sa", "")) {
      Statement stmt = conn.createStatement();
      stmt.executeUpdate("CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(50))");
      stmt.executeUpdate("INSERT INTO users VALUES(1, 'Alice')");

      ResultSet rs = stmt.executeQuery("SELECT * FROM users");
      while (rs.next()) {
        System.out.println(rs.getInt("id") + ": " + rs.getString("name"));
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}

5. Using JPA (Hibernate) for ORM

JPA simplifies database access using object-relational mapping. Entities are Java classes mapped to database tables.

@Entity
public class User {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;
  private String name;
  private String email;

  // Getters and setters
}

public interface UserRepository extends JpaRepository<User, Long> { }

Spring Boot automatically manages entity mapping and SQL generation.

6. CRUD Operations

@RestController
@RequestMapping("/api/users")
public class UserController {
  @Autowired
  private UserRepository userRepository;

  @GetMapping
  public List<User> getAll() {
    return userRepository.findAll();
  }

  @PostMapping
  public User create(@RequestBody User user) {
    return userRepository.save(user);
  }

  @DeleteMapping("/{id}")
  public void delete(@PathVariable Long id) {
    userRepository.deleteById(id);
  }
}

7. Transactions and Rollback

Transactions ensure data consistency. Use @Transactional to manage operations that must succeed or fail as a unit.

@Service
public class PaymentService {
  @Autowired
  private AccountRepository repo;

  @Transactional
  public void transfer(Long fromId, Long toId, double amount) {
    repo.debit(fromId, amount);
    repo.credit(toId, amount);
  }
}

8. NoSQL Database Example (MongoDB)

MongoDB stores documents in JSON-like format. Java connects through Spring Data MongoDB.

@Document(collection = "students")
public class Student {
  @Id
  private String id;
  private String name;
  private String course;
}

public interface StudentRepository extends MongoRepository<Student, String> { }

9. Exercises

10. Assessment Criteria

• Pass: Successful connection to database and basic CRUD functionality.
• Credit: Use of JPA repositories with entity relationships.
• Distinction: Demonstrates transaction management and documentation of data model.

Further Reading

• Spring Data JPA Reference — spring.io
• Hibernate User Guide
• MongoDB Java Driver Documentation

Prepared from a professor’s perspective — technical, structured, and emphasizing both conceptual and implementation depth for backend data management.