在Spring Boot H2資料庫的datasource jdbc url設定如下。
Spring Boot H2資料庫預設已配置datasource的jdbc url，所以不用任何額外設定即可搭配Spring Data JPA或JdbcTemplate存取H2的資料。
本篇的由來是因為同事第一次接觸H2資料庫，查了一些二手資料以為必須在application.properties設定datasource及url等，但如上述Spring Boot只要匯入H2依賴函式庫就能直接用了不用額外設定，除非有額外的配置需求。
測試環境：
- Spring Boot 2.3.2.RELEASE
- Spring Data JPA
- H2 database
- Lombok
- JUnit 5
Spring Boot專案的pom.xml。
H2資料庫初始資料data.sql設定如下。
INSERT INTO EMPLOYEE (ID, NAME, AGE) VALUES (1, 'John', 22);
INSERT INTO EMPLOYEE (ID, NAME, AGE) VALUES (2, 'Mary', 25);
INSERT INTO EMPLOYEE (ID, NAME, AGE) VALUES (3, 'Andy', 33);
專案的application.properties並無配置任何的datasource。
application.properties
#context path
server.servlet.context-path=/demo
#port
server.port=8080
也沒在@Configuration類中配置任何DataSource的Bean。
設定entity類Employee如下。
Employee
package com.abc.demo.entity;
import lombok.Data;
import javax.persistence.Entity;
import javax.persistence.Id;
import java.io.Serializable;
@Data
@Entity
public class Employee implements Serializable {
private static final Long serialVersionUID = 1L;
@Id
private Long id;
private String name;
private Integer age;
}
使用Spring Data JPA存取
EmployeeRepository用來存取Employee。
EmployeeRepository
package com.abc.demo.repository;
import com.abc.demo.entity.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
}
測試
EmployeeRepositoryTests
package com.abc.demo.repository;
import com.abc.demo.entity.Employee;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import javax.persistence.EntityNotFoundException;
@SpringBootTest
public class EmployeeRepositoryTests {
@Autowired
private EmployeeRepository employeeRepository;
@Test
void findById_test() {
Employee employee = employeeRepository.findById(1L).orElseThrow(EntityNotFoundException::new);
System.out.println(employee); // Employee(id=1, name=John, age=22)
Assertions.assertEquals("John", employee.getName()); // pass by 肉豬
}
}
使用JdbcTemplate存取
EmployeeRepository用來存取Employee。
EmployeeDao中使用NamedParameterJdbcTemplate用來存取Employee。
EmployeeDao
package com.abc.demo.dao;
import com.abc.demo.entity.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
@Service
public class EmployeeDao {
@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public Employee findById(long id) {
String sql = "select * from EMPLOYEE where id = :id";
MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("id", id);
return namedParameterJdbcTemplate.queryForObject(sql, params, new BeanPropertyRowMapper<>(Employee.class));
}
}
測試
EmployeeDaoTests
package com.abc.demo.dao;
import com.abc.demo.entity.Employee;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest
public class EmployeeDaoTests {
@Autowired
private EmployeeDao employeeDao;
@Test
void findById_test() {
Employee employee = employeeDao.findById(1);
System.out.println(employee); // Employee(id=1, name=John, age=22)
Assertions.assertEquals("John", employee.getName()); // pass by 肉豬
}
}
參考github。
沒有留言:
張貼留言