게시판(DB 활용)
package kr.co.web.controller;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
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 kr.co.web.service.Model;
@WebServlet(urlPatterns = {"/","/write","/detail","/remove"})
public class Controller extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
dual(req,resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
dual(req,resp);
}
private void dual(HttpServletRequest req, HttpServletResponse resp)
throws IOException, ServletException {
req.setCharacterEncoding("UTF-8");
String uri = req.getRequestURI();
String ctx = req.getContextPath();
String addr = uri.substring(ctx.length());
Model model = new Model(req,resp);
switch (addr) {
case "/":
model.getList();
break;
case "/write":
model.wirte();
break;
case "/detail":
model.detail();
break;
case "/remove":
model.remove();
break;
}
}
}
package kr.co.web.service;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import kr.co.web.dao.BoardDAO;
import kr.co.web.dto.BoardBean;
public class Model {
HttpServletRequest req;
HttpServletResponse resp;
RequestDispatcher dis;
public Model(HttpServletRequest req, HttpServletResponse resp) {
this.req = req;
this.resp = resp;
}
//리스트 부르기
public void getList() throws ServletException, IOException {
BoardDAO dao = new BoardDAO();
ArrayList<BoardBean> list = dao.list();
req.setAttribute("list", list);
RequestDispatcher dis = req.getRequestDispatcher("list.jsp");
dis.forward(req, resp);
}
//글쓰기
public void wirte() throws ServletException, IOException {
String user_name = req.getParameter("user_name");
String subject = req.getParameter("subject");
String content = req.getParameter("content");
BoardDAO dao = new BoardDAO();
dao.write(subject,user_name,content);
getList(); //리스트 불러오기
}
//상세보기
public void detail() throws ServletException, IOException {
String idx = req.getParameter("idx");
BoardDAO dao = new BoardDAO();
BoardBean bean = dao.detail(Integer.parseInt(idx));
req.setAttribute("board", bean);
dis = req.getRequestDispatcher("detail.jsp");
dis.forward(req, resp);
}
//삭제하기
public void remove() throws ServletException, IOException {
String idx = req.getParameter("idx");
BoardDAO dao = new BoardDAO();
dao.remove(Integer.parseInt(idx));
getList();
}
}
package kr.co.web.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import kr.co.web.dto.BoardBean;
public class BoardDAO {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
public BoardDAO() { //DB 연결
try {
// 1.Context.xml 객체화
Context ctx = new InitialContext();
// 2.name을 통해서 Resource 찾기 >> DataSource 변환
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/MariaDB");
// 3.Connection 얻어오기
conn = ds.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
}
public ArrayList<BoardBean> list() {
// 1.쿼리문 준비
String sql = "SELECT idx,subject,user_name FROM bbs ORDER BY idx DESC";
ArrayList<BoardBean> list = new ArrayList<BoardBean>();
// 2.Statement or PreparedStatement
try {
ps = conn.prepareStatement(sql);
// 3.쿼리문 실행(executeQuery or executeUpdate)
rs = ps.executeQuery();
// 4. ResultSet 추출
while(rs.next()) {
BoardBean bean = new BoardBean();
bean.setIdx(rs.getInt("idx"));
bean.setSubject(rs.getString("subject"));
bean.setUser_name(rs.getString("user_name"));
list.add(bean);
}
// 5. 자원반납
rs.close();
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public void write(String subject, String user_name, String content) {
// 1.쿼리문 준비
String sql = "INSERT INTO bbs(subject,user_name,content) VALUES(?,?,?)";
try {
// 2.PrepareStatement
ps = conn.prepareStatement(sql);
// 2-2.? 대응
ps.setString(1, subject);
ps.setString(2, user_name);
ps.setString(3, content);
// 3.쿼리문 실행(executeUpdate)
// 4.결과반환
int row = ps.executeUpdate();
System.out.println("변경된 데이터 : "+row);
// 5.자원반납
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public BoardBean detail(int idx) {
// 1. 쿼리문 준비
String sql = "SELECT user_name,subject,content FROM bbs WHERE idx = ?";
BoardBean bean = new BoardBean();
// 2. PrepareStatement
try {
ps = conn.prepareStatement(sql);
// 2-2.?대응
ps.setInt(1, idx);
// 3. 쿼리문 실행(executeQuery)
rs = ps.executeQuery();
// 4. ResultSet 추출
while(rs.next()) {
bean.setUser_name(rs.getString("user_name"));
bean.setSubject(rs.getString("subject"));
bean.setContent(rs.getString("content"));
}
// 5. 자원반납
rs.close();
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return bean;
}
public void remove(int idx) {
// 1.쿼리문 준비
String sql = "DELETE FROM bbs WHERE idx = ?";
// 2.PrepareStatement
try {
ps = conn.prepareStatement(sql);
// 2-2.?대응
ps.setInt(1, idx);
// 3.executeUpdate
// 4.결과 반환
int row = ps.executeUpdate();
// 5.자원반납
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
package kr.co.web.dto;
public class BoardBean {
private String user_name;
private String subject;
private String content;
private int idx;
public int getIdx() {
return idx;
}
public void setIdx(int idx) {
this.idx = idx;
}
public String getUser_name() {
return user_name;
}
public void setUser_name(String user_name) {
this.user_name = user_name;
}
public String getSubject() {
return subject;
}
public void setSubject(String subject) {
this.subject = subject;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
}
회원가입(DB 활용)
package kr.co.web.controller;
import java.io.IOException;
import java.util.HashMap;
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 javax.servlet.http.HttpSession;
import kr.co.web.service.MemberModel;
@WebServlet(urlPatterns = {"/login","/join"})
public class MemberController extends HttpServlet {
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
req.setCharacterEncoding("UTF-8");
String uri = req.getRequestURI();
String ctx = req.getContextPath();
String addr = uri.substring(ctx.length());
MemberModel model = new MemberModel();
String msg = "";
if(addr.equals("/join")) {
HashMap<String, String> params = new HashMap<String, String>();
params.put("id", req.getParameter("id"));
params.put("pw", req.getParameter("pw"));
params.put("name", req.getParameter("name"));
params.put("age", req.getParameter("age"));
params.put("gender", req.getParameter("gender"));
params.put("email", req.getParameter("email"));
msg = model.join(params);
}
if(addr.equals("/login")){
String id = req.getParameter("id");
String pw = req.getParameter("pw");
boolean success = model.login(id, pw);
if(success) {
msg = "로그인에 성공했습니다.";
HttpSession session = req.getSession();
session.setAttribute("loginId", id);
}else {
msg = "로그인에 실패했습니다.";
}
}
req.setAttribute("msg", msg);
RequestDispatcher dis = req.getRequestDispatcher("index.jsp");
dis.forward(req, resp);
}
}
package kr.co.web.service;
import java.util.HashMap;
import kr.co.web.dao.MemberDAO;
public class MemberModel {
public String join(HashMap<String, String> params) {
MemberDAO dao = new MemberDAO();
String msg = "이미 존재하는 아이디입니다.";
//1.해당 아이디를 가지고 있는 사용자가 있는지 db에 물어봄
//2.없으면 가져온 파라메터를 이용해 DB에 INSERT
boolean overlay = dao.overlay(params.get("id"));
//System.out.println("중복아이디 여부 : "+overlay);
// true : 중복O / false : 중복X
if(!overlay) {
if(dao.join(params) == 1) {
msg = "회원가입이 완료되었습니다.";
}else {
msg = "회원가입에 실패했습니다.";
}
}
return msg;
}
public boolean login(String id, String pw) {
String msg = "아이디 또는 비밀번호를 확인 해 주세요";
MemberDAO dao = new MemberDAO();
return dao.login(id,pw);
}
}
package kr.co.web.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
public class MemberDAO {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
public MemberDAO() {
//1.context.xml 객체화
try {
Context ctx = new InitialContext();
//2.name으로 resource 찾아서 datasource 반환
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/MariaDB");
//3.connection 얻어오기
conn = ds.getConnection();
//System.out.println("db접속");
} catch (Exception e) {
e.printStackTrace();
}
}
public boolean overlay(String id) {
//1.쿼리문 준비
String sql = "SELECT id FROM member WHERE id = ?";
boolean overlay = true;
//2.preparedStatement
try {
ps = conn.prepareStatement(sql);
//2-2.?대응
ps.setString(1, id);
//3.executeQuery
rs = ps.executeQuery();
//4.결과반환
//값이 있는지 없는지 boolean값으로 반환
overlay = rs.next();
//5.자원반납
rs.close();
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
return overlay;
}
public int join(HashMap<String, String> params) {
int row = 0;
//1.쿼리문 준비
String sql = "INSERT INTO member(id,pw,name,age,gender,email) VALUES(?,?,?,?,?,?)";
//2.preparedStatement
try {
ps = conn.prepareStatement(sql);
//2-2.?대응
ps.setString(1, params.get("id"));
ps.setString(2, params.get("pw"));
ps.setString(3, params.get("name"));
ps.setInt(4, Integer.parseInt(params.get("age")));
ps.setString(5, params.get("gender"));
ps.setString(6, params.get("email"));
//3.executeUpdate
//4.결과반환
row = ps.executeUpdate();
//5.자원닫기
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return row;
}
public boolean login(String id, String pw) {
boolean success = false;
String sql = "SELECT id FROM member WHERE id = ? AND pw = ?";
try {
ps = conn.prepareStatement(sql);
ps.setString(1, id);
ps.setString(2, pw);
rs = ps.executeQuery();
success = rs.next();
} catch (SQLException e) {
e.printStackTrace();
}
return success;
}
}
