Category: SpringBoot CRUD

[SpringBoot2.4.2] jdbcTemplateの削除を実装する

index.html

1
<a th:href="'/test1/delete/' + ${list.id}"><button type="button" class="btn btn-danger">削除</button></a>

delete_complete.html

1
2
3
4
5
6
7
8
9
<h1>社員情報削除 完了</h1>
<div class="col-md-8">
<p>社員の削除が完了しました。</p>
<table class="table">
    <tr><td>名前</td><td th:text="${name}"></td></tr>
    <tr><td>所属</td><td th:text="${department}"></td></tr>
</table>
<button type="button" class="btn btn-primary" onclick="location.href='/test1/index'">一覧に戻る</button>
</div>

UserRepository.java

1
2
3
4
5
6
7
8
9
10
11
12
public Users delete(Long id) throws DataAccessException {
    String sql1 = ""
        + "SELECT * FROM users WHERE id = ?";
    Map<String, Object> users = jdbcTemplate.queryForMap(sql1, id);
    Users user = new Users();
    user.setName((String)users.get("name"));
    user.setDepartment((String)users.get("department"));
     
    String sql2 = "DELETE FROM users WHERE id = ?";
    jdbcTemplate.update(sql2, id);
    return user;
}

MainController.java

1
2
3
4
5
6
7
@GetMapping("delete/{userId}")
public String deleteUser(@PathVariable("userId") long userId, Model model) {
    Users user = usersRepository.delete(userId);
    model.addAttribute("name", user.getName());
    model.addAttribute("department", user.getDepartment());
    return "test1/delete_complete";
}

一度理解するとあとは早いな
とりあえずCRUD完
authに行きたいが、まずこれでVPSにデプロイしてみたい。

[SpringBoot2.4.2] 編集画面から編集完了画面を作る

画面遷移としては、編集->編集確認->編集完了

edit_confirm.html

1
2
3
4
5
6
7
8
9
10
11
<form class=""  method="get" action="/test1/editcomplete">
<input type="hidden" name="id" th:value="${id}">
<input type="hidden" name="name" th:value="${name}">
<input type="hidden" name="department" th:value="${department}">
<table class="table">
    <tr><td>名前</td><td th:text="${name}"></td></tr>
    <tr><td>所属</td><td th:text="${department}"></td></tr>
</table>
<button type="button" class="btn btn-primary" onclick="location.href='/test1/index'">戻る</button>
<button type="submit" class="btn btn-primary">編集完了</button>
</form>

MainController.java

1
2
3
4
5
6
7
8
9
10
11
@GetMapping("editconfirm")
    public String editconfirm(
            @RequestParam(name = "id") Integer id,
            @RequestParam(name = "name") String name,
            @RequestParam(name = "department") String department,
            Model model) {
            model.addAttribute("id", id);
            model.addAttribute("name", name);
            model.addAttribute("department", department);
            return "test1/edit_confirm";
    }

ここまでは何も考えずにいける
updateする為にnameとdepartment以外にidも加える

UsersRepository.java
L エンティティの値をupdate

1
2
3
4
5
6
7
8
public Users update(Users users) throws DataAccessException {
        // SQL文を作成
        String sql = ""
            + "UPDATE users SET name = ?, department = ?"
            + " WHERE"  + " id = ?";
        jdbcTemplate.update(sql, users.getName(),users.getDepartment(),users.getId());
        return users;
    }

MainController.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@GetMapping("editcomplete")
    public String editcomplete(
            @RequestParam(name = "id") Integer id,
            @RequestParam(name = "name") String name,
            @RequestParam(name = "department") String department,
            Model model) {
            Users users = new Users();
            users.setId(id);
            users.setName(name);
            users.setDepartment(department);
            usersRepository.update(users);
             
            model.addAttribute("name", name);
            model.addAttribute("department", department);
            return "test1/edit_complete";
    }

updateされました。

よしゃああああああああああああああああああああああああ
SpringBootもCRUまできた。残りはDやな。

[SpringBoot2.4.2] URLのパスを取得してjdbcTemplateでedit画面を作成する

まずtemplates に edit.html を作ります。

1
2
3
4
5
6
7
8
9
10
11
12
<div class="form-group">
        <label class="control-label col-md-2">名前</label>
        <div class="col-md-4">
            <input type="text" class="form-control" name="name" th:value="${name}">
        </div>
    </div>
    <div class="form-group">
        <label class="control-label col-md-2">所属部署</label>
        <div class="col-md-4">
            <input type="text" class="form-control" name="department" th:value="${department}">
        </div>
    </div>

