網頁

2020/12/30

Spring Boot H2 Database JdbcTemplate datasource jdbc url

在Spring Boot H2資料庫的datasource jdbc url設定如下。

Spring Boot H2資料庫預設已配置datasource的jdbc url,所以不用任何額外設定即可搭配Spring Data JPAJdbcTemplate存取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


沒有留言:

張貼留言