package com.oreilly.forum.jdbcimpl;
import com.oreilly.forum.*;
import com.oreilly.forum.adapter.*;
import com.oreilly.forum.domain.*;
import java.sql.*;
import java.util.*;
/**
* An implementation of the DataAdapter that uses JDBC.
*/
public class JdbcDataAdapter extends DataAdapter {
private String dbURL;
/**
* Construct the data adapter and load the JDBC driver.
*/
public JdbcDataAdapter() throws DataException {
this.dbURL = ForumConfig.getDatabaseURL();
try {
Class.forName(ForumConfig.getJDBCDriverClassName());
} catch (Exception ex) {
ex.printStackTrace();
throw new DataException("Unable to load JDBC driver: "
+ ForumConfig.getJDBCDriverClassName());
}
}
/**
* @param msgID must be a valid message identifier.
* @return the message with the specified id.
* @throws DataException if msgID does not exist or a database
* error occurs.
*/
public Message getMessage(long msgID) throws DataException {
Connection con = null;
Statement stmt = null;
try {
con = DBUtil.getConnection(dbURL);
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(
"SELECT inReplyToID, createDay, createMonth, createYear, "
+ "boardID, subject, authorEmail, msgText "
+ "FROM Message WHERE id="
+ msgID);
if (rs.next()) {
long inReplyToID = rs.getLong(1);
int createDay = rs.getInt(2);
int createMonth = rs.getInt(3);
int createYear = rs.getInt(4);
long boardID = rs.getLong(5);
String subject = rs.getString(6);
String authorEmail = rs.getString(7);
String msgText = DBUtil.getLongString(rs, 8);
BoardSummary boardSummary = this.getBoardSummary(boardID, stmt);
return new MessageImpl(msgID,
new DayMonthYear(createDay, createMonth, createYear),
boardSummary, subject, authorEmail, msgText,
inReplyToID);
} else {
throw new DataException("Illegal msgID: " + msgID);
}
} catch (SQLException sqe) {
sqe.printStackTrace();
throw new DataException(sqe.getMessage());
} finally {
DBUtil.close(stmt, con);
}
}
/**
* Add a reply to an existing message.
*
* @throws DataException if a database error occurs, or if any
* parameter is illegal.
*/
public Message replyToMessage(long origMsgID,
String msgSubject, String authorEmail, String msgText)
throws DataException {
Message inReplyToMsg = this.getMessage(origMsgID);
return insertMessage(inReplyToMsg.getBoard(), origMsgID,
msgSubject, authorEmail, msgText);
}
/**
* Post a new message.
*
* @return the newly created message.
* @throws DataException if a database error occurs, or if any
* parameter is illegal.
*/
public Message postNewMessage(long boardID, String msgSubject,
String authorEmail, String msgText) throws DataException {
BoardSummary board = this.getBoardSummary(boardID);
return insertMessage(board, -1, msgSubject, authorEmail, msgText);
}
/**
* If no messages exist for the specified board and month, return
* an empty iterator.
* @return an iterator of <code>MessageSummary</code> objects.
* @throws DataException if the boardID is illegal or a database
* error occurs.
*/
public Iterator getAllMessages(long boardID, MonthYear month)
throws DataException {
List allMsgs = new ArrayList();
Connection con = null;
Statement stmt = null;
try {
con = DBUtil.getConnection(dbURL);
stmt = con.createStatement();
BoardSummary boardSum = this.getBoardSummary(boardID, stmt);
ResultSet rs = stmt.executeQuery(
"SELECT id, inReplyToID, createDay, "
+ "subject, authorEmail "
+ "FROM Message WHERE createMonth="
+ month.getMonth()
+ " AND createYear="
+ month.getYear()
+ " AND boardID="
+ boardID);
while (rs.next()) {
long msgID = rs.getLong(1);
long inReplyTo = rs.getLong(2);
int createDay = rs.getInt(3);
String subject = rs.getString(4);
String authorEmail = rs.getString(5);
DayMonthYear createDMY = new DayMonthYear(
createDay, month.getMonth(), month.getYear());
allMsgs.add(new MessageSummaryImpl(msgID, createDMY,
boardSum,
subject, authorEmail, inReplyTo));
}
return allMsgs.iterator();
} catch (SQLException sqe) {
sqe.printStackTrace();
throw new DataException(sqe);
} finally {
DBUtil.close(stmt, con);
}
}
/**
* @return an iterator of all <code>BoardSummary</code> objects.
*/
public Iterator getAllBoards() throws DataException {
List allBoards = new ArrayList();
Connection con = null;
Statement stmt = null;
Statement stmt2 = null;
try {
con = DBUtil.getConnection(dbURL);
stmt = con.createStatement();
stmt2 = con.createStatement();
ResultSet rs = stmt.executeQuery(
"SELECT id, name, description FROM Board "
+ "ORDER BY name");
while (rs.next()) {
long id = rs.getLong(1);
String name = rs.getString(2);
String description = rs.getString(3);
// get the months with messages. Use a different
// Statement object because we are in the middle of
// traversing a ResultSet that was created with the
// first Statement.
List monthsWithMessages =
this.getMonthsWithMessages(id, stmt2);
allBoards.add(new BoardSummaryImpl(id, name, description,
monthsWithMessages));
}
return allBoards.iterator();
} catch (SQLException sqe) {
sqe.printStackTrace();
throw new DataException(sqe);
} finally {
if (stmt2 != null) {
try {
stmt2.close();
} catch (SQLException ignored) {
}
}
DBUtil.close(stmt, con);
}
}
/**
* @return a board summary for the given id.
* @throws DataException if boardID is illegal or a database
* error occurs.
*/
public BoardSummary getBoardSummary(long boardID)
throws DataException {
Connection con = null;
Statement stmt = null;
try {
con = DBUtil.getConnection(dbURL);
stmt = con.createStatement();
return getBoardSummary(boardID, stmt);
} catch (SQLException sqe) {
sqe.printStackTrace();
throw new DataException(sqe);
} finally {
DBUtil.close(stmt, con);
}
}
private BoardSummary getBoardSummary(long boardID, Statement stmt)
throws DataException, SQLException {
ResultSet rs = stmt.executeQuery(
"SELECT name, description FROM Board WHERE id=" + boardID);
if (rs.next()) {
String name = rs.getString(1);
String description = rs.getString(2);
List monthsWithMessages = getMonthsWithMessages(boardID, stmt);
return new BoardSummaryImpl(boardID, name, description,
monthsWithMessages);
} else {
throw new DataException("Unknown boardID");
}
}
/**
* @return a list of MonthYear objects
*/
private List getMonthsWithMessages(long boardID, Statement stmt)
throws SQLException {
List monthsWithMessages = new ArrayList();
ResultSet rs = stmt.executeQuery(
"SELECT DISTINCT createMonth, createYear "
+ "FROM Message "
+ "WHERE boardID=" + boardID);
while (rs.next()) {
monthsWithMessages.add(new MonthYear(
rs.getInt(1), rs.getInt(2)));
}
return monthsWithMessages;
}
private Message insertMessage(BoardSummary board, long inReplyToID,
String msgSubject, String authorEmail,
String msgText) throws DataException {
// avoid overflowing the max database column lengths
if (msgSubject.length() > ForumConfig.MAX_MSG_SUBJECT_LEN) {
msgSubject = msgSubject.substring(0,
ForumConfig.MAX_MSG_SUBJECT_LEN);
}
if (authorEmail.length() > ForumConfig.MAX_EMAIL_LEN) {
authorEmail = authorEmail.substring(0,
ForumConfig.MAX_EMAIL_LEN);
}
DayMonthYear createDate = new DayMonthYear();
Connection con = null;
PreparedStatement stmt = null;
try {
con = DBUtil.getConnection(dbURL);
long newMsgID = DBUtil.getNextID("Message", con);
stmt = con.prepareStatement("INSERT INTO Message "
+ "(id, inReplyToID, createMonth, createDay, createYear, "
+ "boardID, subject, authorEmail, msgText) "
+ "VALUES (?,?,?,?,?,?,?,?,?)");
stmt.setString(1, Long.toString(newMsgID));
stmt.setString(2, Long.toString(inReplyToID));
stmt.setInt(3, createDate.getMonth());
stmt.setInt(4, createDate.getDay());
stmt.setInt(5, createDate.getYear());
stmt.setString(6, Long.toString(board.getID()));
stmt.setString(7, msgSubject);
stmt.setString(8, authorEmail);
DBUtil.setLongString(stmt, 9, msgText);
stmt.executeUpdate();
return new MessageImpl(newMsgID, createDate,
board, msgSubject, authorEmail,
msgText, inReplyToID);
} catch (SQLException sqe) {
sqe.printStackTrace();
throw new DataException(sqe);
} finally {
DBUtil.close(stmt, con);
}
}
}
|
