データベースを利用した画像データとテキストデータの関連付けができません

Question

実現したいこと

現在java H2データベースを使用してTwitter(X)のようなつぶやきアプリの制作をしています。
画像を格納するテーブルと、テキストを格納するテーブルの２つがあります。
画像とテキストデータを関連付けて表示させたいです。(関連付けて投稿したつもりでも識別子がうまく付与されずにテキストのみ、と画像のみという判定になってしまう)

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

ユーザー情報登録→それを使用してログイン→メイン画面(実際につぶやきをするタイムライン)
ログインまではできており、実際に送信をすることでテキストと画像データもデータベースへと格納までできています。コメントを送信するとタイムラインに新しいものから順に表示され、画像も同じように表示されます。しかし、どうしてもテキストと画像を関連付けて表示することができません。
テーブルにも参照整合性制約を持たせてもみましたが、制約違反が解決できず現在は制約をはずしました。画像は格納されても、テキストテーブルと同じIDを挿入できません。nullと表示されてしまいます。

なぜnullになってしまうのか、なぜ制約をもたせると違反になってしまうのか、その解決と、テキストと画像を効率的に関連付ける方法があれば教えていただきたいです。

先週の金曜日から今日まで50時間以上同じところで悩んでいます。できれば一人で解決したかったのですが、ここ二日は考えすぎて睡眠もとれず徹夜状態で頭がおかしくなりそうで相談させていただくことにしました。よろしくお願いします。
プログラミングをはじめて2か月にも満たない初心者で突っ込みどころの多いコードだと思います。
他にロジッククラスやBOクラスがありますがそちらは特に問題ではないと思っていますが、非常に多いので、問題のありそうだと思われるところを抜粋しますがもし足りない情報があれば追加します。

上記の画像のように表示されてしまっていました。このテキストと画像を同時に投稿した場合には独立せず同じ枠内で表示したかったのです。SELECTクエリではA,B,Cそれぞれをデータベース上で確認をして、全てのデータを重複せずに取得できることを確認しています。
この後行った修正は、残っている原因として考えられるものはA,B,Cで取得したレコードを全て一つのリストにまとめていましたが、これを順番に取り出す過程で関連させたい画像とテキストデータが結びついていないからでは？という発想に至り、Mapオブジェクトを作成してみることにしました。これでそれぞれA,B,Cから取得したレコードをMapオブジェクトにしてから一つのリストにまとめましたがこれでも表示は変わりませんでした。
データベースからの取得してくるクエリで、UNIONというものを教えてもらい、それでやってみたら思い通りの表示が可能になりました。以下使用したクエリ
String queryA = "SELECT * FROM (";
queryA += "SELECT m.mutter_id AS mutter_id, m.name AS name, m.text AS text, m.tweet_date AS tweet_date, f.name AS file_name, f.content AS content, f.content_type AS content_type, f.uploaded_at AS uploaded_at ";
queryA += "FROM MUTTERS m ";
queryA += "LEFT JOIN FILEPATHS f ON m.mutter_id = f.mutter_id ";
queryA += "UNION ";
queryA += "SELECT m.mutter_id AS mutter_id, f.name AS name, m.text AS text, m.tweet_date AS tweet_date, m.name AS file_name, f.content AS content, f.content_type AS content_type, f.uploaded_at AS uploaded_at ";
queryA += "FROM MUTTERS m ";
queryA += "RIGHT JOIN FILEPATHS f ON m.mutter_id = f.mutter_id ";
queryA += ") AS result ORDER BY COALESCE(tweet_date, uploaded_at) DESC";

このクエリを使用しただけで表示が改善されたので、データベースからの取得が間違っていたのだと思います。(まだ具体的に以前使用していたクエリの問題点ははっきりしていません)
この後、取得したレコードの表示順でまた問題が発生したため最後の AS result ORDER BY COALESCE(tweet_date, uploaded_at) DESC";というものを調べて外側で並び替えを行えるというものだったのでこれを使用したら並び替えもうまくできました。

