teratail
回答率
85.46%
質問するログイン新規登録
トップOracle Databaseに関する質問jdbc update入力された値のみ更新、未入力は更新しない

編集履歴

回答編集履歴

6

コード修正

2024/01/31 02:53

投稿

jimbe
jimbe

スコア12721

test CHANGED
@@ -109,44 +109,41 @@
109
109
  ---
110
110
  StringJoiner を使ったり bind の処理を SQL を組む時にリストに用意するとこんな感じ...
111
111
  ```java
112
+ @FunctionalInterface
112
- import java.util.*;
113
+ interface Binder {
113
- import java.util.function.BiConsumer;
114
-
115
- public class TestMain {
116
- public static void main(String[] args) throws Exception {
114
+ void accept(PreparedStatement pstmt, int index) throws SQLException;
117
- update("A", "B", "C", "D", "E");
118
115
  }
119
116
 
120
117
  public static void update(String empId, String empName, String gender, String birthday, String deptId) throws SQLException, ClassNotFoundException {
121
118
 
122
119
  StringJoiner builder = new StringJoiner(", ", "UPDATE employee SET ", " WHERE emp_id = ?");
123
- List<BiConsumer<PreparedStatement,Integer>> bindList = new ArrayList<>();
120
+ List<Binder> binderList = new ArrayList<>();
124
121
  //selection
125
122
  if (empName != null) {
126
123
  builder.add("emp_name = ?");
127
- bindList.add((pstat,i) -> pstat.setString(i, empName));
124
+ binderList.add((pstat,i) -> pstat.setString(i, empName));
128
125
  }
129
126
  if (gender != null) {
130
127
  builder.add("gender = ?");
131
- bindList.add((pstat,i) -> pstat.setString(i, gender));
128
+ binderList.add((pstat,i) -> pstat.setString(i, gender));
132
129
  }
133
130
  if (birthday != null) {
134
131
  builder.add("birthday = ?");
135
- bindList.add((pstat,i) -> pstat.setString(i, birthday));
132
+ binderList.add((pstat,i) -> pstat.setString(i, birthday));
136
133
  }
137
134
  if (deptId != null) {
138
135
  builder.add("dept_id = ?");
139
- bindList.add((pstat,i) -> pstat.setString(i, deptId));
136
+ binderList.add((pstat,i) -> pstat.setString(i, deptId));
140
137
  }
141
138
  //where
142
- bindList.add((pstat,i) -> pstat.setString(i, empId));
139
+ binderList.add((pstat,i) -> pstat.setString(i, empId));
143
140
 
144
141
  Connection connection = null;
145
142
  try {
146
143
  connection = DBManager.getConnection();
147
144
  PreparedStatement pstat = connection.prepareStatement(builder.toString());
148
145
  //bind
149
- for(int i=0; i<bindList.size(); i++) bindList.get(i).accept(pstat,i+1);
146
+ for(int i=0; i<binderList.size(); i++) binderList.get(i).accept(pstat,i+1);
150
147
 
151
148
  pstat.executeUpdate();
152
149
 
@@ -154,10 +151,6 @@
154
151
  connection.close();
155
152
  }
156
153
  }
157
-
158
- //以下テスト用ダミー
159
- //略
160
- }
161
154
  ```
