jdbc update入力された値のみ更新、未入力は更新しない

実現したいこと

jdbc updateにて
■ 更新

値が入力された列のみを更新する
未入力の列は更新されない

この機能を追加させたいです。

発生している問題・分からないこと

更新機能で全ての列に値を指定した場合を最初に実装しましたが、未入力値はそのままで、入力された値のみ更新する機能にしたいです。

エラーメッセージ

error
1java.sql.SQLException: INまたはOUTパラメータがありません - 索引:: 5
2	at com.oracle.database.jdbc/oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:2018)
3	at com.oracle.database.jdbc/oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3736)
4	at com.oracle.database.jdbc/oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3918)
5	at com.oracle.database.jdbc/oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3897)
6	at com.oracle.database.jdbc/oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:992)
7	at jp.co.sss.crud.db.DBController.update(DBController.java:276)
8	at jp.co.sss.crud.main.SystemStart.main(SystemStart.java:86)

該当のソースコード

public static void update(String EmpID, String Empname, String Gender, String Birthday, String DeptId)
			throws SQLException, ClassNotFoundException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;

		try {
			// DBに接続
			connection = DBManager.getConnection();

			// SQL文の初期化
			StringBuilder sqlBuilder = new StringBuilder(ConstantSQL.SQL_UPDATE);
			boolean isFirst = true; // 最初の更新列かどうかを示すフラグ

			// 各列の値が指定されているかをチェックし、SQL文を構築する
			if (Empname != null) {
				sqlBuilder.append(" SET emp_name = ?");
				isFirst = false;
			}
			if (Gender != null) {
				sqlBuilder.append(isFirst ? " SET" : ",")
						.append(" gender = ?");
				isFirst = false;
			}
			if (Birthday != null) {
				sqlBuilder.append(isFirst ? " SET" : ",")
						.append(" birthday = ?");
				isFirst = false;
			}
			if (DeptId != null) {
				sqlBuilder.append(isFirst ? " SET" : ",")
						.append(" dept_id = ?");
			}

			// 最後に WHERE 句を追加
			sqlBuilder.append(" WHERE emp_id = ?");
			
			// ステートメントを作成
			preparedStatement = connection.prepareStatement(sqlBuilder.toString());

			// パラメータをバインド
			int parameterIndex = 1; // パラメータインデックスの初期値
			if (Empname != null) {
				preparedStatement.setString(parameterIndex++, Empname);
			}
			if (Gender != null) {
				preparedStatement.setString(parameterIndex++, Gender);
			}
			if (Birthday != null) {
				preparedStatement.setString(parameterIndex++, Birthday);
			}
			if (DeptId != null) {
				preparedStatement.setString(parameterIndex++, DeptId);
			}
			preparedStatement.setString(parameterIndex++, EmpID); // emp_idのバインド
			// SQL文を実行
			preparedStatement.executeUpdate();


public static String SQL_UPDATE = "UPDATE employee SET emp_name = ?, gender = ?, birthday = ?, dept_id = ? WHERE emp_id = ?";

試したこと・調べたこと

teratailやGoogle等で検索した
ソースコードを自分なりに変更した
知人に聞いた
その他

上記の詳細・結果

Google等で検索した結果このエラーは、SQL 文の実行時にパラメータのインデックスが不正であること。このエラーが発生する原因として考えられるのは、パラメータのバインドが正しく行われていない可能性があるとの事ですが、バインド数、順番とう変更してもわかりませんでした。

補足

特になし

jimbe

2024/01/29 18:22 編集

最後に付いている SQL_UPDATE は何でしょうか。そのエラーが発生した時はどのようなパラメータを指定されていたのでしょうか。そして、そのパラメータの時にどの if 文を通ってどのような SQL が組み立てられたのか、どの if 文を通って何番のバインドがされたのかを(『ここを通ったはずです』では無く)実際に各所にログを入れて確認したのでしょうか。ついでに、 java の変数名はパラメータでも小文字始まりです。他人に見せないならどんなでも構いませんが、見せることになった時に大文字始まりだと違和感がコードの解析の邪魔になりますので、ご留意戴けると有り難いです。 "," 区切りで可変長な文字列を作る場合は StringJoiner を用いると isFirst のような制御を自分で視覚的済みます。また、同じ if 文を繰り返し書くよりも、2回目用の処理のリストを1回目で作成する方が間違いが減らせそうな気がします。

行動規範の内容に同意します

回答1件

メソッドで使っているモノのダミーを用意してどう動いているのか見てみます。

java
1public class TestMain {
2  public static void main(String[] args) throws Exception {
3    update("A", "B", "C", "D", "E");
4  }
5
6  public static void update(String EmpID, String Empname, String Gender, String Birthday, String DeptId) throws SQLException, ClassNotFoundException {
7    Connection connection = null;
8    PreparedStatement preparedStatement = null;
9
10    try {
11      // DBに接続
12      connection = DBManager.getConnection();
13
14      // SQL文の初期化
15      StringBuilder sqlBuilder = new StringBuilder(ConstantSQL.SQL_UPDATE);
16      boolean isFirst = true; // 最初の更新列かどうかを示すフラグ
17
18      // 各列の値が指定されているかをチェックし、SQL文を構築する
19      if (Empname != null) {
20        sqlBuilder.append(" SET emp_name = ?");
21        isFirst = false;
22      }
23      if (Gender != null) {
24        sqlBuilder.append(isFirst ? " SET" : ",")
25        .append(" gender = ?");
26        isFirst = false;
27      }
28      if (Birthday != null) {
29        sqlBuilder.append(isFirst ? " SET" : ",")
30        .append(" birthday = ?");
31        isFirst = false;
32      }
33      if (DeptId != null) {
34        sqlBuilder.append(isFirst ? " SET" : ",")
35        .append(" dept_id = ?");
36      }
37
38      // 最後に WHERE 句を追加
39      sqlBuilder.append(" WHERE emp_id = ?");
40
41      // ステートメントを作成
42      preparedStatement = connection.prepareStatement(sqlBuilder.toString());
43
44      // パラメータをバインド
45      int parameterIndex = 1; // パラメータインデックスの初期値
46      if (Empname != null) {
47        preparedStatement.setString(parameterIndex++, Empname);
48      }
49      if (Gender != null) {
50        preparedStatement.setString(parameterIndex++, Gender);
51      }
52      if (Birthday != null) {
53        preparedStatement.setString(parameterIndex++, Birthday);
54      }
55      if (DeptId != null) {
56        preparedStatement.setString(parameterIndex++, DeptId);
57      }
58      preparedStatement.setString(parameterIndex++, EmpID); // emp_idのバインド
59      // SQL文を実行
60      preparedStatement.executeUpdate();
61    } finally {
62      connection.close();
63    }
64  }
65
66  //以下テスト用ダミー
67  static class ConstantSQL {
68    static final String SQL_UPDATE = "[ConstantSQL.SQL_UPDATE]";
69  }
70  static class SQLException extends Exception {}
71  static class PreparedStatement {
72    void setString(int i, String s) throws SQLException {
73      System.out.println("PreparedStatement: setString(" + i + ", \"" + s + "\")");
74    }
75    void executeUpdate() throws SQLException {
76      System.out.println("PreparedStatement: executeUpdate()");
77    };
78  }
79  static class Connection {
80    PreparedStatement prepareStatement(String s) throws SQLException {
81      System.out.println("Collection: preparedStatement(\"" + s + "\")");
82      return new PreparedStatement();
83    };
84    void close() {}
85  }
86  static private class DBManager {
87    static Connection getConnection() { return new Connection(); }
88  }
89}

Collection: preparedStatement("[ConstantSQL.SQL_UPDATE] SET emp_name = ?, gender = ?, birthday = ?, dept_id = ? WHERE emp_id = ?")
PreparedStatement: setString(1, "B")
PreparedStatement: setString(2, "C")
PreparedStatement: setString(3, "D")
PreparedStatement: setString(4, "E")
PreparedStatement: setString(5, "A")
PreparedStatement: executeUpdate()

動作はあっていると思います。
怪しいのは [ConstantSQL.SQL_UPDATE] で、もし質問のコードの最後に書かれている SQL_UPDATE が ConstantSQL.SQL_UPDATE なら、

UPDATE employee SET emp_name = ?, gender = ?, birthday = ?, dept_id = ? WHERE emp_id = ? SET emp_name = ?, gender = ?, birthday = ?, dept_id = ? WHERE emp_id = ?

というおかしな SQL が作られていることになります。

StringJoiner を使ったり bind の処理を SQL を組む時にリストに用意するとこんな感じ...

java
1  @FunctionalInterface
2  interface Binder {
3    void accept(PreparedStatement pstmt, int index) throws SQLException;
4  }
5
6  public static void update(String empId, String empName, String gender, String birthday, String deptId) throws SQLException, ClassNotFoundException {
7
8    StringJoiner builder = new StringJoiner(", ", "UPDATE employee SET ", " WHERE emp_id = ?");
9    List<Binder> binderList = new ArrayList<>();
10    //selection
11    if (empName != null) {
12      builder.add("emp_name = ?");
13      binderList.add((pstat,i) -> pstat.setString(i, empName));
14    }
15    if (gender != null) {
16      builder.add("gender = ?");
17      binderList.add((pstat,i) -> pstat.setString(i, gender));
18    }
19    if (birthday != null) {
20      builder.add("birthday = ?");
21      binderList.add((pstat,i) -> pstat.setString(i, birthday));
22    }
23    if (deptId != null) {
24      builder.add("dept_id = ?");
25      binderList.add((pstat,i) -> pstat.setString(i, deptId));
26    }
27    //where
28    binderList.add((pstat,i) -> pstat.setString(i, empId));
29
30    Connection connection = null;
31    try {
32      connection = DBManager.getConnection();
33      PreparedStatement pstat = connection.prepareStatement(builder.toString());
34      //bind
35      for(int i=0; i<binderList.size(); i++) binderList.get(i).accept(pstat,i+1);
36
37      pstat.executeUpdate();
38
39    } finally {
40      connection.close();
41    }
42  }

Collection: preparedStatement("UPDATE employee SET emp_name = ?, gender = ?, birthday = ?, dept_id = ? WHERE emp_id = ?")
PreparedStatement: setString(1, "B")
PreparedStatement: setString(2, "C")
PreparedStatement: setString(3, "D")
PreparedStatement: setString(4, "E")
PreparedStatement: setString(5, "A")
PreparedStatement: executeUpdate()

SQL 関係を纏めユーティリティ的なクラスを作ると

java
1  public static void update(String empId, String empName, String gender, String birthday, String deptId) throws SQLException, ClassNotFoundException {
2
3    UpdateBuilder builder = new UpdateBuilder("employee")
4        .where("emp_id", empId)
5        .putIfNotNull("emp_name", empName)
6        .putIfNotNull("gender", gender)
7        .putIfNotNull("birthday", birthday)
8        .putIfNotNull("dept_id", deptId);
9    if(builder.size() == 0) return; //update する要素が何も無いなら無処理
10
11    Connection connection = null;
12    try {
13      connection = DBManager.getConnection();
14      builder.prepareStatement(connection).executeUpdate();
15    } finally {
16      connection.close();
17    }
18  }
19
20  static class UpdateBuilder {
21    @FunctionalInterface
22    interface Binder {
23      void accept(PreparedStatement pstmt, int index) throws SQLException;
24    }
25
26    private final StringJoiner update;
27    private int valueCount;
28    private final List<Binder> binderList = new ArrayList<>();
29    private final StringJoiner where = new StringJoiner(" AND ", " WHERE ", "").setEmptyValue("");
30
31    public UpdateBuilder(String table) {
32      update = new StringJoiner(", ", "UPDATE " + table + " SET ", "");
33    }
34
35    //value が null なら入れない
36    public UpdateBuilder putIfNotNull(String column, String value) {
37      return value == null ? this : put(column, value);
38    }
39    //value が null でも入れる
40    public UpdateBuilder put(String column, String value) {
41      return put(column, (pstat,i) -> pstat.setString(i, value));
42    }
43    public UpdateBuilder put(String column, Binder bind) {
44      if(column == null || bind == null) throw new NullPointerException("column=" + column + ", bind=" + bind);
45      update.add(column + " = ?");
46      binderList.add(valueCount++, bind);
47      return this;
48    }
49
50    public int size() { return valueCount; }
51
52    public UpdateBuilder where(String column, String value) {
53      where.add(column + " = ?");
54      binderList.add((pstat,i) -> pstat.setString(i, value));
55      return this;
56    }
57
58    public PreparedStatement prepareStatement(Connection conn) throws SQLException {
59      String sql = update.toString() + where.toString();
60      PreparedStatement pstat = conn.prepareStatement(sql);
61      for(int i=0; i<binderList.size(); i++) binderList.get(i).accept(pstat,i+1);
62      return pstat;
63    }
64  }