teratail
回答率
85.35%
teratail header banner
teratail header banner
質問するログイン新規登録
トップに関する質問Java SpringBoot SQL文について

編集履歴

質問編集履歴

1

ソールコードを追記しました。

2021/11/08 14:08

投稿

dyxRZKg87iYEc4p
dyxRZKg87iYEc4p

スコア21

test CHANGED
File without changes
test CHANGED
@@ -18,6 +18,10 @@
18
18
 
19
19
 
20
20
 
21
+ ソースコード追記しております。
22
+
23
+
24
+
21
25
 
22
26
 
23
27
 
@@ -30,9 +34,119 @@
30
34
 
31
35
  NoteController.java
32
36
 
33
-
37
+ 省略
38
+
34
-
39
+ @RequiredArgsConstructor
40
+
41
+ @Controller
42
+
43
+ public class NoteController {
44
+
45
+
46
+
47
+ private final NoteService service;
48
+
49
+
50
+
51
+ @GetMapping("/list")
52
+
53
+ public String getAllNotes(Model model, @PageableDefault(size = 20) Pageable pageable) {
54
+
55
+ model.addAttribute("page", service.selectAll(pageable));
56
+
57
+
58
+
59
+ return "list";
60
+
61
+ }
62
+
63
+
64
+
65
+ @GetMapping("/add")
66
+
67
+ public String addNote(@ModelAttribute Note note) {
68
+
69
+ return "form";
70
+
71
+ }
72
+
73
+
74
+
75
+ @PostMapping("/process")
76
+
77
+ public String process(@Validated @ModelAttribute Note note, BindingResult result, Model model,
78
+
79
+ RedirectAttributes redirectAttributes) {
80
+
81
+ if (result.hasErrors()) {
82
+
83
+ model.addAttribute("agein", "もう一度入力してください");
84
+
85
+ return "form";
86
+
87
+ }
88
+
89
+ // 現在日付をnoteに格納
90
+
91
+ note.setDate(LocalDate.now());
92
+
93
+ service.save(note);
94
+
95
+
96
+
97
+ redirectAttributes.addFlashAttribute("add", "追加しました");
98
+
99
+
100
+
101
+ return "redirect:/list";
102
+
103
+ }
104
+
105
+
106
+
107
+ @GetMapping("/edit/{id}")
108
+
109
+ public String editNote(@PathVariable Long id, Model model) {
110
+
111
+ model.addAttribute("note", service.selectByPrimaryKey(id));
112
+
113
+ return "form";
114
+
115
+ }
116
+
117
+
118
+
119
+ @GetMapping("/delete/{id}")
120
+
121
+ public String deleteNote(@PathVariable Long id, RedirectAttributes redirectAttributes) {
122
+
123
+ service.deleteByPrimaryKey(id);
124
+
125
+ redirectAttributes.addFlashAttribute("delete", "削除しました");
126
+
127
+ return "redirect:/list";
128
+
129
+ }
130
+
131
+ }
132
+
133
+
134
+
135
+ ```
136
+
137
+
138
+
139
+ ### 該当のソースコード
140
+
141
+
142
+
143
+ ```ここに言語名を入力
144
+
145
+ Note.java
146
+
147
+
148
+
35
- package com.beit_and_pear.controller;
149
+ package com.beit_and_pear.model;
36
150
 
37
151
 
38
152
 
@@ -40,131 +154,55 @@
40
154
 
41
155
 
42
156
 
