1 package org.mvnforum.phpbb2mvnforum.db.jdbc; 2 3 import java.sql.Connection ; 4 import java.sql.PreparedStatement ; 5 import java.sql.ResultSet ; 6 import java.sql.SQLException ; 7 import java.sql.Timestamp ; 8 import java.util.Collection ; 9 10 import org.mvnforum.phpbb2mvnforum.db.ForumDAO; 11 import org.mvnforum.util.DBUtils; 12 13 import net.myvietnam.mvncore.exception.CreateException; 14 import net.myvietnam.mvncore.exception.DatabaseException; 15 import net.myvietnam.mvncore.exception.DuplicateKeyException; 16 import net.myvietnam.mvncore.exception.ForeignKeyNotFoundException; 17 import net.myvietnam.mvncore.exception.ObjectNotFoundException; 18 19 import com.mvnforum.db.ForumBean; 20 21 public class ForumDAOImplJDBC implements ForumDAO { 22 23 public void findByPrimaryKey(int forumID) 24 throws ObjectNotFoundException, DatabaseException { 25 26 Connection connection = null; 27 PreparedStatement statement = null; 28 ResultSet resultSet = null; 29 StringBuffer sql = new StringBuffer (512); 30 sql.append("SELECT ForumID"); 31 sql.append(" FROM " + TABLE_NAME); 32 sql.append(" WHERE ForumID = ?"); 33 try { 34 connection = DBUtils.getMvnConnection(); 35 statement = connection.prepareStatement(sql.toString()); 36 statement.setInt(1, forumID); 37 resultSet = statement.executeQuery(); 38 if (!resultSet.next()) { 39 throw new ObjectNotFoundException("Cannot find the primary key (" + forumID + ") in table 'forum'."); 40 } 41 } catch (SQLException sqle) { 42 throw new DatabaseException("Error executing SQL in forumDAOImplJDBC.findByPrimaryKey."); 43 } finally { 44 DBUtils.closeResultSet(resultSet); 45 DBUtils.closeStatement(statement); 46 DBUtils.closeConnection(connection); 47 } 48 } 49 50 public void findByAlternateKey_ForumName_CategoryID(String forumName, int categoryID) 51 throws ObjectNotFoundException, DatabaseException { 52 53 Connection connection = null; 54 PreparedStatement statement = null; 55 ResultSet resultSet = null; 56 StringBuffer sql = new StringBuffer (512); 57 sql.append("SELECT ForumName, CategoryID"); 58 sql.append(" FROM " + TABLE_NAME); 59 sql.append(" WHERE ForumName = ? AND CategoryID = ?"); 60 try { 61 connection = DBUtils.getMvnConnection(); 62 statement = connection.prepareStatement(sql.toString()); 63 statement.setString(1, forumName); 64 statement.setInt(2, categoryID); 65 resultSet = statement.executeQuery(); 66 if (!resultSet.next()) { 67 throw new ObjectNotFoundException("Cannot find the alternate key [ForumName, CategoryID] (" + forumName 68 + ", " + categoryID + ") in table 'forum'."); 69 } 70 } catch (SQLException sqle) { 71 throw new DatabaseException( 72 "Error executing SQL in forumDAOImplJDBC.findByAlternateKey_ForumName_CategoryID."); 73 } finally { 74 DBUtils.closeResultSet(resultSet); 75 DBUtils.closeStatement(statement); 76 DBUtils.closeConnection(connection); 77 } 78 } 79 80 87 public void create(int forumID, int categoryID, String lastPostMemberName, 88 String forumName, String forumDesc, Timestamp forumCreationDate, 89 Timestamp forumModifiedDate, Timestamp forumLastPostDate, int forumOrder, 90 int forumType, int forumFormatOption, int forumOption, 91 int forumStatus, int forumModerationMode, String forumPassword, 92 int forumThreadCount, int forumPostCount) 93 throws CreateException, DatabaseException, DuplicateKeyException { 94 95 try { 99 findByPrimaryKey(forumID); 101 throw new DuplicateKeyException("Primary key already exists. Cannot create new forum with the same [ForumID] (" + forumID + ")."); 103 } catch(ObjectNotFoundException e) { 104 } 106 107 try { 110 findByAlternateKey_ForumName_CategoryID(forumName, categoryID); 112 throw new DuplicateKeyException("Alternate key already exists. Cannot create new forum with the same [ForumName, CategoryID] (" + forumName + ", " + categoryID + ")."); 114 } catch(ObjectNotFoundException e) { 115 } 117 118 Connection connection = null; 119 PreparedStatement statement = null; 120 StringBuffer sql = new StringBuffer (512); 121 sql.append("INSERT INTO " + TABLE_NAME + " (ForumID, CategoryID, LastPostMemberName, ForumName, ForumDesc, ForumCreationDate, ForumModifiedDate, ForumLastPostDate, ForumOrder, ForumType, ForumFormatOption, ForumOption, ForumStatus, ForumModerationMode, ForumPassword, ForumThreadCount, ForumPostCount)"); 122 sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); 123 try { 124 connection = DBUtils.getMvnConnection(); 125 statement = connection.prepareStatement(sql.toString()); 126 127 statement.setInt(1, forumID); 128 statement.setInt(2, categoryID); 129 statement.setString(3, lastPostMemberName); 130 statement.setString(4, forumName); 131 statement.setString(5, forumDesc); 132 statement.setTimestamp(6, forumCreationDate); 133 statement.setTimestamp(7, forumModifiedDate); 134 statement.setTimestamp(8, forumLastPostDate); 135 statement.setInt(9, forumOrder); 136 statement.setInt(10, forumType); 137 statement.setInt(11, forumFormatOption); 138 statement.setInt(12, forumOption); 139 statement.setInt(13, forumStatus); 140 statement.setInt(14, forumModerationMode); 141 statement.setString(15, forumPassword); 142 statement.setInt(16, forumThreadCount); 143 statement.setInt(17, forumPostCount); 144 145 if (statement.executeUpdate() != 1) { 146 throw new CreateException("Error adding a row into table 'forum'."); 147 } 148 } catch(SQLException sqle) { 149 throw new DatabaseException("Error executing SQL in forumDAOImplJDBC.create."); 150 } finally { 151 DBUtils.closeStatement(statement); 152 DBUtils.closeConnection(connection); 153 } 154 } 155 156 public void createMultiple(Collection beans) { 157 159 } 160 161 162 163 164 } 165 | Popular Tags |