私が実現したコメント方法

23426 ワード

	public int insertReply(Comment comment) throws SQLException {
		int result = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		int insertedRef = comment.getRef();
		int insertedRe_level = comment.getRe_level() + 1;
		
		String sqlMaxBd_cd_num = "SELECT MAX(bd_cm_num) FROM comments WHERE bd_code = ? AND bd_num = ?";
		String sqlSearchRe_step = "SELECT NVL(MIN(re_step), -3) FROM comments WHERE bd_code = ? AND bd_num = ? AND ref = ? AND re_step > ? AND re_level <= ? ";
		String sqlMaxRe_step = "SELECT MAX(re_step) FROM comments WHERE bd_code = ? AND bd_num = ? AND ref = ?";
		String sqlUpdateRe_step = "UPDATE comments SET re_step = re_step + 1 WHERE bd_code = ? AND bd_num = ? AND ref = ? AND re_step >= ?";
		String sqlInsertReply = "INSERT INTO comments VALUES (?, ?, ?, ?, ?,    SYSDATE, ?, ?, ?)";
		
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sqlMaxBd_cd_num);
			pstmt.setInt(1, comment.getBd_code());
			pstmt.setInt(2, comment.getBd_num());
			rs = pstmt.executeQuery();
			rs.next(); // as reply, no check It's null.
			int insertedBd_cm_num= rs.getInt(1) + 1; // inserted bd_cm_num
			pstmt.close();
			rs.close();
			
			pstmt = conn.prepareStatement(sqlSearchRe_step);
			pstmt.setInt(1, comment.getBd_code());
			pstmt.setInt(2, comment.getBd_num());
			pstmt.setInt(3, comment.getRef());
			pstmt.setInt(4, comment.getRe_step());
			pstmt.setInt(5, comment.getRe_level());
			rs = pstmt.executeQuery();
			int insertedRe_step = -1;
			if(rs.next()) {
				insertedRe_step = rs.getInt(1);
			}
			pstmt.close();
			rs.close();
			if(insertedRe_step < 0) {
				pstmt = conn.prepareStatement(sqlMaxRe_step);
				pstmt.setInt(1, comment.getBd_code());
				pstmt.setInt(2, comment.getBd_num());
				pstmt.setInt(3, comment.getRef());
				rs = pstmt.executeQuery();
				rs.next();
				insertedRe_step = rs.getInt(1) + 1;
				pstmt.close();
				rs.close();
			}

			pstmt = conn.prepareStatement(sqlUpdateRe_step);
			pstmt.setInt(1, comment.getBd_code());
			pstmt.setInt(2, comment.getBd_num());
			pstmt.setInt(3, comment.getRef());			
			pstmt.setInt(4, insertedRe_step);	
			pstmt.executeUpdate();
			pstmt.close();
			
			pstmt = conn.prepareStatement(sqlInsertReply);
			pstmt.setInt(1, comment.getBd_code());
			pstmt.setInt(2, comment.getBd_num());
			pstmt.setInt(3, insertedBd_cm_num);
			pstmt.setString(4,comment.getM_id());
			pstmt.setString(5,comment.getContent());
			pstmt.setInt(6, insertedRef);
			pstmt.setInt(7, insertedRe_step);
			pstmt.setInt(8, insertedRe_level);
			pstmt.executeUpdate();
			pstmt.close();
			
			System.out.println("insertedBd_cm_num: " + insertedBd_cm_num);
			System.out.println("insertedRef: " + insertedRef);
			System.out.println("insertedRe_step: " + insertedRe_step);
			System.out.println("insertedRe_level: " + insertedRe_level);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs !=null) rs.close();
			if (pstmt != null) pstmt.close();
			if (conn !=null) conn.close();
		}

		return result;
	}
対応するrefについて
  • 挿入するre stepを探す
  • 対象コメントより大きい番号でreレベルが対象コメントより低い番号を検索
  • 見つからない場合はそのrefの中で最大のre stepとする
  • 見つかったre stepより大きい番号を付けて倒す
  • 見つかったre stepに大コメントを挿入