teratail
回答率
85.35%
teratail header banner
teratail header banner
質問するログイン新規登録
トップに関する質問Springで検索機能を実装中、SQLエラーが出る

編集履歴

質問編集履歴

7

文法の追加

2020/05/25 05:16

投稿

TaSa
TaSa

スコア1

test CHANGED
File without changes
test CHANGED
@@ -304,24 +304,48 @@
304
304
 
305
305
 
306
306
 
307
+ import javax.persistence.Column;
308
+
309
+ import javax.persistence.Entity;
310
+
311
+ import javax.persistence.Id;
312
+
313
+ import javax.persistence.Table;
314
+
315
+ import javax.validation.constraints.NotNull;
316
+
317
+
318
+
307
319
  import lombok.Data;
308
320
 
309
321
 
310
322
 
323
+ @Entity
324
+
311
325
  @Data
312
326
 
327
+ @Table(name = "product")
328
+
313
329
  public class Product {
314
330
 
315
331
 
316
332
 
333
+ @Id
334
+
335
+ @Column(name = "productCode")
336
+
317
337
  private String productCode; //商品コード
318
338
 
319
339
 
320
340
 
341
+ @Column(name = "productName")
342
+
321
343
  private String productName; //商品名
322
344
 
323
345
 
324
346
 
347
+ @NotNull
348
+
325
349
  private int price; //値段
326
350
 
327
351
  }
@@ -341,3 +365,53 @@
341
365
  ;
342
366
 
343
367
  ```
368
+
369
+ ```ProductDaoJdbcImpl
370
+
371
+ (中略)
372
+
373
+ @Override
374
+
375
+ // Productテーブルのデータを全件取得.
376
+
377
+ public List<Product> selectMany() throws DataAccessException {
378
+
379
+ List<Map<String, Object>> getList = jdbc.queryForList("SELECT * FROM product");
380
+
381
+ //結果返却用の変数
382
+
383
+ List<Product> productList = new ArrayList<Product>();
384
+
385
+ //取得したデータを結果返却用のListに格納していく
386
+
387
+ for (Map<String, Object> map : getList) {
388
+
389
+ //Productインスタンスの生成
390
+
391
+ Product product = new Product();
392
+
393
+
394
+
395
+ //Productインスタンスに取得したデータをセットする
396
+
397
+ product.setProductCode((String) map.get("productCode")); //商品コード
398
+
399
+ product.setProductName((String) map.get("productName")); //商品ID
400
+
401
+ product.setPrice((Integer) map.get("price")); //商品ID
402
+
403
+
404
+
405
+ //結果返却用のListに追加
406
+
407
+ productList.add(product);
408
+
409
+ }
410
+
411
+ return productList;
412
+
413
+ }
414
+
415
+
416
+
417
+ ```

6

誤字の修正

2020/05/25 05:16

投稿

TaSa
TaSa

スコア1

test CHANGED
File without changes
test CHANGED
@@ -22,9 +22,9 @@
22
22
 
23
23
  There was an unexpected error (type=Internal Server Error, status=500).
24
24
 
25
- org.hibernate.hql.internal.ast.QuerySyntaxException: product is not mapped [SELECT p FROM product p WHERE p.productCode LIKE :productCode ]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: product is not mapped [SELECT p FROM product p WHERE p.productCode LIKE :productCode ]
26
-
27
- org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.ast.QuerySyntaxException: product is not mapped [SELECT p FROM product p WHERE p.productCode LIKE :productCode ]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: product is not mapped [SELECT p FROM product p WHERE p.productCode LIKE :productCode ]
25
+ org.hibernate.hql.internal.ast.QuerySyntaxException: expecting IDENT, found '*' near line 1, column 10 [SELECT p.* FROM product p WHERE p.productCode LIKE :productCode ]
26
+
27
+ java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: expecting IDENT, found '*' near line 1, column 10 [SELECT p.* FROM product p WHERE p.productCode LIKE :productCode ]
28
28
 
29
29
  ```
30
30
 

5

誤字の修正

2020/05/25 02:30

投稿

TaSa
TaSa

スコア1

test CHANGED
File without changes
test CHANGED
@@ -300,40 +300,38 @@
300
300
 
