回答編集履歴

テーブルを 1 つにしたコードに全面変更

2024/03/02 17:14

投稿

jimbe

スコア12672

test CHANGED Viewed

@@ -1,21 +1,7 @@
-質問等の感じからして、あまり仕様を意識せずなんとなく作られていて、ある部分を作った時と別部分を作った時とでその瞬間の考えが違っていてあちこちで齟齬が起きているのではないでしょうか。
-カレーを作るか肉じゃがを作るかシチューを作るか決めないまま料理をしているようなものです。作り慣れているならそれでも何とかなりますが、調べながら作っている状態では何が起きるか分かりません。
+SELECT に関する情報が質問に追加されましたが、テーブルが 2 つあってどちらも有ったり無かったりするのが原因で複雑な処理・SELECT 文になっています。
+テーブルを 1 つに纏めてしまえば id の問題も SELECT の複雑さも無くなり処理が簡単になります。
-とりあえずネットワークは関係無さそうですのでデータベースの部分だけに注目してみます。
-テキスト(とユーザ名)を保存する mutters テーブルと画像を保存して関係するテキストの id を持つ filepaths テーブルがあり、テキスト＋画像、テキストのみ、画像のみの 3 パターンでテーブルに保存するメソッド insertTextAndImage を考えます。
-仕様としては
-- text があれば texts テーブルに保存する.
-- image があれば images テーブルに保存する. この時 text もあって texts テーブルに保存していたら、 texts_id にその id を入れる.
-の 2 点のみです。まずはこれだけをしっかり作ります。
-テーブル構造がアプリに対して十分なのかは(現状こちらには情報がありませんので)考えません。
-テキストの保存メソッドとイメージの保存メソッドをそれぞれ作り、 insertTextAndImage は必要なデータが有れば必要なメソッドを適切なパラメータで呼ぶことに集中します。
+テキストが無い時や画像が無い時等はぞれぞれのレコードを作らなければ何かの節約になると考えられたのかもしれませんが、そのような最適化を考えるのは時期尚早です。
-このように小さな機能をメソッドとして完結するよう定義し、それらを組み合わせてより大きくしていく構造を意識しておくと、混乱し難いかもしれません。(個人の感想です。)
-**※Spring を使わないため Image クラスはダミーを定義・利用しています。**
 ```java
 import java.io.IOException;
 import java.io.InputStream;
@@ -28,102 +14,71 @@
   public static void main(String[] args) throws Exception {
     try(Connection conn = DriverManager.getConnection("jdbc:h2:~/test", "sa", "")) {
       initTables(conn);
-ボールドテキスト
-      // 3パターンをテスト
+      // 3パターンをテスト(少し時間を開けて insert )
       //text & image
-      insertTextAndImage(conn, "Aaa", "ABCDEFG", new Image("test1", new byte[]{1,2,3,4}));
+      insertPost(conn, "Aaa", "ABCDEFG", "file1", new Image("type1", new byte[]{1,2,3,4}));
       //text only
+      Thread.sleep(10);
-      insertTextAndImage(conn, "Bbb", "abcdefg", null);
+      insertPost(conn, "Bbb", "abcdefg", null, null);
       //image only
+      Thread.sleep(10);
-      insertTextAndImage(conn, "Ccc", null, new Image("test2", new byte[]{5,6,7,8}));
+      insertPost(conn, "Ccc", null, "file2", new Image("type2", new byte[]{5,6,7,8}));
       //確認
       printTables(conn);
     }
   }
-  static void insertTextAndImage(Connection conn, String name, String text, Image image) throws SQLException {
+  static void insertPost(Connection conn, String name, String text, String file_name, Image image) throws SQLException {
-    conn.setAutoCommit(false);
+    String sql = "INSERT INTO posts (name, datetime, text, image_name, image_type, image) VALUES (?, ?, ?, ?, ?, ?)";
+    try(PreparedStatement pstmt = conn.prepareStatement(sql)) {
+      //共通
-    Integer texts_id = null;
+      pstmt.setString(1, name);
+      pstmt.setTimestamp(2, new java.sql.Timestamp(System.currentTimeMillis()));
+      //テキスト
+      if(isValid(text)) {
+        pstmt.setString(3, text);
+      } else {
+        pstmt.setNull(3, Types.VARCHAR);
+      }
+      //画像
-    if(text != null && text.length() > 0) {
+      if(isValid(file_name) && image != null) {
+        pstmt.setString(4, file_name);
-      texts_id = insertText(conn, name, text);
+        pstmt.setString(5, image.getContentType());
+        pstmt.setBlob(6, image.getFileContent());
+      } else {
+        pstmt.setNull(4, Types.VARCHAR);
+        pstmt.setNull(5, Types.VARCHAR);
+        pstmt.setNull(6, Types.BLOB);
+      }
+      if(pstmt.executeUpdate() <= 0) {
+        throw new IllegalStateException();
+      }
     }
-    if(image != null) {
-      insertImage(conn, texts_id, image.getFileContent(), image.getContentType());
-    }
-    conn.commit();
   }
-  static int insertText(Connection conn, String name, String text) throws SQLException {
-    String sql = "INSERT INTO texts (name, text, tweet_date) VALUES (?, ?, ?)";
-    try(PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
-      pstmt.setString(1, name);
-      pstmt.setString(2, text);
-      pstmt.setDate(3, new java.sql.Date(System.currentTimeMillis()));
-      if(pstmt.executeUpdate() > 0) {
+  static boolean isValid(String s) {
-        try(ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
-          if(generatedKeys.next()) {
-            return generatedKeys.getInt(1);
+    return s != null && !s.isEmpty();
-          }
-        }
-      }
-    }
-    throw new IllegalStateException();
   }
-  static int insertImage(Connection conn, Integer texts_id, Blob content, String content_type) throws SQLException {
-    String sql = "INSERT INTO images (texts_id, content, content_type) VALUES (?, ?, ?)";
-    try(PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
-      if(texts_id == null) {
-        pstmt.setNull(1, Types.INTEGER);
-      } else {
-        pstmt.setInt(1, texts_id);
-      }
-      pstmt.setBlob(2, content);
-      pstmt.setString(3, content_type);
-      if(pstmt.executeUpdate() > 0) {
-        try(ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
-          if(generatedKeys.next()) {
-            return generatedKeys.getInt(1);
-          }
-        }
-      }
-    }
-    throw new IllegalStateException();
-  }
-//テスト用 初期化
+  //テスト用 初期化
   static void initTables(Connection conn) throws SQLException {
     try(Statement stmt = conn.createStatement()) {
       stmt.execute(
-          "DROP TABLE IF EXISTS images;"
-          + "CREATE TABLE IF NOT EXISTS images ("
-          + " id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,"
-          + " texts_id INT,"
-          + " content BLOB,"
-          + " content_type VARCHAR(32),"
-          + " update_at TIMESTAMP"
-          + ");");
-      stmt.execute(
-          "DROP TABLE IF EXISTS texts;"
+          "DROP TABLE IF EXISTS posts;"
-          + "CREATE TABLE IF NOT EXISTS texts ("
+          + "CREATE TABLE IF NOT EXISTS posts ("
           + " id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,"
           + " name VARCHAR(200),"
-          + " text VARCHAR(200),"
-          + " tweet_date DATE"
+          + " datetime DATETIME,"
+          + " text VARCHAR(200)," //テキスト投稿(テキストが無いときは null)
+          + " image_name VARCHAR(200)," //画像投稿(画像が無いときは null)
+          + " image_type VARCHAR(32),"  //同上
+          + " image BLOB"               //同上
-          + ")");
+          + ");"
-      stmt.execute("DELETE FROM filepaths");
-      stmt.execute("DELETE FROM mutters");
+          + "DELETE FROM posts;");
     }
   }
@@ -142,24 +97,16 @@
   //テスト用 確認表示
   private static void printTables(Connection conn) throws SQLException {
     try(Statement stmt = conn.createStatement()) {
-      System.out.println("-- texts --------");
+      System.out.println("-- posts --------");
-      try(ResultSet rs = stmt.executeQuery("SELECT * FROM texts")) {
+      try(ResultSet rs = stmt.executeQuery("SELECT id, name, datetime, text, image_name, image_type, image FROM posts ORDER BY datetime DESC")) {
         while(rs.next()) {
           System.out.println("ID=" + rs.getInt(1));
           System.out.println("name=" + rs.getString(2));
+          System.out.println("datetime=" + rs.getTimestamp(3));
-          System.out.println("text=" + rs.getString(3));
+          System.out.println("text=" + rs.getString(4));
+          System.out.println("image_name=" + rs.getString(5));
-          System.out.println("date=" + rs.getDate(4));
+          System.out.println("image_type=" + rs.getString(6));
-        }
-      }
-      System.out.println("-- images --------");
-      try(ResultSet rs = stmt.executeQuery("SELECT * FROM images")) {
-        while(rs.next()) {
-          System.out.println("ID=" + rs.getInt(1));
-          Integer texts_id = rs.getInt(2);
-          if(rs.wasNull()) texts_id = null;
-          System.out.println("texts_id=" + texts_id);
-          System.out.println("content=" + toString(rs.getBlob(3)));
+          System.out.println("image=" + toString(rs.getBlob(7)));
-          System.out.println("content_type=" + rs.getString(4));
         }
       }
     }
@@ -167,6 +114,7 @@
   //テスト用 Blob 表示
   private static String toString(Blob blob) throws SQLException {
+    if(blob == null) return "null";
     StringJoiner sj = new StringJoiner(",");
     try(InputStream is = blob.getBinaryStream()) {
       for(int c ; (c=is.read())!=-1; ) sj.add(String.format("%d", c&0xff));
@@ -177,24 +125,27 @@
   }
 }
 ```
-実行結果
 ```
--- texts --------
+-- posts --------
+ID=3
+name=Ccc
+datetime=2024-03-03 02:01:07.598
+text=null
+image_name=file2
+image_type=type2
+image=5,6,7,8
+ID=2
+name=Bbb
+datetime=2024-03-03 02:01:07.582
+text=abcdefg
+image_name=null
+image_type=null
+image=null
 ID=1
 name=Aaa
+datetime=2024-03-03 02:01:07.563
 text=ABCDEFG
-date=2024-02-29
-ID=2
-name=Bbb
-text=abcdefg
-date=2024-02-29
--- images --------
+image_name=file1
-ID=1
-texts_id=1
+image_type=type1
-content=1,2,3,4
+image=1,2,3,4
-content_type=test1
-ID=2
-texts_id=null
-content=5,6,7,8
-content_type=test2
 ```