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

編集履歴

質問編集履歴

3

ダンプファイルの追加

2019/03/20 04:19

投稿

kitten
kitten

スコア25

title CHANGED
File without changes
body CHANGED
@@ -98,6 +98,66 @@
98
98
  </html>
99
99
 
100
100
  ```
101
+ ### ダンプファイル（必要最低限のみ）
102
+ 重要な部分は上部の情報だと思ったのでレコード自体は一部のみあげております。
103
+
104
+ ```dump
105
+ --
106
+ -- PostgreSQL database dump
107
+ --
108
+
109
+ -- Dumped from database version 11.1
110
+ -- Dumped by pg_dump version 11.1
111
+
112
+ SET statement_timeout = 0;
113
+ SET lock_timeout = 0;
114
+ SET idle_in_transaction_session_timeout = 0;
115
+ SET client_encoding = 'UTF8';
116
+ SET standard_conforming_strings = on;
117
+ SELECT pg_catalog.set_config('search_path', '', false);
118
+ SET check_function_bodies = false;
119
+ SET client_min_messages = warning;
120
+ SET row_security = off;
121
+
122
+ SET default_tablespace = '';
123
+
124
+ SET default_with_oids = false;
125
+
126
+ --
127
+ -- Name: search_gaityu; Type: TABLE; Schema: public; Owner: postgres
128
+ --
129
+
130
+ CREATE TABLE public.search_gaityu (
131
+ customer character(80),
132
+ prodname character(80),
133
+ partname character(80),
134
+ part2name character(80),
135
+ partcode character(80),
136
+ stkvalue character(80),
137
+ other character(80)
138
+ );
139
+
140
+
141
+ ALTER TABLE public.search_gaityu OWNER TO postgres;
142
+
143
+ --
144
+ -- Data for Name: search_gaityu; Type: TABLE DATA; Schema: public; Owner: postgres
145
+ --
146
+
147
+ COPY public.search_gaityu (customer, prodname, partname, part2name, partcode, stkvalue, other) FROM stdin;
148
+ (有)横山研磨 \N ﾚﾊﾞｰ \N 3232 059 0040 2 \N
149
+ (有)横山研磨 TJ-35P2-BC/40A ｺﾝﾀｸﾄﾉｰｽﾞB \N \N 4,000.00 一式(100個)
150
+ (有)横山研磨 TJ-35P2-BC/40A ｺﾝﾀｸﾄﾉｰｽﾞB \N \N 4,000.00 一式(200個)
151
+ (有)横山研磨 TJ-35P2-BC/40A ｺﾝﾀｸﾄﾉｰｽﾞB \N \N 4,000.00 一式(300個)
152
+ (有)横山研磨 TJ-35P2-BC/40A ｺﾝﾀｸﾄﾉｰｽﾞB \N \N 4,000.00 一式(360個)
153
+
154
+ --
155
+ -- PostgreSQL database dump complete
156
+ --
157
+ ```
158
+
159
+
160
+
101
161
  ### 発生している問題・エラーメッセージ
102
162
 
103
163
  前回と同じ表で説明させて頂きます。

2

ソースコードの修正

2019/03/20 04:19

投稿

kitten
kitten

スコア25

title CHANGED
File without changes
body CHANGED
@@ -8,6 +8,7 @@
8
8
 
9
9
  尚、項目を分かり易くするためカラム名を変更しております。
10
10
  ### 該当のソースコード
11
+
11
12
  ```JSP
12
13
  <%@page import="java.util.*"%>
13
14
  <%@page import="java.sql.*"%>
@@ -17,81 +18,86 @@
17
18
  <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
18
19
  <html>
19
20
  <head>
20
- <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
21
+ <meta http-equiv="Content-Type" content="text/html; charset="UTF-8">
21
22
 
22
- <title>PostgreSQL server connection test</title>
23
+ <title>検索結果画面</title>
23
24
  </head>
24
25
  <body>
25
26
 
26
27
 
27
28
  <%
28
- //変数定義
29
+ //変数定義
29
- Connection conn = null;
30
+ Connection conn = null;
30
- PreparedStatement ps = null;
31
+ PreparedStatement ps = null;
31
- ResultSet rs = null;
32
+ ResultSet rs = null;
32
33
 