該当のソースコード

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // リクエストパラメータの取得
    request.setCharacterEncoding("UTF-8");
    String text = request.getParameter("text");

    
   
    request.setCharacterEncoding("UTF-8");

    List<String> images = new ArrayList<String>();
    
    // postされたパラメータの確認
    String name = request.getParameter("name");
    Part filePart = request.getPart("file1");

    
    if (!text.isEmpty() && filePart != null && filePart.getSize() > 0) {
    	// セッションスコープに保存されたユーザー情報を取得
        HttpSession session = request.getSession();
        User loginUser = (User) session.getAttribute("loginUser");

        // つぶやきをつぶやきリストに追加
        Mutter mutter = new Mutter(loginUser.getName(), text);
        PostMutterLogic postMutterLogic = new PostMutterLogic();
        postMutterLogic.execute(mutter);
    	
     // 画像のアップロード処理
        String filename = makeUploadedFileName(filePart);
        InputStream fileContent = filePart.getInputStream();
        String contentType = filePart.getContentType();
        Image image = new Image(filename, fileContent, contentType);
        PostImageLogic postImageLogic = new PostImageLogic();
  		try {
			postImageLogic.execute(image);
		} catch (FileNotFoundException e) {
		
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
		
			e.printStackTrace();
		}
    }else if (text == null || text.isEmpty()) {
  		// 画像のアップロード処理
  	        String filename = makeUploadedFileName(filePart);
  	        InputStream fileContent = filePart.getInputStream();
  	        String contentType = filePart.getContentType();
  	    
  	    	Image image = new Image(filename,fileContent,contentType);
  	        PostImageLogic postImageLogic = new PostImageLogic();
  	  		try {
  				postImageLogic.execute(image);
  			} catch (FileNotFoundException e) {
  			
  				e.printStackTrace();
  			} catch (ClassNotFoundException e) {
  			
  				e.printStackTrace();
  			}
  	    }
    		else if (!text.isEmpty()){
        	// セッションスコープに保存されたユーザー情報を取得
            HttpSession session = request.getSession();
            User loginUser = (User) session.getAttribute("loginUser");

            // つぶやきをつぶやきリストに追加
            Mutter mutter = new Mutter(loginUser.getName(), text);
            PostMutterLogic postMutterLogic = new PostMutterLogic();
            postMutterLogic.execute(mutter);
            
    		}

public class ImageDAO {
  // データベース接続に使用する情報
  private final static String JDBC_URL = "jdbc:h2:tcp://localhost/~/dokoTsubuUp";
  private final static String DB_USER = "sa";
  private final static String DB_PASS = "";
 

 
//画像のみの投稿の処理
  public boolean create(Image image) {
	    try (Connection conn = DriverManager.getConnection(JDBC_URL, DB_USER, DB_PASS)) {
	        String sql = "INSERT INTO FILEPATHS (content, content_type, uploaded_at) VALUES (?, ?, ?)";
	        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
	            pstmt.setBlob(1, image.getFileContent());
	            pstmt.setString(2, image.getContentType());
	            pstmt.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));
	            int result = pstmt.executeUpdate();
	            return result == 1;
	        }
	    } catch (SQLException e) {
	        e.printStackTrace();
	        return false;
	    }
	}

//画像とテキストが投稿された場合の処理
public boolean insertTextAndImage(Mutter mutter, Image image) {
    try (Connection conn = DriverManager.getConnection(JDBC_URL, DB_USER, DB_PASS)) {
        conn.setAutoCommit(false); // トランザクションを開始

        // テキストを挿入
        String insertMutterSQL = "INSERT INTO MUTTERS (name, text, tweet_date) VALUES (?, ?, ?)";
        try (PreparedStatement pstmtMutter = conn.prepareStatement(insertMutterSQL, Statement.RETURN_GENERATED_KEYS)) {
            pstmtMutter.setString(1, mutter.getUserName());
            pstmtMutter.setString(2, mutter.getText());
            pstmtMutter.setDate(3, new java.sql.Date(System.currentTimeMillis()));

            int affectedRows = pstmtMutter.executeUpdate();
            if (affectedRows == 0) {
                conn.rollback(); // ロールバック
                return false;
            }

            try (ResultSet generatedKeys = pstmtMutter.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    int mutterId = generatedKeys.getInt(1);

                    // 画像を挿入
                    String insertImageSQL = "INSERT INTO FILEPATHS (mutter_id, content, content_type) VALUES (?, ?, ?)";
                    try (PreparedStatement pstmtImage = conn.prepareStatement(insertImageSQL)) {
                        pstmtImage.setInt(1, mutterId);
                        pstmtImage.setBlob(2, image.getFileContent());
                        pstmtImage.setString(3, image.getContentType());

                        affectedRows = pstmtImage.executeUpdate();
                        if (affectedRows == 0) {
                            conn.rollback(); // ロールバック
                            return false;
                        }
                    }
                }
            }
        }

        conn.commit(); // コミット
        return true;
    } catch (SQLException e) {
        e.printStackTrace();
        return false;
    }
}
}

