SpringBoot入門：DBから値を取得

気軽に試せるH2データベースを使えるようにする

src/main/resources/
application.properties

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

spring.sql.init.encoding=UTF-8
spring.sql.init.mode=always
spring.sql.init.schema-locations=classpath:schema.sql
spring.sql.init.data-locations=classpath:data.sql

spring.h2.console.enabled=true

スキーマとテストデータのSQL

schema.sql

create table if not exists employee(
  id varchar(50) primary key,
  name varchar(50),
  age int
);

data.sql

insert into employee(id, name, age)
values('1', 'Tom', 30);

Model、Repository、Service、Controller、Viewを作成、修正

src/main/java/com.example.demo/hello
Employee.java

@Data
public class Employee {
	private String employeeId;
	private String employeeName;
	private int employeeAge;
}

HelloRepository.java

@Repository
public class HelloRepository {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	public Map<String, Object> findById(String id) {
		String query = "select *"
				+ " from employee"
				+ " where id=?";		
		Map<String, Object> employee = jdbcTemplate.queryForMap(query, id);		
		return employee;
	}
}

HelloService.java

@Service
public class HelloService {
	@Autowired
	private HelloRepository repository;
	
	public Employee getEmployee(String id) {
		Map<String, Object> map = repository.findById(id);
		
		String employeeId = (String) map.get("id");
		String name = (String) map.get("name");
		int age = (Integer) map.get("age");
		
		Employee employee = new Employee();
		employee.setEmployeeId(employeeId);
		employee.setEmployeeName(name);
		employee.setEmployeeAge(age);
		
		return employee;
	}
}

HelloController.javaに追記

	@Autowired
	private HelloService service;
	
	@PostMapping("/hello/db")
	public String postDbRequest(@RequestParam("eid") String id, Model model) {
		Employee employee = service.getEmployee(id);
		model.addAttribute("employee", employee);
		return "hello/db";
	}

src/main/resources/templates
db.htmlを作成

        <p th:text="${employee.employeeId}"></p>
        <p th:text="${employee.employeeName}"></p>
        <p th:text="${employee.employeeAge}"></p>

hello.htmlに追記

        <form method="post" action="/hello/db">
            <input type="text" name="eid" th:value="${eid_value}">
            <button>送信</button>
        </form>

H2データベースのコンソール
http://localhost:8080/h2-console
JDBC URL: jdbc:h2:mem:testdb

forget for get

覚えるために忘れる

SpringBoot入門：DBから値を取得