Skip to content

Instantly share code, notes, and snippets.

@htr3n htr3n/Customer.java
Last active Nov 20, 2018

Embed
What would you like to do?
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
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;
}
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;
}
}
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) { ... }
}
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();
}
}
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
public boolean delete(Integer id) {
String sql = "DELETE FROM customer WHERE id = ?";
Object[] params = new Object[]{id};
return this.jdbcTemplate.update(sql, params) == 1;
}
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();
}
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()));
}
// 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);
}
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
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;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.