public int create(Mutter mutter) {
	  int mutterId = -1; // 初期値として無効なIDを設定

	    // JDBCドライバを読み込む
	    try {
	        Class.forName("org.h2.Driver");
	    } catch (ClassNotFoundException e) {
	        throw new IllegalStateException("JDBCドライバを読み込めませんでした");
	    }

	    // データベース接続
	    try (Connection conn = DriverManager.getConnection(JDBC_URL, DB_USER, DB_PASS)) {
	        // INSERT文の準備
	        String sql = "INSERT INTO MUTTERS(name, text, tweet_date) VALUES (?, ?, ?)";
	        PreparedStatement pStmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

	        // INSERT文中の「?」に使用する値を設定しSQLを完成
	        pStmt.setString(1, mutter.getUserName());
	        pStmt.setString(2, mutter.getText());
	        pStmt.setDate(3, new java.sql.Date(System.currentTimeMillis()));  // タイムスタンプの取得と設定

	        // INSERT文を実行し、自動生成されたIDを取得する
	        int result = pStmt.executeUpdate();
	        if (result == 1) {
	            ResultSet generatedKeys = pStmt.getGeneratedKeys();
	            if (generatedKeys.next()) {
	                mutterId = generatedKeys.getInt(1); // 自動生成されたIDを取得
	            } else {
	                throw new SQLException("Failed to get generated keys.");
	            }
	        }
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }

	    return mutterId; // 挿入されたテキストのIDを返す
	}
}

試したこと・調べたこと

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

上記の詳細・結果

特に改善はみられず、むしろ最初のころより冗長になってる気もします…
他にはAIを使用してエラーチェックなど行っています。

補足

Tomcat9_java17
Eclipse使用

Comments

[jimbe]

まずは落ち着いて、タイトルには問題を端的に説明する文章を書いてください。
「アプリを作っています」では何の問題の回答を探しているのか全く分かりません。

>画像は格納されても、テキストテーブルと同じIDを挿入できません。nullと表示されてしまいます。

それはご提示のコードのどこでのことでしょうか。

[arias]

大変申し訳ありません！画像にしてきたのですがここでは画像が貼れないのですね…使い方もままならないですがタイトル等修正していきます。

まず、画像は格納されている(2枚目のコードで行っている)public boolean create(Image image)メソッドで画像のみの格納処理を担当しています。
なのでJSP上で画像も表示はされるのですが、データベース内の画像投稿用テーブルには
image_id、mutter_id（テキスト投稿用テーブルと関連付けるためのキー）、ファイルパス(ここではバイナリデータ)、アップロード時間、が存在しています。
この中のmutter_idの値がデータベース上でみるとnullと表示されてしまっています。
テキストテーブルの方には問題なく格納されています。

もしテキストも一緒に投稿された場合にはトランザクション処理を開始し、テキスト投稿したときに発生するmutter_idを画像テーブルに取得できるように処理をしている部分がコード2枚目のpublic boolean insertTextAndImage(Mutter mutter, Image image)メソッド内の処理になります。

この説明でわかりづらかったらすみません(´;ω;｀)

[jimbe]

編集ありがとうございます。
このコメント欄には画像に限らずマークダウンは使えません。
質問に関する情報は基本的には質問を編集して追加してください。

ImageDAO クラスの create(Image) は

"INSERT INTO FILEPATHS (content, content_type, uploaded_at) VALUES (?, ?, ?)";

として FILEPATHS テーブルに INSERT していますが『画像投稿用テーブル』というのは FILEPATHS のことでしょうか。
カラムリストは "content, content_type, uploaded_at" となっていて mutter_id というカラムに値を設定していないのですから(恐らく初期値である) null なのは当然かと思いますけども…。
画像だけの時、 mutter_id にはどんな値が入るはずなのでしょうか。

>テキストテーブルの方には問題なく格納されています。

というのは何のことでしょう。 create(Image) メソッドでは他テーブルに insert はしていないようですが。

[arias]

>"INSERT INTO FILEPATHS (content, content_type, uploaded_at) VALUES (?, ?, ?)";
として FILEPATHS テーブルに INSERT していますが『画像投稿用テーブル』というのは FILEPATHS のことでしょうか。
カラムリストは "content, content_type, uploaded_at" となっていて mutter_id というカラムに値を設定していないのですから(恐らく初期値である) null なのは当然かと思いますけども…。
画像だけの時、 mutter_id にはどんな値が入るはずなのでしょうか。