162
155
  ```
163
156
  Collection: preparedStatement("UPDATE employee SET emp_name = ?, gender = ?, birthday = ?, dept_id = ? WHERE emp_id = ?")
@@ -192,17 +185,17 @@
192
185
 
193
186
  static class UpdateBuilder {
194
187
  @FunctionalInterface
195
- interface Bind {
188
+ interface Binder {
196
189
  void accept(PreparedStatement pstmt, int index) throws SQLException;
197
190
  }
198
191
 
199
- private final StringJoiner builder;
192
+ private final StringJoiner update;
200
193
  private int valueCount;
201
- private final List<Bind> bindList = new ArrayList<>();
194
+ private final List<Binder> binderList = new ArrayList<>();
202
195
  private final StringJoiner where = new StringJoiner(" AND ", " WHERE ", "").setEmptyValue("");
203
196
 
204
197
  public UpdateBuilder(String table) {
205
- builder = new StringJoiner(", ", "UPDATE " + table + " SET ", "");
198
+ update = new StringJoiner(", ", "UPDATE " + table + " SET ", "");
206
199
  }
207
200
 
208
201
  //value が null なら入れない
@@ -213,10 +206,10 @@
213
206
  public UpdateBuilder put(String column, String value) {
214
207
  return put(column, (pstat,i) -> pstat.setString(i, value));
215
208
  }
216
- public UpdateBuilder put(String column, BiConsumer<PreparedStatement,Integer> bind) {
209
+ public UpdateBuilder put(String column, Binder bind) {
217
210
  if(column == null || bind == null) throw new NullPointerException("column=" + column + ", bind=" + bind);
218
- builder.add(column + " = ?");
211
+ update.add(column + " = ?");
219
- bindList.add(valueCount++, bind);
212
+ binderList.add(valueCount++, bind);
220
213
  return this;
221
214
  }
222
215
 
@@ -224,14 +217,14 @@
224
217
 
225
218
  public UpdateBuilder where(String column, String value) {
226
219
  where.add(column + " = ?");
227
- bindList.add((pstat,i) -> pstat.setString(i, value));
220
+ binderList.add((pstat,i) -> pstat.setString(i, value));
228
221
  return this;
229
222
  }
230
223
 
231
224
  public PreparedStatement prepareStatement(Connection conn) throws SQLException {
232
- String sql = builder.toString() + where.toString();
225
+ String sql = update.toString() + where.toString();
233
226
  PreparedStatement pstat = conn.prepareStatement(sql);
234
- for(int i=0; i<bindList.size(); i++) bindList.get(i).accept(pstat,i+1);
227
+ for(int i=0; i<binderList.size(); i++) binderList.get(i).accept(pstat,i+1);
235
228
  return pstat;
236
229
  }
237
230
  }

5

コード修正

2024/01/31 02:26

投稿

jimbe
jimbe

スコア12721

test CHANGED
@@ -71,15 +71,15 @@
71
71
  }
72
72
  static class SQLException extends Exception {}
73
73
  static class PreparedStatement {
74
- void setString(int i, String s) {
74
+ void setString(int i, String s) throws SQLException {
75
75
  System.out.println("PreparedStatement: setString(" + i + ", \"" + s + "\")");
76
76
  }
77
- void executeUpdate() {
77
+ void executeUpdate() throws SQLException {
78
78
  System.out.println("PreparedStatement: executeUpdate()");
79
79
  };
80
80
  }
81
81
  static class Connection {
82
- PreparedStatement prepareStatement(String s) {
82
+ PreparedStatement prepareStatement(String s) throws SQLException {
83
83
  System.out.println("Collection: preparedStatement(\"" + s + "\")");
84
84
  return new PreparedStatement();
85
85
  };
@@ -191,9 +191,14 @@
191
191
  }
192
192
 
193
193
  static class UpdateBuilder {
194
+ @FunctionalInterface
195
+ interface Bind {
196
+ void accept(PreparedStatement pstmt, int index) throws SQLException;
197
+ }
198
+
194
199
  private final StringJoiner builder;
195
200
  private int valueCount;
196
- private final List<BiConsumer<PreparedStatement,Integer>> bindList = new ArrayList<>();
201
+ private final List<Bind> bindList = new ArrayList<>();
197
202
  private final StringJoiner where = new StringJoiner(" AND ", " WHERE ", "").setEmptyValue("");
198
203
 
199
204
  public UpdateBuilder(String table) {

4

修正

2024/01/30 08:01

投稿

jimbe
jimbe

スコア12721

test CHANGED
@@ -171,50 +171,6 @@
171
171
 
172
172
  SQL 関係を纏めユーティリティ的なクラスを作ると
173
173
  ```java
174
- static class UpdateBuilder {
175
- private final StringJoiner builder;
176
- private int valueCount;
177
- private final List<BiConsumer<PreparedStatement,Integer>> bindList = new ArrayList<>();
178
- private final StringJoiner where = new StringJoiner(" AND ", " WHERE ", "").setEmptyValue("");
179
-
180
- public UpdateBuilder(String table) {
181
- builder = new StringJoiner(", ", "UPDATE " + table + " SET ", "");
182
- }
183
-
184
- //value が null なら入れない
185
- public UpdateBuilder putIfNotNull(String column, String value) {
186
- if(value != null) put(column, (pstat,i) -> pstat.setString(i, value));
187
- return this;
188
- }
189
- //value が null でも入れる
190
- public UpdateBuilder put(String column, String value) {
191
- put(column, (pstat,i) -> pstat.setString(i, value));
192
- return this;
193
- }
194
- public UpdateBuilder put(String column, BiConsumer<PreparedStatement,Integer> bind) {
195
- if(column == null || bind == null) throw new NullPointerException("column=" + column + ", bind=" + bind);
196
- builder.add(column + " = ?");
197
- bindList.add(valueCount++, bind);
198
- return this;
199
- }
200
-
201
- public int size() { return valueCount; }
202
-
203
- public UpdateBuilder where(String column, String value) {
204
- where.add(column + " = ?");
205
- bindList.add((pstat,i) -> pstat.setString(i, value));
206
- return this;
207
- }
208
-
209
- public PreparedStatement prepareStatement(Connection conn) throws SQLException {
210
- String sql = builder.toString() + where.toString();
211
- PreparedStatement pstat = conn.prepareStatement(sql);
212
- for(int i=0; i<bindList.size(); i++) bindList.get(i).accept(pstat,i+1);
213
- return pstat;
214
- }
215
- }
216
- ```
217
- ```java
218
174
  public static void update(String empId, String empName, String gender, String birthday, String deptId) throws SQLException, ClassNotFoundException {
219
175
 
220
176
  UpdateBuilder builder = new UpdateBuilder("employee")
@@ -233,5 +189,46 @@
233
189
  connection.close();
234
190
  }