301
301
  ```model
302
302
 
303
+ package com.example.demo.domain.model;
304
+
305
+
306
+
307
+ import lombok.Data;
308
+
309
+
310
+
311
+ @Data
312
+
313
+ public class Product {
314
+
315
+
316
+
317
+ private String productCode; //商品コード
318
+
319
+
320
+
321
+ private String productName; //商品名
322
+
323
+
324
+
325
+ private int price; //値段
326
+
327
+ }
328
+
329
+ ```
330
+
331
+ ```DB
332
+
303
333
  /* 商品マスタのデータ */
304
334
 
305
- package com.example.demo.domain.model;
306
-
307
-
308
-
309
- import lombok.Data;
310
-
311
-
312
-
313
- @Data
314
-
315
- public class Product {
316
-
317
-
318
-
319
- private String productCode; //商品コード
320
-
321
-
322
-
323
- private String productName; //商品名
324
-
325
-
326
-
327
- private int price; //値段
328
-
329
- }
330
-
331
- ```
332
-
333
- ```DB
334
-
335
- /* 商品マスタのデータ */
336
-
337
335
  insert into product(productCode,productName,price)
338
336
 
339
337
  values

4

文法の修正、追加

2020/05/25 02:26

投稿

TaSa
TaSa

スコア1

test CHANGED
File without changes
test CHANGED
@@ -124,7 +124,7 @@
124
124
 
125
125
  StringBuilder sql = new StringBuilder();
126
126
 
127
- sql.append("SELECT p FROM product p WHERE ");
127
+ sql.append("SELECT p.* FROM product p WHERE ");
128
128
 
129
129
  boolean andFlg = false;
130
130
 
@@ -298,6 +298,38 @@
298
298
 
299
299
  ```
300
300
 
301
+ ```model
302
+
303
+ /* 商品マスタのデータ */
304
+
305
+ package com.example.demo.domain.model;
306
+
307
+
308
+
309
+ import lombok.Data;
310
+
311
+
312
+
313
+ @Data
314
+
315
+ public class Product {
316
+
317
+
318
+
319
+ private String productCode; //商品コード
320
+
321
+
322
+
323
+ private String productName; //商品名
324
+
325
+
326
+
327
+ private int price; //値段
328
+
329
+ }
330
+
331
+ ```
332
+
301
333
  ```DB
302
334
 
303
335
  /* 商品マスタのデータ */

3

情報の追加

2020/05/25 02:25

投稿

TaSa
TaSa

スコア1

test CHANGED
File without changes
test CHANGED
@@ -8,6 +8,12 @@
8
8
 
9
9
 
10
10
 
11
+ 参考にしたサイト
12
+
13
+ https://dev.classmethod.jp/articles/spring-data-jpa_search/
14
+
15
+
16
+
11
17
  ### 発生している問題・エラーメッセージ
12
18
 
13
19
 

2

内容の修正

2020/05/22 07:34

投稿

TaSa
TaSa

スコア1

test CHANGED
File without changes
test CHANGED
@@ -2,7 +2,7 @@
2
2
 
3
3
  SpringBootでwebアプリケーションを開発しています。
4
4
 
5
- 検索機能を実装していて、SQLの部分でエラーが出てデータを引っ張ってこれない状況です。
5
+ 検索機能を実装していて、SQLの部分でエラーが出ていますが原因がわかりません。
6
6
 
7
7
  何かお気づきの点ありましたら、ご教授いただけると幸いです。
8
8
 
@@ -16,9 +16,9 @@
16
16
 
17
17
  There was an unexpected error (type=Internal Server Error, status=500).
18
18
 
19
- org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: * near line 1, column 8 [SELECT * FROM product WHERE productCode LIKE :productCode AND productName LIKE :productName ]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: * near line 1, column 8 [SELECT * FROM product WHERE productCode LIKE :productCode AND productName LIKE :productName ]
20
-
21
- org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: * near line 1, column 8 [SELECT * FROM product WHERE productCode LIKE :productCode AND productName LIKE :productName ]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: * near line 1, column 8 [SELECT * FROM product WHERE productCode LIKE :productCode AND productName LIKE :productName ]
19
+ org.hibernate.hql.internal.ast.QuerySyntaxException: product is not mapped [SELECT p FROM product p WHERE p.productCode LIKE :productCode ]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: product is not mapped [SELECT p FROM product p WHERE p.productCode LIKE :productCode ]
20
+
21
+ org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.ast.QuerySyntaxException: product is not mapped [SELECT p FROM product p WHERE p.productCode LIKE :productCode ]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: product is not mapped [SELECT p FROM product p WHERE p.productCode LIKE :productCode ]
22
22
 
23
23
  ```
24
24
 
@@ -30,164 +30,182 @@
30
30
 
