ㄱㄷㅎ
50
2021-06-11 15:04:08
4
96

서블릿 db연동


우선 메인서블릿이구요

package exam;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.Date;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class MenuServlet
 */
@WebServlet("/menu")
public class MenuServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doHandle(request,response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doHandle(request, response);
	}

	protected void doHandle(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=utf-8");
		MenuDAO dao = new MenuDAO();
		PrintWriter out = response.getWriter();
		String command = request.getParameter("command");
		if ( command != null && command.equals("addMenu")) {
			String _name = request.getParameter("name");
			int _price = Integer.parseInt(request.getParameter("price"));
			MenuVO vo = new MenuVO();
			vo.setname(_name);
			vo.setprice(_price);
			dao.addMenu(vo);
		}
		else if ( command != null && command.equals("delMenu")) {
			String name = request.getParameter("name");
			dao.delMenu(name);
		}
	
		
		
		List<MenuVO> list = dao.listMenu();
		
		out.print("<html><body>");
		out.print("<table border=1>");
		out.print("<tr align=center width=50>");
		out.print("<td width=100>메뉴이름</td>");
		out.print("<td width=100>가격</td>");
		out.print("<td width=100>등록일자</td>");
		out.print("<td width=100>삭제</td>");
		out.print("</tr>");
		
		for ( int i = 0; i < list.size(); i++ )
		{
			MenuVO menuVO = (MenuVO)list.get(i);
			String name = menuVO.getname();
			Integer price = menuVO.getprice();
			Date regDate = menuVO.getregDate();
			
			out.print("<html><body><table border=1>");
			out.print("<tr align=center>");
			out.print("<td width=100>"+name+"</td>");
			out.print("<td width=100>"+price+"</td>");
			out.print("<td width=100>"+regDate+"</td>");
			out.print("<td width=100><a href='/exam/menu?command=delMenu&name="+name+"'>삭제</a></td>");
			out.print("</tr>");
		}
		
		
		out.print("</table>");
		out.print("<a href='/exam/menuform.html'>메뉴추가</a>");
		out.print("</body></html>");
		
	}

}


이게 DB연동,데이터추가,데이터삭제,데이터출력등 하는 DAO클래스구

package exam;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import exam.MenuVO;

public class MenuDAO {
	private Connection con;
	private PreparedStatement pstmt;
	private DataSource dataFactory;
	
	public MenuDAO() 
	{
		try 
		{
			Context cont = new InitialContext();
			Context envContext = (Context) cont.lookup("java:/comp/env");
			dataFactory = (DataSource) envContext.lookup("jdbc/oracle");
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	
	
	public List<MenuVO> listMenu()
	{
		List<MenuVO> list = new ArrayList<MenuVO>();
		try
		{
			
			connDB();
			
			
			String query = "select * from t_menu";
			System.out.println("preparedstatement: " + query);
			pstmt = con.prepareStatement(query);
			ResultSet rs = pstmt. executeQuery();
			while (rs.next())
			{
				String name = rs.getString("name");
				Integer price = rs.getInt("price");
				Date regDate = rs.getDate("regDate");
		
				MenuVO vo = new MenuVO();
				
				vo.setname(name);
				vo.setprice(price);
				vo.setregDate(regDate);
				
				list.add(vo);
			}
			
			rs.close();		// 결과셋 -> 운영체제 반환
			pstmt.close();	// 준비질의객체 -> 운영체제 반환
			con.close();	// 데이터베이스 접속 끊고 운영체제한테 반환
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		return list;
	}
	
	public void addMenu(MenuVO menuVO) {
		try {
			con = dataFactory.getConnection();
			String name = menuVO.getname();
			int price = menuVO.getprice();
			String query = "insert into t_menu";
			query += " (name, price) ";
			query += " values (?, ?) ";
			System.out.println("prepareStatememt: " + query);
			pstmt = con.prepareStatement(query);
			pstmt.setString(1, name);
			pstmt.setInt(2, price);
			pstmt.executeUpdate();
			pstmt.close();
		}	catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void delMenu(String name)
	{
		try {
			con = dataFactory.getConnection();
			
			String query = "delete from t_menu" + "where name=?";
			System.out.println("preparestatment:"+ query);
			pstmt = con.prepareStatement(query);
			pstmt.setString(1, name);
			pstmt.executeUpdate();
			pstmt.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
		
	}
	
	private void connDB()
	{
		try
		{	
			String dbURL = "jdbc:oracle:thin:@localhost:1521:XE";
			String dbID = "scott";
			String dbPW = "tiger";
			
			Class.forName("oracle.jdbc.driver.OracleDriver");
			System.out.println("Oracle 드라이버 로딩 성공");
			
			con = DriverManager.getConnection(dbURL, dbID, dbPW);
			System.out.println("Connection 생성 성공");
			pstmt = (PreparedStatement) con.createStatement();
			
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
}


이게 메뉴 등록하는 폼입니다.

<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>menuForm</title>
<script type="text/javascript">
function sendMenu(){
		var menuform = document.menuform;
		var name = menuform.name.value;
		var price = menuform.price.value;

		if (name.length == 0 || name == "") {
			alert("메뉴이름 입력은 필수!");
		} else if (price.length == 0 || price == ""){
			alert("가격 입력은 필수!");
		} else {
			menuform.method = "post"
			menuform.action = "menu"
			menuform.submit();
		}
}
</script>
</head>

<body>
<form name="menuform">
<table>
<tr><td>메뉴이름 : </td><td><input type="text" name="name"><br></td></tr>
<tr><td>가격 : <td> <input type="text" name="price"><br></td></tr>
</table>
<input type="button" onClick="sendMenu()" value="메뉴등록">
<input type="reset" value="초기화">
<input type="hidden" name="command" value="addMenu" />
</form>
</body>
</html>


DB연동, 데이터 출력까진 잘되는데 삭제버튼 누르면 400에러뜨구요

메뉴등록버튼 누르면 오류는안뜨는데 DB에 추가가안돼용... 어디가 잘못됐을까요

밑은 VO클래스입니다.

package exam;

import java.util.Date;

public class MenuVO {
	private String name;
	private int price;
	private Date regDate;
	
	public MenuVO()
	{
		System.out.println("MenuVO 생성자 호출");
	}

	public String getname() {
		return name;
	}

	public void setname(String name) {
		this.name = name;
	}

	public int getprice() {
		return price;
	}

	public void setprice(int price) {
		this.price = price;
	}

	public Date getregDate() {
		return regDate;
	}

	public void setregDate(Date regDate) {
		this.regDate = regDate;
	}
	
	
}



0
  • 답변 4

  • BalanceE
    544
    2021-06-11 16:05:25

    delMenu

    String query = "delete from t_menu" + "where name=?";

    t_menu 띄어쓰기 where가 되어야 하는데 붙어있음



  • BalanceE
    544
    2021-06-11 16:07:43

    그리고 리스트 호출은 conDB()메소드를 호출하는데 

    왜 삽입이랑 삭제는 getConnection으로 가져오는지는 모르겠네요

  • ㄱㄷㅎ
    50
    2021-06-11 22:42:14

    띄어쓰기 해도 400오류뜨네용..

  • ㄱㄷㅎ
    50
    2021-06-11 22:48:28

    conDB() 사용하면 DB여ㅑㄴ결되는데

    getConnection으로 하니까 DB연동이 안되서 conDB로 해놓고 getConnection 주석 했어야했는데 깜빡햇네요. 근데 주석처리하고 conDB로 해도 질문의 문제해결을 안되네요 ㅠㅠ,,,

  • 로그인을 하시면 답변을 등록할 수 있습니다.