33
- try {
34
+ try {
34
35
 
35
- String dbURL = "jdbc:postgresql://localhost:5432/testdb";
36
+ String dbURL = "jdbc:postgresql://localhost:5432/testdb";
36
- String user = "postgres";
37
+ String user = "postgres";
37
- String pass = "takahashi";
38
+ String pass = "takahashi";
38
- String key = "%";
39
+ String key = "%";
39
40
 
40
- if(request.getParameter("key") != null) {
41
+ if(request.getParameter("key") != null) {
41
- if(request.getParameter("key") != "") {
42
+ if(request.getParameter("key") != "") {
42
- key = request.getParameter("key") + "%";
43
+ key = request.getParameter("key") + "%";
43
- }
44
+ }
44
- }
45
+ }
45
46
 
46
- System.out.println("*** key = " + key);
47
+ System.out.println("*** key = " + key);
47
48
 
48
- conn = DriverManager.getConnection(dbURL, user, pass);
49
+ conn = DriverManager.getConnection(dbURL, user, pass);
49
50
 
51
+
52
+ String sql = "SELECT * from search_gaityu WHERE customer ILIKE ? or prodname ILIKE ? or partname ILIKE ? or part2name ILIKE ? or partcode ILIKE ? ";
50
53
 
54
+ ps = conn.prepareStatement(sql);
51
- String sql = "SELECT * from search_gaityu WHERE stock ILIKE ?";
55
+ key = key.replace("*", "%");
56
+ ps.setString(1, key);
57
+ ps.setString(2, key);//ok
58
+ ps.setString(3, key);
59
+ ps.setString(4, key);
60
+ ps.setString(5, key);//ok
52
61
 
53
- ps = conn.prepareStatement(sql);
62
+
54
- key = key.replace("*", "%");
55
- ps.setString(1, key);
56
63
 
57
-
58
-
59
64
  //SELECTを実行する
60
65
  rs = ps.executeQuery();
61
-
66
+
62
67
  //取得した結果を全件出力する
63
68
  //out.println("<div class="table-scroll">");
64
69
  out.println("<table>");
65
- out.println("<tr><th>取引先</th><th>製品名</th><th>部品名</th><th>部品名2</th><th>部品コード</th><th>仕入値</th><th>仕入値2</th</tr>");
70
+ out.println("<tr><th>取引先</th><th>製品名</th><th>部品名</th><th>部品名2</th><th>部品コード</th><th>仕入値</th><th>備考</th</tr>");
66
71
  while(rs.next()){
67
- out.println("<tr>");
72
+ out.println("<tr>");
68
- out.print("<td>" + rs.getString("customer") + "</td>");
73
+ out.print("<td>" + rs.getString("customer") + "</td>");
69
- out.print("<td>" + rs.getString("pname") + "</td>");
74
+ out.print("<td>" + rs.getString("prodname") + "</td>");
70
- out.print("<td>" + rs.getString("parts") + "</td>");
71
- out.print("<td>" + rs.getString("parts2") + "</td>");
75
+ out.print("<td>" + rs.getString("partname") + "</td>");
76
+ out.print("<td>" + rs.getString("part2name") + "</td>");
72
- out.print("<td>" + rs.getString("stock") + "</td>");
77
+ out.print("<td>" + rs.getString("partcode") + "</td>");
78
+ out.print("<td>" + rs.getString("stkvalue") + "</td>");
73
- out.print("<td>" + rs.getString("other") + "</td>");
79
+ out.print("<td>" + rs.getString("other") + "</td>");
74
- out.print("<td>" + rs.getString("other2") + "</td>");
75
- out.println("</tr>");
80
+ out.println("</tr>");
76
81
  }
77
82
  out.println("</table>");
78
83
  //out.println("</div>");
79
84
 
80
- } catch (SQLException ex) {
85
+ } catch (SQLException ex) {
81
- ex.printStackTrace();
86
+ ex.printStackTrace();
82
- } finally {
87
+ } finally {
83
- try {
88
+ try {
84
- if (conn != null && !conn.isClosed()) {
89
+ if (conn != null && !conn.isClosed()) {
85
- conn.close();
90
+ conn.close();
86
- }
91
+ }
87
- } catch (SQLException ex) {
92
+ } catch (SQLException ex) {
88
- ex.printStackTrace();
93
+ ex.printStackTrace();
89
- }
94
+ }
90
- }
95
+ }
91
96
  %>
92
97
  </body>
93
- </html>```
98
+ </html>
94
99
 
100
+ ```
95
101
  ### 発生している問題・エラーメッセージ
96
102
 
97
103
  前回と同じ表で説明させて頂きます。

1

タグ　SQLの追加

2019/03/19 04:51

投稿

kitten
kitten

スコア25

title CHANGED
File without changes
body CHANGED
File without changes