teratail
回答率
85.48%
質問するログイン新規登録
トップに関する質問JSP SQL文where句で複数カラムで検索したい

編集履歴

質問編集履歴

3

ダンプファイルの追加

2019/03/20 04:19

投稿

kitten
kitten

スコア25

test CHANGED
File without changes
test CHANGED
@@ -198,6 +198,126 @@
198
198
 
199
199
  ```
200
200
 
201
+ ### ダンプファイル（必要最低限のみ）
202
+
203
+ 重要な部分は上部の情報だと思ったのでレコード自体は一部のみあげております。
204
+
205
+
206
+
207
+ ```dump
208
+
209
+ --
210
+
211
+ -- PostgreSQL database dump
212
+
213
+ --
214
+
215
+
216
+
217
+ -- Dumped from database version 11.1
218
+
219
+ -- Dumped by pg_dump version 11.1
220
+
221
+
222
+
223
+ SET statement_timeout = 0;
224
+
225
+ SET lock_timeout = 0;
226
+
227
+ SET idle_in_transaction_session_timeout = 0;
228
+
229
+ SET client_encoding = 'UTF8';
230
+
231
+ SET standard_conforming_strings = on;
232
+
233
+ SELECT pg_catalog.set_config('search_path', '', false);
234
+
235
+ SET check_function_bodies = false;
236
+
237
+ SET client_min_messages = warning;
238
+
239
+ SET row_security = off;
240
+
241
+
242
+
243
+ SET default_tablespace = '';
244
+
245
+
246
+
247
+ SET default_with_oids = false;
248
+
249
+
250
+
251
+ --
252
+
253
+ -- Name: search_gaityu; Type: TABLE; Schema: public; Owner: postgres
254
+
255
+ --
256
+
257
+
258
+
259
+ CREATE TABLE public.search_gaityu (
260
+
261
+ customer character(80),
262
+
263
+ prodname character(80),
264
+
265
+ partname character(80),
266
+
267
+ part2name character(80),
268
+
269
+ partcode character(80),
270
+
271
+ stkvalue character(80),
272
+
273
+ other character(80)
274
+
275
+ );
276
+
277
+
278
+
279
+
280
+
281
+ ALTER TABLE public.search_gaityu OWNER TO postgres;
282
+
283
+
284
+
285
+ --
286
+
287
+ -- Data for Name: search_gaityu; Type: TABLE DATA; Schema: public; Owner: postgres
288
+
289
+ --
290
+
291
+
292
+
293
+ COPY public.search_gaityu (customer, prodname, partname, part2name, partcode, stkvalue, other) FROM stdin;
294
+
295
+ (有)横山研磨 \N ﾚﾊﾞｰ \N 3232 059 0040 2 \N
296
+
297
+ (有)横山研磨 TJ-35P2-BC/40A ｺﾝﾀｸﾄﾉｰｽﾞB \N \N 4,000.00 一式(100個)
298
+
299
+ (有)横山研磨 TJ-35P2-BC/40A ｺﾝﾀｸﾄﾉｰｽﾞB \N \N 4,000.00 一式(200個)
300
+
301
+ (有)横山研磨 TJ-35P2-BC/40A ｺﾝﾀｸﾄﾉｰｽﾞB \N \N 4,000.00 一式(300個)
302
+
303
+ (有)横山研磨 TJ-35P2-BC/40A ｺﾝﾀｸﾄﾉｰｽﾞB \N \N 4,000.00 一式(360個)
304
+
305
+
306
+
307
+ --
308
+
309
+ -- PostgreSQL database dump complete
310
+
311
+ --
312
+
313
+ ```
314
+
315
+
316
+
317
+
318
+
319
+
320
+
201
321
  ### 発生している問題・エラーメッセージ
202
322
 
203
323
 

2

ソースコードの修正

2019/03/20 04:19

投稿

kitten
kitten

スコア25

test CHANGED
File without changes
test CHANGED
@@ -18,6 +18,8 @@
18
18
 
19
19
  ### 該当のソースコード
20
20
 
21
+
22
+
21
23
  ```JSP
22
24
 
23
25
  <%@page import="java.util.*"%>
@@ -36,11 +38,11 @@
36
38
 
37
39
  <head>
38
40
 
39
- <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
41
+ <meta http-equiv="Content-Type" content="text/html; charset="UTF-8">
40
-
41
-
42
-
42
+
43
+
44
+
43
- <title>PostgreSQL server connection test</title>
45
+ <title>検索結果画面</title>
44
46
 
45
47
  </head>
46
48
 
@@ -52,139 +54,149 @@
52
54
 
53
55
  <%
54
56
 