続いて、indexからeditへのリンク。これは、/edit/${userId}とします。

1
<td th:text="${list.id}"></td><td th:text="${list.name}">狩野 良平</td><td th:text="${list.department}">営業部</td><td><a th:href="'/edit/' + ${list.id}"><button type="button" class="btn btn-secondary">編集</button></a></td><td><a th:href="'/delete/' + ${list.id}"><button type="button" class="btn btn-danger" onclick="location.href='/delete_complete.html'">削除</button></a></td>

UsersRepository.java
L jdbcTemplate.queryForMapで取得する

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
public Users selectOne(Long id) throws DataAccessException {
        // SQL文を作成
        String sql = ""
            + "SELECT"
                + " *"
            + " FROM"
                + " users"
            + " WHERE"
                + " id = ?";
        Map<String, Object> users = jdbcTemplate.queryForMap(sql, id);
 
        // Userオブジェクトに格納する。
        Users user = new Users();
        user.setName((String)users.get("name"));
        user.setDepartment((String)users.get("department"));
        return user;
    }

MainController.java

1
2
3
4
5
6
7
@GetMapping("edit/{userId}")
public String editForm(@PathVariable("userId") long userId, Model model) {
    Users user = usersRepository.selectOne(userId);
    model.addAttribute("name", user.getName());
    model.addAttribute("department", user.getDepartment());
    return "test1/edit";
}

まじかよ、これ作るの3時間半くらいかかったんだけど。。。

[SpringBoot2.4.2] postgresのデータをselect文で全件取得して表示する

UsersRepository.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
package com.example.demo;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
 
@Repository
public class UsersRepository {
     
    private final JdbcTemplate jdbcTemplate;
     
    @Autowired
    public UsersRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
     
    public void insertUsers(Users users) {
        jdbcTemplate.update("INSERT INTO users(name,department) Values (?,?)",
                users.getName(), users.getDepartment());
    }
     
    public List<Users> getAll(){
        String sql = "select id, name, department from users";
        List<Map<String, Object>>usersList = jdbcTemplate.queryForList(sql);
        List<Users> list = new ArrayList<>();
        for(Map<String, Object> str1: usersList) {
            Users users = new Users();
            users.setId((int)str1.get("id"));
            users.setName((String)str1.get("name"));
            users.setDepartment((String)str1.get("department"));
            list.add(users);
        }
        return list;
    }
}

MainController.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
package com.example.demo;
import java.util.List;
import org.springframework.stereotype.Controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
 
@Controller
@RequestMapping("/test1")
public class MainController {
    @Autowired
    private UsersRepository usersRepository;
     
    @GetMapping("index")
    public String index(Model model) {
        List<Users> list = usersRepository.getAll();
        model.addAttribute("UsersList", list);
        return "test1/index";
    }

index.html
L 文字を接続する際は、”‘string’ + ${list}”とする

1
2
3
4
5
6
7
8
<h1>社員一覧</h1>
<div class="col-md-8">
<table class="table">
    <tr><th>ID</th><th>名前</th><th>所属部署</th><th>編集</th><th>削除</th></tr>
    <tr th:each="list: ${UsersList}">
        <td th:text="${list.id}"></td><td th:text="${list.name}">狩野 良平</td><td th:text="${list.department}">営業部</td><td><a th:href="'/edit/' + ${list.id}"><button type="button" class="btn btn-secondary">編集</button></a></td><td><a th:href="'/delete/' + ${list.id}"><button type="button" class="btn btn-danger" onclick="location.href='/delete_complete.html'">削除</button></a></td>
    </tr>
</table>

select allはわかった。
Javaの map, list, arrayListの使い方を理解する必要がある。

[SpringBoot2.4.2] thymeleafの入力データをpostgresにINSERTする

pom.xml

1
2
3
4
5
6
7
8
9
10
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
 
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

application.properties

1
2
3
4
spring.jpa.database=POSTGRESQL
spring.datasource.url=jdbc:postgresql://localhost:5432/test
spring.datasource.username=root
spring.datasource.password=

### データ格納用のDBテーブル作成
$ psql -U root test
psql: error: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket “/tmp/.s.PGSQL.5432”?
ん？
$ postgres -D /usr/local/var/postgres
$ brew services restart postgresql
test=> \d
test=> CREATE TABLE users (
id SERIAL NOT NULL,
name varchar(255),
department varchar(255),
PRIMARY KEY(id)
);

### Repository
Users.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package com.example.demo;
 
public class Users {
    private Integer id;
    private String name;
    private String department;
 
