simplified reddit home page clone
This web app was created using Java EE, MySQL, JDBC, Servlet, and JSP was used for the view layer.
The goal of this web app is to clone the reddit home page.
features include:
- 1) login // register users
- 2) create // edit (only for authors) // reply (only for registered users) // view // delete boards
project directory:
MySQL query: (create members)
CREATE TABLE `members` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` varchar(100) NOT NULL,
`userPw` varchar(100) NOT NULL,
`userEmail` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
)
MySQL query: (create boards)
CREATE TABLE `posts` (
`mId` int(11) NOT NULL AUTO_INCREMENT,
`mGroup` int(11) NOT NULL,
`step` int(11) NOT NULL,
`hit` int(11) NOT NULL DEFAULT '0',
`indent` int(11) NOT NULL,
`userId` varchar(100) NOT NULL,
`title` varchar(100) NOT NULL DEFAULT 'title',
`content` varchar(400) NOT NULL,
`date` timestamp NOT NULL,
PRIMARY KEY (`mId`)
)
database table looks like this:
lets get to it!!!
context.xml
// make sure the name matches the context lookup string in the DBConnect class
<?xml version="1.0" encoding="UTF-8"?>
<Context path="/">
<Resource name="jdbc/db"
auth="Container"
type="javax.sql.DataSource"
username="[]"
password="[]"
driverClassName="com.mysql.cj.jdbc.Driver"
url="jdbc:mysql://localhost:3306/webprojects?allowPublicKeyRetrieval=true&useSSL=false&amp&useUnicode=true&amp&serverTimezone=UTC"
loginTimeout="100"
maxActive="500"
maxIdle="100"
/>
</Context>
web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
version="4.0">
<!-- mysql -->
<context-param>
<description>sql driver</description>
<param-name>driver</param-name>
<param-value>com.mysql.cj.jdbc.Driver</param-value>
</context-param>
<context-param>
<description>sql url</description>
<param-name>url</param-name>
<param-value>jdbc:mysql://localhost:3306/webprojects?allowPublicKeyRetrieval=true&useSSL=false&useUnicode=true&serverTimezone=UTC</param-value>
</context-param>
<context-param>
<description>hostId</description>
<param-name>hostId</param-name>
<param-value>[]</param-value>
</context-param>
<context-param>
<description>hostPw</description>
<param-name>hostPw</param-name>
<param-value>[]</param-value>
</context-param>
<!-- connection pool -->
<resource-ref>
<description>connectDBCP</description>
<res-ref-name>jdbc/db</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
<!-- welcome files -->
<display-name>simple reddit clone</display-name>
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
<welcome-file>index.html</welcome-file>
</welcome-file-list>
<!-- error page -->
<error-page>
<location>/error.html</location>
</error-page>
</web-app>
let's setup the connection with the database by setting up DBConnect (connection pooling setup)
// this class links datasource information in the context.xml file and creates a dataSource
package org.web.dbConnect;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import java.sql.Connection;
public class DBConnect {
public static Connection getConnection() {
Connection conn = null;
try {
Context initContext = new InitialContext();
Context envContext = (Context) initContext.lookup("java:/comp/env");
DataSource ds = (DataSource) envContext.lookup("jdbc/db");
conn = ds.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
}
MemberDTO
package org.web.memberDTO;
public class MemberDTO {
private int id;
private String userId, userPw, userEmail;
public MemberDTO(String userId, String userPw, String userEmail) {
this.userId = userId;
this.userPw = userPw;
this.userEmail = userEmail;
}
public MemberDTO(int id, String userId, String userPw, String userEmail) {
this.id = id;
this.userId = userId;
this.userPw = userPw;
this.userEmail = userEmail;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public String getUserPw() {
return userPw;
}
public void setUserPw(String userPw) {
this.userPw = userPw;
}
public String getUserEmail() {
return userEmail;
}
public void setUserEmail(String userEmail) {
this.userEmail = userEmail;
}
// for debugging purposes
@Override
public String toString() {
return "MemberDTO{" +
"id=" + id +
", userId='" + userId + '\'' +
", userPw='" + userPw + '\'' +
", userEmail='" + userEmail + '\'' +
'}';
}
}
BoardDTO
package org.web.boardDTO;
import java.sql.Timestamp;
public class BoardDTO {
private int mId, mGroup, step, hit, indent;
private String userName, title, content;
private Timestamp date;
public BoardDTO(int mId, int mGroup, int step, int hit, int indent, String userName, String title, String content, Timestamp date) {
this.mId = mId;
this.mGroup = mGroup;
this.step = step;
this.hit = hit;
this.indent = indent;
this.userName = userName;
this.title = title;
this.content = content;
this.date = date;
}
public int getmId() {
return mId;
}
public void setmId(int mId) {
this.mId = mId;
}
public int getmGroup() {
return mGroup;
}
public void setmGroup(int mGroup) {
this.mGroup = mGroup;
}
public int getStep() {
return step;
}
public void setStep(int step) {
this.step = step;
}
public int getHit() {
return hit;
}
public void setHit(int hit) {
this.hit = hit;
}
public int getIndent() {
return indent;
}
public void setIndent(int indent) {
this.indent = indent;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public Timestamp getDate() {
return date;
}
public void setDate(Timestamp date) {
this.date = date;
}
}
Controller (redirecting users so they can't access the pages manually)
package org.web.controller;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@WebServlet("*.do")
public class Controller extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doService(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doService(req, resp);
}
private void doService(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
resp.setCharacterEncoding("utf-8");
// retrieving the url
String path = req.getContextPath();
String URI = req.getRequestURI();
// substringing to cut out everything before /
String basicURL = URI.substring(path.length());
String url = "";
// redirect links
switch (basicURL) {
case "/join.do":
url = "/join.jsp";
break;
case "/login.do":
url = "/login.jsp";
break;
case "/profile.do":
url = "/profile.jsp";
break;
case "/updateProfile.do":
url = "/updateProfile.jsp";
break;
case "/deleteProfile.do":
url = "/deleteProfile.jsp";
break;
case "/profileDeleted.do":
url = "/profileDeleted.jsp";
break;
}
RequestDispatcher dispatcher = req.getRequestDispatcher(url);
dispatcher.forward(req, resp);
}
}
MemberController
package org.web.memberController;
import org.web.memberCommand.*;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@WebServlet("*.mo")
public class MemberController extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
moService(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
moService(req, resp);
}
private void moService(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
resp.setCharacterEncoding("utf-8");
String path = req.getContextPath();
String URI = req.getRequestURI();
String basicURL = URI.substring(path.length());
String url = "";
MemberCommand action;
RequestDispatcher requestDispatcher;
switch (basicURL) {
case "/idCheck.mo":
action = new IdCheck();
action.executeQueryCommand(req, resp);
break;
case "/join.mo":
action = new MemberJoin();
action.executeQueryCommand(req, resp);
url = (String) req.getAttribute("url");
requestDispatcher = req.getRequestDispatcher(url);
requestDispatcher.forward(req, resp);
break;
case "/login.mo":
action = new MemberLogin();
action.executeQueryCommand(req, resp);
break;
case "/logout.mo":
action = new MemberLogout();
action.executeQueryCommand(req, resp);
url = (String) req.getAttribute("url");
requestDispatcher = req.getRequestDispatcher(url);
requestDispatcher.forward(req, resp);
break;
case "/viewProfile.mo":
action = new MemberView();
action.executeQueryCommand(req, resp);
url = (String) req.getAttribute("url");
requestDispatcher = req.getRequestDispatcher(url);
requestDispatcher.forward(req, resp);
break;
case "/updateProfile.mo":
action = new MemberUpdate();
action.executeQueryCommand(req, resp);
url = (String) req.getAttribute("url");
requestDispatcher = req.getRequestDispatcher(url);
requestDispatcher.forward(req, resp);
break;
case "/deleteProfile.mo":
action = new MemberDelete();
action.executeQueryCommand(req, resp);
url = (String) req.getAttribute("url");
requestDispatcher = req.getRequestDispatcher(url);
requestDispatcher.forward(req, resp);
break;
}
}
}
BoardController
package org.web.boardController;
import org.web.boardCommand.*;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@WebServlet("*.bo")
public class BoardController extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doService(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doService(req, resp);
}
private void doService(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf8");
String path = req.getContextPath();
String uri = req.getRequestURI();
String basicurl = uri.substring(path.length());
String url = "";
BoardCommand action;
RequestDispatcher dispatcher;
switch (basicurl) {
case "/boardList.bo":
action = new BoardList();
action.executeQueryCommand(req, resp);
url = (String) req.getAttribute("url");
dispatcher = req.getRequestDispatcher(url);
dispatcher.forward(req, resp);
break;
case"/boardCommentCount.bo":
action = new BoardCommentCount();
action.executeQueryCommand(req, resp);
break;
case "/boardUpdate.bo":
action = new BoardUpdate();
action.executeQueryCommand(req, resp);
break;
case "/boardWrite.bo":
action = new BoardWrite();
action.executeQueryCommand(req, resp);
break;
case "/boardView.bo":
action = new BoardView();
action.executeQueryCommand(req, resp);
url = (String) req.getAttribute("url");
dispatcher = req.getRequestDispatcher(url);
dispatcher.forward(req, resp);
break;
case "/boardReply.bo":
action = new BoardReply();
action.executeQueryCommand(req, resp);
break;
}
}
}
interface command that will have a method for other operations to override
package org.web.memberCommand;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
public interface MemberCommand {
void executeQueryCommand(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException;
}
MemberCommand (login, logout, join, delete, update, view)
MemberLogin
package org.web.memberCommand;
import org.web.memberDAO.MemberDAO;
import org.web.memberDTO.MemberDTO;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.io.PrintWriter;
public class MemberLogin implements MemberCommand {
@Override
public void executeQueryCommand(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String userId = req.getParameter("userId");
String userPw = req.getParameter("userPw");
MemberDAO dao = MemberDAO.getInstance();
PrintWriter out = resp.getWriter();
HttpSession session = req.getSession(false);
if (session != null && !session.isNew()) {
session.invalidate();
}
int result = dao.memberLogin(userId, userPw);
MemberDTO member = dao.memberLoginAndRetrieveAll(userId, userPw);
if (result == 1) {
session = req.getSession(true);
session.setAttribute("sessionId", member.getUserId());
session.setAttribute("member", member);
session.setMaxInactiveInterval(60 * 10);
}
out.write(result + "");
out.close();
}
}
MemberLogout
package org.web.memberCommand;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
public class MemberLogout implements MemberCommand {
@Override
public void executeQueryCommand(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
HttpSession session = req.getSession();
String url;
if (session != null) {
// terminate session
session.invalidate();
url = "/boardList.do";
} else {
url = "/logout.mo";
}
req.setAttribute("url", url);
}
}
MemberJoin
package org.web.memberCommand;
import org.web.memberDAO.MemberDAO;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
public class MemberJoin implements MemberCommand {
@Override
public void executeQueryCommand(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
System.out.println("join command");
String userId = req.getParameter("userId");
String userPw = req.getParameter("userPw");
String userEmail = req.getParameter("userEmail");
MemberDAO dao = MemberDAO.getInstance();
int result = dao.memberJoin(userId, userPw, userEmail);
String url = "";
if (result == 1) {
url = "/login.jsp";
} else {
url = "/join.jsp";
}
// redirecting user
req.setAttribute("url", url);
}
}
MemberView
package org.web.memberCommand;
import org.web.memberDAO.MemberDAO;
import org.web.memberDTO.MemberDTO;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
public class MemberView implements MemberCommand {
@Override
public void executeQueryCommand(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String memberId = req.getParameter("memberId");
MemberDAO dao = MemberDAO.getInstance();
MemberDTO member = dao.memberView(memberId);
String url = "";
if (member != null) {
req.setAttribute("member", member);
url = "/profile.do";
} else {
url = "/profile.do";
}
req.setAttribute("url", url);
}
}
MemberDelete
package org.web.memberCommand;
import org.web.memberDAO.MemberDAO;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.io.PrintWriter;
public class MemberDelete implements MemberCommand {
@Override
public void executeQueryCommand(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String id = req.getParameter("id");
String userId = req.getParameter("userId");
String userPw = req.getParameter("userPw");
MemberDAO dao = MemberDAO.getInstance();
int result = dao.memberDelete(userId, userPw);
HttpSession session = req.getSession();
PrintWriter out = resp.getWriter();
if (result == 1) {
session.invalidate();
}
out.write(result + "");
out.close();
}
}
MemberUpdate
package org.web.memberCommand;
import org.web.memberDAO.MemberDAO;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
public class MemberUpdate implements MemberCommand {
@Override
public void executeQueryCommand(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
int id = Integer.parseInt(req.getParameter("memberId"));
String userId = req.getParameter("userId");
String userPw = req.getParameter("userPw");
String userEmail = req.getParameter("userEmail");
MemberDAO dao = MemberDAO.getInstance();
int result = dao.memberUpdate(id, userId, userPw, userEmail);
String url = "";
// try to update session as well
if (result == 1) {
HttpSession session = req.getSession(false);
session.getAttribute("sessionId");
// session.setAttribute("member", member);
url = "/viewProfile.mo?memberId=" + id;
req.setAttribute("url", url);
}
}
}
BoardCommand
package org.web.boardCommand;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
public interface BoardCommand {
void executeQueryCommand(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException;
}
BoardList
package org.web.boardCommand;
import org.web.boardDAO.BoardDAO;
import org.web.boardDTO.BoardDTO;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
public class BoardList implements BoardCommand {
@Override
public void executeQueryCommand(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
BoardDAO dao = BoardDAO.getInstance();
ArrayList<BoardDTO> lists = dao.list();
req.setAttribute("lists", lists);
req.setAttribute("url", "/boardList.jsp");
}
}
BoardView
package org.web.boardCommand;
import org.web.boardDAO.BoardDAO;
import org.web.boardDTO.BoardDTO;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
public class BoardView implements BoardCommand {
@Override
public void executeQueryCommand(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
int mId = (Integer.parseInt(req.getParameter("mId")));
BoardDAO dao = BoardDAO.getInstance();
BoardDTO dto = dao.boardView(mId);
// get the arraylist of all posts
ArrayList<BoardDTO> lists = dao.list();
// send all posts to boardView page
req.setAttribute("lists", lists);
// send the specific post requested to view
req.setAttribute("list", dto);
// req.setAttribute("count", count);
req.setAttribute("url", "/boardView.jsp");
}
}
BoardWrite
package org.web.boardCommand;
import org.web.boardDAO.BoardDAO;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
public class BoardWrite implements BoardCommand {
@Override
public void executeQueryCommand(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String userId = req.getParameter("userId");
String title = req.getParameter("title");
String content = req.getParameter("content");
BoardDAO dao = BoardDAO.getInstance();
int result = dao.write(userId, title, content);
PrintWriter out = resp.getWriter();
out.write(result + "");
out.close();
}
}
BoardDelete
package org.web.boardCommand;
import org.web.boardDAO.BoardDAO;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
public class BoardDelete implements BoardCommand {
@Override
public void executeQueryCommand(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String mId = req.getParameter("mId");
BoardDAO dao = BoardDAO.getInstance();
int result = dao.boardDelete(mId);
PrintWriter out = resp.getWriter();
if (result == 1) {
out.write("1");
} else {
out.write("0");
}
out.close();
}
}
BoardUpdate
package org.web.boardCommand;
import org.web.boardDAO.BoardDAO;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
public class BoardUpdate implements BoardCommand {
@Override
public void executeQueryCommand(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
int mId = Integer.parseInt(req.getParameter("mId"));
String title = req.getParameter("title");
String content = req.getParameter("content");
BoardDAO dao = BoardDAO.getInstance();
int result = dao.boardUpdate(mId, title, content);
PrintWriter out = resp.getWriter();
out.write(result + "");
out.close();
}
}
BoardReply
package org.web.boardCommand;
import org.web.boardDAO.BoardDAO;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
public class BoardReply implements BoardCommand {
@Override
public void executeQueryCommand(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
int mId = Integer.parseInt(req.getParameter("mId"));
int mGroup = Integer.parseInt(req.getParameter("mGroup"));
int step = Integer.parseInt(req.getParameter("step"));
int indent = Integer.parseInt(req.getParameter("indent"));
String userId = req.getParameter("userId");
String title = req.getParameter("title");
String content = req.getParameter("content");
BoardDAO dao = BoardDAO.getInstance();
int result = dao.boardReply(mId, mGroup, step, indent, userId, title, content);
PrintWriter out = resp.getWriter();
out.write(result + "");
out.close();
}
}
MemberDAO (with Singleton pattern)
package org.web.memberDAO;
import org.web.dbConnect.DBConnect;
import org.web.memberDTO.MemberDTO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class MemberDAO {
private Connection conn = null;
private PreparedStatement pstm = null;
private ResultSet rs = null;
// singleton method to have MemberDAO be retrieved without starting a new instantiation every time
private MemberDAO() {
}
public static MemberDAO getInstance() {
return singleton.instance;
}
private static class singleton {
private static final MemberDAO instance = new MemberDAO();
}
// i put the last method in a method to make the code DRY
private void cleanUp() {
try {
if (conn != null) conn.close();
if (pstm != null) pstm.close();
if (rs != null) rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
// id validation method
public int idCheck(String userId) {
int result = 0;
try {
conn = DBConnect.getConnection();
String query = "select count(*) from simpleDatabase.members where userId = ?";
pstm = conn.prepareStatement(query);
pstm.setString(1, userId);
rs = pstm.executeQuery();
if (rs != null) {
while (rs.next()) {
result = rs.getInt(1);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
cleanUp();
}
return result;
}
// join method
public int memberJoin(String userId, String userPw, String userEmail) {
int result = 0;
try {
conn = DBConnect.getConnection();
String query = "insert into simpleDatabase.members (id, userId, userPw, userEmail) values (null, ?, ?, ?)";
pstm = conn.prepareStatement(query);
pstm.setString(1, userId);
pstm.setString(2, userPw);
pstm.setString(3, userEmail);
result = pstm.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
cleanUp();
}
return result;
}
public MemberDTO memberLoginAndRetrieveAll(String userId, String userPw) {
MemberDTO member = null;
try {
conn = DBConnect.getConnection();
String query = "select * from simpleDatabase.members where userId=? and userPw=?";
pstm = conn.prepareStatement(query);
pstm.setString(1, userId);
pstm.setString(2, userPw);
rs = pstm.executeQuery();
if (rs != null) {
while (rs.next()) {
int memberId = rs.getInt(1);
String userId1 = rs.getString(2);
String userPw1 = rs.getString(3);
String userEmail = rs.getString(4);
member = new MemberDTO(memberId, userId1, userPw1, userEmail);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
cleanUp();
}
return member;
}
public int memberLogin(String userId, String userPw) {
int result = 0;
try {
conn = DBConnect.getConnection();
String query = "select count(*) from simpleDatabase.members where userId=? and userPw=?";
pstm = conn.prepareStatement(query);
pstm.setString(1, userId);
pstm.setString(2, userPw);
rs = pstm.executeQuery();
if (rs != null) {
while (rs.next()) {
result = rs.getInt(1);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
cleanUp();
}
return result;
}
public int memberDelete(String userId, String userPw) {
int result = 0;
try {
conn = DBConnect.getConnection();
String query = "delete from simpleDatabase.members where userId=? and userPw=?";
pstm = conn.prepareStatement(query);
pstm.setString(1, userId);
pstm.setString(2, userPw);
result = pstm.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
cleanUp();
}
return result;
}
public MemberDTO memberView(String memberId) {
MemberDTO member = null;
try {
conn = DBConnect.getConnection();
// String query = "select * from simpleDatabase.members where memberId=?";
String query = "select * from simpleDatabase.members where id = ?";
pstm = conn.prepareStatement(query);
pstm.setString(1, memberId);
rs = pstm.executeQuery();
if (rs != null) {
while (rs.next()) {
int memberId1 = rs.getInt(1);
String userId = rs.getString(2);
String userPw = rs.getString(3);
String userEmail = rs.getString(4);
member = new MemberDTO(memberId1, userId, userPw, userEmail);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
cleanUp();
}
return member;
}
public int memberUpdate(int id, String userId, String userPw, String userEmail) {
int result = 0;
try {
conn = DBConnect.getConnection();
String query = "update simpleDatabase.members set userId=?, userPw=?, userEmail=? where id=?";
pstm = conn.prepareStatement(query);
pstm.setString(1, userId);
pstm.setString(2, userPw);
pstm.setString(3, userEmail);
pstm.setInt(4, id);
result = pstm.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
cleanUp();
}
return result;
}
}
BoardDAO
package org.web.boardDAO;
import org.web.boardDTO.BoardDTO;
import org.web.dbConnect.DBConnect;
import java.sql.*;
import java.util.ArrayList;
public class BoardDAO {
private Connection conn = null;
private PreparedStatement pstm = null;
private ResultSet rs = null;
private BoardDAO() {
}
// singleton pattern
private static class singleton {
private static final BoardDAO instance = new BoardDAO();
}
public static BoardDAO getInstance() {
return singleton.instance;
}
private void cleanUp() {
try {
if (conn != null) {
conn.close();
}
if (pstm != null) {
pstm.close();
}
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
// list all boards method
public ArrayList<BoardDTO> list() {
ArrayList<BoardDTO> lists = new ArrayList<>();
try {
conn = DBConnect.getConnection();
String query = "select * from simpleDatabase.posts order by hit desc, mGroup asc, indent asc";
pstm = conn.prepareStatement(query);
rs = pstm.executeQuery();
if (rs != null) {
while (rs.next()) {
int mId = rs.getInt(1);
int mGroup = rs.getInt(2);
int mIndent = rs.getInt(3);
int step = rs.getInt(4);
int hit = rs.getInt(5);
String userId = rs.getString(6);
String title = rs.getString(7);
String content = rs.getString(8);
Timestamp date = rs.getTimestamp(9);
BoardDTO dto = new BoardDTO(mId, mGroup, mIndent, step, hit, userId, title, content, date);
lists.add(dto);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
cleanUp();
}
return lists;
}
public int write(String userId, String title, String content) {
int result = 0;
try {
conn = DBConnect.getConnection();
String query = "insert into simpleDatabase.posts(mGroup, step, hit, indent, userId, title, content, date) values ((select case count(*) when 0 then 1 else max(mGroup) + 1 end from simpleDatabase.posts b1), 0, 0, 0, ?, ?, ?, now())";
pstm = conn.prepareStatement(query);
pstm.setString(1, userId);
pstm.setString(2, title);
pstm.setString(3, content);
result = pstm.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
cleanUp();
}
return result;
}
public BoardDTO boardView(int mId) {
BoardDTO dto = null;
upHit(mId);
try {
conn = DBConnect.getConnection();
String query = "select * from simpleDatabase.posts where mId=?";
pstm = conn.prepareStatement(query);
pstm.setInt(1, mId);
rs = pstm.executeQuery();
if (rs != null) {
while (rs.next()) {
int mId1 = rs.getInt(1);
int mGroup = rs.getInt(2);
int step = rs.getInt(3);
int hit = rs.getInt(4);
int indent = rs.getInt(5);
String userId = rs.getString(6);
String title = rs.getString(7);
String content = rs.getString(8);
Timestamp date = rs.getTimestamp(9);
dto = new BoardDTO(mId1, mGroup, step, hit, indent, userId, title, content, date);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
cleanUp();
}
return dto;
}
public int boardReply(int mId, int mGroup, int step, int indent, String userId, String title, String content) {
int result = 0;
boardReplyUpdate(mGroup, step);
try {
conn = DBConnect.getConnection();
String query = "insert into simpleDatabase.posts(mId, mGroup, step, indent, userId, title, content, date) VALUES (null, ?, ?, ?, ?, ?, ?, now())";
pstm = conn.prepareStatement(query);
pstm.setInt(1, mGroup);
pstm.setInt(2, step + 1);
pstm.setInt(3, indent + 1);
pstm.setString(4, userId);
pstm.setString(5, title);
pstm.setString(6, content);
result = pstm.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
cleanUp();
}
return result;
}
private int upHit(int mId) {
int result = 0;
try {
conn = DBConnect.getConnection();
String query = "update simpleDatabase.posts set hit = hit + 1 where mId = ?";
pstm = conn.prepareStatement(query);
pstm.setInt(1, mId);
result = pstm.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
cleanUp();
}
return result;
}
private int boardReplyUpdate(int mGroup, int step) {
int result = 0;
try {
conn = DBConnect.getConnection();
String query = "update simpleDatabase.posts set step = step + 1 where mGroup = ? and step > ?";
pstm = conn.prepareStatement(query);
pstm.setInt(1, mGroup);
pstm.setInt(2, step);
result = pstm.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
cleanUp();
}
return result;
}
public int commentCount(int mGroup) {
int count = 0;
try {
conn = DBConnect.getConnection();
String query = "select count(*) from simpleDatabase.posts where mGroup = ?";
pstm = conn.prepareStatement(query);
pstm.setInt(1, mGroup);
rs = pstm.executeQuery();
if (rs != null) {
while (rs.next()) {
count = rs.getInt(1);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
cleanUp();
}
return count;
}
public int boardUpdate(int mId, String title, String content) {
int result = 0;
try {
conn = DBConnect.getConnection();
String query = "update simpleDatabase.posts set title = ?, content = ? where mId = ?";
pstm = conn.prepareStatement(query);
pstm.setString(1, title);
pstm.setString(2, content);
pstm.setInt(3, mId);
result = pstm.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
cleanUp();
}
return result;
}
public int boardDelete(String mId) {
int result = 0;
try {
conn = DBConnect.getConnection();
String query = "delete from simpleDatabase.posts where mId = ?";
pstm = conn.prepareStatement(query);
pstm.setString(1, mId);
result = pstm.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
cleanUp();
}
return result;
}
}
IdCheck (to check if the id exists already in the database)
package org.web.memberCommand;
import org.web.memberDAO.MemberDAO;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
public class IdCheck implements MemberCommand {
@Override
public void executeQueryCommand(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String userId = req.getParameter("userId");
MemberDAO dao = MemberDAO.getInstance();
int result = dao.idCheck(userId);
PrintWriter out = resp.getWriter();
out.write(result + "");
out.close();
}
}
check out part 2 for the view layer
.png)
.png)
.png)