55
- //変数定義
56
-
57
- Connection conn = null;
58
-
59
- PreparedStatement ps = null;
60
-
61
- ResultSet rs = null;
62
-
63
-
64
-
65
- try {
66
-
67
-
68
-
69
- String dbURL = "jdbc:postgresql://localhost:5432/testdb";
70
-
71
- String user = "postgres";
72
-
73
- String pass = "takahashi";
74
-
75
- String key = "%";
76
-
77
-
78
-
79
- if(request.getParameter("key") != null) {
80
-
81
- if(request.getParameter("key") != "") {
82
-
83
- key = request.getParameter("key") + "%";
84
-
85
- }
57
+ //変数定義
58
+
59
+ Connection conn = null;
60
+
61
+ PreparedStatement ps = null;
62
+
63
+ ResultSet rs = null;
64
+
65
+
66
+
67
+ try {
68
+
69
+
70
+
71
+ String dbURL = "jdbc:postgresql://localhost:5432/testdb";
72
+
73
+ String user = "postgres";
74
+
75
+ String pass = "takahashi";
76
+
77
+ String key = "%";
78
+
79
+
80
+
81
+ if(request.getParameter("key") != null) {
82
+
83
+ if(request.getParameter("key") != "") {
84
+
85
+ key = request.getParameter("key") + "%";
86
+
87
+ }
88
+
89
+ }
90
+
91
+
92
+
93
+ System.out.println("*** key = " + key);
94
+
95
+
96
+
97
+ conn = DriverManager.getConnection(dbURL, user, pass);
98
+
99
+
100
+
101
+
102
+
103
+ String sql = "SELECT * from search_gaityu WHERE customer ILIKE ? or prodname ILIKE ? or partname ILIKE ? or part2name ILIKE ? or partcode ILIKE ? ";
104
+
105
+
106
+
107
+ ps = conn.prepareStatement(sql);
108
+
109
+ key = key.replace("*", "%");
110
+
111
+ ps.setString(1, key);
112
+
113
+ ps.setString(2, key);//ok
114
+
115
+ ps.setString(3, key);
116
+
117
+ ps.setString(4, key);
118
+
119
+ ps.setString(5, key);//ok
120
+
121
+
122
+
123
+
124
+
125
+
126
+
127
+ //SELECTを実行する
128
+
129
+ rs = ps.executeQuery();
130
+
131
+
132
+
133
+ //取得した結果を全件出力する
134
+
135
+ //out.println("<div class="table-scroll">");
136
+
137
+ out.println("<table>");
138
+
139
+ out.println("<tr><th>取引先</th><th>製品名</th><th>部品名</th><th>部品名2</th><th>部品コード</th><th>仕入値</th><th>備考</th</tr>");
140
+
141
+ while(rs.next()){
142
+
143
+ out.println("<tr>");
144
+
145
+ out.print("<td>" + rs.getString("customer") + "</td>");
146
+
147
+ out.print("<td>" + rs.getString("prodname") + "</td>");
148
+
149
+ out.print("<td>" + rs.getString("partname") + "</td>");
150
+
151
+ out.print("<td>" + rs.getString("part2name") + "</td>");
152
+
153
+ out.print("<td>" + rs.getString("partcode") + "</td>");
154
+
155
+ out.print("<td>" + rs.getString("stkvalue") + "</td>");
156
+
157
+ out.print("<td>" + rs.getString("other") + "</td>");
158
+
159
+ out.println("</tr>");
86
160
 
87
161
  }
88
162
 
89
-
90
-
91
- System.out.println("*** key = " + key);
92
-
93
-
94
-
95
- conn = DriverManager.getConnection(dbURL, user, pass);
96
-
97
-
98
-
99
-
100
-
101
- String sql = "SELECT * from search_gaityu WHERE stock ILIKE ?";
102
-
103
-
104
-
105
- ps = conn.prepareStatement(sql);
106
-
107
- key = key.replace("*", "%");
108
-
109
- ps.setString(1, key);
110
-
111
-
112
-
113
-
114
-
115
-
116
-
117
- //SELECTを実行する
118
-
119
- rs = ps.executeQuery();
120
-
121
-
122
-
123
- //取得した結果を全件出力する
124
-
125
- //out.println("<div class="table-scroll">");
126
-
127
- out.println("<table>");
128
-
129
- out.println("<tr><th>取引先</th><th>製品名</th><th>部品名</th><th>部品名2</th><th>部品コード</th><th>仕入値</th><th>仕入値2</th</tr>");
130
-
131
- while(rs.next()){
132
-
133
- out.println("<tr>");
134
-
135
- out.print("<td>" + rs.getString("customer") + "</td>");
136
-
137
- out.print("<td>" + rs.getString("pname") + "</td>");
138
-
139
- out.print("<td>" + rs.getString("parts") + "</td>");
140
-
141
- out.print("<td>" + rs.getString("parts2") + "</td>");
142
-
143
- out.print("<td>" + rs.getString("stock") + "</td>");
144
-
145
- out.print("<td>" + rs.getString("other") + "</td>");
146
-
147
- out.print("<td>" + rs.getString("other2") + "</td>");
148
-
149
- out.println("</tr>");
150
-
151
- }
152
-
153
163
  out.println("</table>");
154
164
 
155
165
  //out.println("</div>");
156
166
 
157
167
 
158
168
 
159
- } catch (SQLException ex) {
169
+ } catch (SQLException ex) {
160
-
170
+
161
- ex.printStackTrace();
171
+ ex.printStackTrace();
162
-
172
+
163
- } finally {
173
+ } finally {
164
-
174
+
165
- try {
175
+ try {
166
-
176
+
167
- if (conn != null && !conn.isClosed()) {
177
+ if (conn != null && !conn.isClosed()) {
168
-
178
+
169
- conn.close();
179
+ conn.close();
170
-
180
+
171
- }
181
+ }
172
-
182
+
173
- } catch (SQLException ex) {
183
+ } catch (SQLException ex) {
174
-
184
+
175
- ex.printStackTrace();
185
+ ex.printStackTrace();
176
-
186
+
177
- }
187
+ }
178
-
188
+
179
- }
189
+ }
180
190
 
181
191
  %>
182
192
 
183
193
  </body>
184
194
 
185
- </html>```
195
+ </html>
196
+
197
+
198
+
186
-
199
+ ```
187
-
188
200
 
189
201
  ### 発生している問題・エラーメッセージ
190
202
 

1

タグ　SQLの追加

2019/03/19 04:51

投稿

kitten
kitten

スコア25

test CHANGED
File without changes
test CHANGED
File without changes