    public Integer getId() {
        return id;
    }
    public String getName() {
        return name;
    }
    public String getDepartment() {
        return department;
    }
    public void setName(String name) {
        this.name = name;
    }
    public void setDepartment(String department) {
        this.department = department;
    }
}

UsersRepository.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
package com.example.demo;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
 
@Repository
public class UsersRepository {
     
    private final JdbcTemplate jdbcTemplate;
     
    @Autowired
    public UsersRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
     
    public void insertUsers(Users users) {
        jdbcTemplate.update("INSERT INTO users(name,department) Values (?,?)",
                users.getName(), users.getDepartment());
    }
}

MainController.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
package com.example.demo;
import org.springframework.stereotype.Controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
 
@Controller
@RequestMapping("/test1")
public class MainController {
    @Autowired
    private UsersRepository usersRepository;
     
     
    @GetMapping("input")
    public String input1() {
        return "test1/input";
    }
     
    @GetMapping("inputconfirm")
    public String output1(
            @RequestParam(name = "name") String name,
            @RequestParam(name = "department") String department,
            Model model) {
            model.addAttribute("name", name);
            model.addAttribute("department", department);
            return "test1/input_confirm";
    }
     
    @GetMapping("inputcomplete")
    public String output2(
            @RequestParam(name = "name") String name,
            @RequestParam(name = "department") String department,
            Model model) {
            Users users = new Users();
            users.setName(name);
            users.setDepartment(department);
            usersRepository.insertUsers(users);
             
            model.addAttribute("name", name);
            model.addAttribute("department", department);
            return "test1/input_complete";
    }
     
}

view

postgres側
test=> select * from users;
id | name | department
—-+———-+————
1 | 山田太郎 | 営業部
(1 row)

test=> select * from users;
id | name | department
—-+————+————
1 | 山田太郎 | 営業部
2 | 佐藤　祐一 | 経理部
(2 rows)

入力できてるーーーーーーーーーーーーーーーーー
ぎゃあああああああああああああああああああああああああああああ
😇😇😇😇😇😇😇😇😇😇😇😇😇

なんとなくServiceとRepositoryとControllerとthymeleafの関係性がわかってきたああああああああああ

[SpringBoot2.4.2] 登録確認画面を作る

src/main/resources/templates/test1/input_confirm.html
　L thymeleafでinput typeのvalueを取得して表示する

1
2
3
4
5
6
7
8
9
10
<form class=""  method="get" action="inputcomplete">
<input type="hidden" name="name" th:value="${name}">
<input type="hidden" name="department" th:value="${department}">
<table class="table">
    <tr><td>名前</td><td th:text="${name}">狩野 良平</td></tr>
    <tr><td>所属</td><td th:text="${department}">営業部</td></tr>
</table>
<button type="button" class="btn btn-primary" onclick="location.href='/test1/input'">戻る</button>
<button type="submit" class="btn btn-primary">登録完了</button>
</form>

MainController.java
　L completeもconfirmと基本は同じ、RequestParamで取得する

1
2
3
4
5
6
7
8
9
@GetMapping("inputcomplete")
public String output2(
        @RequestParam(name = "name") String name,
        @RequestParam(name = "department") String department,
        Model model) {
        model.addAttribute("name", name);
        model.addAttribute("department", department);
        return "test1/input_complete";
}

src/main/resources/templates/test1/input_complete.html

1
2
3
4
5
6
7
8
9
10
11
12
13
    <link th:href="@{/css/style.css}" rel="stylesheet" type="text/css">
 
// 省略
 
<h1>社員登録 完了</h1>
<div class="col-md-8">
<p>社員の登録が完了しました。</p>
<table class="table">
    <tr><td>名前</td><td th:text="${name}">狩野 良平</td></tr>
    <tr><td>所属</td><td th:text="${department}">営業部</td></tr>
</table>
<button type="button" class="btn btn-primary" onclick="location.href='/index'">一覧に戻る</button>
</div>

GetではなくPostにしたいが、Getなら凄く簡単だということはわかった。
ファイルのルーティングはControllerでやるのね。

で、この入力データをINSERTしたい。

[SpringBoot2.4.2] ユーザ登録画面を作る

src/main/resources/templates にinput.htmlファイルを作ります。
formはgetメソッドで、遷移先はinputconfirmとする
thymeleafを使う

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>社員登録</title>
<!--     <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-giJF6kkoqNQ00vy+HMDP7azOuL0xtbfIcaT9wjKHr8RbDVddVHyTfAAsrekwKmP1" crossorigin="anonymous">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.14.0/css/all.min.css"> -->
    <link th:href="@{/css/style.css}" rel="stylesheet" type="text/css">
</head>
<body>
<h1>社員登録</h1>
<div class="col-md-8">
<form class="form-inline" method="get" action="inputconfirm">
    <div class="form-group">
        <label class="control-label col-md-2">名前</label>
        <div class="col-md-4">
            <input type="text" name="name" class="form-control">
        </div>
    </div>
    <div class="form-group">
        <label class="control-label col-md-2">所属部署</label>
        <div class="col-md-4">
            <input type="text" name="department" class="form-control">
        </div>
    </div>
    <br>
    <button type="submit" class="btn btn-primary">確認</button>
</form>
</div>

### SpringBootでのCSSファイル
sassで作ったcssファイルは src/main/resources/static 配下に置く

1
<link th:href="@{/css/style.css}" rel="stylesheet" type="text/css">

### MainController.java
– GetMappingでpathを指定する

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
package com.example.demo;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
 
@Controller
@RequestMapping("/test1")
public class MainController {
     
