Go to Qiita Advent Calendar 2024 Top
LoginSignup
100
123

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@t-iguchi

SpringBoot + Spring JPAでデータベースに接続する

Last updated at Posted at 2018-04-17

##SpringBootでデータベースにアクセスしてみます

jpaを使うために、以下の作業を行います

・application.propertiesにＤＢ接続のための設定を行う
・Entity, Repositoryというクラスを作成する

1.構成

sample-jpa
│  build.gradle
└─src
    └─main
        ├─java
        │  └─com
        │      └─jpasample
        │              Employee.java
        │              EmployeeRepository.java
        │              HeloController.java
        │              SamplePrj004JpaApplication.java
        │              ServletInitializer.java
        └─resources
            │  application.properties
            ├─static
            └─templates
                    index.html

2.ＤＢ接続情報の設定
application.propertiesに以下のように設定します

application.properties
spring.datasource.url=jdbc:mysql://localhost:3306/sampledb
spring.datasource.username=testuser
spring.datasource.password=testuser
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.jpa.database=MYSQL
spring.jpa.hibernate.ddl-auto=update

spring.jpa.hibernate.ddl-auto=updateとしておくと、
ＤＢにテーブルが定義されていなくてもテーブルが作成されます。

3.依存関係

build.gradle
dependencies {
	compile('org.springframework.boot:spring-boot-starter-data-jpa')
	compile('org.springframework.boot:spring-boot-starter-thymeleaf')
	compile('org.springframework.boot:spring-boot-starter-web')
	runtime('org.springframework.boot:spring-boot-devtools')
	runtime('mysql:mysql-connector-java')
	compileOnly('org.projectlombok:lombok')
	providedRuntime('org.springframework.boot:spring-boot-starter-tomcat')
	testCompile('org.springframework.boot:spring-boot-starter-test')
}

4.データベースにテーブルを定義する。

従業員マスタを検索するサンプルとしています。


CREATE TABLE `m_emp` (
  `empno` bigint(20) NOT NULL AUTO_INCREMENT,
  `empname` varchar(255) DEFAULT NULL,
  `departmentid` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

INSERT INTO sampledb.m_emp
(empname,departmentid)
VALUES
 ('従業員A','10101001')
,('従業員B','10101001')
,('従業員C','10101002')
,('従業員D','10101003')

5.Entityクラスを作成する

Employee.java
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="m_emp")
public class Employee {
	@Id
	@Column(name="empno")
	@GeneratedValue(strategy=GenerationType.IDENTITY)
	private Long id;
	private String empname;
	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public String getEmpname() {
		return empname;
	}
	public void setEmpname(String empname) {
		this.empname = empname;
	}
}

6.Repository

EmployeeRepository.java
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {}

7.Controller

従業員マスタを全件検索しています。

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

@Controller
public class HeloController {
	@Autowired
	EmployeeRepository empRepository;
	@RequestMapping(value = "/", method = RequestMethod.GET)
    public String index(Model model) {
		List<Employee> emplist=empRepository.findAll();
        model.addAttribute("emplist", emplist);
        return "index";
    }
}

8.html

index.html
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:th="http://www.thymeleaf.org">
	<head>
		<title>Springboot</title>
		<meta charset="utf-8" />
	</head>
	<body>
		<h1 th:text="${title}"></h1>
		<table>
			<tr th:each="emp : ${emplist}" th:object="${emp}">
				<td th:text="*{id}"></td>
				<td th:text="*{empname}"></td>
			</tr>
		</table>
	</body>
</html>

9.Springbootを実行してＵＲＬにアクセスしてみます。
http://localhost:8080/

image.png

無事表示されました。

2019.10.08 追記

postgresqlに接続する場合

application.propertiesは以下のようになる。

application.properties
spring.jpa.database=POSTGRESQL
spring.datasource.url=jdbc:postgresql://localhost:5432/testdb
spring.datasource.username=testuser
spring.datasource.password=secret
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

しかし、これだけでは以下のようなエラーが発生する。

Caused by: java.sql.SQLFeatureNotSupportedException: org.postgresql.jdbc.PgConnection.createClob() メソッドはまだ実装されていません。
	at org.postgresql.Driver.notImplemented(Driver.java:688) ~[postgresql-42.2.5.jar:42.2.5]
	at org.postgresql.jdbc.PgConnection.createClob(PgConnection.java:1269) ~[postgresql-42.2.5.jar:42.2.5]

解決方法

hibernate.propertiesを、resourcesフォルダの直下（application.propertiesと同じ階層）に作成する。

hibernate.properties
hibernate.jdbc.lob.non_contextual_creation = true

無事、接続できた。

2019-10-08 23:37:42.626  INFO 5464 --- [           main] org.hibernate.Version                    : HHH000412: Hibernate Core {5.3.7.Final}
2019-10-08 23:37:42.626  INFO 5464 --- [           main] org.hibernate.cfg.Environment            : HHH000205: Loaded properties from resource hibernate.properties: {hibernate.bytecode.use_reflection_optimizer=false, hibernate.jdbc.lob.non_contextual_creation=true}
2019-10-08 23:37:42.876  INFO 5464 --- [           main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.0.4.Final}
2019-10-08 23:37:43.485  INFO 5464 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2019-10-08 23:37:43.641  INFO 5464 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2019-10-08 23:37:43.657  INFO 5464 --- [           main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.PostgreSQL95Dialect
2019-10-08 23:37:43.891  INFO 5464 --- [           main] o.h.e.j.e.i.LobCreatorBuilderImpl        : HHH000421: Disabling contextual LOB creation as hibernate.jdbc.lob.non_contextual_creation is true
2019-10-08 23:37:43.907  INFO 5464 --- [           main] org.hibernate.type.BasicTypeRegistry     : HHH000270: Type registration [java.util.UUID] overrides previous : org.hibernate.type.UUIDBinaryType@2e590b
2019-10-08 23:37:44.289  INFO 5464 --- [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2019-10-08 23:37:47.675  INFO 5464 --- [           main] o.s.b.web.embedded.netty.NettyWebServer  : Netty started on port(s): 8080
2019-10-08 23:37:47.686  INFO 5464 --- [           main] com.example.SecurityExampleApplication   : Started SecurityExampleApplication in 9.322 seconds (JVM running for 10.28)
100
123
4

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
100
123

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?