235
191
  }
192
+
193
+ static class UpdateBuilder {
194
+ private final StringJoiner builder;
195
+ private int valueCount;
196
+ private final List<BiConsumer<PreparedStatement,Integer>> bindList = new ArrayList<>();
197
+ private final StringJoiner where = new StringJoiner(" AND ", " WHERE ", "").setEmptyValue("");
198
+
199
+ public UpdateBuilder(String table) {
200
+ builder = new StringJoiner(", ", "UPDATE " + table + " SET ", "");
201
+ }
202
+
203
+ //value が null なら入れない
204
+ public UpdateBuilder putIfNotNull(String column, String value) {
205
+ return value == null ? this : put(column, value);
206
+ }
207
+ //value が null でも入れる
208
+ public UpdateBuilder put(String column, String value) {
209
+ return put(column, (pstat,i) -> pstat.setString(i, value));
210
+ }
211
+ public UpdateBuilder put(String column, BiConsumer<PreparedStatement,Integer> bind) {
212
+ if(column == null || bind == null) throw new NullPointerException("column=" + column + ", bind=" + bind);
213
+ builder.add(column + " = ?");
214
+ bindList.add(valueCount++, bind);
215
+ return this;
216
+ }
217
+
218
+ public int size() { return valueCount; }
219
+
220
+ public UpdateBuilder where(String column, String value) {
221
+ where.add(column + " = ?");
222
+ bindList.add((pstat,i) -> pstat.setString(i, value));
223
+ return this;
224
+ }
225
+
226
+ public PreparedStatement prepareStatement(Connection conn) throws SQLException {
227
+ String sql = builder.toString() + where.toString();
228
+ PreparedStatement pstat = conn.prepareStatement(sql);
229
+ for(int i=0; i<bindList.size(); i++) bindList.get(i).accept(pstat,i+1);
230
+ return pstat;
231
+ }
232
+ }
236
233
  ```
237
234
  かなり見た目簡単になります。

3

追加修正

2024/01/30 07:44

投稿

jimbe
jimbe

スコア12721

test CHANGED
@@ -119,23 +119,23 @@
119
119
 
120
120
  public static void update(String empId, String empName, String gender, String birthday, String deptId) throws SQLException, ClassNotFoundException {
121
121
 
122
- StringJoiner builder = new StringJoiner(",", "UPDATE employee SET", " WHERE emp_id = ?");
122
+ StringJoiner builder = new StringJoiner(", ", "UPDATE employee SET ", " WHERE emp_id = ?");
123
123
  List<BiConsumer<PreparedStatement,Integer>> bindList = new ArrayList<>();
124
124
  //selection
125
125
  if (empName != null) {
126
- builder.add(" emp_name = ?");
126
+ builder.add("emp_name = ?");
127
127
  bindList.add((pstat,i) -> pstat.setString(i, empName));
128
128
  }
129
129
  if (gender != null) {
130
- builder.add(" gender = ?");
130
+ builder.add("gender = ?");
131
131
  bindList.add((pstat,i) -> pstat.setString(i, gender));
132
132
  }
133
133
  if (birthday != null) {
134
- builder.add(" birthday = ?");
134
+ builder.add("birthday = ?");
135
135
  bindList.add((pstat,i) -> pstat.setString(i, birthday));
136
136
  }
137
137
  if (deptId != null) {
138
- builder.add(" dept_id = ?");
138
+ builder.add("dept_id = ?");
139
139
  bindList.add((pstat,i) -> pstat.setString(i, deptId));
140
140
  }
141
141
  //where
@@ -156,28 +156,7 @@
156
156
  }
157
157
 
158
158
  //以下テスト用ダミー
159
- static class ConstantSQL {
160
- static final String SQL_UPDATE = "[ConstantSQL.SQL_UPDATE]";
161
- }
162
- static class SQLException extends Exception {}
163
- static class PreparedStatement {
164
- void setString(int i, String s) {
165
- System.out.println("PreparedStatement: setString(" + i + ", \"" + s + "\")");
166
- }
167
- void executeUpdate() throws SQLException {
168
- System.out.println("PreparedStatement: executeUpdate()");
169
- };
170
- }
171
- static class Connection {
172
- PreparedStatement prepareStatement(String s) {
173
- System.out.println("Collection: preparedStatement(\"" + s + "\")");
174
- return new PreparedStatement();
175
- };
176
- void close() {}
159
+ //略
177
- }
178
- static class DBManager {
179
- static Connection getConnection() { return new Connection(); }
180
- }
181
160
  }
182
161
  ```