31
31
  ```service
32
32
 
33
+ @Service
34
+
35
+ public class ProductService {
36
+
37
+
38
+
39
+ @Autowired
40
+
41
+ ProductDao dao;
42
+
43
+ @Autowired
44
+
45
+ ProductRepositoryCustom repositoryCustom;
46
+
47
+
48
+
49
+ public List<Product> search(String productCode, String productName, BigDecimal priceFrom, BigDecimal priceTo) {
50
+
51
+ List<Product> result;
52
+
53
+ if ("".equals(productCode) && "".equals(productName) && priceFrom == null && priceTo == null) {
54
+
55
+ result = dao.findAll();
56
+
57
+ } else {
58
+
59
+ result = repositoryCustom.search(productCode, productName, priceFrom, priceTo);
60
+
61
+ }
62
+
63
+ return result;
64
+
65
+ }
66
+
67
+
68
+
69
+ ```
70
+
71
+ ```repository
72
+
33
- package com.example.demo.domain.service;
73
+ package com.example.demo.domain.repository;
74
+
75
+
76
+
34
-
77
+ import java.math.BigDecimal;
35
-
36
78
 
37
79
  import java.util.List;
38
80
 
39
81
 
40
82
 
41
- import org.springframework.beans.factory.annotation.Autowired;
42
-
43
- import org.springframework.stereotype.Service;
44
-
45
-
46
-
47
83
  import com.example.demo.domain.model.Product;
48
84
 
49
- import com.example.demo.domain.repository.ProductDao;
85
+
50
-
51
- import com.example.demo.domain.repository.ProductRepositoryCustom;
86
+
52
-
53
-
54
-
55
- @Service
56
-
57
- public class ProductService {
87
+ public interface ProductRepositoryCustom {
88
+
89
+ public List<Product> search(String productCode, String productName, BigDecimal priceFrom, BigDecimal priceTo);
90
+
91
+ }
92
+
93
+
94
+
95
+
96
+
97
+ ```
98
+
99
+ ```repository
100
+
101
+ @Repository
102
+
103
+ public class ProductRepositoryCustomImpl implements ProductRepositoryCustom {
58
104
 
59
105
 
60
106
 
61
107
  @Autowired
62
108
 
109
+ EntityManager manager;
110
+
111
+
112
+
63
- ProductRepositoryCustom repositoryCustom;
113
+ @SuppressWarnings("unchecked")
114
+
64
-
115
+ @Override
65
-
66
-
116
+
67
- public List<Product> search(String productCode, String productName, int price) {
117
+ public List<Product> search(String productCode, String productName, BigDecimal priceFrom, BigDecimal priceTo) {
118
+
68
-
119
+ StringBuilder sql = new StringBuilder();
120
+
121
+ sql.append("SELECT p FROM product p WHERE ");
122
+
123
+ boolean andFlg = false;
124
+
69
- List<Product> result;
125
+ boolean productCodeFlg = false;
126
+
70
-
127
+ boolean priceFromFlg = false;
128
+
129
+ boolean priceToFlg = false;
130
+
71
- if ("".equals(productCode) && "".equals(productName)) {
131
+ if (!"".equals(productCode) && productCode != null) {
72
-
73
- result = dao.findAll();
132
+
74
-
75
- } else {
76
-
77
- result = repositoryCustom.search(productCode, productName, price);
133
+ sql.append(" p.productCode LIKE :productCode ");
134
+
78
-
135
+ productCodeFlg = true;
136
+
137
+ andFlg = true;
138
+
79
- }
139
+ }
140
+
80
-
141
+ boolean productNameFlg = false;
142
+
143
+ if (!"".equals(productName) && productName != null) {
144
+
145
+ if (andFlg)
146
+
147
+ sql.append(" AND ");
148
+
149
+ sql.append("p.productName LIKE :productName ");
150
+
151
+ productNameFlg = true;
152
+
153
+ andFlg = true;
154
+
155
+ }
156
+
157
+ if (priceFrom != null) {
158
+
159
+ if (andFlg)
160
+
161
+ sql.append(" AND ");
162
+
163
+ sql.append("p.price >= :priceFrom ");
164
+
165
+ priceFromFlg = true;
166
+
167
+ andFlg = true;
168
+
169
+ }
170
+
171
+ if (priceTo != null) {
172
+
173
+ if (andFlg)
174
+
175
+ sql.append(" AND ");
176
+
177
+ sql.append("p.price <= :priceTo ");
178
+
179
+ priceToFlg = true;
180
+
181
+ andFlg = true;
182
+
183
+ }
184
+
185
+ Query query = manager.createQuery(sql.toString());
186
+
187
+ if (productCodeFlg)
188
+
189
+ query.setParameter("productCode", "%" + productCode + "%");
190
+
191
+ if (productNameFlg)
192
+
193
+ query.setParameter("productName", "%" + productName + "%");
194
+
195
+ if (priceFromFlg)
196
+
197
+ query.setParameter("priceFrom", priceFrom);
198
+
199
+ if (priceToFlg)
200
+
201
+ query.setParameter("priceTo", priceTo);
202
+
81
- return result;
203
+ return query.getResultList();
82
204
 
83
205
  }
84
206
 
85
207
 
86
208
 
87
- ```
88
-
89
- ```repository
90
-
91
- package com.example.demo.domain.jdbc;
92
-
93
-
94
-
95
- import java.util.List;
96
-
97
-
98
-
99
- import javax.persistence.EntityManager;
100
-
101
- import javax.persistence.Query;
102
-
103
-
104
-
105
- import org.springframework.beans.factory.annotation.Autowired;
106
-
107
- import org.springframework.stereotype.Repository;
108
-
109
-
110
-
111
- import com.example.demo.domain.model.Product;
112
-
113
- import com.example.demo.domain.repository.ProductRepositoryCustom;
114
-
115
-
116
-
117
- @Repository
118
-
119
- public class ProductRepositoryCustomImpl implements ProductRepositoryCustom {
120
-
121
-
122
-
123
- @Autowired
124
-
125
- EntityManager manager;
126
-
127
-
128
-
129
- @SuppressWarnings("unchecked")
130
-
131
- @Override
132
-
133
- public List<Product> search(String productCode, String productName, int price) {
134
-
135
- StringBuilder sql = new StringBuilder();
136
-
137
- sql.append("SELECT * FROM product WHERE ");
138
-
139
- boolean andFlg = false;
140
-
141
- boolean productCodeFlg = false;
142
-
143
- boolean productCodeNameFlg = false;
144
-
145
- boolean priceFlg = false;
146
-
147
- if (!"".equals(productCode) && productCode != null) {
148
-
149
- sql.append(" productCode LIKE :productCode ");
150
-
151
- productCodeFlg = true;
152
-
153
- andFlg = true;
154
-
155
- }
156
-
157
- boolean productNameFlg = false;
158
-
159
- if (!"".equals(productName) && productName != null) {
160
-
161
- if (andFlg)
162
-
163
- sql.append(" AND ");
164
-
165
- sql.append("productName LIKE :productName ");
166
-
167
- productNameFlg = true;
168
-
169
- andFlg = true;
170
-
171
- }
172
-
173
-
174
-
175
- Query query = manager.createQuery(sql.toString());
176
-
177
- if (productCodeFlg)
178
-
179
- query.setParameter("productCode", "%" + productCode + "%");
180
-
181
- if (productNameFlg)
182
-
183
- query.setParameter("productName", "%" + productName + "%");
184
-
185
- return query.getResultList();
186
-
187
- }
188
-
189
-
190
-
191
209
  }
192
210
 
193
211
 
@@ -228,9 +246,9 @@
228
246
 
229
247
  <tr>
230
248
 
231
- <td>価格帯 : <input type="text" class="form-control" id="price_from" name="priceFrom" th:value="${priceFrom}"/>
249
+ <td>価格帯 : <input type="text" class="form-control" id="priceFrom" name="priceFrom" th:value="${priceFrom}"/>
232
-
250
+
233
- ～ <input type="text" class="form-control" id="price_to" name="priceTo" th:value="${priceTo}"/></td>
251
+ ～ <input type="text" class="form-control" id="priceTo" name="priceTo" th:value="${priceTo}"/></td>
234
252
 
235
253
  </tr>
236
254
 
@@ -273,3 +291,17 @@
273
291
  </html>
274
292
 
275
293
  ```
294
+
295
+ ```DB
296
+
297
+ /* 商品マスタのデータ */
298
+
299
+ insert into product(productCode,productName,price)
300
+
301
+ values
302
+
303
+ ('A0001','商品A',10000)
304
+
305
+ ;
306
+
307
+ ```

1

不要な文の削除

2020/05/22 07:33

投稿

TaSa
TaSa

スコア1

test CHANGED
File without changes
test CHANGED
@@ -273,21 +273,3 @@
273
273
  </html>
274
274
 
275
275
  ```
276
-
277
-
278
-
279
-
280
-
281
- ### 試したこと
282
-
283
-
284
-
285
- ここに問題に対して試したことを記載してください。
286
-
287
-
288
-
289
- ### 補足情報（FW/ツールのバージョンなど）
290
-
291
-
292
-
293
- ここにより詳細な情報を記載してください。