repository - crud (JdbcTemplate)

 

Domain

public class Order {

	private Long id;
	private Long userId;
	private Long productId;
	private OrderState state;
	private String rejectMsg;
	private LocalDateTime createAt;
    
    // constructor & getter & toString  
}

 

 

Repository

@Repository
public class JdbcOrderRepository {

	private final JdbcTemplate jdbcTemplate;

	public JdbcOrderRepository(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public Optional<Order> findById(final Long id) {
		final String sql = "SELECT * FROM orders WHERE id=?";
		final List<Order> orders = jdbcTemplate.query(
				sql,
				rowMapper(),
				id);
		return Optional.ofNullable((orders.isEmpty()) ? null : orders.get(0));
	}

	// offset based pagination
	public List<Order> findAll(final SearchCondition searchCondition) {
		String sql = "SELECT * FROM orders ORDER BY id DESC LIMIT ?,?";
		return jdbcTemplate.query(
				sql,
				rowMapper(),
				searchCondition.offset(),
				searchCondition.size());
	}

	public void update(final Order order) {
		String sql = "UPDATE orders SET state=? WHERE id=?";
		jdbcTemplate.update(
				sql,
				order.getState(),
				order.getId()
		);
	}

	public Order save(final Order order) {
		String sql = "INSERT INTO orders(user_id, product_id, state, reject_msg, create_at) VALUES(?, ?, ?, ?, ?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();

		jdbcTemplate.update(con -> {
			PreparedStatement pstmt = con.prepareStatement(sql, new String[] {"id"});
			pstmt.setLong(1, order.getUserId());
			pstmt.setString(2, order.getState().toString());
			pstmt.setString(3, order.getRejectMsg());
			pstmt.setTimestamp(4, Timestamp.valueOf(order.getCreateAt()));
			return pstmt;
		}, keyHolder);

		return new Order(
				keyHolder.getKey().longValue(),
				order.getUserId(),
				order.getProductId(),
				order.getState(),
				order.getRejectMsg(),
				order.getCreateAt());
	}

	public void delete(final Order order) {
		String sql = "DELETE FROM orders WHERE id=?";

		jdbcTemplate.update(
				sql,
				order.getId()
		);
	}

	private RowMapper<Order> rowMapper() {
		return ((rs, rowNum) -> new Order(
				rs.getLong("id"),
				rs.getLong("user_id"),
				rs.getLong("product_id"),
				OrderState.valueOf(rs.getString("order_state")),
				rs.getString("reject_msg"),
				rs.getTimestamp("create_at").toLocalDateTime()
		));
	}

}