183
162
  ```
@@ -189,3 +168,70 @@
189
168
  PreparedStatement: setString(5, "A")
190
169
  PreparedStatement: executeUpdate()
191
170
  ```
171
+
172
+ SQL 関係を纏めユーティリティ的なクラスを作ると
173
+ ```java
174
+ static class UpdateBuilder {
175
+ private final StringJoiner builder;
176
+ private int valueCount;
177
+ private final List<BiConsumer<PreparedStatement,Integer>> bindList = new ArrayList<>();
178
+ private final StringJoiner where = new StringJoiner(" AND ", " WHERE ", "").setEmptyValue("");
179
+
180
+ public UpdateBuilder(String table) {
181
+ builder = new StringJoiner(", ", "UPDATE " + table + " SET ", "");
182
+ }
183
+
184
+ //value が null なら入れない
185
+ public UpdateBuilder putIfNotNull(String column, String value) {
186
+ if(value != null) put(column, (pstat,i) -> pstat.setString(i, value));
187
+ return this;
188
+ }
189
+ //value が null でも入れる
190
+ public UpdateBuilder put(String column, String value) {
191
+ put(column, (pstat,i) -> pstat.setString(i, value));
192
+ return this;
193
+ }
194
+ public UpdateBuilder put(String column, BiConsumer<PreparedStatement,Integer> bind) {
195
+ if(column == null || bind == null) throw new NullPointerException("column=" + column + ", bind=" + bind);
196
+ builder.add(column + " = ?");
197
+ bindList.add(valueCount++, bind);
198
+ return this;
199
+ }
200
+
201
+ public int size() { return valueCount; }
202
+
203
+ public UpdateBuilder where(String column, String value) {
204
+ where.add(column + " = ?");
205
+ bindList.add((pstat,i) -> pstat.setString(i, value));
206
+ return this;
207
+ }
208
+
209
+ public PreparedStatement prepareStatement(Connection conn) throws SQLException {
210
+ String sql = builder.toString() + where.toString();
211
+ PreparedStatement pstat = conn.prepareStatement(sql);
212
+ for(int i=0; i<bindList.size(); i++) bindList.get(i).accept(pstat,i+1);
213
+ return pstat;
214
+ }
215
+ }
216
+ ```
217
+ ```java
218
+ public static void update(String empId, String empName, String gender, String birthday, String deptId) throws SQLException, ClassNotFoundException {
219
+
220
+ UpdateBuilder builder = new UpdateBuilder("employee")
221
+ .where("emp_id", empId)
222
+ .putIfNotNull("emp_name", empName)
223
+ .putIfNotNull("gender", gender)
224
+ .putIfNotNull("birthday", birthday)
225
+ .putIfNotNull("dept_id", deptId);
226
+ if(builder.size() == 0) return; //update する要素が何も無いなら無処理
227
+
228
+ Connection connection = null;
229
+ try {
230
+ connection = DBManager.getConnection();
231
+ builder.prepareStatement(connection).executeUpdate();
232
+ } finally {
233
+ connection.close();
234
+ }
235
+ }
236
+ ```
237
+ かなり見た目簡単になります。

2

表示微修正

2024/01/29 19:14

投稿

jimbe
jimbe

スコア12721

test CHANGED
@@ -104,7 +104,8 @@
104
104
  ```
105
105
  UPDATE employee SET emp_name = ?, gender = ?, birthday = ?, dept_id = ? WHERE emp_id = ? SET emp_name = ?, gender = ?, birthday = ?, dept_id = ? WHERE emp_id = ?
106
106
  ```
107
- 等という SQL が作られていることになります。
107
+ というおかしな SQL が作られていることになります。
108
+
108
109
  ---
