回答編集履歴

コード修正

2024/01/31 02:53

投稿

jimbe

スコア13357

answer CHANGED Viewed

@@ -109,44 +109,41 @@
 ---
 StringJoiner を使ったり bind の処理を SQL を組む時にリストに用意するとこんな感じ...
 ```java
+  @FunctionalInterface
-import java.util.*;
+  interface Binder {
-import java.util.function.BiConsumer;
-public class TestMain {
-  public static void main(String[] args) throws Exception {
+    void accept(PreparedStatement pstmt, int index) throws SQLException;
-    update("A", "B", "C", "D", "E");
   }
   public static void update(String empId, String empName, String gender, String birthday, String deptId) throws SQLException, ClassNotFoundException {
     StringJoiner builder = new StringJoiner(", ", "UPDATE employee SET ", " WHERE emp_id = ?");
-    List<BiConsumer<PreparedStatement,Integer>> bindList = new ArrayList<>();
+    List<Binder> binderList = new ArrayList<>();
     //selection
     if (empName != null) {
       builder.add("emp_name = ?");
-      bindList.add((pstat,i) -> pstat.setString(i, empName));
+      binderList.add((pstat,i) -> pstat.setString(i, empName));
     }
     if (gender != null) {
       builder.add("gender = ?");
-      bindList.add((pstat,i) -> pstat.setString(i, gender));
+      binderList.add((pstat,i) -> pstat.setString(i, gender));
     }
     if (birthday != null) {
       builder.add("birthday = ?");
-      bindList.add((pstat,i) -> pstat.setString(i, birthday));
+      binderList.add((pstat,i) -> pstat.setString(i, birthday));
     }
     if (deptId != null) {
       builder.add("dept_id = ?");
-      bindList.add((pstat,i) -> pstat.setString(i, deptId));
+      binderList.add((pstat,i) -> pstat.setString(i, deptId));
     }
     //where
-    bindList.add((pstat,i) -> pstat.setString(i, empId));
+    binderList.add((pstat,i) -> pstat.setString(i, empId));
     Connection connection = null;
     try {
       connection = DBManager.getConnection();
       PreparedStatement pstat = connection.prepareStatement(builder.toString());
       //bind
-      for(int i=0; i<bindList.size(); i++) bindList.get(i).accept(pstat,i+1);
+      for(int i=0; i<binderList.size(); i++) binderList.get(i).accept(pstat,i+1);
       pstat.executeUpdate();
@@ -154,10 +151,6 @@
       connection.close();
     }
   }
-  //以下テスト用ダミー
-  //略
-}
 ```
 ```
 Collection: preparedStatement("UPDATE employee SET emp_name = ?, gender = ?, birthday = ?, dept_id = ? WHERE emp_id = ?")
@@ -192,17 +185,17 @@
   static class UpdateBuilder {
     @FunctionalInterface
-    interface Bind {
+    interface Binder {
       void accept(PreparedStatement pstmt, int index) throws SQLException;
     }
-    private final StringJoiner builder;
+    private final StringJoiner update;
     private int valueCount;
-    private final List<Bind> bindList = new ArrayList<>();
+    private final List<Binder> binderList = new ArrayList<>();
     private final StringJoiner where = new StringJoiner(" AND ", " WHERE ", "").setEmptyValue("");
     public UpdateBuilder(String table) {
-      builder = new StringJoiner(", ", "UPDATE " + table + " SET ", "");
+      update = new StringJoiner(", ", "UPDATE " + table + " SET ", "");
     }
     //value が null なら入れない
@@ -213,10 +206,10 @@
     public UpdateBuilder put(String column, String value) {
       return put(column, (pstat,i) -> pstat.setString(i, value));
     }
-    public UpdateBuilder put(String column, BiConsumer<PreparedStatement,Integer> bind) {
+    public UpdateBuilder put(String column, Binder bind) {
       if(column == null || bind == null) throw new NullPointerException("column=" + column + ", bind=" + bind);
-      builder.add(column + " = ?");
+      update.add(column + " = ?");
-      bindList.add(valueCount++, bind);
+      binderList.add(valueCount++, bind);
       return this;
     }
@@ -224,14 +217,14 @@
     public UpdateBuilder where(String column, String value) {
       where.add(column + " = ?");
-      bindList.add((pstat,i) -> pstat.setString(i, value));
+      binderList.add((pstat,i) -> pstat.setString(i, value));
       return this;
     }
     public PreparedStatement prepareStatement(Connection conn) throws SQLException {
-      String sql = builder.toString() + where.toString();
+      String sql = update.toString() + where.toString();
       PreparedStatement pstat = conn.prepareStatement(sql);
-      for(int i=0; i<bindList.size(); i++) bindList.get(i).accept(pstat,i+1);
+      for(int i=0; i<binderList.size(); i++) binderList.get(i).accept(pstat,i+1);
       return pstat;
     }
   }

コード修正

2024/01/31 02:26

投稿

jimbe

スコア13357

answer CHANGED Viewed

@@ -71,15 +71,15 @@
   }
   static class SQLException extends Exception {}
   static class PreparedStatement {
-    void setString(int i, String s) {
+    void setString(int i, String s) throws SQLException {
       System.out.println("PreparedStatement: setString(" + i + ", \"" + s + "\")");
     }
-    void executeUpdate() {
+    void executeUpdate() throws SQLException {
       System.out.println("PreparedStatement: executeUpdate()");
     };
   }
   static class Connection {
-    PreparedStatement prepareStatement(String s) {
+    PreparedStatement prepareStatement(String s) throws SQLException {
       System.out.println("Collection: preparedStatement(\"" + s + "\")");
       return new PreparedStatement();
     };
@@ -191,9 +191,14 @@
   }
   static class UpdateBuilder {
+    @FunctionalInterface
+    interface Bind {
+      void accept(PreparedStatement pstmt, int index) throws SQLException;
+    }
     private final StringJoiner builder;
     private int valueCount;
-    private final List<BiConsumer<PreparedStatement,Integer>> bindList = new ArrayList<>();
+    private final List<Bind> bindList = new ArrayList<>();
     private final StringJoiner where = new StringJoiner(" AND ", " WHERE ", "").setEmptyValue("");
     public UpdateBuilder(String table) {

修正

2024/01/30 08:01

投稿

jimbe

スコア13357

answer CHANGED Viewed

@@ -171,6 +171,25 @@
 SQL 関係を纏めユーティリティ的なクラスを作ると
 ```java