43
- import org.springframework.data.domain.Pageable;
44
-
45
- import org.springframework.data.web.PageableDefault;
46
-
47
- import org.springframework.stereotype.Controller;
48
-
49
- import org.springframework.ui.Model;
50
-
51
- import org.springframework.validation.BindingResult;
52
-
53
- import org.springframework.validation.annotation.Validated;
54
-
55
- import org.springframework.web.bind.annotation.GetMapping;
56
-
57
- import org.springframework.web.bind.annotation.ModelAttribute;
58
-
59
- import org.springframework.web.bind.annotation.PathVariable;
60
-
61
- import org.springframework.web.bind.annotation.PostMapping;
62
-
63
- import org.springframework.web.servlet.mvc.support.RedirectAttributes;
64
-
65
-
66
-
67
- import com.beit_and_pear.model.Note;
68
-
69
- import com.beit_and_pear.service.NoteService;
70
-
71
-
72
-
73
- import lombok.RequiredArgsConstructor;
74
-
75
-
76
-
77
- @RequiredArgsConstructor
78
-
79
- @Controller
80
-
81
- public class NoteController {
82
-
83
-
84
-
85
- private final NoteService service;
86
-
87
-
88
-
89
- @GetMapping("/list")
90
-
91
- public String getAllNotes(Model model, @PageableDefault(size = 20) Pageable pageable) {
92
-
93
- model.addAttribute("page", service.selectAll(pageable));
94
-
95
-
96
-
97
- return "list";
98
-
99
- }
100
-
101
-
102
-
103
- @GetMapping("/add")
104
-
105
- public String addNote(@ModelAttribute Note note) {
106
-
107
- return "form";
108
-
109
- }
110
-
111
-
112
-
113
- @PostMapping("/process")
114
-
115
- public String process(@Validated @ModelAttribute Note note, BindingResult result, Model model,
116
-
117
- RedirectAttributes redirectAttributes) {
118
-
119
- if (result.hasErrors()) {
120
-
121
- model.addAttribute("agein", "もう一度入力してください");
122
-
123
- return "form";
124
-
125
- }
126
-
127
- // 現在日付をnoteに格納
128
-
129
- note.setDate(LocalDate.now());
130
-
131
- service.save(note);
132
-
133
-
134
-
135
- redirectAttributes.addFlashAttribute("add", "追加しました");
136
-
137
-
138
-
139
- return "redirect:/list";
140
-
141
- }
142
-
143
-
144
-
145
- @GetMapping("/edit/{id}")
146
-
147
- public String editNote(@PathVariable Long id, Model model) {
148
-
149
- model.addAttribute("note", service.selectByPrimaryKey(id));
150
-
151
- return "form";
152
-
153
- }
154
-
155
-
156
-
157
- @GetMapping("/delete/{id}")
158
-
159
- public String deleteNote(@PathVariable Long id, RedirectAttributes redirectAttributes) {
160
-
161
- service.deleteByPrimaryKey(id);
162
-
163
- redirectAttributes.addFlashAttribute("delete", "削除しました");
164
-
165
- return "redirect:/list";
166
-
167
- }
157
+ import javax.validation.constraints.NotBlank;
158
+
159
+ import javax.validation.constraints.Size;
160
+
161
+
162
+
163
+ import org.springframework.format.annotation.DateTimeFormat;
164
+
165
+
166
+
167
+ import lombok.Getter;
168
+
169
+ import lombok.Setter;
170
+
171
+
172
+
173
+ @Getter
174
+
175
+ @Setter
176
+
177
+ public class Note {
178
+
179
+
180
+
181
+ private Long id;
182
+
183
+
184
+
185
+ @Size(max = 20)
186
+
187
+ private String name;
188
+
189
+
190
+
191
+ @NotBlank
192
+
193
+ @Size(max = 250)
194
+
195
+ private String content;
196
+
197
+
198
+
199
+ @DateTimeFormat(pattern = "yyyy-MM-dd")
200
+
201
+ private LocalDate date;
202
+
203
+
204
+
205
+ private String userId;
168
206
 
169
207
  }
170
208
 
@@ -172,8 +210,6 @@
172
210
 
173
211
  ```
174
212
 
175
-
176
-
177
213
  ### 該当のソースコード
178
214
 
179
215
 
@@ -192,7 +228,9 @@
192
228
 
193
229
  name VARCHAR(20),
194
230
 
195
- content VARCHAR(250) NOT NULL
231
+ content VARCHAR(250) NOT NULL,
232
+
233
+ user_id VARCHAR(100)
196
234
 
197
235
  );
198
236
 
@@ -210,6 +248,32 @@
210
248
 
211
249
  ```
212
250
 
251
+ ### 該当のソースコード
252
+
253
+
254
+
255
+ ```ここに言語名を入力
256
+
257
+ data.sql
258
+
259
+
260
+
261
+ INSERT INTO note(name, date, content, user_id)
262
+
263
+ VALUES('テスト', '2021-10-28','サンプルです。削除して構いません。','tanakaken'),
264
+
265
+ ('テスト', '2021-10-28','サンプルです。削除して構わない。','user');
266
+
267
+
268
+
269
+ INSERT INTO m_user (user_id, password, role)
270
+
271
+ VALUES('tanakaken', '$2a$10$sASu3M4jSGuO80Q9yBKI4ugTCWCOnbvXSZets1wfqkBGnGKJgG.jy', 'ROLE_ADMIN'),
272
+
273
+ ('user', '$2a$10$sASu3M4jSGuO80Q9yBKI4ugTCWCOnbvXSZets1wfqkBGnGKJgG.jy', 'ROLE_GENERAL');
274
+
275
+ ```
276
+
213
277
 
214
278
 
215
279
  ### 該当のソースコード
@@ -280,13 +344,19 @@
280
344
 
281
345
  SELECT COUNT(*) FROM note
282
346
 
347
+ where user_id = #{userId}
348
+
283
349
  </select>
284
350
 
285
351
 
286
352
 
287
353
  <select id="selectAll" resultType="Note">
288
354
 
289
- SELECT * FROM note ORDER BY id DESC
355
+ SELECT * FROM note
356
+
357
+ where user_id = #{userId}
358
+
359
+ ORDER BY id DESC
290
360
 
291
361
  </select>
292
362
 
@@ -296,7 +366,7 @@
296
366
 
297
367
  SELECT * FROM note
298
368
 
299
- WHERE id = #{id}
369
+ WHERE id = #{id}, user_id = #{userId}
300
370
 
301
371
  </select>
302
372
 
@@ -318,7 +388,7 @@
318
388
 
319
389
  SET name = #{name}, content = #{content}
320
390
 
321
- WHERE id = #{id}
391
+ WHERE id = #{id}, user_id = #{userId}
322
392
 
323
393
  </update>
324
394
 
@@ -328,7 +398,7 @@
328
398
 
329
399
  DELETE FROM note
330
400
 
331
- WHERE id = #{id}
401
+ WHERE id = #{id}, user_id = #{userId}
332
402
 
333
403
  </delete>
334
404
 
@@ -343,3 +413,5 @@
343
413
 
344
414
 
345
415
  ![イメージ説明](04d356b7c6dc5a52da8cc6c34bb4c243.png)
416
+
417
+ ![イメージ説明](0fafc7af318fdac50183a6ae1afa559d.png)