    @GetMapping("input")
    public String input1() {
        return "test1/input";
    }
     
    @GetMapping("inputconfirm")
    public String output1(
            @RequestParam(name = "name") String name,
            @RequestParam(name = "department") String department,
            Model model) {
            model.addAttribute("name", name);
            model.addAttribute("department", department);
            return "test1/input_confirm";
    }
     
}

test1/input_confirm.html

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>社員登録 確認</title>
<!--     <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-giJF6kkoqNQ00vy+HMDP7azOuL0xtbfIcaT9wjKHr8RbDVddVHyTfAAsrekwKmP1" crossorigin="anonymous">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.14.0/css/all.min.css"> -->
    <link th:href="@{/css/style.css}" rel="stylesheet" type="text/css">
</head>
<body>
<h1>社員登録 確認</h1>
<div class="col-md-8">
<p>登録内容を確認してください。</p>
<form class="">
<table class="table">
    <tr><td>名前</td><td th:text="${name}">狩野 良平</td></tr>
    <tr><td>所属</td><td th:text="${department}">営業部</td></tr>
</table>
</form>
<button type="button" class="btn btn-primary" onclick="location.href='/input.html'">戻る</button>
<button type="button" class="btn btn-primary" onclick="location.href='/input_complete.html'">登録完了</button>
</div>
 
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.1/umd/popper.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.0.0-alpha1/js/bootstrap.min.js"></script>
</body>
</html>

あれ？　もしかして上手くいってる？？

[Spring Boot2.4.2] Hibernateでselectする

HibernateはRed Hat開発のフリーソフト

pom.xml

1
2
3
4
5
6
7
8
9
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
</dependency>
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-annotations</artifactId>
    <version>3.5.6-Final</version>
</dependency>

application.propertiesは変更なし

src/main/java/hibernate-config.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
  
 <hibernate-configuration>
  
 <session-factory>
    <property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
    <property name="hibernate.connection.url">jdbc:postgresql://localhost:5432/test</property>
    <property name="hibernate.connection.username">root</property>
    <property name="hibernate.connection.password"></property>
    <property name="hibernate.connection.pool_size">1</property>
    <property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>
    <property name="hibernate.current_session_context_class">thread</property>
    <property name="hibernate.show_sql">true</property>
 </session-factory>
 </hibernate-configuration>

Syain.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
package com.example.demo;
import javax.persistence.Entity;
import javax.persistence.Table;
import javax.persistence.Id;
 
@Entity
@Table(name="syain")
public class Syain {
    @Id
    private Integer id;
    private String name;
    private String romaji;
     
    public Integer getId() {
        return id;
    }
    public String getName() {
        return name;
    }
    public String getRomaji() {
        return romaji;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public void setName(String name) {
        this.name = name;
    }
    public void setRomaji(String romaji) {
        this.romaji = romaji;
    }
}

SyainRepository.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
package com.example.demo;
 
import java.util.List;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.springframework.stereotype.Repository;
 
@Repository
public class SyainRepository {
     
    @SuppressWarnings("unchecked")
    public List<Syain> findAll(){
        List<Syain> syainList = null;
         
        SessionFactory sf = new Configuration().configure("hibernate-config.xml").addAnnotatedClass(Syain.class)
                .buildSessionFactory();
         
        Session session = sf.getCurrentSession();
         
        try {
            session.beginTransaction();
            syainList = session.createQuery("from Syain").list();
            session.getTransaction().commit();
        } catch (Exception e) {
            session.getTransaction().rollback();
            e.printStackTrace();
        } finally {
            sf.close();
        }
        return syainList;
    }
}

MainController.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
package com.example.demo;
import java.util.List;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
 
@RestController
public class MainController {
    @Autowired
    private SyainRepository syainRepository;
     