109
110
  StringJoiner を使ったり bind の処理を SQL を組む時にリストに用意するとこんな感じ...
110
111
  ```java

1

追加

2024/01/29 19:12

投稿

jimbe
jimbe

スコア12721

test CHANGED
@@ -61,7 +61,7 @@
61
61
  // SQL文を実行
62
62
  preparedStatement.executeUpdate();
63
63
  } finally {
64
-
64
+ connection.close();
65
65
  }
66
66
  }
67
67
 
@@ -83,6 +83,7 @@
83
83
  System.out.println("Collection: preparedStatement(\"" + s + "\")");
84
84
  return new PreparedStatement();
85
85
  };
86
+ void close() {}
86
87
  }
87
88
  static private class DBManager {
88
89
  static Connection getConnection() { return new Connection(); }
@@ -104,3 +105,86 @@
104
105
  UPDATE employee SET emp_name = ?, gender = ?, birthday = ?, dept_id = ? WHERE emp_id = ? SET emp_name = ?, gender = ?, birthday = ?, dept_id = ? WHERE emp_id = ?
105
106
  ```
106
107
  等という SQL が作られていることになります。
108
+ ---
109
+ StringJoiner を使ったり bind の処理を SQL を組む時にリストに用意するとこんな感じ...
110
+ ```java
111
+ import java.util.*;
112
+ import java.util.function.BiConsumer;
113
+
114
+ public class TestMain {
115
+ public static void main(String[] args) throws Exception {
116
+ update("A", "B", "C", "D", "E");
117
+ }
118
+
119
+ public static void update(String empId, String empName, String gender, String birthday, String deptId) throws SQLException, ClassNotFoundException {
120
+
121
+ StringJoiner builder = new StringJoiner(",", "UPDATE employee SET", " WHERE emp_id = ?");
122
+ List<BiConsumer<PreparedStatement,Integer>> bindList = new ArrayList<>();
123
+ //selection
124
+ if (empName != null) {
125
+ builder.add(" emp_name = ?");
126
+ bindList.add((pstat,i) -> pstat.setString(i, empName));
127
+ }
128
+ if (gender != null) {
129
+ builder.add(" gender = ?");
130
+ bindList.add((pstat,i) -> pstat.setString(i, gender));
131
+ }
132
+ if (birthday != null) {
133
+ builder.add(" birthday = ?");
134
+ bindList.add((pstat,i) -> pstat.setString(i, birthday));
135
+ }
136
+ if (deptId != null) {
137
+ builder.add(" dept_id = ?");
138
+ bindList.add((pstat,i) -> pstat.setString(i, deptId));
139
+ }
140
+ //where
141
+ bindList.add((pstat,i) -> pstat.setString(i, empId));
142
+
143
+ Connection connection = null;
144
+ try {
145
+ connection = DBManager.getConnection();
146
+ PreparedStatement pstat = connection.prepareStatement(builder.toString());
147
+ //bind
148
+ for(int i=0; i<bindList.size(); i++) bindList.get(i).accept(pstat,i+1);
149
+
150
+ pstat.executeUpdate();
151
+
152
+ } finally {
153
+ connection.close();
154
+ }
155
+ }
156
+
157
+ //以下テスト用ダミー
158
+ static class ConstantSQL {
159
+ static final String SQL_UPDATE = "[ConstantSQL.SQL_UPDATE]";
160
+ }
161
+ static class SQLException extends Exception {}
162
+ static class PreparedStatement {
163
+ void setString(int i, String s) {
164
+ System.out.println("PreparedStatement: setString(" + i + ", \"" + s + "\")");
165
+ }
166
+ void executeUpdate() throws SQLException {
167
+ System.out.println("PreparedStatement: executeUpdate()");
168
+ };
169
+ }
170
+ static class Connection {
171
+ PreparedStatement prepareStatement(String s) {
172
+ System.out.println("Collection: preparedStatement(\"" + s + "\")");
173
+ return new PreparedStatement();
174
+ };
175
+ void close() {}
176
+ }
177
+ static class DBManager {
178
+ static Connection getConnection() { return new Connection(); }
179
+ }
180
+ }
181
+ ```
182
+ ```
183
+ Collection: preparedStatement("UPDATE employee SET emp_name = ?, gender = ?, birthday = ?, dept_id = ? WHERE emp_id = ?")
184
+ PreparedStatement: setString(1, "B")
185
+ PreparedStatement: setString(2, "C")
186
+ PreparedStatement: setString(3, "D")
187
+ PreparedStatement: setString(4, "E")
188
+ PreparedStatement: setString(5, "A")
189
+ PreparedStatement: executeUpdate()
190
+ ```