application.properties

spring.datasource.url=jdbc:h2:mem:db;DB_CLOSE_DELAY=-1
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.driver-class-name=org.h2.Driver

create.java

// Create
public Customer create(Customer customer) {
  String sql = "INSERT INTO customer (name, email) VALUES (?, ?)";
  KeyHolder keyHolder = new GeneratedKeyHolder();
  this.jdbcTemplate.update(connection -> {
    PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    statement.setString(1, customer.getName());
    statement.setString(2, customer.getEmail());
    return statement;
  }, keyHolder);
  Integer newCustomerId = keyHolder.getKey().intValue();
  customer.setId(newCustomerId);
  return customer;
}

Customer.java

package io.github.htr3n.springjdbcsimple.entity;

public class Customer {
  private Integer id;
  private String name;
  private String email;

  public Integer getId() {
    return id;
  }
  public void setId(Integer id) {
    this.id = id;
  }
  public String getName() {
    return name;
  }
  public void setName(String name) {
    this.name = name;
  }
  public String getEmail() {
    return email;
  }
  public void setEmail(String email) {
    this.email = email;
  }
}

CustomerDao.java

package io.github.htr3n.springjdbcsimple.dao;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;

@Repository
public class CustomerDao {

  @Autowired
  private JdbcTemplate jdbcTemplate;

  // Create
  public Customer create(Customer customer) { ... }
  
  // Retrieve
  public List<Customer> findAll() {...}

  // Retrieve
  public Optional<Customer> findById(Integer id) { ... } 
  
  // Update
  public boolean update(Customer customer) { ... } 
  
  // Delete
  public boolean delete(Integer id) { ... }
  
}

CustomerDaoTest.java

package io.github.htr3n.springjdbcsimple.dao;

import io.github.htr3n.springjdbcsimple.entity.Customer;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.jdbc.JdbcTest;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;
import java.util.Optional;
import java.util.Random;

import static org.assertj.core.api.Assertions.assertThat;

@RunWith(SpringRunner.class)
@JdbcTest
@ComponentScan
public class CustomerDaoTest {

  private static final String ALICE_NAME = "Alice";
  private static final String ALICE_EMAIL = "alice@test.com";
  private static final String BOB_NAME = "Bob";
  private static final String BOB_EMAIL = "bob@test.com";
  private static final int ONE_CUSTOMER = 1;
  private static final int TWO_CUSTOMERS = 2;

  @Autowired
  private CustomerDao customerDao;

  @Test
  public void create_shouldReturnValidCustomer_whenAddingNewCustomer() {

    Customer alice = new Customer();
    alice.setName(ALICE_NAME);
    alice.setEmail(ALICE_EMAIL);
    customerDao.create(alice);

    assertThat(alice.getId()).isNotNull();

    Optional<Customer> result = customerDao.findById(alice.getId());

    assertThat(result.isPresent()).isTrue();
    assertThat(alice).hasFieldOrPropertyWithValue("name", ALICE_NAME);
    assertThat(alice).hasFieldOrPropertyWithValue("email", ALICE_EMAIL);
  }

  @Test
  public void findById_shouldReturnInvalidCustomer_forEmptyDatabase() {
    Optional<Customer> invalidCustomer = customerDao.findById(new Random().nextInt());
    assertThat(invalidCustomer.isPresent()).isFalse();
  }

  @Test
  public void findById_shouldReturnValidCustomer_forExistingCustomer() {
    final Customer alice = new Customer();
    alice.setName(ALICE_NAME);
    alice.setEmail(ALICE_EMAIL);
    customerDao.create(alice);

    Optional<Customer> validCustomer = customerDao.findById(alice.getId());
    assertThat(validCustomer.isPresent()).isTrue();
    assertThat(validCustomer.get().getName()).isEqualTo(alice.getName());
    assertThat(validCustomer.get().getEmail()).isEqualTo(alice.getEmail());
  }

  @Test
  public void findAll_shouldYieldEmptyList_forEmptyDatabase() {
    List<Customer> noCustomers = customerDao.findAll();
    assertThat(noCustomers).isNullOrEmpty();
  }