はい、説明不足でした。テキスト投稿用テーブル(MUTTERS)、画像投稿用テーブル(FILEPATHS)になります。jimbeさんがご指摘してくださった"INSERT INTO FILEPATHS (content, content_type, uploaded_at) VALUES (?, ?, ?)";　クエリは画像のみ投稿された場合に適用されるメソッドとなっていて、画像のみが投稿された場合にはmutter_idには挿入されないようにしています。
画像とテキストの両方が投稿されたと判断された場合にのみ、public boolean insertTextAndImage(Mutter mutter, Image image)メソッドの方で、String insertImageSQL = "INSERT INTO FILEPATHS (mutter_id, content, content_type) VALUES (?, ?, ?)";クエリを指定しています。またこの時ここで挿入されるmutter_idはMUTTERSテーブルから最新のキーを設定しているつもりです。(ここがうまく機能していないのかもしれませんが原因がわかりません。）
画像のみ投稿された場合、画像投稿用のテーブルのmutter_idにはnullが入るように設定されていると思います。なので画像とテキストを同時に投稿した場合でもおそらく画像のみの投稿、とテキストのみの投稿として判断されてしまっているのだと考えていますが原因がわかりません。

>テキストテーブルの方には問題なく格納されています。
というのは何のことでしょう。 create(Image) メソッドでは他テーブルに insert はしていないようですが。

テキストテーブル(MUTTERS)にはテキストのみが投稿された場合に3枚目のコードにあるpublic int create(Mutter mutter)でテキストテーブルに格納されるように指定していますので、こちらが動作して格納されているのだと思います。
また、画像とテキストが同時に投稿された場合に画像投稿用テーブルに挿入されるmutter_idはここから返してきています。

もしかしたら画像のみ挿入されるメソッド
public boolean create(Image image)のか所に//画像とテキストの両方を投稿した場合の処理と誤って記述してしまっているので誤解を招いたかもしれません。申し訳ありません。修正しておきます。

[jimbe]

create(Image) で mutter_id が null になるのは問題無く、insertTextAndImage(Mutter, Image) が実行されているのに FILEPATHS の mutter_id に入っていないのが問題なのでしょうか。

コード内のコメントは間違っていることもよくありますからそのまま信用はしませんので問題ありません。
むしろ、ご自身のコードのはずなのに何処を実行しているのかを分かっていないような書かれ方をしているのが気になります。

>画像とテキストを同時に投稿した場合でもおそらく画像のみの投稿、とテキストのみの投稿として判断されてしまっているのだと考えていますが原因がわかりません。

その可能性があるのでしたら、その切り分けをしている箇所で判断材料となる変数の値を出力(ログに記録)して確認するべきでしょう。
そこで想定している値になっていないのであれば、変数の値が悪いのか条件が悪いのかの判断で変数の元を辿ったり条件を考え直したりということになります。

コードに睨んでいればバグが分かる(眼力デバッグ)とは限りません。
A をやっているのか B をやっているのか分からないなら、 A の始まりで "A" と出力し B の始まりで "B" と出力するようにして実行してみれば、どっちなのかは考えるまでも無いでしょう。
お手元で幾らでも試せるのでしたら、情報の出力をどんどん追加して情報を集めてください。

[arias]

ご回答ありがとうございます。
ご指摘の通り、自分自身でかいているコードなのに書けば書くほど何をやっているのか混乱してしまったりする場面もあり毎回のように１つのエラーに対して数日かけている始末です。
もちろん自分で考えられる情報出力を試し、わからない部分は調べて参考になりそうな記事を試しては原因を絞ってはいるのですが今回の問題は全く解決する兆しがみえずセンスがないのかと少し弱気になっておりました。
まだプログラミングを初めて1月半ほどですが、エンジニアを目指すのであれば当然のようにかかる時間なのでしょうか？

[jimbe]

問題が起きた時、何が起きているのかはっきり分からないのに「修正」してはいけません。
"はっきり"というのは、「この変数がこうであの変数があぁで・・・その変数がそうの時、このプログラムをここを通ってこの行でそっちの変数が xx になるのが問題」と言えるくらいということです。
混乱していると思われたら、冷静になって情報を整理してください。時には一旦止めて一日程度外に出てリフレッシュするとかも良いでしょう。だらだらと時間を掛けても能率は上がりません。

何かを成す為に時間がどれだけ掛かることが正しいのかという問い自体があまり意味がありません。統計にしろ結果論にしろ、自と他は同じでは無いのです。目安にしても視点が違ったら役に立たないでしょう。(まだ半分か、もう半分か。)
確実なのは一歩ずつ進んでいくことです。(A に行けば B が見えるはず、 B に行けば C が見えるはず・・・)

[jimbe]

SELECT に関する情報が追加されましたので読ませて頂きました。

はっきり言って、テーブルを一つにした方がよほど簡単です。
投稿１つに対しテキストと画像が最大１件ずつしか入らないなら、画像だけの場合は text が null になり、テキストだけの場合は file_name, content, content_type が null になる…と決めてしまえば、保存時はそのように各カラムを設定するだけですし(トランザクション制御も要りません)、表示時にテキストだけなのか画像だけなのかそれとも両方なのかの判断は付くはずです。
そうすれば SELECT なんて "SELECT * FROM テーブル ORDER BY ～" とするだけで済むでしょう。

Answer 1

SELECT に関する情報が質問に追加されましたが、テーブルが 2 つあってどちらも有ったり無かったりするのが原因で複雑な処理・SELECT 文になっています。
テーブルを 1 つに纏めてしまえば id の問題も SELECT の複雑さも無くなり処理が簡単になります。

テキストが無い時や画像が無い時等はぞれぞれのレコードを作らなければ何かの節約になると考えられたのかもしれませんが、そのような最適化を考えるのは時期尚早です。

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.StringJoiner;

import javax.sql.rowset.serial.SerialBlob;

public class Main {
  public static void main(String[] args) throws Exception {
    try(Connection conn = DriverManager.getConnection("jdbc:h2:~/test", "sa", "")) {
      initTables(conn);

      // 3パターンをテスト(少し時間を開けて insert )
      //text & image
      insertPost(conn, "Aaa", "ABCDEFG", "file1", new Image("type1", new byte[]{1,2,3,4}));
      //text only
      Thread.sleep(10);
      insertPost(conn, "Bbb", "abcdefg", null, null);
      //image only
      Thread.sleep(10);
      insertPost(conn, "Ccc", null, "file2", new Image("type2", new byte[]{5,6,7,8}));

      //確認
      printTables(conn);
    }
  }

  static void insertPost(Connection conn, String name, String text, String file_name, Image image) throws SQLException {
    String sql = "INSERT INTO posts (name, datetime, text, image_name, image_type, image) VALUES (?, ?, ?, ?, ?, ?)";

    try(PreparedStatement pstmt = conn.prepareStatement(sql)) {
      //共通
      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(isValid(file_name) && image != null) {
        pstmt.setString(4, file_name);
        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();
      }
    }
  }

  static boolean isValid(String s) {
    return s != null && !s.isEmpty();
  }

  //テスト用 初期化
  static void initTables(Connection conn) throws SQLException {
    try(Statement stmt = conn.createStatement()) {
      stmt.execute(
          "DROP TABLE IF EXISTS posts;"
          + "CREATE TABLE IF NOT EXISTS posts ("
          + " id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,"
          + " name VARCHAR(200),"
          + " datetime DATETIME,"
          + " text VARCHAR(200)," //テキスト投稿(テキストが無いときは null)
          + " image_name VARCHAR(200)," //画像投稿(画像が無いときは null)
          + " image_type VARCHAR(32),"  //同上
          + " image BLOB"               //同上
          + ");"
          + "DELETE FROM posts;");
    }
  }

  //テスト用 ダミー
  private static class Image {
    private String type;
    private byte[] bytes;
    Image(String type, byte[] bytes) {
      this.type = type;
      this.bytes = bytes;
    }
    String getContentType() { return type; }
    Blob getFileContent() throws SQLException { return new SerialBlob(bytes); }
  }

  //テスト用 確認表示
  private static void printTables(Connection conn) throws SQLException {
    try(Statement stmt = conn.createStatement()) {
      System.out.println("-- posts --------");
      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(4));
          System.out.println("image_name=" + rs.getString(5));
          System.out.println("image_type=" + rs.getString(6));
          System.out.println("image=" + toString(rs.getBlob(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));
    } catch(IOException e) {
      throw new SQLException("Blob read error.", e);
    }
    return sj.toString();
  }
}

-- 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
image_name=file1
image_type=type1
image=1,2,3,4