+  public static void update(String empId, String empName, String gender, String birthday, String deptId) throws SQLException, ClassNotFoundException {
+    UpdateBuilder builder = new UpdateBuilder("employee")
+        .where("emp_id", empId)
+        .putIfNotNull("emp_name", empName)
+        .putIfNotNull("gender", gender)
+        .putIfNotNull("birthday", birthday)
+        .putIfNotNull("dept_id", deptId);
+    if(builder.size() == 0) return; //update する要素が何も無いなら無処理
+    Connection connection = null;
+    try {
+      connection = DBManager.getConnection();
+      builder.prepareStatement(connection).executeUpdate();
+    } finally {
+      connection.close();
+    }
+  }
   static class UpdateBuilder {
     private final StringJoiner builder;
     private int valueCount;
@@ -183,13 +202,11 @@
     //value が null なら入れない
     public UpdateBuilder putIfNotNull(String column, String value) {
-      if(value != null) put(column, (pstat,i) -> pstat.setString(i, value));
+      return value == null ? this : put(column, value);
-      return this;
     }
     //value が null でも入れる
     public UpdateBuilder put(String column, String value) {
-      put(column, (pstat,i) -> pstat.setString(i, value));
+      return put(column, (pstat,i) -> pstat.setString(i, value));
-      return this;
     }
     public UpdateBuilder put(String column, BiConsumer<PreparedStatement,Integer> bind) {
       if(column == null || bind == null) throw new NullPointerException("column=" + column + ", bind=" + bind);
@@ -214,24 +231,4 @@
     }
   }
 ```
-```java
-  public static void update(String empId, String empName, String gender, String birthday, String deptId) throws SQLException, ClassNotFoundException {
-    UpdateBuilder builder = new UpdateBuilder("employee")
-        .where("emp_id", empId)
-        .putIfNotNull("emp_name", empName)
-        .putIfNotNull("gender", gender)
-        .putIfNotNull("birthday", birthday)
-        .putIfNotNull("dept_id", deptId);
-    if(builder.size() == 0) return; //update する要素が何も無いなら無処理
-    Connection connection = null;
-    try {
-      connection = DBManager.getConnection();
-      builder.prepareStatement(connection).executeUpdate();
-    } finally {
-      connection.close();
-    }
-  }
-```
 かなり見た目簡単になります。

追加修正

2024/01/30 07:44

投稿

jimbe

スコア13357

answer CHANGED Viewed

@@ -119,23 +119,23 @@
   public static void update(String empId, String empName, String gender, String birthday, String deptId) throws SQLException, ClassNotFoundException {
-    StringJoiner builder = new StringJoiner(",", "UPDATE employee SET", " WHERE emp_id = ?");
+    StringJoiner builder = new StringJoiner(", ", "UPDATE employee SET ", " WHERE emp_id = ?");
     List<BiConsumer<PreparedStatement,Integer>> bindList = new ArrayList<>();
     //selection
     if (empName != null) {
-      builder.add(" emp_name = ?");
+      builder.add("emp_name = ?");
       bindList.add((pstat,i) -> pstat.setString(i, empName));
     }
     if (gender != null) {
-      builder.add(" gender = ?");
+      builder.add("gender = ?");
       bindList.add((pstat,i) -> pstat.setString(i, gender));
     }
     if (birthday != null) {
-      builder.add(" birthday = ?");
+      builder.add("birthday = ?");
       bindList.add((pstat,i) -> pstat.setString(i, birthday));
     }
     if (deptId != null) {
-      builder.add(" dept_id = ?");
+      builder.add("dept_id = ?");
       bindList.add((pstat,i) -> pstat.setString(i, deptId));
     }
     //where
@@ -156,28 +156,7 @@
   }
   //以下テスト用ダミー
-  static class ConstantSQL {
-    static final String SQL_UPDATE = "[ConstantSQL.SQL_UPDATE]";
-  }
-  static class SQLException extends Exception {}
-  static class PreparedStatement {
-    void setString(int i, String s) {
-      System.out.println("PreparedStatement: setString(" + i + ", \"" + s + "\")");
-    }
-    void executeUpdate() throws SQLException {
-      System.out.println("PreparedStatement: executeUpdate()");
-    };
-  }
-  static class Connection {
-    PreparedStatement prepareStatement(String s) {
-      System.out.println("Collection: preparedStatement(\"" + s + "\")");
-      return new PreparedStatement();
-    };
-    void close() {}
+  //略
-  }
-  static class DBManager {
-    static Connection getConnection() { return new Connection(); }
-  }
 }
 ```
 ```
@@ -188,4 +167,71 @@
 PreparedStatement: setString(4, "E")
 PreparedStatement: setString(5, "A")
 PreparedStatement: executeUpdate()
-```
+```
+SQL 関係を纏めユーティリティ的なクラスを作ると
+```java
+  static class UpdateBuilder {
+    private final StringJoiner builder;
+    private int valueCount;
+    private final List<BiConsumer<PreparedStatement,Integer>> bindList = new ArrayList<>();
+    private final StringJoiner where = new StringJoiner(" AND ", " WHERE ", "").setEmptyValue("");
+    public UpdateBuilder(String table) {
+      builder = new StringJoiner(", ", "UPDATE " + table + " SET ", "");
+    }
+    //value が null なら入れない
+    public UpdateBuilder putIfNotNull(String column, String value) {
+      if(value != null) put(column, (pstat,i) -> pstat.setString(i, value));
+      return this;
+    }
+    //value が null でも入れる
+    public UpdateBuilder put(String column, String value) {
+      put(column, (pstat,i) -> pstat.setString(i, value));
+      return this;
+    }
+    public UpdateBuilder put(String column, BiConsumer<PreparedStatement,Integer> bind) {
+      if(column == null || bind == null) throw new NullPointerException("column=" + column + ", bind=" + bind);
+      builder.add(column + " = ?");
+      bindList.add(valueCount++, bind);
+      return this;
+    }
+    public int size() { return valueCount; }
+    public UpdateBuilder where(String column, String value) {
+      where.add(column + " = ?");
+      bindList.add((pstat,i) -> pstat.setString(i, value));
+      return this;
+    }
+    public PreparedStatement prepareStatement(Connection conn) throws SQLException {
+      String sql = builder.toString() + where.toString();
+      PreparedStatement pstat = conn.prepareStatement(sql);
+      for(int i=0; i<bindList.size(); i++) bindList.get(i).accept(pstat,i+1);
+      return pstat;
+    }
+  }
+```
+```java
+  public static void update(String empId, String empName, String gender, String birthday, String deptId) throws SQLException, ClassNotFoundException {
+    UpdateBuilder builder = new UpdateBuilder("employee")
+        .where("emp_id", empId)
+        .putIfNotNull("emp_name", empName)
+        .putIfNotNull("gender", gender)
+        .putIfNotNull("birthday", birthday)
+        .putIfNotNull("dept_id", deptId);
+    if(builder.size() == 0) return; //update する要素が何も無いなら無処理
+    Connection connection = null;
+    try {
+      connection = DBManager.getConnection();
+      builder.prepareStatement(connection).executeUpdate();
+    } finally {
+      connection.close();
+    }
+  }
+```
+かなり見た目簡単になります。

表示微修正

2024/01/29 19:14

投稿

jimbe

スコア13357

answer CHANGED Viewed

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

追加

2024/01/29 19:12

投稿

jimbe

スコア13357

answer CHANGED Viewed

@@ -61,7 +61,7 @@
       // SQL文を実行
       preparedStatement.executeUpdate();
     } finally {
+      connection.close();
     }
   }
@@ -83,6 +83,7 @@
       System.out.println("Collection: preparedStatement(\"" + s + "\")");
       return new PreparedStatement();
     };
+    void close() {}
   }
   static private class DBManager {
     static Connection getConnection() { return new Connection(); }
@@ -103,4 +104,87 @@
 ```
 UPDATE employee SET emp_name = ?, gender = ?, birthday = ?, dept_id = ? WHERE emp_id = ? SET emp_name = ?, gender = ?, birthday = ?, dept_id = ? WHERE emp_id = ?
 ```
-等という SQL が作られていることになります。
+等という SQL が作られていることになります。
+---
+StringJoiner を使ったり bind の処理を SQL を組む時にリストに用意するとこんな感じ...
+```java
+import java.util.*;
+import java.util.function.BiConsumer;
+public class TestMain {
+  public static void main(String[] args) throws Exception {
+    update("A", "B", "C", "D", "E");
+  }
+  public static void update(String empId, String empName, String gender, String birthday, String deptId) throws SQLException, ClassNotFoundException {
+    StringJoiner builder = new StringJoiner(",", "UPDATE employee SET", " WHERE emp_id = ?");
+    List<BiConsumer<PreparedStatement,Integer>> bindList = new ArrayList<>();
+    //selection
+    if (empName != null) {
+      builder.add(" emp_name = ?");
+      bindList.add((pstat,i) -> pstat.setString(i, empName));
+    }
+    if (gender != null) {
+      builder.add(" gender = ?");
+      bindList.add((pstat,i) -> pstat.setString(i, gender));
+    }
+    if (birthday != null) {
+      builder.add(" birthday = ?");
+      bindList.add((pstat,i) -> pstat.setString(i, birthday));
+    }
+    if (deptId != null) {
+      builder.add(" dept_id = ?");
+      bindList.add((pstat,i) -> pstat.setString(i, deptId));
+    }
+    //where
+    bindList.add((pstat,i) -> pstat.setString(i, empId));
+    Connection connection = null;
+    try {
+      connection = DBManager.getConnection();
+      PreparedStatement pstat = connection.prepareStatement(builder.toString());
+      //bind
+      for(int i=0; i<bindList.size(); i++) bindList.get(i).accept(pstat,i+1);
+      pstat.executeUpdate();
+    } finally {
+      connection.close();
+    }
+  }
+  //以下テスト用ダミー
+  static class ConstantSQL {
+    static final String SQL_UPDATE = "[ConstantSQL.SQL_UPDATE]";
+  }
+  static class SQLException extends Exception {}
+  static class PreparedStatement {
+    void setString(int i, String s) {
+      System.out.println("PreparedStatement: setString(" + i + ", \"" + s + "\")");
+    }
+    void executeUpdate() throws SQLException {
+      System.out.println("PreparedStatement: executeUpdate()");
+    };
+  }
+  static class Connection {
+    PreparedStatement prepareStatement(String s) {
+      System.out.println("Collection: preparedStatement(\"" + s + "\")");
+      return new PreparedStatement();
+    };
+    void close() {}
+  }
+  static class DBManager {
+    static Connection getConnection() { return new Connection(); }
+  }
+}
+```
+```
+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()
+```