    @GetMapping("/test1")
    public List<Syain> get(){
        return syainRepository.findAll();
    }
}

http://localhost:8080/test1
[{“id”:1,”name”:”鈴木”,”romaji”:”suzuki”},{“id”:2,”name”:”田中”,”romaji”:”tanaka”},{“id”:3,”name”:”佐藤”,”romaji”:”sato”},{“id”:4,”name”:”武田”,”romaji”:”takeda”}]

jdbc使えるなら、Hibernateを使う理由がイマイチわからんが、とにかくOK

[Spring Boot2.4.2] SpringJDBCでinsert

serviceは、selectと変更なし

SyainRepository.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
package com.example.demo;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
 
@Repository
public class SyainRepository {
    private final JdbcTemplate jdbcTemplate;
     
    @Autowired
    public SyainRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
     
    public void insertSyain(Syain syain) {
        jdbcTemplate.update("INSERT INTO syain(id,name,romaji) Values(?,?,?)",
                syain.getId(),syain.getName(),syain.getRomaji());
    }
}

MainController.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
package com.example.demo;
import java.time.LocalDateTime;
 
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
 
@Controller
@RequestMapping("/test1")
public class MainController {
    @Autowired
    private SyainRepository syainRepository;
     
    @GetMapping
    public String index(Model model) {
        Syain syain = new Syain();
        syain.setId(4);
        syain.setName("武田");
        syain.setRomaji("takeda");
        syainRepository.insertSyain(syain);
        return "test1/index";
    }
}

$ psql -U root test
psql (13.1)
Type “help” for help.

test=> select * from syain;
id | name | romaji
—-+——+——–
1 | 鈴木 | suzuki
2 | 田中 | tanaka
3 | 佐藤 | sato
(3 rows)

index.html

1
2
3
4
5
6
7
8
9
10
<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
  <head>
    <meta charset="utf-8" />
    <title>Check</title>
  </head>
  <body>
    ok
  </body>
</html>

http://localhost:8080/test1

test=> select * from syain;
id | name | romaji
—-+——+——–
1 | 鈴木 | suzuki
2 | 田中 | tanaka
3 | 佐藤 | sato
4 | 武田 | takeda
(4 rows)

なるほど、CRUDはJDBCを使うのね、完全に理解した!

[Spring Boot2.4.2] SpringJDBCでselect

pom.xml

1
2
3
4
5
6
7
8
9
<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>

Syain.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
package com.example.demo;
 
public class Syain {
     
    private Integer id;
    private String name;
    private String romaji;
     
    public Integer getId() {
        return id;
    }
    public String getName() {
        return name;
    }
    public String getRomaji() {
        return romaji;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public void setName(String name) {
        this.name = name;
    }
    public void setRomaji(String romaji) {
        this.romaji = romaji;
    }
}

SyainRepository.java
L mapとは、「キー」と「値」をペアにして複数のデータを格納できるもの
-> 連想配列みたいなものか。。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
package com.example.demo;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
 
@Repository
public class SyainRepository {
    private final JdbcTemplate jdbcTemplate;
     
    @Autowired
    public SyainRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
     
    public List<Syain> getAll(){
        String sql = "select id, name, romaji from syain";
        List<Map<String, Object>>syainList = jdbcTemplate.queryForList(sql);
        List<Syain> list = new ArrayList<>();
        for(Map<String,Object> str1: syainList) {
            Syain syain = new Syain();
            syain.setId((int)str1.get("id"));
            syain.setName((String)str1.get("name"));
            syain.setRomaji((String)str1.get("romaji"));
            list.add(syain);
        }
        return list;
    }
}

MainController.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
package com.example.demo;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
 
@Controller
@RequestMapping("/test1")
public class MainController {
    @Autowired
    private SyainRepository syainRepository;
     
    @GetMapping
    public String index(Model model) {
        List<Syain> list = syainRepository.getAll();
        model.addAttribute("SyainList", list);
        return "test1/index";
    }
}

index.html

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
  <head>
    <meta charset="utf-8" />
    <title>Check</title>
  </head>
  <body>
    <table>
        <tr>
            <th>id</th><th>name</th><th>romaji</th>
        </tr>
        <tr th:each="list : ${SyainList}">
            <td th:text="${list.id}"></td>
            <td th:text="${list.name}"></td>
            <td th:text="${list.romaji}"></td>
        </tr>
    </table>
  </body>
</html>

なんかもう殆どSpringBootでアプリ作れるような気がしてきた。