《Pro Spring Boot 2》第五章：Data Access with Spring Boot
package com.apress.todo.repository; import com.apress.todo.domain.ToDo; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; import org.springframework.stereotype.Repository; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.time.Instant; import java.time.LocalDate; import java.time.LocalDateTime; import java.time.ZoneId; import java.time.temporal.TemporalAccessor; import java.util.*; @Repository public class ToDoRepository implements CommonRepository<ToDo> { private static final String SQL_INSERT = "insert into todo (id, description, created, modified, completed) values (:id,:description,:created,:modified,:completed)"; private static final String SQL_QUERY_FIND_ALL = "select id, description, created, modified, completed from todo"; private static final String SQL_QUERY_FIND_BY_ID = SQL_QUERY_FIND_ALL + " where id = :id"; private static final String SQL_UPDATE = "update todo set description = :description, modified = :modified, completed = :completed where id = :id"; private static final String SQL_DELETE = "delete from todo where id = :id"; private final NamedParameterJdbcTemplate jdbcTemplate; public ToDoRepository(NamedParameterJdbcTemplate jdbcTemplate){ this.jdbcTemplate = jdbcTemplate; } private RowMapper<ToDo> toDoRowMapper = (ResultSet rs, int rowNum) -> { ToDo toDo = new ToDo(); toDo.setId(rs.getString("id")); toDo.setDescription(rs.getString("description")); toDo.setModified(rs.getTimestamp("modified").toLocalDateTime()); toDo.setCreated(rs.getTimestamp("created").toLocalDateTime()); toDo.setCompleted(rs.getBoolean("completed")); return toDo; }; @Override public ToDo save(final ToDo domain) { ToDo result = findById(domain.getId()); if(result != null){ result.setDescription(domain.getDescription()); result.setCompleted(domain.isCompleted()); result.setModified(LocalDateTime.now()); return upsert(result, SQL_UPDATE); } return upsert(domain,SQL_INSERT); } private ToDo upsert(final ToDo toDo, final String sql){ Map<String, Object> namedParameters = new HashMap<>(); namedParameters.put("id",toDo.getId()); namedParameters.put("description",toDo.getDescription()); namedParameters.put("created",java.sql.Timestamp.valueOf(toDo.getCreated())); namedParameters.put("modified",java.sql.Timestamp.valueOf(toDo.getModified())); namedParameters.put("completed",toDo.isCompleted()); this.jdbcTemplate.update(sql,namedParameters); return findById(toDo.getId()); } @Override public Iterable<ToDo> save(Collection<ToDo> domains) { domains.forEach( this::save); return findAll(); } @Override public void delete(final ToDo domain) { Map<String, String> namedParameters = Collections.singletonMap("id", domain.getId()); this.jdbcTemplate.update(SQL_DELETE,namedParameters); } @Override public ToDo findById(String id) { try { Map<String, String> namedParameters = Collections.singletonMap("id", id); return this.jdbcTemplate.queryForObject(SQL_QUERY_FIND_BY_ID, namedParameters, toDoRowMapper); } catch (EmptyResultDataAccessException ex) { return null; } } @Override public Iterable<ToDo> findAll() { return this.jdbcTemplate.query(SQL_QUERY_FIND_ALL, toDoRowMapper); } }
点击Connect
package com.apress.todo.repository; import com.apress.todo.domain.ToDo; import org.springframework.data.repository.CrudRepository; public interface ToDoRepository extends CrudRepository<ToDo,String> { }
package com.apress.todo.domain; import lombok.Data; import lombok.NoArgsConstructor; import org.hibernate.annotations.GenericGenerator; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.Id; import javax.persistence.PrePersist; import javax.persistence.PreUpdate; import javax.validation.constraints.NotBlank; import javax.validation.constraints.NotNull; import java.time.LocalDateTime; @Entity @Data @NoArgsConstructor public class ToDo { @Id @GeneratedValue(generator = "system-uuid") @GenericGenerator(name = "system-uuid", strategy = "uuid") private String id; @NotNull @NotBlank private String description; @Column(insertable = true, updatable = false) private LocalDateTime created; private LocalDateTime modified; private boolean completed; public ToDo(String description){ this.description = description; } @PrePersist void onCreate() { this.setCreated(LocalDateTime.now()); this.setModified(LocalDateTime.now()); } @PreUpdate void onUpdate() { this.setModified(LocalDateTime.now()); } }
package com.apress.todo.controller; import com.apress.todo.domain.ToDo; import com.apress.todo.domain.ToDoBuilder; import com.apress.todo.repository.ToDoRepository; import com.apress.todo.validation.ToDoValidationError; import com.apress.todo.validation.ToDoValidationErrorBuilder; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.http.HttpStatus; import org.springframework.http.ResponseEntity; import org.springframework.validation.Errors; import org.springframework.web.bind.annotation.*; import org.springframework.web.servlet.support.ServletUriComponentsBuilder; import javax.validation.Valid; import java.net.URI; import java.util.Optional; //@CrossOrigin(origins = "*", allowedHeaders = "*") @RestController @RequestMapping("/api") public class ToDoController { private static Logger log = LoggerFactory.getLogger(ToDoController.class); private ToDoRepository toDoRepository; @Autowired public ToDoController(ToDoRepository toDoRepository) { this.toDoRepository = toDoRepository; } @GetMapping("/todo") public ResponseEntity<Iterable<ToDo>> getToDos(){ return ResponseEntity.ok(toDoRepository.findAll()); } @GetMapping("/todo/{id}") public ResponseEntity<ToDo> getToDoById(@PathVariable String id){ Optional<ToDo> toDo = toDoRepository.findById(id); if(toDo.isPresent()) return ResponseEntity.ok(toDo.get()); return ResponseEntity.notFound().build(); } @PatchMapping("/todo/{id}") public ResponseEntity<ToDo> setCompleted(@PathVariable String id){ Optional<ToDo> toDo = toDoRepository.findById(id); if(!toDo.isPresent()) return ResponseEntity.notFound().build(); ToDo result = toDo.get(); result.setCompleted(true); toDoRepository.save(result); URI location = ServletUriComponentsBuilder.fromCurrentRequest() .buildAndExpand(result.getId()).toUri(); return ResponseEntity.ok().header("Location",location.toString()).build(); } @RequestMapping(value="/todo", method = {RequestMethod.POST,RequestMethod.PUT}) public ResponseEntity<?> createToDo(@Valid @RequestBody ToDo toDo, Errors errors){ if (errors.hasErrors()) { errors.getAllErrors().stream().forEach(e -> log.error(e.getObjectName() + " " + e.getDefaultMessage())); return ResponseEntity.badRequest().body(ToDoValidationErrorBuilder.fromBindingErrors(errors)); } ToDo result = toDoRepository.save(toDo); URI location = ServletUriComponentsBuilder.fromCurrentRequest().path("/{id}") .buildAndExpand(result.getId()).toUri(); return ResponseEntity.created(location).body(result); //ResponseEntity.created(location).build(); } @DeleteMapping("/todo/{id}") public ResponseEntity<ToDo> deleteToDo(@PathVariable String id){ toDoRepository.delete(ToDoBuilder.create().withId(id).build()); return ResponseEntity.noContent().build(); } @DeleteMapping("/todo") public ResponseEntity<ToDo> deleteToDo(@RequestBody ToDo toDo){ toDoRepository.delete(toDo); return ResponseEntity.noContent().build(); } @ExceptionHandler @ResponseStatus(value = HttpStatus.BAD_REQUEST) public ToDoValidationError handleException(Exception exception) { log.error(exception.getMessage()); return new ToDoValidationError(exception.getMessage()); } }