  @Test
  public void findAll_shouldYieldListOfCustomers_forNonemptyDatabase() {

    Customer alice = new Customer();
    alice.setName(ALICE_NAME);
    alice.setEmail(ALICE_EMAIL);
    customerDao.create(alice);

    List<Customer> customers = customerDao.findAll();
    assertThat(customers).isNotNull().hasSize(ONE_CUSTOMER);

    Customer result = customers.get(0);
    assertThat(result).hasFieldOrPropertyWithValue("name", ALICE_NAME);
    assertThat(result).hasFieldOrPropertyWithValue("email", ALICE_EMAIL);


    Customer bob = new Customer();
    bob.setName(BOB_NAME);
    bob.setEmail(BOB_EMAIL);
    customerDao.create(bob);

    customers = customerDao.findAll();
    assertThat(customers).isNotNull().hasSize(TWO_CUSTOMERS);
  }

  @Test
  public void update_shouldYieldFalse_forEmptyDatabase() {
    Customer notFound = new Customer();
    notFound.setId(new Random().nextInt());
    assertThat(customerDao.update(notFound)).isFalse();
  }

  @Test
  public void update_shouldYieldTrue_forExistingCustomer() {
    Customer customer = new Customer();
    customer.setName(ALICE_NAME);
    customer.setEmail(ALICE_EMAIL);
    customerDao.create(customer);

    assertThat(customer.getId()).isNotNull();
    assertThat(customerDao.update(customer)).isTrue();

    customer.setName(BOB_NAME);
    customer.setEmail(BOB_EMAIL);
    assertThat(customerDao.update(customer)).isTrue();

    Optional<Customer> found = customerDao.findById(customer.getId());
    assertThat(found.isPresent()).isTrue();
    assertThat(found.get().getName()).isEqualTo(customer.getName());
    assertThat(found.get().getEmail()).isEqualTo(customer.getEmail());
  }

  @Test
  public void delete_shouldYieldFalse_forEmptyDatabaseOrNonexistingCustomer() {
    assertThat(customerDao.delete(new Random().nextInt())).isFalse();
  }

  @Test
  public void delete_shouldYieldTrue_forExistingCustomer() {
    Customer alice = new Customer();
    alice.setName(ALICE_NAME);
    alice.setEmail(ALICE_EMAIL);
    customerDao.create(alice);

    assertThat(customerDao.findAll()).hasSize(ONE_CUSTOMER);
    assertThat(customerDao.delete(alice.getId())).isTrue();
    assertThat(customerDao.findById(alice.getId()).isPresent()).isFalse();
    assertThat(customerDao.findAll()).isEmpty();
  }
}

CustomerMapper.java

class CustomerMapper implements RowMapper<Customer> {
  @Override
  public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {
      Customer customer = new Customer();
      customer.setId(rs.getInt("id"));
      customer.setName(rs.getString("name"));
      customer.setEmail(rs.getString("email"));
      return customer;
  }
}

delete.java

// Delete
public boolean delete(Integer id) {
  String sql = "DELETE FROM customer WHERE id = ?";
  Object[] params = new Object[]{id};
  return this.jdbcTemplate.update(sql, params) == 1;
}

JDBC.java

DataSource ds = ...; // obtain a DataSource object
try (
     Connection conn = ds.getConnection();
     Statement stmt = conn.createStatement()) {
    try (ResultSet rs = smmt.executeQuery("SELECT * FROM customer")){
        while (rs.next()){
            // process the ResultSet
        }
    } catch (SQLException e2) {
    	e2.printStackTrace();    
    }
} catch (SQLException e1) {
    e1.printStackTrace();
}

problematic-findCustomerById.java

public Optional<Customer> findById(Integer id) {
  String sql = "SELECT id, name, email FROM customer WHERE id = ?";
  return Optional.of(jdbcTemplate.queryForObject(sql, new Object[] { id }, new CustomerMapper()));
}

retrieval.java

// Retrieve
public List<Customer> findAll() {
  String sql = "SELECT * FROM customer";
  return this.jdbcTemplate.query(sql, new CustomerMapper());
}

// Retrieve
public Optional<Customer> findCustomerById(Integer id) {
  String sql = "SELECT id, name, email FROM customer WHERE id = ?";
  return this.jdbcTemplate.query(sql, 
                                 rs -> rs.next() ? Optional.of(new CustomerMapper().mapRow(rs, 1)): Optional.empty(), 
                                 id);
}

schema.sql

CREATE TABLE IF NOT EXISTS customer(
  id integer not null auto_increment,
  name varchar(255) not null,
  email varchar (255) not null,
  primary key (id)
);

update.java

// Update
public boolean update(Customer customer) {
  String sql = "UPDATE customer SET name=?, email=? WHERE id=?";
  Object[] params = new Object[]{customer.getName(), customer.getEmail(), customer.getId()};
  return this.jdbcTemplate.update(sql, params) == 1;
}