Go to Qiita Advent Calendar Top
LoginSignup
14
17

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 5 years have passed since last update.

@leonis_sk

Spring JDBC を使ってみる

Last updated at Posted at 2018-05-13

目的

Spring JDBC を SpringBoot から使ってみたので自分用に手順をまとめてみる。

環境

macOS Sierra 10.12.3
postgres (PostgreSQL) 10.4

DB の準備

PostgreSQL の導入〜起動

$ brew install postgresql
$ postgres -D /usr/local/var/postgres # 起動

テーブルの作成

$ createuser -P leonis_sk # ユーザ作成
Enter password for new role:
Enter it again:
$ createdb db-example -O leonis_sk # db 作成
$ psql -U leonis_sk db-example # db 接続
psql (10.4)
Type "help" for help.

db-example=> create table users (
db-example(> id varchar(20),
db-example(> name varchar(30)
db-example(> );
CREATE TABLE
db-example=> \d
         List of relations
 Schema | Name  | Type  |   Owner
--------+-------+-------+-----------
 public | users | table | leonis_sk
(1 row)

db-example=> select * from users;
 id | name
----+------
(0 rows)

db-example=> insert into users values('A001', 'alex'), ('A002', 'betty'), ('A003', 'carol');
INSERT 0 3
db-example=> select * from users;
  id  | name
------+-------
 A001 | alex
 A002 | betty
 A003 | carol
(3 rows)

db-example=> \q

Spring Boot Project の作成

雛形の作成

https://start.spring.io/
上記サイトで、最低限の情報を入れるだけで Spring Boot プロジェクトの雛形を生成できる。

  • Generate a Maven Project with Java and Spring Boot 1.5.13
  • Group : 任意（ここでは com.example）
  • Artifact は任意（ここでは demo）
  • Dependency に「Web」「JDBC」「PostgreSQL」を追加

Generate Project で zip ファイルをダウンロードできる。
解凍して Eclipse の workspace へ移動。
maven project としてインポート。

DB 接続情報

application.properties を編集

src/main/resources/application.properties
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/db-example
spring.datasource.username=leonis_sk
spring.datasource.password=password

RestController

以下のような TestController.java を作成

com.example.demo.TestController.java
package com.example.demo;

import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping(path="/jdbc/sample")
public class TestController {
    private static final Logger LOG = LoggerFactory.getLogger(TestController.class);
    
    @Autowired
    JdbcTemplate jdbcTemplate;
    
    @RequestMapping(path="/users", method=RequestMethod.GET)
    public String index() {
        List<Map<String,Object>> list;
        list = jdbcTemplate.queryForList("select * from users");
        return list.toString();
    }
    
    @RequestMapping(path="/users/{id}", method=RequestMethod.GET)
    public String read(@PathVariable String id) {
        List<Map<String,Object>> list;
        list = jdbcTemplate.queryForList("select * from users where id = ?", id);
        return list.toString();
    }
}

実行

起動

$ pwd
/path/to/eclipse-workspace/demo
$ mvn spring-boot:run

いざ起動、と思いきや、BUILD SUCCESS が出て止まってしまった。

[INFO] Scanning for projects...
[INFO]
[INFO] --------------------------< com.example:demo >--------------------------
[INFO] Building demo 0.0.1-SNAPSHOT
[INFO] --------------------------------[ jar ]---------------------------------
[INFO]
[INFO] >>> spring-boot-maven-plugin:1.5.13.RELEASE:run (default-cli) > test-compile @ demo >>>
[WARNING] The POM for org.objenesis:objenesis:jar:2.1 is invalid, transitive dependencies (if any) will not be available, enable debug logging for more details
[INFO]
[INFO] --- maven-resources-plugin:2.6:resources (default-resources) @ demo ---
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] Copying 1 resource
[INFO] Copying 0 resource
[INFO]
[INFO] --- maven-compiler-plugin:3.1:compile (default-compile) @ demo ---
[INFO] Nothing to compile - all classes are up to date
[INFO]
[INFO] --- maven-resources-plugin:2.6:testResources (default-testResources) @ demo ---
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] skip non existing resourceDirectory /Users/LEON/eclipse-workspace/demo/src/test/resources
[INFO]
[INFO] --- maven-compiler-plugin:3.1:testCompile (default-testCompile) @ demo ---
[INFO] Nothing to compile - all classes are up to date
[INFO]
[INFO] <<< spring-boot-maven-plugin:1.5.13.RELEASE:run (default-cli) < test-compile @ demo <<<
[INFO]
[INFO]
[INFO] --- spring-boot-maven-plugin:1.5.13.RELEASE:run (default-cli) @ demo ---

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::       (v1.5.13.RELEASE)

2018-05-14 01:49:38.701  INFO 8906 --- [           main] com.example.demo.DemoApplication         : Starting DemoApplication on LEON-Mac.local with PID 8906 (/Users/LEON/eclipse-workspace/demo/target/classes started by LEON in /Users/LEON/eclipse-workspace/demo)
2018-05-14 01:49:38.707  INFO 8906 --- [           main] com.example.demo.DemoApplication         : No active profile set, falling back to default profiles: default
2018-05-14 01:49:38.795  INFO 8906 --- [           main] s.c.a.AnnotationConfigApplicationContext : Refreshing org.springframework.context.annotation.AnnotationConfigApplicationContext@5901caa1: startup date [Mon May 14 01:49:38 JST 2018]; root of context hierarchy
2018-05-14 01:49:41.108  INFO 8906 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
2018-05-14 01:49:41.127  INFO 8906 --- [           main] com.example.demo.DemoApplication         : Started DemoApplication in 3.122 seconds (JVM running for 7.238)
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 5.845 s
[INFO] Finished at: 2018-05-14T01:49:41+09:00
[INFO] ------------------------------------------------------------------------
2018-05-14 01:49:41.132  INFO 8906 --- [       Thread-3] s.c.a.AnnotationConfigApplicationContext : Closing org.springframework.context.annotation.AnnotationConfigApplicationContext@5901caa1: startup date [Mon May 14 01:49:38 JST 2018]; root of context hierarchy
2018-05-14 01:49:41.134  INFO 8906 --- [       Thread-3] o.s.j.e.a.AnnotationMBeanExporter        : Unregistering JMX-exposed beans on shutdown

「?」と思い調べたところ、似たような症例も見つかったがよく分からず。
色々試したら、pom.xml の parent のバージョンを 1.5.13 → 1.5.12 にするとなぜかうまくいった。

pom.xml
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.12.RELEASE</version> <!-- <version>1.5.13.RELEASE</version> --> 
        <relativePath /> <!-- lookup parent from repository -->
    </parent>

原因はあとで調べることにして、次へ。

$ curl http://localhost:8080/jdbc/sample/users
[{id=A001, name=alex}, {id=A002, name=betty}, {id=A003, name=carol}]%
$ curl http://localhost:8080/jdbc/sample/users/A001
[{id=A001, name=alex}]%

curl コマンドでアクセスしてみると、きちんと取得できている模様。
それにしても Spring Boot はお手軽ですね。

14
17
0

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
14
17

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?